Solved problems in SQL - SET 4

Given the following relational model relating to the simplified management of the stages of the Tour de France 2019, one of the stages of the "individual time trial" type taking place in Saint-Etienne:

•   TEAM(CodeTEAM, NomTEAM, DirecteurSportif)
•   RUNNER(NumberRUNNER, NameRunner, #CodeTEAM, #CodeCOUNTRY)
•   COUNTRY(CodeCOUNTRY, NameCountry)
•   STAGE_TYPE(CodeType, CaptionType)
•   STAGE(NumberEtap, DateSTAGE, StartCity, EndCity, NbKm, #CodeType
•   PARTICIPATE(#NumberRUNNER, #NumberSTAGE, RealizedTime)
•   ATTRIBUTE_BONUS(#NumberSTAGE, #NumberRUNNER, km, Rank, NbSeconds)

Express the following queries in SQL:

1. What is the composition of the Festina team (number, name and country of the runners)?

                                           SELECT NumeroRUNNER , NameRunner , NameCountry FROM TEAM A, RUNNER B, COUNTRY C 
                                           WHERE A.CodeTEAM=B.CodeTEAM And B.CodeCOUNTRY=C.CodeCOUNTRY And NomTEAM="FESTINA" 
                                                                   

2. What is the total number of kilometers of the Tour de France 2019?

                                           SELECT SUM(Nbkm) FROM STAGE 
                                                                   

3. What is the total number of kilometers of "High Mountain" type stages?

                                           SELECT SUM(Nbkm) FROM STAGE A, STAGE_TYPE B 
                                           WHERE A.CodeType=B.CodeType And CaptionType="HAUTE MONTAGNE" 
                                                                   

4. What are the names of the runners who did not get bonuses?

                                           SELECT NameRunner FROM RUNNER
                                           WHERE NumeroRUNNER NOT IN (SELECT NumeroRUNNER FROM ATTRIBUTE_BONUS) 
                                                                   

5. What are the names of the runners who participated in all stages?

                                           SELECT NameRunner FROM PARTICIPATE A, RUNNER B WHERE A.NumeroRUNNER=B.NumeroRUNNER 
                                           GROUP BY NameRunner
                                           HAVING COUNT(*)=(SELECT COUNT(*) FROM STAGE) 
                                                                   

6. What is the general classification of the riders (name, team code, country code and time of the riders) after the first 13 stages, knowing that the bonuses have been incorporated into the times achieved at each stage?

                                           SELECT NameRunner , CodeTEAM , CodeCOUNTRY , SUM(RealizedTime) AS Total
                                           FROM PARTICIPATE A, RUNNER B
                                           WHERE A.NumeroRUNNER=B.NumeroRUNNER and NumeroSTAGE <=13 
                                           GROUP BY NameRunner , CodeTEAM , CodeCOUNTRY ORDER BY Total 
                                       

7. What is the team ranking after the first 13 stages (name and time of the teams)?

                                           SELECT NomTEAM , SUM(RealizedTime) AS Total
                                           FROM PARTICIPATE A, RUNNER B, TEAM C
                                           WHERE A.NumeroRUNNER=B.NumeroRUNNER And B.CodeTEAM=C.CodeTEAM  And NumeroSTAGE <=13
                                           GROUP BY NomTEAM 
                                           ORDER BY Total 
                                                                   

The service manager of a household appliance company has set up a small database to manage the interventions of these technicians. The relational model of this database is as follows:

•   Client (Codecl, nomcl, prenomcl, adresse, cp, ville)
•   Product (Référence, désignation, price)
•   Technicians (Codetec, nomtec, prenomtec, tauxhoraire)
•   Intervention (Number, date, raison, #codecl, #référence, #codetec)

The manager asks you to write the following queries in SQL language:

1. The list of products (reference and designation) classified from cheapest to most expensive.

                                           Select Reference, designation from Product order by price ASC 
                                                                   

2. The technician's intervention number n ° 2381.

                                           Select count (*) from Intervention where codetec =2381 
                                                                   

3. The list of customers who have requested an intervention for products priced above 300 dhs.

                                           SELECT nomcl FROM Client clt, Product prod, Intervention inter
                                           WHERE clt.codecl=inter.codecl AND prod.Reference=inter.Reference AND prod.price>300
                                                                   

4. The interventions made by the technician: ‘Mestiri Ismail’ between August 1 and 31, 2019.

                                           Select Numero , date , raison from Intervention int , Technicians tec
                                           where int.codetec=tec.codetec and tec.nomtec="Mestiri" and tec.prenomtec="Ismail" 
                                           and int.date between "2019-08-01" and "2019-08-31"
                                                                   

   Ou

                                           Select Numero , date , raison from Intervention int , Technicians tec where int.codetec=tec.codetec and tec.nomtec="Mestiri" and tec.prenomtec="Ismail" and MONTH(int.date)=8 and YEAR(int.date)=2009 
                                                                   

5. In addition it informs you that the product referenced 548G saw its price increase (new price = 320 dhs).

                                           update Product set price=320 where Reference="548G" 
                                                                   

6. You also learn from the director of human resources that a new technician has been recruited: his code is 3294, his name is "El Moutawakil Ridha" and is paid at an hourly rate of 15 dhs.

                                           Insert into Technicien values(3294,"EL Abed","Ridha",15) 
                                                                   

The following relational model is a simplified description of a commercial enterprise billing management database.

•   Client (Numcli, Cli_Name, Cli_lastname, adressecli, mailcli)
•   Product (Numprod, designation, price , amount_storage)
•   Seller (IdSeller, SellerName, SellerAddress)
•   Order (Numcom, #Numcli, #IdSeller, #Numprod, date_order, amount)

We assume that Numcli, Numprod, IdSeller and Numcom are of numeric type.

The name, first name and address of the customers as well as the sellers are mandatory information, the email may not be indicated. The default value of the quantity in stock of the products (amount_storage) is equal to 0

Express the following queries in SQL:

1. Create the tables:Product and Order.

                                           CREATE TABLE Product(
                                               Numprod int primary key , 
                                               designation varchar(30), 
                                               price float,
                                               amount_storage int default 0
                                           );
                                           CREATE TABLE Order(
                                               Numcom int primary key , 
                                               Numcli int , 
                                               idSeller int , 
                                               Numprod int,
                                               date_order date,
                                               amount int,
                                               FOREIGN KEY(Numcli) REFERENCES Client(Numcli), 
                                               FOREIGN KEY(idSeller) REFERENCES Seller(idSeller), 
                                               FOREIGN KEY(Numprod) REFERENCES Product(Numprod) 
                                           )
                                                                   

2. The list of clients from marrakech.

                                           select * from Client where adressecli like "%marrakech%" 
                                                                   

3. The list of products (Numprod, designation, price) classified from most expensive to least expensive.

                                           select Numprod , designation , price from Product order by price ASC 
                                                                   

4. Names and addresses of vendors whose names begin with the letter "M".

                                           select SellerName, SellerAddress from Seller where SellerName like "M%" 
                                                                   

5. The list of orders made by the seller "Ismail" between January 1 and 30, 2020.

                                           Select Nymcom, Numcli, IdSeller, Numprod, date_order, amount
                                           FROM Order cmd, Seller vend
                                           WHERE cmd.IdSeller=vend.IdSeller AND vend.SellerName="Ismail"
                                           AND cmd.date_order BETWEEN "2020-01-01" AND "2020-01-30"
                                                                   

6. The number of orders containing product n ° 365.

                                           select count (*) from Order where Numprod =365