More SQL Select - Database Systems - Lecture Slides, Slides of Database Management Systems (DBMS)

Download More SQL Select - Database Systems - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

More SQL Select

But first…

Track

cID Num Title Time aID

1 1 Violent 239 1 1 2 Every Girl 410 1 1 3 Breather 217 1 1 4 Part of Me 279 1 2 1 Star 362 1 2 2 Teaboy 417 2

CD cID Title Price 1 Mix 9. 2 Compilation 12.

Artist aID Name 1 Stellar 2 Cloudboy

SQL SELECT Overview

SELECT

[DISTINCT | ALL] <column-list>

FROM <table-names>

[WHERE ]

[ORDER BY <column-list>]

[GROUP BY <column-list>]

[HAVING ]

([] - optional, | - or)

This ‘AS’ is optional, but Oracle

doesn’t accept it at all

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

Example

SELECT

E.ID AS empID,

E.Name, W.Dept

FROM

Employee E

WorksIn W

WHERE

E.ID = W.ID

empID Name Dept

123 John Marketing

124 Mary Sales

124 Mary Marketing

Aliases and ‘Self-Joins’

Aliases can be used to

copy a table, so that it

can be combined with

itself:

SELECT A.Name FROM

Employee A, Employee B

WHERE A.Dept=B.Dept

AND B.Name=‘Andy’

Employee

Name Dept

John Marketing

Mary Sales

Peter Sales

Andy Marketing

Anne Marketing

Aliases and Self-Joins

A.Name A.Dept B.Name B.Dept John Marketing John Marketing Mary Sales John Marketing Peter Sales John Marketing Andy Marketing John Marketing Anne Marketing John Marketing John Marketing Mary Sales Mary Sales Mary Sales Peter Sales Mary Sales Andy Marketing Mary Sales Anne Marketing Mary Sales

SELECT … FROM Employee A, Employee B …

Aliases and Self-Joins

A.Name A.Dept B.Name B.Dept John Marketing John Marketing Andy Marketing John Marketing Anne Marketing John Marketing Mary Sales Mary Sales Peter Sales Mary Sales Mary Sales Peter Sales Peter Sales Peter Sales John Marketing Andy Marketing Andy Marketing Andy Marketing Anne Marketing Andy Marketing

SELECT … FROM Employee A, Employee B

WHERE A.Dept = B.Dept

Aliases and Self-Joins

SELECT A.Name FROM Employee A, Employee B

WHERE A.Dept = B.Dept AND B.Name = ‘Andy’

A.Name John Andy Anne

The result is the names of all employees who work in the

same department as Andy.

Subqueries

• A SELECT statement

can be nested inside

another query to

form a subquery

• The results of the

subquery are passed

back to the

containing query

• E.g. get the names

of people who are in

Andy’s department:

SELECT Name

FROM Employee

WHERE Dept =

(SELECT Dept

FROM Employee

WHERE Name=‘Andy’)

Subqueries

• Often a subquery will

return a set of

values rather than a

single value

• You can’t directly

compare a single

value to a set

• Options
  • IN - checks to see if a value is in the set
  • EXISTS - checks to see if the set is empty or not
  • ALL/ANY - checks to see if a relationship holds for every/one member of the set

(NOT) IN

• Using IN we can see

if a given value is in

a set of values

• NOT IN checks to

see if a given value

is not in the set

• The set can be given

explicitly or from a

subquery

SELECT

FROM

WHERE

IN

SELECT

FROM

WHERE

NOT IN

(NOT) IN

Employee

Name Department Manager

John Marketing Chris Mary Marketing Chris Chris Marketing Jane Peter Sales Jane Jane Management

SELECT *

FROM Employee

WHERE Name NOT IN

(SELECT Manager

FROM Employee)

(NOT) IN

• First the subquery

SELECT Manager FROM Employee

• is evaluated giving
  • This gives

SELECT * FROM Employee WHERE Name NOT IN (‘Chris’, Manager ‘Jane’) Chris Chris Jane Jane

Name Department Manager John Marketing Chris Mary Marketing Chris Peter Sales Jane