-
Notifications
You must be signed in to change notification settings - Fork 0
/
questions-solutions.sql
122 lines (106 loc) · 4.87 KB
/
questions-solutions.sql
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
-- Comments: "-- " and "/*___*/"
USE spj; -- "use" is used to select database.
-- ==== Query questions ==== --
-- 1. Get Full details of all projects.
SELECT * FROM project; -- "select" query is used to select or fetch data from the particular database.table
-- 2. Get Full details of all projects in London.
SELECT * FROM project
WHERE CITY="LONDON"; -- "where" is used to filter records returned by select.
-- 3. Get supplier numbers for suppliers who supply projects J1.
SELECT SNO FROM shipment
WHERE JNO="J1";
-- 4. Get all shipments where the quantity is in the range 300 to 750 inclusive.
SELECT * FROM shipment
WHERE QTY BETWEEN 300 AND 750; -- "between" operator selects values within a given range.
-- AND, OR, and NOT are logical operators
/* 5. Get all part-color/part-city pairs.
Note : Here and subsequently, the terms “all” means “all currently represented in the database, “ not “all possible”
*/
SELECT CONCAT_WS("-", PNAME, COLOR) AS "part-color",
CONCAT_WS("-", PNAME, CITY) AS "part-city" FROM part;
-- or
SELECT CONCAT_WS("\t",
CONCAT(PNAME, "-", COLOR),
CONCAT(PNAME, "-", CITY)
) AS columnName FROM part;
-- "concat" function is used concatenate strings (outputs)
-- "concat_ws" function is used to concatenate strings (outputs) with a specified character.
-- "as" is used to give alias (a temporary name)
/* 6 Get all supplier -number/part- number/project- number triples such that the indicated supplier, part and
project are all collocated (i.e. all in the same city). output: https://prnt.sc/RZkLL_qFr9_U */
-- for above we have to specify a inner join as we want the same/common data from different tables.
SELECT CONCAT_WS("->", s.SNO, p.PNO, j.JNO) AS columnName, s.CITY
FROM supplier AS s
JOIN part AS p
JOIN project AS j ON p.CITY = j.CITY AND j.CITY = s.CITY;
-- OR
SELECT CONCAT_WS("->", s.SNO, p.PNO, j.JNO) AS columnName,
s.CITY AS supplierCity,
p.CITY AS partCity,
j.CITY AS projectCity
FROM supplier AS s,
part AS p,
project AS j
WHERE s.CITY=p.CITY AND p.CITY=j.CITY AND s.CITY = j.CITY;
-- A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
/* 7. Get all supplier -number/part- number/project- number triples such that the indicated supplier, part
and project are not all collocated. output: https://prnt.sc/FVrRIegH5gLh */
SELECT CONCAT_WS("->", s.SNO, p.PNO, j.JNO) AS columnName,
s.CITY AS supplierCity,
p.CITY AS partCity,
j.CITY AS projectCity
FROM supplier AS s,
part AS p,
project AS j
WHERE NOT (s.CITY=p.CITY AND p.CITY=j.CITY AND s.CITY = j.CITY);
-- OR
SELECT CONCAT_WS(" -> ", s.SNO, p.PNO, j.JNO) AS columnName,
s.CITY AS supplierCity,
p.CITY AS partCity,
j.CITY AS projectCity
FROM supplier AS s
JOIN part AS p
JOIN project AS j ON NOT (p.CITY = j.CITY AND j.CITY = s.CITY AND s.CITY = j.CITY);
/* 8. Get all supplier -number/part- number/project- number triples such that no two of the indicated
supplier, part and project are collocated. */
SELECT CONCAT_WS(" -> ", s.SNO, p.PNO, j.JNO) AS columnName,
s.CITY AS supplierCity,
p.CITY AS partCity,
j.CITY AS projectCity
FROM supplier AS s
JOIN part AS p
JOIN project j ON NOT s.CITY = p.CITY AND NOT p.CITY = j.CITY AND NOT s.CITY = j.CITY;
-- OR
SELECT CONCAT_WS(" -> ", s.SNO, p.PNO, j.JNO) AS columnName,
s.CITY AS supplierCity,
p.CITY AS partCity,
j.CITY AS projectCity
FROM supplier AS s,
part AS p,
project j
WHERE NOT s.CITY = p.CITY AND NOT p.CITY = j.CITY AND NOT s.CITY = j.CITY;
-- 9. Get full details for parts supplied by the supplier in the London.
SELECT * FROM part WHERE CITY="London";
-- 10. Get part numbers for parts supplied by a supplier in London to a project in London.
SELECT DISTINCT part.PNO-- , part.CITY, p.CITY
FROM part
LEFT JOIN project p on part.CITY = p.CITY WHERE p.CITY="LONDON"
ORDER BY part.PNO;
-- DISTINCT keyword is used to filter the repeated data rows
-- ORDER BY is used sort data in ascending or descending with respect to specified column. default is ASC, can use DESC.
-- 11. Get all pairs of city names such that a supplier in the first city supplies a project in the second city.
-- 15. Get the total number of projects supplied by supplier S1.
SELECT SNO, COUNT(JNO) AS totalNumProjects
FROM shipment
WHERE SNO="S1"
GROUP BY SNO;
-- COUNT() function returns the number of records returned by a select query
-- The GROUP BY statement groups rows that have the same values into summary rows, often used with aggregate functions
-- to group the result-set by one or more columns.
-- 16. Get the total quantity of part P1 supplied by supplier S1.
SELECT SNO, PNO, SUM(QTY) totalQuantity
FROM shipment
WHERE SNO="S1" AND PNO="P1"
GROUP BY SNO;
-- SUM function is used sum the values of select query
--