Data Manipulation in Database System

ICT IGCSE Relationship Queries Importing CSV: Make sure currency is in Euros with two decimal places Chapter 18: Data Manipulation (Access) Using a suitable database package, import the file CARS.CSV Set appropriate data types during the import & design view. Set the VIN field as a key field Import the file DISTRIBUTORS.CSV as a new table in your database. Set the Distributor_code field as a key field Create a one-to-many relationship as a link between the Distributor_code field in the Distributor table and the Distributor field in the Cars table. Insert these three records into the Cars table: VIN 376C15423002 MicroZed 376C15423010 MicroSE 377C15423010 Model Power Engine_Size Cost_Price Colour 1200 900 8500.55 600 Distributor FMD SA FMD SA FMD SA Dispatched_Date 19/06/2015 20/06/2015 21/06/2015 Dispatched Yes Yes Yes D H G 7500 Blue Blue Blue Micro 9000 WWW.YAHMAD.CO.UK

ICT IGCSE Relationship Queries Make sure currency is in Euros with two decimal places Chapter 18: Data Manipulation (Access) Show the following fields only: Vin, Model, Cost_Price, Port, County and Updated_Price Show only the cars dispatched from Paris Insert a new field called Updated_Price which is calculated at a run time You need to add 10% to the price. Sort the model into ascending order Has a page orientation of landscape Count the number of cars under the model field. Include a suitable label. Has your name at the top of each page Show the following fields only: Vin, Model, Engine size, Dispatched_Date, Port, Country and Updated_Price Show only the cars dispatched between January and February 2014 Insert a new field called Updated_Price which is calculated at a run time You need to add 15% to the price. Sort the Port and then Country into ascending order Has a page orientation of landscape Work out the average of the Updated_Price field. Include a suitable label. Has your name at the bottom of each page Show the following fields only: Vin, Model, Colour, Dispatched, Distributor_Code, Contact Name and Updated_Price Show only the Vin which ends in 22, Model is Micro, Colour is Red and has been Dispatched. Insert a new field called Updated_Price which is calculated at a run time You need to add 25% to the price. Sort the updated price into ascending order Has a page orientation of landscape Work out the total sum of the Updated_Price field. Include a suitable label. Has your name at the bottom of the report WWW.YAHMAD.CO.UK

ICT IGCSE Relationship Queries Make sure currency is in Euros with two decimal places Show the following fields only: Model, Colour, Vin, Distributor_Code, Region and Updated_Price Show only the Vin which ends with 22, Model is Micro B, Colour is Green and models which have been dispatched after January 2014 Insert a new field called Updated_Price which is calculated at a run time You need to add 17.5% to the price. Sort the Region into ascending order Has a page orientation of landscape Count the total number of models. Include a suitable label. Has your name at the bottom of each page. Chapter 18: Data Manipulation (Access) Show the following fields only: Model, Cost_Price, Distributor, Port, Country, City and Updated_Price Show only the Micro or MicroB models, Cost_Price is more than 7500 and Distributor is FMD SA Insert a new field called Updated_Price which is calculated at a run time You need to add 22.5% to the price. Sort the Cost_Price into ascending order Has a page orientation of landscape Work out the average of the Cost_Price field. Include a suitable label. Has your name at the bottom of each page. Show the following fields only: Model, Power, Colour, Dispatched_Date, Contact_Name, City and Updated_Price Show only the Models which have been dispatched after February 2014, VIN ends in 13, Power is H or G and Colour is Gold. Insert a new field called Updated_Price which is calculated at a run time You need to add 5.5% to the price. Sort the Contact_name into ascending order Has a page orientation of landscape Work out the total sum of the Updated_Price field. Include a suitable label. Has your name at the bottom of the report. WWW.YAHMAD.CO.UK

ICT IGCSE Relationship Queries Make sure currency is in Euros with two decimal places Show the following fields only: Model, Dispatched, Colour, Cost_Price, Distributor, First_Name and Address_1 Show only the models which are not Micro or MicroB, colour is blue, Distributor is NMD or FMD SA and there is no dispatched date. Sort the Distributor into ascending order Has a page orientation of landscape Count the total number of models. Include a suitable label. Has your name at the top of each page. Chapter 18: Data Manipulation (Access) Show the following fields only: VIN, Engine_Size, Cost_Price, Distributor_Code, City, Region and Updated_Price Show only the models which are green or red, Cost_Price is more than 9000 and Distributor is EMD SA or BMD AG Insert a new field called Updated_Price which is calculated at a run time You need to add 12.5% to the price. Sort the City into ascending order Has a page orientation of landscape Work out the average of the Updated_Price field. Include a suitable label. Has your name at the bottom of each page. Show the following fields only: Distributor_Code, Model, Engine, Colour, Port, Cost_Price and Updated_Price Show only the Models which have a engine size of 1200, Price is between 7500 and 8600, dispatched date is after May 2014 and the colour is Silver or Blue. Insert a new field called Updated_Price which is calculated at a run time You need to add 7.5% to the price. Sort the Port into ascending order Has a page orientation of landscape Work out the total sum of the Cost_Price field. Include a suitable label. Has your name at the top of the report. WWW.YAHMAD.CO.UK

ICT IGCSE Relationship Queries Make sure currency is in Euros with two decimal places Chapter 18: Data Manipulation (Access) Show the following fields only: Port, City, Region, Model, Colour, Cost_Price, Power and Dispatched Date Show only the models which are not Micro or MicroB, Colour is not Red or Silver, Power is not E and dispatched in the month of May 2014. Sort the Port and then City into ascending order Has a page orientation of landscape Count the total number of models. Include a suitable label. Has your name at the top of each page. WWW.YAHMAD.CO.UK