SQL Structured Query Language 1 Best known and most commonly used - - PowerPoint PPT Presentation

1

SQL Structured Query Language

2

• Best known and most commonly used

relational database query and manipulation language.

• Used in many commercial database

systems.

3

SQL includes several components including:

• Data definition
• Query (interactive) Language
• View definition
• Integrity
• Authorisation
• Transaction control

4

SQL as a Query Language

Basic query structure: SELECT Attributes FROM Relations WHERE Conditions

5

SELECT A1, …, An FROM R1, …, Rm WHERE C The WHERE part may be missing.

6

Example: SELECT City, Status FROM S WHERE Sname = ‘Smith’

7

Result

City Status London 20 _______________ (1 row)

8

Querying One Relation

Examples: Get the names of all the suppliers in London. SELECT Sname FROM S WHERE City = ‘London’

9

Get the names and cities of all the suppliers with status higher than 10. SELECT Sname, City FROM S WHERE Status>10

10

Use of asterisk (*)

SELECT * FROM P WHERE City = ‘London’ The asterisk (*) means that all columns of the table P are to be presented.

11

The Use of IN and NOT IN

The condition in WHERE clauses can refer to a set of values. To do this the keywords IN and NOT IN may be used. Examples: SELECT Sname FROM S WHERE City IN [‘London’, ‘Paris’]

12

In does not add much to SQL expressive power. SELECT Sname FROM S WHERE City=‘London’ OR City= ‘Paris’; IN is more efficient when set contains many values.

13

SELECT Sname FROM S WHERE City NOT IN [‘Athens’, ‘Rome’]

14

IS NULL

SELECT EMP#, Name FROM Emp WHERE MgrEmp# IS NULL Negated version IS NOT NULL tests for non-NULL values.

15

Removing duplicates in answers: DISTINCT

SELECT DISTINCT Status FROM S

16

Result Status 10 20 30 _____ (3 rows)

17

Sorting

The rows of the resulting relations can be sorted by the values in one or more columns. SELECT Sname, City, Status FROM S ORDER BY Status The above query will present the result in ascending order of “status”.

18

Result Sname City Status Jones Paris 10 Smith London 20 Clark London 20 Blake Paris 30 Adams Athens 30 (5 rows)

19

SELECT S#, P#, Qty From SP ORDER BY P# ASC, Qty DESC

20

Result S# P# Qty S1 P1 300 S2 P1 300 S2 P2 400 S1 P2 200 S3 P2 200 S4 P2 200 S1 P3 400 S4 P4 300 S1 P4 200 S4 P5 400 S1 P5 100 S1 P6 100 (12 ROWS)

21

SQL Built-in Aggregate Functions

SQL provides 5 standard built-in aggregate functions:

• COUNT
• SUM
• AVG
• MAX
• MIN

22

• Each returns a single value.
• COUNT, MIN, MAX apply to numeric and

non-numeric fields.

• SUM, AVG apply to numeric fields only.
• Apart from COUNT, each function

eliminates NULL values first and operates

• nly on the remaining non-NULL values.

23

SELECT COUNT(*) From S This counts the number of the rows in table S, and displays the number in a table which has a single row and a single column.

24

The AS clause to name columns

SELECT COUNT(*) AS no_of_suppliers From S

25

Result no_of_suppliers _____________ 5 _____________ (1 row)

26

SELECT COUNT (DISTINCT S#) From SP This counts the number of distinct suppliers we have orders with according to table SP.

27

Result Col1 4 ______ (1 row)

28

Built-in Aggregate Functions and Grouping

To increase their utility, the built-in aggregate functions can be applied to groups of rows within a table. Such groups are formed by considering those rows that have the same value for a specified column.

29

SELECT P#, SUM(Qty) AS Orders FROM SP GROUP BY P# This gives the sum of all quantities on

• rder for each part number.

30

Result P# Orders P1 600 P2 1000 P3 400 P4 500 P5 500 P6 100 (6 rows)

31

SELECT P#, COUNT(*) FROM SP GROUP BY P# HAVING COUNT(*)>2 This gives the total number of orders for each part which has more than two

• rders.

32

Result P# col2 P2 4 (1 row)

33

Querying Multiple Tables

Examples of using product, natural join, union: Get supplier names for suppliers who supply part P2. SELECT S.Sname FROM S, SP WHERE S.S#=SP.S# AND SP.P# ='P2'

34

Cartesian Product of Relations

R1 R2 A B B C 1 b1 b1 c1 2 b2 b3 c3

35

R1*R2

R1.A R1.B R2.B R2.C 1 b1 b1 c1 1 b1 b3 c3 2 b2 b1 c1 2 b2 b3 c3

36

R1 NATURAL JOIN R2

R1.A R1.B R2.B R2.C 1 b1 b1 c1 1 b1 b3 c3 2 b2 b1 c1 2 b2 b3 c3 A B C 1 b1 c1

37

SELECT S.Sname FROM S NATURAL JOIN SP WHERE SP.P# ='P2'

38

Get the names and quantities of parts supplied by S1. SELECT P.Pname, SP.Qty FROM P, SP WHERE P.P# = SP.P# AND SP.S# = ‘S1’

39

Get part numbers of parts that are either stored in London or are supplied by S1, or both. SELECT P.P# FROM P WHERE P.City='LONDON' UNION SELECT SP.P# FROM SP WHERE SP.S#='S1'

40

The rename operation

SELECT SP1.S# FROM SP SP1, SP SP2 WHERE SP2.S# = S1 AND SP1.P# = P1 AND SP2.P# = P1 AND SP1.Qty>SP2.Qty

41

The Use of Subqueries

SELECT S.Sname FROM S WHERE S.S# IN (SELECT SP.S# FROM SP WHERE SP.P# ='P2') Notice that we could have done the above query with a product or a join.

42

SELECT S.Sname FROM S, SP WHERE S.S# = SP.S# AND SP.P# ='P2'

43

Comparison of Sunqueries and Products/Joins

• Joins or products can be used instead
• f all subqueries, but in some cases

subqueries may be more “natural” to use (for example those involving EXISTS and NOT EXISTS - described below).

44

• Subqueries cannot be used to express

all joins/products. In particular in a join/product, the displayed columns may come from any of the joined

• relations. But when using a subquery,

the displayed columns may come from

• nly the relation named in the FROM

expression of the first SELECT.

45

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS are logical expressions which have truth values (false or true). Examples: Get supplier names of those suppliers which supply part P2.

46

SELECT S.Sname FROM S WHERE EXISTS (SELECT * FROM SP WHERE S.S#=SP.S# AND SP.P# ='P2')

47

Get supplier names for suppliers who do not supply part p2. SELECT S.Sname FROM S WHERE NOT EXISTS (SELECT * FROM SP WHERE S.S#=SP.S# AND SP.P# ='P2')

48

Get the supplier numbers of those suppliers who supply at lease two different parts. SELECT S.S# FROM SP SP1 WHERE EXISTS (SELECT * FROM SP SP2 WHERE SP1.S#=SP2.S# AND SP1.P# NOT=SP2.P#)

49

Some Additional Features

Consider relation FILM, as below: FILM(Name, Director, Producer) SELECT Producer, Director FROM FILM WHERE Name LIKE %mountain Also %third% sequence of characters of any length containing ‘third’

50

SELECT Director FROM FILM WHERE Name LIKE _ _ _ _ _ _ : any single character

51

Summary: SELECT

SELECT [DISTINCT | ALL] {* | [column_expression [AS new_name]] [,...] } FROM table_name [alias] [, ...] [WHERE condition] [GROUP BY column_list] [HAVING condition] [ORDER BY column_list]

52

SELECT Specifies which columns are to appear in output. FROM Specifies table(s) to be used. WHERE Filters rows. GROUP BY Forms groups of rows with same column value. HAVING Filters groups subject to some condition. ORDER BY Specifies the order of the

• utput.

53

• Order
• f

the clauses cannot be changed.

• Only

SELECT and FROM are mandatory.