Please send questions to
[email protected] .-- example SQL and SQL*Plus commands from 9-7-00 CIS 315 lab. -- to run this script, you can type one of the following at the SQL> -- prompt: -- start lab02 -- start lab02.sql -- @ lab02 -- @ lab02.sql -- if I type the SQL*Plus command: -- set echo on -- before running this script, then SQL*Plus will show each -- script command before it is executed -- write everything that goes to the SQL*Plus screen from this point -- until I type 'spool off' also to the file named lab02_results.txt spool lab02_results.txt -- example of creating a table -- in a SQL script, it is traditional to make sure old -- tables with the same name are deleted before creating a table drop table parts cascade constraints; create table parts (part_num integer, part_name varchar2(25), quantity_on_hand smallint, price decimal(6,2), level_code char(3), -- level code must be 3 digits last_inspected date, primary key (part_num) ); -- a SQL*Plus command that shows you what columns make up a table describe parts -- let's put some rows into this table -- (note how the different types are represented --- strings, -- numbers, dates...) insert into parts values (10601, '3/8 in lug nut', 1000, 0.02, '002', '03-SEP-2000'); -- this WILL NOT WORK ---part name is longer than column part_name -- allows insert into parts values (10602, '5/8 in lug nut from Argentina or Brazil', 16, 4.50, '105', '04-SEP-2000'); insert into parts values (10603, 'hexagonal wrench', 13, 9.99, '003', '05-SEP-2000'); insert into parts values (10604, 'tire', 287, 39.99, '333', '06-SEP-2000'); -- show the contents of the parts table select * from parts; -- make SURE you spool off when you want to STOP writing the results -- to a file!!!! spool off
Please send questions to
[email protected] .-- script for lab session 3, 2-1-00
-- lab03.sql
-- to start, make sure commands in the script are NOT echoed to the
-- screen before execution
set echo off
-- let's spool the results into a file
spool lab03_results.txt
-- here is parts table again
drop table parts cascade constraints;
create table parts
(part_num integer,
part_name varchar2(25),
quantity_on_hand smallint,
price decimal(6,2),
level_code char(3), -- level code must be 3 digits
last_inspected date,
primary key (part_num)
);
-- let's put some rows into this table
insert into parts
values
(10601, '3/8 in lug nut', 1000, 0.02, '002', '03-SEP-2000');
insert into parts
values
(10603, 'hexagonal wrench', 13, 9.99, '003', '05-SEP-2000');
insert into parts
values
(10604, 'tire', 287, 39.99, '333', '06-SEP-2000');
-- show the contents of the parts table
select *
from parts;
-- 'set echo on' causes the script command to be printed to
-- the screen before it is executed:
set echo on
-- command WILL appear before this output
select *
from parts;
-- create orders table, which has a foreign key referencing
-- the parts table
-- NOTE: part_num had to be defined/declared within orders BEFORE
-- it could be made a foreign key; be sure that its type matches
-- the column it is referring to, also.
drop table orders cascade constraints;
create table orders
(order_num char(6),
cust_num char(8),
part_num integer,
order_date date,
quantity integer,
order_code char(1),
primary key (order_num),
foreign key (part_num) references parts
);
-- one COULD put:
-- ...
-- foreign key (part_num) references parts(part_num)
-- these inserts into orders will work --- they are orders
-- for existing parts:
insert into orders
values
('111111', '11111111', '10601', '01-Feb-2000', 6, 'B');
-- this variant of insert can be used to make a column
-- in a row be empty --- to have a NULL value
-- (list the columns being filled AFTER the table name, in the
-- order you plan to list the values in the insert statement)
-- (you also need it to show that a default clause is working...)
insert into orders(order_num, part_num, cust_num, order_date, quantity)
values
('222222', '10604', '22222222', '1-Jan-00', 4);
-- now, what is in orders?
select *
from orders;
-- these insert will FAIL
-- let's NOT list the columns but try to leave out the order_code
insert into orders
values
('333333', '33333333', '10601', '01-Feb-2000', 8);
-- hey! THIS WORKS!!! you CAN insert a null value this way?!?!
insert into orders
values
('333333', '33333333', '10601', '01-Feb-2000', 8, NULL);
-- THIS FAILS, TOO!!!
-- no, you cannot just leave it blank, either
insert into orders
values
('333334', '33333333', '10601', , 8, 'G');
-- now, this WILL fail --- an order for a nonexistent part
insert into orders
values
('444444', '11111111', '66666', '12-Dec-99', 10, 'A');
-- what's in the table now?
select *
from orders;
-- examples of ADDITIONAL checks you can make, IN the create
-- table statement
-- maxpoints integer not null,
-- quantity integer default 1, -- put 1 in if NO value
-- -- inserted for this column
-- car_color varchar2(10) check(car_color IN ('red', 'green', 'white')),
-- quiz_grade integer check(quiz_grade >= 0 AND quiz_grade <= 100),
-- quiz_grade integer check(quiz_grade between 0 and 100),
-- now, let's use some of these in orders:
drop table orders cascade constraints;
create table orders
(order_num char(6),
cust_num char(8) not null,
part_num integer,
order_date date,
quantity integer default 1,
order_code char(1) check(order_code in ('B', 'I', 'G')),
primary key (order_num),
foreign key (part_num) references parts
);
insert into orders
values
('111111', '11111111', '10601', '01-Feb-2000', 6, 'B');
insert into orders
values
('333333', '33333333', '10601', '01-Feb-2000', 8, NULL);
insert into orders(order_num, part_num, cust_num, order_date, quantity)
values
('222222', '10604', '22222222', '1-Jan-00', 4);
-- show that default clause works for quantity, if NO value put in
insert into orders(order_num, part_num, cust_num, order_date)
values
('444444', '10601', '22222222', '1-Feb-00');
-- note, EXPLICIT insertion of null OVERRIDES default of quantity!
insert into orders
values
('555555', '44444444', '10601', '3-Mar-98', NULL, 'G');
-- BAD inserts, no biscuit
-- order_code MUST be 'B', 'I' or 'G':
insert into orders
values
('666666', '44444444', '10601', '25-Dec-99', 5, 'b');
-- cust_num CANNOT be null
insert into orders(order_num, part_num)
values
('777777', '11111111');
select *
from orders;
-- turn OFF spooling
spool off
-- you should not regularly HAVE to alter tables after the fact ---
-- if they are designed well --- BUT, well, sometimes...
-- here are some examples, JUST in case
-- what if I want to add a NEW column to the parts table --- say,
-- a supplier column?
alter table parts
add
(supplier varchar2(20)
);
-- see the new column in parts' structure?
describe parts
-- see how its value is null for all currently-existing rows?
-- (yes it is ugly! too wide a line... it is wrapping)
select *
from parts;
-- here's adding a foreign key after the fact (DON't make a
-- habit of this --- see class coding standards)
drop table customer cascade constraints;
create table customer
(cust_num char(8),
cust_lname varchar2(20) not null,
cust_fname varchar2(20),
cust_phone varchar2(12),
primary key (cust_num)
);
insert into customer
values
('11111111', 'One', 'First', '111-1111');
insert into customer
values
('22222222', 'Two', 'Second', '222-2222');
insert into customer
values
('33333333', 'Three', 'Third', '333-3333');
insert into customer
values
('44444444', 'Four', 'Fourth', '444-4444');
-- now, example of adding foreign key after the fact:
alter table orders
add
(foreign key (cust_num) references customer);
-- so, this will FAIL, because it is an order for a non-existent
-- customer:
insert into orders
values
('666666', '12121212', '10601', '01-Feb-00', 4, 'I');
-- I can make fields "bigger", but generally cannot make them "smaller"
alter table customer
modify
(cust_lname varchar2(30));
-- notice the new length of cust_lname:
describe customer
-- this WON'T work
alter table customer
modify
(cust_fname varchar2(10));
Please send questions to
[email protected] .-- this file sets up example tables for query practice.
-- last modified: 9-15-99
-- create and populate table dept
drop table dept cascade constraints;
create table dept
(dept_num char(3) primary key,
dept_name varchar2(15) not null,
dept_loc varchar2(15) not null);
insert into dept
values
('100', 'Accounting', 'New York');
insert into dept
values
('200', 'Research', 'Dallas');
insert into dept
values
('300', 'Sales', 'Chicago');
insert into dept
values
('400', 'Operations', 'Boston');
insert into dept
values
('500', 'Management', 'New York');
-- create and populate table empl
drop table empl cascade constraints;
create table empl
(empl_num char(4)
primary key, empl_last_name varchar2(15) not null,
job_title varchar2(10),
mgr char(4),
hiredate date not null,
salary number(6,2),
commission number(6,2),
dept_num char(3),
foreign key (dept_num) references dept);
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7369', 'Smith', 'Clerk', '7902', '17-Dec-1990', 800.00, '200');
insert into empl
values
('7499', 'Michaels', 'Salesman', '7698', '20-Feb-1991', 1600.00, 300.00,
'300');
insert into empl
values
('7521', 'Ward', 'Salesman', '7698', '22-Feb-1991', 1250.00, 500.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7566', 'Jones', 'Manager', '7839', '02-Apr-1991', 2975.00, '200');
insert into empl
values
('7654', 'Martin', 'Salesman', '7698', '28-Sep-1991', 1250.00, 1400.00,
'300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7698', 'Blake', 'Manager', '7839', '01-May-1991', 2850.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7782', 'Raimi', 'Manager', '7839', '09-Jun-1991', 2450.00, '100');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7788', 'Scott', 'Analyst', '7566', '09-Nov-1991', 3000.00, '200');
insert into empl(empl_num, empl_last_name, job_title, hiredate,
salary, dept_num)
values
('7839', 'King', 'President', '17-Nov-91', 5000.00, '500');
insert into empl
values
('7844', 'Turner', 'Salesman', '7698', '08-Sep-1991', 1500.00, 0.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7876', 'Adams', 'Clerk', '7788', '23-Sep-1991', 1100.00, '400');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7900', 'James', 'Clerk', '7698', '03-Dec-1991', 950.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7902', 'Ford', 'Analyst', '7566', '03-Dec-1991', 3000.00, '200');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7934', 'Miller', 'Clerk', '7782', '23-Jan-1992', 1300.00, '100');
-- create and populate table customer
drop table customer cascade constraints;
create table customer
(cust_id char(6) primary key,
cust_lname varchar2(20) not null,
cust_fname varchar2(15),
empl_rep char(4),
cust_street varchar2(30),
cust_city varchar2(15),
cust_state char(2),
cust_zip varchar2(10),
cust_balance number(7, 2) default 0.0,
foreign key (empl_rep)
references empl(empl_num));
insert into customer
values
('123456', 'Firstly', 'First', '7499', '1111 First Street', 'Fortuna', 'CA',
'95520', 1111.11);
insert into customer
values
('234567', 'Secondly', 'Second', '7654', '2222 Second Street',
'McKinleyville', 'CA', '95523', 222.20);
insert into customer
values
('345678', 'Thirdly', 'Third', '7499', '333 Third Street', 'Arcata',
'CA', '95519-1234', 33.33);
Please send questions to
[email protected] .-- lab07.sql
-- intro to nested selects,
-- plus some fancier joins and etc.
--
-- last modified: 10-12-00
-- whoops! HW #4 can conflict with these, I need to make sure that I
-- have a "clean" set of dept/empl/customer tables;
@ set_up_ex_tbls
-- 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;
-- 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';
-- example of table aliases
select cust_lname, empl_last_name, dept_loc
from customer c, empl e, dept d
where c.empl_rep = e.empl_num
and e.dept_num = d.dept_num;
-- and, here I'm putting the dept_num, too;
--
-- NOTE --- dept_num is in more than one of the tables,
-- so I have to tell SQL which dept_num I want; note
-- that I use the alias in select clause, then,
-- even though "defined" in second line!!
--
-- (in fact, will not accept dept.dept_num on the first line;
-- try it and see!)
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;
-- 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, e2.empl_last_name manager
from empl e1, empl e2
where e1.mgr = e2.empl_num;
-- IN predicate examples
-- first: here's one way you could find out the names and
-- job_titles of managers and analysts (NOT using IN, yet):
select empl_last_name, job_title
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
from empl
where job_title IN ('Analyst', 'Manager');
-- 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 built-in functions
-- what is the average salary, the minimum salary, the maximum
-- salary, the total of all salaries, and the number of salaries?
select avg(salary), min(salary), max(salary), sum(salary), count(salary)
from empl;
-- same query, except with "nicer" column labels on the result
select avg(salary) average, 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;
-- count(*) can be used to indicate the number of rows in the
-- resulting table
select count(*) "Number of Employees"
from empl;
select count(*) "Number of Managers"
from empl
where job_title = 'Manager';
-- note that NULL values are NOT involved in computations
-- of built-in functions, either:
select count(*), count(commission)
from empl;
-- (notice that the average of commission is based on number
-- of non-null commissions)
select count(*), sum(commission), count(commission), avg(commission)
from empl;
-- find out how many clerks there are, and their average
-- salary:
select count(*), avg(salary)
from empl
where job_title = 'Clerk';
-- nested select/subselect examples...
-- who is/are the manager(s) of the highest-paid clerk (or clerks)?
-- first: working backwards...
-- what is the salary of the highest paid clerk?
select max(salary)
from empl
where job_title = 'Clerk';
-- next: what's is (or are) the employee numbers of that/those
-- clerks?
select empl_num
from empl
where job_title = 'Clerk'
and salary =
(select max(salary)
from empl
where job_title = 'Clerk');
-- next: what is/are the employee numbers of the manager(s) of
-- such clerk(s)?
select mgr
from empl
where empl_num in
(select empl_num
from empl
where job_title = 'Clerk'
and salary =
(select max(salary)
from empl
where job_title = 'Clerk'));
-- FINALLY, let's get the name(s) of these managers!!!
select empl_last_name
from empl
where empl_num in
(select mgr
from empl
where empl_num in
(select empl_num
from empl
where job_title = 'Clerk'
and salary =
(select max(salary)
from empl
where job_title = 'Clerk')));
-- who are the managers of clerks making more than the average
-- salary for clerks?
select empl_last_name
from empl
where empl_num in
(select mgr
from empl
where empl_num in
(select empl_num
from empl
where job_title = 'Clerk'
and salary >
(select avg(salary)
from empl
where job_title = 'Clerk')));
-- here's one where I might more commonly expect multiple values...
-- what are the names of managers of clerks?
-- what are the employee numbers of clerks?
select empl_num
from empl
where job_title = 'Clerk';
-- what are the employee numbers of the MANAGERS of these clerks?
select mgr
from empl
where empl_num in
(select empl_num
from empl
where job_title = 'Clerk');
-- and, what are the empl_last_names of these managers?
select empl_last_name
from empl
where empl_num in
(select mgr
from empl
where empl_num in
(select empl_num
from empl
where job_title = 'Clerk'));
-- what are the names of managers of salesmen? (note there will
-- not be duplicates in this particular result...)
select empl_last_name
from empl
where empl_num in
(select mgr
from empl
where empl_num in
(select empl_num
from empl
where job_title = 'Salesman'));
-- there can be more than one table involved here...!
-- what are the names and salaries of employees who work in Dallas?
-- part 1: what are the department numbers located in Dallas?
select dept_num
from dept
where dept_loc = 'Dallas';
-- part 2: which employees work in that (those) department(s)?
select empl_last_name, salary
from empl
where dept_num in
(select dept_num
from dept
where dept_loc = 'Dallas');
-- note: especially when the result columns are all from one table,
-- but the "computation" involves more than one table, there is
-- OFTEN more than one valid way to write a query ---
-- here, then, is a JOIN that gives the names and salaries of
-- employees working in Dallas:
select empl_last_name, salary
from empl, dept
where empl.dept_num = dept.dept_num
and dept_loc = 'Dallas';
-- other predicates than IN can be used in nested selects, too;
-- give all the employee info for clerks who make MORE than the
-- lowest-paid salesmen
-- what's the salary of the lowest-paid salesman?
select min(salary)
from empl
where job_title = 'Salesman';
-- so, what's the info for Clerks who make more than this?
select *
from empl
where job_title = 'Clerk'
and salary >
(select min(salary)
from empl
where job_title = 'Salesman');
-- ALL example: find every salesman whose salary is higher
-- than the highest-paid clerk
select *
from empl
where job_title = 'Salesman'
and salary >ALL
(select salary
from empl
where job_title = 'Clerk');
-- ANY example: find every clerk whose salary is higher than any
-- non-clerk employee
select *
from empl
where job_title = 'Clerk'
and salary >ANY
(select salary
from empl
where job_title != 'Clerk');
-- what you can do with a nested select, you can often do
-- with a join, as well --- here's an example:
-- names of employees whose location is Chicago
select empl_last_name
from empl
where dept_num in
(select dept_num
from dept
where dept_loc = 'Chicago');
select empl_last_name
from empl, dept
where empl.dept_num = dept.dept_num
and dept_loc = 'Chicago';
Please send questions to
[email protected] .-- lab08.sql
-- 315 Lab session 10-19-00
-- set up tables for lab 8
@ create_mail
@ insert_mail
-- take a look at structure/contents
-- of new tables
select *
from employees;
select *
from parts;
select *
from customers;
select *
from orders;
select *
from odetails;
select *
from zipcodes;
-- Demonstration of putting a literal value
-- into a select clause
-- shows that you project that value for
-- each row in the result set
-- This sets the "pagesize" of the sqlplus
-- output buffer:
set pagesize 20
select 'b'
from empl;
-- add a department with no employees
insert into dept
values
('600', 'Computer', 'Arcata');
-- use EXISTS to list only the locations and
-- names of departments WITH employees
--
-- note that the inner query, with an exists,
-- virtually ALWAYS has a correlation condition!!!!
--
-- note the inner query is used to see if there
-- are any rows matching the criterion - so it
-- doesn't matter what you use in the inner
-- select and selecting a literal is considered
-- more efficient than, say, select *
select dept_loc, dept_name
from dept
where exists
(select 'a'
from empl
where empl.dept_num = dept.dept_num);
-- use NOT EXISTS to list which departments
-- currently have NO employees:
select dept_loc, dept_name
from dept
where NOT exists
(select 'a'
from empl
where empl.dept_num = dept.dept_num);
-- note the correlation condition is absolutely
-- necessary;
-- the correlation condition is correlating the
-- inner query with the outer query
--
-- The inner query is executed for each row of
-- the outer query
-- What happens if you make the inner select
-- a join instead of a simple correlated query?
select dept_loc, dept_name
from dept
where NOT exists
(select 'a'
from empl, DEPT
where empl.dept_num = dept.dept_num);
-- Note that we just got the WRONG result - it
-- says that NO department has no employees
-- (that is, it says all departments have employees
-- which just isn't true)
--
-- What is happening?
-- The inner query is not correlated with the outer
-- query. Each time it fires, it produces exactly
-- the same set of rows. Since it always returns
-- some rows, the NOT EXISTS is never true. So, we
-- get no rows returned from the outer query, which
-- we interpret as "all departments have employees"
-- Here is another example, Query 2.18 from the text:
-- "Get the cname values of customers who have placed
-- at least one order through employee with eno = 1000"
-- could think of this as, "for each customer, is there
-- a row of orders with this customer number and employee
-- number of 1000?"
select cname
from customers
where exists
(select 'a'
from orders
where orders.cno = customers.cno -- correlation condition
and eno = 1000);
-- Notice that for EACH row of the customers relation,
-- the subquery is evaluated, according to THAT ROW's
-- value of customers.cno
-- Beware, don't turn the subquery correlation condition
-- into a join!! Watch what happens here:
select cname
from customers
where exists
(select 'a'
from orders, CUSTOMERS
where orders.cno = customers.cno -- now a JOIN condition
and eno = 1000);
-- Notice that you get ALL the customers, because the inner query
-- returns the same set of data for each row of the outer query,
-- so each row of the outer query is reported, since the exists
-- condition is true
-- Query 2.19 from the text: what if you want to know what
-- customers HAVE NOT placed an order with that particular
-- employee?
-- You want the rows where the inner query returns no rows,
-- that is, where no rows exist in the subquery
-- so we can replace the EXISTS in the previous query with
-- NOT EXISTS!
select cname
from customers
where NOT exists
(select 'a'
from orders
where orders.cno = customers.cno -- correlation condition
and eno = 1000);
-- for this example we return to the old empl/dept/customer tables.
-- Are there any customers with an employee representative
-- who has made more than $1000 in commissions?
select cust_lname, cust_fname
from customer c
where exists
(select 'a'
from empl e
where c.empl_rep = e.empl_num -- correlation condition
and commission > 1000);
-- Are there any orders involving "part" 'Dr. Zhivago'?
select *
from orders or1
where exists
(select 'a'
from odetails od1, parts p1
where od1.ono = or1.ono -- ono = order number; correlation cond.
and od1.pno = p1.pno -- pno = part number; join cond.
and pname = 'Dr. Zhivago');
-- This is Query 2.20 from the text with a small
-- revision to make it more usable:
-- Have ANY customers ordered BOTH 'Sleeping Beauty'
-- AND 'When Harry Met Sally'?
select cname
from customers
where exists
(select 'a'
from orders, parts, odetails
where orders.ono = odetails.ono
and odetails.pno = parts.pno
and orders.cno = customers.cno -- correlation condition
and pname = 'Sleeping Beauty')
and exists
(select 'a'
from orders, parts, odetails
where orders.ono = odetails.ono
and orders.cno = customers.cno -- correlation condition
and odetails.pno = parts.pno
and pname = 'When Harry Met Sally');
-- Here's what's happening:
-- I want to know if the following BOTH exist, for each row
-- of the customers table:
-- 1) an order for 'Sleeping Beauty' and
-- 2) an order for 'When Harry Met Sally'
--
-- But in order to write the exists condition, I
-- need data from three tables (2 join conditions)
-- AND ALSO a correlation condition
-- AND ALSO a restriction condition
-- for the total of 4 WHERE conditions in the inner query
-- Finally, sometimes a query can be written with
-- IN or with EXISTS or with a JOIN.
-- The three queries below all correctly answer this
-- question:
-- List locations with at least one employee hired
-- before May 1, 1991
-- This one uses EXISTS and a correlated subquery:
select dept_loc
from dept d
where exists
(select 'a'
from empl e
where e.dept_num = d.dept_num -- correlation condition
and hiredate < '01-May-1991');
-- This one uses an IN and a subquery:
select dept_loc
from dept d
where dept_num in
(select dept_num
from empl e
where hiredate < '01-May-1991');
-- And this one uses a join
select distinct dept_loc
from dept d, empl e
where d.dept_num = e.dept_num -- join condition
and hiredate < '01-May-1991';
-- And that's all, folks
Please send questions to
[email protected] .--------------------------------------------------------------- -- Mail Order Database: Insert Rows -- Chapter 2; Oracle Programming -- A Primer -- by R. Sunderraman --------------------------------------------------------------- insert into zipcodes values (67226,'Wichita'); insert into zipcodes values (60606,'Fort Dodge'); insert into zipcodes values (50302,'Kansas City'); insert into zipcodes values (54444,'Columbia'); insert into zipcodes values (66002,'Liberal'); insert into zipcodes values (61111,'Fort Hays'); insert into employees values (1000,'Jones',67226,'12-DEC-95'); insert into employees values (1001,'Smith',60606,'01-JAN-92'); insert into employees values (1002,'Brown',50302,'01-SEP-94'); insert into parts values (10506,'Land Before Time I',200,19.99,20); insert into parts values (10507,'Land Before Time II',156,19.99,20); insert into parts values (10508,'Land Before Time III',190,19.99,20); insert into parts values (10509,'Land Before Time IV',60,19.99,20); insert into parts values (10601,'Sleeping Beauty',300,24.99,20); insert into parts values (10701,'When Harry Met Sally',120,19.99,30); insert into parts values (10800,'Dirty Harry',140,14.99,30); insert into parts values (10900,'Dr. Zhivago',100,24.99,30); insert into customers values (1111,'Charles','123 Main St.',67226,'316-636-5555'); insert into customers values (2222,'Bertram','237 Ash Avenue',67226,'316-689-5555'); insert into customers values (3333,'Barbara','111 Inwood St.',60606,'316-111-1234'); insert into orders values (1020,1111,1000,'10-DEC-94','12-DEC-94'); insert into orders values (1021,1111,1000,'12-JAN-95','15-JAN-95'); insert into orders values (1022,2222,1001,'13-FEB-95','20-FEB-95'); insert into orders values (1023,3333,1000,'20-JUN-97',null); insert into odetails values (1020,10506,1); insert into odetails values (1020,10507,1); insert into odetails values (1020,10508,2); insert into odetails values (1020,10509,3); insert into odetails values (1021,10601,4); insert into odetails values (1022,10601,1); insert into odetails values (1022,10701,1); insert into odetails values (1023,10800,1); insert into odetails values (1023,10900,1);
Please send questions to
[email protected] .--------------------------------------------------------------- -- Mail Order Database; Create Tables Script -- Chapter 2; Oracle Programming -- A Primer -- by R. Sunderraman --------------------------------------------------------------- drop table zipcodes cascade constraints; create table zipcodes ( zip number(5), city varchar2(30), primary key (zip)); drop table employees cascade constraints; create table employees ( eno number(4) not null primary key, ename varchar2(30), zip number(5) references zipcodes, hdate date); drop table parts cascade constraints; create table parts( pno number(5) not null primary key, pname varchar2(30), qoh integer check(qoh >= 0), price number(6,2) check(price >= 0.0), olevel integer); drop table customers cascade constraints; create table customers ( cno number(5) not null primary key, cname varchar2(30), street varchar2(30), zip number(5) references zipcodes, phone char(12)); drop table orders cascade constraints; create table orders ( ono number(5) not null primary key, cno number(5) references customers, eno number(4) references employees, received date, shipped date); drop table odetails cascade constraints; create table odetails ( ono number(5) not null references orders, pno number(5) not null references parts, qty integer check(qty > 0), primary key (ono,pno));
Please send questions to
[email protected] .Full Select Statement explanation
* adapted from Sunderraman, "Oracle Programming: A Primer", section 2.4.7,
pp. 53-54
* <sub-select> general form:
select [distinct] <expression> {, <expression>}
from <tablename> [<alias>]{, <tablename> [<alias>]}
[where <search_condition>]
[group by <column> {, <column>}]
[having <condition>]
* <select> general form:
<sub-select>
{union [all] | intersect | minus <sub-select>}
[order by result_column [asc|desc] {, result_column [asc|desc]}
* note that order by clause must come at the end of a select, and since
it really only affects "output", it cannot be in a sub-select;
* important: CONCEPTUAL order of evaluation (may not ACTUALLY be what
SQL uses, because of efficiency/optimization, but you can think of it
this way) (Sunderraman pp. 53-54)
(1) The product of all tables in the from clause [of the
"outer"/initial sub-select] is formed.
(2) The where clause [of the "outer"/initial sub-select] is
evaluated to eliminate rows that do not
satisfy the search_condition.
(3) The rows are grouped using the columns in the group by
clause [of the "outer"/initial sub-select].
(4) Groups that do not satisfy the condition in the having
clause [of the "outer"/initial sub-select]
are eliminated.
(5) The expressions in the select clause target list [of the
"outer"/initial sub-select] are evaluated.
(6) If the distinct keyword is present in the select clause [of
the "outer"/initial sub-select], duplicate
rows are now eliminated.
(7) The union/intersect/minus is taken after each sub-select is
evaluated.
(8) The resulting rows are sorted according to the columns
specified in the order by clause.
* <sub-select> general form:
(6) (5)
select [distinct] <expression> {, <expression>}
(1) from <tablename> [<alias>]{, <tablename> [<alias>]} (2) [where <search_condition>]
(3) [group by <column> {, <column>}]
(4) [having <condition>]
* <select> general form:
<sub-select>
(7) {union [all] | intersect | minus <sub-select>}
(8) [order by result_column [asc|desc] {, result_column [asc|desc]}
* (and, of course, for each sub-select "connected" by union or
intersect or minus, and for each sub-select buried within a
search-condition, you'd do steps (1)-(6) above.)
Please send questions to
[email protected] .-- lab09.sql
--
-- last modified: 10-25-00
-- some UNION examples:
-- the union of department numbers of departments in Chicago, and
-- department numbers of employees who are managers
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select dept_num
from empl
where job_title = 'Manager');
-- the two relations being "union'ed" must be union-compatible ---
-- these ARE NOT: (this will fail)
(select dept_num, dept_name
from dept
where dept_loc = 'Chicago')
union
(select dept_num
from empl
where job_title = 'Manager');
-- these are not union-compatible, either: (this will fail, too)
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select salary
from empl
where job_title = 'Manager');
-- sadly, the SQL interpreter cannot tell if two compatible types
-- are not compatible in terms of meaning... (this runs, but results
-- are nonsensical)
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select empl_num
from empl
where job_title = 'Manager');
-- so, remember ---make those union sub-selects union-compatible...
-- "intersect" works similarly --- AND NOTE THAT UNION-COMPATIBILITY
-- IS STILL REQUIRED
-- the INTERSECTION of employees hired after 7-1-91 intersected with
-- employees located in Dallas (just project out the employee last name,
-- dept_num, and hiredate)
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
intersect
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas');
-- and, here's the UNION of these two sets...
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
union
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas');
-- NOTE -- "union" operator DOES remove duplicates (there will NOT be
-- duplicate rows in the union operator's result in SQL);
-- IF you want duplicates, use "union all" in place of "union" above.
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
union all
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas');
-- "minus" is the Oracle keyword used for the DIFFERENCE set-theoretic
-- operation (A-B are those rows in A not also in B)...
-- (SQL minus is used in an analogous way to intersect, union, and
-- UNION COMPATIBILITY IS STILL REQUIRED.)
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
minus
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas');
-- AGGREGATE FUNCTIONS --- already covered! (in a previous lab)
-- but, interesting tidbit --- can use distinct INSIDE the parentheses
-- of a call of one of these...
-- for example: to find out how many DIFFERENT job_titles are used in
-- empl table:
select count(distinct job_title)
from empl;
-- making the result a little prettier...
select count(distinct job_title) "How Many Job-titles"
from empl;
-- this, on the other hand, counts the number of employees with
-- non-null job_titles --- a bigger number!
select count(job_title)
from empl;
-- notice that number of non-null commissions is NOT 14...
select count(commission)
from empl;
-- ORDER BY --- to specify what order you want rows in a result
-- to be displayed in (DOESN'T change what's stored in database!!)
-- let's see all employee info in increasing order of salary:
select *
from empl
order by salary;
-- and, for those with the same salary, in decreasing order of hiredate
select *
from empl
order by salary, hiredate desc;
-- note that order by does NOT affect what columns are projected, or
-- the "order" that those columns appear;
-- for example, I don't even have to project the column I'm ordering
-- by:
select empl_last_name
from empl
order by salary;
-- oh, let's see them in order of hiredate now...
select *
from empl
order by hiredate;
-- in reverse hiredate order:
select *
from empl
order by hiredate desc;
-- and in order of dept_num? no problem:
select *
from empl
order by dept_num;
-- if you give multiple attributes in the order by clause, you are
-- saying what to sort by in case of ties in the previous attributes
-- given in the order by
-- say that I want to sort the employees by job_title,
-- and if they have the same job_title, sort by mgr number,
-- and if they have the same job_title and mgr number, sort
-- by hiredate;
select *
from empl
order by job_title, mgr, hiredate;
-- by default, order by gives you ascending order --- to get DESCENDING
-- order, use keyword DESC
-- see those with highest salaries first:
select *
from empl
order by salary desc;
-- put the desc after EACH attribute that you want in descending
-- order --- for example, to get employees in descending alpha
-- order by job_title, but by increasing order of mgr within that
-- job_title, and by descending hiredate with the same manager
-- and job_title...
select *
from empl
order by job_title desc, mgr, hiredate desc;
-- DO NOT use order-by in a sub-select! A) it is not allowed, and
-- B) it doesn't make sense anyway, if you REALLY think about it
-- GROUP BY
-- group by: a way to group rows sharing common characteristics...
-- here's how you get the average salary of ALL employees?
select avg(salary)
from empl;
-- here's how you get the average salary of each job_title...
select avg(salary)
from empl
group by job_title;
-- (and this prints WHICH job_title has each average)
select job_title, avg(salary)
from empl
group by job_title;
-- what if I want the minimum and maximum salaries and
-- other computations for each dept_num?
select dept_num, min(salary), max(salary), min(hiredate),
max(hiredate), sum(salary)
from empl
group by dept_num;
-- you can only project columns/info that are IDENTICAL for all in each
-- group --- for example, THIS FAILS:
-- (remember, group by essentially gives you "one row" per group;)
select dept_num, empl_last_name, min(salary), max(salary),
min(hiredate), max(hiredate)
from empl
group by dept_num;
-- this fails because, even though I know dept_name is the same
-- for all departments, Oracle doesn't know that...
select d.dept_num, dept_name, min(salary), max(salary),
min(hiredate), max(hiredate)
from empl e, dept d
where e.dept_num = d.dept_num
group by d.dept_num;
-- this is fine (if a little overkill)
select d.dept_num, min(salary), max(salary),
min(hiredate), max(hiredate)
from empl e, dept d
where e.dept_num = d.dept_num
group by d.dept_num;
-- do NOT confuse order-by and group-by!
-- if you want your results in a certain order, you STILL need order-by
-- (even with group-by)
-- what if I want the previous (good) example ordered by minimum salary?
select dept_num, min(salary), max(salary), min(hiredate),
max(hiredate), sum(salary)
from empl
group by dept_num
order by min(salary);
-- if you have a where clause, that SELECTION is done before the
-- GROUPING...
-- what's the average salary for each department ONLY for those
-- hired after July 15, 1991?
-- note that the selection based on hiredate is done BEFORE the
-- grouping based on dept_num:
select dept_num, avg(salary), count(*)
from empl
where hiredate > '15-Jul-1991'
group by dept_num;
-- note that that result IS different from this one:
select dept_num, avg(salary), count(*)
from empl
group by dept_num;
-- if you group by more than one column, you get the groups for
-- rows with the same value in BOTH of those columns (or
-- as many columns as are specified in the group-by clause)
select job_title, mgr, avg(salary), count(*)
from empl
group by job_title, mgr;
-- "having" is to groups what "where" is to rows...a way to
-- limit which GROUPS you see in the result;
-- for example, if I want dept_num's, and average salaries for each
-- dept_num, ONLY for dept_num's with an average salary greater than
-- 1500, I MUST use HAVING:
select dept_num, avg(salary)
from empl
group by dept_num
having avg(salary) > 1500;
-- let's see that in order of decreasing (descending) average salary:
select dept_num, avg(salary)
from empl
group by dept_num
having avg(salary) > 1500
order by avg(salary) desc;
-- and --- what if I only am interested in average salaries within each
-- dept of employees hired after July 15, 1991, and only for departments
-- with average salary greater than 1500 (and still in order of descending
-- average salary)
select dept_num, avg(salary)
from empl
where hiredate > '15-Jul-1991'
group by dept_num
having avg(salary) > 1500
order by avg(salary) desc;
-- now --- gee, we'd like to use department name with the above
-- instead of dept_num...
select dept_name, avg(salary)
from empl e, dept d
where e.dept_num = d.dept_num
and hiredate > '15-Jul-1991'
group by dept_name
having avg(salary) > 1500
order by avg(salary) desc;
Please send questions to
[email protected] .7566, Jones, Manager, 7839, 02-Apr-91, 2975.00,, 20 7654, Martin, Salesman, 7698, 28-Sep-91, 1250.00,1400.00,30 7698, Blake, Manager, 7839, 01-May-91, 2850.00,, 30 7782, Clark, Manager, 7839, 09-Jun-91, 2450.00,, 10 7788, Scott, Analyst, 7566, 09-Nov-91, 3000.00,, 20 7839, King, President,, 17-Nov-91, 5000.00,, 10 7844, Turner, Salesman, 7698, 08-Sep-91, 1500.00,0.00, 30 7876, Adams, Clerk, 7788, 23-Sep-91, 1100.00,, 20 7900, James, Clerk, 7698, 03-Dec-91, 950.00,, 30 7902, Ford, Analyst, 7566, 03-Dec-91, 3000.00,, 20 7934, Miller, Clerk, 7782, 23-Jan-92, 1300.00,, 10
Please send questions to
[email protected] .-- to load this,
-- 1. make sure that empltemp table *has* been created; note that,
-- since APPEND is used below, it need not be empty, in this case.
-- 2. create a file with these contents named empltemp_data2.ctl
-- 3. make sure empltemp_data2.dat exists before you attempt to run this.
-- 3. type at the UNIX prompt (WITHOUT the two dashes, and
-- replacing xx with YOUR Oracle username):
--
-- sqlldr userid=xx control=empltemp_data2.ctl log=empltemp_data2.log
LOAD DATA
INFILE 'empltemp_data2.dat' -- after INFILE, can put the name of an
-- input data file --- preferably
-- ending in .DAT?)
APPEND -- put if adding data to a non-empty table
INTO TABLE empltemp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(empl_num, empl_last_name, job_title, mgr, hiredate, salary,
commission, dept_num)
-- end
Please send questions to
[email protected] .-- to load this,
-- 1. make sure that empltemp table *has* been created, but is *empty*
-- 2. create a file with these contents named empltemp_data1.ctl
-- 3. type at the UNIX prompt (WITHOUT the two dashes, and
-- replacing xx with YOUR Oracle username):
--
-- sqlldr userid=xx control=empltemp_data1.ctl log=empltemp_data1.log
LOAD DATA
INFILE *
INTO TABLE empltemp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(empl_num, empl_last_name, job_title, mgr, hiredate, salary,
commission, dept_num)
BEGINDATA
7369,Smith,Clerk,7902,17-Dec-90,800.00,,20
"7499","Allen","Salesman","7698","20-Feb-91",1600.00,300.00,"30"
"7521",Ward,"Salesman",7698,"22-Feb-91",1250.00,500.00,30
Please send questions to
[email protected] .drop table empltemp cascade constraints;
create table empltemp
(empl_num char(4) primary key,
empl_last_name varchar2(15) not null,
job_title varchar2(10),
mgr char(4),
hiredate date not null,
salary decimal(6,2),
commission decimal(6,2),
dept_num char(2));
Please send questions to
[email protected] .--------
-- lab11.sql
--------
-- last modified: 11-9-00
--------
-- let's start all of this with a nice, clean set of example tables;
-- (copy and paste from course web page --- under "SQL and Lab-related
-- Examples List" --- if you do not have this script handy.)
@ set_up_ex_tbls
-- an aside: note that you CAN group by more than one column name.
--
-- if you do, then you want stats for rows where ALL of those column
-- names have the same value;
--
-- so, the below lets you see how many are in each (department, job_title)
-- pair, and their average salary:
select dept_name, job_title, count(*), avg(salary)
from empl e, dept d
where e.dept_num = d.dept_num
group by dept_name, job_title;
-- now: VIEW examples
-- create a view short_empl that JUST includes the employee's
-- name, their job_title, and their manager's employee number
-- (it is fairly common, note, to preced these with a "drop view"
-- statement when they are in scripts that might be re-run...)
drop view short_empl;
create view short_empl as
select empl_num, empl_last_name, job_title, mgr
from empl;
-- once a view is created, you can use it just like a
-- table in queries --- BUT remember, it is not REALLY a
-- table, it is "built" whenever it is referred to...
-- let's look at view short_empl:
select *
from short_empl;
-- I can now use this like ANY table in queries, note!
select empl_last_name, cust_lname
from short_empl, customer
where short_empl.empl_num = customer.empl_rep;
-- ...and can even use it to build another view...!
drop view cust_rep_display;
create view cust_rep_display as
select empl_last_name, cust_lname
from short_empl, customer
where short_empl.empl_num = customer.empl_rep;
select *
from cust_rep_display;
-- you can also give the columns in the view different names
-- than the columns/expressions being projected to create
-- that view
-- we'll do that in short_empl2:
drop view short_empl2;
create view short_empl2(name, "job category", manager) as
select empl_last_name, job_title, mgr
from empl;
-- see what column names appear when you look at short_empl2:
select *
from short_empl2;
-- the queries using views can be as complex as you'd like, note
-- (but you should use the column names you set up for the view)
--
-- (note the need for double quotes for the column name containing
-- a blank...!)
select "job category", name
from short_empl2
order by "job category";
-- (what if you use the original table's column names, instead?)
-- THIS WILL FAIL, complaining about the invalid column name;
select "job category", empl_last_name
from short_empl2
order by "job category";
-- you can also give the columns names using column aliases in the
-- sub-select
drop view short_empl3;
create view short_empl3 as
select empl_last_name last_name, job_title position
from empl;
select position, last_name
from short_empl3
order by last_name;
-- another view example --- with functions,
-- you HAVE to give the column another name...
-- let's say you want a view that gives the average salary
-- per job category: let's call it salary_avgs
-- this WON'T work --- it'll complain that you need a column
-- alias for avg(salary)
drop view salary_avgs;
create view salary_avgs as
select job_title, avg(salary)
from empl
group by job_title;
-- this WILL work...
create view salary_avgs(job, salary_avg) as
select job_title, avg(salary)
from empl
group by job_title;
select *
from salary_avgs;
-- now, it USED to be an error to use an "order by" clause in
-- view CREATION --- after all, it certainly doesn't make a
-- lot of sense to include it. You can always order the view
-- however you want when you display it.
-- but --- in Oracle 8.1.6, it is NOT giving the error that it
-- did in the previous version (!!) --- this WORKS?!
-- (no longer a "SQL command not properly ended" error?!)
drop view empl_roster;
create view empl_roster as
select empl_last_name, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num
order by dept_name;
select *
from empl_roster;
select *
from empl_roster
order by dept_loc;
-- I still think NOT having the order-by clause is
-- better STYLE, though:
drop view empl_roster;
create view empl_roster as
select empl_last_name, dept_name, dept_loc
from empl, dept
where empl.dept_num = dept.dept_num;
-- makes more sense, anyway --- you can always display the
-- view in any order you like!
-- by dept_name...
select dept_name, empl_last_name, dept_loc
from empl_roster
order by dept_name;
-- by dept_loc...
select dept_loc, empl_last_name, dept_name
from empl_roster
order by dept_loc;
-- by empl_last_name!
select *
from empl_roster
order by empl_last_name;
-- new INSERT variant --- you can insert values into one
-- table from another;
-- so, for example, say that I had a table Salesman:
-- (note --- I think a view WOULD be better, in this case!)
drop table salesman;
create table salesman
(name varchar2(15) not null,
empl_num char(4),
hiredate date not null,
salary number(6,2),
commission number(6,2),
primary key (empl_num),
foreign key (empl_num) references empl);
-- I COULD then fill it straight from the empl table, as shown
-- (does NOT change the empl table, note!!!)
insert into salesman
select empl_last_name, empl_num, hiredate, salary, commission
from empl
where job_title = 'Salesman';
-- this shows that salesman does, indeed, have contents
select *
from salesman;
-- like any insert, also, you can specify which columns are
-- being filled...
-- ugly query, but it demos this syntax:
-- let's say I wanted to insert MANAGERS of salesmen into
-- the salesman table...
insert into salesman(empl_num, name, salary, hiredate)
select distinct e2.empl_num, e2.empl_last_name, e2.salary, e2.hiredate
from empl e1, empl e2
where e1.mgr = e2.empl_num
and e1.job_title = 'Salesman';
select *
from salesman;
-- I can UNDO the rows added to salesman with the ROLLBACK
-- command -- it undoes all changes back to the PREVIOUS commit
-- (note -- commit is AUTOMATICALLY done on a SQL exit, create
-- table, drop table, and alter table)
rollback;
-- note, salesman table is now EMPTY:
select *
from salesman;
-- let's put back the salesmen (not their manager):
insert into salesman
select empl_last_name, empl_num, hiredate, salary, commission
from empl
where job_title = 'Salesman';
-- let's COMMIT these salesmen!
commit;
-- put in the manager:
insert into salesman(empl_num, name, salary, hiredate)
select distinct e2.empl_num, e2.empl_last_name, e2.salary, e2.hiredate
from empl e1, empl e2
where e1.mgr = e2.empl_num
and e1.job_title = 'Salesman';
select *
from salesman;
-- what will happen if I rollback NOW?
rollback;
-- no more manager, again! BUT the salesmen were committed,
-- and so they are still there:
select *
from salesman;
-- UPDATE examples
-- what is dept table like right now?
select *
from dept;
-- change all the locations to Detroit
update dept
set dept_loc = 'Detroit';
select *
from dept;
rollback;
-- to change only the Accounting dept's location to Detroit:
update dept
set dept_loc = 'Detroit'
where dept_name = 'Accounting';
select *
from dept;
-- the where clause can be more interesting, of course:
-- change the dept_name to George for all depts with a
-- location containing a letter a:
update dept
set dept_name = 'George'
where dept_loc like '%a%';
select *
from dept;
rollback;
-- you can set the new value to more than just a constant --- it
-- can be an expression, the result of a select, etc.
-- let's give all the Salesmen a 20% raise:
update empl
set salary = salary * 1.2
where job_title = 'Salesman';
select *
from empl
where job_title = 'Salesman';
rollback;
-- what if we ONLY give 30% raises to those salesmen making less than
-- the highest-paid clerk?
update empl
set salary = salary * 1.30
where job_title = 'Salesman'
and salary <
(select max(salary)
from empl
where job_title = 'Clerk');
-- you'll note that ONLY 2 rows were changed, with this data
select *
from empl
where job_title = 'Salesman';
rollback;
-- what if I want those salescritters to have new salary
-- of 5% more than the highest paid clerk (IF were making less)?
update empl
set salary = 1.05 * (select max(salary)
from empl
where job_title = 'Clerk')
where job_title = 'Salesman'
and salary <
(select max(salary)
from empl
where job_title = 'Clerk');
select *
from empl
where job_title = 'Salesman';
-- you CAN set a column to null;
-- let's set ALL of the mgr values to null:
update empl
set mgr = null;
select *
from empl
where mgr is null;
rollback;
-- and, the set expression can be as complex as you'd like,
-- also
-- let's say I want to change the mgr for all salesmen
-- to be Jones
update empl
set mgr = (select empl_num
from empl
where empl_last_name = 'Jones')
where job_title = 'Salesman';
select *
from empl;
rollback;
-- make sure it is clear, then --- the "set" and "where" clauses can be as
-- COMPLICATED as you desire;
--
-- let's make Jones the manager of all employees with location Chicago.
update empl
set mgr = (select empl_num
from empl
where empl_last_name = 'Jones')
where dept_num =
(select dept_num
from dept
where dept_loc = 'Chicago');
rollback;
-- ...and so on...!
-- DELETE examples
-- you CAN delete ALL the rows from a table without
-- nuking the table:
-- this removes ALL rows from salesman:
delete from salesman;
select *
from salesman;
rollback;
-- and, with the where clause, you can just delete
-- CERTAIN rows
-- to get rid of the Operations dept
-- this fails --- there are 'kids' in the empl table with
-- department Operations!
delete from dept
where dept_name = 'Operations';
-- so, let's change everyone with department Operations to
-- have a department of null:
update empl
set dept_num = null
where dept_num =
(select dept_num
from dept
where dept_name = 'Operations');
select *
from empl;
-- NOW, above delete will work:
delete from dept
where dept_name = 'Operations';
select *
from dept;
rollback;
-- get rid of managers
delete from empl
where job_title = 'Manager';
select *
from empl;
rollback;
-- note --- WHERE clause can be as COMPLEX as you'd like
-- want to delete all clerks making more than the lowest-paid
-- salesman?
delete from empl
where job_title = 'Clerk'
and salary >
(select min(salary)
from empl
where job_title = 'Salesman');
select *
from empl;
rollback;
Please send questions to
[email protected] .-- this file sets up example tables for query practice.
-- last modified: 9-15-99
-- create and populate table dept
drop table dept cascade constraints;
create table dept
(dept_num char(3) primary key,
dept_name varchar2(15) not null,
dept_loc varchar2(15) not null);
insert into dept
values
('100', 'Accounting', 'New York');
insert into dept
values
('200', 'Research', 'Dallas');
insert into dept
values
('300', 'Sales', 'Chicago');
insert into dept
values
('400', 'Operations', 'Boston');
insert into dept
values
('500', 'Management', 'New York');
-- create and populate table empl
drop table empl cascade constraints;
create table empl
(empl_num char(4) primary key,
empl_last_name varchar2(15) not null,
job_title varchar2(10),
mgr char(4),
hiredate date not null,
salary number(6,2),
commission number(6,2),
dept_num char(3),
foreign key (dept_num) references dept);
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7369', 'Smith', 'Clerk', '7902', '17-Dec-1990', 800.00, '200');
insert into empl
values
('7499', 'Michaels', 'Salesman', '7698', '20-Feb-1991', 1600.00, 300.00,
'300');
insert into empl
values
('7521', 'Ward', 'Salesman', '7698', '22-Feb-1991', 1250.00, 500.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7566', 'Jones', 'Manager', '7839', '02-Apr-1991', 2975.00, '200');
insert into empl
values
('7654', 'Martin', 'Salesman', '7698', '28-Sep-1991', 1250.00, 1400.00,
'300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7698', 'Blake', 'Manager', '7839', '01-May-1991', 2850.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7782', 'Raimi', 'Manager', '7839', '09-Jun-1991', 2450.00, '100');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7788', 'Scott', 'Analyst', '7566', '09-Nov-1991', 3000.00, '200');
insert into empl(empl_num, empl_last_name, job_title, hiredate,
salary, dept_num)
values
('7839', 'King', 'President', '17-Nov-91', 5000.00, '500');
insert into empl
values
('7844', 'Turner', 'Salesman', '7698', '08-Sep-1991', 1500.00, 0.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7876', 'Adams', 'Clerk', '7788', '23-Sep-1991', 1100.00, '400');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7900', 'James', 'Clerk', '7698', '03-Dec-1991', 950.00, '300');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7902', 'Ford', 'Analyst', '7566', '03-Dec-1991', 3000.00, '200');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate,
salary, dept_num)
values
('7934', 'Miller', 'Clerk', '7782', '23-Jan-1992', 1300.00, '100');
-- create and populate table customer
drop table customer cascade constraints;
create table customer
(cust_id char(6) primary key,
cust_lname varchar2(20) not null,
cust_fname varchar2(15),
empl_rep char(4),
cust_street varchar2(30),
cust_city varchar2(15),
cust_state char(2),
cust_zip varchar2(10),
cust_balance number(7, 2) default 0.0,
foreign key (empl_rep)
references empl(empl_num));
insert into customer
values
('123456', 'Firstly', 'First', '7499', '1111 First Street', 'Fortuna', 'CA',
'95520', 1111.11);
insert into customer
values
('234567', 'Secondly', 'Second', '7654', '2222 Second Street',
'McKinleyville', 'CA', '95523', 222.20);
insert into customer
values
('345678', 'Thirdly', 'Third', '7499', '333 Third Street', 'Arcata',
'CA', '95519-1234', 33.33);
Please send questions to
[email protected] .-- lab12.sql -- last modified: 11-16-00 -- *** BONUS EXAMPLES *** - -- (NOT from lab session --- -- examples of a feature, ||, mentioned at END of lab session, -- but not as prominently!!!) -- using || to project concatenated columns: -- simply put || between column names, constants, etc. and they -- will be concatenated together into a result-column: select empl_last_name || ': ' || job_title from empl; -- you can, of course, rename that column to make it prettier: select empl_last_name || ': ' || job_title "what each does" from empl; -- but, if you want a named column that you can use the column -- command with (see below!), you may want to put this into -- a view that gives your concatenated conglomeration a "real" -- column name: drop view empl_job_title; create view empl_job_title(name_and_title) as select empl_last_name || ': ' || job_title "what each does" from empl; select * from empl_job_title order by name_and_title desc; *** END of BONUS EXAMPLES *** -- sequence-related examples drop table painter cascade constraints; create table painter (ptr_num char(3), ptr_lname varchar2(30) not null, ptr_fname varchar2(15), primary key (ptr_num) ); -- create a sequence to help me set good primary keys -- over time for table painter drop sequence painter_seq; create sequence painter_seq increment by 2 start with 100; -- let's use the sequence to help set primary keys... insert into painter values (painter_seq.nextval, 'Van Gogh', 'Vincent'); insert into painter values (painter_seq.nextval, 'Monet', 'Claude'); insert into painter values (painter_seq.nextval, 'Da Vinci', 'Leonardo'); select * from painter; -- sequences can be used in queries, too... maybe??? -- (OK, these didn't work...) select * from painter where ptr_num = painter_seq.currval; select * from painter where ptr_num = (select painter_seq.currval from painter); -- some Oracle Data Dictionary tables -- user_catalog: synonym: cat -- contains info about tables and views for a particular user describe cat select * from cat; -- user_objects -- contains info about ALL objects defined by a particular user describe user_objects select object_name, object_type from user_objects; -- user_tables: synonym: tabs -- more information about user tables describe tabs select table_name from tabs; -- user_tab_columns: synonym: cols -- information about the columns of tables describe cols select column_name, table_name from cols order by table_name; -- user_views -- information about a user's views describe user_views select view_name from user_views; -- moving on to simple ASCII reports -- note: / on a line by itself means to re-run the previous -- SQL command (not SQL*PLus command, note!) -- so, this'll redo previous query: / -- first thing: it is good form to make sure that no previous -- breaks, columns, computes are going to mess up your coming -- report --- so clear 'em in your report script, first: clear breaks clear columns clear computes -- feedback: that little line saying how many rows were selected -- by a query -- want to know its current value? Here's how: show feedback -- here's how to set the feedback to a different number: set feedback 3 show feedback -- this'll note that 3 rows were selected. select * from painter; -- this will not note that one row was: select * from painter where ptr_lname = 'Monet'; -- this will simply turn feedback OFF set feedback off -- when you are ready to have results written to a file, spool! spool report_output.txt -- pagesize: how many lines in a "page" show pagesize set pagesize 30 -- you can set pagesize to 0 to mean, NEVER want page breaks (when -- you want to generate a flat file of data for another program, -- for example -- linesize: how many characters are in a line show linesize set linesize 50 -- newpage: the number of blank lines to put before the top title -- of a page (if any) show newpage set newpage 5 -- can set newpage to 0 as well --- nice for creating a flat file -- of input -- (oddly enough, the number of lines in a page is -- pagesize + newpage...) -- prompt: print a message to the screen (or to a file, of course, -- if spooling prompt Hello, there, here is a message -- (you already have echo set off by default, which is what you want -- in a report!) set echo off -- COLUMN examples! -- the BIG thing to remember: these do NOT change the tables or data -- in ANY WAY --- they are simply DISPLAY preferences; column empl_last_name heading 'Employee|Last Name' format a25 set linesize 80 select * from empl; -- lets make the employee last name column a trifle narrower -- (note how the longer names wrap to the next line!) column empl_last_name heading 'Employee|Last Name' format a2 / column empl_last_name heading "Employee|Last Name" format a16 / -- use format A for dates as well -- ugly! note how too-short a format for a string column causes ugly -- wrapping to next line; column hiredate heading 'Date of Hire' format a3 / -- prettier: column hiredate heading 'Date of Hire' format a12 / -- some numeric formatting examples -- let's play with formatting salaries... column salary heading 'Salary' format 999999999 select empl_last_name, dept_num, salary from empl; -- let's make it too narrow: column salary heading 'Salary' format 99 / -- let's make it better: column salary heading 'Salary' format 99999 / -- let's add fractional parts.. column salary heading 'Salary' format 99999.99 / -- do you want the comma? column salary heading 'Salary' format 99,999.99 / -- and a dollar sign, since this is money column salary heading 'Salary' format $99,999.99 / -- make first digit in format a 0 to get leading zeros column salary heading 'Salary' format $09,999.99 / -- no 0 for values less than 1, more than or equal to 0, this way: column commission heading 'Commission' format $9,999.99 select empl_last_name, commission from empl where job_title = 'Salesman'; -- this'll get you that 0: column commission heading 'Commission' format $9,990.99 / -- I can give one column the same format as another using "like" column salary heading 'Salary' format $99,990.99 column commission like salary heading 'Commission' select empl_last_name, salary, commission from empl where job_title = 'Salesman'; -- BREAK command -- do NOT confuse with group by clause! -- it is used in conjunction with order-by clause to get -- "prettier" ordered table displays -- note how this looks... select dept_num, empl_last_name, salary from empl order by dept_num; column dept_num heading 'Dept' format a4 / -- this BREAK causes only the "first" dept_num in a "row" to -- display; break on empl on dept_num / -- I can get blank lines between each broken-into section: break on empl on dept_num skip 1 / select dept_num, mgr, empl_last_name, salary from empl order by dept_num, mgr; break on empl on dept_num on mgr skip 1 / -- COMPUTE will let you make computations on those broken-into sections -- (this works in CONJUNCTION with a break!!) break on empl on dept_num on mgr skip 1 compute avg min max of salary on dept_num / -- 'compute' will show you your current compute definition compute -- and 'break' will show you your current break definition break -- (NOTE that each break, compute CLEARS/replaces the previous -- break, compute (respectively() -- want a TITLE aTOP each page? ttitle ttitle 'Beautiful|Three Line|Top Title' -- want a BOTTOM title? btitle btitle 'Gorgeous Two-line|Bottom Title' / spool off -- quick flat file example: -- aha! space is # of spaces BETWEEN columns; default is 1 set space 0 set newpage 0 set linesize 80 set pagesize 0 set echo off set feedback off set heading off spool flat_empl_data.txt select empl_last_name || ',' || salary from empl; -- don't forget to spool off, or results file may be empty or -- incomplete; spool off -- clean up when done (so as to not shock user with their -- next query) clear breaks clear columns clear computes set space 1 set feedback 6 set pagesize 14 set linesize 80 set newpage 1 set heading on -- to turn off titles set! ttitle off btitle off
Please send questions to
[email protected] .-- lab13.sql
-- last modified: 11-30-00
-- note from a fellow student:
-- one student HAD to have a newline after their last row of data in
-- SQL*Loader to get the last row to be loaded...
-- PLEASE NOTE:
-- NEED this so that dbms_output lines within triggers
-- WILL be shown on-screen
set serveroutput on
-- example of concatenation, to get prettier output:
select stu_lname || ', ' || stu_fname "Student", stu_major "Major"
from student
order by stu_lname;
-- let's TEST the tables and triggers set up in trigger_ex_create
-- and trigger_ex_trig scripts.
spool trigger_test.txt
-- set up inventory and orders tables
-- (includes bonus: naming of primary and foreign key constraints)
@ trigger_setup
-- how does error message look if I try to insert a row
-- into inventory with the same primary key as an existing row?
prompt this next insert SHOULD FAIL:
insert into inventory
values
(4, 'thingamabob', 500, 5.55);
-- and, is it prettier if a foreign key constraint is violated?
prompt this one should fail TOO:
insert into orders
values
(1, 'Jones', 783, 2000);
-- set up trigger inventory_update
@ trigger_example
-- NOTE --- if you get the error message, upon creating a trigger, that:
-- Warning: Trigger created with compilation errors.
-- then the following will give you information about those errors:
-- (there aren't any right now)
show errors
-- TEST 1 --- insert an order that SHOULD be fillable.
-- part 1 of test 1 --- what is CURRENTLY in orders, inventory?
prompt Test 1: Inserting a fillable order
prompt Contents of INVENTORY before Test 1
select *
from inventory;
prompt Contents of ORDERS before Test 1:
select *
from orders;
-- part 2 of test 1 --- let customer HUGHES attempt to order 10
-- of item num 1; let's make this order number 100.
prompt customer HUGHES tries to order 10 of item_num 1 (order number 100)
insert into orders
values
(100, 'Hughes', 1, 10);
-- part 3 of test 1 --- are tables NOW in the desired state?
-- (is there an order 100 as entered above?)
-- (are there 10 FEWER of item 1 in the Inventory table?)
prompt in Orders, is there now an Order 100?
select *
from orders;
prompt in Inventory, are there now 90 of item 1?
select *
from inventory;
-- TEST 2 --- is the order REFUSED if you try to order
-- too much?
prompt Test 2: See if an insert of a too-big order FAILS
-- what if Tuttle tries to order 91 of item 1?
prompt TEST 2 --- TRY to order 91 of item_num 1 (order number 200)
insert into orders
values
(200, 'Tuttle', 1, 91);
prompt there should be NO order 200 here:
select *
from orders;
prompt there should STILL be 90 of item 1:
select *
from inventory;
-- TEST 3: could also test that it DOES allow you to order ALL
-- remaining in inventory of an item...
prompt Test 3: Will an order for ALL of an item succeed?
-- what if Shmoo tries to order 200 of item 2?
prompt TEST 3 --- TRY to order 200 of item_num 2 (order number 300)
insert into orders
values
(300, 'Shmoo', 2, 200);
prompt there should be an order 300 here:
select *
from orders;
prompt there should be 0 of item 2:
select *
from inventory;
-- more REPORT-related odds and ends
-- to set the character between column name, contents to something
-- besides default of '-'
set underline !
-- this tells me what underline is currently set to:
show underline
select *
from inventory;
-- maybe not...
set underline '-'
-- to see your current column settings:
column
column stu_age heading 'Age' format 990.9
-- to see the current column settings for stu_age:
column stu_age
-- to clear JUST the settings for ONE column (here, stu_age):
column stu_age clear
column stu_age
-- with BREAK, you can specify to go to next PAGE with "skip page"
break on dept_num skip page
ttitle 'Employees|By Department'
column salary format $999,990.99
set pagesize 20
-- you see the "skip page" effect here because you see the columns
-- headings for EACH diff dept_num, and top titles before each
-- diff dept_num, too
select dept_num, empl_last_name, salary
from empl
order by dept_num;
spool off
-- let's create a FLAT file of comma-delimited data from the
-- empl table
clear columns
clear break
clear compute
-- NO additional lines at top of each page!
set newpage 0
set space 0
set linesize 80
-- if you set pagesize to 0, it works well, oddly enough
set pagesize 0
set echo off
set feedback off
-- this suppresses the printing of column headings
set heading off
spool empl_data.txt
column salary format 999990.99
select empl_num || ', ' || empl_last_name || ', ' || dept_num ||
', ' || salary
from empl;
spool off
-- clean up when done
clear breaks
clear columns
clear computes
set feedback 6
set pagesize 14
set linesize 80
set newpage 1
set space 1
set heading on
-- to turn off titles set!
ttitle off
btitle off
Please send questions to
[email protected] .-- trigger_example.sql
-- last modified: 11-30-00
-- attempt at a simple trigger example
--
-- this creates the trigger inventory_update, which updates the
-- quantity in the inventory table when an order is inserted into
-- the orders table.
--
-- it complains and does not permit the orders insert if the
-- order is for more than the current quantity available.
drop trigger inventory_update;
-- EVERY TRIGGER needs to indicate BEFORE or AFTER WHAT actions
-- on WHAT table it needs to be executed....
--
-- 'for each row' means to perform trigger actions for EACH
-- individual row affected by that trigger action
--
-- think carefully: should action be done BEFORE or AFTER triggering
-- event?
--
-- note: Oracle appears to only permit one trigger per table;
-- thus, you CAN have more than one action after the 'before' or 'after';
--
-- also note: Oracle takes some pains to avoid undesirable trigger
-- cascades; beware;
create trigger inventory_update
before insert
on orders
for each row
declare
-- declare section can be omitted if you do not want
-- to declare any variables...
amt_ordered integer;
item_ordered integer;
amt_in_stock integer;
begin
-- set some variables for convenience
-- :new.colName lets you use the value of attribute colName
-- of the newly[to be]
-- inserted or updated row.
--
-- :old.colName lets you use the value of the attribute
-- colName of the previous[to be]deleted or updated row
-- set variable amt_ordered to the value of
-- attribute order_quantity in the NEWLY to-be-inserted
-- orders row; note the := for assignment to a variable
amt_ordered := :new.order_quantity;
-- set variable item_ordered to be the value of
-- attribute item_num in the NEWLY to-be-inserted
-- orders row
item_ordered := :new.item_num;
-- get quantity of that item in inventory
-- and put it into variable amt_in_stock
-- (note use of PL/SQL 'into' clause in this
-- otherwise-normal select statement)
select item_quantity
into amt_in_stock
from inventory
where item_num = item_ordered;
-- is it "safe" to do this insert?
if (amt_in_stock >= amt_ordered) then
-- it is safe --- update this item's quantity
-- in the inventory table
update inventory
set item_quantity = item_quantity - amt_ordered
where item_num = item_ordered;
dbms_output.put_line('Successfully updated inventory'
|| ' item number: ' || item_ordered);
else
-- this will PREVENT the insert into orders,
-- and will complain with the following
-- error message!!
raise_application_error ( -20601, 'Order number ' ||
:new.order_num || ' cannot be placed, '
|| 'because order quantity ' || amt_ordered
|| ' of item ' || item_ordered ||
' is more than stock on hand of ' ||
amt_in_stock);
end if;
end;
-- this slash below seems to be necessary to "compile" trigger!
-- DO NOT FORGET THIS!!!
/
Please send questions to
[email protected] .-------- -- trigger_setup.sql -- last modified: 11-30-00 -------- -- set up tables for trigger example -------- -- set up an inventory table -- (bonus aspect: giving a name to the primary key, which is -- actually a constraint object) drop table inventory cascade constraints; create table inventory (item_num integer, item_name varchar2(15), item_quantity integer default(0), item_price decimal(5,2), constraint inventory_pk primary key(item_num)); -- set up a simple orders table drop table orders cascade constraints; create table orders (order_num integer, cust_name varchar2(20), item_num integer not null, order_quantity integer default(1), constraint orders_pk primary key(order_num), constraint inventory_orders_fk foreign key (item_num) references inventory); -- stick some initial contents into these tables insert into inventory values (1, 'widget', 100, 1.11); insert into inventory values (2, 'gadget', 200, 2.22); insert into inventory values (3, 'whatsit', 300, 3.33); insert into inventory values (4, 'doohickey', 400, 4.44); prompt Inventory now contains: select * from inventory; prompt But, there are NO orders yet: select * from orders;