Database Design and SQL

SQL tutorial


Previous page Previous     TOC Contents     Next pageNext


Lesson 9 - Advanced queries



A new case study - The Editor Project

This is one that's been around, in the public domain as far as I know, for many years. It's been used to teach SQL for the last 25 years, at least. I converted it to run in MySQL.





You can download the new database, the MySQL version, from the Downloads area.

You've got Authors. Authors write books. Sometimes, a book is written by several authors, each of whom will receive a percentage of the royalties. Some authors have written several books; others have yet to write any (we may have a file on them because they're in the process of writing and we gave them an advance). So, the relationship between Authors and Books is many-to-many.

The BookAuthor table is the linking table between authors and books.

Publishers are companies that print and distribute the books. We'll get to that relationship later.

P.S. You'll notice that the last column in most tables has a funny sign at the end of the data. That's a carry-over of a CRLF because the table was imported from Access. It would be better to modify those because some queries will not work properly.

As an exercice in SQL, use an Update command to change them, as in this example:



If you're not sure how that works, look-up the Update syntax in the Query browser.

The % sign is the widcard character in MySQL, like the * in Access.



THE UNION QUERY

You use the UNION query when you want to combine similar columns from two tables that are not related - that are not joined directly.

Show DOB of all students and all professors in a single query. Both tables may be joined eventually through Registrations and so on but they don't have a common field so they can't be joined directly.

SELECT StudentName, StudentDOB FROM Students
UNION
SELECT ProfName, ProfDOB FROM Profs;




The UNION query produces rows for the two queries combined.

The columns specified in the two queries must be of the same type - that is, the first column is Text in both, the second is Date in both and so on. However, field size doesn't matter.

Column names in the query result will be those of the first query - in our example, StudentName and StudentDOB; if you want to modify the names, use the AS operator

SELECT StudentName AS Name, StudentDOB AS BirthDate FROM Students
UNION
SELECT ProfName, ProfDOB FROM Profs;


The technique has an interesting application when you need to produce totals at the end of a query.

SELECT Str(CourseNumber) AS Course, CourseName,
         ClassHours AS Class, LabHours AS Lab
FROM Courses
UNION
SELECT 'Totals:', ' ', SUM(ClassHours), SUM(LabHours)
FROM Courses;


Important points to note:
  • You can display constants in a query; we haven't done it before but it is not unusual; in this case, 'Totals:' and '  ' are constants.

  • The two SELECT statements must have the same number of columns; that's why the second SELECT has a '  ' column; it makes the third and fourth columns match.

  • The data type of the columns in the two statements must be the same; since CourseNumber is defined as Integer in the table, it must be converted to a String type to display it in the same column as the String constant 'Totals:'; the STR( ) function converts a Numeric data type to a String data type.



For the rest of this lesson we'll go back to the BookStor database. If you study the Queries included in that application, you'll see that the SELECT statement can sometimes get a lot more complicated than what was suggested in the previous lessons!

Let's use the table 'Authors' to build a UNION query:

SELECT au_fname,au_lname,au_state, au_salary
FROM Authors
WHERE au_state = 'KS'
UNION
SELECT 'Average salary:', ' ', ' ', AVG(au_salary)
FROM Authors
WHERE au_state = 'KS';





Whoops! One thing I forgot to emphasize when I said earlier that it combines the queries: the UNION query automatically combines the rows from the two SELECT statements alphabetically on the first column, which is why we get our Average after the A names.

To correct, use a prefix on the final row to make sure it ends up at the end; z or zz usually does it.

SELECT au_fname,au_lname,au_state, au_salary
FROM Authors
WHERE au_state = 'KS'
UNION
SELECT 'z_Average salary:', ' ', ' ', AVG(au_salary)
FROM Authors
WHERE au_state = 'KS';





Top






THE CROSSTAB QUERY

The crosstab (Xtab) query is an efficient way of displaying table data based on two different criteria:

  • How many authors grouped by state and sex?

  • How many authors grouped by sex and subject?

  • What's the total salary grouped by subject and state?

From the BookStore database:
How many authors of each sex are there in each state?

This can be answered with a query using GROUP BY like this:

SELECT au_state, au_sex, COUNT(au_ID) AS [Number]
            FROM authors
            GROUP BY au_state, au_sex
            ORDER BY 2;


From which you get this:

           

which, you have to admit is no great shakes because it is hard to read and not clear in its meaning.



It would be a lot better to see it this way:



Here is the SQL code that produced this query:

SELECT allstate.au_state,
(SELECT count(*) FROM authors WHERE allstate.au_state = authors.au_state AND au_sex = 'M') AS Male,
(SELECT count(*) FROM authors WHERE allstate.au_state = authors.au_state AND au_sex = 'F') AS Female,
(SELECT count(*) FROM authors WHERE allstate.au_state = authors.au_state AND au_sex = 'U') AS Unknown
FROM Allstate;


This will require a few explanations.

Before writing the query, I decide which columns I will want to group the data on. Then I create a table for each of those groups.

For example, here, I know that I will group on sex, state and subject. I therefore create a table for each of those groups using a technique called the create table query which extracts data from the Authors table and creates another table with it.

SELECT DISTINCT [authors].[au_sex] INTO AllSex
FROM authors;


SELECT DISTINCT [authors].[au_state] INTO AllState
FROM authors;


SELECT DISTINCT [authors].[au_subject] INTO AllSubject
FROM authors;




Note that the select_list can include sub-queries and that is what makes it so useful.

Let's look at another example, using the group tables again.

Show average salaries grouped by subject and by state.

SELECT AllState.au_state AS State,
(SELECT AVG(au_salary) FROM authors WHERE allstate.au_state = authors.au_state AND au_subject = 'Action') AS Action,
(SELECT AVG(au_salary) FROM authors WHERE allstate.au_state = authors.au_state AND au_subject = 'Romance') AS Romance,
(SELECT AVG(au_salary) FROM authors WHERE allstate.au_state = authors.au_state AND au_subject = 'Sci-Fi') AS Sci-Fi,
(SELECT AVG(au_salary) FROM authors WHERE allstate.au_state = authors.au_state AND au_subject = 'Travel') AS Travel
FROM Allstate;





And you can also put all the techniques together to produce totals on columns as well as rows. Look up the code for this one in the BookStor database, Query Sheet3B:












If you haven't found the database resource you're looking for,
use our Google Search box for more information.

Google






Top



Home | Tutorials | Contact | Sitemap | Add URL | Privacy policy