Discovering SQL: A Hands-On Guide for Beginners
Alex Kriegel
Language: English
Pages: 432
ISBN: 1118002679
Format: PDF / Kindle (mobi) / ePub
Teaching the SQL skills that businesses demand when hiring programmers
If you're a SQL beginner, you don't just want to learn SQL basics, you also want to get some practical SQL skills you can use in the job market. This book gives you both. Covering the basics through intermediate topics with clear explanations, hands-on exercises, and helpful solutions, this book is the perfect introduction to SQL. Topics include both the current SQL:2008 standards, the upcoming SQL:2011 standards, and also how to use SQL against current releases of the most popular commercial SQL databases, such as Oracle, SQL Server, and MySQL.
- Introduces SQL concepts, explains SQL statements, and clearly shows how to write efficient and effective SQL code
- Uses a hands-on style and a sample database that incorporates all SQL concepts taught in the book; this database will be enhanced through the book as key points and lessons are covered
- Covers topics such as how SQL interacts with the sample database via various interfaces, including vendor-provided utilities, programming languages, SQL clients, and productivity software
- Includes appendices with primers on database normalization, set theory and bollean algebra, RDBMS software step-by-step setup guides, and database connectivity
Learn how to write effective, efficient SQL code with Discovering SQL: A Hands-On Guide for Beginners.
described in greater detail in Chapter 11, which deals with unstructured and semistructured data. Implicit and Explicit Data Conversion With so many data types flying around, how do we ever get reports out of this thing in normal human readable text? Why, by using conversion, of course! Conversion is not for humans only; RDBMSs routinely use it when asked to perform tasks where intent is either implied or explicitly stated; thus the conversions are either implicit or explicit, respectively. An
guess). The WHERE clause provides the needed selectivity. Suppose that we want to retrieve all the books published by a particular publisher. For this purpose, we can use the WHERE keyword. It works like this: SELECT bk_title as Book, bk_publisher as Publisher FROM dbo.books WHERE bk_publisher=’Wiley’; Only books published by Wiley will be retrieved. In fact, you don’t even need the Publisher column in our result; it will just be Wiley, Wiley, Wiley all the way down. You’ve successfully fi ltered
c04.indd 130 3/15/2011 12:22:39 PM SQL Procedural Extensions x 131 for other RDBMSs are available for download from the book’s site at www.wrox.com and at www .agilitator.com. Happy Parsing: Stored Procedures The mechanics of creating a stored procedure is simple: CREATE PROCEDURE
-----------------4 The result is 4, which is how many Wiley books are in our library. The results will be quite different if we ask for a DISTINCT record count: SELECT COUNT(DISTINCT bk_publisher) FROM books WHERE bk_publisher = ‘Wiley’; AS pub_count pub_count ---------------1 The four values have collapsed into one because we have exactly one occurrence of the value in the data set. Of course, both variants could be used within the same query: SELECT COUNT(bk_publisher) , COUNT(DISTINCT
x CHAPTER 5 GROUPING AND AGGREGATION Predictably, we see results organized by publisher in ascending order (the default in all RDBMSs, but the ASC modifier is added to demonstrate the point). Now, by adding BK_PRICE DESC to the ORDER BY clause we instruct the database engine to organize the returned records first in ascending order by publisher and then in descending order by price: SELECT bk_publisher AS publisher ,bk_price AS price FROM books ORDER BY bk_publisher ASC , bk_price DESC; publisher