mysql/Lab7.sql at master · tgfbikes/mysql · GitHub

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
use books;

# What is the average price of books

SELECT
	ROUND(AVG(price),2) AS "Average Price"
FROM
	titles;


# How many books are in the table

SELECT
	COUNT(title_id) AS 'Qty'
FROM
	titles;


# What is the total sales volume (price * sales) of the books

SELECT
	SUM(price)*SUM(sales) AS 'Volume'
FROM
	titles;


# What is the average number of pages by book type

SELECT
	type,
	ROUND(AVG(pages),0) AS 'Average Pages'
FROM
	titles
GROUP BY type;


# Display all book types and the average number of pages if the average is greater than 500

SELECT
	type,
	ROUND(AVG(pages),0) AS 'Average Pages'
FROM
	titles
GROUP BY type
HAVING
	AVG(pages) > 500;


# Same as above but sort by average number of pages

SELECT
	type,
	ROUND(AVG(pages),0) AS 'Average Pages'
FROM
	titles
GROUP BY type
HAVING
	AVG(pages) > 500
ORDER BY pages ASC;


# How many states are in the authors table

SELECT
	COUNT(DISTINCT state) AS 'Number of States'
FROM
	authors;


# Subtotal the number of authors from each state

SELECT
	state,
	COUNT(*) AS '# of Authors'
FROM
	authors
GROUP BY state;


# Subtotal the number of books in each type except children

SELECT
	type,
	COUNT(*) AS '# of Books'
FROM
	titles
WHERE
	type <> 'children'
GROUP BY type;

# Same as above but having count greater than 1

SELECT
	type,
	COUNT(*) AS '# of Books'
FROM
	titles
WHERE
	type <> 'children'
GROUP BY type
HAVING COUNT(*) > 1;