Course Web Pages - Spring 2012 - LIBR 242-01/10 Greensheet - Sample Codes - SQL Querying
Retrieving/Querying/Selecting Records from Database
1. To retrieve all records from a table, in full columns,
SELECT * FROM CUSTOMER;
2. To retrieve only records meeting certain condition, in full columns,
SELECT * FROM CUSTOMER WHERE CUS_CODE = `101';
3. To retrieve only records meeting certain condition, in some columns,
SELECT CUS_LNAME, CUS_FNAME, CUS_ADDRESS
FROM CUSTOMER
WHERE CUS_CODE = `101';
4. To retrieve correlated (via foreign keys) records from multiple tables,
SELECT CUSTOMER.CUS_CODE, PRODUCT.PROD_NAME
FROM CUSTOMER, INVOICE, PRODUCT
WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE AND
INVOICE.PROD_CODE = PRODUCT.PROD_CODE;
5. To retrieve and summarize records using SQL functions
SELECT CUSTOMER.CUS_CODE, CUSTOMER.CUS_BALANCE,
SUM(LINE.LINE_UNITS * LINE.LINE_PRICE)
COUNT(*), AVG(LINE.LINE_UNITS * LINE.LINE_PRICE)
FROM CUSTOMBER, INVOICE, LINE
WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE AND
INVOICE.INV_NUM = LINE.INV_NUM
GROUP BY CUSTOMER.CUS_CODE, CUS_BALANCE;
where SUM() does the summation across selected records, AVG() computes the average/arithmatic mean across selected records, and COUNT(*) counts the total number of selected records.
6. To correlate records from multiple tables to bring burried and scattered information together and to the surface,
SELECT CHAR_DATE, CHARTER.AC_NUM, MOD_NAME, EMP_LNAME, CUS_LNAME
FROM CHARTER, AIRCRAFT, MODEL, PILOT, EMPLOYEE, CUSTOMER
WHERE CHARTER.AC_NUM = AIRCRAFT.AC_NUM AND
AIRCRAFT.MOD_CODE = MODEL.MOD_CODE AND
PILOT.EMP_NUM = EMPLOYEE.EMP_NUM AND
CHARTER.CHAR_PILOT = PILOT.EMP_NUM AND
CHARTER.CUS_CODE = CUSTOMER.CUS_CODE AND
CHAR_DATE >= '13-NOV-92';
Select here to return to the sample codes page.