THE CODE BELOW IS dbcreate.sql, THE COPIABLE SQL CODE (ALL THE GIVEN TABLES), dbdrop.sql JUST DROPS THE TABLES FROM THE DATABASE (TO REMOVE TBALES FROM A PREVIOUS QUESTION AND PREVENT REPLICATION ERRORS). solution3.sql JUST HAS THE ABOVE QUESTIONS ON IT, NO ACTUAL CODE. AS SUCH, THE CODE BELOW IS THE ONLY CODE GIVEN AND NEEDED TO ANSWER THE QUESTIONS. Thankyou.

Task 3 (10 marks) An objective of this task is to implement data retrieval, data manipulation and data definition statements of SQL. In this task we use the sample database available for the subject and used in the coursework tasks. Before you start implementation of this task, download the files dbcreate.sql and dbdrop.sql. Next, process a script file dbdrop.sql and immediately after that the scripts abcreate.sql to refresh a sample database. No report is expected from processing of the scripts dbdrop.sql and dbcreate.sql. Download a file solution3.sql and insert into the file the implementations of the following actions. (1) Find the first and the last names of drivers and mechanics, such that the drivers used at least one time a truck with a registration PKR768 and the mechanics provided their date of birth. (2) Find the registration numbers of trucks and years when the trucks had been manufactured for all trucks used by at least one driver with a driving licence number B2. (3) Find the total number of employees who are not drivers. Assume that there exist employees who are not drivers and who are not mechanics and who are not administration people. (4) Find how many times each truck has been used for the trips. List a truck registration number and the total number of times a truck has been used for the trips. Include the truck registration numbers of all trucks that have not been used for the trips so far with the total number of trips equal to zero. (5) Find the names of departure locations such that at least 5 legs of any trips started in each location. (6) Modify a structure of the database such that after the modification it would be possible to add information about repairs performed by each mechanic. A repair is described by a date when it was performed, short explanations, and mechanic involved. Assume that one mechanic can work on only one repair per day. (7) Modify a structure of the database such that after the modification it would be possible to add information about the total number of trips performed by each driver. Then, update the database, such that it contains information about the total number of trips performed by each driver. (8) Delete from a database information about the drivers who performed more than 3 trips. It is recommended to decompose the complex queries into the sequences of subqueries and use SELECT statement with WITH clause for implementation of subqueries. The correct implementations of the subtasks from (1) to (6) are worth 1 mark each and the solutions of the tasks (7) and (8) are worth 2 marks each. NULL, CREATE TABLE EMPLOYEE ENUM DECIMAL(12) NOT NULL, /* Employee number FNAME VARCHAR(50) NOT NULL, /* First name INITIALS VARCHAR(5) NULL, /* Initials LNAME VARCHAR(50) NOT NULL, /* Last name DOB DATE /* Date of birth BLDG DECIMAL (3) NOT NULL /* Building number STREET VARCHAR(50) NOT NULL, /* Street name SUBURB VARCHAR(50) NOT NULL, /* Suburb name STATE VARCHAR(30) NOT NULL, /* State ZIPCODE DECIMAL (4) NOT NULL, /* Zip code SALARY DECIMAL (9,2) NOT NULL, /* Salary CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM), CONSTRAINT EMPLOEE_CHECK1 CHECK (DOB AND BLDG O AND SALARY = 0 AND YEXP = 0 AND YEXP = 1999 AND YMANUF AND CAPACITY =5 AND SEATS = DEPARTURE_DATE ) ); INSERT INTO TRIP VALUES( 1, 19001, 'PKR768', 2019-01-12', '2019-01-17' ); INSERT INTO TRIP VALUES( 2, 10001, 'SYF777' 2019-02-20', '2019-02-22' ); INSERT INTO TRIP VALUES( 3, 10001, 'AB0804 2019-03-12', '2019-03-17' ); INSERT INTO TRIP VALUES( 4, 10001, 'PKR768 2019-06-29', '2019-07-06' ); INSERT INTO TRIP VALUES( 5, 20002, 'PKR768 2019-01-12', '2019-01-17' ); INSERT INTO TRIP VALUES( 6, 10002, 'SYF777'. 2019-02-20', '2019-02-25' ); INSERT INTO TRIP VALUES( 7, 30005, 'KKK007 2019-03-12', '2019-03-26' ); INSERT INTO TRIP VALUES( 8, 10001, 'AB0804', 2019-01-12', '2019-01-17' ); INSERT INTO TRIP VALUES( 9, 10002, "QRT834', 2019-09-17', '2019-09-19' ); INSERT INTO TRIP VALUES(10, 30005, 'KKK007 2019-12-15', '2019-12-25' ); INSERT INTO TRIP VALUES(11, 10003, 'SST005 2018-01-23', '2018-01-29' ); INSERT INTO TRIP VALUES(12, 10002, 'PKR768 2018-03-12', '2018-03-17' ); INSERT INTO TRIP VALUES(13, 20002, 'QRT834', 2019-04-23', '2019-04-26' ); INSERT INTO TRIP VALUES (14, 20002, 'PKR008 2019-04-23', 2019-04-29' ); INSERT INTO TRIP VALUES (15, 30005, 'PKR768', 2019-05-24', '2019-05-29' ); INSERT INTO TRIP VALUES(16, 30005, 'SST005', '2017-08-02 2017-08-17' ); INSERT INTO TRIP VALUES(17, 20002, 'ORT834', '2017-09-17', '2017-09-22' ); INSERT INTO TRIP VALUES (18, 10001, 'KKK007 2017-12-15', '2019-12-17' ); INSERT INTO TRIP VALUES (19, 30005, 'SST005 2018-01-23', '2018-01-28' ); INSERT INTO TRIP VALUES (20, 10003, 'PKR768 '2018-03-12 '2018-03-17' ); INSERT INTO TRIP VALUES (21, 10001, 'QRT834', 2016-04-23', '2016-04-28' ); INSERT INTO TRIP VALUES (22, 30005, 'PKR008 2016-04-23', '2016-04-26' ); INSERT INTO TRIP VALUES (23, 10003, 'PKR768 '2016-05-25', '2016-05-31' ); INSERT INTO TRIP VALUES (24, 20002, 'SST005' 2016-08-02 2016-08-17' ); INSERT INTO TRIP VALUES (25, 10001, 'PKR768'. 2017-01-12', '2017-01-17' ); INSERT INTO TRIP VALUES (26, 10001, AB0804 2013-02-20', '2013-02-26' ); INSERT INTO TRIP VALUES (27, 20002, 'KKK007 2013-03-12', '2013-03-17' ); INSERT INTO TRIP VALUES (28, 30005, 'AB0804 2010-06-29', '2010-07-05' ); INSERT INTO TRIP VALUES (29, 10001, 'QRT834', '2010-09-17', '2010-09-25' ); INSERT INTO TRIP VALUES (30, 10002, 'KKK007 2010-12-15', 2010-12-17' ); INSERT INTO TRIP VALUES (31, 10003, 'SST005 2010-01-23', 2010-01-29' ); INSERT INTO TRIP VALUES (32, 20002, 'PKR768', 2010-03-12', '2010-03-17' ); INSERT INTO TRIP VALUES (33, 30005, 'QRT834', '2003-04-23', '2003-05-01 ); INSERT INTO TRIP VALUES (34, 30005, 'PKR008' '2004-04-23 '2004-04-29' ); INSERT INTO TRIP VALUES (35, 10001, 'PKR768', 2018-05-24 '2018-06-03' ); INSERT INTO TRIP VALUES (36, 10002, 'SYF777', '2017-02-25', '2017-02-28' ); INSERT INTO TRIP VALUES (37, 30005, 'PKR008' 2017-01-23', '2017-01-29' ); INSERT INTO TRIP VALUES ( 38, 10003, 'SST005 '2018-01-10 '2018-01-29' ); INSERT INTO TRIP VALUES (39, 30005, 'PKR008' 2019-04-23', '2019-04-29' ); INSERT INTO TRIP VALUES (40, 10002, 'SYF777 '2018-02-20', '2018-02-25' ); INSERT INTO TRIP VALUES (41, 30005, 'PKR008' '2018-04-23', '2018-04-29' ); INSERT INTO TRIP VALUES (42, 10003, 'SST005 '2018-05-23 '2018-05-29' ); INSERT INTO TRIP VALUES (43, 10002, 'SYF777', '2017-02-20'. 2017-02-25' ); INSERT INTO TRIP VALUES (44, 10002, 'PKR008' 2019-04-23 2019-04-29' ); INSERT INTO TRIP VALUES (45, 10003, 'SST005', 2017-01-23 '2017-01-29' ); INSERT INTO TRIP VALUES(46, 30005, 'AB0988 2019-04-23', '2019-04-29' ); INSERT INTO TRIP VALUES (47, 10002, 'AB0804' '2019-02-20', '2019-02-25' ); INSERT INTO TRIP VALUES (48, 40005, 'SST005' '2018-07-23 2018-07-29' ); INSERT INTO TRIP VALUES (49, 10002, 'SST005' 2020-11-01', '2020-11-02' ); INSERT INTO TRIP VALUES (50, 10001, 'SST005' 2020-10-05', '2020-10-06' ); INSERT INTO TRIP VALUES (51, 20002, 'SST005', '2020-10-23', '2020-10-29' ); NNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNN CREATE TABLE TRIPLEG TNUM DECIMAL (10) NOT NULL, /* Trip number LEGNUM DECIMAL (2) NOT NULL, /* Leg number DEPARTURE VARCHAR(30) NOT NULL, /* Departure location DESTINATION VARCHAR(30) NOT NULL, /* Destination location CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM), CONSTRAINT TRIPLEG_FKEY1 FOREIGN KEY (TNUM) REFERENCES TRIP (TNUM)); INSERT INTO TRIPLEG VALUES( 1, 1, Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 1, 2, 'Melbourne', 'Hobart'); INSERT INTO TRIPLEG VALUES( 1, 3, "Hobart', 'Perth'); INSERT INTO TRIPLEG VALUES ( 1, 4, 'Perth', 'Adelaide'); INSERT INTO TRIPLEG VALUES( 1, 5, 'Adelaide', 'Wollongong'); INSERT INTO TRIPLEG VALUES( 2, 1, 'Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 3, 1, 'Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 4, 1, Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 5, 1, Melbourne', 'Sydney'); INSERT INTO TRIPLEG VALUES( 6, 1, Melbourne', 'Sydney'); INSERT INTO TRIPLEG VALUES( 7, 1, Melbourne', 'Sydney'); INSERT INTO TRIPLEG VALUES( 8, 1, 'Sydney', 'Newcastle'); Task 3 (10 marks) An objective of this task is to implement data retrieval, data manipulation and data definition statements of SQL. In this task we use the sample database available for the subject and used in the coursework tasks. Before you start implementation of this task, download the files dbcreate.sql and dbdrop.sql. Next, process a script file dbdrop.sql and immediately after that the scripts abcreate.sql to refresh a sample database. No report is expected from processing of the scripts dbdrop.sql and dbcreate.sql. Download a file solution3.sql and insert into the file the implementations of the following actions. (1) Find the first and the last names of drivers and mechanics, such that the drivers used at least one time a truck with a registration PKR768 and the mechanics provided their date of birth. (2) Find the registration numbers of trucks and years when the trucks had been manufactured for all trucks used by at least one driver with a driving licence number B2. (3) Find the total number of employees who are not drivers. Assume that there exist employees who are not drivers and who are not mechanics and who are not administration people. (4) Find how many times each truck has been used for the trips. List a truck registration number and the total number of times a truck has been used for the trips. Include the truck registration numbers of all trucks that have not been used for the trips so far with the total number of trips equal to zero. (5) Find the names of departure locations such that at least 5 legs of any trips started in each location. (6) Modify a structure of the database such that after the modification it would be possible to add information about repairs performed by each mechanic. A repair is described by a date when it was performed, short explanations, and mechanic involved. Assume that one mechanic can work on only one repair per day. (7) Modify a structure of the database such that after the modification it would be possible to add information about the total number of trips performed by each driver. Then, update the database, such that it contains information about the total number of trips performed by each driver. (8) Delete from a database information about the drivers who performed more than 3 trips. It is recommended to decompose the complex queries into the sequences of subqueries and use SELECT statement with WITH clause for implementation of subqueries. The correct implementations of the subtasks from (1) to (6) are worth 1 mark each and the solutions of the tasks (7) and (8) are worth 2 marks each. NULL, CREATE TABLE EMPLOYEE ENUM DECIMAL(12) NOT NULL, /* Employee number FNAME VARCHAR(50) NOT NULL, /* First name INITIALS VARCHAR(5) NULL, /* Initials LNAME VARCHAR(50) NOT NULL, /* Last name DOB DATE /* Date of birth BLDG DECIMAL (3) NOT NULL /* Building number STREET VARCHAR(50) NOT NULL, /* Street name SUBURB VARCHAR(50) NOT NULL, /* Suburb name STATE VARCHAR(30) NOT NULL, /* State ZIPCODE DECIMAL (4) NOT NULL, /* Zip code SALARY DECIMAL (9,2) NOT NULL, /* Salary CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM), CONSTRAINT EMPLOEE_CHECK1 CHECK (DOB AND BLDG O AND SALARY = 0 AND YEXP = 0 AND YEXP = 1999 AND YMANUF AND CAPACITY =5 AND SEATS = DEPARTURE_DATE ) ); INSERT INTO TRIP VALUES( 1, 19001, 'PKR768', 2019-01-12', '2019-01-17' ); INSERT INTO TRIP VALUES( 2, 10001, 'SYF777' 2019-02-20', '2019-02-22' ); INSERT INTO TRIP VALUES( 3, 10001, 'AB0804 2019-03-12', '2019-03-17' ); INSERT INTO TRIP VALUES( 4, 10001, 'PKR768 2019-06-29', '2019-07-06' ); INSERT INTO TRIP VALUES( 5, 20002, 'PKR768 2019-01-12', '2019-01-17' ); INSERT INTO TRIP VALUES( 6, 10002, 'SYF777'. 2019-02-20', '2019-02-25' ); INSERT INTO TRIP VALUES( 7, 30005, 'KKK007 2019-03-12', '2019-03-26' ); INSERT INTO TRIP VALUES( 8, 10001, 'AB0804', 2019-01-12', '2019-01-17' ); INSERT INTO TRIP VALUES( 9, 10002, "QRT834', 2019-09-17', '2019-09-19' ); INSERT INTO TRIP VALUES(10, 30005, 'KKK007 2019-12-15', '2019-12-25' ); INSERT INTO TRIP VALUES(11, 10003, 'SST005 2018-01-23', '2018-01-29' ); INSERT INTO TRIP VALUES(12, 10002, 'PKR768 2018-03-12', '2018-03-17' ); INSERT INTO TRIP VALUES(13, 20002, 'QRT834', 2019-04-23', '2019-04-26' ); INSERT INTO TRIP VALUES (14, 20002, 'PKR008 2019-04-23', 2019-04-29' ); INSERT INTO TRIP VALUES (15, 30005, 'PKR768', 2019-05-24', '2019-05-29' ); INSERT INTO TRIP VALUES(16, 30005, 'SST005', '2017-08-02 2017-08-17' ); INSERT INTO TRIP VALUES(17, 20002, 'ORT834', '2017-09-17', '2017-09-22' ); INSERT INTO TRIP VALUES (18, 10001, 'KKK007 2017-12-15', '2019-12-17' ); INSERT INTO TRIP VALUES (19, 30005, 'SST005 2018-01-23', '2018-01-28' ); INSERT INTO TRIP VALUES (20, 10003, 'PKR768 '2018-03-12 '2018-03-17' ); INSERT INTO TRIP VALUES (21, 10001, 'QRT834', 2016-04-23', '2016-04-28' ); INSERT INTO TRIP VALUES (22, 30005, 'PKR008 2016-04-23', '2016-04-26' ); INSERT INTO TRIP VALUES (23, 10003, 'PKR768 '2016-05-25', '2016-05-31' ); INSERT INTO TRIP VALUES (24, 20002, 'SST005' 2016-08-02 2016-08-17' ); INSERT INTO TRIP VALUES (25, 10001, 'PKR768'. 2017-01-12', '2017-01-17' ); INSERT INTO TRIP VALUES (26, 10001, AB0804 2013-02-20', '2013-02-26' ); INSERT INTO TRIP VALUES (27, 20002, 'KKK007 2013-03-12', '2013-03-17' ); INSERT INTO TRIP VALUES (28, 30005, 'AB0804 2010-06-29', '2010-07-05' ); INSERT INTO TRIP VALUES (29, 10001, 'QRT834', '2010-09-17', '2010-09-25' ); INSERT INTO TRIP VALUES (30, 10002, 'KKK007 2010-12-15', 2010-12-17' ); INSERT INTO TRIP VALUES (31, 10003, 'SST005 2010-01-23', 2010-01-29' ); INSERT INTO TRIP VALUES (32, 20002, 'PKR768', 2010-03-12', '2010-03-17' ); INSERT INTO TRIP VALUES (33, 30005, 'QRT834', '2003-04-23', '2003-05-01 ); INSERT INTO TRIP VALUES (34, 30005, 'PKR008' '2004-04-23 '2004-04-29' ); INSERT INTO TRIP VALUES (35, 10001, 'PKR768', 2018-05-24 '2018-06-03' ); INSERT INTO TRIP VALUES (36, 10002, 'SYF777', '2017-02-25', '2017-02-28' ); INSERT INTO TRIP VALUES (37, 30005, 'PKR008' 2017-01-23', '2017-01-29' ); INSERT INTO TRIP VALUES ( 38, 10003, 'SST005 '2018-01-10 '2018-01-29' ); INSERT INTO TRIP VALUES (39, 30005, 'PKR008' 2019-04-23', '2019-04-29' ); INSERT INTO TRIP VALUES (40, 10002, 'SYF777 '2018-02-20', '2018-02-25' ); INSERT INTO TRIP VALUES (41, 30005, 'PKR008' '2018-04-23', '2018-04-29' ); INSERT INTO TRIP VALUES (42, 10003, 'SST005 '2018-05-23 '2018-05-29' ); INSERT INTO TRIP VALUES (43, 10002, 'SYF777', '2017-02-20'. 2017-02-25' ); INSERT INTO TRIP VALUES (44, 10002, 'PKR008' 2019-04-23 2019-04-29' ); INSERT INTO TRIP VALUES (45, 10003, 'SST005', 2017-01-23 '2017-01-29' ); INSERT INTO TRIP VALUES(46, 30005, 'AB0988 2019-04-23', '2019-04-29' ); INSERT INTO TRIP VALUES (47, 10002, 'AB0804' '2019-02-20', '2019-02-25' ); INSERT INTO TRIP VALUES (48, 40005, 'SST005' '2018-07-23 2018-07-29' ); INSERT INTO TRIP VALUES (49, 10002, 'SST005' 2020-11-01', '2020-11-02' ); INSERT INTO TRIP VALUES (50, 10001, 'SST005' 2020-10-05', '2020-10-06' ); INSERT INTO TRIP VALUES (51, 20002, 'SST005', '2020-10-23', '2020-10-29' ); NNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNN CREATE TABLE TRIPLEG TNUM DECIMAL (10) NOT NULL, /* Trip number LEGNUM DECIMAL (2) NOT NULL, /* Leg number DEPARTURE VARCHAR(30) NOT NULL, /* Departure location DESTINATION VARCHAR(30) NOT NULL, /* Destination location CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM), CONSTRAINT TRIPLEG_FKEY1 FOREIGN KEY (TNUM) REFERENCES TRIP (TNUM)); INSERT INTO TRIPLEG VALUES( 1, 1, Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 1, 2, 'Melbourne', 'Hobart'); INSERT INTO TRIPLEG VALUES( 1, 3, "Hobart', 'Perth'); INSERT INTO TRIPLEG VALUES ( 1, 4, 'Perth', 'Adelaide'); INSERT INTO TRIPLEG VALUES( 1, 5, 'Adelaide', 'Wollongong'); INSERT INTO TRIPLEG VALUES( 2, 1, 'Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 3, 1, 'Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 4, 1, Sydney', 'Melbourne'); INSERT INTO TRIPLEG VALUES( 5, 1, Melbourne', 'Sydney'); INSERT INTO TRIPLEG VALUES( 6, 1, Melbourne', 'Sydney'); INSERT INTO TRIPLEG VALUES( 7, 1, Melbourne', 'Sydney'); INSERT INTO TRIPLEG VALUES( 8, 1, 'Sydney', 'Newcastle')