5-Demo-Hello-Again-SQL/finish.sql at master · Amazon-Java-0824/5-Demo-Hello-Again-SQL · 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
USE robot_db;

SELECT *
FROM robots;
SELECT name AS 'Robot Name', price AS Price
FROM robots;


# Aggregation queries

SELECT COUNT(*)
FROM robots;

SELECT AVG(price) AS 'Average Price'
FROM robots;

SELECT MIN(price) AS 'Minimum Price'
FROM robots;
SELECT MAX(price) AS 'Maximum Price'
FROM robots;

INSERT INTO robots (id, name, model, category, price, weight, battery_capacity)
VALUES (6, 'RoboProgrammer', 'RM-113', 'Security', 999.99, 5.5, 5000);


SELECT category
FROM robots
GROUP BY category;

SELECT category, COUNT(*)
FROM robots
GROUP BY category;

SELECT category, COUNT(*), AVG(price)
FROM robots
WHERE price > 800
GROUP BY category
HAVING COUNT(*) > 1;


SELECT *
FROM robots
WHERE category = 'Security';

SELECT *
FROM robots
WHERE category = 'Security'
  AND price > 2000;
SELECT *
FROM robots
WHERE category = 'Security'
   OR category = 'Household';

SELECT *
FROM robots
WHERE category IN ('Security', 'Household');


SELECT *
FROM robots;

SELECT *
FROM robots
WHERE category LIKE '%n%';

SELECT *
FROM robots
WHERE model LIKE 'R_-_00';


SELECT *
FROM robots
WHERE price BETWEEN 1000 AND 4000;


SELECT *
FROM robots
ORDER BY weight;

SELECT DISTINCT category
FROM robots;


SELECT *
FROM components;
SELECT *
FROM robots;

SELECT *
FROM components
         LEFT JOIN robots ON components.robot_id = robots.id;

SELECT m.maintenance_date, r.name, m.description
FROM maintenance_logs m
         LEFT JOIN robots r ON m.robot_id = r.id
ORDER BY m.maintenance_date DESC;