SQL SELECT

1
SQL SELECT

• Database Systems Lecture 7

2
Last Lecture

• Starting SQL
• DROP TABLE
• ALTER TABLE
• INSERT, UPDATE, and DELETE
• Sequences
• For more information
• Connolly and Begg chapters 5 and 6

3
Todays hour of fun

• The data dictionary
• SQL SELECT
• WHERE clauses
• SELECT from multiple tables
• JOINs
• Aliases
• For more information
• Connolly and Begg Chapter 5

4
SQL

• SQL is based on relational algebra.
• An algebra is a system of operations where each
  operation creates an item of the same type.
• This means that every single SQL DML command
  takes relations as inputs and gives relations as
  outputs.
• These outputs can then be used as inputs in
  another operation.

5
Base Relations

• Base Relations
• Tables as stored on disk in the database.
• Virtual Relations
• Tables generated in the ether, derived by an SQL
  operation on base relations and other virtual
  relations.
• Views
• Virtual relations whose definition is stored by
  the DBMS.

6
Different Sections of SQL

• The Data Definition Language (DDL)
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

7
Different Sections of SQL

• Data Manipulation Language (DML)
• UPDATE - updates data in a database table
• DELETE FROM- deletes data from a database table
• INSERT INTO - inserts new data into a database
  table

SELECT - extracts data from a database table
8
Different Sections of SQL

• Data Control Language (DCL).
• Some examples
• DESCRIBE list the structure of a table
• GRANT - gives user's access privileges to
  database
• REVOKE - withdraw access given with the GRANT
  command
• COMMIT submit a batch of db alterations as a
  permanant change
• ROLLBACK rewind a database to a previous state

9
The Data Dictionary

• The data dictionary or catalogue stores
• Information about database tables
• Information about the columns of tables
• Other information - users, locks, indexes, and
  more
• This is metadata

• Some DBMSs let you query the catalogue
• In Oracle you can access the metadata in several
  ways
• There are system tables with metadata in them
• You can also DESCRIBE tables

10
Data Dictionaries

• To find the details of a table use
• DESCRIBE lttable namegt
• Example
• SQLgt DESCRIBE Student
• Name Null? Type
• ------------ -------- ----------
• STUID NOT NULL NUMBER(38)
• STUNAME NOT NULL VARCHAR2(50)
• STUADDRESS VARCHAR2(50)
• STUYEAR NUMBER(38)

11
Oracle Data Dictionary

• To find out what tables and sequences you have
  defined use
• SELECT table_name FROM user_tables
• The user_tables table is maintained by Oracle
• It has lots of columns, so dont use
• SELECT FROM user_tables

12
SELECT

• The SQL command you will use most often
• Queries a set of tables and returns results as a
  table
• Lots of options, we will look at many of them
• Usually more than one way to do any given query

• SQLs SELECT is different from the relational
  algebras Select
• SELECT in SQL has all the functionality of its
  equivalent in relational algebra
• But it is a bit different because SQL differs
  from the relational model, adding extra
  qualifiers

13
SQL SELECT Overview

• SELECT
• DISTINCT ALL ltcolumn-listgt
• FROM lttable-namesgt
• WHERE ltconditiongt
• ORDER BY ltcolumn-listgt
• GROUP BY ltcolumn-listgt
• HAVING ltconditiongt
• (- optional, - or)

14
Example Tables
15
Sample SELECTs

• SELECT ID FROM Student

16
Sample SELECTs

• SELECT FROM Student

17
DISTINCT and ALL

• Sometimes you end up with duplicate entries
• Using DISTINCT removes duplicates
• Using ALL retains them - this is the default

• SELECT ALL Last
• FROM Student

SELECT DISTINCT Last FROM Student
18
WHERE Clauses

• Usually you dont want all the rows
• A WHERE clause restricts the rows that are
  returned
• It takes the form of a condition - only those
  rows that satisfy the condition are returned

• Example conditions
• Mark lt 40
• First John
• First ltgt John
• First Last
• (First John) AND
• (Last Smith)
• (Mark lt 40) OR (Mark gt 70)

19
WHERE Examples

• SELECT FROM Grade
• WHERE Mark gt 60

• SELECT DISTINCT ID
• FROM Grade
• WHERE Mark gt 60

20
WHERE Example

• Given the table

• Write an SQL query to find a list of the ID
  numbers and marks in IAI of students who have
  passed (scored 40 or higher) IAI

ID Mark S103 58 S104 65
21
One Solution
SELECT ID, Mark FROM Grade WHERE (Code IAI)
AND (Mark gt 40)
22
SELECT from Multiple Tables

• Often you need to combine information from two or
  more tables
• You can get the effect of a product by using
• SELECT FROM Table1, Table2...

• If the tables have columns with the same name
  ambiguity results
• You resolve this by referencing columns with the
  table name
• TableName.Column

23
SELECT from Multiple Tables

• SELECT
• First, Last, Mark
• FROM Student, Grade
• WHERE
• (Student.ID
• Grade.ID) AND
• (Mark gt 40)

24
SELECT from Multiple Tables
SELECT ... FROM Student, Grade WHERE...
ID First Last ID Code Mark S103 John Smith S103 D
BS 72 S103 John Smith S103 IAI 58 S103 John Smith
S104 PR1 68 S103 John Smith S104 IAI 65 S103 John
Smith S106 PR2 43 S103 John Smith S107 PR1 76 S103
John Smith S107 PR2 60 S103 John Smith S107 IAI 3
5 S104 Mary Jones S103 DBS 72 S104 Mary Jones
S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Ma
ry Jones S104 IAI 65 S104 Mary Jones
S106 PR2 43
25
SELECT from Multiple Tables
SELECT ... FROM Student, Grade WHERE
(Student.ID Grade.ID) AND ...
26
SELECT from Multiple Tables
SELECT ... FROM Student, Grade WHERE
(Student.ID Grade.ID) AND (Mark gt 40)
ID First Last ID Code Mark S103 John Smith S103 D
BS 72 S103 John Smith S103 IAI 58 S104 Mary
Jones S104 PR1 68 S104 Mary Jones
S104 IAI 65 S106 Mark Jones S106 PR2 43 S107 Jo
hn Brown S107 PR1 76 S107 John Brown S107 PR2 60
27
SELECT from Multiple Tables
SELECT First, Last, Mark FROM Student, Grade
WHERE (Student.ID Grade.ID) AND (Mark gt 40)
First Last Mark John Smith 72 John Smith 58 Mary
Jones 68 Mary Jones 65 Mark Jones
43 John Brown 76 John Brown 60
28
SELECT from Multiple Tables

• When selecting from multiple tables you almost
  always use a WHERE clause to find entries with
  common values
• You have joined tables by listing them with
  commas.

• SELECT FROM
• Student, Grade, Course
• WHERE
• Student.ID Grade.ID
• AND
• Course.Code
• Grade.Code

29
SELECT from Multiple Tables
Grade
Student
Course
Student.ID Grade.ID
Course.Code Grade.Code
30
JOINs

• JOINs can be used to combine tables
• There are many types of JOIN
• CROSS JOIN
• INNER JOIN
• NATURAL JOIN
• OUTER JOIN
• OUTER JOINs are linked with NULLs - more later

• A CROSS JOIN B
• returns all pairs of rows from A and B
• A NATURAL JOIN B
• returns pairs of rows with common values
• A INNER JOIN B
• returns pairs of rows satisfying a condition

31
CROSS JOIN

• SELECT FROM
• Student CROSS JOIN
• Enrolment

32
NATURAL JOIN

• SELECT FROM
• Student NATURAL JOIN Enrolment

33
Equivalencies

• SELECT FROM
• A CROSS JOIN B
• is the same as
• SELECT FROM A, B

• SELECT FROM
• A NATURAL JOIN B
• is the same as
• SELECT FROM
• A, B
• WHERE A.col1 B.col1
• AND A.col2 B.col2
• AND...

34
INNER JOIN

• INNER JOINs specify a condition which the pairs
  of rows satisfy
• SELECT FROM
• A INNER JOIN B
• ON ltconditiongt

• Can also use
• SELECT FROM
• A INNER JOIN B
• USING
• (col1, col2,)
• Chooses rows where the given columns are equal

35
INNER JOIN

• SELECT FROM
• Student INNER JOIN Enrolment USING (ID)

36
INNER JOIN

• SELECT FROM
• Buyer INNER JOIN Property ON
• Price lt Budget

37
INNER JOIN

• SELECT FROM
• A INNER JOIN B
• ON ltconditiongt
• is the same as
• SELECT FROM A, B
• WHERE ltconditiongt

• SELECT FROM
• A INNER JOIN B
• USING(col1, col2,...)
• is the same as
• SELECT FROM A, B
• WHERE A.col1 B.col1
• AND A.col2 B.col2
• AND ...

38
JOINs vs WHERE Clauses

• JOINs (so far) are not needed
• You can have the same effect by selecting from
  multiple tables with an appropriate WHERE clause
• So should you use JOINs or not?

• Yes, because
• They lead to better, conciser queries
• NATURAL JOINs are very common
• No, because
• Support for JOINs varies a fair bit among SQL
  dialects it can be inconsistent.

39
Writing Queries

• When writing queries
• There are often many ways to write the query
• You should worry about being correct, clear, and
  concise in that order
• Dont worry about being clever or efficient

• Most DBMSs have query optimisers
• These take a users query and figure out how to
  efficiently execute it
• A simple query is easier to optimise
• Well look at some ways to improve efficiency
  later

40
Multiple Joins Example
41
Multiple Join Query

• What is the sex of the actors who have worked
  with directors under the age of 65?

42
Solution

• What is the sex of the actors who have worked
  with directors under the age of 65?

• SELECT Act.Name, Act.Sex FROM Direct
• INNER JOIN Work
• ON (Direct.name Work.director)
• INNER JOIN Act
• ON (Work.actor Act.name)
• WHERE Direct.age lt 65

43
Thinking with brackets

• This looks like an intimidating query at first
  sight, but it can be built up step by step

SELECT Act.Name, Act.Sex FROM ( ... )
WHERE Direct.age lt 65
SELECT Act.Name, Act.Sex FROM ( ( Direct
INNER JOIN Work ON (Direct.name
Work.director) ) ... ) WHERE Direct.age lt 65
SELECT Act.Name, Act.Sex FROM ( ( Direct
INNER JOIN Work ON (Direct.name
Work.director) ) INNER JOIN Act ON
(Work.actor Act.name) ) WHERE Direct.age lt 65
44
Aliases

• Aliases rename columns or tables to
• Make names more meaningful
• Make names shorter and easier to type
• Resolve ambiguous names

• Two forms
• Column alias
• SELECT column
• AS newName...

• Table alias
• SELECT ...
• FROM table
• AS newName

45
Business Alias Example
Employees

• SELECT
• E.ID AS empID,
• E.Name, W.Dept
• FROM
• Employee E
• WorksIn W
• WHERE
• E.ID W.ID

46
Business Alias Example

• SELECT
• E.ID AS empID,
• E.Name, W.Dept
• FROM
• Employee E
• WorksIn W
• WHERE
• E.ID W.ID

result
ID Name Dept 124 Dipsy
Marketing 125 La La Sales 125 La La
Marketing
47
This Lecture in Exams
48
This Lecture in Exams

• Find a list of all the CD titles. (1 mark)
• Find a list of the titles of tracks that are
  more than 300 seconds long.
• (2 marks)
• Find a list of the names of those artists who
  have a track on the CD with the title
  Compilation.
• (4 marks)

49
Next Lecture

• More SQL SELECT
• Self-joins
• Subqueries
• IN, EXISTS, ANY, ALL
• For more information
• Connolly and Begg Chapter 5

50
The Assignment

• Worth 25 of your final mark
• Two components
• A written worksheet
• A set of SQL programs
• Due Monday 1st December
• Available but not all parts covered in lectures
  yet