7YGk5VJy

1use nkamm;
2
3drop table if exists VehicleOwners;
4drop table if exists Vehicle;
5drop table if exists Owner;
6
7
8create table VehicleOwners(
9	vin int,
10    vehicle_make varchar(50),
11    vehicle_model varchar(50),
12    owner_id int);
13    
14create table Vehicle(
15	make varchar(50),
16    model varchar(50));
17    
18create table Owner(
19	owner_id int,
20    name varchar(50));
21    
22insert into VehicleOwners(vin, vehicle_make, vehicle_model, owner_id) values
23	(1, 'Ford', 'Focus Electric', 20),
24    (2, 'Tesla', 'Model S', 20),
25    (3, 'Chevy', 'Bolt', 20),
26    (4, 'Chevy', 'Bolt', 21);
27    
28insert into Vehicle(make, model) values
29	('Ford', 'Focus Electric'),
30    ('Tesla', 'Model S'),
31    ('Chevy', 'Bolt');
32    
33insert into Owner(owner_id, name) values
34	(20, 'Henry Ford'),
35    (21, 'Elon Musk'),
36    (22, 'Louis Chevrolet');
37    
38    
39SELECT * FROM Vehicle v 
40LEFT JOIN VehicleOwners vo ON v.make = vo.vehicle_make AND v.model = vo.vehicle_model
41LEFT JOIN Owner o ON o.owner_id = vo.owner_id
42ORDER BY v.make, v.model;
43
44SELECT o.name FROM Owner o 
45WHERE EXISTS (SELECT * FROM VehicleOwners vo WHERE vo.owner_id=o.owner_id);
46    
47SELECT o.name FROM Owner o 
48WHERE o.owner_id NOT IN (SELECT owner_id FROM VehicleOwners);
49    
50select * from VehicleOwners vo left join Owner o on o.owner_id = vo.owner_id;
51    
52SELECT a.name FROM (SELECT o.* FROM Vehicle v 
53JOIN VehicleOwners vo ON v.make = vo.vehicle_make AND v.model = vo.vehicle_make
54RIGHT JOIN Owner o ON o.owner_id = vo.owner_id) a;
55
56SELECT o.name, count(vo.vin) 
57FROM Owner o LEFT JOIN 
58VehicleOwners vo ON o.owner_id = vo.owner_id ORDER BY o.name;
59
60SELECT o.name, count(vo.vin)
61 FROM Owner o LEFT JOIN
62 VehicleOwners vo ON o.owner_id = vo.owner_id GROUP BY o.name;
63    
64SELECT v.make, v.model, count(vo.vin) FROM Vehicle v 
65LEFT JOIN VehicleOwners vo ON v.make = vo.vehicle_make AND v.model = vo.vehicle_model
66LEFT JOIN Owner o ON o.owner_id = vo.owner_id
67group BY v.make, v.model;
68
69SELECT a.* FROM (SELECT o.* FROM Vehicle v 
70JOIN VehicleOwners vo ON v.make = vo.vehicle_make AND v.model = vo.vehicle_model
71RIGHT JOIN Owner o ON o.owner_id = vo.owner_id) a;