Winter BEDU496 2006
Sample Tables
Jae Park
1
BEDU496 Winter 2006
Find all product information SELECT * FROM PRODUCT_T;
Jae Park
2
BEDU496 Winter 2006
Find some information (three attributes in this case) of all products
SELECT Standard_Price, Product_Description, Product_ID FROM PRODUCT_t;
OR
SELECT PRODUCT_t.Standard_Price, PRODUCT_t.Product_Description, PRODUCT_t.Product_ID FROM PRODUCT_t;
Jae Park
3
BEDU496 Winter 2006
Find products with standard price less than $275
SELECT PRODUCT_ID, STANDARD_PRICE FROM PRODUCT_T WHERE STANDARD_PRICE < 275;
Jae Park
4
BEDU496 Winter 2006
Using the COUNT aggregate function to find totals
SELECT COUNT(*) FROM ORDER_LINE_T WHERE ORDER_ID = 1004;
Jae Park
5
BEDU496 Winter 2006
Functions such as COUNT, MIN, MAX, SUM, and AVG. SELECT AVG(STANDARD_PRICE) AS AVERAGE FROM PRODUCT_T;
SELECT MIN(STANDARD_PRICE) AS AVERAGE FROM PRODUCT_T;
Jae Park
6
BEDU496 Winter 2006
AND, OR, and NOT Operators for customizing conditions in WHERE clause
SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE FROM PRODUCT_T WHERE (PRODUCT_DESCRIPTION LIKE *Desk OR PRODUCT_DESCRIPTION LIKE *Table) AND STANDARD_PRICE > 300;
Jae Park
7
BEDU496 2006
Distinct Winter values
SELECT DISTINCT Order_ID FROM ORDER_LINE_T;
Jae Park
8
BEDU496 Winter 2006
Using Range for qualification
SELECT PRODUCT_t.Product_Description, PRODUCT_t.Standard_Price FROM PRODUCT_t WHERE PRODUCT_t.Standard_Price Between 200 And 300;
Jae Park
9
BEDU496 Winter 2006
Sort the results first by STATE, and within a state by CUSTOMER_NAME
SELECT CUSTOMER_NAME, CUSTOMER_CITY, CUSTOMER_STATE FROM CUSTOMER_t WHERE CUSTOMER_STATE IN ("FL","TX","CA","HI")
ORDER BY CUSTOMER_STATE, CUSTOMER_NAME;
Jae Park
10
BEDU496 Winter 2006
For use with aggregate functions
Scalar aggregate: single value returned from SQL query with aggregate function Vector agg...