SQL Part 2 Advanced SQL

1
SQL Part 2 Advanced SQL

• CIS 324 Chapters 6 7

2
Sub-Queries
Example We need the names of all employees (not
their employee number) for all employees who
worked more than 40 hours on an
assignment. Sub-Queries Nested Queries
(Effective when the results ultimately comes from
ONE table) SELECT Name FROM EMPLOYEE WHERE Emp
loyeeNumber IN (SELECT DISTINCT
EmployeeNum FROM ASSIGNMENT WHERE HoursWorke
d gt 40)
3
Sub Queries (cont)
Multiple nesting is allowed What if we only
need Accounting projects from the above
example SELECT Name FROM EMPLOYEE WHERE Empl
oyeeNumber IN (SELECT DISTINCT
EmployeeNum FROM ASSIGNMENT WHERE ProjectID
IN ( SELECT ProjectID FROM PROJECT
WHERE Department Accounting))
4
Multiple Table Queries - Joins
When we need to display data from 2 or more
tables SELECT Name, HoursWorked FROM EMPLOYEE
, ASSIGNMENT WHERE EmployeeNumber
EmployeNum This creates a new table with Name
from the EMPLOYEE table and HoursWorked from the
ASSIGNMENT table when the condition of matching
employee numbers occurs. A join is just another
table so all earlier SQL statements are available
for use. (Group BY, WHERE, etc.)
5
Multiple Table Joins
We can join more than 2 tables together SELECT
Project.Name, HoursWorked, EMPLOYEE.Name FROM P
ROJECT, ASSIGNMENT, EMPLOYEE WHERE PROJECT.Proje
ctID ASSIGNMENT.ProjectID AND EMPLOYEE.Employ
eeNumber ASSIGNMENT.EmployeeNum
6
Joins (cont)
If two columns in separate tables have the same
column name you will need to indicate the table
name and column name in the WHERE
statement SELECT Name, HoursWorked FROM PROJ
ECT, ASSIGNMENT WHERE PROJECT.ProjectID
ASSIGNMENT.ProjectID If not all rows in both
tables have a match in the join condition these
rows will not appear in the join table.
7
Natural Join

• Links the tables by selecting only the rows with
  common values in their common attributes
• Used when tables share one or more common
  attributes with common names
• Results do not include unmatched rows
• Duplicate columns appear only once in new table
• Select column-list
• From table1 Natural Join table2
• Select ProjectId, ProjectName, EmployeeNum,
  HoursWorked
• From Project Natural Join Assignment

8
Outer Joins
OUTER JOIN Can be a Left, Right or Full
Join An outer join returns the rows in the
select statement that match the criteria
specified or have unmatched values. Left Join
Returns the rows matching the criteria as well as
the rows from the first (left) table in the
select statement that have unmatched
values Right Join Returns the rows matching
the criteria as well as the rows from the second
(right) table in the select statement that have
unmatched values Full Join Returns the rows
matching the criteria as well as the rows from
both tables in the select statement that have
unmatched values SELECT Name,
HoursWorked FROM PROJECT LEFT JOIN
ASSIGNMENT WHERE PROJECT.ProjectID
ASSIGNMENT.ProjectID
9
Sample Outer Join
This appends the name of the project to the left
side of the assignment table. The unmatched rows
will receive a null value Q3 Portfolio
Analysis 17.50 Q3 Portfolio Analysis 12.50 Q
3 Portfolio Analysis 8.00 Q3 Portfolio
Analysis 20.25 Q3 Tax Prep 45.75 Q3 Tax
Prep 70.50 Q3 Tax Prep 40.50 Q4 Product
Plan 75.00 Q4 Product Plan 20.25 Q4
Product Plan 25.25 Q4 Portfolio Analysis null
10
Triggers

• Objects that perform actions automatically (they
  respond to modifications made to data within a
  table
• Fired (Like Events) Not Directly Invoked
• 3 Main Types
• Insert
• Update
• Delete
• (Will be fired before or after an
  insert/update/delete)

11
New to SQL-Standards

• Added to SQL-Standards in 1999
• Most DBMS Implemented Earlier
• Most Trigger Implementations are Proprietary
• Different Functionality
• Different Implementation

12
Triggers

• Associated with ONE Database Table
• A Table may have one or more Triggers
• A Trigger is part of the transactions that
  triggers it
• Can be used to enforce Constraints
• Can add additional functionality
• Automate Critical Actions
• Provide Warnings and Suggestions for Actions
• Create Auditing logs
• Generate Values for Derived Columns
• Create Backup Tables

13
Create SQL Triggers

• CREATE TRIGGER ltNAMEgt
• BEFOREAFTER
• INSERTDELETEUPDATE OF ltcolumngt
• ON lttable namegt REFENCING ltaliasgt
• FOR EACH row/statement
• WHEN (ltsearch conditionsgt)
• lttriggered SQL statementsgt

14
Insert Trigger

• CREATE TRIGGER Insert InsertEmp
• AFTER INSERT ON Employee
• FOR EACH ROW
• BEGIN ATOMIC
• INSERT INTO EmployeeLog (Action)
• VALUES (Insert)
• END

15
Update Trigger

• CREATE TRIGGER UpdateEmp
• AFTER UPDATE ON Employee
• FOR EACH STATEMENT
• BEGIN ATOMIC
• INSERT INTO EmployeeLog (Action)
• VALUES (Update)
• END

16
Delete Trigger

• CREATE TRIGGER DeleteEmp
• BEFORE DELETE ON Employee
• FOR EACH ROW
• BEGIN ATOMIC
• INSERT INTO EmployeeLog (Action)
• VALUES (DELETE)
• END

17
Additional SQL Code in Triggers

• Trigger code can be used to Declare Variables
• Insert Columns / Rows to Tables
• Calculate Values
• Update Columns / Rows in Tables

18
Stored Procedures

• A named collection of procedural and SQL
  statements (executed as one transaction)
• Stored within the Database
• Can be Directly Invoked (Unlike Triggers)
• Increase Performance of Database
• Stored Procedures are stored at the Server
• Less Network Traffic

19
Basic Syntax for Stored Procedure

• CREATE PROCEDURE ltnamegt
• (parameter declaration, parameter
  declaration)
• BEGIN
• ltsql statementsgt
• END