SQL - PowerPoint PPT Presentation

About This Presentation

Title:

SQL

Description:

SELECT course#, COUNT(stuid) FROM enrollment GROUP BY course# Examples ... ORDER BY rank DESC, facname; SELECT - JOIN ... ORDER BY f.facname; (rightouterjoin) ... – PowerPoint PPT presentation

Number of Views:43

Avg rating:3.0/5.0

Slides: 58

Provided by: CSUS5

Learn more at: https://www.csus.edu

Category:

Tags: sql | by

more less

Transcript and Presenter's Notes

Title: SQL

1
Chapter 5

2
Agenda

Data Manipulation Language (DML)
SELECT
Union compatible operations
Update database

3
SQL DML - SELECT

SELECT DISTINCTALL col-expr AS
newname,...
FROM table-name alias ,...
WHERE condition
GROUP BY colm , colm
HAVING condition
ORDER BY colm , colm

4
SQL DML - SELECT

SELECT attributes (or calculations , -, /, )
FROM relation
SELECT DISTINCT attributes
FROM relation

5
Examples

SELECT stuname FROM student
SELECT stuid, stuname, credit FROM student
SELECT stuid, stuname, credit10 FROM
student
SELECT DISTINCT major FROM student

6
SQL DML - SELECT

SELECT attributes (or wild card)
FROM relation
WHERE condition

7
Examples

SELECT FROM student
SELECT stuname, major, credit FROM
student WHERE stuid S114
SELECT FROM faculty WHERE dept
MIS

8
SELECT - WHERE condition

AND OR
NOT IN
NOT IN BETWEEN
IS NULL IS NOT NULL
SOME ALL
LIKE '' multiple characters
LIKE _ single character

9
Examples

SELECT FROM faculty WHERE dept
MIS AND rank full professor
SELECT FROM faculty WHERE dept
MIS OR rank full professor
SELECT FROM faculty WHERE dept
MIS NOT rank full professor

SELECT FROM class WHERE room LIKE
B_S
SELECT FROM class WHERE room NOT
LIKE BUS
SELECT productid, productname FROM
inventory WHERE onhand BETWEEN 50 and
100

SELECT companyid, companyname FROM
company WHERE companyname BETWEEN G
AND K
SELECT productid, productname FROM
inventory WHERE onhand NOT BETWEEN 50
and 100
SELECT companyid, companyname FROM
company WHERE companyname NOT BETWEEN
G AND K

SELECT facname FROM faculty WHERE
dept IN (MIS, ACT)
SELECT facname FROM faculty WHERE
rank NOT IN (assistant, lecture)
SELECT customername FROM customer WHERE
emailadd IS NOT NULL

SELECT customername FROM customer WHERE
creditlimit IS NULL

14
SELECT - aggregate functions

COUNT ()
COUNT
SUM
AVG
MIN
MAX

15
Examples

SELECT COUNT() FROM student
SELECT COUNT(major) FROM student
SELECT COUNT(DISTINCT major) FROM student

SELECT COUNT(stuid), SUM(credit),
AVG(credit), MAX(credit),
MIN(credit) FROM student

How many different guests have made bookings for
August 2004?
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

How many different guests have made bookings for
August 2004?
SELECT COUNT(DISTINCT guestno)
FROM booking
WHERE (datefrom lt 8/31/04) AND
(dateto gt 8/1/04)

19
SELECT - GROUP

GROUP BY
HAVING

20
Examples

SELECT major, AVG(credit) FROM
student GROUP BY major
SELECT course, COUNT(stuid) FROM
enrollment GROUP BY course

21
Examples

SELECT major, AVG(credit) FROM
student GROUP BY major HAVING COUNT()
gt 2
SELECT course, COUNT(stuid) FROM
enrollment GROUP BY course HAVING
COUNT() gt 2

SELECT major, AVG(credit) FROM
student WHERE major IN (mis, act)
GROUP BY major HAVING COUNT() gt 2

23
SELECT - ORDER BY

ORDER BY
ORDER BY ... DESC

24
Examples

SELECT facname, rank FROM faculty
ORDER BY facname
SELECT facname, rank FROM faculty
ORDER BY rank DESC, facname

25
SELECT - JOIN Tables

Multiple tables in FROM clause
MUST have join conditions!!!

26
Examples

SELECT stuname, grade FROM student,
enrollment
WHERE student.stuid enrollment.stuid

SELECT enrollment.course, stuname,
major FROM class, enrollment, student WHERE
class.course enrollment.course AND
enrollment.stuid student.stuid AND
facid F114 ORDER BY enrollment.course

28
OUTER JOINS

RIGHT JOIN
LEFT JOIN
FULL JOIN
Appending () to the optional column (null) in
the join condition (Oracle)

29
Examples

SELECT f.facid, f.facname, c.course FROM
class c, faculty f WHERE c.facid ()
f.facid ORDER BY f.facname (rightouterjoin)
SELECT s.stuname, major, grade FROM student
s, enrollment e WHERE s.stuid e.stuid
() ORDER BY s.stuname (leftouterjoin)

SELECT f.facid, f.facname, c.course FROM
class c RIGHT JOIN faculty f ON c.facid
f.facid
ORDER BY f.facname (rightouterjoin)
SELECT s.stuname, major, grade FROM student
s LEFT JOIN enrollment e
ON s.stuid e.stuid
ORDER BY s.stuname (leftouterjoin)

SELECT f.facid, f.facname, c.course,
c.room FROM class c, faculty f WHERE
c.facid () f.facid () ORDER BY f.facname
(fullouterjoin)

SELECT f.facid, f.facname, c.course,
c.room FROM class c FULL JOIN faculty f ON
c.facid f.facid ORDER BY f.facname
(fullouterjoin)

33
Example

List the number of room in each hotel in London.
Hotel (hotelno, hotelname, city)
Room (roomno, hotelno, type, price)
Booking (hotelno, guestno, datefrom, dateto,
roomno)
Guest (guestno, guestname, guestaddress)

34
Example

List the number of room in each hotel in London.
SELECT r.hotelno, COUNT(roomno)
FROM room r, hotel h
WHERE r.hotelnoh.hotelno AND
city London'
GROUP BY hotelno

35
Union Compatible Operations

UNION
MINUS or EXCEPT
INTERSECT
Union compatible operator ALL
CORRESPONGINDBY column,.. (ALL includes
duplicated rows in the result)
Used between SELECT commands

36
Examples

SELECT stuid, stuname FROM sacstudent
UNION
SELECT stuid, stuname FROM chicostudent
SELECT
FROM sacstudent
UNION CORRESPONGIND BY stuid, stuname
SELECT
FROM chicostudent

SELECT stuid, stuname FROM sacstudent
EXCEPT
SELECT stuid, stuname FROM chicostudent
(SELECT stuid, stuname FROM sacstudent)
INTERSECT
(SELECT stuid, stuname FROM
chicostudent) ORDER BY 2

38
Column Alias

SELECT prodid, prodname, (salesprice -
goodofcost) profit FROM product ORDER BY
prodid
SELECT prodid, prodname, (salesprice -
goodofcost) AS profit FROM product ORDER
BY prodid

39
SUBQUERY