Alan Beaulieu - Learning SQL
Here you can read online Alan Beaulieu - Learning SQL full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. year: 2009, publisher: OReilly Media, genre: Home and family. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:
Romance novel
Science fiction
Adventure
Detective
Science
History
Home and family
Prose
Art
Politics
Computer
Non-fiction
Religion
Business
Children
Humor
Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.
- Book:Learning SQL
- Author:
- Publisher:OReilly Media
- Genre:
- Year:2009
- Rating:4 / 5
- Favourites:Add to favourites
- Your mark:
Learning SQL: summary, description and annotation
We offer to read an annotation, description, summary or preface (depends on what the author of the book "Learning SQL" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.
Alan Beaulieu: author's other books
Who wrote Learning SQL? Find out the surname, the name of the author of the book and a list of all author's works by series.
Learning SQL — read online for free the complete book (whole text) full work
Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Learning SQL" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.
Font size:
Interval:
Bookmark:
is an entity-relationship (ER) diagram for the example database used in this book. As the name suggests, the diagram depicts the entities, or tables, in the database along with the foreign-key relationships between the tables. Here are a few tips to help you understand the notation:
Each rectangle represents a table, with the table name above the upper-left corner of the rectangle. The primary-key column(s) are listed first and are separated from nonkey columns by a line. Nonkey columns are listed below the line, and foreign key columns are marked with (FK).
Lines between tables represent foreign key relationships. The markings at either end of the lines represents the allowable quantity, which can be zero (0), one (1), or many (
). For example, if you look at the relationship between the account and product tables, you would say that an account must belong to exactly one product, but a product may have zero, one, or many accounts.
For more information on entity-relationship modeling, please see http://en.wikipedia.org/wiki/Entity-relationship_model.
Figure A-1. ER diagram
Since this book uses the MySQL server for all the examples, I thought it would be useful for readers who are planning to continue using MySQL to include an appendix on MySQLs extensions to the SQL language. This appendix explores some of MySQLs extensions to the select, insert, update, and delete statements that can be very useful in certain situations.
MySQLs implementation of the select statement includes two additional clauses, which are discussed in the following subsections.
In some situations, you may not be interested in all of the rows returned by a query. For example, you might construct a query that returns all of the bank tellers along with the number of accounts opened by each teller. If your reason for executing the query is to determine the top three tellers so that they can receive an award from the bank, then you dont necessarily need to know who came in fourth, fifth, and so on. To help with these types of situations, MySQLs select statement includes the limit clause, which allows you to restrict the number of rows returned by a query.
To demonstrate the utility of the limit clause, I will begin by constructing a query to show the number of accounts opened by each bank teller:
SELECT open_emp_id, COUNT(*) how_many -> FROM account -> GROUP BY open_emp_id;+-------------+----------+| open_emp_id | how_many |+-------------+----------+| 1 | 8 || 10 | 7 || 13 | 3 || 16 | 6 |+-------------+----------+4 rows in set (0.31 sec) The results show that four different tellers opened accounts; if you want to limit the result set to only three records, you can add a limit clause specifying that only three records be returned:
SELECT open_emp_id, COUNT(*) how_many -> FROM account -> GROUP BY open_emp_id -> LIMIT 3;+-------------+----------+| open_emp_id | how_many |+-------------+----------+| 1 | 8 || 10 | 7 || 13 | 3 |+-------------+----------+3 rows in set (0.06 sec) Thanks to the limit clause (the fourth line of the query), the result set now includes exactly three records, and the fourth teller (employee ID 16) has been discarded from the result set.
While the previous query returns three records, theres one small problem; you havent described which three of the four records you are interested in. If you are looking for three specific records, such as the three tellers who opened the most accounts, you will need to use the limit clause in concert with an order by clause, as in:
SELECT open_emp_id, COUNT(*) how_many -> FROM account -> GROUP BY open_emp_id -> ORDER BY how_many DESC -> LIMIT 3;+-------------+----------+| open_emp_id | how_many |+-------------+----------+| 1 | 8 || 10 | 7 || 16 | 6 |+-------------+----------+3 rows in set (0.03 sec) The difference between this query and the previous query is that the limit clause is now being applied to an ordered set, resulting in the three tellers with the most opened accounts being included in the final result set. Unless you are interested in seeing only an arbitrary sample of records, you will generally want to use an order by clause along with a limit clause.
The limit clause is applied after all filtering, grouping, and ordering have occurred, so it will never change the outcome of your select statement other than restricting the number of records returned by the statement .
Instead of finding the top three tellers, lets say your goal is to identify all but the top two tellers (instead of giving awards to top performers, the bank will be sending some of the less-productive tellers to assertiveness training). For these types of situations, the limit clause allows for an optional second parameter; when two parameters are used, the first designates at which record to begin adding records to the final result set, and the second designates how many records to include. When specifying a record by number, remember that MySQL designates the first record as record 0. Therefore, if your goal is to find the third-best performer, you can do the following:
SELECT open_emp_id, COUNT(*) how_many -> FROM account -> GROUP BY open_emp_id -> ORDER BY how_many DESC -> LIMIT 2, 1;+-------------+----------+| open_emp_id | how_many |+-------------+----------+| 16 | 6 |+-------------+----------+1 row in set (0.00 sec) In this example, the zeroth and first records are discarded, and records are included starting at the second record. Since the second parameter in the limit clause is 1, only a single record is included.
If you want to start at the second position and include all the remaining records, you can make the second argument to the limit clause large enough to guarantee that all remaining records are included. If you do not know how many tellers opened new accounts, therefore, you might do something like the following to find all but the top two performers:
SELECT open_emp_id, COUNT(*) how_many -> FROM account -> GROUP BY open_emp_id -> ORDER BY how_many DESC -> LIMIT 2, 999999999;+-------------+----------+| open_emp_id | how_many |+-------------+----------+| 16 | 6 || 13 | 3 |+-------------+----------+2 rows in set (0.00 sec)Font size:
Interval:
Bookmark:
Similar books «Learning SQL»
Look at similar books to Learning SQL. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.
Discussion, reviews of the book Learning SQL and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.