basic sql select commands. basic relational query operations selection projection natural join...
Post on 21-Dec-2015
218 views
TRANSCRIPT
![Page 1: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/1.jpg)
Basic SQL Select Commands
![Page 2: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/2.jpg)
Basic Relational Query Operations
• Selection
• Projection
• Natural Join
• Sorting
• Aggregation: Max, Min, Sum, Count, Avg– Total– Sub totals: Group By
• Calculated field
![Page 3: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/3.jpg)
Examples
• University database:– Student: SID, Sname, Sex, Major, GPA, FID– Account: SID, Balance– Faculty: FID, Fname, Phone– Course: CID, Cname, Credits, FID– StudentCourse: SID, CID
• Download the batch file Univ.SQL to create the database.
![Page 4: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/4.jpg)
Selection
• Selection operation works on a single relation and defines a relation that contains records that satisfy the criteria.
![Page 5: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/5.jpg)
Selection Syntax• SELECT * FROM tableName WHERE criteria;• Criteria:
– =, <>, <, >, <=, >=– (), NOT, AND, OR– BETWEEN
• WHERE salary BETWEEN 1000 AND 10000;
– LIKE, NOT LIKE: %• WHERE ename LIKE ‘C%’
– Cannot say: WHERE ename = ‘C%’
– IN, NOT IN• WHERE eid IN (‘e1’,’e3’)
– IS NULL• WHERE rating IS NULL
– Arithmetic expression in criteria:• WHERE salary*0.1 < 500
![Page 6: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/6.jpg)
ROWNUM Field
• ROWNUM field is a pseudocolumn that applies to every table even though it is not displayed through a SELECT * command.
• SELECT ROWNUM, empid FROM emp;
• SELECT * FROM emp WHERE ROWNUM < = 3;
![Page 7: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/7.jpg)
Projection
• Projection operation works on a single relation and defines a vertical subset of the relation, extracting the values of specified attributes.
![Page 8: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/8.jpg)
Projection Syntax
SELECT fields FROM tableName;– Ex: SELECT empID, ename FROM
Employee;
• Alias:• Ex: SELECT eid AS empID, ename AS empName
FROM emp;
• Projection and Selection:– SELECT fields FROM tableName WHERE
criteria;
![Page 9: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/9.jpg)
Duplications due to Projection
• StudentCourse file:
• SID CID• ------ --------• s1 bics263• s1 acct101• s2 bics363• s2 bics464• s2 fin350• s3 bics263
• SELECT sid FROM StudentCourse;
![Page 10: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/10.jpg)
DISTINCT/UNIQUE
• SELECT DISTINCT fields FROM tableName WHERE criteria;– Ex. SELECT DISTINCT sid FROM Registration;
• UNIQUE– SELECT UNIQUET fields FROM tableName WHERE
criteria;
– Ex. SELECT UNIQUE sid FROM Registration;
![Page 11: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/11.jpg)
Calculated Fields
• SELECT empid,ename,salary*0.1 AS tax FROM emp;
![Page 12: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/12.jpg)
Oracle Functions• Text functions:
– UPPER, LOWER, INITCAP• Ex. SELECT INITCAP(Fname) FROM Faculty;
– SUBSTR(text,starting position, # of chars)– LPAD(text,length of text,padding symbol)
• LPAD(salary,10,’*”)– CONCAT(text1,text2)
• We can also use concatenation operator, ||
• Date functions:– SYSDATE,NEXT_DAY
• Ex. NEXT_DAY(SYSDATE,’MONDAY’)– MONTHS_BETWEEN(date1,date2)
• Ex. SELECT Ename, MONTHS_BETWEEN(SYSDATE, Hire_Date) FROM Employee;
– ADD_MONTHS(date,# of months added)
![Page 13: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/13.jpg)
Using the SQL CASE Function
SELECT sid, sname, CASE WHEN gpa < 2.0 THEN 'Poor'WHEN gpa < 3.0 THEN 'Good'ELSE 'Excellent'END AS GPAGroupFROM student;
![Page 14: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/14.jpg)
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.
![Page 15: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/15.jpg)
Natural Join Example
Faculty File: FID FnameF1 ChaoF2 SmithF3 Boxer
Student File:SID Sname FIDS1 Peter F1S2 Paul F2S3 Smith F1
Faculty Join Student =
Note: Use RelationName.FieldName to make a field name unique.
![Page 16: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/16.jpg)
Natural Join Syntax• SELECT * FROM table1 NATURAL JOIN table2;
– SELECT * FROM student NATURAL JOIN faculty;
• SELECT * FROM table1 JOIN table2 USING (join attribute);– SELECT * FROM student JOIN faculty USING (fid);
• 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;
• Note: What if the FID in the student table is named AdvisorID?
![Page 17: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/17.jpg)
Other Join Syntax
• SELECT *– FROM student s JOIN faculty f ON s.fid=f.fid;– FROM student s INNER JOIN faculty f ON
s.fid=f.fid;– FROM student JOIN faculty USING fid;
• Not supported by Oracle
![Page 18: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/18.jpg)
Join 3 Tables
• Display: SID, Sname, CID, Cname, Units– SELECT SID, Sname, CID, Cname, Units– FROM student NATURAL JOIN registration– NATURAL JOIN course;
![Page 19: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/19.jpg)
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;
• Note 1: Don’t name a table “ORDER” because ORDER is a SQL keyword.
• Note 2: If there is a WHERE clause, then the ORDER BY clause should be placed after the WHERE clause.
![Page 20: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/20.jpg)
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;
![Page 21: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/21.jpg)
GROUP BY• GROUP By one field:
– SELECT groupingFields, function(fieldname) FROM tablename GROUP BY groupingFields;
– SELECT major, count(sid) FROM student GROUP BY major;
• GROUP BY more than one field:– SELECT sex, major, count(sid) FROM student GROUP BY sex, major;
• Compute more than one subtotals:– SELECT major, count(sid), avg(gpa) FROM student – GROUP BY major
• Note 1: All grouping fields in the SELECT clause must be included in the GROUP BY clause).
• Note 2:WHERE clause must come before the GROUP BY:– SELECT major, count(sid) FROM student WHERE GPA > 3.0 GROUP BY
major;
![Page 22: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/22.jpg)
Compute Subtotals from a Join
• Compute the number of courses taken by each student:– SELECT sid, sname, COUNT(cid)
• FROM student NATURAL JOIN registration• GROUP BY sid, sname;
• Compute the number of students advised by each faculty advisor:– SELECT fid, fname, COUNT(sid)– FROM faculty NATURAL JOIN student– GROUP BY fid, fname;
![Page 23: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/23.jpg)
Adding a Criteria for the Sub Totals with HAVING
• Find majors that have at least 5 students:– SELECT major, count(sid) FROM student– GROUP BY major– HAVING count(sid) > 5;
• Sometime the aggregates are not required to display:– SELECT major FROM student– GROUP BY major– HAVING count(sid) > 5;
![Page 24: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/24.jpg)
Examples
• University database:– Student: SID, Sname, Sex, Major, GPA, FID– Account: SID, Balance– Faculty: FID, Fname, Phone– Course: CID, Cname, Units, FID– Registration: SID, CID
![Page 25: Basic SQL Select Commands. Basic Relational Query Operations Selection Projection Natural Join Sorting Aggregation: Max, Min, Sum, Count, Avg –Total –Sub](https://reader030.vdocuments.us/reader030/viewer/2022032522/56649d605503460f94a41d37/html5/thumbnails/25.jpg)
Questions• Q1: Display College of Business students’ ID and name.• Q2: Display students’ ID and name who owe university
more than $2000.• Q3: Display faculty’s name and phone if the student’s
GPA is lower than 2.0.• Q4: Display faculty’s name and phone if the faculty
advises at least 2 student.• Q5: Display students’ ID and name who are taking at
least 2 course.• Q6: Display students’ ID and name who are taking 464
and GPA < 2.0.• Q7: Display CID, Cname and the number of students in
each course.• Q8: Display SID, Sname, TotalUnits