Find the order status of the customer, which is displayed as: customer ID, company name, order ID, employee ID
select customers.CustomerID,customers.CompanyName,orders.OrderID,orders.EmployeeID from customers,orders where orders.CustomerID=customers.CustomerID and orders.ShipCity=customers.City order by customers.CustomerID ASC
This topic requires the preparation of SQL statements to retrieve the student ID of at least 'C001' and 'C002' courses in the sc table
select a.sno 'Student number' from (select * from sc where (cno = 'C001')) a inner join (select * from sc where (cno = 'C002')) b on a.sno = b.sno
This topic requires the preparation of SELECT statement, in the SPJ database, a summary list of the total quantity of various parts supplied by each supplier.
Requirement: display the total quantity of each part supplied by each supplier, the total quantity of all parts supplied by each supplier and the total quantity of all parts supplied by all suppliers.
Tip: please use the "WITH ROLLUP" statement to answer.
select coalesce(sno,'All suppliers') as supplier,coalesce(pno,'All parts') as spare parts,sum(qty) as Supply from spj group by sno,pno with rollup;
This topic requires to write SQL statements to query the computer manufacturers (PC or laptop) with the highest production speed, and the query results are arranged in ascending order of manufacturers
select distinct maker from ( select maker,speed from pc,product where pc.model=product.model union select maker,speed from laptop,product where laptop.model=product.model ) a where a.speed in ( select max(speed) from ( select speed from pc,product where pc.model=product.model union select speed from laptop,product where laptop.model=product.model ) b );
Check the printer model with the highest price.
select distinct model from printer where price >= all ( select price from printer )
A2-2 find the product information in the product table where the reorder quantity is greater than or equal to 10 and the repair quantity is greater than the ordered quantity
select ProductID,ProductName,SupplierID from products where ReorderLevel> UnitsOnOrder and ReorderLevel>=10
Ask each student for courses that exceed his or her average.
select sno Student number,cname Course name,grade achievement from cou,sc a where cou.cno = a.cno and (a.grade > (select avg(b.grade) from sc b where a.sno = b.sno))
Check with all manufacturers who sell laptops (not PC s).
select distinct maker from product,laptop where product.model=laptop.model and maker not in ( select maker from product,pc where product.model=pc.model );
Search for stars in the films produced by st1 in 2018.
select distinct starName from StarsIn,Movie where StarsIn.movieTitle=Movie.title and StarsIn.movieYear=Movie.year and year=2018 and studioName='st1';
Retrieve all the male movie stars in the MovieStar table or the movie stars whose addresses contain 4
select distinct name from MovieStar where gender='M' or address like '%4'