Transcript Principles of Information Systems

Transact-SQL
SECTION 6
Based on SQL (1992)
Introduction
• Most important relational data manipulation
language
• ANSI
• Used by many commercial databases
• SEQUEL
• 1992 ANSI standard
• SQL3
• An interactive query language
• Or embedded in other languages
• SQL is not a programming language
• Transact-SQL
• Relations used for SQL examples
•
•
•
•
•
•
JUNIOR (Snum, Name, Major)
HONOUR-STUDENT (Number, Name, Interest)
STUDENT (SID, Name, Major, GradeLevel, Age)
CLASS (Name, Time, Room)
ENROLLMENT ( StudentNumber, ClassName, PositionNumber)
FACULTY (FID, Name, Department)
Is there a problem with the ENROLLMENT table and the primary key?
Creating a Table
CREATE TABLE STUDENT
(SID NUMERIC(3) CONSTRAINT student_pk
PRIMARY KEY,
Name nVARCHAR(20),
Major nVARCHAR(3),
GradeLevel nVARCHAR(2),
Age NUMERIC(3))
CREATE TABLE ENROLLMENT
(StudentNumber NUMERIC(3),
ClassName nVARCHAR(6),
CONSTRAINT enrollment_pk (StudentNumber, ClassName) PRIMARY KEY,
PositionNumber NUMERIC(2),
CONSTRAINT student_fk REFERENCES STUDENT (StudentNumber),
CONSTRAINT class_fk REFERENCES CLASS (ClassName))
Inserting Values
INSERT INTO STUDENT VALUES
(100, ‘JONES’, ‘HISTORY’, ‘GR’, 21)
INSERT INTO STUDENT VALUES
(150, ‘PARKS’, ‘ACCOUNTING’, ‘SO’, 19)
INSERT INTO STUDENT VALUES
(200, ‘BAKER’, ‘MATH’, ‘GR’, 50)
INSERT INTO STUDENT VALUES
(250, ‘GLASS’, ‘HISTORY’, ‘SN’, 50)
INSERT INTO STUDENT VALUES
(300, ‘BAKER’, ‘ACCOUNTING’, ‘SN’, 41)
INSERT INTO STUDENT VALUES
(350, ‘RUSSELL’, ‘MATH’, ‘JR’, 20)
INSERT INTO STUDENT VALUES
(400, ‘RYE’, ‘ACCOUNTING’, ‘FR’, 18)
INSERT INTO STUDENT VALUES
(450, ‘JONES’, ‘HISTORY’, ‘SN’, 24)
Querying a Single Table
• SQL is a transform-oriented language
• Accepts as input
• The result?
• Sample Data
STUDENT
SID
Name
Major
GradeLevel
Age
100
JONES
HISTORY
GR
21
150
PARKS
ACCOUNTING
SO
19
200
BAKER
MATH
GR
50
250
GLASS
HISTORY
SN
50
300
BAKER
ACCOUNTING
SN
41
350
RUSSELL
MATH
JR
20
400
RYE
ACCOUNTING
FR
18
450
JONES
HISTORY
SN
24
ENROLLMENT
StudentNumber
ClassName
PositionNumber
100
BD445
1
150
BA200
1
200
BD445
2
200
CS250
1
300
CS150
1
400
BA200
2
400
BF410
1
400
CS250
2
450
BA200
3
CLASS
Name
Time
Room
BA200
M-F9
SC110
BD445
MWF3
SC213
BF410
MWF8
SC213
CS150
MWF3
EA304
CS250
MWF12
EB210
Projections Using SQL
•
The projection
–
•
STUDENT [SID, NAME, MAJOR]
Specified in SQL
SELECT
FROM
SID, Name, Major
STUDENT
SELECT
FROM
STUDENT.SID, STUDENT.Name, STUDENT.Major
STUDENT
SELECT
FROM
s.SID, s.Name, s.Major
STUDENT s
• The result
100
JONES
HISTORY
150
PARKS
ACCOUNTING
200
BAKER
MATH
250
GLASS
HISTORY
300
BAKER
ACCOUNTING
350
RUSSELL
MATH
400
RYE
ACCOUNTING
450
JONES
HISTORY
• The word SELECT
Another Example
SELECT
FROM
Major
STUDENT
A
HISTORY
ACCOUNTING
MATH
HISTORY
ACCOUNTING
MATH
ACCOUNTING
HISTORY
SELECT
FROM
DISTINCT Major
STUDENT
B
HISTORY
ACCOUNTING
MATH
Selections Using SQL
•
The Relational Algebra selection operator
is also performed
•
Specified in SQL
SELECT
FROM
WHERE
SID, Name, Major, GradeLevel, Age
STUDENT
Major = ‘Math’
• SELECT - FROM - WHERE
SELECT
FROM
WHERE
*
STUDENT
Major = ‘Math’
200
BAKER
MATH
GR
50
350
RUSSELL
MATH
JR
20
• Combination of Selection and Projection
SELECT
FROM
WHERE
Name, Age
STUDENT
Major = ‘Math’
BAKER
50
RUSSELL
20
• Conditions in a WHERE clause
SELECT
FROM
WHERE
Name, Age
STUDENT
Major = ‘Math’ AND Age > 21
BAKER
50
• Conditions in a WHERE clause can refer to
a set of values
SELECT
FROM
WHERE
Name
STUDENT
Major IN (‘Math’, ‘ACCOUNTING’)
PARKS
BAKER
BAKER
RUSSELL
RYE
• The following expression
SELECT
FROM
WHERE
Name
STUDENT
Major NOT IN (‘Math’, ‘ACCOUNTING’)
JONES
GLASS
JONES
• WHERE clause and ranges of values
SELECT
FROM
WHERE
Name, Major
STUDENT
Age BETWEEN 19 AND 30
JONES
HISTORY
RUSSELL
MATH
JONES
HISTORY
• Equivalent to:
SELECT
FROM
WHERE
Name, Major
STUDENT
Age > 19 AND Age < 30
• WHERE clause and the LIKE keyword
SELECT
FROM
WHERE
Name, GradeLevel
STUDENT
GradeLevel LIKE ‘_R’
JONES
GR
BAKER
GR
RUSSELL
JR
RYE
FR
• Finding last names ending with S
SELECT
FROM
WHERE
Name
STUDENT
Name LIKE ‘%S’
JONES
PARKS
GLASS
JONES
• IS NULL keyword
SELECT
FROM
WHERE
•
Name
STUDENT
GradeLevel IS NULL
What will this return?
Sorting
•
Rows of a result can be sorted
SELECT
FROM
WHERE
ORDER BY
Name, Major, Age
STUDENT
Major = ‘Accounting’
Name
BARKER
ACCOUNTING
41
PARKS
ACCOUNTING
19
RYE
ACCOUNTING
18
• Sorting more than one column
SELECT
FROM
WHERE
ORDER BY
Name, Major, Age
STUDENT
GradeLevel IN (‘FR’, ‘SO’, ‘SN’)
Major ASC, Age DESC
BARKER
ACCOUNTING
41
PARKS
ACCOUNTING
19
RYE
ACCOUNTING
18
GLASS
HISTORY
50
JONES
HISTORY
24
CAST & CONVERT
•
Both of these functions change data values
CAST
Syntax CAST(expression as [datatype])
COURSE
SID
Name
Course
Mark
100
JONES
Actg4P87
82.4
SELECT SID, Name, Course CAST(Mark as Integer) Int_Mark FROM COURSE
COURSE
SID
Name
Course
Int_Mark
100
JONES
Actg4P87
82
CONVERT
Syntax CCONVERT(datatype, expression, [style])
COURSE
SID
Name
Course
Mark
100
JONES
Actg4P87
82.4
SELECT SID, Name, Course CONVERT(Integer, Mark) Int_Mark FROM COURSE
COURSE
SID
Name
Course
Int_Mark
100
JONES
Actg4P87
82
CAST & CONVERT with Datetime2
Datetime2 datatype
DATETIME2 can store century, year, month, date, hour, minute, and second,
represented in both character and number datatypes.
DateOfBith is a Datetime2 datatype
Declare as: DateOfBirth DATETIME2
Stored as YYYY-MM-DD hh:mm:ss.n
STUDENT
SID
Name
DateOfBirth
100
JONES
1985-03-16 16:25:30.0000000
Datetime2 datatypes can easily be modified using
CAST or CONVERT
Select SID, Name, CAST(DateOfBirth as nVARCHAR(10)) Date
FROM STUDENT.
STUDENT
SID
Name
Date
100
JONES
1985-03-16
Or:
Select SID, Name, CONVERT(nVARCHAR(10), DateOfBirth)) Date
FROM STUDENT.
CONVERT using styles
Style
Appearance
Style
Appearance
0
mon dd yyyy hh:miAM (or PM)
100
mon dd yyyy hh:miAM (or PM)
1
mm/dd/yy
101
mm/dd/yyyy
2
yy.mm.dd
102
yyyy.mm.dd
3
dd/mm/yy
103
dd/mm/yyyy
4
dd.mm.yy
104
dd.mm.yyyy
5
dd-mm-yy
105
dd-mm-yyyy
6
dd mon yy
106
dd mon yyyy
7
Mon dd, yy
107
Mon dd, yyyy
8
hh:mi:ss
108
hh:mi:ss
9
mon dd yyyy hh:mi:ss:mmmAM (or PM)
109
mon dd yyyy hh:mi:ss:mmmAM (or PM)
10
mm-dd-yy
110
mm-dd-yyyy
11
yy/mm/dd
111
yyyy/mm/dd
12
yymmdd
112
yyyymmdd
13
dd mon yyyy hh:mi:ss:mmm(24h)
113
dd mon yyyy hh:mi:ss:mmm(24h)
14
hh:mi:ss:mmm(24h)
114
hh:mi:ss:mmm(24h)
Select SID, Name, CONVERT(nVARCHAR(10), DateOfBirth),107) Date
FROM STUDENT.
STUDENT
SID
Name
DateOfBirth
100
JONES
Mar 6, 1985
Select SID, Name, CONVERT(nVARCHAR(10), DateOfBirth),111) Date
FROM STUDENT.
STUDENT
SID
Name
DateOfBirth
100
JONES
1985/03/16
Datetime Functions
•
GETDATE
–
Returns the current database system date and time
–
E.G. SELECT GETDATE() as ‘Current Date & Time’
Current Date & Time
2015-03-23 14:43:33.470
•
GETUTCDATE
–
Returns the Coordinated Universal Time (UTC)
–
E.G. SELECT GETUTCDATE() as ‘GMT Date & Time’
GMT Date & Time
2015-03-23 18:43:33.470
•
DATEDIFF
–
Returns the time between two dates as a signed integer
–
Syntax:
DATEDIFF (datepart, startdate, enddate)
datepart
Abbreviation
year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
weekday
dw, w
hour
hh
minute
mi, n
second
ss, s
millisecond
ms
microsecond
mcs
nanosecond
ns
SELECT DATEDIFF (dy, ‘2015-02-15’, ‘2015-03-20’) as ‘No. of Days’
No. of Days
33
•
•
DATEADD
−
Adds or subtracts a specified time interval from a date.
–
Syntax:
DATEADD (datepart, number, date)
SELECT DATEADD(dy,30,GETDATE())
(NO COLUMN NAME)
2015-04-22 15:00:07.043
•
DATEPART
–
Returns a single part of a datetime field.
–
Syntax:
DATEPART (datepart, date)
SELECT DATEPART (yyyy, GETDATE()) as Year,
DATEPART (mm, GETDATE()) as Month,
DATEPART (dd, GETDATE()) as Day
Year
Month
Day
2015
3
23
SQL Built-In Functions
•
Functions
–
COUNT, SUM, AVG, MAX, and MIN
SELECT
FROM
COUNT (*)
STUDENT
8
•
Consider the following
SELECT
FROM
vs.
SELECT
FROM
COUNT (Major)
STUDENT
COUNT (DISTINCT Major)
STUDENT
8
3
Built-In Functions and Grouping
•
To increase built-in function utility
•
Formed by collecting rows that have the
same value of a specified column
•
GROUP BY
• An example
SELECT
FROM
GROUP BY
Major, COUNT (*)
STUDENT
Major
HISTORY
3
ACCOUNTING
3
MATH
2
• Grouping subsets
SELECT
FROM
GROUP BY
HAVING
Major, COUNT (*)
STUDENT
Major
COUNT (*) > 2
HISTORY
3
ACCOUNTING
3
• Greater generality using WHERE
SELECT
FROM
WHERE
GROUP BY
HAVING
Major, AVG (Age)
STUDENT
GradeLevel = ‘SN’
Major
COUNT (*) > 1
HISTORY
37
OVER and PARTITIONING
ORDERS
OrderNum
CustomerNo
Cost
345
1
7.50
346
1
8.50
347
1
9.50
348
2
8.00
Try the following:
SELECT SUM(Cost) as SumCost
FROM ORDERS
SumCost
33.50
SELECT SUM(Cost) as SumCost, OrderNum
FROM ORDERS
This does not work
SELECT SUM(Cost) OVER () AS SumCost, OrderNum
FROM Orders
SumCost
OrderNum
33.50
345
33.50
346
33.50
347
33.50
348
OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get
aggregate data alongside it.
Quick translation:
SUM(cost) – get me the sum of the COST column
OVER – for the set of rows….
() – …that encompasses the entire result set.
OVER exposes the entire resultset to the aggregation…”SumCost” was the sum of all [Cost] in the resultset. We can break up that
resultset into partitions with the use of PARTITION BY:
SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS SumCost, OrderNum, CustomerNo
FROM Orders
The partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”.
This will give the sum of cost for Customer 1, and then the sum for Customer 2.
SumCost
OrderNum
CustomerNo
25.50
345
1
25.50
346
1
25.50
347
1
8.00
348
2
The translation here is:
SUM(cost) – get me the sum of the COST column
OVER – for the set of rows….
(PARTITION BY CustomerNo) – …that have the same CustomerNo.
Querying Multiple Tables
• Study now extended to two or more tables
• Will use the data from the following tables
• STUDENT (SID, Name, Major, GradeLevel, Age)
• CLASS (Name, Time, Room)
• ENROLLMENT ( StudentNumber, ClassName, PositionNumber)
Retrieval Using Subquery
• Want to know the names of the students
enrolled in class BD445
SELECT
FROM
WHERE
SELECT
FROM
WHERE
Name
STUDENT
SID IN (100,200)
StudentNumber
ENROLLMENT
ClassName = ‘BD445’
100
200
• Use it as a subquery
SELECT
FROM
WHERE
Name
STUDENT
SID IN
(SELECT
FROM
WHERE
JONES
BAKER
StudentNumber
ENROLLMENT
ClassName = ‘BD445’)
• Subqueries can consist of three or more
tables
SELECT
FROM
WHERE
ENROLLMENT.StudentNumber
ENROLLMENT
ENROLLMENT.ClassName IN
(SELECT
CLASS.Name
FROM
CLASS
WHERE
ClassTime = ‘MWF3’)
100
200
300
• To get the names of the students
SELECT
FROM
WHERE
STUDENT.Name
STUDENT
STUDENT.SID IN
(SELECT
ENROLLMENT.StudentNumber
FROM
ENROLLMENT
WHERE
ENROLLMENT.ClassName IN
(SELECT
CLASS.Name
FROM
CLASS
WHERE
JONES
BAKER
BAKER
ClassTime = ‘MWF3’))
Joining With SQL – The Inner Join
• A join that displays only the rows that have a match in both the joined tables
• Want to produce SID, StudentName, and ClassName for every student
• What two tables must be joined?
• Looking for equality between STUDENT.SID and ENROLLMENT.
StudentNumber
SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName
FROM
STUDENT, ENROLLMENT
WHERE STUDENT.SID = ENROLLMENT.StudentNumber
• Alternative Syntax
SELECT s.SID, s.Name, e.ClassName
FROM
STUDENT s -- left join table
INNER JOIN ENROLLMENT e --right join table
ON
s.SID = e.StudentNumber
SELECT s.SID, s.Name, e.ClassName
FROM
STUDENT s -- left join table
JOIN ENROLLMENT e --right join table
ON
s.SID = e.StudentNumber
http://www.codeproject.com/Tips/712941/Types-of-Join-in-SQL-Server
• What operations make up a join?
•
FROM equates to
•
WHERE expresses
100
JONES
BD445
150
PARKS
BA200
200
BAKER
BD445
200
BAKER
CS250
300
BAKER
CS125
400
RYE
BA200
400
RYE
BF410
400
RYE
CS250
450
JONES
BA200
• The WHERE clause and qualifiers
SELECT
STUDENT.SID, ENROLLMENT.ClassName
FROM
STUDENT, ENROLLMENT
WHERE
STUDENT.SID = ENROLLMENT.StudentNumber
AND
STUDENT.Name = ‘RYE’
AND
ENROLLMENT.PositionNumber = 1
400
RYE
BF410
• Data from more than two tables
SELECT
STUDENT.SID, CLASS.Name, CLASS.Time
ENROLLMENT.ClassName
FROM
STUDENT, ENROLLMENT, CLASS
WHERE
STUDENT.SID = ENROLLMENT.StudentNumber
AND
ENROLLMENT.ClassName = CLASS.Name
AND
STUDENT.Name = ‘BAKER’
200
BD445
MWF3
2
200
CS250
MWF12
1
300
CS150
MWF3
1
Other type of Joins
Need to create two new tables:
EMPLOYEE:
CREATE TABLE EMPLOYEE
(EmployeeNumber INTEGER IDENTITY(100,1) CONSTRAINT
emp_pk PRIMARY KEY,
FirstName nVARCHAR(20),
LastName nVARCHAR(20),
DepartID INTEGER,
ManagerId INTEGER)
DEPARTMENT:
CREATE TABLE DEPARTMENT
(DeptId INTEGER IDENTITY(1,1) CONSTRAINT dept_pk
PRIMARY KEY,
DepartmentName nVARCHAR(10))
What is IDENTITY?
Syntax IDENTITY (seed, increment)
A function that generates an Identity Column.
An Identity Column is a column in a table that is made up of
values generated by the database. This is much like an
autonumber Field in Microsoft Access or a sequence in Oracle.
It is an important concept in database, implemented by many
RDBMS, although each has its own terminology.
Identity is an auto-increment function that allows a unique
number to be generated when a new record is inserted into a
table.
INSERT INTO EMPLOYEE VALUES
('Sheldon', 'White',1,NULL)
INSERT INTO EMPLOYEE VALUES
('Geraldine', 'Summers',1,107)
INSERT INTO EMPLOYEE VALUES
('Gillian', 'Hall',1,107)
INSERT INTO EMPLOYEE VALUES
('William', 'Hancock',2,100)
INSERT INTO EMPLOYEE VALUES
('Sheila', 'Tunning',3,105)
INSERT INTO EMPLOYEE VALUES
('Waylon', 'Hart',3,100)
INSERT INTO EMPLOYEE VALUES
('Jennifer', 'Albacore',3,105)
INSERT INTO EMPLOYEE VALUES
('Harold', 'Spring',1,100)
INSERT INTO EMPLOYEE VALUES
('Lenny', 'Carling',NULL,100)
INSERT INTO DEPARTMENT VALUES
('Accounting')
INSERT INTO DEPARTMENT VALUES
('Marketing')
INSERT INTO DEPARTMENT VALUES
('Finance')
INSERT INTO DEPARTMENT VALUES
('Sales')
INSERT INTO DEPARTMENT VALUES
('IT')
DeptId
DepartmentName
1
Accounting
2
Marketing
3
Finance
EmployeeNumber
FirstName
LastName
DepartId
ManagerId
4
Sales
100
Sheldon
White
1
NULL
5
IT
101
Geraldine
Summers
1
107
102
Gillian
Hall
1
107
103
William
Hancock
2
100
104
Sheila
Tunning
3
105
105
Waylon
Hart
3
100
106
Jennifer
Albacore
3
105
107
Harold
Spring
1
100
108
Lenny
Carling
NULL
100
Left Outer Join
Displays all the rows from first table and matched rows from second table
Syntax:
SELECT * FROM Employee e
LEFT OUTER JOIN Department d
ON e.DepartID = d.DeptId
EmployeeNumber
FirstName
LastName
DepartId
ManagerId
DeptId
DepartmentName
100
Sheldon
White
1
NULL
1
Accounting
101
Geraldine
Summers
1
107
1
Accounting
102
Gillian
Hall
1
107
1
Accounting
103
William
Hancock
2
100
2
Marketing
104
Sheila
Tunning
3
105
3
Finance
105
Waylon
Hart
3
100
3
Finance
106
Jennifer
Albacore
3
105
3
Finance
107
Harold
Spring
1
100
1
Accounting
108
Lenny
Carling
NULL
100
NULL
NULL
Right Outer Join
Displays all the rows of second table and matched rows from first table
SELECT * FROM EMPLOYEE e
RIGHT OUTER JOIN Department d
ON e.DepartID = d.DeptId
EmployeeNumber
FirstName
LastName
DepartId
ManagerId
DeptId
DepartmentName
100
Sheldon
White
1
NULL
1
Accounting
101
Geraldine
Summers
1
107
1
Accounting
102
Gillian
Hall
1
107
1
Accounting
107
Harold
Spring
1
100
1
Accounting
103
William
Hancock
2
100
2
Marketing
104
Sheila
Tunning
3
105
3
Finance
105
Waylon
Hart
3
100
3
Finance
106
Jennifer
Albacore
3
105
3
Finance
NULL
NULL
NULL
NULL
NULL
4
Sales
NULL
NULL
NULL
NULL
NULL
5
IT
Full Outer Join
Full outer join returns all the rows from both tables whether it has been matched
or not
SELECT * FROM Employee e
FULL OUTER JOIN Department d
ON e.DepartID = d.DeptId
EmployeeNumber
FirstName
LastName
DepartId
ManagerId
DeptId
DepartmentName
100
Sheldon
White
1
NULL
1
Accounting
101
Geraldine
Summers
1
107
1
Accounting
102
Gillian
Hall
1
107
1
Accounting
103
William
Hancock
2
100
2
Marketing
104
Sheila
Tunning
3
105
3
Finance
105
Waylon
Hart
3
100
3
Finance
106
Jennifer
Albacore
3
105
3
Finance
107
Harold
Spring
1
100
1
Accounting
108
Lenny
Carling
NULL
100
NULL
NULL
NULL
NULL
NULL
NULL
NULL
4
Sales
NULL
NULL
NULL
NULL
NULL
5
IT
Cross Join
Cartesian product The size of a Cartesian product is the number of the rows in
the first table multiplied by the number of rows in the second table
SELECT * FROM EMPLOYEE
CROSS JOIN DEPARTMENT
This table will have 45rows and seven columns
Self Join
Joining the table itself called self join or theta join. A self join is used to retrieve the records having some
relation or similarity with other records in the same table. Need to use aliases for the same table to set a self join
between single table and retrieve records satisfying the condition in where clause.
SELECT e1.EmployeeNumber,e1.FirstName, e1.LastName,
e2.EmployeeNumber as ManagerId, e2.FirstName, e2.LastName
FROM EMPLOYEE e1
INNER JOIN EMPLOYEE e2
ON e1.ManagerId = e2.EmployeeNumber
EmployeeNumber
FirstName
LastName
ManagerId
FirstName
LastName
101
Geraldine
Summers
107
Harold
Spring
102
Gillian
Hall
107
Harold
Spring
103
William
Hancock
100
Sheldon
White
104
Sheila
Tunning
105
Waylon
Hart
105
Waylon
Hart
100
Sheldon
White
106
Jennifer
Albacore
105
Waylon
Hart
107
Harold
Spring
100
Sheldon
White
108
Lenny
Carling
100
Sheldon
White
SELECT
CONCAT(e1.EmployeeNumber,' - ',e1.FirstName,' ', e1.LastName) as Employee,
CONCAT(e2.EmployeeNumber,' - ', e2.FirstName,' ', e2.LastName) as Manager
FROM EMPLOYEE e1
INNER JOIN EMPLOYEE e2
ON e1.ManagerId = e2.EmployeeNumber
Employee
Manager
101 – Geraldine Summers
107 – Harold Spring
102 – Gillian Hall
107 – Harold Spring
103 – William Hancock
100 – Sheldon White
104 – Sheila Tunning
105 – Waylon Hart
105 – Waylon Hart
100 – Sheldon White
106 – Jennifer Albacore
105 – Waylon Hart
107 – Harold Spring
100 – Sheldon White
108 – Lenny Carling
100 – Sheldon White
Comparison of SQL Subquery and Join
• A join is an alternative to expressing many
subqueries
• For example, “Classes taken by undergrads?”
SELECT
FROM
WHERE
AND
DISTINCT ENROLLMENT.ClassName
ENROLLMENT, STUDENT
ENROLLMENT.StudentNumber = STUDENT.SID
STUDENT.Grade.Level NOT = ‘GR’
• As a subquery
SELECT
FROM
WHERE
DISTINCT Class.Name
ENROLLMENT
StudentNumber IN
(SELECT
SID
FROM
STUDENT
WHERE
GradeLevel NOT = ‘GR’)
• If we want to know:
“Class names and grade levels of undergrads?”
SELECT
FROM
WHERE
AND
DISTINCT ENROLLMENT.ClassName, SUDENT.GradeLevel
ENROLLMENT, STUDENT
ENROLLMENT.StudentNumber = STUDENT.SID
STUDENT.GradeLevel NOT = ‘GR’
BA200
SO
CS150
SN
BA200
FR
BF410
FR
CS250
FR
BA200
SN
• Why cannot a subquery be used?
• Thus in the previous subquery example:
SELECT
FROM
WHERE
DISTINCT Class.Name
ENROLLMENT
StudentNumber IN
(SELECT
SID
FROM STUDENT
WHERE GradeLevel NOT = ‘GR’)
• This would not work:
SELECT
FROM
WHERE
DISTINCT ENROLLMENT.ClassName, STUDENT.GradeLevel
ENROLLMENT
StudentNumber IN
(SELECT
SID
FROM
STUDENT
WHERE
GradeLevel NOT = ‘GR’)
Creating a View
A view is a virtual table based on the result of an SQL statement. A view contains
rows and columns, just like a real table. The fields in a view are fields from one or
more real tables in the database.
A view can consist of table columns, aggregates, constants, and computed columns.
SYNTAX:
CREATE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition
SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName
FROM STUDENT, ENROLLMENT
100
JONES
BD445
WHERE STUDENT.SID = ENROLLMENT.StudentNumber
150
PARKS
BA200
200
BAKER
BD445
200
BAKER
CS250
300
BAKER
CS125
400
RYE
BA200
SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName
400
RYE
BF410
FROM STUDENT, ENROLLMENT
400
RYE
CS250
WHERE STUDENT.SID = ENROLLMENT.StudentNumber
450
JONES
BA200
CREATE VIEW StudentClass
AS
CREATE VIEW StudentClass
AS
SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName
FROM Server1.CompantData.dbo.STUDENT, Server2.CompantData.dbo.ENROLLMENT
WHERE STUDENT.SID = ENROLLMENT.StudentNumber
SELECT DISTINCT Name,
MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary,
MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary,
AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary,
COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmpsPerDept
FROM HR.EmployeePayHistory eph, HR.EmployeeDepartmentHistory edh, HR.Department d
WHERE eph.BusinessEntityID = edh.BusinessEntityID
AND d.DepartmentID = edh.DepartmentID
AND edh.EndDate IS NULL
ORDER BY Name
Name
MinSalary
MaxSalary
AvgSalary
EmpsPerDept
----------------------------- --------------------- --------------------- --------------------- ---------------Executive
39.06
125.50
68.3034
4
Finance
13.4615
43.2692
23.935
10
Human Resources
13.9423
27.1394
18.0248
6
Information Services
27.4038
50.4808
34.1586
10
Marketing
13.4615
37.50
18.4318
11
Production
6.50
84.1346
13.5537
195
Purchasing
9.86
30.00
18.0202
14
Research and Development 40.8654
50.4808
43.6731
4
Sales
23.0769
72.1154
29.9719
18
Shipping and Receiving
9.00
19.2308
10.8718
6
Advantages of views:
1. Database Views can represent a subset of the data contained in a table and thus can limit
the degree of exposure of the underlying tables to the outside world. A user may have
permission to query the view, while denied access to the rest of the base table.
2. Views can join and simplify multiple tables into a single virtual table.
3. Views can act as aggregated tables, where the database engine aggregates data ( sum ,
average, etc.) and presents the calculated results as part of the data.
4. Views can hide the complexity of data. For example, a view could appear as Sales2000 or
Sales2001, transparently partitioning the actual underlying table.
5. Views take very little space to store; the database contains only the definition of a view,
not a copy of all the data that it presents.
6. Depending on the SQL engine used, views can provide extra security.
EXISTS and NOT EXISTS
• Logical operators
SELECT
FROM
WHERE
DISTINCT StudentNumber
ENROLLMENT A
EXISTS
(SELECT *
FROM
ENROLLMENT B
WHERE
A.StudentNumber = B.StudentNumber
AND
A.ClassName NOT = B.ClassName)
• Both query and subquery refer to the same
table
• Two uses assigned a different name
• What is the meaning of the subquery?
• General procedure:
• First row in A is compared with first row in B
• First Row in A compared with second row in B
200
400
• NOT EXISTS operator
SELECT
FROM
WHERE
STUDENT.Name
STUENT
NOT EXISTS
(SELECT *
FROM
ENROLLMENT
WHERE
NOT EXISTS
(SELECT
*
FROM
CLASS
WHERE
CLASS.Name = ENROLLMENT.ClassName
AND
ENROLLMENT.StudentNumber = STUDENT.SID))
• Query has three parts
CONCAT
Enables the concatenation, or linking together, of two or more string
values into a single string. The output is VCHAR or nVARCHAR.
Syntax: CONCAT(string_value1, string_value2, ……string_valueN)
STUDENT
SID
Name
Major
GradeLevel
Age
100
JONES
HISTORY
GR
21
150
PARKS
ACCOUNTING
SO
19
200
BAKER
MATH
GR
50
250
GLASS
HISTORY
SN
50
300
BAKER
ACCOUNTING
SN
41
350
RUSSELL
MATH
JR
20
400
RYE
ACCOUNTING
FR
18
450
JONES
HISTORY
SN
24
SELECT CONCAT(SID, ‘-’, Name) as ‘Number & Name’, Major
FROM STUDENT
Number & Name
Major
100-JONES
HISTORY
150-PARKS
ACCOUNTING
200-BAKER
MATH
250-GLASS
HISTORY
300-BAKER
ACCOUNTING
350-RUSSELL
MATH
400-RYE
ACCOUNTING
450-JONES
HISTORY
Changing Data
Inserting Data
• Inserting a single row
INSERT
INTO ENROLLMENT
VALUES (400, ‘BD445’, 44)
• Some data in a row not known
INSERT
INTO ENROLLMENT
(StudentNumber, ClassName)
VALUES (400, ‘BD445’)
• Mass copy rows
INSERT
INTO JUNIOR (SID, Name, Major)
SELECT SID, Name, Major
FROM
STUDENT
WHERE
GradeLevel = ‘JR’
Deleting Data
• Can be deleted one at a time
DELETE
WHERE
STUDENT
STUDENT.SID = 100
• Groups of rows can be deleted
DELETE
WHERE
DELETE
WHERE
ENROLLMENT
ENROLLMENT.StudentNumber IN
(SELECT STUDENT.SID
FROM
STUDENT
WHERE
STUDENT.Major = ‘Accounting’)
STUDENT
STUDENT.Major = ‘Accounting’
Modifying Data
• Rows can be modified one at a time
UPDATE
SET
WHERE
ENROLLMENT
PositionNumber = 44
SID = 400
UPDATE
SET
WHERE
ENROLLMENT
PositionNumber = MAX (PositionNumber) + 1
SID = 400
• Mass Updates
UPDATE
SET
WHERE
UPDATE
SET
WHERE
ENROLLMENT
ClassName = ‘BD564’
ClassName = ‘BD445’
CLASS
ClassName = ‘BD564’
ClassName = ‘BD445’
Summary
• Importance of SQL
• Operations on a single table
• Operations on two or more tables