SA0951a: Introduction to SQLStructured Query LanguageStructured Query Language

Lots of SQL books in library and ebrarye.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# orhttp://www.free-ed.net/free-ed/InfoTech/informit/ITLC15.asp: Parts 3 & 4

2

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

3

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)control access permissions to the database etc. later

DML (Data Manipulation Language)query, manipulate data in table(s) now and next

non-procedural i.e. specify what to do, not how to do it

Widespread use in development (embedded in multiple platforms)

4

SELECT Query Structure

Basic Form

SELECT <attribute(s)>FROM <table(s)>WHERE <condition>;

SELECT *FROM TransportWHERE Make='BMW' OR Make='VOLVO';

* denotes all columns

Semi-colon at end

How many rows in the answer?

query 1

http://uadisq01.uad.ac.uk:5560/isqlplus

5

Column Alias (AS)

Renames attributes for output result

SELECT salary AS PayFROM Personnel WHERE Surname = 'FRENCH';

Pay20184

RESULT

In Oracle, you can omit AS

You’ll see from your database that French’s salary is 20184

query 2

6

Using two or more tables

Can use many tables E.g. SELECT p.div, b.div, surnameFROM personnel p, branch bWHERE p.div=b.div and City='BRISTOL';

List all tables used in FROM clause Specify matching columns in WHERE clause!!!!!! Make it clear which table each column belongs to

Use table.column notation where ambiguous Can use table aliases to shorten

Table aliases

query 3

7

WHERE Clause

Any Boolean expression involving attribute conditions Use

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 PERSONNELWHERE Sex='f';query 4

8

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 PERSONNELWHERE surname LIKE '_A%E'

picks 'BATE' and 'MACRAE' but not 'HAMILTON' or 'RAINES'

query 5

9

ORDER BY

ORDER BY <column> [ASC|DESC] Sorts the result according to the column Can use several levels, e.g.SELECT *FROM PERSONNELORDER BY JobTitle, Salary Desc; Sorts results by jobtitle,

and where jobtitle is the same, sorts by salary, highest first

query 6

SELECT Surname,City,Salary AS IncomeFROM Personnel,Branch BWHERE Personnel.Div = b.div AND (City LIKE '%S%' OR Surname LIKE '_R%')

ORDER BY CITY, SALARY DESC;

What's the result?

SURNAME CITY INCOMEKHAN BRISTOL 42000RAINES BRISTOL 25872 HAMILTON BRISTOL 18534TRINGHAM BRISTOL 9384 FRENCH LONDON 20184BRAY LONDON 18000BROCK LONDON 12288

SURNAME CITY INCOMETRINGHAM BRISTOL 9384

SURNAME CITY INCOMETRINGHAM BRISTOL 9384 FRENCH LONDON 20184BRAY LONDON 18000BROCK LONDON 12288

SURNAME CITY INCOMEKHAN BRISTOL 42000RAINES BRISTOL 25872 HAMILTON BRISTOL 18534TRINGHAM BRISTOL 9384 KUMAR LONDON 30816FRENCH LONDON 20184BRAY LONDON 18000MACRAE LONDON 16200BROCK LONDON 12288

A B

CD

query 7

11

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>;

12

Worked ExampleSELECT city, jobtitle FROM branch b LEFT JOIN personnel p ON b.div=p.divWHERE city <>'BRISTOL';

These are included in the LEFT JOIN even though there is no match. They would NOT be included if it were an INNER JOIN

query 8

CITY JOBTITLE

LONDON SECRETARY

LONDON CLERK

LONDON CHAIRMAN

LONDON DIRECTOR

LONDON MANAGER

LONDON SECRETARY

LONDON ACCOUNTANT

LONDON CONSULTANT

LONDON CONSULTANT

LONDON MANAGER

LONDON CONSULTANT

MANCHESTER  

BIRMINGHAM  

13

More SELECT features

What if we wanted to strip out duplicates from the answer?Use DISTINCT word Select distinct city, jobTitle

From branch b left join personnel p on b.div=p.divWhere city <>'BRISTOL';

Can we perform maths in the SELECT? YES!!!

SELECT salary/12 AS monthPaySELECT salary + bonus AS totalPay

query 9

14

Aggregates

extends SQL COUNT

COUNT(*) how many tuples? COUNT(DISTINCT <field>) how many

unique values in field? SUM, MAX, MIN, AVG Examples

SELECT COUNT(*) SELECT SUM(Salary) SELECT MIN(Salary),MAX(Salary),AVG(Salary)

15

GROUP BY

Applies aggregate to subsets of tuples (subtotals)

DIV SUM(SALARY)

30 98400

20 95790

10 179340

SELECT Div, SUM(Salary)FROM PersonnelGROUP BY Div

SELECT SUM(Salary)FROM Personnel

SELECT Div, SUM(Salary)FROM Personnel Error!

SUM(SALARY)

373530

16

Group conditions: HAVING

HAVING 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 PERSONNELGROUP by divHAVING max(salary)- min(salary)>30000;

query10

For each division where total salary is more than £25,000, show no. of employees and total salary. Which SQL will achieve this?

SELECT Div, COUNT(Surname), SUM(SALARY)FROM Personnel GROUP BY DivHAVING SUM(Salary)>25000;AA

BB

CC

DD

SELECT Div, COUNT(Surname), SUM(SALARY)FROM PersonnelWHERE Salary > 25000GROUP BY Div;

SELECT Div,COUNT(Surname),SUM(SALARY) TotalFROM Personnel GROUP BY DivHAVING Total>25000;

Both A and C are correct

18

Use of Aliases

Renaming columns in the result output table abbreviations for use within SQL

Joining a table with itself to find multiples instances of an attribute, e.g.

Finds employees who share the same manager and the same job title

SELECT p1.surname , p2.surnameFROM personnel p1, personnel p2WHERE p1.manager = p2.manager

and p1.surname <> p2.surnameand p1.jobtitle = p2.jobtitle;

Query11

19

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

20

Keyword Definitions

WHERE A condition on individual tuples determines whether it is included in the

result implicit joins (e.g. table1.key = table2.key)

GROUP BY Collects together tuples which have the same value for the specified fields

HAVING A condition on each group determines whether that group is included in

result

ORDER BY The result table is sorted with this clause

21

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