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

SQL Structured Query Language 1

• Best known and most commonly used relational database query and manipulation language. • Used in many commercial database systems. 2

SQL includes several components including: • Data definition • Query (interactive) Language • View definition • Integrity • Authorisation • Transaction control 3

SQL as a Query Language Basic query structure: SELECT Attributes FROM Relations WHERE Conditions 4

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

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

Result City Status London 20 _______________ (1 row) 7

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

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

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

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’] 11

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. 12

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

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

Removing duplicates in answers: DISTINCT SELECT DISTINCT Status FROM S 15

Result Status 10 20 30 _____ (3 rows) 16

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”. 17

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

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

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) 20

SQL Built-in Aggregate Functions SQL provides 5 standard built-in aggregate functions: • COUNT • SUM • AVG • MAX • MIN 21

• 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 only on the remaining non-NULL values. 22

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. 23

The AS clause to name columns SELECT COUNT(*) AS no_of_suppliers From S 24

Result no_of_suppliers _____________ 5 _____________ (1 row) 25

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

Result Col1 4 ______ (1 row) 27

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. 28

SELECT P#, SUM(Qty) AS Orders FROM SP GROUP BY P# This gives the sum of all quantities on order for each part number. 29

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

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 orders. 31

Result col2 P# P2 4 (1 row) 32

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' 33

Cartesian Product of Relations R1 R2 A B B C 1 b1 b1 c1 2 b2 b3 c3 34

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 35

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 36

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

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’ 38

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' 39

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 40

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. 41

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

Comparison of Sunqueries and Products/Joins • Joins or products can be used instead of all subqueries, but in some cases subqueries may be more “natural” to use (for example those involving EXISTS and NOT EXISTS - described below). 43

• 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 only the relation named in the FROM expression of the first SELECT. 44

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. 45

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

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') 47

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#) 48

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’ 49

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

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] 51

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 output. 52

• Order of the clauses cannot be changed. • Only SELECT and FROM are mandatory. 53