Please send questions to
st10@humboldt.edu .
-------
-- 315lab05-projected.sql
-- example SQL and SQL*Plus commands for CIS 315 -
-- Week 5 Lab, 9-22-10
--
-- last modified: 09-22-10 (post-lab)
-------
--******
-- WEEK 5 LAB EXERCISE. part 1:
-- start a SQL script named lab5.sql, and put in one or more
-- comments containing your name and some indication that
-- this is the week 5 lab...
--******
-- make sure we have "clean" copies of the example
-- tables empl, dept, and customer
-- (note that I copied set-up-ex-tbls.sql into current
-- directory...)
@ set-up-ex-tbls
-- let's spool the results into a file
spool lab5-results.txt
--********
-- LAB EXERCISE PART 2 -
-- add spool and spool off commands to your lab5.sql script
-- * let's spool to lab5-results.txt
--********
-- and, let's set up a new orders table:
drop table orders cascade constraints;
-- sysdate is an Oracle function that returns the current date
-- (it is a no-argument function -- Oracle PL/SQL does not
-- require () for a no-argument function call)
create table orders
(order_num char(5),
cust_id char(6) not null,
empl_num char(4) not null,
-- NOTE: had to put not
-- null AFTER default!!
order_made date default sysdate not null,
order_filled date,
order_total decimal(5,2) not null,
primary key (order_num),
foreign key (cust_id) references customer,
foreign key (empl_num) references empl
);
insert into orders
values
('00001', '123456', '7521', '02-Feb-2004', '04-Feb-2004', 58.99);
insert into orders(order_num, cust_id, empl_num, order_total)
values
('00002', '345678', '7654', 99.97);
-- remember the SQL select statement semantics presented in lab!!!
--
-- 1) do a Cartesian product of the tables listed in the
-- FROM clause;
-- 2) do a relational selection of the resulting rows that meet
-- the condition in the WHERE clause
-- 3) do a "partial" projection (only pure if DISTINCT is
-- included!) of the expressions/columns in the SELECT
-- clause of the resulting rows
-- (these are for SIMPLE SQL select statements, note...)
--*********************************************************
-- COMBINING RELATIONAL OPERATIONS
--
-- AND ... after all of our examples using select to specify
-- desired "pure" relational operations...
-- we are now ready to note that SQL's select allows us to
-- COMBINE these relational operations within a single
-- SQL select statement for fun and information!
--*********************************************************
-- for example,
-- we often don't do a "pure" natural or equi-join --- we
-- simply project from the equi-join the desired columns;
select empl_last_name, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num;
select empl_last_name, dept_name, dept_loc
from empl join dept on (empl.dept_num = dept.dept_num);
-- what if I want JUST the last names and salaries of employees
-- who are managers?
-- combine project and selection:
select empl_last_name, salary
from empl
where job_title = 'Manager';
-- give me job_titles and hiredates for employees with commissions
-- greater than 0 (combination selection and projection)
select job_title, hiredate
from empl
where commission > 0;
-- SQL GOTCHA: how can I select JUST rows where a column is
-- null (or not null)?
-- *************************************************
-- IS NULL IS NOT NULL
-- ...to select such rows!!
-- *************************************************
-- when you try THIS, no rows will be selected --
-- = doesn't work to see if a column contains null;
select empl_last_name
from empl
where commission = NULL;
-- this WILL give the last names of employees with no
-- commission (NULL commission)
select empl_last_name
from empl
where commission is NULL;
-- likewise, this does NOT work like we might think...
select empl_last_name
from empl
where commission != NULL;
-- but this does:
select empl_last_name
from empl
where commission is not NULL;
--*******
-- lab exercise part 3 -
-- write a query that projects the employee last name and
-- salary for employees who have a MGR column whose
-- value is NULL.
--*******
-- give me the employees and their department names and locations
-- ONLY for employees hired since 12-1-1991
-- (combination select, join, and project...)
select empl_last_name, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num
and hiredate > '01-dec-1991';
select empl_last_name, dept_name, dept_loc
from empl join dept on (empl.dept_num = dept.dept_num)
where hiredate > '01-dec-1991';
-- and we will of course go much farther from here!
--*********************************************************
-- WHEN TABLE NAMES are REQUIRED before a column name
--*********************************************************
-- what if I wanted the department number in there, too?
-- this WON'T WORK:
select empl_last_name, dept_num, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num
and hiredate > '01-dec-1991';
select empl_last_name, dept_num, dept_name, dept_loc
from empl join dept on (empl.dept_num = dept.dept_num)
where hiredate > '01-dec-1991';
-- IF a column name is in MORE than 1 table involved in the from
-- clause --- MUST precede it by the name of the table ANYWHERE
-- it appears in the select statement, EVEN in the select clause;
-- (telling WHICH table's copy is desired)
-- ... this DOES work:
select empl_last_name, dept.dept_num, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num
and hiredate > '01-dec-1991';
select empl_last_name, dept.dept_num, dept_name, dept_loc
from empl join dept on (empl.dept_num = dept.dept_num)
where hiredate > '01-dec-1991';
--**************************************************
-- MORE POSSIBILITIES FOR THE 'WHERE' CLAUSE
--**************************************************
-- various goodies related to WHERE clause conditions....
-- continued
--------
-- boolean operators: AND, OR, NOT
--------
-- AND and OR and NOT are the boolean operators in SQL,
-- remember...
-- (as mentioned in create table check-clause discussion)
-- see employees who are salesmen with salaries of 1500 or more
select *
from empl
where job_title = 'Salesman'
and salary >= 1500;
-- what about employees who are EITHER salesmen OR have salaries
-- of 1500 or more?
select *
from empl
where job_title = 'Salesman'
or salary >= 1500;
-- please use parentheses for compound logical/Boolean conditions
-- when OR's and AND's are BOTH involved;
-- (here, I only want rows with hiredate > 1-1-93, but for
-- those also who are either salesmen or make more or equal to
-- 1500)
select *
from empl
where (job_title = 'Salesman' or salary >= 1500)
and hiredate > '01-Mar-1991';
-- here's a NOT example:
-- employees with a job_title that is NOT 'Salesman'
select *
from empl
where not (job_title = 'Salesman');
select *
from empl
where not job_title = 'Salesman';
select *
from empl
where job_title <> 'Salesman';
select *
from empl
where job_title != 'Salesman';
--------
-- between operator
--------
-- BETWEEN example:
-- attrib BETWEEN val1 AND val2
-- (is attribute between val1 and val2, inclusive?)
-- (is it in the interval [val1, val2] )
-- same as: (attrib >= val1) AND (attrib <= val2)
-- ex: employees whose salary is between 1100 and 1600, inclusive:
-- (in the interval [1100, 1600])
select *
from empl
where salary between 1100 and 1600;
select *
from empl
where salary NOT between 1100 and 1600;
--------
-- like operator
--------
-- LIKE examples, for looking for string attributes that match a
-- given pattern:
-- %: matches any 0 or more characters
-- _: matches any 1 character
-- which employees have an employee number ending with a 9?
select *
from empl
where empl_num like '%9';
-- which employees have an employee number with a 7 ANYWHERE
-- in it?
select *
from empl
where empl_num like '%7%';
-- which employees are managers --- but I don't remember
-- the case
select *
from empl
where job_title like '_anager'
or job_title = 'MANAGER';
-- which employee numbers have a 9 as the third digit?
select *
from empl
where empl_num like '__9_';
-- which employees have 'a' as the third character in
-- their last name?
select *
from empl
where empl_last_name like '__a%';
-- which have a 9 in them, period?
select *
from empl
where empl_num like '%9%';
-- does this work for TRULY-NUMERIC fields?
select *
from empl
where salary like '_6__';
select *
from orders
where order_total like '__._7';
select *
from orders
where order_total like '__.9_';
select *
from orders
where order_total like '___9_';
select *
from empl
where hiredate like '__-FEB%';
select *
from empl
where hiredate NOT like '__-FEB%';
-- geesh, are the quotes required here?
-- YES, THEY ARE --- THIS FAILS!!!
select *
from empl
where salary like _6__;
--*************************************************
-- COMPUTED COLUMNS (and renaming columns)
--************************************************
-- computed columns: I can have a computation in the select clause!
-- note that +, *, /, - are all supported;
-- silly: show double everyone's salary:
select empl_last_name, salary * 2
from empl;
-- a computed column in a select does NOT CHANGE THE DATA
-- IN THE DATABASE!!!!
-- you CAN rename a projected column or expression --- put a string AFTER
-- the projected expression (NO comma!)
-- NOTE that, here, the string is expected to use double quotes...!?
-- (some call this a column alias... I usually don't though...)
select empl_last_name "Last Name", salary * 2 "double salary"
from empl;
-- you can only omit the double quotes if there is no blank in
-- the proposed column alias --- AND, note that that alias will
-- then appear in all caps!
select empl_last_name lastname, salary * 2 twiceCurrent
from empl;
-- what is the sum of salary + commmission?
-- (note: computations are ONLY done when all columns involved have
-- NON-NULL values...)
select salary + commission "combined gross"
from empl;
-- note that if you don't use quotes in a column alias,
-- it shows as all-caps (and cannot have blanks inside!!)
select empl_last_name, salary + commission gross
from empl;
--****************************
-- TABLE ALIASES
--****************************
-- can give a table a nickname, too...
-- (but it can save typing --- ONCE SET, USE THROUGHOUT THE
-- SELECT!!)
-- (also called a table alias)
select d.dept_num, empl_last_name
from dept d, empl e
where d.dept_num = e.dept_num;
select d.dept_num, empl_last_name
from dept d join empl e on (d.dept_num = e.dept_num);
-- BUT NOTE --- if you DO this, you must USE the alias
-- THROUGHOUT the select statement; EVEN in the select clause!
-- so, SQL*Plus WON'T like this:
select dept.dept_num, empl_last_name
from dept d, empl e
where d.dept_num = e.dept_num;
select dept.dept_num, empl_last_name
from dept d join empl e on (d.dept_num = e.dept_num);
-- but this is fine:
select d.dept_num, empl_last_name
from dept d, empl e
where d.dept_num = e.dept_num;
select d.dept_num, empl_last_name
from dept d join empl e on (d.dept_num = e.dept_num);
--**********************************************
-- ASIDE: here is a three-table join
--**********************************************
-----
-- important rule of thumb --- in a (natural or equi-)join of X
-- tables, you should have X-1 join conditions!!
-----
-- so, to join 3 tables, you need 2 join conditions
-- what if I want, for each customer, the customer's last name,
-- the name of that customer's employee rep, and the department
-- location of that employee
select cust_lname, empl_last_name, dept_loc
from customer, empl, dept
where customer.empl_rep = empl.empl_num
and empl.dept_num = dept.dept_num;
select cust_lname, empl_last_name, dept_loc
from customer join empl on (customer.empl_rep = empl.empl_num)
join dept on (empl.dept_num = dept.dept_num);
-- what if I want the above for customers represented by employee
-- Michaels?
select cust_lname, empl_last_name, dept_loc
from customer, empl, dept
where customer.empl_rep = empl.empl_num
and empl.dept_num = dept.dept_num
and empl_last_name = 'Michaels';
select cust_lname, empl_last_name, dept_loc
from customer join empl on (customer.empl_rep = empl.empl_num)
join dept on (empl.dept_num = dept.dept_num)
where empl_last_name = 'Michaels';
-----
-- more examples of table aliases...
-----
select cust_lname, empl_last_name, dept_loc, d.dept_num
from customer c, empl e, dept d
where c.empl_rep = e.empl_num
and e.dept_num = d.dept_num;
select cust_lname, empl_last_name, dept_loc, d.dept_num
from empl e join customer c on (c.empl_rep = e.empl_num)
join dept d on (e.dept_num = d.dept_num);
---------------------------------------------
-- example of when table aliases are REQUIRED!!
-----------------------------------------------
-- say you want to print each employee's name, and the name
-- each employee's manager;
select e1.empl_last_name employee, e2.empl_last_name manager
from empl e1, empl e2
where e1.mgr = e2.empl_num;
select e1.empl_last_name employee, e2.empl_last_name manager
from empl e1 join empl e2 on (e1.mgr = e2.empl_num);
--****************************
-- IN predicate examples
--****************************
-- (now we are using in a select's where clause)
--------
-- first: here's one way you could find out the names and
-- job_titles and salaries
-- of managers and analysts (NOT using IN, yet):
select empl_last_name, job_title, salary
from empl
where job_title = 'Analyst'
or job_title = 'Manager';
-- you could ALSO do the same thing using the IN predicate:
select empl_last_name, job_title, salary
from empl
where job_title IN ('Analyst', 'Manager');
--*********
-- lab exercise:
-- write a query using IN that selects those rows of dept
-- whose location (dept_loc) is either 'Dallas', 'Boston',
-- or 'New York'
--*********
-- there's also NOT IN, which does what you'd probably expect:
-- this gives last name and job_title for anyone who isn't
-- an analyst or a manager:
select empl_last_name, job_title
from empl
where job_title NOT IN ('Analyst', 'Manager');
--*************************************
-- examples of aggregate functions
--*************************************
-- computed columns perform a computation for EACH row ---
-- aggregate functions perform ONE computation for ALL the rows;
-- only ONE row results, when aggregate functions are called
-- in "simple" selects;
-- what is the average salary, the minimum salary, the maximum
-- salary, the total of all salaries, and the number of salaries
-- for ALL employees, in aggregate?
select avg(salary), avg(commission), min(salary), max(salary),
sum(salary), count(salary), count(mgr), min(hiredate),
count(commission)
from empl;
-- same query, except with "nicer" column labels on the result
select avg(salary) "Average Salary", avg(commission) "Average Commission",
min(salary) minimum, max(salary) maximum,
sum(salary) sum, count(salary) "number of employees"
from empl;
-- let's do these computations for commission as well, to make
-- the point that these functions operate on NON-NULL values
select avg(commission) "Avg Comm", min(commission) "Min Comm",
max(commission) "Max Comm",
sum(commission) "Comm Sum", count(commission) "How many have comm"
from empl;
-- playing with the count() aggregate function:
select count(salary), count(commission), count(mgr)
from empl;
-- count(*) can be used to indicate the number of rows in the
-- resulting table
-- counting rows vs. non-null values...
select count(*), count(commission), count(salary), count(mgr)
from empl;
-- some more count() examples
select count(*) "Number of Employees"
from empl;
select count(*) "Number of Managers"
from empl
where job_title = 'Manager';
-- find out how many clerks there are, and their average
-- salary:
select count(*), avg(salary)
from empl
where job_title = 'Clerk';
--*************************************
-- examples of using & for interactive input
-- (and short intro to SQL INJECTION)
--*************************************
-- if you put a & in front of a name within a query,
-- then when run, SQLPlus will query you to enter an interactive
-- value for that name, substitute it into the query, and run it;
select salary
from empl
where job_title = &job_ttl;
-- when run, you'll see:
-- Enter value for job_ttl:
--
-- (and be sure to type string data in single quotes...!)
--**********
-- SIDE NOTE: in sqlplus, typing
/
-- ...redoes the PREVIOUS SQL statement... (handy for playing with &, here!)
--**********
-- & is useful for more customizable report SQL scripts or regularly-run SQL
-- scripts, for example;
-- BUT -- when you trust user input (without checking it first or
-- SANITIZING it), you become prone to SQL INJECTION
-- from:
-- http://st-curriculum.oracle.com/tutorial/SQLInjection/index.htm, sect 1.1:
/* "SQL injection is a technique to maliciously exploit applications that
use client-supplied data in SQL statements.
Attackers trick the SQL engine into executing unintended commands by
supplying specially crafted string input, thereby gaining unauthorized
access to a database in order to view or manipulate restricted data.
SQL injection techniques may differ, but they all exploit a single
vulnerability in the application:
Incorrectly validated or nonvalidated string literals are concatenated
into a dynamic SQL statement, and interpreted as code by the SQL engine."
*/
/* example 1:
with the above query, when run, enter:
Enter value for job_ttl: 'Manager' or 1 = 1
...you'll get salaries for ALL employees;
example 2:
We haven't discussed UNION yet, but, for the query:
select empl_last_name
from empl
where job_title = &job_ttl;
if, when run, you enter:
Enter value for job_ttl: 'Manager' union select table_name from user_tables
...you'll get the names of ALL tables in this user's corner of the Oracle
student database;
*/
-- SQL injection is a real problem --
-- it was cited as one of the top 3 security weaknesses in 2010 at
-- https://buildsecurityin.us-cert.gov
--**********
-- SOME ways to combat this? For example... (NOT a comprehensive list,
-- I'm sure!)
-- * in CIS 318 we'll talk about being careful to always SANITIZE
-- user input before trustingly plugging it into DYNAMICALLY-built
-- SQL queries
-- * we might take care to encrypt certain data, even in a database
-- table;
-- * couldn't judicious use of GRANT help with this, too? After all,
-- if sensitive tables only have SELECT access granted to a trusted
-- few, then if this malicious user isn't in that trusted few, then
-- this attempt should fail...?
-- * I think it is advised you take as MANY of preventive measures
-- as you can...! (layers of protection);
-- and now you may have a greater appreciation of this:
-- http://xkcd.com/327/
spool off
-- end of 315lab05-projected.sql