COMP 231 Database Management Systems

1
COMP 231 Database Management Systems
Lab 3More Practice on SQL
Fall, 2006
2
Lab Objective

• Using Oracle 10g Instantclient
• Oracle Data Type
• More Practice on SQL

3
Oracle 10g Instant Client

• Oracle client for the Windows environment
• No need to set the ORACLE_HOME
• Located at
• C\Program Files\Oracle\instantclient
• Connect to Oracle using
• gtsqlplus ltaccountgt_at_comp231.cs.ust.hk

4
Oracle Data Types

• Oracle supports these data types
• char(n) Fixed-length character string with n
  characters long. The maximum size for n is 2000
  bytes in Oracle 8. Note that a string of type
  char is always padded on right with blanks to
  full length of n and therefore can be memory
  consuming
• varchar2(n) Variable-length character string.
  Maximum n is 4000 in Oracle 8. Only the bytes
  used for a string require storage
• number(o, d) Numeric data. o is the overall
  number of digits, d is number of digits to the
  right of the decimal point. Use number (without
  specifying o and d) for floating point data
• date Date and time. The default format is
  DD-MON-YY, e.g. 12-Feb-02

5
Oracle Date and Time

• Oracle supports both date and time using one
  datatype, DATE.
• DATE type is used in the same way as other
  built-in types.
• create table event(
• time DATE,
• location VARCHAR2(50)
• )
• To insert a DATE value, a string is converted by
  a TO_DATE function according to some DATE format
• No need to call TO_DATE explicitly
• insert into event values ('31-May-06','PG Hall
  II')
• Explicit call
• insert into event values
• (to_date('2006/05/31120000AM',
  'yyyy/mm/ddhhmissam'), 'PG HALL II')

6
Oracle DATE Format

• When a DATE value is displayed, its converted by
  a TO_CHAR function according to some DATE format.
• By default the format is DD-MON-YY
• SQLgt select time from event
• TIME
• ---------------
• 31-MAY-06
• The display format can be customized.
• SQLgtselect TO_CHAR(time,'yyyy/mm/ddhhmissam')
  from event
• TO_CHAR(TIME,'YYYY/MM
• --------------------------------------
• 2006/05/31120000am

7
Popular Format Options for DATE
MM Numeric month (e.g., 07) MON Abbreviated
month name (e.g., JUL) MONTH Full month name
(e.g., JULY) DD Day of month (e.g.,
24) DY Abbreviated name of day (e.g.,
FRI) YYYY 4-digit year (e.g., 1998) YY Last 2
digits of the year (e.g., 98) RR Like YY, but
the two digits are rounded'' to a year in the
range 1950 to 2049. Thus, 06 is considered
2006 instead of 1906 AM (or PM) Meridian
indicator HH Hour of day (1-12) HH24 Hour of
day (0-23) MI Minute (0-59) SS Second (0-59)
8
SQL

• Its time to have more practice on SQL!
• Run lab3-create.sql and lab3-insert.sql to create
  tables and insert some values (use lab3-drop.sql
  to drop all tables)
• URL are http//course.cs.ust.hk/comp231/fall06/use
  rs/zhaojing/lab3/create3.sql
• http//course.cs.ust.hk/comp231/fall06/users/zhao
  jing/lab3/insert3.sql
• http//course.cs.ust.hk/comp231/fall06/users/zhao
  jing/lab3/drop3.sql
• There are 5 tables
• Motorcycle(model, maker, capacity, list_price,
  quantity)
• Supplier(sno, sname, city, address, phone_no)
• Customer(cno, cname, age, city, phone_no)
• Supply(sno, model, quantity, purchase_price,
  supply_date)
• Sales(cno, model, selling_price, sales_date)

9
Search Conditions

• Examples of search conditions
• select from Motorcycle where maker HONDA
• select from Motorcycle where capacity gt 2
• select from Supply where quantity between 10
  and 20
• select from Motorcycle where maker in (HONDA,
  YAMAHA)
• select from Customer where phone_no like
  0852
• select cname from Customer where city is null
• select cname from Customer where city is not null
• select model, list_price from Motorcycle where
  capacity lt 4 or capacity gt 5
• Select model from Motorcycle where not maker
  HONDA

10
Arithmetic Expressions

• Examples of arithmetic operators
• list total amount for each piece of supply
• select sno, model, purchase_pricequantity
  amount, supply_date
• from supply
• Whatre the average purchase price for each
  model?
• select model, list_price/(1markup) avg_price
• from Motorcycle

11
Aggregate Functions

• Examples on aggregate functions
• List total number of motorcycles
• select sum(quantity) from Motorcycle
• Whats the un-weighted average price for all
  model?
• select avg(list_price) from Motorcycle
• Whats the weighted average list price for all
  model?
• select sum(list_pricequantity)/sum(quantity)
  from Motorcycle
• Tell me the maximum price
• select max(list_price) from Motorcycle
• Compute total number of motorcycle sold
• select count() from Sales

12
Group By Clause

• Examples of group by clause
• Compute the un-weighted average price for each
  brand
• select maker, avg(list_price) from Motorcycle
• group by maker
• Compute number of customers for each city
• select city, count()
• from Customer
• group by city

13
Having Clause

• Examples of having clause
• List cities that have customers average age gt 30
• select city, avg(age)
• from Customer
• group by city
• having avg(age) gt 30
• List models that have sold more than 1 units in
  February, 2000

14
Order By clause

• Examples of order by clause
• Select model, list_price from Motorcycle
• order by list_price
• Select model, list_price from Motorcycle
• order by list_price desc
• Select model, list_price, quantity from
  Motorcycle
• order by list_price desc, quantity

15
Joins

• Examples of joins
• Select
• from Motorcycle M, Sales S
• where M.model S.model
• Join operators
• The join operators that determin the basis on
  which columns will be matched are the relational
  opeators including , gt gt, lt, lt, !, !gt, !lt
• Most common join is based on equality, i.e.
  equijoins.

16
Self-join

• Examples of Self-join
• Compare the average price for motorcycles that
  have same capacity but made by HONDA and YAMAHA
  respectively
• Self-join compare values within a column of ONE
  table and combine rows that quatify

17
Outer Joins

• Out joins
• Use () next to a column to indicate this
  column can be padded with nulls in the outer
  join result
• Left outer join
• select C.cname, S.model
• from Customer C, Sales S
• where C.cno () S.cno
• Right outer join
• select S.selling_price, M.model
• from Sales S, Motorcycle M
• where S.model M.model ()

18
Subquery

• Subqueries are queries that appear within a where
  or having clause of antoher sQL statement OR in
  the select list of a statement
• Examples
• List all models that cost more than the average
  price

19
Quantified Predicate Subqueries

• Quantified predicate subqueries can be introduced
  with the keywords exists, in, not in,
  any all, etc.
• Finds all the names of customers who buy
  motorcycles
• List every model that is more expensive than any
  HONDA motor

20
Multi-level Nested Queries

• Theoretically, there can be as many levels of
  nested subqueries as needed in one query. But
  practically, theres a limit of 16 nesting levels
  in alsmost all DBMS systems.
• Example
• List the maker of every models sold to customers
  in Hong Kong