Ch 7

1
Ch 7

• Introduction to SQL

2
Learning Objectives

• The basic commands and functions of SQL
• How to use SQL for data administration (to create
  tables, indexes, and views)
• How to use SQL for data manipulation (to add,
  modify, delete, and retrieve data)
• How to use SQL to query a database to extract
  useful information

3
SQL

• --a standard for relational DB
• --used as DDL, DML
• --portable
• Benefits
• .reduced training costs
• .enhanced productivity
• .application portability
• .multiple vendors
• .application longevity
• .cross systems communications

4
CREATE TABLEs..SEE Pg 223 RC ORACLE HANDOUT
and lesson 17 (BF)

• define fields
• field type
• length
• null/not null
• unique
• primary key
• foreign key

5
Create Statement

• Format
• CREATE TABLE tablename
• (col name datatype col_constraints,
• .
• .
• .
• primary key block
• referential constraint
• .
• .
• .)

6
constraints in Create table statements

• Constraint clause can constrain a single column
  or a group of columns in a table
• It helps maintain data integrity
• Primary key
• Foreign key
• Check conditions

7
Two ways to specify constraints

• part of col def
• (col constraint)
• at the end of the table
• (table constraint)

8
Primary key

• A table can have only ONE primary key
• P.K. can not have NULL values
• for single-column primary key, it can be defined
  with a column
• For composite it must be defined as a constraint

9
WORKER (w-name, age, lodging)

• Create table WORKER
• (w_name varchar(25) Primary key,
• age number,
• Lodging varchar(15))
• or
• Create table WORKER (
• w_name varchar(25),
• age number,
• Lodging varchar(15),
• primary key (name))

10
ENROLL(st_name, cl_name, grade)

• PK is st_name, cl_name
• Create table Enrollment
• (ST_name varchar(30) not null,
• cl_name varchar(25) not null,
• grade smallint
• primary key (st_name, cl_name))
• NOTE A concatenated (composite) PK must be
  defined at the END

11
Foreign Key (SEE ORACLE HANDOUT AND PAGE 241
(RC), LESSON 22 (bf)

• known as referential integrity constraint
• ex
• WORKER (name, age, lodging)
• LODGING (lodging_name , No_of_rooms,
  lodging_Address)

12

• Create table WORKER
• (name...
• age...
• lodging..REFERENCES LODGING)
• will automatically reference PK of LODGING
  relation
• or
• foreign key(lodging) references
  LODGING(lodging_name)

13
CHECK constraint lesson 22 (bf)227(RC)

• format
• col type CHECK (condition)
• condition is any valid expression that tests
  True/false.
• ex if age should be between 18 65
• Create table WORKER
• (name..
• age number check (age between 18 and 65),
• lodging...)
• or age...check (age gt 12),
• ex
• class char(20) not null check (lower(class) in
  ('so', 'jr', 'sn') )

14
Data type(see ORACLE handout)

• CHAR(size)   May contain any printable character
  such as 'A', '3', '', or ' ' (blanks). Maximum
  size 255 characters. Default is 1 byte. Padded
  on right with blanks to full length of size.
• VARCHAR2(size) Variable length character string
  having a maximum of size bytes (up to 2000). NOT
  padded with blanks.
• NUMBER For a numeric column of up to 40 digits.
  May be used with or without a size designation.
  You may use this type for integer fields, or for
  fields in which you wish to insert the decimal
  point.
• NUMBER(size) For a NUMBER column of the specified
  size.
• NUMBER(size, d) For a NUMBER column of specified
  size with d digits after the decimal point. For
  example, NUMBER(6,2) could contain nothing larger
  than 9999.99 without generating an error message.
  
• DATE The DATE data type is stored in a special
  internal ORACLE format that includes not just the
  month, day, and year, but also the hour, minute,
  and second. However, for most tables, you will
  not need to be concerned about the time. For
  inserting a date into a table, use the following
  format '10-OCT-06'.

15
exORDER(O, O_date, Promised_date,
C)CUSTOMER(C, name, address, discount)

• CREATE TABLE ORDER
• (ORDERNO NUMBER NOT NULL,
• ORDERDATE DATE,
• PROMISED_DATE DATE,
• CUSTOMER_NO CHAR925) NOT NULL REFERENCES
  CUSTOMER,
• PRIMARY KEY(ORDERNO))

16
Entering Data in tables lesson 15 (BF), p230(RC)
and oracle handout

• Format
• INSERT INTO table-name VALUES ( val1, val2,
  val3...)
• values must be in the same order as the cols in
  the table structure
• char must be in single quotes
• date must be in quotes and default format (see
  ORACLE handout)
• Ex ORDER(O, O_date, Promised_date, C)
• Sqlgtinsert into order values (61396,6-jun-06',
  '29-jun-06',1256)
• you can also insert a NULL value in a column..col
  will be left empty for this row
• Ex
• insert into order
• values(61396,'6-jun-06',null,1256)

17
SQL

• SQL can be used to manipulate data (DML)
• format
• SqlgtSELECT col name(s)
• FROM table name(s)
• Where condition(s)
• Group BY..HAVING.
• UNION\INTERSECT\MINUS
• ORDER BY....

18
Assume following tables

• Customer
• ( Cust_numb, Cust_name, Cust_address,
  cust_state,Cust_balance,Credit_limit, sales_numb)
• Salesperson
• (Sales_numb, sales_name, Sale_add, sales_state,
  commission_rate, sales)
• Order
• (Order_numb, Order_date, Cust_numb)

19
Working with single table

• List names and balances of each customer
• SqlgtSelect cust_name, cust_balance
• From Customer
• If you want all attributes
• Sqlgtselect from customer
• Is a wild card and selects everything

20
Remove duplicate entries

• Select name of cities our customers live in
• Sqlgtselect distinct cust_state
• From customer

21
LOGICAL TESTS AGAINST A SINGLE VALUE

• Used with-- where clause
• Creates a horizontal subsection
• Mathematical operators
• , gt, gt, lt, lt, ltgt or !

22
List the name of customer whose customer number
is 9833

• Sqlgt select cust_name
• From customer
• Where cust_numb 9833

23
List the names of salesperson who live in MD

• Sqlgtselect sales_name
• From salesperson
• Where upper(sales_state) MD
• Ex give the customers who ordered before
  February 15, 2007.
• Sqlgtselect cust_numb
• From order where order_date lt15-Feb-2006

24
LOGICAL operators

• And
• Or
• Not
• List the names of salespeople who live in new
  york and earn more than 5 as commission
• Sqlgtselect sales_name
• From salesperson
• Where upper(sales_ad) NEWYORK AND
  commission_rate is gt 5
• List the names of salespeople who do not live in
  new york and earn more than 5 as commission

25
Case Sensitivity test

• Upper
• Lower

26
SPECIAL OPERATORS

• In
• Between
• not in
• not between
• Ex
• WHERE CITY IN ('CHICAGO','BALTIMORE','TAMPA')
• WHERE AGE IN (20,21,22)
• same as upper(city) CHICAGO OR upper(city)
  BALTIMORE OR upper(city ) TAMPA

27
Renaming columns with Aliases/Can do arithmetic
calculations on numeric attributesuseful when
doing calculations

• format
• Sqlgtselect colname AS alias
• from table name
• where. .
• Ex give commission of salespeople.
• Sqlgtselect sales_name, salescommission_rate as
  commission
• From salesperson
• EX
• List the names of customers whose credit is
  greaterthan 2000 but less than 10000
• Credit between 2000 and 10000

28
Calculations

• , -, /, ,
• Order of calculations
• Parentheses,
• ,
• and /,
• and -

29
Comparisons

• Numeric
• Based on numeric values
• Character
• Left to right based on collating sequence of the
  computer
• Smithson gt Smith

30
Special operator

• Like
• Used to find patterns

• Allows you select tuples that have similar
  characters in strings
• Any of char.
• For ONE character
• List names of customers whose names end with an s
• SELECT cust_name
• FROM customer
• WHERE lower(cust_name) like s

31
Find the names of salesperson that have a c
followed by an s

• SORTING
• output from a query can be ordered
• Must always be the last line of a query
• ORDER BY colname asc
• desc
• default is ascending
• Ex sort all customers by state and descending
  order of their credit limit
• Sqlgtselect
• From customer
• Order by cust_state, credit_limit desc

32
Exists condition

• Main query
• Sub query
• If subquery returns values , then ONLY main query
  is executed
• Format
• Select col(s)
• From table name
• Where exists ( sub query)

33
BUILT-IN-FUNCTIONS

• Max
• Min
• Sum
• Avg
• Ex
• Select max(credit), cust_name from customer
• Has no meaning because ename is an array and
  max(salary) is a scalar cannot associate names
  with max(sal)
• Can not mix built-in functions with other
  attributes, exception with groups

34
Count

• Used to tally number of not-null values
• Format
• Count ()
• Count(distinct attributename)
• Count (all attribute name)
• count the number of customer in maryland or
  virginia
• Sqlgt select count()
• from customer
• where upper(cust_state) In (MD, VA)

35
Find maximum credit limit of customer whose names
contain at least one o

• Select max(credit_limit), cust_num
• From customer
• Where upper(cust_name) like O

36
Group By

• think of grouping as categorizing P260(RC),
  lesson 10(bf)
• Allows grouping by rows and then calculate
  statistics on groups
• group by state
• Will group by state
• Group by state, city
• Will group by state first, then by city within
  state
• Ex get the maximum credit limit by state
• Sqlgtselect credit_limit, cust_state
• From customer
• Group by cust_state

37
Condition on groups Having clause

• Having puts a condition on groups
• Ex select max credit limit by state where there
  are atleast two customers
• Sqlgtselect max(credit_limit), cust_state
• From customer
• group by cust_state
• Having count() gt2

38
Copying Part of table

• Allows you to break tables in parts
• Two steps
• 1.Create NEW table
• 2. Use SQL to fill up the data
• Format
• Insert into target_table_name (attribute names)
• From source_table

39
Second method

• Create new table and select rows directly from
  source table in single query
• Format
• Create table target_tablename
• Select attribute names
• From source_table

40
Create table credit for customers

• Method
• Create table credit
• (c_name char(20) primary key,
• C_limit number (8,2))
• Insert into credit (c_name, c_limit)
• Select cust_name, cust_limit
• From customer
• 2nd Method
• Create table credit as
• Select cust_name as c_name, cust_limit as c_limit
• From customer

41
Altering Table structure (see ORACLE Handout),
lesson 17, 247 (RC)

• all changes in table structure are done thru
  ALTER command
• MODIFY allows changing existing column
• ADD allows adding new fields
• DROP to delete a column

42
Alter format

• ALTER TABLE tablename
• ADD (col name...,
• colname...)
• or
• ALTER TABLE tablename
• MODIFY (col name..)
• or
• ALTER TABLE tablename
• Drop (col name..)

43
Rules for ADDition

• can add a col anytime if not null is not
  specified
• can add multiple col at one time
• can add a NULL column in three steps
• add the col without not null
• fill every row
• modify col to not null

44
Ex add a new column soc-sec_no to customer

• alter table customer
• add (soc_sec_no char(14))
• step2
• update table customer
• set soc_sec_no 123-32-4444
• step 3
• alter table customer
• modify (soc_sec_no not null)

45
Changing column structure

• Ex increase credit limit to 6,2
• Alter table customer
• Modify (credit_limit number(6,2))

46
Creating views (p 262) ch 18(BF)

• Users view of data
• Create or REPLACE VIEW view-name (col name) AS
• SQL query
• used if this view already exists

47
Create a view of customers with credit limit of
gt5000

• Create view high_limit as
• select cust_name from customer where
• Credit_limit gt500

48

• COLUMN NAMES MUST BE specified if
• ..column names are derived
• ..two or more cols have the same name
• Ex create a view that keeps track of credit
  averages
• Create view c_avg (cust_avg, cust_name) as
• Select cust_name, avg(credit_limit)
• From customer
• Group by cust_name)

49
Drop a view/advantages of views

• Format
• Drop view view_name
• Advantages of views
• provide data independence
• different users can view data differently
• provide security