Notes

SELECT CUSTOMERS WHO HAVE BOUGHT MORE THAN 1 'Woodyard lizard'
*CUSTOMER NUMBER
*QUANTITY
*VALUE
*COMPANY :

SELECT CUSTOMER.CNO, SUM(DETAIL.QTY), SUM(DETAIL.LTOTAL), CUSTOMER.COMPANY
FROM RK0.CUSTOMER, RK0.PARTS, RK0.DETAIL, RK0.INVOICES
WHERE PARTS.DESCRIPT LIKE '%Woodyard lizard%' AND PARTS.PNO=DETAIL.PNO AND DETAIL.INO=INVOICES.INO AND INVOICES.CNO=CUSTOMER.CNO
GROUP BY CUSTOMER.CNO, CUSTOMER.COMPANY
HAVING SUM(DETAIL.QTY)>1


SELECT CUSTOMERS WHICH HAVE BOUGHT NOTHING (HAVE NO INVOICE)
*CUSTOMER NUMBER
*COMPANY :

SELECT CUSTOMER.CNO, CUSTOMER.COMPANY
FROM RK0.CUSTOMER
MINUS
SELECT CUSTOMER.CNO, CUSTOMER.COMPANY
FROM RK0.CUSTOMER, RK0.INVOICES
WHERE CUSTOMER.CNO=INVOICES.CNO

SELECT CUSTOMER.CNO, CUSTOMER.COMPANY
FROM RK0.CUSTOMER
WHERE CUSTOMER.CNO NOT IN
(SELECT INVOICES.CNO
FROM RK0.INVOICES)

SELECT DISTINCT CUSTOMER.CNO, CUSTOMER.COMPANY
FROM RK0.CUSTOMER
WHERE NOT EXISTS
(SELECT *
FROM RK0.INVOICES
WHERE INVOICES.CNO=CUSTOMER.CNO)

SELECT THE STATES WHICH HAVE BOUGHT NOTHING
*STATE

SELECT CUSTOMER.STATE
FROM RK0.CUSTOMER
MINUS
SELECT CUSTOMER.STATE
FROM RK0.CUSTOMER, RK0.INVOICES
WHERE CUSTOMER.CNO=INVOICES.CNO


SELECT STATES IN WHICH EVERY CUSTOMER HAS BOUGHT SOMETHING (HAS AT LEAST ONE INVOICE)

SELECT CUSTOMER.STATE
FROM RK0.CUSTOMER
MINUS
SELECT CUSTOMER.STATE
FROM RK0.CUSTOMER, RK0.INVOICES
WHERE NOT EXISTS
(SELECT *
FROM RK0.INVOICES
WHERE INVOICES.CNO=CUSTOMER.CNO)

SELECT CUSTOMERS (CNO, COMPANY, QTY OF INVOICES) WHO HAVE LESS THAN 2 INVOICES
(HINT:USE SET OPERATOR)


sinav 2 part
1/25 test
2/bunun gibi selectli seyler minik detaylara takilmayacak ama genel konsepti anlamamiz lazim