Repository navigation
Expand file tree
/
Copy pathSession-09.sql
More file actions
95 lines (75 loc) · 2.39 KB
/
Copy pathSession-09.sql
File metadata and controls
95 lines (75 loc) · 2.39 KB
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
-- SESSION 9
drop table restaurants ;
-- Create the 'restaurants' table
CREATE TABLE restaurants (
id INT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(50)
);
-- Create the 'dishes' table with a Foreign Key constraint
CREATE TABLE dishes (
id INT PRIMARY KEY,
restaurant_id INT,
dish_name VARCHAR(100),
price DECIMAL(10, 2),
FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
);
-- Insert 3 restaurants
INSERT INTO restaurants (id, name, city) VALUES
(1, 'The Golden Spoon', 'New York'),
(2, 'Sushi Master', 'Tokyo'),
(3, 'La Bella Italia', 'Rome');
-- Insert dishes for each restaurant
INSERT INTO dishes (id, restaurant_id, dish_name, price) VALUES
-- The Golden Spoon
(101, 1, 'Classic Burger', 12.99),
(102, 1, 'Caesar Salad', 9.50),
(103, 1, 'Chocolate Cake', 6.00),
-- Sushi Master
(201, 2, 'Salmon Nigiri', 8.00),
(202, 2, 'Dragon Roll', 14.50),
-- La Bella Italia
(301, 3, 'Margherita Pizza', 11.00),
(302, 3, 'Spaghetti Carbonara', 13.50),
(303, 3, 'Tiramisu', 7.25);
select * from restaurants ;
select * from dishes ;
select r.name , r.city
from restaurants r
inner join dishes d
on r.id = d.restaurant_id ;
select r.name , r.city
from restaurants r
left join dishes d
on r.id = d.restaurant_id ;
SELECT
d.dish_name,
d.price,
r.name AS restaurant_name,
r.city
FROM
restaurants r
RIGHT JOIN
dishes d ON r.id = d.restaurant_id;
show tables;
select * from musicplaylist ;
/*Given this scenario:
You want to show a list of all playlists and the songs inside them,
like Spotify. Explain which JOIN type (INNER, LEFT, or RIGHT)
you would use to show all playlists, even if some are empty, and write the SQL query for it.
Finished
To show all playlists even if they are empty (contain no songs), you must use a LEFT JOIN.
Why LEFT JOIN?
Goal: Keep every row from the playlists table.
Logic: A LEFT JOIN returns all records from the left table (playlists) and the matched records from the right table (songs). If no match is found (an empty playlist), the song columns will simply show NULL.
Alternative: An INNER JOIN would hide empty playlists entirely. A RIGHT JOIN would work only if you swapped the table order, but LEFT JOIN is the standard convention for "Show all X and their related Y".
SQL Query
SELECT
p.playlist_name,
s.song_title,
s.artist
FROM
playlists p
LEFT JOIN
songs s ON p.id = s.playlist_id;
*/