
-- list of example queries from SQL tutorial

SELECT FirstName, LastName, Address, City, State
FROM EmployeeAddressTable; 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000; 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager'; 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY > 40000 AND POSITION = 'Staff'; 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY < 40000 OR BENEFITS < 10000;

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager' AND SALARY > 60000 OR BENEFITS > 12000; 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager' AND (SALARY > 50000 OR BENEFITS > 10000);

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff'); 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000; 

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY NOT BETWEEN 30000 AND 50000; 

SELECT SSN
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'S%'; 

SELECT OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUEOWNERS, ANTIQUES
WHERE BUYERID = OWNERID AND ITEM = 'Chair'; 

SELECT ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME
FROM ANTIQUEOWNERS, ANTIQUES
WHERE ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID AND ANTIQUES.ITEM = 'Chair'; 

SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME
FROM ANTIQUES, ANTIQUEOWNERS
WHERE SELLERID = OWNERID
ORDER BY OWNERLASTNAME, OWNERFIRSTNAME;

SELECT OWN.OWNERLASTNAME AS Last_Name, ORD.ITEMDESIRED AS Item_Ordered
FROM ORDERS ORD, ANTIQUEOWNERS OWN
WHERE ORD.OWNERID = OWN.OWNERID 
AND ORD.ITEMDESIRED IN (SELECT ITEM FROM ANTIQUES)
ORDER BY Item_Ordered;

SELECT SUM(SALARY) AS sum, ROUND(AVG(SALARY),2) AS avg
FROM EMPLOYEESTATISTICSTABLE;

SELECT MIN(BENEFITS) AS min
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager'; 

SELECT COUNT(*) AS count
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Staff'; 

SELECT BUYERID, MAX(PRICE) AS max
FROM ANTIQUES
GROUP BY BUYERID
ORDER BY BUYERID; 

SELECT BUYERID, MAX(PRICE) AS max
FROM ANTIQUES
GROUP BY BUYERID
HAVING MAX(PRICE) > 1000
ORDER BY BUYERID; 

SELECT BUYERID
FROM ANTIQUES
WHERE PRICE > (SELECT AVG(PRICE) + 100 FROM ANTIQUES);

SELECT OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE OWNERID IN (SELECT DISTINCT BUYERID FROM ANTIQUES);

SELECT OWNERFIRSTNAME, OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE EXISTS (SELECT * FROM ANTIQUES WHERE ITEM = 'Chair' AND BuyerID=OwnerID);

SELECT BUYERID, ITEM
FROM ANTIQUES
WHERE PRICE = ALL (SELECT PRICE FROM ANTIQUES);

SELECT ANTIQUEOWNERS.*, ORDERS.*
FROM ANTIQUEOWNERS, ORDERS;

