Relational Operators, SQL, and Access Query

1
Relational Operators, SQL, and Access Query

• ISYS 562

2
Relational Operators

• Set operators Union, intersection, difference,
  Cartesian product
• Relational operators Selection, projection,
  join, etc.

3
Union

• Set1A, B, C
• Set2C, D, E
• Union Members in Set 1 or in Set 2
• Set1 U Set 2 A, B, C, D, E
• Or

4
Intersect

• Members in Set 1 and in Set 2
• Set1 n Set2C
• And

5
Difference

• Set1 Set2 Members in Set1 but not in set2
  A,B
• Set2 Set1Members in Set2 but not in set1 D,
  E
• Set1-Set2 ? Set2 Set1

6
Use Union and Difference to Simulate Intersect

• Set1 n Set2 Set1 (Set1 Set2)

7
Files as Sets

• Business students file BusSt
• Science students file SciSt
• BusSt U SciSt
• BusSt n SciSt
• BusSt SciSt
• Spring 04 Student file S04St
• Fall 04 Student file F04St
• S04St F04St
• F04St S04St

8
Product

• Set1 a, b, c
• Set2 X, Y, Z
• Set1 X Set2 aX, aY aZ, bX, bY, bZ, cX, cY, cZ

9

• Faculty File
• FID Fname
• F1 Chao
• F2 Smith
• Student File
• SID Sname FID
• S1 Peter F1
• S2 Paul F2
• S3 Smith F1
• Faculty X Student

10
Projection

• Projection operation works on a single relation
  and defines a vertical subset of the relation,
  extracting the values of specified attributes.

11
Duplications due to Projection

• WorkLog file
• EID PjID Hours
• E1 P2 5
• E1 P1 4
• E2 P2 6
• E2 P1 8
• E3 P1 4
• Project eid from (WorkLog)
• Project Hours from (WorkLog)
• In practice, users determine whether to eliminate
  duplicates
• SELECT DISTINCT EID FROM WorkLog
• SELECT HOURS FROM WorkLog WHERE PjID P1

12
Natural Join

• The two relations must have common attributes.
• Combines two relations to form a new relation
  where records of the two relations are combined
  if the common attributes have the same value.
  One occurrence of each common attribute is
  eliminated.

13
Aggregate Functions

• Max, Min, Sum, Count, Avg
• Aggregates by grouping fields

14

• Student SID,Sname, GPA, Sex, Major
• Enroll SID, CID
• Course CID, Cname, Credits
• Queries
• Number of students in each course
• CID, CName, NumbeOfStudents
• Total credits for each student
• SID, Sname, TotalCredits

15
Outer Join

• Records in a relation that do not have matching
  values are included in the result relation.
  Missing values are set to null.

16
Outer Join Exmple

• Product Table
• PID Pname
• P1 TV
• P2 VCR
• P3 Computer
• P4 Tape
• P5 DVD
• TotalSales
• PID TotalSales
• P1 50
• P3 60
• P5 40
• Product Join TotalSales
• Product OuterJoin Totalsales

17
Branch BID City B1 SF B2 SM B3 SJ Full
Outer Join BID City PID B1 SF P3 B2 SM Null B
3 SJ P2 Null LA P1 Right Outer
Join BID City PID B1 SF P3 B3 SJ P2 Null LA P
1
Property PID City P1 LA P2 SJ P3 SF Left
Outer Join BID City PID B1 SF P3 B2 SM Null B
3 SJ P2
18
Structured Query Language
19
Language Overview

• Two major components
• Data definition language
• Create Table
• Data manipulation language
• Updating database
• Insert, Delete, Update
• Query database
• Select

20
CREATE TABLE

• CREATE TABLE tableName(fields and data type
  separated by commas)
• Ex.
• CREATE TABLE employee(
• eid CHAR(5),
• ename VARCHAR(40),
• sex CHAR,
• salary NUMERIC(9,2),
• hire_Date DATE)

21
SQL Insert Command
INSERT INTO tableName VALUES (field values
separated by commas) INSERT INTO tableName
(Column names separated by commas)VALUES (field
values separated by commas) Ex 1. Customer table
with CID, CNAME, CITY, RATING. a. INSERT INTO
CUSTOMER VALUES (C1, SMITH, SF, A) b.
INSERT INTO CUSTOMER (CID, CNAME,RATING) VALUES
(C1, SMITH, A)
22
SQL Delete Command
DELETE FROM tableName WHERE criteria Ex 1.
Delete a record from the Customer table. DELETE
FROM CUSTOMER WHERE CID C1
23
SQL Update Command
UPDATE tableName SET field new value WHERE
criteria Ex 1. UPDATE CUSTOMER SET RATING
A WHERE CIDC1 Ex 2. UPDATE CUSTOMER SET
CITY SF, SET RATING A WHERE CIDC1
24
Selection

• SELECT FROM tableName WHERE criteria
• Criteria
• , ltgt, lt, gt, lt, gt
• (), NOT, AND, OR
• BETWEEN
• WHERE salary BETWEEN 1000 AND 10000
• LIKE, NOT LIKE , , _
• WHERE ename LIKE C
• IN, NOT IN
• WHERE eid IN (e1,e3)
• IS NULL
• WHERE rating IS NULL

25

• Projection
• SELECT fields FROM tableName WHERE criteria
• SELECT DISTINCT fields FROM tableName WHERE
  criteria
• Field name alias AS
• Ex SELECT eid AS empID, ename AS empName FROM
  emp
• Product
• SELECT fields FROM table1, table2
• table name alias
• Ex SELECT s., f. FROM student s, faculty f

26
Natural Join

• SELECT FROM table1 NATURAL JOIN table2
• SELECT FROM student NATURAL JOIN faculty
• SELECT FROM table1, table2 WHERE
  table1.JoinAttribute table2.JoinAttribute
• SELECT FROM student, faculty
• WHERE student.fid faculty.fid
• Table name alias
• SELECT FROM student s, faculty f
• WHERE s.fid f.fid
• Other forms
• FROM student s JOIN faculty f ON s.fidf.fid
• FROM student s INNER JOIN faculty f ON
  s.fidf.fid
• FROM student JOIN faculty USING fid
• Not supported by Oracle

27
Sorting

• ORDER BY fieldName DESC
• SELECT FROM student ORDER BY sname
• SELECT FROM student ORDER BY sname DESC
• More than one field
• SELECT FROM student ORDER BY major, sname

28
Set Operators

• Union compatible
• (SELECT FROM table1)
• UNION (SELECT FROM table2)
• INTERSECT (SELECT FROM table2)
• MINUS (SELECT FROM table2)

29
Aggregates

• SELECT AVG(fieldName) FROM tableName
• COUNT(fieldName), COUNT()
• COUNT(DISTINCT fieldName)
• MAX(fieldName)
• MIN(fieldName)
• SUM(fieldName)
• More than one aggregate
• SELECT AVG(fieldName), MAX(fieldName),
  MIN(fieldName) FROM tableName
• With alias
• SELECT AVG(gpa) AS avggpa, COUNT(sid) AS scount
• FROM student

30
GROUP BY

• SELECT groupingField, function(fieldname) FROM
  tablename GROUP BY groupingField
• SELECT major, count(sid) FROM student GROUP BY
  major
• All field names in the GROUP BY clause must be
  included in the retrieved fields.
• Compute the number of courses taken by each
  student
• SELECT sid, sname, COUNT(cid)
• FROM student NATURAL JOIN enroll
• GROUP BY sid, sname
• WHERE clause must come before the GROUP BY
• SELECT major, count(sid) FROM student WHERE GPA gt
  3.0 GROUP BY major

31
Adding a Criteria for the Sub Totals with HAVING

• SELECT major, count(sid) FROM student
• GROUP BY major
• HAVING count(sid) gt 5
• Sometime the aggregates are not required to
  display
• Find majors that have more than 5 students
• SELECT major FROM student
• GROUP BY major
• HAVING count(sid) gt 5

32
Sub (or Nested ) Query

• Q Find students whose GPA is below the average.
• The criteria itself required a SQL statement.
• SELECT FROM student
• WHERE gpa lt (SELECT AVG(gpa) FROM student)

33
Sub Query with IN

• Q Find students who take at least one course and
  display their ID and name.
• SELECT sid, sname FROM
• student NATURAL JOIN enroll
• GROUP BY sid
• HAVING COUNT(cid) gt 1
• SELECT sid, sname FROM student
• WHERE sid IN (SELECT DISTINCT sid FROM enroll)
• Q Find students who take more than 5 courses and
  display their ID and name.
• SELECT sid, sname FROM student
• WHERE sid IN (SELECT sid FROM enroll GROUP BY sid
• HAVING COUNT(cid) gt 5)

34
Sub Query with ALL/SOME/ANY

• Q Find students whose gpa is greater than
  all/some bus majors gpa
• SELECT sid, sname FROM student
• WHERE gpa gt ALL(SELECT gpa FROM student
• WHERE majorbus)
• SELECT sid, sname FROM student
• WHERE gpa gt SOME (SELECT gpa FROM student
• WHERE majorbus)
• SELECT sid, sname FROM student
• WHERE gpa gt ANY (SELECT gpa FROM student
• WHERE majorbus)

35
Access Query

• Selection, Projection, Product, Join, Outer Join
• Calculated field, alias, parameter query
• GroupBy, Having
• Union, Intersect, Minus
• Wizards CrossTab, Find Duplicates, Find
  Unmatched
• Others
• Make table, update

36
Examples

• University database
• Student SID, Sname, Sex, Major, GPA, FID
• Account SID, Balance
• Faculty FID, Fname
• Course CID, Cname, Credits
• StudentCourse SID, CID
• Questions
• Display student names who owe at least 2000.
• Display faculty names who advise at least one
  student
• Display faculty names who do not advise any
  student
• Display faculty names who advise more than 2
  students
• Display total credits for all students
• Find students enrolled in 263
• Find students enrolled in 263 and 363