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

Query students taking two courses

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

spj - displays a summary list of supplier supplied parts

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;

5-3 query the manufacturer of the computer (PC or laptop) with the highest production speed

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
);

Search for the most expensive printer model

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

Query courses above average

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))

3-2 - © check with all manufacturers who sell laptops (not PC s)

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 

);

3-1 - © search for stars in st1's 2018 films

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';

2-1-(e) query all male movie stars or movie stars whose addresses contain 4

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'