# Solved problems in SQL - SET 2

## Solved problems in SQL - SET 2

### Problem 1

Given the database called "project_management" allowing to manage projects related to software development. It is described by the following simplified text representation:

•   Developer (NumDev, NameDev, AdrDev, EmailDev, PhoneDev)
•   Project (NumProj, TitleProj, DateStart, DateEnd)
•   Software (CodSoft, SoftName, SoftPrice, #NumProj)
•   Realization (#NumProj, #NumDev)

Write SQL queries allowing: :

1. Display the names and prices of software belonging to the project having the title "stock management", sorted in descending order of price.
                                        SELECT L.NomLog, L.PrixLog FROM Logiciel L INNER JOIN Projet P
ON L.NumProj=P.NumProj WHERE P.TitreProj="gestion␣de␣stock"
ORDER BY L.PrixLog DESC

2. Display the total price of software for project number 10. When displayed, the column title will be "total project price".
                                        SELECT SUM(PrixLog) as "cout␣total␣du␣projet" FROM Logiciel WHERE NumPRoj=10

3. Display the number of developers who participated in the project called "stock management"
                                        SELECT count(*) FROM Developpeur D INNER JOIN Realisation R
ON D.NumDev=R.NumDev INNER JOIN Projet P ON P.NumProj=R.NumProj

4. Show projects that have more than 5 software
                                        SELECT NumProj, TitreProj FROM PRojet P INNER JOIN Logiciel L ON P.NumProj=L.NumProj
GROUP BY NumProj, TitreProj
HAVING count(*)>5

5. The numbers and names of the developers who participated in all the projects.
                                        SELECT NumDev, NomDev FROM Developpeur D INNER JOIN Realisation R ON D.NumDev=R.NumDev
GROUP BY NumDev, NomDev
HAVING count(*)=(SELECT COUNT(*) FROM Projet)

6. The project numbers in which all the developers participate in its realization.
                                        SELECT NumProj, TitreProj FROM Projet P INNER JOIN Realisation R ON P.NumProj=R.NumProj
GROUP BY NumProj, TitreProj
HAVING count(*)=(SELECT COUNT(*) FROM Developpeur)


### Problem 2

Below is a simplified text representation of a training course database for students.

It is considered that each teacher teaches only one subject and that at the end of the training cycle, one mark per subject is assigned to each student. On the other hand, students may not follow the same subjects.

•   STUDENT(STD_CODE, STD_NAME, DatnEt)
•   SUBJECT(SUB_CODE, SUB_NAME, SUB_COEF)
•   TEACHER(CodeEns, TEACH_NAME, TEACH_GRADE, #SUB_CODE)

Write the SQL queries to display:

1. Student information (Code, Name and Date of birth) in alphabetical order by name
                                        SELECT * FROM ETUDIANT ORDER BY STD_NAME ASC

2. The names and grades of the teachers who teach the subject "BD".
                                        SELECT E.TEACH_NAME, E.TEACH_GRADE FROM ENSEIGNANT E INNER JOIN MATIERE M
ON M.SUB_CODE=E.SUB_CODE WHERE M.SUB_NAME="BD"

3. The list made up of the distinct names and coefficients of the different subjects which are taught by teachers of grade "Grd3".
                                        SELECT DISTINCT(M.SUB_NAME), M.SUB_COEF FROM ENSEIGNANT E INNER JOIN MATIERE M
ON M.SUB_CODE=E.SUB_CODE WHERE E.TEACH_GRADE="Grd3"

4. The list of subjects (Name and Coefficient) which are followed by the student of code ‘Et321’.
                                        SELECT M.SUB_NAME, M.SUB_COEF FROM MATIERE M INNER JOIN NOTE N
ON M.SUB_CODE=N.SUB_CODE INNER JOIN ETUDIANT E ON E.STD_CODE=N.STD_CODE
WHERE E.STD_CODE="Et321"

5. The number of teachers who teach the subject name "IT"
                                        SELECT COUNT(*) FROM ENSEIGNANT E INNER JOIN MATIERE M ON M.SUB_CODE=E.SUB_CODE
WHERE M.SUB_NAME="Informatique"


### Problem 3

We consider the following database BD_AIR_MAROC :

•   PILOT (NUMPIL, PILNAME, CITY, SALARY)
•   PLANE (PLANE_NUM, PLANENAME, CAPACITY, CITY)
•   FLIGHT (FLIGHTNUM, #NUMPIL, #PLANE_NUM, S_CITY, CITY_ARR, S_TIME, A_TIME)

Write the SQL queries to display:

1. List the planes with a capacity greater than 350 passengers.
                                        SELECT * FROM AVION WHERE CAPACITY>350

2. What are the numbers and names of the planes located in Marrakech?
                                        SELECT PLANE_NUM, PLANENAME FROM AVION WHERE CITY=’Marrakech’

3. What are the numbers of the pilots in service and the departure cities of their flights?
                                        SELECT NUMPIL , S_CITY FROM VOL

4. Give all information about the pilots of the company.
                                        SELECT * FROM PILOTE

5. What are the names of the pilots domiciled in Meknes whose salary is more than 20,000 DH?
                                        SELECT PILNAME FROM PILOTE WHERE CITY=’Meknes’ AND SALARY>20000

6. Which planes (number and name) are located in Marrakech or whose capacity is less than 350 passengers?
                                        SELECT PLANE_NUM, PLANENAME FROM AVION WHERE CITY=’Marrakech’ AND CAPACITY< 350

7. What are the numbers of pilots who are not in service?
                                        SELECT NUMPIL FROM PILOTE WHERE NUMPIL NOT IN (SELECT DISTINCT NUMPIL FROM VOL)

8. Give the number of flights made from Marrakech by pilots from Meknes ?
                                        SELECT DISTINCT V.FLIGHTNUM FROM VOL AS V, PILOTE AS P
WHERE V.NUMPIL=P.NUMPIL AND V.S_CITY=’Marrakech’ AND P.CITY=’Meknes’

Or
                                        SELECT DISTINCT FLIGHTNUM FROM VOL WHERE V.S_CITY=’Marrakech’ AND
NUMPIL NOT IN (SELECT NUMPIL FROM PILOTE WHERE CITY=’Meknes’)

9. What flights are made by an airplane that is not located in Marrakech?
                                        SELECT DISTINCT V.FLIGHTNUM FROM VOL V, AVION A WHERE A.PLANE_NUM=V.PLANE_NUM AND A.CITY !=’Marrakech’

10. Which cities are served from the city of arrival of a flight departing from Fes?
                                        SELECT DISTINCT CITY_ARR FROM VOL WHERE S_CITY=’Fes’ AND S_CITY != CITY_ARR