Basic-SQL-Queries/MySQL Code at main · MasegoMM/Basic-SQL-Queries · 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
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
CREATE DATABASE IF NOT EXISTS RGI_Truck;
USE RGI_Truck;

CREATE TABLE Driver (
Driver_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR (50),
Surname VARCHAR (50),
Licence_type INT,
Truck_ID VARCHAR (20),
Route_ID VARCHAR (10),
FOREIGN KEY  (Licence_type) REFERENCES Licence(Licence_type),
FOREIGN KEY  (Truck_ID) REFERENCES Truck(Truck_ID),
FOREIGN KEY  (Route_ID) REFERENCES Route(Route_ID)
);

/# table for licence #/
CREATE TABLE Licence (
Licence_type INT PRIMARY KEY,
Description VARCHAR(50)
);

/# table for Truck #/
CREATE TABLE Truck (
Truck_ID VARCHAR (20) PRIMARY KEY,
Truck_desc VARCHAR (100)
);

/# table for route #/
CREATE TABLE Route (
Route_ID VARCHAR (10) PRIMARY KEY
);

/# table for employee supertype #/
CREATE TABLE Employee (
    Employee_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Surname VARCHAR(50)
);

/# subtyppes #/
CREATE TABLE Dispatcher (
    Employee_ID INT PRIMARY KEY,
    FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);

CREATE TABLE FirstResponder (
    Employee_ID INT PRIMARY KEY,
    FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);

CREATE TABLE Mechanic (
    Employee_ID INT PRIMARY KEY,
    FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);

CREATE TABLE Clerk (
    Employee_ID INT PRIMARY KEY,
    FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);


/# Maintenace Query #/
CREATE TABLE Maintenance_Query (
    Query_ID INT PRIMARY KEY,
    Clerk_ID INT,
    Mechanic_ID INT,
    Truck_ID VARCHAR(20),
    FOREIGN KEY (Clerk_ID) REFERENCES Clerk(Employee_ID),
    FOREIGN KEY (Mechanic_ID) REFERENCES Mechanic(Employee_ID),
    FOREIGN KEY (Truck_ID) REFERENCES Truck(Truck_ID)
);

/# Add the data into the table #/
/# insert into licence table #/
INSERT INTO Licence VALUES
(1, "Class A"),
(2, "Class B"),
(3, "Class C");

/# Insert into Truck table #/
INSERT INTO Truck VALUES
("FG 87 FB ZN", "Purple with
yellow trim"),
("FF 88 HH ZN", "Blue"),
("DDD ZN", "Northen
Blue"),
("KING S ZN", "Gold with
gold rims"),
("LITTLE ZN", "Pink with
mocking
jay hood
ornamen"),
("1 MAN ZN", "White "),
("HY PP FG ZN", "Red "),
("HOLD ZN", "Black");


/# Insert into Route Tale #/
INSERT INTO Route VALUES
('N3'), ('M4'), ('N2'), ('M16'),
('N1'), ('N7'), ('M27'), ('N4');

/#Insert into Driver Table #/
INSERT INTO Driver VALUES
('RGI-D25', 'John', 'Snow', 2, 'FG 87 FB ZN', 'N3'),
('RGI-F45', 'Aegon', 'Targaryen', 1, 'FF 88 HH ZN', 'M4'),
('RGI-G56', 'Rob', 'Stark', 1, 'DDD ZN', 'N2'),
('RGI-F70', 'Jamie', 'Lannister', 1, 'KING S ZN', 'M16'),
('RGI-Z56', 'Petyr', 'Baelish', 3, 'LITTLE ZN', 'N1'),
('RGI-Q34', 'Jaqen', 'H’ghar', 1, '1 MAN ZN', 'N7'),
('RGI-P90', 'Theon', 'Greyjoy', 3, 'HY PP FG ZN', 'M27'),
('RGI-T89', 'Hodor', 'Hodor', 2, 'HOLD ZN', 'N4');

/# Insert my record #/
INSERT INTO Driver VALUES
("RGI-DBS", "Masego", "Moatshe", 1, "GP 12 NW ZI", "N2");

SELECT
    d.Driver_ID,
    d.Name AS Driver_Name,
    d.Surname AS Driver_Surname,
    l.Description AS Licence_Type,
    t.Truck_ID,
    t.Truck_Desc,
    r.Route_ID AS Route
FROM Driver d
JOIN Licence l ON d.Licence_Type = l.Licence_Type
JOIN Truck t ON d.Truck_ID = t.Truck_ID
JOIN Route r ON d.Route_ID = r.Route_ID;