Basic SQL syntax

Create tables

CREATE TABLE tablename (

Columnname1 datatype [constraint]*

Columnname2 datatype [constraint]

…

Columnamen datatype [constraint]

PRIMARY KEY (columnname)

FOREIGN KEY (columnname) REFERENCES tablename(columnname))

Selection query

SELECT columnlist

FROM tablelist

[WHERE conditionlist]

Join query

SELECT columnlist

FROM table1 INNER JOIN table2 ON table1.column = table2.column

[WHERE conditionlist]

Update query

UPDATE tablename

SET columnname = value

[WHERE conditionlist]

*[] indicates optional

Advanced SELECT queries

SELECT columnlist

FROM tablelist

[Where conditionlist]

[ORDER BY columnlist [ASC |DESC]]

[GROUP BY columnlist [Having conditionlist]]

e.g. list the price of product in the ascending order

SELECT P_PRICE, P_DESCRIPT FROM PRODUCT

ORDER BY P_PRICE

e.g. list the average price of product supplied by vendor grouped by vendor where price is cheaper than 10

SELECT AVG(P_PRICE), V_CODE FROM PRODUCT

GROUP BY V_CODE

HAVING AVG(P_PRICE) < 10

Aggregate functions

• COUNT
• MIN
• MAX
• SUM
• AVG

e.g. returns the highest price of product

SELECT MAX(P_PRIC) FROM PRODUCT

e.g. returns the number of state where VENDOR is located

SELECT COUNT(V_STATE) FROM VENDOR

Nested query

You can embed another query in one query

e.g. list code, description, and price of the most expensive product

SELECT P_CODE, P_DESCRIPT, P_PRICE

FROM PRODUCT

WHERE P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT);

e.g. return the number of unique STATEs where VENDOR is located

SELECT COUNT(V_STATE) FROM (SELECT DISTINCT V_STATE FROM VENDOR)

---

Lab

1. Write down SQLs that answer the following questions/requests.

Use the file P:\geog495_aut05\Ch06_SaleCo.mdb.

(1) Can you give me more information on products whose available units (P_ONHAND) is larger than twice the minimum units (P_MIN)?

(2) List the product satisfying the condition (1) in the order of price

(3) Show me the list of customers whose area code of their phone number starts with 7.

(4) Obtain the unique list of product satisfying the condition (2)

(5) Give me the unique list of those States where vendors are located.

(6) Highest price of product?

(7) How many States from (4)?

(8) How many vendors are associated with all products in our database?

(9) What is the total sum of customer balance?

(10) What is the average product price? Show me the list of product price, description, vendor code where its price is more than average in the descending order

(11) Give me the list of average price of product grouped by vendor code?

(12) Create a new table called P2 and populate the table with values from PRODUCT where discount rate (P_DISCOUNT) is zero

---

The questions above check your ability to use

(1) comparative/arithmetic operator

(2) SELECT columnlist FROM tablelist WHERE conditions

ORDER BY columnlist

(3) Left(column, n)

(4) DISTINCT

(5) DISTINCT

(6) MAX; alias

(7) COUNT; nested query

(8) COUNT; nested query; table join

(9) SUM

(10) AVG(); nested query

(11) SELECT columnlist FROM tablelist WHERE conditions

GROUP BY columnlist

(12) INSERT INTO with nested select query

---

IMPORTANT!

In your project (representative queries in the final report),

MAKE SURE that at least one join, at least one nested query, at least one appropriate use of an aggregate function within a select statement, and at least one group-by are re included within your set of queries. These must be potentially meaningful/useful to users of your database.