Solved problems in SQL - SET 3

Solved problems in SQL - SET 3

Problem 1

Let the following relational diagram:

  •   Departement (NameD, N_Dep, Director)
  •   Employees (Matricule, first_name, last_name, birthday, Address, Salary, #N_dep, superior)
  •   Project (NameP, N_pro, location, #N_Dep)
  •   Working (#Matricule, #N_Proj, hours)

The superior attribute in the Employeese relationship contains the personnel number of the employee's direct superior. Each employee belongs to a department and works on zero, one or more projects. Each project is attached to a department which may be different from that of the employees working on this project.

Express the following queries in SQL:

  1. Taha Lamharchi's date of birth and address.
                                            SELECT birthday, Address FROM Employees WHERE Nom=’Lamharchi’ AND Prenom =’Taha’ 
                                        
  2. Name and address of employees working in the research department.
                                            SELECT E.first_name, E.Address FROM Employees as E, Departement as D 
                                            WHERE E.N_dep=D.N_dep AND NameD=’recherche’ 
                                        
  3. Last name and first name of the employees whose superior is Taha Lamharchi.
                                            SELECT first_name , last_name FROM Employees 
                                            WHERE superior=(SELECT Matricule FROM Employees WHERE Nom=’Lamharchi’ AND Prenom =’Taha’) 
                                        
  4. Name of the employees who work more than 10 hours on a project in Meknes
                                            SELECT E.first_name FROM Employees as E, Working as T , Project P 
                                            WHERE E.Matricule=T.Matricule AND T.N_proj=P.N_proj AND T.hours >=10 AND P.location=’Meknes’ 
                                        
  5. Name of the projects on which Taha Lamharchi and Dounia Mahmoud are working.
                                            SELECT T.N_proj FROM Working as T,Employees as E WHERE T.Matricule=E.Matricule AND E.first_name=’Lamharchi’ AND E.last_name=’Taha’
                                            INTERSECT
                                            SELECT T.N_proj FROM Working as T,Employees as E WHERE T.Matricule=E.Matricule AND E.first_name=’Mahmoud’ AND E.last_name=’Dounia’ 
                                                                    
  6. Last name and first name of the employees who do not work on any project.
                                            SELECT first_name , last_name FROM Employees 
                                            WHERE Matricule NOT IN ( SELECT Matricule FROM Working) 
                                                                    
  7. Number of projects that have at least one participant from each department.
                                            SELECT T.N_proj FROM Working as T,Project as P, Employees as E 
                                            WHERE T.N_proj=P.N_proj AND T.Matricule=E.Matricule 
                                            GROUP BY T.N_proj 
                                            HAVING count(DISTINCT E.N_dep)=(SELECT count(*) FROM Departement) 
                                        
  8. Name of the employees who are not working on a project in Meknes.
                                            SELECT Nom FROM Employees 
                                            WHERE Matricule NOT IN(SELECT T.Matricule FROM Working as T, Project as P 
                                                WHERE T.N_proj=P.N_proj AND P.location=’Meknes’) 
                                                                    

Problem 2

Given the following relationship diagram which represents the database of an online travel agency.

  •   CLIENT (NumCli, first_name, last_name, e-mail, NumCB )
  •   TRIP (CodeTRIP, Destination, Durée, Prix )
  •   BOOKING (#NumCli, #CodeTRIP, DateRes )

Express the following queries in SQL:

  1. Last name, first name and e-mail of customers with a current booking
                                            SELECT first_name, last_name, e-mail FROM CLIENT 
                                            WHERE NumCli IN (SELECT DISTINCT NumCli FROM BOOKING) 
                                                                    
  2. Last name, first name and e-mail of customers with no current booking
                                            SELECT first_name, last_name, e-mail FROM CLIENT 
                                            WHERE NumCli NOT IN (SELECT DISTINCT NumCli FROM BOOKING) 
                                                                    
  3. Destination and list of clients who have booked for a trip of more than 10 days and cost less than 1000 DH.
                                            SELECT C.first_name, C.last_name, V.Destination FROM CLIENT as C, TRIP as V, BOOKING as R
                                            WHERE C.NumCli=R.NumCli and V.CodeTRIP = R.CodeTRIP AND Duree >=10 AND Prix < 1000 
                                                                    
  4. Numbers of all customers who have booked on all trips offered.
                                            SELECT NumCli FROM BOOKING 
                                            GROUP BY NumCli 
                                            HAVING count(*)=(SELECT count(*) FROM TRIP) 
                                                                    

Problem 3

Given the "cinema" database, the relational diagram of which is given below :

  •   CITY (CodePostal, City_name )
  •   CINEMA (NumCine, Cine_name, Address, #CodePostal )
  •   ROOM (NumROOM, Capacity, #NumCine )
  •   FILM (NumExploit, title, Durée)
  •   PROJECTION (#NumExploit, #NumROOM, WeekNum, NBSeated)

Express the following queries in SQL:

  1. Title of films whose duration is greater than or equal to two hours
                                            SELECT NumExploit , title FROM FILM WHERE Duree >=2 
                                                                    
  2. Name of the cities hosting a cinema named "RIF"
                                            SELECT City_name FROM CITY
                                            WHERE CodePostal IN (SELECT CodePostal FROM CINEMA WHERE Cine_name=’RIF’) 
                                                                    
  3. Name of cinemas located in Meknes or containing at least one room with more than 100 seats
                                            SELECT Cine_name FROM CINEMA 
                                            WHERE CodePostal=(SELECT CodePostal FROM CITY WHERE City_name=’Meknes’) 
                                            OR NumCine IN (SELECT NumCine FROM ROOM WHERE Capacity>=100) 
                                                                    
  4. Name, address and city of the cinemas in which the film "Hypnosis" is played week 18
                                            SELECT C.Cine_name , C.Address , V.City_name FROM CINEMA as C, CITY as V
                                            WHERE C.CodePostal=V.CodePostal AND 
                                            C.NumCine IN (SELECT S.NumCine FROM ROOM as S, FILM as F, PROJECTION as P WHERE P.NumExploit=F.NumExploit AND P.NumROOM=S.NumROOM AND F.title=’Hypnose’ AND P.WeekNum=18) 
                                                                    
  5. Exploitation number of films shown in all theaters
                                            SELECT NumExploit FROM PROJECTION 
                                            GROUP BY NumExploit 
                                            HAVING count(*)=(SELECT count(*) FROM ROOM) 
                                                                    
  6. Title of films that have not been projeted
                                            SELECT title FROM FILM WHERE NumExploit NOT IN (SELECT NumExploit FROM PROJECTION) 
                                                                    

Share this course with your friends :

This course is written by M. ESSADDOUKI Mostafa

Many people realize their hearts desires late in life. Continue learning, never stop striving and keep your curiosity sharp, and you will never become too old to appreciate life.

0 Comment(s)

To leave a comment you must have an account Sign up, or Sign in