Queries fall in two categories the ones that are made by the customer and the ones that are made by the companies.

SQL Query 1 (Join)

Find all companies, which supply a particular part with the cost less than that specified by the user. You are required to only display the companies name and its address.

Assume the user chooses the knobsupply it at cost less than 1000 dollars.

Select Sname, Saddress
>From parttype, company, part
Where (
Part. pcost < 100 AND
Part. pname = knob
Part. TID = parttype. TID AND
Parttype. SID = company. SID) SQL Query 2 (Join)

Find all parts that are sold by a company. In this case the user just specifies the company name. Given the company name you are expected to list out cost for each part that it sells. The cost displayed is sum of the part

Assume the user wants to see all parts sold by the company lappens.

Select sname, pname, pcost + starrif AS partcost
>From company, part, parttype
Where (
Sname = lappens
Parttype. SID = customer. SID AND
Part. TID = parttype. TID)
 
  SQL Query 3 (Division)

Find the company, which sells all parttypes.

The user just wants to see the name of the companies, which sell all the parttypes with their locations and mailing addresses.

Select sname, saddress, semail
Fom company
Where not exists
(Select TID
From parttype
Where TID except
(Select TID
From parttype p2
Where p2. TID = company. TID) SQL Query 4 (Oracle system function)

The customer wants to see all the purchases that he has made within the last 60 days. This means that he wants to see all parts purchased within the last 60 days

Select rfp. Pid, rfp. Quantity, rfp. Cost
>From customer, rfp
Where
Rfp. Cid = customer. Cid AND
Cname = naru
Rfp. Ptime sysdate > 60
 
  SQL Query 5 (Outer Join)

Give me the list of companies that I have brought an item from even if I have purchased an item from the company list it. The user wants to see all the companies including those from whom he has purchased an item.

Select distinct (sname)
>From customer, RFP, company
Where
Rfp. Cid = customer. Cid AND
Cname = naru
RFP. ID = company. ID (+) SQL Query 6 (Sorted output)

Suppose a company wants to find out each user who has purchased a part from his company. The company wants the results in a sorted manner with the user who has purchased the parts listed in decreasing order of the number of parts purchased. The user who purchased the maximum number of parts is to be listed first, and the one who has purchased the least number is listed last.

Assume that the company name is lappens. It wants to see which customer has made the maximum purchases from the company.

Select Cname
>From company, customer, RFP
Where
(Company. ID = RFP. ID AND
Customer. CID = RFP. CID AND
Company. Sname = lappens
Order by Cname SQL Query 7 (Grouping and group aggregation)

Suppose the company is interested in finding only those users who have purchased more than 5 parts. Each part should have cost greater than a certain amount specified by the company.

Select Cname
>From customer, company, RFP
Where
(Company. ID = RFP. ID AND
Customer. CID = RFP. CID AND
Company. Sname = lappens
Group by customer. CID, company. ID, RFP. PID
Having count (*) > 5

                                                                                                            Next