Answer all 15 questions in SQL and make sure it runs correctly

(e) The maximum load is measured in tons and never greater than test it on other servers you might get different behaviour. Don't forget that in MySQL table names are case sensitive. You must not deviate from the requested order and name of the columns in queries. Note that for every query the names and order of columns is clearly specified inthe paper. Any change will most likely confuse the testing seript and cost you marks. Each question does only allow oe SQL statement as answer. Copy this statement directly below the cormresponding question comment, e.g. -801 if you answer Question 1, on a new line in the template file. Do not remowe any of the template comments as they drive the testing rig. If you cannot answer a question just leave thes comment. You can submit partial answers but they must be syntax correct and run. The marks you get for t 99.9 tons. We only keep one digit after the decimal point. The default load is 40.0 tons Your code mu execute withon snax errer and withoat runtime error assuming that other tables have been set up by the seript 12-setup.sql which you must not include. 17 marks 2. Write SQL coe to set up table FW.TransportRequirement ac cording to the following Relational Schema: FW Trans primary key (number,order.no) foreign key order.no references FW SalesOrdeo) foreign key lorry references FW Lorry vchicleRegNo) tnumber,order no,orry,transport.quantity) pace empty below the question n umber on the quality of the answer. ht wish to add comments to help the marker appreciate what you h ave done in case your query is You must accomodate the folowing requirements: (a) For lable anl column names you must pick the names used in the schema above (otherwise you wil lose marks as tests will fail). 1. Write SQL code to set up table Fw.lorry according to the following (b) For each order there are never more than 255 transpont require- Relational Schema FW Lorry vehicleRegNo.make.model maxLoad,accessories) primary key vehicleRcgNo You must accommodate the following requirements: (c) The type of the transport quantity column must he like the ono for the maximum load for lorries given in the question le) above (d) When an order is deleted, al transportation requirement records (e) When an order's order number changes (which will rarely hap- however the defult must now be 0 Fo table and column names you mt pick the names used in the schema above (otherwise you will lose marks as ests will fail) for this order shall be deleted automatically as well. (b) The vehicle registration number may contain letters and is al- ways 7 characters long pem.bentbe change is automatically and consistently performed in the affected transport requirements. (c) The make of the lorry is always one of the following: Volvo, (f) When a lorry is deleted from the database, the transport require- Ashok. Ford, Hyundai, Iveco, MAN, Mercedes-Benz, Scania, Skoda. Tata. The company does not use any other. When or- dered, they should be ordered alphabetically on their string val- ues. Comparison, however, should be normal string comparison like standard collation prescribes for strings). ments affected should remain on the database (not affocting the deletion of tbe lorry otherwise). (g) If and where applicable equip the foeign constraints with con straint names of your choosing (d) The accessories can be any combination of the following four strings: Combination, Dual, EOBR, Tandem (which stand for combination truck, dual wheel usage on truck, clectronic on board reconding system, and use of tandem axle on truck). Your code mus execute wiho svnfax eror and without runime error assuming that all other lables have been set up by the seript a2-setups qi which you must not include marks (e) The maximum load is measured in tons and never greater than test it on other servers you might get different behaviour. Don't forget that in MySQL table names are case sensitive. You must not deviate from the requested order and name of the columns in queries. Note that for every query the names and order of columns is clearly specified inthe paper. Any change will most likely confuse the testing seript and cost you marks. Each question does only allow oe SQL statement as answer. Copy this statement directly below the cormresponding question comment, e.g. -801 if you answer Question 1, on a new line in the template file. Do not remowe any of the template comments as they drive the testing rig. If you cannot answer a question just leave thes comment. You can submit partial answers but they must be syntax correct and run. The marks you get for t 99.9 tons. We only keep one digit after the decimal point. The default load is 40.0 tons Your code mu execute withon snax errer and withoat runtime error assuming that other tables have been set up by the seript 12-setup.sql which you must not include. 17 marks 2. Write SQL coe to set up table FW.TransportRequirement ac cording to the following Relational Schema: FW Trans primary key (number,order.no) foreign key order.no references FW SalesOrdeo) foreign key lorry references FW Lorry vchicleRegNo) tnumber,order no,orry,transport.quantity) pace empty below the question n umber on the quality of the answer. ht wish to add comments to help the marker appreciate what you h ave done in case your query is You must accomodate the folowing requirements: (a) For lable anl column names you must pick the names used in the schema above (otherwise you wil lose marks as tests will fail). 1. Write SQL code to set up table Fw.lorry according to the following (b) For each order there are never more than 255 transpont require- Relational Schema FW Lorry vehicleRegNo.make.model maxLoad,accessories) primary key vehicleRcgNo You must accommodate the following requirements: (c) The type of the transport quantity column must he like the ono for the maximum load for lorries given in the question le) above (d) When an order is deleted, al transportation requirement records (e) When an order's order number changes (which will rarely hap- however the defult must now be 0 Fo table and column names you mt pick the names used in the schema above (otherwise you will lose marks as ests will fail) for this order shall be deleted automatically as well. (b) The vehicle registration number may contain letters and is al- ways 7 characters long pem.bentbe change is automatically and consistently performed in the affected transport requirements. (c) The make of the lorry is always one of the following: Volvo, (f) When a lorry is deleted from the database, the transport require- Ashok. Ford, Hyundai, Iveco, MAN, Mercedes-Benz, Scania, Skoda. Tata. The company does not use any other. When or- dered, they should be ordered alphabetically on their string val- ues. Comparison, however, should be normal string comparison like standard collation prescribes for strings). ments affected should remain on the database (not affocting the deletion of tbe lorry otherwise). (g) If and where applicable equip the foeign constraints with con straint names of your choosing (d) The accessories can be any combination of the following four strings: Combination, Dual, EOBR, Tandem (which stand for combination truck, dual wheel usage on truck, clectronic on board reconding system, and use of tandem axle on truck). Your code mus execute wiho svnfax eror and without runime error assuming that all other lables have been set up by the seript a2-setups qi which you must not include marks