Product Description

Updated for the latest database management systems - including MySQL 6.0, Oracle 11g, and Microsoft's SQL Server 2008 - this introductory guide will get you up and running with SQL quickly. Whether you need to write database applications, perform administrative tasks, or generate reports, "Learning SQL, Second Edition", will help you easily master all the SQL fundamentals. Each chapter presents a self-contained lesson on a key SQL concept or technique, with numerous illustrations and annotated examples. Exercises at the end of each chapter let you practice the skills you learn. With this book, you will: move quickly through SQL basics and learn several advanced features; use SQL data statements to generate, manipulate, and retrieve data; create database objects, such as tables, indexes, and constraints, using SQL schema statements; learn how data sets interact with queries, and understand the importance of subqueries; and, convert and manipulate data with SQL's built-in functions, and use conditional logic in data statements Knowledge of SQL is a must for interacting with data. With "Learning SQL", you'll quickly learn how to put the power and flexibility of this language to work.

Product Details

• Amazon Sales Rank: #42686 in Books
• Published on: 2009-04-27
• Original language: English
• Number of items: 1
• Binding: Paperback
• 335 pages

From the Publisher
This introductory guide gets you up and running on SQL in short order. A series of helpful chapter exercises teaches you how to generate, manipulate, and retrieve the data stored in your organization's database. Ideal for anyone writing applications, performing administrative tasks, or generating reports.

About the Author
Alan Beaulieu has been designing, building, and implementing custom database applications for over 13 years. He currently runs his own consulting company that specializes in designing Oracle databases and supporting services in the fields of Financial Services and Telecommunications. In building large databases for both OLTP and OLAP environments, Alan utilizes such Oracle features as Parallel Query, Partitioning, and Parallel Server. Alan has a Bachelor of Science degree in Operations Research from the Cornell University School of Engineering. He lives in Massachusetts with his wife and two daughters and can be reached at albeau_mosql@yahoo.com.

Excerpted from Learning SQL by Alan Beaulieu. Copyright © 2005. Reprinted by permission. All rights reserved.
Chapter 3 Query Primer

So far, you have seen a few examples of database queries (a.k.a. select statements) sprinkled throughout the first two chapters. Now it’s time to take a closer look at the
different parts of the select statement and how they interact.

Query Mechanics

Before dissecting the select statement, it might be interesting to look at how queries are executed by the MySQL server (or, for that matter, any database server). If you are using the mysql command-line tool (which I assume you are), then you have already logged in to the MySQL server by providing your username and password (and possibly a hostname if the MySQL server is running on a different computer). Once the server has verified that your username and password are correct, a database connection is generated for you to use. This connection is held by the application that requested it (which, in this case, is the mysql tool) until either the application releases the connection (i.e., as a result of your typing quit) or the server closes the connection (i.e., when the server is shut down). Each connection to the MySQL server is assigned an identifier, which is shown to you when you first log in:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.11-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

In this case, my connection ID is 2. This information might be useful to your database administrator if something goes awry, such as a malformed query that runs for hours, so you might want to jot it down.

Once the server has verified your username and password and issued you a connection, you are ready to execute queries (along with other SQL statements). Each time a query is sent to the server, the server checks the following things prior to statement execution:

• Do you have permission to execute the statement?
• Do you have permission to access the desired data?
• Is your statement syntax correct?

If your statement passes these three tests, then your query is handed to the query optimizer, whose job it is to determine the most efficient way to execute your query. The optimizer will look at such things as the order in which to join the tables named in the query and what indexes are available, and then picks an execution plan, which is used by the server to execute your query.

Understanding and influencing how your database server chooses execution plans is a fascinating topic that many of you will wish to explore. For those readers using MySQL, you might consider reading High Performance MySQL (O’Reilly). Among other things, you will learn how to generate indexes, analyze execution plans, influence the optimizer via query hints, and tune your server’s startup parameters. If you are using Oracle Database or SQL Server, there are dozens of tuning books available.

Once the server has finished executing your query, the result set is returned to the calling application (which is, once again, the mysql tool). As was mentioned in Chapter 1, a result set is just another table containing rows and columns. If your query fails to yield any results, the mysql tool will show you the message found at the end of the following example:

mysql> SELECT emp_id, fname, lname
-> FROM employee
-> WHERE lname = 'Bkadfl';
Empty set (0.00 sec)

If the query returns one or more rows, the mysql tool will format the results by adding column headers and by constructing boxes around the columns using the -, |, and + symbols, as shown in the next example:

This query returns the first and last names of all of the employees in the employee table. After the last row of data is displayed, the mysql tool displays a message telling you how many rows were returned, which, in this case, is 18.

Query Clauses

There are several components or clauses that make up the select statement. While only one of them is mandatory when using MySQL (the select clause), you will usually include at least two or three of the six available clauses. Table 3-1 shows the different clauses and their purposes.

All of the clauses shown in Table 3-1 are included in the ANSI specification; additionally, there are several other clauses unique to MySQL that will be explored in Appendix B. The following sections delve into the uses of the six major query clauses.

Customer Reviews

An Excellent Book
After wanting to get a better grasp on SQL and its Syntax I bought this book along with it other companion "SQL Cookbook".

It has a nicely written and easy to understand flow with exercises at the end of each chapter. I find this a great help as when you have read the chapter, you can try and solve the exercises and hopefully it will have sunk in more.
The hardest section is on subqueries as it seem that you can put them everywhere(ugggh).

It also explains the nuances with SQL Server and Oracle, as the syntax changes for little things like altering tables and transaction.
So in all a very good book that will prove a useful reference and tool for improving ones knowledge of SQL.

Fantastic book for begginers
I'm not a programmer and I bought this book having no idea of SQL, and I can say I learned a lot from it. It's easy to understand and it's well structured. Maybe it doesn't cover every aspect of SQL, but it's the place to start to write your first queries. Give it a try, you won't regret!