Solved problems in SQL - SET 5

Solved problems in SQL - SET 5

Problem 1

In order to ensure the quality of products expected by Customers, the company seeks to optimize the management of breakdowns that may occur in the production infrastructure necessary for the manufacture of Cement. here is an extract from the database:

  •  TECHNICIAN (idTech, last_name, first_name, specialty)
  •  STATION (idstat, last_name, Position, coordLat, coordLong,phase)
  •  MACHINE (idmach, status, DatePutService, DateLastRevision, #idStat)
  •  TYPEINCIDENT (id, description, TimePlannedRepair)
  •  INCIDENT (idInd, remarks, dateHour, dateHourClosing,#idmach,#idType)
  •  INTERVENTION (idInterv, dateHourStart, dateHourEnd, #idInd, #idTech)
  1. Write the SQL query to obtain the list in alphabetical order of the names and surnames of the technicians who performed an intervention on the Machine identified by Ber001.
                                            SELECT last_name , first_name FROM TECHNICIAN tec , INCIDENT inc , INTERVENTION int 
                                            WHERE tec.idTech=int.idTech and int.idInd=inc.idInd and idmach="Ber001" 
                                            ORDER BY last_name ASC , first_name ASC 
                                                                    
  2. Write the SQL query to obtain the list of phases having experienced an "overheating" incident for the month May 2019.
                                            SELECT distinct phase FROM STATION st, MACHINE mch, INCIDENT inc, TYPEINCDENT type
                                            WHERE inc.idmach=mch.idmach AND st.idstat=mch.idstat AND type.id=inc.idType 
                                            AND type.description="sur-chauffage" AND MONTH(dateHour)=5 AND YEAR(dateHour)=2019 
                                                                    
  3. Write the SQL query to obtain the number of unclosed incidents.
                                            SELECT count(*) FROM INCIDENT WHERE dateHourClosing is NULL 
                                                                    
  4. Write the SQL query to obtain the list of names of stations having had more than ten incidents.
                                            SELECT last_name FROM STATION st, MACHINE mch, INCIDENT inc 
                                            WHERE inc.idmach=mch.idmach and st.idstat=mch.idstat
                                            GROUP by last_name 
                                            HAVING count (*) >10 
                                                                    

Problem 2

Here is an excerpt from the sales management database:

  •   Product (Ref, Designation, UnitPrice, Dimension, #code_Machine)
  •   Sale (NSale, #Ref, Amout , DeliveryDate)
  •   Order (NSale, DateOrder, #CodeClt,#EmployeeCode)
  •   product_competitor(Ref,Designation,UnitPrice,Dimension,#code_Machine,Competitor_Name)
  1. Give the SQL query which allows to obtain the monthly turnover of the current year
                                            SELECT sum(Amout*UnitPrice), MONTH(DateOrder) FROM Product, Sale, Order 
                                            WHERE Product.Ref=Sale.Ref AND Sale.NSale=Order.NSale AND YEAR(DateOrder)=YEAR(NOW()) 
                                            GROUP BY MONTH(DateOrder) 
                                                                    
  2. Give the SQL query which calculates the sales rate for each product.
                                            SELECT Ref, sum(Amout)/(select sum(Amout) FROM Sale) FROM Sale 
                                            GROUP BY Ref 
                                                                    
  3. Give the SQL query that displays the best-selling product of the current month.
                                            SELECT Ref, Designation, tot 
                                            FROM (SELECT Ref, Designation, SUM(Amout) as tot FROM Product, Sale WHERE Product.Ref=Sale.Ref GROUP BY Ref) 
                                            ORDER BY tot DESC LIMIT 1
                                                                    
  4. The competitor product table is made up of information on the competitors' featured products; Give the request to add all of the competitor GleenAlu's products to the Products table.
                                            INSERT INTO Product (Ref,Designation,UnitPrice,Dimension,code_machine) 
                                            (SELECT Ref, Designation, UnitPrice, Dimension,code_machine FROM product_competitor where competitor_Name="GleenAlu") 
                                        

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