SQL – Simple Queries and JOIN

1. SQL – Simple Queries and JOIN MGMT 360 Database Management

2. Selecting Data From Database • General Form:SELECT list_of_columns FROM table[s] [WHERE search_conditions] • Choosing all columns:SELECT * FROM table_list;Examples: SELECT * FROM FACULTY; SELECT * FROM STUDENT;

3. Selecting Data From Database • Choosing Specific Columns: SELECT column_name [, column_name] … FROM table_name; SELECT FacFirstName, FacLastName, FacSalary FROM Faculty;

4. Selecting Data From Database • Renaming Columns: SELECT column_name as column_heading FROM table_name; SELECT FacFirstName AS First, FacLastName AS Last, FacSalary AS Salary FROM Faculty

5. Parentheses Multiplication Division Subtraction Addition Selecting Data From Database • Using Expressions: SELECT FacFirstName, FacLastName, FacCity, FacSalary*1.1 AS IncreasedSalary, FacHireDate FROM Faculty; Precedence Hierarchy for Arithmetic Operators

6. Selecting Data From Database • Comparison Operators:= , <, >, <=, >=, <>SELECT OfferNo, CourseNo, FacSSN FROM Offering WHERE OffTerm = ‘SPRING’;SELECT OfferNo, CourseNo, FacSSN FROM OfferingWHERE OffTerm <> ‘FALL’;

7. Selecting Data From Database SELECT FacFirstName, FacLastName, FacSSN FROM FacultyWHERE to_number(to_char(FacHireDate, ‘YYYY’)) > 1991;SELECT FacFirstName, FacLastName, FacCity, FacSalary*1.1 AS IncreasedSalary, FacHireDate FROM Faculty WHERE to_number(to_char(FacHireDate, 'YYYY')) > 1991

8. Selecting Data From Database • Boolean OR LOGICAL OPERATORS AND, OR, and NOT:SELECT FacFirstName, FacLastName, FacSalaryFROM FacultyWHERE FacSalary > 65000 AND FacRank = 'PROF‘;SELECT OfferNo, CourseNo, FacSSN FROM Offering WHERE (OffTerm = 'FALL' AND OffYear = 1999) OR (OffTerm = 'WINTER' AND OffYear = 2000)

9. Selecting Data From Database Logical Operator Precedence Parentheses Multiplication Division Subtraction Addition NOT AND OR

10. Selecting Data From Database • Ranges: • With the comparison operators > and < • With the keyword BETWEEN SELECT FacFirstName, FacLastName, FacHireDate FROM Faculty WHERE to_number(to_char(FacHireDate, ‘YYYY’)) BETWEEN 1994 AND 1995 SELECT FacFirstName, FacLastName, FacHireDate FROM Faculty WHERE to_number(to_char(FacHireDate, ‘YYYY’)) >= 1994 AND to_number(to_char(FacHireDate, ‘YYYY’)) <= 1995

11. Selecting Data From Database • Lists Using IN and NOT IN: SELECT StdFirstName, StdLastNAme, StdMajor FROM Student WHERE StdClass IN ('JR', 'SR'); • Using IS NULL, NOT NULL:SELECT OfferNo, CourseNo FROM Offering WHERE FacSSN IS NULL;

12. Selecting Data From Database • Matching Character Strings Using LIKE: • Two wildcard characters for use with LIKE: • % any string of zero or more characters • - any single character (Used with %) SELECT CourseNo FROM Offering WHERE CourseNo LIKE 'IS%‘; SELECT CourseNo FROM Offering WHERE CourseNo LIKE ‘IS_2%’;

13. Eliminating Duplicate Rows • Eliminating Duplicate Rows: Distinct and All • DISTINCT returns only those rows that are unique • ALL returns all qualified rows, by default all rows are returned by a SELECT query • SELECT [DISTINCT | ALL] select_list • Distinct combination of items • DISTINCT treats each null in a particular column as a duplicate of all other null values in that column

14. Eliminating Duplicate Rows SELECT DISTINCT CourseNo from Offering; SELECT ALL CourseNo from Offering; (Same as the next statement)SELECT CourseNo from Offering;

15. Sorting Data • Use of ORDER BY clause to order/sort the rows from a select query SELECT select_list FROM table_list [WHERE conditions] [ORDER BY {expression [ASC | DESC] | position [ASC | DESC]} [, {expression [ASC | DESC] | position [ASC | DESC] }] …]

16. Sorting Data • Most systems require that each sort element (column or expression) appear in the select list • If multiple columns are specified the sort is done based on the order in which columns are specified • Default sort direction is ASC • NULL will appear at the beginning or the end of the sorted list

17. Sorting Data SELECT StdFirstName, StdLastName, StdCity FROM Student ORDER BY StdLastName; SELECT StdClass, StdFirstName, StdLastNAme, StdMajor FROM Student WHERE StdClass IN ('JR', 'SR') ORDER BY StdClass ASC, StdLastName DESC;

18. Sorting Data • Three options for expressions: • Use an integer to represent the expression’s position in the select list • Use a column heading in the select list • Use the whole expression SELECT StdClass, StdFirstName, StdLastNAme, StdMajor FROM Student WHERE StdClass IN ('JR', 'SR') ORDER BY 3 ASC, 1 DESC;

19. Aggregate Functions • Used to obtain summary values • Aggregate functions always take an argument • General syntax of aggregate functions: • aggregate_function ([DISTINCT] expressions)(You can omit the DISTICT clause) • Aggregate functions are used in conjunction with GROUP BY clause in a SELECT query.

20. Aggregate Functions

21. Aggregate Functions and Grouping Data SELECT FacRank, AVG(FacSalary) AS AvgSalary FROM Faculty GROUP BY FacRank; SELECT FacRank, Max(FacSalary) AS Maximum_Salary, Min(FacSalary) AS Minimum_Salary FROM Faculty GROUP BY FacRank; SELECT CourseNo, Count(DISTINCT FacSSN) AS No_Insructors FROM Offering GROUP BY CourseNo;

22. Conditions on Grouped Data • Use HAVING to select row that satisfy some condition on grouped data • You can still use WHERE clause to select rows according to some condition • WHERE eliminates the rows first and then groups the data • HAVING groups first and then eliminates the rows SELECT StdMajor, AVG(StdGPA) AS AvgGpa FROM Student WHERE StdClass IN ('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.1;

23. Grouping Data Overview SQL statement processing order (adapted from van der Lans, p.100)

24. Joining Tables • Most databases have many tables • Combine tables using the join operator (Inner Join or Outer Join) • Specify matching condition • Can be any comparison but usually = • PK = FK most common join condition • Relationship diagram useful when combining tables • Columns being compared should have similar values • Join column data types must be compatible • Use alias names to improve readability

25. Joining Tables Inner Joins • Join Types: • Equijoin • Natural join • Self join • Outer join • Equijoin: • Joining condition is based on equality between values in the common column • Common columns appear (repeat) in the result table

26. Joining Tables • Natural Join: • Same as equijoin except join column displayed only once • Natural Join may be based on other relational operators • Self-join: Compares values within a column of a single table • Outer Joins: • Rows that do not have matching values in common columns are included in the result table

27. Joining Tables

28. Joining Tables: Inner join • List Tables in the FROM clause • List conditions in the WHERE clause SELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM Offering, Faculty WHERE OffTerm = 'FALL' AND OffYear = 1999 AND FacRank = 'ASST' AND CourseNo LIKE 'IS%' AND Faculty.FacSSN = Offering.FacSSN;

29. Joining Tables: Outer Join • One-Sided Outer joins (using a + sign): SELECT OfferNo, CourseNo, FacLastName, Faculty.FacSSN FROM Offering, Faculty WHERE Faculty.FacSSN (+) = Offering.FacSSN; (Starts with all the rows from the Offering Table) SELECT OfferNo, CourseNo, FacLastName, Faculty.FacSSN FROM Offering, Faculty WHERE Faculty.FacSSN = Offering.FacSSN (+); (Starts with all the rows from the Faculty Table)

30. Joining Tables: Outer Join • Full Outer Join: • SELECT FacSSN, FacFirstName, FacLastName, • FacSalary, StdSSN, StdFirstName, • StdLastName, StdGPA • FROM Faculty, Student • WHERE Student.StdSSN = Faculty.FacSSN (+) • UNION • SELECT FacSSN, FacFirstName, FacLastName, • FacSalary, StdSSN, StdFirstName, • StdLastName, StdGPA • FROM Faculty, Student • WHERE Student.StdSSN (+) = Faculty.FacSSN

31. Joining Tables: Outer Join • Mixing Inner and Outer Joins: • SELECT OfferNo, Offering.CourseNo, OffTerm, • CrsDesc, Faculty.FacSSN, FacLastName • FROM Faculty, Course, Offering • WHERE Offering.FacSSN = Faculty.FacSSN (+) • AND Course.CourseNo = Offering.CourseNo • AND Course.CourseNo LIKE 'IS%'

32. Functions • ORACLE has several functions that you can use: • Arithmetic functions: ABS, CEIL, FLOOR etc. • String Manipulation Function: CONCAT, LENGTH, LPAD etc. • Date Functions: SYSDATE, ADD_MONHTS, LAST_DATE etc. More details can be found in your ORACLE Reference Book