Please send questions to
st10@humboldt.edu .
-- CIS 315 - Week 4 Lab - 09-15-10
-- today: we START our discussion of the SQL SELECT
-- statement, "a simple and powerful way of expressing
-- ad-hoc queries against the database" [Sunderraman?]
-- UNFORTUNATE language collision:
-- do not confuse the SQL SELECT statement
-- with the relational SELECTION operator;
-- ...you use the SQL SELECT statement to
-- express ALL the relational operations,
-- selection AND projection AND the joins
-- AND union AND Cartesian product AND...
-- NOTE about SQL: it is NOT CASE SENSITIVE;
-- select SELECT Select sELeCT --- all work!
--
-- BUT: string literals ARE case sensitive, so
-- BE CAREFUL
-- ('Jones' != 'jones')
/**********/
-- basic SELECT syntax
-- select [distinct] <one or more expressions, separated by commas>
-- from <one or more expressions representing tables,
-- separated by commas>
-- [where <search condition>];
-- CLASS STYLE STANDARD:
-- * start the FROM clause on its own line,
-- * start the WHERE clause on its own line
-- basic SELECT *semantics*
-- step 1: do the Cartesian product of everything
-- in the FROM clause
-- step 2: do a relational selection of that
-- product from step one, selecting
-- ONLY those rows that satisfy the
-- WHERE clause's condition
-- step 3: do a relational projection of
-- ONLY those expressions from the
-- SELECT clause
-- so: do you see how a select statement allows
-- you to express COMBINATIONS of relational operations?
-- in the SELECT clause, * is a shorthand meaning
-- ALL of the columns in ALL of the table(s) in that
-- that select statement's FROM clause...
-- (in the order the columns appear in those tables'
-- create table statement(s))
-- (we now ran the set-up-ex-tbls.sql script from the
-- public course web page to create tables named
-- empl, dept, and customer
-- NOTE the foreign key syntax,
-- how you say that a column can't contain null,
-- how you give values for just SOME of the columns
-- in a newly-inserted row,
-- cascade constraints,
-- and how you can specify a default column value...)
-- NEXT: how to express the CLASSIC THREE relational
-- operations in SQL
-- (selection, projection, and equi-join/natural join)
spool 315lab04-results.txt
/**********/
-- how to express "pure" RELATIONAL PROJECTION in SQL
-- select distinct <cols-to-project>
-- from <tbl>;
-- how can I project the empl_last_name and salary columns
-- from the empl table?
select distinct empl_last_name, salary
from empl;
-- how can I project the job_title and dept_num columns
-- from the empl table?
select distinct job_title, dept_num
from empl;
-- what happens if you leave out the distinct?
-- you get ALMOST the relational projection
-- (including any duplicate rows!)
select job_title, dept_num
from empl;
/**********/
-- RELATIONAL SELECTION
-- select *
-- from <tbl>
-- where <condition specifying the rows to select>;
-- how about a selection of all the rows of empl
-- where the salary is more than 2500?
-- note: Oracle supports < > <= >= =
select *
from empl
where salary > 2500;
-- a selection of all the rows of empl
-- where the salary is equal to 5000
select *
from empl
where salary = 5000;
-- a selection of all the rows
-- of empl whose job title is 'Salesman'
-- (note: case MATTERS in string LITERALS...
-- works:
select *
from empl
where job_title = 'Salesman';
-- selects NO rows:
select *
from empl
where job_title = 'salesman';
/**********/
-- walk through an relational equi-join
-- select *
-- from tbl1, tbl2
-- where <join condition>;
-- NOTE: what happens if you leave out the join condition
-- (no where with the proper conditiion)?
-- ...you get the CARTESIAN PRODUCT! which is rarely what you want!!
select *
from empl, dept
where empl.dept_num = dept.dept_num;
-- this is a CARTESIAN PRODUCT, NOT an equi-join!!!
select *
from empl, dept;
-- for a natural join? you must project all of the columns except
-- the one "duplicate" column you don't want to see...
select empl_num, empl_last_name, job_title, mgr, salary, commission,
empl.dept_num, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num;
/**********/
-- "ANSI joins"
-- starting with Oracle 9i, Oracle also supports ANSI SQL/99 joins
-- as well;
-- I showed you "old" Oracle SQL/86 style joins above;
-- advantage: * makes the join semantics more apparent
-- disadvantage: * too easy to accidentally write an inadvertent
-- Cartesian product
-- Here is (I believe) the ANSI SQL/99 join syntax:
-- (NOTE: the syntax is DIFFERENT for DIFFERENT kinds of joins...!
-- Hopefully we'll get to at least the outer join syntax
-- later in the semester...)
-- (reference:
-- http://certcities.com/editorial/columns/story.asp?EditorialsID=106)
-- (the uppercase JOIN and ON are just for emphasis -- any case is OK for
-- those)
select *
from empl
JOIN dept ON (empl.dept_num = dept.dept_num);
-- also note: there is also a NATURAL JOIN option, but two different
-- columns I found (the reference above, and
-- http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm)
-- ...recommend against using that, because of potential
-- ambiguity problems and possible headaches if column names
-- change over time. SO -- we will not use that syntax, and
-- the NATURAL JOIN syntax is NOT a permitted style in this course.
/**********/
-- now: note that you can COMBINE these operations in
-- a single select statement!
-- NOTE: AND and OR are the boolean and and or operators
-- for example: I want to (purely) project the salary and department
-- name for the equi-join of empl and dept
select distinct salary, dept_name
from empl, dept
where empl.dept_num = dept.dept_num;
select distinct salary, dept_name
from empl
join dept on (empl.dept_num = dept.dept_num);
-- what if ONLY want those projected for the selection
-- of rows with dept_name of Research?
select distinct salary, dept_name
from empl, dept
where empl.dept_num = dept.dept_num
and dept_name = 'Research';
select distinct salary, dept_name
from empl
join dept on (empl.dept_num = dept.dept_num)
where dept_name = 'Research';
spool off