Transcript SQL - University of Kent

SA0951a: Introduction to SQL
Structured Query Language
Lots of SQL books in library and ebrary
e.g. Head First SQL, SQL for Mere Mortals (on SAFARI)
Connolly/Begg (4th ed.) 5.1, 5.2, 5.3.1 to 5.3.4, 5.3.7
Lots of SQL websites: e.g.
http://www.w3schools.com/SQl/default.asp# or
http://www.free-ed.net/free-ed/InfoTech/informit/ITLC15.asp: Parts 3 & 4
So what is the most sought after
skill in industry today?
A - being able to sort out margins efficiently in
Microsoft WORD
B - an ability to throw your PC at your workmate?
C - the ability to send e-mails to the wrong people
D - SQL
2
What is SQL?


A database language
3 parts
 DDL (Data Definition Language)
set up tables, create keys, change table design  lab 1

DCL (Data Control Language)

DML (Data Manipulation Language)
control access permissions to the database etc.  later
query, manipulate data in table(s)

non-procedural


3
 now and next
i.e. specify what to do, not how to do it
Widespread use in development (embedded in multiple
platforms)
http://uadisq01.uad.ac.uk:5560/isqlplus
SELECT Query Structure

Basic Form
SELECT <attribute(s)>
FROM
<table(s)>
WHERE <condition>;
Semi-colon at end
* denotes all columns
SELECT *
FROM Transport
query 1 WHERE Make='BMW' OR Make='VOLVO';
4
How many rows in the answer?
Column Alias (AS)

query 2
Renames attributes for output result
SELECT salary AS Pay
FROM Personnel
WHERE Surname = 'FRENCH';
You’ll see from your database
that French’s salary is 20184
5
RESULT
Pay
20184
In Oracle,
you can
omit AS
Using two or more tables

Can use many tables

E.g.
SELECT p.div, b.div, surname
FROM personnel p, branch b
WHERE p.div=b.div and City='BRISTOL';
query 3



Table aliases
List all tables used in FROM clause
Specify matching columns in WHERE clause!!!!!!
Make it clear which table each column belongs to


6
Use table.column notation where ambiguous
Can use table aliases to shorten
WHERE Clause


Any Boolean expression involving attribute conditions
Use



query 4
7
column names, symbols =, <, etc., calculations, numbers, text
Combine conditions with AND, OR
Text strings must be enclosed in single quotes
 case sensitive (in Oracle)!
 E.g. this will return nothing from your database
SELECT * FROM PERSONNEL
WHERE Sex='f';
LIKE operator


Used for string comparisons and pattern matching in
WHERE clause
Uses wildcards:
 _ (underscore): any single character (? in Access)
 % (percent): string of any length (* in Access)
SELECT * FROM PERSONNEL
WHERE surname LIKE '_A%E'
query 5
8

picks 'BATE' and 'MACRAE' but not 'HAMILTON' or
'RAINES'
ORDER BY

ORDER BY <column> [ASC|DESC]
Sorts the result according to the column
 Can use several levels, e.g.
SELECT *
FROM PERSONNEL
query 6 ORDER BY JobTitle, Salary Desc;


9
Sorts results by jobtitle,
and where jobtitle is the same, sorts by salary,
highest first
What's the result?
query 7
SELECT Surname,City,Salary AS Income
FROM Personnel,Branch B
WHERE Personnel.Div = b.div
AND (City LIKE '%S%' OR Surname LIKE '_R%')
ORDER BY CITY, SALARY DESC;
ASURNAME
KHAN
RAINES
HAMILTON
TRINGHAM
KUMAR
FRENCH
BRAY
MACRAE
BROCK
C
SURNAME
TRINGHAM
CITY
BRISTOL
BRISTOL
BRISTOL
BRISTOL
LONDON
LONDON
LONDON
LONDON
LONDON
INCOME
42000
25872
18534
9384
30816
20184
18000
16200
12288
CITY
INCOME
BRISTOL 9384
B
SURNAME
KHAN
RAINES
HAMILTON
TRINGHAM
FRENCH
BRAY
BROCK
D SURNAME
TRINGHAM
FRENCH
BRAY
BROCK
CITY
BRISTOL
BRISTOL
BRISTOL
BRISTOL
LONDON
LONDON
LONDON
CITY
BRISTOL
LONDON
LONDON
LONDON
INCOME
42000
25872
18534
9384
20184
18000
12288
INCOME
9384
20184
18000
12288
Explicit Join


can specify JOINS explicitly in the From clause
different types of JOIN operations:
INNER, LEFT, RIGHT, FULL
SELECT <columns>
FROM <table1>
[INNER|LEFT|RIGHT|FULL] JOIN
<table2> ON <Join Condition>;
11
query 8
SELECT city, jobtitle
Worked
FROM
branchExample
b LEFT JOIN personnel p ON b.div=p.div
WHERE city <>'BRISTOL';
12
CITY
JOBTITLE
LONDON
LONDON
LONDON
LONDON
LONDON
LONDON
LONDON
LONDON
LONDON
LONDON
LONDON
MANCHESTER
BIRMINGHAM
SECRETARY
CLERK
CHAIRMAN
DIRECTOR
MANAGER
SECRETARY
ACCOUNTANT
CONSULTANT
CONSULTANT
MANAGER
CONSULTANT
These are included in the
LEFT JOIN even though
there is no match. They
would NOT be included if
it were an INNER JOIN
More SELECT features
What if we wanted to strip out
query 9
duplicates from the answer?
Select distinct city, jobTitle
Use DISTINCT word
From branch b left join
personnel p on b.div=p.div
Where city <>'BRISTOL';
Can we perform maths in the
SELECT? YES!!!
SELECT salary/12 AS monthPay
SELECT salary + bonus AS totalPay
13
Aggregates




extends SQL
COUNT
 COUNT(*)  how many tuples?
 COUNT(DISTINCT <field>)  how many
unique values in field?
SUM, MAX, MIN, AVG
Examples



14
SELECT COUNT(*)
SELECT SUM(Salary)
SELECT
MIN(Salary),MAX(Salary),AVG(Salary)
GROUP BY

Applies aggregate to subsets
of tuples (subtotals)
SELECT Div, SUM(Salary)
FROM Personnel
GROUP BY Div
SELECT SUM(Salary)
FROM Personnel
15
SELECT Div, SUM(Salary)
FROM Personnel
DIV SUM(SALARY)
30
98400
20
95790
10
179340
SUM(SALARY)
373530
Error!
Group conditions: HAVING

HAVING



query10
16
For conditions at the group level
Can only be used with GROUP BY
WHERE is for conditions on individual rows
SELECT div, max(salary)- min(salary)
FROM PERSONNEL
GROUP by div
HAVING max(salary)- min(salary)>30000;
For each division where total salary is more
than £25,000, show no. of employees and
total salary. Which SQL will achieve this?
A
SELECT Div, COUNT(Surname), SUM(SALARY)
FROM Personnel GROUP BY Div
HAVING SUM(Salary)>25000;
B
SELECT Div, COUNT(Surname), SUM(SALARY)
FROM Personnel
WHERE Salary > 25000
GROUP BY Div;
C
D
SELECT Div,COUNT(Surname),SUM(SALARY) Total
FROM Personnel GROUP BY Div
HAVING Total>25000;
Both A and C are correct
Use of Aliases

Renaming



18
columns in the result output
table abbreviations for use within SQL
Joining a table with itself

Query11
Finds employees who
share the same
manager and the same
job title
to find multiples instances of an attribute, e.g.
SELECT p1.surname , p2.surname
FROM personnel p1, personnel p2
WHERE p1.manager = p2.manager
and p1.surname <> p2.surname
and p1.jobtitle = p2.jobtitle;
Syntax of SELECT
The full syntax of an SQL Select statement is
SELECT [DISTINCT] <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <attribute list>]
[HAVING <group condition>]
[ORDER BY <attribute list>];
[…] denotes optional parts. Explicit JOIN not included
19
Keyword Definitions

WHERE



GROUP BY


20
Collects together tuples which have the same value for the
specified fields
HAVING


A condition on individual tuples determines whether it is
included in the result
implicit joins (e.g. table1.key = table2.key)
A condition on each group determines whether that group is
included in result
ORDER BY

The result table is sorted with this clause
SQL Tutor – if you have bought the book!



Interactive practice
environment for SQL
Available in the
DatabasePlace online
You should have details and a
password in your copy of
Connolly/Begg
Lots of SQL tutors online – try Google!
TryIt in w3schools: http://www.w3schools.com/SQl/sql_tryit.asp
21