------Vitalie Bancu @vitallybankou

  SELECT

FLOOR(price/10)*10 AS "Category”,

COUNT(*) AS "Count”

FROM titles

GROUP BY FLOOR(price/10)*10;

  */

  

   ------Vitalie Bancu @vitallybankou

 Listing 6.18 List the number of books written (or

cowritten) by each author who has written three

or more books. See Figure 6.18 for the result.

 */ 

 

  SELECT 

    au_id,

    COUNT(*) AS "num_books"

  FROM title_authors

  GROUP BY au_id

  HAVING COUNT(*) >= 3;

  

  

   ------Vitalie Bancu @vitallybankou

 Listing 6.19 List the number of titles and average

revenue for the types with average revenue more

than $1 million. See Figure 6.19 for the result.

 */ 

 

SELECT

    type,

    COUNT(price) AS "COUNT(price)",

    AVG(price * sales) AS "AVG revenue"

  FROM titles

  GROUP BY type

  HAVING AVG(price * sales) > 1000000;

 

  ------Vitalie Bancu @vitallybankou  

Listing 6.20 Listing 6.19 still works without AVG(price

* sales) in the SELECT list. See Figure 6.20 for the

result.  

*/

 

SELECT

    type,

    COUNT(price) AS "COUNT(price)"

  FROM titles

  GROUP BY type

  HAVING AVG(price * sales) > 1000000;

 

 

 

 

SELECT pub_id, SUM(sales) --Faster

FROM titles

WHERE pub_id IN ('P03', 'P04')

GROUP BY pub_id

HAVING SUM(sales) > 10000;

 

 

SELECT pub_id, SUM(sales) --Slower

FROM titles

GROUP BY pub_id

HAVING SUM(sales) > 10000

AND pub_id IN ('P03', 'P04');

 

 

  ------Vitalie Bancu @vitallybankou

Listing 6.21 List the number of books of each type for

each publisher, for publishers with more than one

title of a type. See Figure 6.21 for the result.

*/

SELECT 

    pub_id,

    type,

    COUNT(*) AS "COUNT(*)"

  FROM titles

  GROUP BY pub_id, type

  HAVING COUNT(*) > 1

  ORDER BY pub_id ASC, "COUNT(*)" DESC;

  

  ------Vitalie Bancu @vitallybankou

Listing 6.22 For books from publishers P03 and P04,

list the total sales and average price by type, for types

with more than $10,000 total sales and less than $20

average price. See Figure 6.22 for the result.

*/

 

SELECT

    type,

    SUM(sales) AS "SUM(sales)",

    AVG(price) AS "AVG(price)"

  FROM titles

  WHERE pub_id IN ('P03', 'P04')

  GROUP BY type

  HAVING SUM(sales) > 10000

     AND AVG(price) < 20;

     

     

--15.01.2014

 

  ------Vitalie Bancu @vitallybankou

SELECT columns

FROM table1 join_type table2

ON join_conditions

[WHERE search_condition]

[GROUP BY grouping_columns]

[HAVING search_condition]

[ORDER BY sort_columns];

*/

 

  ------Vitalie Bancu @vitallybankou

SELECT columns

FROM table1, table2

WHERE join_conditions

[GROUP BY grouping_columns]

[HAVING search_condition]

[ORDER BY sort_columns];

*/

 

  ------Vitalie Bancu @vitallybankou

Listing 7.1 Here, the qualified names resolve

otherwise-ambiguous references to the column

city in the tables authors and publishers. See

Figure 7.1 for the result.

*/

SELECT au_id, authors.city

  FROM authors

  INNER JOIN publishers

    ON authors.city = publishers.city;

      

    

    

  SELECT au_id, authors.city

FROM authors, publishers

WHERE authors.city = publishers.city;  

    

 

  ------Vitalie Bancu @vitallybankou

Listing 7.2 Tables aliases make queries shorter and

easier to read. Note that you can use an alias in the

SELECT clause before it’s actually defined later in the

statement. See Figure 7.2 for the result.

*/

 

SELECT au_fname, au_lname, a.city

  FROM authors a

  INNER JOIN publishers p

    ON a.city = p.city;

    

  ------Vitalie Bancu @vitallybankou

Listing 7.3a A join that uses JOIN syntax. See

Figure 7.3 for the result.

*/    

SELECT au_fname, au_lname, a.city

  FROM authors a

  INNER JOIN publishers p

    ON a.city = p.city;