Database PPT 2

Advanced SQL
Modern Database Management

12th Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
Data Manipulation Language
SELECT Statement
 Is the heart of DML
 Is used to fetch data from tables
 Used for queries on single or multiple tables
 Simplest SELECT statement:

SELECT List the columns (and expressions) to be returned from the query
FROM Indicate the table(s) or view(s) from which data will be obtained ;
SELECT Statement
Clauses of the SELECT Statement:
SELECT
List the columns (and expressions) to be returned from the query
FROM
Indicate the table(s) or view(s) from which data will be obtained
WHERE
Indicate the conditions under which a row will be included in the result
GROUP BY
Indicate categorization of results
HAVING
Indicate the conditions under which a category (group) will be included
ORDER BY
Sorts the result according to specified criteria
Objectives
• Define terms
• Write single and multiple table SQL queries
• Define and use three types of joins
• Write subqueries
• Understand and use SQL in procedural languages (e.g. PHP,

PL/SQL)
• Understand triggers and stored procedures

Single table queries
 The from clause lists the relations involved in the query
select 
from instructor
Multiple table queries
 The from clause lists the relations involved in the query
 Corresponds to the Cartesian product operation of the relational
algebra
select 
from instructor, teaches
 generates every possible instructor – teaches pair, with all attributes

from both relations
 Cartesian product not very useful directly, but useful combined with
where-clause condition
Cartesian Product: instructor X teaches
instructor teaches
Multiple table queries
• The Cartesian product is not very useful directly
• It is useful when combined with where-clause condition:
select 
from instructor, teaches
where instructor.ID = teaches.ID
• Join: a relational operation that causes two or more tables

with a common domain to be combined into a single table or
view
The common columns in joined tables are usually the primary key of the dominant table
and the foreign key of the dependent table in 1:M relationships.
Joining instructor and teaches
instructor teaches
Quiz
• Find the course ID, semester, year and title of each course offered by the
Comp. Sci. department
Quiz
• Find the course ID, semester, year and title of each course offered by the
Comp. Sci. department
select section.course_id, semester, year, title

from section, course
where section.course_id = course.course_id
and dept_name = ‘Comp_Science’
This schema is used in queries that follow
These tables are used in queries that follow:
15 customers but only 9 have placed the 10 orders!

Chapter 7 © 2013 Pearson Education, Inc. Publishing as Prentice Hall
Processing Multiple Tables
• Equi-join or INNER- Join

• a join in which the joining condition is based on equality between
values in the common columns
• common columns appear redundantly in the result table
• Natural join:
• Same as equi-join
• one of the duplicate columns is eliminated in the result table
1. Equi-Join Example
• For each customer who placed an order, what is the
customer’s name and order number?
1. Equi-Join Example
SELECT *
FROM Customer_T, Order_T
WHERE Customer_T.CustomerID = Order_T. CustomerID
ORDER BY OrderId;
Customer ID
appears twice in the
result
1. Equi-Join Example – Syntax
SELECT *
FROM Customer_T INNER JOIN Order_T
ON Customer_T.CustomerID = Order_T. CustomerID
ORDER BY OrderId;
Customer ID
appears twice in the
result
1. Equi-Join Example – Syntax
SELECT *
FROM Customer_T INNER JOIN Order_T
ON Customer_T.CustomerID = Order_T. CustomerID
ORDER BY OrderId;
INNER JOIN clause is an alternative to WHERE clause, and is used to match

primary and foreign keys.
An INNER join will only return rows from each table that have matching rows
in the other.
This query produces same results as previous equi-join example.

Quiz
Item_Id Item_Name Artist_Id Item_Price
Artist_Id Artist_name 1 Umami In Concert 101 17.95
101 Umami 2 Race Car Sounds 102 13
102 The Ubernerds 3 No Rest For The Weary 103 16.95
103 No Rest For The Weary 4 More Songs About Structures and Comestibles 103 17.95
105 Burt Ruggles 5 On The Road With Burt Ruggles 105 17.5
106 Sewed the Vest Pocket 6 No Fixed Address 106 16.95
107 Jess and Odie 7 Rude Noises 13
108 Burt Ruggles 8 Burt Ruggles: An Intimate Portrait 108 17.95
110 Onn and Onn 9 Zone Out With Umami 101 16.95
10 Etcetera 110 17
11 Fake Mona Lisa 18
• Report the Artist Name and their Creations

Quiz
select a.artist_id, a.artist_name, i.item_id, i.item_name
from artist a INNER JOIN item i ON a.artist_id = i.artist_id;
select a.artist_id, a.artist_name, i.item_id, i.item_name

from artist a, item i
where a.artist_id = i.artist_id;
• Report the Artist Name and their Creations
Artist_Id Artist_name Item_Id Item_Name
101 Umami 1 Umami In Concert
101 Umami 9 Zone Out With Umami
102 The Ubernerds 2 Race Car Sounds
103 No Rest For The Weary 3 No Rest For The Weary
103 No Rest For The Weary 4 More Songs About Structures
104 Burt Ruggles 5 On The Road With Burt Ruggles
105 Sewed the Vest Pocket 6 No Fixed Address
107 Burt Ruggles 8 Burt Ruggles: An Intimate Portrait
108 Onn and Onn 10 Etcetera

Database PPT 2

Uploaded by

Copyright:

Available Formats

Database PPT 2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database PPT 2

Uploaded by

Copyright:

Available Formats

Advanced SQL

Modern Database Management

 Is used to fetch data from tables

 Used for queries on single or multiple tables

 Simplest SELECT statement:

• Write single and multiple table SQL queries

• Define and use three types of joins

• Understand and use SQL in procedural languages (e.g. PHP,

• Understand triggers and stored procedures

 generates every possible instructor – teaches pair, with all attributes

• Join: a relational operation that causes two or more tables

select section.course_id, semester, year, title

15 customers but only 9 have placed the 10 orders!

• Equi-join or INNER- Join

INNER JOIN clause is an alternative to WHERE clause, and is used to match

This query produces same results as previous equi-join example.

• Report the Artist Name and their Creations

select a.artist_id, a.artist_name, i.item_id, i.item_name

You might also like