ch11answers.pks 1.71 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
/**
problem 1

Calculate the average value, the maximum value, and the minimum value for one of the columns in the JustLee Books Database using the AVG, MAX and MIN group functions.
*/

SELECT MAX(COST), MIN(COST), AVG(COST) FROM BOOKS;

/**
problem 2

Use the COUNT function to count non-NULL values first and then to count the total number of records in one of the tables in JustLee Books Database.
*/

SELECT COUNT(*) FROM BOOKS;

SELECT COUNT(*) FROM BOOKS WHERE NOT DISCOUNT IS NULL;

/**
problem 3

Write an SQL query that uses the GROUP BY, WHERE and HAVING statements. Explain what the query is supposed to do
*/

SELECT COUNT(*), CATEGORY FROM BOOKS WHERE COST > 10 GROUP BY CATEGORY HAVING CATEGORY <> 'SELF HELP' AND COUNT(*) >= 2;

-- Finds categories of books that have 2 or more books that cost more than $10

/**
problem 4

Write an SQL query that uses the GROUP BY ROLL statement. Explain what the query is supposed to do.
*/

SELECT TITLE, CATEGORY, SUM(PAIDEACH*QUANTITY) REVENUE, SUM(QUANTITY) SALES, AVG(RETAIL) AS COST_PER_BOOK FROM ORDERS JOIN ORDERITEMS USING(ORDER#) JOIN BOOKS USING (ISBN) GROUP BY ROLLUP(CATEGORY, TITLE) ORDER BY CATEGORY, TITLE;

-- shows the total revenue made from each book, the number of books sold, and the cost per book. Then calculates the revenue made for each category, the number of books sold in a category and the total revenue made.

/**
problem 5

Write an SQL query that uses the GROUP BY CUBE statement. Explain what the query is supposed to do.
*/


SELECT  CUSTOMER#, TITLE, SUM(QUANTITY) FROM CUSTOMERS JOIN ORDERS USING (CUSTOMER#) JOIN ORDERITEMS USING (ORDER#) JOIN BOOKS USING (ISBN) GROUP BY CUBE(CUSTOMER#, TITLE);

-- items this customer ordered and customers who ordered each item