SQL Tutorial

1
Structured Query Language The Basics

• By Hitesh Sahni
• www.hiteshsahni.com

2
What Were Going to Cover

• Overview of SQL (This may be review for some of
  you)
• Data Definition Language
• Creating tables (well just talk about this)
• Data Manipulation Language
• Inserting/Updating/Deleting data
• Retrieving data
• Single table queries
• Where
• Joins
• Grouping

3
SQL

• SQL is a data manipulation language.
• SQL is not a programming language.
• SQL commands are interpreted by the DBMS engine.
• SQL commands can be used interactively as a query
  language within the DBMS.
• SQL commands can be embedded within programming
  languages.

4
3 Types of SQL Commands

• Data Definition Language (DDL)
• Commands that define a database - Create, Alter,
  Drop
• Data Manipulation Language (DML)
• Commands that maintain and query a database.
• Data Control Language (DCL)
• Commands that control a database, including
  administering privileges and committing data.

5
Data Manipulation Language (DML)

• Four basic commands
• INSERT
• UPDATE
• DELETE
• SELECT

6
Inserting Data into a Table
INSERT INTO tablename (column-list) VALUES
(value-list) PUTS ONE ROW INTO A TABLE INSERT
INTO COURSE (COURSE_CODE, COURSE_NAME,
CREDIT_HOURS) VALUES (MIS499,ADVANCED
ORACLE,4)
7
More on Inserting Data
INSERT INTO COURSEVALUES (MIS499,ADVANCED
ORACLE,4) INSERT INTO COURSE(COURSE_NAME,
COURSE_CODE, CREDIT_HOURS)VALUES (ADVANCED
ORACLE,MIS499,4)
COLUMN LIST IS OPTIONAL IF YOU PLAN TO INSERT A
VALUE IN EVERY COLUMN AND IN THE SAME ORDER AS
IN THE TABLE
COLUMN LIST IS NEEDED TO CHANGE THEORDER - MUST
MATCH VALUE LIST
NOTE - TABLE STILL HAS THE ORIGINAL COLUMN ORDER
8
Inserting Null Data
INSERT INTO COURSE (COURSE_CODE,
CREDIT_HOURS) VALUES (MIS499,4) INSERT INTO
COURSE VALUES (MIS499,,4) INSERT INTO
COURSE VALUES (MIS499,NULL,4)
COLUMN LIST IS NEEDED IF YOU PLAN TO LEAVE OUT A
VALUE IN THE VALUE LIST
COLUMN LIST CAN BE OMITTED IF YOU PUT IN A BLANK
VALUE
THE NULL KEYWORD CAN BE USED TO CREATE A
BLANK COLUMN
ALL OF THESE ASSUME THAT THE DATABASE ALLOWS THE
COLUMN TO BE NULL. YOU CANNOT LEAVE PRIMARY KEYS
AND FOREIGN KEYS BLANK
9
Inserting and Integrity Constraints
SQLgt INSERT INTO SECTION VALUES
('1234','MIS333','10-12','MW','COPE101','20000000
0') INSERT INTO SECTION VALUES
('1234','MIS333','10-12','MW','COPE101',
ERROR at line 1 ORA-02291 integrity
constraint (ORA40.SYS_C00337) violated - parent
key not found
COURSE COURSE_CODE KEY COURSE_NAME CREDIT_HOURS
SECTION CALL_NUMBER KEY COURSE_CODE SECTION_TIME
SECTION_DAYS SECTION_ROOM INSTRUCTOR_ID
INSTRUCTOR INSTRUCTOR_ID KEY INSTRUCTOR_NAME INST
RUCTOR_OFFICE
10
Entity Integrity Problems
SQLgt INSERT INTO COURSE VALUES
('MIS220','NEW',4) insert into course values
('MIS220','NEW',4) ERROR at line
1 ORA-00001 unique constraint
(ORA40.SYS_C00335) violated
11
Deleting Data
Be careful!! This deletes ALL of the rows in your
table. If you use this command in error, you can
use ROLLBACK to undo the changes.
DELETE COURSE DELETES ALL ROWS DELETE COURSE
WHERE COURSE_CODE MIS220 DELETES SPECIFIC
ROWS (MORE TYPICAL) DELETE COURSE WHERE
HOURS4 DELETES A GROUP OF ROWS DELETE COURSE
WHERE HOURSlt4
12
Deleting and Integrity Constraints
SQLgt DELETE COURSE WHERE COURSE_CODE'MIS220'
DELETE COURSE WHERE COURSE_CODE'MIS220'
ERROR at line 1 ORA-02292 integrity
constraint (ORA40.SYS_C00341) violated - child
record found
13
Updating Data
UPDATE COURSE SET HOURS5 CHANGES EVERY
ROW UPDATE COURSE SET HOURS5 WHERE
COURSE_CODEMIS220 CHANGES ONE ROW (MORE
TYPICAL) UPDATE COURSE SET HOURS3 WHERE
COURSE_CODE LIKE MIS CHANGES A GROUP OF ROWS
14
Updating and Integrity Constraints
YOU CAN CHANGE THE VALUE OF A FOREIGN KEY AS LONG
AS THE NEW VALUE ALSO COMPLIES WITH REFERENTIAL
INTEGRITY CONSTRAINTS. PRIMARY KEY VALUES CAN BE
UPDATED AS LONG AS THERE ARE NO ROWS IN OTHER
TABLES WITH FOREIGN KEYS WITH THE SAME VALUE
DOES NOT MATTER IF CONSTRAINT IS RESTRICTED OR
CASCADED
15
Integrity Error
SQLgt UPDATE COURSE SET COURSE_CODE'MIS221 WHER
E COURSE_CODE'MIS220' UPDATE COURSE
ERROR at line 1 ORA-02292 integrity
constraint (ORA40.SYS_C00341) violated - child
record found
16
Rollback and Commit
CHANGES TO DATA ARE TEMPORARY DURING YOUR SQLPLUS
SESSION DOES NOT APPLY TO CHANGES IN DATABASE
STRUCTURE - ALTER... BEFORE LEAVING SQLPLUS, YOU
CAN REVERSE THEM
APPLIES TO INSERTS, UPDATES, AND DELETES
17
Rollback and Commit

• SQLgtROLLBACK
• Rollback complete.
• REVERSES ALL CHANGES TO DATA MADE DURING YOUR
  SESSION
• SQLgtCOMMIT
• MAKES ALL CHANGES TO THIS POINT PERMANENT
• POINTS AT WHICH COMMIT IS ISSUED, DEFINE EXTENT
  OF ROLLBACK
• ROLLBACK REVERSES EVERY CHANGE SINCE THE LAST
  COMMIT
• EXITING SQLPLUS ISSUES A COMMIT

18
SQL for Retrieving Data from One Table

• SELECT column_name, column_name,
• FROM table_name
• WHERE condition/criteria
• This statement will retrieve the specified field
  values for all rows in the specified table that
  meet the specified conditions.
• Every SELECT statement returns a recordset.

19
Conceptual Evaluation Strategy

• Semantics of an SQL query defined in terms of
  the following conceptual evaluation strategy
• Compute the cross-product of relation-list.
• Discard resulting tuples if they fail
  qualifications.
• Delete attributes that are not in target-list.
• If DISTINCT is specified, eliminate duplicate
  rows.
• This strategy is probably the least efficient way
  to compute a query! An optimizer will find more
  efficient strategies to compute the same answers.

20
WHERE Conditions
SELECT FROM COURSE WHERE COURSE_CODE LIKE
MIS SELECT FROM COURSE WHERE CREDIT
HOURS BETWEEN 3 AND 5 SELECT FROM
CUSTOMER WHERE BALANCE lt CREDIT_LIMIT
USE TO SUBSTITUTE FOR ANY STRING
3 AND 5 ARE INCLUDED
YOU CAN COMPARE TWO COLUMNS
21
More WHERE Conditions
SELECT FROM CUSTOMERWHERE STATE IN
(OH,WV,KY) SELECT FROM CUSTOMERWHERE
(CREDIT_LIMIT - BALANCE) lt1000
LIST OF SPECIFIC VALUES TO LOOK FOR
CAN MANIPULATE NUMBER VALUES MATHMATICALLY
22
AND/OR/NOT Conditions
SELECT FROM CUSTOMER WHERE BALANCE gt500 AND
BALANCElt1000 SELECT FROM CUSTOMER WHERE
STATE OH OR CREDIT_LIMITgt1000 SELECT
FROM CUSTOMER WHERE NOT (STATEOH)
TWO COMPARISONS ON THE SAME COLUMN
TWO COMPARISONS ON THE DIFFERENT COLUMNS
SAME AS STATEltgtOH
23
More on AND/OR/NOT
SELECT FROM CUSTOMERWHERE STATE OH OR
(CREDIT_LIMIT1000 AND BALANCE lt500)
Use parentheses to make complex logic more
understandable.
CUST STATE LIMIT BAL A OH 1000 600 B WV 1000
200 C OH 500 300 D OH 1000 200 E KY 1300
800 F KY 1000 700 G MA 200 100 H NB 1000
100
Who will be selected??
24
SQL - Other Features

• - All columns in a table
• Aliases
• SELECT EmployeeID, LastName, FirstName, BirthDate
  AS DOB FROM Employee
• SELECT EmployeeID, LastName, FirstName, FROM
  Employee AS E
• Dot Notation - ambiguous attribute names
• SELECT Customer.LName, E.Lname
• FROM Customer, Employee AS E
• WHERE ...

25
SQL - Other Features

• DISTINCT
• Arithmetic operators , -, , /
• Comparison operators , gt, gt, lt, lt, ltgt
• Concatenation operator
• Substring comparisons , _
• BETWEEN
• AND, OR

26
SQL - Other Features

• ORDER BY Clause
• UNION, EXCEPT, INTERSECT
• IN

27
SQL for Retrieving Data from Two or More Tables

• SQL provides two ways to retrieve data from
  related tables
• Join - When two or more tables are joined by a
  common field.
• Subqueries - When one Select command is nested
  within another command.

28
SQL - Joins

• Joins
• The WHERE clause is used to specify the common
  field.
• For every relationship among the tables in the
  FROM clause, you need one WHERE condition (2
  tables - 1 join, 3 tables - 2 joins)

SELECT C.Cust_ID, Comp_Name, Country,OrderID FROM
Customer AS C, Order AS O WHERE C.Cust_ID
O.Cust_ID AND Country USA
29
SQL - Joins

• Inner Join - records from two tables are
  selected only when the records have the same
  value in the common field that links the tables
  (the default join).
• Outer Join - A join between two tables that
  returns all the records from one table and, from
  the second table, only those records in which
  there is a matching value in the field on which
  the tables are joined.

30
Multi-Table Queries Views
Getting tables into 3NF eliminates unnecessary
redundancy, BUT now we need data from multiple
tables to create some forms and reports.
TRANSCRIPT
STUDENT 444-44-4444 NAME JOE
STUDENT CODE NAME HOURS GRADE QUAR/YR MIS220 FI
LE PROC 4 A 389 ZOO100 BIOLOGY 3 B 288 PSY280 EXP
PSY 4 B 190
31
Two-Table Query
DESIRED OUTPUT CALL-NUMBER 1234 COURSE_CODE MIS3
80 SECTION_ROOM COPE012 INSTRUCTOR_ID 111111111 IN
STRUCTOR_NAME DAY
REQUIRED NAVIGATION
SECTION CALL_NUMBER COURSE_CODE SECTION_TIME SECT
ION_DAYS SECTION_ROOM INSTRUCTOR_ID
INSTRUCTOR INSTRUCTOR_ID INSTRUCTOR_NAME INSTRUC
TOR_OFFICE
JOIN
SECTION 1234 MIS380 8-10 WF COPE012 111111111
INSTRUCTOR 111111111 DAY COPE290A
32
SELECT Command with Join
SELECT CALL_NUMBER, COURSE_CODE,
SECTION_ROOM, SECTION.INSTRUCTOR_ID,
INSTRUCTOR_NAME FROM SECTION, INSTRUCTOR WHERE
SECTION.INSTRUCTOR_ID INSTRUCTOR.INSTRUCTOR_
ID
Two Tables ? One Join
THE WHERE CLAUSE IS USED TO TELL ORACLE HOW TO
MATCH ROWS BETWEEN THE TWO TABLES REQUIRES A
COMMON KEY FOR COLUMN NAMES WHOSE LOCATION IS
AMBIGUOUS, YOU MUST SPECIFY A TABLE NAME - SEE
INSTRUCTOR_ID
33
Results
CALL COURS SECTION INSTRU INSTR_NAME ----
-------- -------- ----------
------------------- 0030 MIS300 COPE112
500000000 SUTHERLAND 0031 MIS300 COPE112
260000000 CHEN 0032 MKT301 COPE633
180000000 KIRCH 0033 MKT301 COPE107
180000000 KIRCH 0034 BUSL255 COPE001
260000000 CHEN 0035 OPN310 COPE107
190000000 CUTRIGHT 0036 OPN310 COPE108
240000000 JDAY
34
Joining More Than Two Tables
STUDENT 444-44-4444 NAME JOE
STUDENT CODE NAME HOURS GRADE QUAR/YR M
IS220 FILE PROC 4 A 389 BIO100 BIOLOGY
3 B 288 PSY280 EXP PSY 4 B 190
TRANSCRIPT STUDENT_NUMBER COURSE_CODE GRADE QUAR_
YR
STUDENT STUDENT_NUMBER STUDENT_NAME STUDENT_ADD
RESS STUDENT_PHONE MAJOR
COURSE COURSE_CODE COURSE_NAME CREDIT_HOURS
35
SELECT Command
SELECT STUDENT.STUDENT_NUMBER, STUDENT_NAME,
TRANSCRIPT.COURSE_CODE, GRADE, QUAR_YR,
COURSE_NAME, CREDIT_HOURS FROM STUDENT,
TRANSCRIPT, COURSE WHERE STUDENT.STUDENT_NUMBER
TRANSCRIPT.STUDENT_NUMBER AND
TRANSCRIPT.COURSE_CODE COURSE.COURSE_CODE

Three Tables ? Two Joins
The number of joins is always one less than the
number of tables involved in the query
36
Results
STUD STUDENT_NAME COUR GR QUA COURSE_NAME
CREDIT 1121 TRENT RAZEK MIS320 A 1/91
SYSTEMS I 4 1121 TRENT RAZEK
MIS420 C 2/92 SYSTEMS II 4 1121 TRENT
RAZEK MIS495 B 3/93 MGT INFO SYSTEMS
4
NOTICE HOW YOU GET LOGICAL ROWS BACK FROM THE
SELECT AS IF THEY CAN FROM A SINGLE TABLE WHEN IN
FACT THE DATA COMES FROM THREE SEPARATE TABLES
37
Using Aliases
Aliases for table names can be created in the
FROM part of the SELECT statement. Then you can
use the alias in place of the full table name
when referring to columns in that
table. Sometimes this can save you considerable
typing!
38
Alias Example
SELECT S.STUDENT_NUMBER, STUDENT_NAME,
T.COURSE_CODE, GRADE, QUAR_YR, COURSE_NAME,
CREDIT_HOURS FROM STUDENT S, TRANSCRIPT T, COURSE
C WHERE S.STUDENT_NUMBER T.STUDENT_NUMBER
AND T.COURSE_CODEC.COURSE_CODE
39
Hints for Successful Joins

• Plan your joins
• Draw a mini-ERD to show what tables are involved.
• Count the number of tables involved in the SELECT
  query.
• The number of joins is always one less than the
  number of tables in the query.
• Watch out for ambiguous column names.

40
SQL - Aggregate Functions

• These functions are applied to a set(s) of
  records/rows and return one value for each set.
• Count ()
• Min ()
• Max ()
• Sum ()
• Avg ()
• These functions thus aggregate the rows to which
  they are applied.

41
SQL - Aggregation

• If one field in a Select clause is aggregated,
  all fields in the clause must be aggregated.
• Aggregation The process of transforming data
  from a detail to a summary level.
• You can aggregate a field by including it after
  the GROUP BY clause or by making it the argument
  of an aggregating function.

SELECT Region, SUM(UnitPrice Quantity) FROM
Order_Details GROUP BY Region
42
SQL - Aggregation

• When you use GROUP BY, every field in your
  recordset must be aggregated in some manner.
• The same rule applies when you use an aggregating
  function such as SUM, COUNT, AVERAGE . If one
  field in the Select clause is aggregated, then
  every other field in the Select clause must be
  aggregated in some manner.

43
SQL - Aggregation

• Additional SQL Clause - HAVING
• The HAVING clause is only used after the GROUP BY
  clause.
• The HAVING clause specifies criteria for a GROUP,
  similar to how the WHERE clause specifies
  criteria for individual rows.

44
GROUP BY
USED WITH FUNCTIONS FOR SUBTOTALING
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING
GROUP BY INSTRUCTOR_ID INSTRUCTO
SUM(SALARY) --------- ----------- 100000000
5900 200000000 5900 300000000
4500 400000000 4600 500000000 1900
ORIGINAL DATA IN TABLE INSTRUCTOR
SALARY 100000000 2500 100000000 3400 200000000 35
00 200000000 2400 300000000 4500 400000000 3400 40
0000000 1200 500000000 1900
45
GROUP BY Columns
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM STAFFING
GROUP BY CALL_NUMBER
DOES NOT MAKE SENSE TO DISPLAY SALARY
OR CALL_NUMBER ON A GROUP BY INSTRUCTOR_ID BECAUS
E THEY VARY DOES MAKE SENSE TO
DISPLAY INSTRUCTOR_ID SINCE IT IS THE SAME ACROSS
THE GROUP
INSTRUCTO CALL SALARY ----------------- -----
-- ---------- 100000000 0001
2500 100000000 0002 3400 200000000 0003
3500 200000000 0004 2400 300000000
0005 4500 400000000 0006
3400 400000000 0007 1200
46
GROUP BY and WHERE
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM
STAFFING WHERE SALARYgt2500 GROUP BY
INSTRUCTOR_ID INSTRUCTO SUM(SALARY) ------------
----- -------------------- 100000000
6000 200000000 6100 300000000
4500 400000000 3400
INSTRUCTO CALL SALARY --------- ----
---------- 100000000 0001
2600 100000000 0002 3400 200000000 0003
3500 200000000 0004 2600 300000000
0005 4500 400000000 0006
3400 400000000 0007 1200
WHERE CAN RESTRICT WHICH ROWS ARE PUT INTO THE
GROUP
47
GROUP BY and HAVING
SELECT INSTRUCTOR_ID, SUM(SALARY) FROM
STAFFING HAVING SUM(SALARY)gt4000 GROUP BY
INSTRUCTOR_ID INSTRUCTO SUM(SALARY) ---------
----------- 100000000 5900 200000000
5900 300000000 4500
INSTRUCTO CALL SALARY --------- ----
---------- 100000000 0001
2500 100000000 0002 3400 200000000 0003
3500 200000000 0004 2400 300000000
0005 4500 400000000 0006
2400 400000000 0007 1200
HAVING DETERMINES WHICH GROUPS WILL BE DISPLAYED
48
SQL statement processing order (adapted from van
der Lans, p.100) An intermediate recordset is
developed after each clause.
49
Summary of Select Statements

• SELECT - list of attributes and functions
• FROM - list of tables
• WHERE - conditions / join conditions
• GROUP BY - attributes not aggregated in select
  clause
• HAVING - group condition
• ORDER BY - list of attributes

50
SQL Advanced Topics

• ISM6217 - Advanced Database

51
What Were Going to Cover

• Subqueries (Nested queries)
• Example
• Correlated subquery
• Join types
• Inner/outer
• Integrity constraints
• Triggers
• Functions

52
Sample Database

• Scripts to create and populate the database are
  available on the 6217 Web site.

53
Subqueries

• A subquery is a query that is used in the WHERE
  condition of another query
• AKA Nested query
• Can be multiple levels of nesting
• Can be used with SELECT, INSERT, UPDATE

54
Example 1 By Hand
LIST PARTS W/ gt AVERAGE NUMBER OF
Q_ON_HAND FIRST QUERY DETERMINE
AVERAGE SELECT AVG(Q_ON_HAND) FROM
INVENTORY   SECOND QUERY PLUG AVERAGE
INTO WHERE CLAUSE SELECT PART_NO, Q_ON_HAND,
DESCRIPTION FROM INVENTORY WHERE Q_ON_HAND gt
50.92 ORDER BY PART_NO
55
Example 1 Using Subquery
SELECT PART_NO, Q_ON_HAND,
DESCRIPTION FROM INVENTORY WHERE Q_ON_HAND
gt (SELECT AVG(Q_ON_HAND) FROM
INVENTORY) ORDER BY PART_NO
56
Example 2 Using Join

• List all suppliers who can deliver at least one
  product in less than 10 days

SELECT DISTINCT (S.SUPPLIER_NO),
SUPPLIER_NAME FROM SUPPLIERS S, QUOTATIONS
Q WHERE S.SUPPLIER_NO Q.SUPPLIER_NO AND DELIV
ERY_TIME lt 10 ORDER BY S.SUPPLIER_NO
57
Example 2 Using Subquery
SELECT SUPPLIER_NO, SUPPLIER_NAME FROM SUPPLIERS
WHERE SUPPLIER_NO IN (SELECT SUPPLIER_NO
FROM QUOTATIONS WHERE DELIVERY_TIME lt
10) ORDER BY SUPPLIER_NAME DESC
58
Example 3 With Aggregation

• List all suppliers who can deliver a product in
  less than the average delivery time.

SELECT SUPPLIER_NO, SUPPLIER_NAME FROM
SUPPLIERS WHERE SUPPLIER_NO IN (SELECT
SUPPLIER_NO FROM QUOTATIONS WHERE
DELIVERY_TIME lt (SELECT AVG(DELIVERY_TIME)
FROM QUOTATIONS) ) ORDER BY
SUPPLIER_NAME DESC
59
Example 4 ANY

• LIST SUP_NO, PART, DEL FOR QUOTES WHERE DEL gt ANY
  SUPPLIED BY 71

SELECT SUPPLIER_NO, PART_NO, DELIVERY_TIME FROM
QUOTATIONS WHERE DELIVERY_TIME gt
ANY (SELECT DELIVERY_TIME FROM
QUOTATIONS WHERE SUPPLIER_NO 71)
60
Example 5 ALL

• LIST SUP_NO, PART, DEL FOR QUOTES WHERE DEL gt ALL
  SUPPLIED BY 71

SELECT SUPPLIER_NO, PART_NO, DELIVERY_TIME FROM
QUOTATIONS WHERE DELIVERY_TIME gt
ALL (SELECT DELIVERY_TIME FROM
QUOTATIONS WHERE SUPPLIER_NO 71)
61
Example 6 ANY

• Who are alternate suppliers for parts supplied by
  71?

SELECT SUPPLIER_NO, PART_NO, DELIVERY_TIME FROM
QUOTATIONS WHERE PART_NO ANY (SELECT PART_NO
FROM QUOTATIONS WHERE SUPPLIER_NO 71)
AND SUPPLIER_NO ! 71 ORDER BY SUPPLIER_NO
62
Example 7 NOT EXISTS

• List all suppliers who have not provided a quote

SELECT FROM SUPPLIERS WHERE NOT
EXISTS (SELECT FROM QUOTATIONS
WHERE SUPPLIER_NO SUPPLIERS.SUPPLIER_NO) ORDER
BY SUPPLIER_NO
63
Correlated Subqueries

• A correlated subquery is a subquery that is
  evaluated once for each row processed by the
  parent statement. The parent statement can be a
  SELECT, UPDATE, or DELETE statement. These
  examples show the general syntax of a correlated
  subquery

64
Example 8 Step-by-Step

• List all suppliers, parts and prices where quoted
  price is less than the average quote for that
  part.

SELECT AVG(PRICE) FROM QUOTATIONS WHERE PART_
NO 321   SELECT SUPPLIER_NO, PART_NO,
PRICE FROM QUOTATIONS Q WHERE PRICE lt 4
AND PART_NO 321   SELECT SUPPLIER_NO, PART_NO,
PRICE FROM QUOTATIONS Q WHERE PART_NO 321
65
Example 8 Correlated Subquery
SELECT SUPPLIER_NO, PART_NO, PRICE FROM QUOTATI
ONS Q WHERE PRICE lt (SELECT AVG(PRICE) FROM
QUOTATIONS WHERE Q.PART_NO PART_NO) ORDER BY
PART_NO, SUPPLIER_NO
66
Join Types

• Natural join/inner join
• This is what youre used to.
• Returns only rows where PK and FK values match.
• Does not repeat PK/FK columns
• Equi-Join
• Similar to natural join, but includes both PK and
  FK values in record set.

67
Equi-Join Example
SELECT I.PART_NO, Q.PART_NO, SUPPLIER_NO,
PRICE FROM INVENTORY I, QUOTATIONS Q WHERE
I.PART_NO Q.PART_NO ORDER BY I.PART_NO
68
More Join Types

• Outer join
• Includes columns with null FK values
• Problem Inner join will not return a row that
  does not have a matching value.
• Sometimes this prevents you from getting the
  output you want.
• Example List all parts (including description)
  and any quotes that exist for each part. We want
  to include all parts even if there are no quotes
  for some of them.

69
Solution Left Outer Join

• SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICE
• FROM INVENTORY I, QUOTATIONS Q
• WHERE I.PART_NO Q.PART_NO ()
• ORDER BY I.PART_NO

This is what makes it an outer join. Include all
rows from the table away from the ()
Includes all rows from the left table.
70
SQL Server Version
SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO,
PRICEFROM INVENTORY I, QUOTATIONS
QWHERE I.PART_NO Q.PART_NOORDER BY I.PART_NO
This is what makes it an outer join. Include all
rows from the table closest to the in
Includes all rows from the left table.
71
Non-Solution Right Outer Join

• SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO, PRICE
• FROM INVENTORY I, QUOTATIONS Q
• WHERE I.PART_NO () Q.PART_NO
• ORDER BY I.PART_NO

This query does not include all rows from the
INVENTORY table. So, it doesnt work. We could
reverse the order of tables in the WHERE
condition and the query would be OK
72
Right-Outer Join SQL Server
SELECT I.PART_NO, DESCRIPTION, SUPPLIER_NO,
PRICEFROM INVENTORY I, QUOTATIONS
QWHERE I.PART_NO Q.PART_NOORDER BY I.PART_NO
73
Null Values

• Field values in a tuple are sometimes unknown
  (e.g., a rating has not been assigned) or
  inapplicable (e.g., no spouses name).
• SQL provides a special value null for such
  situations.
• The presence of null complicates many issues.
  E.g.
• Special operators needed to check if value is/is
  not null.
• Is ratinggt8 true or false when rating is equal to
  null? What about AND, OR and NOT connectives?
• We need a 3-valued logic (true, false and
  unknown).
• Meaning of constructs must be defined carefully.
  (e.g., WHERE clause eliminates rows that dont
  evaluate to true.)
• New operators (in particular, outer joins)
  possible/needed.

74
Integrity Constraints (Review)

• An IC describes conditions that every legal
  instance of a relation must satisfy.
• Inserts/deletes/updates that violate ICs are
  disallowed.
• Can be used to ensure application semantics
  (e.g., sid is a key), or prevent inconsistencies
  (e.g., sname has to be a string, age must be lt
  200)
• Types of ICs Domain constraints, primary key
  constraints, foreign key constraints, general
  constraints.
• Domain constraints Field values must be of
  right type. Always enforced.

75
General Constraints
CREATE TABLE Sailors ( sid INTEGER, sname
CHAR(10), rating INTEGER, age REAL, PRIMARY
KEY (sid), CHECK ( rating gt 1 AND rating
lt 10 )

• Useful when more general ICs than keys are
  involved.
• Can use queries to express constraint.
• Constraints can be named.

76
Constraints Over Multiple Relations
CREATE TABLE Sailors ( sid INTEGER, sname
CHAR(10), rating INTEGER, age REAL, PRIMARY
KEY (sid), CHECK ( (SELECT COUNT (S.sid)
FROM Sailors S) (SELECT COUNT (B.bid) FROM
Boats B) lt 100 )
Number of boats plus number of sailors is lt 100

• Awkward and wrong!
• If Sailors is empty, the number of Boats tuples
  can be anything!
• ASSERTION is the right solution not associated
  with either table.

CREATE ASSERTION smallClub CHECK ( (SELECT
COUNT (S.sid) FROM Sailors S) (SELECT COUNT
(B.bid) FROM Boats B) lt 100 )
77
Triggers

• Trigger procedure that starts automatically if
  specified changes occur to the DBMS
• Three parts
• Event (activates the trigger)
• Condition (tests whether the triggers should run)
• Action (what happens if the trigger runs)

78
Triggers Example (SQL1999)

• CREATE TRIGGER youngSailorUpdate
• AFTER INSERT ON SAILORS
• REFERENCING NEW TABLE NewSailors
• FOR EACH STATEMENT
• INSERT
• INTO YoungSailors(sid, name, age, rating)
• SELECT sid, name, age, rating
• FROM NewSailors N
• WHERE N.age lt 18

79
Some Useful Functions

• ABS (n)
• MOD (m,n)
• Returns the remainder of m/n
• POWER (m,n)
• ROUND(n,m)
• SIGN(n)
• SQRT
• TRUNC(15.79,1)

80
Character Functions

• CONCAT(char1, char2)
• LOWER/UPPER
• LTRIM(char ,set) RTRIM(char ,set
• SUBSTR(char, m ,n)
• LENGTH(char)

81
Date Functions

• ADD_MONTHS(d,n)
• LAST_DAY(d)
• MONTHS_BETWEEN(d1, d2)
• ROUND(d,fmt)
• SYSDATE
• TO_CHAR(d , fmt , 'nlsparams' )
• TO_DATE(char , fmt , 'nlsparams' )

82
Data Dictionary Tables

• DICTIONARY
• All tables and views that are available to the
  user
• This table contains several hundred rows
• Useful Data Dictionary Views
• Use just like a table
• More useful (generally) than full tables
• Use DESCRIBE to see the columns in the view
• USER_TABLES
• USER_VIEWS
• USER_CONSTRAINTS
• USER_OBJECTS

83
Visit Me AT

• www.hiteshsahni.com