INFORMATIQUE 3 SECOND YEAR (ESMT)
TP6 SQL
I) Open “Access” and create a new database named “TP6_yourName&FirstName_group”
II) Consider the PDFS (Product Distributor Factory Store) database, represented by the following MLD:
PRODUCT (Barcode, designation, PurchasePrice, SalesPrice, Color, Weight)
DISTRIBUTOR (NumD, Status, #IDCity)
PFD (#BarCode, #NumF, #NumD, QtyD, dateD)
STORE (NumS, Capacity, #IDCity)
STOCK (#BarCode, #NumS , Stockdate, QtyS)
FACTORY (NumF, AreaF, #IDCity)
City(IDcity, NameC)
Distance (#IDCity1, #IDCity2, NbrKM)
- Create the 8 tables of this database then insert the following records into the tables:
III) Write the following queries in SQL:
1) Show the difference between the distributed quantity (QtyD) and the stored quantity (QtyS) for each
designation. (Name the result: Q1)
(Note: you can create 3 queries to solve this question: Query1a, Query1b, and Query1c)
2) Display the designations of products which have a weight not exceeding 19 kg, and which are distributed by a
distributor with status: SARL.
3) Display the designation and margin of the product that has the largest margin between the purchase price and the
sale price. (Name the second column of the result: Margin)
4) Show the list of distributors who distribute to factories located in the same city where they live.
5) Display the color and weight of the product delivered by a distributor who lives in the same city as the factory and
store that stocks this product
6) Show the list of all factories which are far from the city of distributor n°4 with distance >800 km.
IV) Create all relations between tables.
