SLIS A-Z Index
People Search
SLIS Calendar

Programs

Courses

Textbooks by Semester

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.