Transcript sql_i

SQL – Simple Queries
and JOIN
MGMT 360
Database Management
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;
Selecting Data From Database
Choosing Specific Columns:
SELECT column_name [, column_name] …
FROM table_name;
SELECT FacFirstName, FacLastName,
FacSalary
FROM Faculty;
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
Selecting Data From Database
Using Expressions:
SELECT FacFirstName,
FacLastName, FacCity,
FacSalary*1.1 AS
IncreasedSalary,
FacHireDate
FROM Faculty;
Parentheses
Multiplication Division
Subtraction Addition
Precedence Hierarchy for
Arithmetic Operators
Selecting Data From Database
Comparison Operators:
= , <, >, <=, >=, <>
SELECT OfferNo, CourseNo, FacSSN FROM
Offering
WHERE OffTerm = ‘SPRING’;
SELECT OfferNo, CourseNo, FacSSN FROM
Offering
WHERE OffTerm <> ‘FALL’;
Selecting Data From Database
SELECT FacFirstName, FacLastName, FacSSN
FROM Faculty
WHERE 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
Selecting Data From Database
Boolean OR LOGICAL OPERATORS
AND, OR, and NOT:
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty
WHERE FacSalary > 65000 AND FacRank = 'PROF‘;
SELECT OfferNo, CourseNo, FacSSN
FROM Offering
WHERE (OffTerm = 'FALL' AND OffYear = 1999)
OR (OffTerm = 'WINTER' AND OffYear = 2000)
Selecting Data From Database
Logical Operator Precedence
Parentheses
Multiplication Division
Subtraction Addition
NOT
AND
OR
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
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;
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%’;
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
Eliminating Duplicate Rows
SELECT DISTINCT CourseNo from Offering;
SELECT ALL CourseNo from Offering;
(Same as the next statement)
SELECT CourseNo from Offering;
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]
}] …]
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
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;
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;
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.
Aggregate Functions
Aggregate Function
Result
SUM ([DISTINCT] expression)
The total of (distinct) values in
the numerical expression
AVG([DISTINCT] expression)
The average of (distinct) values in
the numeric expression
COUNT([DISTINCT] expression)
The number of (distinct) non-null
values in the expression
COUNT(*)
The number of selected rows
MAX(expression)
The highest value in the
expression
MIN(expression)
The lowest value in the
expression
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;
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;
Grouping Data Overview
SQL statement processing order
(adapted from van der Lans,
p.100)
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
Joining Tables
Join Types:




Equijoin
Natural join
Self join
Outer join
Inner Joins
Equijoin:


Joining condition is based on equality between
values in the common column
Common columns appear (repeat) in the result
table
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
Joining Tables
Faculty
FacSSN
FacName
111-11-1111 joe
222-22-2222 sue
333-33-3333 sara
Offering
OfferNo FacSSN
1111
111-11-1111
2222
222-22-2222
3333
111-11-1111
Natural Join of Offering and
Faculty
FacSSN
111-11-1111
FacName OfferNo
joe
1111
222-22-2222
sue
2222
111-11-1111
joe
3333
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;
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)
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
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%'
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