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)
  •   GRADE(#NStudent, #CodeMat, Date, grade)

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) 
                                        

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