-
Notifications
You must be signed in to change notification settings - Fork 0
/
projectOne
169 lines (149 loc) · 7.45 KB
/
projectOne
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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
2. select employeeNumber, lastName,firstName, extension,officeCode
from employees where officeCode = 4;
+----------------+-----------+-----------+-----------+------------+
| employeeNumber | lastName | firstName | extension | officeCode |
+----------------+-----------+-----------+-----------+------------+
| 1102 | Bondur | Gerard | x5408 | 4 |
| 1337 | Bondur | Loui | x6493 | 4 |
| 1370 | Hernandez | Gerard | x2028 | 4 |
| 1401 | Castillo | Pamela | x2759 | 4 |
| 1702 | Gerard | Martin | x2312 | 4 |
+----------------+-----------+-----------+-----------+------------+
5 rows in set (0.00 sec)
4. select productCode,productName,productVendor, buyPrice, MSRP
from products where msrp=(select min(MSRP) from products);
+-------------+-----------------------------+-------------------------+----------+-------+
| productCode | productName | productVendor | buyPrice | MSRP |
+-------------+-----------------------------+-------------------------+----------+-------+
| S24_1937 | 1939 Chevrolet Deluxe Coupe | Motor City Art Classics | 22.57 | 33.19 |
+-------------+-----------------------------+-------------------------+----------+-------+
1 row in set (0.01 sec)
6. select country, count(customerNumber) as num
from customers group by country having num >= 5 order by 2 desc;
+-----------+-----+
| country | num |
+-----------+-----+
| USA | 36 |
| Germany | 13 |
| France | 12 |
| Spain | 7 |
| UK | 5 |
| Australia | 5 |
+-----------+-----+
6 rows in set (0.01 sec)
8. select employeeNumber, concat(lastName,', ', firstName) as "Employees that report to Anthony Bow"
from employees where reportsTo = 1143;
+----------------+--------------------------------------+
| employeeNumber | Employees that report to Anthony Bow |
+----------------+--------------------------------------+
| 1165 | Jennings, Leslie |
| 1166 | Thompson, Leslie |
| 1188 | Firrelli, Julie |
| 1216 | Patterson, Steve |
| 1286 | Tseng, Foon Yue |
| 1323 | Vanauf, George |
+----------------+--------------------------------------+
6 rows in set (0.00 sec)
10.
#first create table
create table car(
caryear int,
productCode varchar(8)
);
#add year value
insert into car(carYear,productCode)
select substring_index(productName,' ',1) ,productCode from products
where productLine = 'Classic Cars';
#select only 1950's classic car
select p.productName ,caryear
from products p, car c where (c.productCode = p.productCode)
group by p.productName having (caryear > '1950' and caryear <'1960');
+-------------------------------------+---------+
| productName | caryear |
+-------------------------------------+---------+
| 1952 Alpine Renault 1300 | 1952 |
| 1952 Citroen-15CV | 1952 |
| 1956 Porsche 356A Coupe | 1956 |
| 1957 Corvette Convertible | 1957 |
| 1957 Ford Thunderbird | 1957 |
| 1958 Chevy Corvette Limited Edition | 1958 |
+-------------------------------------+---------+
6 rows in set (0.00 sec)
12. select firstName, lastName,jobtitle, employeeNumber
from employees e where employeeNumber not in
(select salesRepEmployeeNumber
from customers c
where c.salesRepEmployeeNumber = e.employeeNumber)and jobtitle="Sales Rep";
+-----------+----------+-----------+----------------+
| firstName | lastName | jobtitle | employeeNumber |
+-----------+----------+-----------+----------------+
| Tom | King | Sales Rep | 1619 |
| Yoshimi | Kato | Sales Rep | 1625 |
+-----------+----------+-----------+----------------+
2 rows in set (0.00 sec)
14. select customername, sum(quantityordered) as totalq
from customers c, orderdetails d, orders o where c.customernumber = o.customernumber and o.ordernumber = d.ordernumber
group by customername having totalq > 2000;
+------------------------------+--------+
| customername | totalq |
+------------------------------+--------+
| Euro+ Shopping Channel | 9327 |
| Mini Gifts Distributors Ltd. | 6366 |
+------------------------------+--------+
2 rows in set (0.01 sec)
16. insert into TopCustomers ( customerNumber , contactDate,orderTotal)
select customerNumber,'2018/10/20', sum(quantityOrdered * priceEach) as totalPrice
from orderDetails d, orders o where d.orderNumber = o.orderNumber
group by customerNumber having totalPrice > 150000;
+----------------+-------------+------------+
| customerNumber | contactDate | orderTotal |
+----------------+-------------+------------+
| 114 | 2018-10-20 | 180585.07 |
| 119 | 2018-10-20 | 158573.12 |
| 124 | 2018-10-20 | 591827.34 |
| 141 | 2018-10-20 | 820689.54 |
| 148 | 2018-10-20 | 156251.03 |
| 151 | 2018-10-20 | 177913.95 |
| 323 | 2018-10-20 | 154622.08 |
+----------------+-------------+------------+
7 rows in set (0.00 sec)
18. alter table TopCustomers add OrderCount int;
+----------------+-------------+------------+------------+
| customerNumber | contactDate | orderTotal | OrderCount |
+----------------+-------------+------------+------------+
| 114 | 2018-10-20 | 180585.07 | NULL |
| 119 | 2018-10-20 | 158573.12 | NULL |
| 124 | 2018-10-20 | 591827.34 | NULL |
| 141 | 2018-10-20 | 820689.54 | NULL |
| 148 | 2018-10-20 | 156251.03 | NULL |
| 151 | 2018-10-20 | 177913.95 | NULL |
| 323 | 2018-10-20 | 154622.08 | NULL |
+----------------+-------------+------------+------------+
7 rows in set (0.00 sec)
19. update TopCustomers set OrderCount=(round((rand()*19),0)) where OrderCount is null;
+----------------+-------------+------------+------------+
| customerNumber | contactDate | orderTotal | OrderCount |
+----------------+-------------+------------+------------+
| 114 | 2018-10-20 | 180585.07 | 6 |
| 119 | 2018-10-20 | 158573.12 | 2 |
| 124 | 2018-10-20 | 591827.34 | 13 |
| 141 | 2018-10-20 | 820689.54 | 18 |
| 148 | 2018-10-20 | 156251.03 | 14 |
| 151 | 2018-10-20 | 177913.95 | 16 |
| 323 | 2018-10-20 | 154622.08 | 18 |
+----------------+-------------+------------+------------+
7 rows in set (0.00 sec)
20. select * from TopCustomers order by OrderCount desc;
+----------------+-------------+------------+------------+
| customerNumber | contactDate | orderTotal | OrderCount |
+----------------+-------------+------------+------------+
| 141 | 2018-10-20 | 820689.54 | 18 |
| 323 | 2018-10-20 | 154622.08 | 18 |
| 151 | 2018-10-20 | 177913.95 | 16 |
| 148 | 2018-10-20 | 156251.03 | 14 |
| 124 | 2018-10-20 | 591827.34 | 13 |
| 114 | 2018-10-20 | 180585.07 | 6 |
| 119 | 2018-10-20 | 158573.12 | 2 |
+----------------+-------------+------------+------------+
7 rows in set (0.01 sec)
21. drop table TopCustomers;