# Solved problems in SQL - SET 1

## Solved problems in SQL - SET 1

### Problem 1

given the database of a music festival: In one performance can participate one or more musicians. A musician can only participate in one performance.

•   Representation (Num_Rep , title_Rep , location)
•   Musician (Num_mus , name , #Num_Rep)
•   Schedule (Date , #Num_Rep , price)

Express the following queries in SQL:

1. The list of titles of performances.
                                        SELECT title_Rep FROM Representation

2. The list of titles of the performances taking place at the "Allissa Theater".
                                        SELECT title_Rep FROM Representation WHERE location="theatre allissa"

3. The list of the names of the musicians and the titles and the titles of the performances in which they participate.
                                        SELECT M.name, R.title_Rep FROM Musician M INNER JOIN Representation R ON R.Num_Rep=M.Num_Rep

4. The list of titles of performances, places and prices from 01/25/2020.
                                        SELECT R.title_Rep, R.location,P.price FROM Schedule P INNER JOIN Representation R
ON P.Num_Rep=R.Num_Rep WHERE P.date="2020-01-25"

5. The number of musicians participating in the performance n ° 20.
                                        SELECT COUNT (*) FROM Musician WHERE Num_Rep =20

6. The performances and their dates whose price does not exceed 20DH.
                                        SELECT R.Num_Rep , R.title_Rep , P.Date FROM Representation R INNER JOIN Schedule P
ON R.Num_Rep=P.Num_Rep WHERE P.price<=20


### Problem 2

Given the following database:

•   Departments :( DNO, DNOM, DIR, CITY)
•   Employees : ( ENO, ENAME, PROF, DATEEMB, SAL, COMM, #DNO)

Express the following queries in SQL:

1. List the employees with a commission
                                        SELECT * FROM Employes WHERE COMM NOT NULL

2. Give the names, jobs and salaries of employees by increasing job, and for each job, by decreasing salary
                                        SELECT ENOM,PROF, SAL FROM Employes ORDER BY PROF ASC, SAL DESC

3. Give the average salary of the employees
                                        SELECT AVG(SAL) FROM Employes

4. Give the average salary of the Production department
                                        SELECT AVG(E.SAL) FROM Employes E INNER JOIN Departement D
ON E.DNO=D.DNO WHERE D.DNOM="production"

5. Give the department numbers and their maximum salary
                                        SELECT DNO, MAX(SAL) FROM Employes GROUP BY DNO

6. Give the different professions and their average salaries?
                                        SELECT PROF, MAX(SAL) FROM Employes GROUP BY PROF

7. Give the lowest average salary by occupation
                                        SELECT PROF, AVG(SAL) as moy FROM Employes
GROUP BY PROF
ORDER BY moy ASC
LIMIT 1

8. Give the job (s) with the lowest average salary, as well as this average salary
                                        SELECT PROF FROM Employes GROUP BY PROF
HAVING AVG(SAL)=(SELECT AVG(SAL) as moy FROM Employes
GROUP BY PROF ORDER BY moy ASC LIMIT 1)


### Problem 3

Given the following relational model relating to the management of the annual grades of a class of students:

•   STUDENT(NStudent, Name, last_name)
•   SUBJECT(CodeMat, Sub_name, CoeffMat)

Express the following queries in SQL:

1. What is the total number of students?
                                        SELECT count(*) FROM STUDENT

2. Which of the notes is the highest and the lowest note?
                                        SELECT MIN(grade) as ’plus basse note’, MAX(grade) as ’plus haute note’ FROM GRADE

3. What are the averages of each student in each subject?
                                        SELECT E.NEtudiant, M.Sub_name, AVG(EV.grade) AS MoyEtuMat
FROM GRADE EV, SUBJECT M, STUDENT E WHERE EV.CodeMat = M.CodeMat AND EV.NStudent = E.NStudent
GROUP BY E.NStudent, M.Sub_name

4. What are the averages by subject? With the MGETU view of question 3 (MOYETUMAT)
                                        SELECT Sub_name, AVG(MoyEtuMat) FROM MOYETUMAT GROUP BY Sub_name

5. What is the overall average for each student? With the MGETU view of question 3 (MOYETUMAT)
                                        SELECT NStudent, SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM MOYETUMAT GROUP BY NStudent

6. What is the overall promotion average? Use the MGETU view of question 5:
                                        SELECT AVG(MgEtu) FROM MGETU

7. Which students have a general average greater than or equal to the general average of the promotion? With the MGETU view of question 5
                                        SELECT NStudent , Name , last_name , MgEtu FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU)