Structured Query Language SQL and Access QBE

1
Structured Query Language (SQL) and Access QBE

• Shaun Simpson
• MIS 372
• Washington State University Vancouver

2
Lecture Outline

• SQL General Syntax
• A Sample Database
• Simple SQL/QBE Queries ( from 1 table)
• Complex Queries (from gt 1 Table)
• Queries Using Some English-like qualifiers (e.g.,
  Like, Between ... and)
• Queries Using Aggregate Functions
• Using SQL to Create Views
• Query Update/Delete/Add

3
SQL General Syntax

• SELECT data_1 , data_2/function(...)...
  FROM table_1 , table_2 ...
  WHERE condition_1
  , not, or, and condition_2 ...
• GROUP BY data_1, ...
• HAVING aggregate function(...) ...
• ORDER BY data_1, ...
• Aggregate Functions
• COUNT() SUM(item)
• AVG(item) MAX(item) MIN(item)

Optional!
4
A Sample Database(for SQL/QBE query
illustrations)

• Database Creation (Some examples in SQL
  commands)
• (see p. 287)
• Create Table STUDENT AS
  (SID char(8), Name
  char(35),...)
• Create Table BANK AS
  (BID integer, Name
  char(35), Type ...)
• Create Table LOAN AS
  (LID integer, Date
  date/time, Term integer, Int_Rate ...)
• Create Index (SID) on Table STUDENT...
• Create ...

5
SQL Database Definition Commands

• CREATE TABLE - define a table
• DROP TABLE - remove a table
• ALTER TABLE - modify a table
• CREATE INDEX - build an index
• DROP INDEX - remove an index
• CREATE VIEW - define a virtual table
• DROP VIEW - remove a virtual table
• (please refer to Chapter 7 pp. 272-307 of your
  textbook)

6
Simple SQL Query(from one table - Example 1)

• Question
• Get all SIDs for those students who have borrowed
  loan(s).

7
Simple SQL Query(from one table - Example 1)

• Question
• Get all SIDs for those students who have
  borrowed some loan(s).

SELECT SID FROM LOAN
8
Simple SQL/QBE Comparison(from one table -
Example 1)

• Question Get all SIDs for those students who
  have borrowed some loan(s).

Std SQL SELECT SID FROM LOAN
Access SQL SELECT LOAN.SID FROM LOAN
9
Simple SQL Query(from one table - Example 2)

• Question
• Find SID, Name for all students who are
  sophomore and with an age gt 20 years old.
• Select SID, Name
• FROM (which one?)
• WHERE (what to check?)

10
Simple SQL Query(from one table - Example 2)

• Question
• Find SID, Name for all students who are
  sophomore and with an age gt 20 years old.
• Std SQL
• Select SID, Name
  From STUDENT
• Where Level 2 and Age gt 20

11
SQL/QBE Comparison(from one table - Example 2)

• Find SID, Name for all students who are sophomore
  and with an age gt 20 years old.

Std SQL Select SID, Name
From STUDENT
Where Level 2 and Age gt 20
Access SQL SELECT STUDENT.SID,
STUDENT.Name FROM STUDENT WHERE
(((STUDENT.Level)2) AND ((STUDENT.Age)20))
12
Complex QueriesExample 1 - Using Equal Join

• Question
• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8

13
Complex Queries Example 1 - Using Equal Join

• Question
• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8
• SELECT SID, Name
• FROM STUDENT, LOAN
• WHERE STUDENT.SID LOAN.SID
• AND Int_Rate gt 8

14
SQL/QBE Query Comparison Example 1 - Using Equal
Join

• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8

Std SQL SELECT SID, Name FROM STUDENT,
LOAN WHERE STUDENT.SID LOAN.SID
AND Int_Rate gt 8
Access SQL SELECT STUDENT.SID, STUDENT.Name
FROM STUDENT INNER JOIN LOAN ON
STUDENT.SID LOAN.SID WHERE
(((LOAN.Int_Rate)gt8))
15
SQL/QBE Query Graphic Line Implication

• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8

An equality check is done between the common
fields of these two tables!
Check off the box and put down right expression!
16
SQL/QBE QueryIf No Graphic Line

• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8

Need a criterion check to ensure the equality
between the common fields of these two tables!
17
Complex Queries Example 1 - Use IN Operator
(Nested)

• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8
• Form a nested SQL using the in operator
• SELECT SID, Name
  FROM STUDENT WHERE
  SID IN (
  )

18
Complex QueriesUse IN Operator in SQL (Nested)

• Get all SID, Name for those students who have
  borrowed loan(s) with an Int_Rate gt 8
• Form a nested SQL using the in operator
• SELECT SID, Name
  FROM STUDENT WHERE
  SID IN (SELECT SID FROM LOAN
  WHERE Int_Rate gt 8)

19
Complex QueriesExample 2 - Using Equal Join

• Question
• Get SID and Name for those students who have
  borrowed money from a SL bank.
• SELECT SID, Name
  FROM STUDENT ... WHERE
  ...

20
Complex Queries Example 2 - Using Equal Join

• Question
• Get SID and Name for those students who have
  borrowed money from a SL bank.
• SELECT SID, Name
  FROM STUDENT, LOAN, BANK
  WHERE STUDENT.SID LOAN.SID and
  LOAN.BIDBANK.BID and TYPESL

21
Complex SQL/QBE Comparison Example 2 - Using
Equal Join

• Std. SQL
• SELECT SID, Name
  
• FROM STUDENT, LOAN,
• BANK WHERE
• STUDENT.SID LOAN.SID
• and LOAN.BIDBANK.BID
• and TYPESL

Ensure proper join!
22
Complex SQL/Access SQL Comparison

• Std. SQL
• SELECT SID, Name
  
• FROM STUDENT, LOAN,
• BANK WHERE STUDENT.SID LOAN.SID
• and LOAN.BIDBANK.BID
• and TYPESL

Simple syntax
Two Inner Joins
Access SQL SELECT STUDENT.SID, STUDENT.Name FROM
BANK INNER JOIN (STUDENT INNER JOIN LOAN ON
STUDENT.SID LOAN.SID) ON BANK.BID
LOAN.BID WHERE (((BANK.Type)"SL))
23
Complex SQL Queries Example 3 - Using Self Join

• Question
• Get all SIDs for those who have borrowed more
  than one loan.
• Select first.SID
• From LOAN as first, LOAN as second
• Where ...

24
Complex SQL Queries Example 3 - Using Self Join
LOAN (first)
LID
Term
Int_Rate
SID
BID
Amt
Date
LOAN (second)
LID
Term
Int_Rate
SID
BID
Amt
Date

• Question
• Get all SIDs for those who have borrowed more
  than one loan.
• Select F.SID
• From LOAN as F, LOAN as S
• Where F.SID S.SID and F.LID ltgt S.LID
• Q. Is the above query good enough?

25
Complex SQL Queries Example 3 - Using Group By

• Question
• Get all SIDs for those who have borrowed more
  than one loan.
• Select SID From LOAN GROUP BY SID
• HAVING COUNT() gt 1
• Need only to access to one LOAN table (why?)

26
Complex Access SQL/QBE QueriesSelf Join

• Get all SIDs for those who have borrowed more
  than one loan.

Answer 1 SELECT DISTINCT F.SID FROM LOAN AS F,
LOAN AS S WHERE F.SID S.SID and F.LID ltgt S.LID
Answer2 SELECT SID FROM LOAN GROUP BY SID HAVING
COUNT()gt1
27
Queries Using English-Like QualifiersUsing
Like Operator

• 1. Get all students names for those having
  MARK as his/her first name.
• SELECT SID, NAME FROM
  STUDENT WHERE NAME LIKE
  MARK"
• 2. Get all students names for those having
  ANDY in his/her name.
• SELECT SID, NAME
  FROM STUDENT WHERE NAME
  LIKE "ANDY"

28
Queries Using English-Like QualifiersUsing
BetweenAnd Operator

• Get all banks BID for those who have a ZIP code
  begins with 98.
• Answer
• Select BID
  From BANK
  Where ZIP between 98001
  and 98999
• Alternate Answer
• Select BID
  From BANK
  Where (ZIP/1000) gt 98 and
  (ZIP/1000) lt99

29
Complex Queries Using Aggregate Functions
STUDENT
LOAN

• Question
• Get SID, Name, total of loans, and total amount
  of loans for students who have borrowed gt the
  overall average.
• Select S.SID, Name, Count(), Sum(Amt)
  From STUDENT S, LOAN L
  Where S.SID L.SID
  GROUP BY S.SID, Name
  Having Sum(Amt) gt
  AVG(Amt)

30
Complex Queries Using Aggregate Functions - More
decorations!

• Select S.SID, Name, Count() as No of Loans,
  Sum(Amount) as Total Amt
  From STUDENT S, LOAN L
  Where S.SID
  L.SID
  GROUP BY S.SID, Name
  Having Sum(Amount) gt
  AVG(Amount) Order by 4
  Desc

Labels on columns
Display in descending order of the 4th data
item in Select
Results
large small
31
Create Views on Tables(Views - Virtual Tables)
STUDENT
SID
Age
ZIP
Name
Level
LOAN
LID
Term
Int_Rate
SID
BID
Amt
Date
SID
Name
LID
Amt
Int_rate

• Using SQL (Create View command) to create a view
  as described above.
• Create View STU_LOAN as
• Select SID, Name, LID, Amount, Int_Rate
  From STUDENT S, LOAN L Where S.SID
  L.SID

32
Create Views on TablesComments on Access SQL

• Create View command is not supported in Access.
  However, you may consider any query formed in
  Access as a View. Then, you can
• Query against that query
• Make a form on top of that query
• Query against that query with other tables
• That is, a saved QUERY in Access can be treated
  as a Virtual Table for data retrieval...

33
Query Update/Delete/AddSome Examples

• 1. Update the interest rate by increasing .5 for
  those loans with a loan approved before 1/1/92
• Update LOAN
• Set Int_Rate Int_Rate .5
• Where Date lt 1/1/92
• 2. Delete all graduate students.
• Delete From STUDENT
• Where SID in
• (Select SID From STUDENT Where Level 5)
• 3. Add a new BANK (1123, USBank, BANK, 98112)
• INSERT into BANK values (1123, USBank, BANK,
  98112)