Basic SQL Select Commands

SELECT sid, sname,
CASE WHEN gpa < 2.0 THEN 'Poor' WHEN gpa < 3.0 THEN 'Good' ELSE 'Excellent'
END AS GPAGroup FROM student;
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.
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;
– 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:
• SELECT sid FROM StudentCourse;
DISTINCT/UNIQUE
• SELECT DISTINCT fields FROM tableName WHERE criteria; – Ex. SELECT DISTINCT sid FROM Registration;
• UNIQUE
• SELECT ROWNUM, empid FROM emp; • SELECT * FROM emp WHERE ROWNUM < = 3;
Projection
• Projection operation works on a single relation and defines a vertical subset of the relation, extracting the values of specified attributes.
• Calculated field
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
– SELECT * FROM student JOIN faculty USING (fid);
• SELECT * FROM table1, table2 WHERE table1.JoinAttribute = tableudent, faculty – WHERE student.fid = faculty.fid;
• LPAD(salary,10,‟*”)
– CONCAT(text1,text2)
• We can also use concatenation operator, ||
• Date functions:
– SYSDATE,NEXT_DAY
• Ex. NEXT_DAY(SYSDATE,‟MONDAY‟)
– SELECT fields FROM tableName WHERE criteria;
Duplications due to Projection
• StudentCourse file:
• SID CID • ------ -------• s1 bics263 • s1 acct101 • s2 bics363 • s2 bics464 • s2 fin350 • s3 bics263
• 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:
Selection Syntax
• SELECT * FROM tableName WHERE criteria; • Criteria:
– =, <>, <, >, <=, >= – (), NOT, AND, OR – BETWEEN
• WHERE salary BETWEEN 1000 AND 10000;
– MONTHS_BETWEEN(date1,date2)
• Ex. SELECT Ename, MONTHS_BETWEEN(SYSDATE, Hire_Date) FROM Employee;
– ADD_MONTHS(date,# of months added)
Using the SQL CASE Function
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)
• Download the batch file Univ.SQL to create the database.
Selection
• Selection operation works on a single relation and defines a relation that contains records that satisfy the criteria.
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:
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 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;
• 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
– SELECT UNIQUET fields FROM tableName WHERE criteria; – Ex. SELECT UNIQUE sid FROM Registration;
Calculated Fields
• SELECT empid,ename,salary*0.1 AS tax FROM emp;
• More than one aggregate:
• SELECT AVG(fieldName), MAX(fieldName), MIN(fieldName) FROM tableName; • With alias:
– SELECT AVG(gpa) AS avggpa, COUNT(sid) AS scount
• Not supported by Oracle
Join 3 Tables
• Display: SID, Sname, CID, Cname, Units
– SELECT SID, Sname, CID, Cname, Units – FROM student NATURAL JOIN registration – NATURAL JOIN course;
• WHERE salary*0.1 < 500
ROWNUM Field
• ROWNUM field is a pseudocolumn that applies to every table even though it is not displayed through a SELECT * command.