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 list of designations of products followed by the sum of their distributed quantities (QtyD) followed by
the sum of their stored quantities (QtyS). (Name the query: Q1)
2) Display the purchasePrice of products which have a weight not exceeding 9kg, and which are distributed by a
distributor with status different to: SARL.
3) Display the color followed by 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) Display the designations of products that are neither stored nor distributed.
5) Show the areas of all factories which are far from TLEMCEN with distance >=1000 km.
