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)
- 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
- 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
- Write the SQL query to obtain the number of unclosed incidents.
SELECT count(*) FROM INCIDENT WHERE dateHourClosing is NULL
- 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
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)
- 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)
- 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
- 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
- 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")