SQL*PLUS Tutorial
CREATE TABLE emp
(empid NUMBER(5),
fname VARCHAR2(20),
lname VARCHAR2(20),
sex VARCHAR2(1),
ssn VARCHAR2(9),
salary NUMBER(8),
deptno NUMBER(5)
);
INSERT INTO emp VALUES (501,'JOHN','DOE','M','500000001',30000,4001);
INSERT INTO emp VALUES (502,'JOHN','SMITH','M','500000002',40000,4001);
INSERT INTO emp VALUES (503,'SEAN','LEE','M','500000003',30000,4001);
INSERT INTO emp VALUES (504,'EVAN','SEAN','M','500000004',50000,4002);
INSERT INTO emp VALUES (505,'REBECCA','SEAN','F','500000005',30000,4002);
INSERT INTO emp VALUES (506,'TIM','DUNCAN','M','500000006',30000,4002);
INSERT INTO emp VALUES (507,'ROBERT','DUVAL','M','500000007',30000,4002);
INSERT INTO emp VALUES (508,'CLINT','JOHNSON','M','500000008',30000,4002);
INSERT INTO emp VALUES (509,'SARRAH','MCMILLAN','F','500000009',60000,4003);
INSERT INTO emp VALUES (510,'DAVID','LIMB','M','500000010',30000,4003);
INSERT INTO emp VALUES (511,'DAVID','BOWE','M','500000011',30000,4003);
INSERT INTO emp VALUES (512,'SMITH','CLARK','M','500000012',50000,4003);
INSERT INTO emp VALUES (513,'TED','KENNEDY','M','',30000,4003);
INSERT INTO emp VALUES (514,'RONALD','REAGAN','M','500000014',30000,4003);
INSERT INTO emp VALUES (515,'FRANKLIN','ROSSEVELT','M','500000015',30000,4003);
INSERT INTO emp VALUES (516,'George','BUSH','M','500000016',30000,4004);
INSERT INTO emp VALUES (517,'SAM','MALONE','M','',30000,4004);
INSERT INTO emp VALUES (518,'NANCY','REAGAN','F','500000018',30000,4004);
INSERT INTO emp VALUES (519,'HILLARY','CLINTON','F','500000019',30000,4004);
INSERT INTO emp VALUES (520,'MARRY','GEORGIA','F','500000020',30000,4004);
alter table emp
add (manager varchar(11));
describe emp;
alter table emp
modify (lname varchar(30) not null);
describe emp;
alter table emp
add constraint pk_empid primary key (empid);
describe emp;
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'EMP';
(ALTER TABLE emp DROP CONSTRAINT pk_empid; # to drop the primay constraint)
CREATE TABLE dept
(deptno NUMBER(5) NOT NULL,
name VARCHAR2(20) NOT NULL,
building VARCHAR2(20),
CONSTRAINT pk_deptno PRIMARY KEY (deptno)
);
INSERT INTO dept VALUES (4001, 'SHOES', 'BUILDING I');
INSERT INTO dept VALUES (4002, 'WOMAN CLOTHING', 'BUILDING II');
INSERT INTO dept VALUES (4003, 'MEN CLOTHING', 'BUILDING II');
INSERT INTO dept VALUES (4004, 'KITCHEN APPLIANCES', 'MAIN BUILDING');
# add foreginkey and delete of referenced table row will affect this table rows
alter table emp
add CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept (deptno) ON DELETE CASCADE
select * from emp where deptno = 4001;
delete from dept where deptno = 4001;
select * from emp where deptno = 4001;
INSERT INTO dept VALUES (4001, 'SHOES', 'BUILDING I');
INSERT INTO emp VALUES (501,'JOHN','DOE','M','500000001',30000,4001, NULL);
INSERT INTO emp VALUES (502,'JOHN','SMITH','M','500000002',30000,4001, NULL);
INSERT INTO emp VALUES (503,'SEAN','LEE','M','500000003',30000,4001, NULL);
# the other way around : delete 4001 in emp will not affect dept table.
#check constraints
alter table emp
add constraint ck_sex CHECK (sex IN ('M', 'F'));
INSERT INTO emp VALUES (600, 'SOON', 'CHUN', 'f', '500000029', 50000,4002,NULL);
alter table emp
add constraint ck_salary CHECK (salary > 10000);
INSERT INTO emp VALUES (601, 'JUNE', 'CHO', 'M', '500000030', 9000, 4001,
NULL);
## create from another table
Create table emp_dept_1 as
select fname, lname, ssn, salary from emp where deptno = 4001;
describe emp_dept_1;
select * from emp_dept_1;
create table highpay as
select * from emp where salary > 40000;
create table emptycopy as
select * from emp where 3=5;
# show how to delete a colume in a table.
create table temp as
select empid, fname, lname, sex, ssn, salary, deptno from emp;
drop table emp;
create table emp as select * from temp;
#Add column
ALTER TABLE emp add join_dt Date;
#drop column
alter table emp drop column join_dt;
#creat drop indexes/sequences
create index emp_indx on emp (ssn);
drop index emp_indx;
#create/drop view - hide salary or sex
create view emp_dno1 as
select fname, lname, deptno from emp where deptno=4001
create table dept2 as
select * from dept where 3=5;
alter table dept2
add (managerssn varchar2(9));
INSERT INTO dept2 VALUES (4001, 'SHOES', 'BUILDING I', '500000003');
INSERT INTO dept2 VALUES (4002, 'WOMAN CLOTHING', 'BUILDING II', '500000004');
INSERT INTO dept2 VALUES (4003, 'MEN CLOTHING', 'BUILDING II', '500000009');
INSERT INTO dept2 VALUES (4004, 'KITCHEN APPLIANCES', 'MAIN BUILDING',
'500000018');
create view dep_managers as
select dept2.deptno, name, managerssn, lname, fname from emp, dept2
where emp.ssn = dept2.managerssn;
select * from dep_managers;
select view_name from user_views;
drop view dep_managers;
# create/alter sequences
create sequence deptno_seq
start with 4005
maxvalue 9999
nocycle;
select deptno_seq.nextval from dual;
insert into dept values (deptno_seq.nextval, 'FINANCE', 'FOX BUILDING');
drop sequence deptno_seq;
A note on dual :
SQL> select sysdate from dual;
other sql functions such as abs, average, calculations etc can be used with dual.
#grant and revoke
#synonyms
#DML insert/select/delete/update
select salary, lname, fname from emp where deptno=4001;
update emp
set salary = salary * 1.5 where deptno = 4001;
delete from emp where deptno=4003;
select * from emp where deptno=4003;
rollback
select * from emp where deptno=4003;
delete from emp where deptno=4003;
commit;
select * from emp where deptno=4003;
create view dept_average_sal as
select dept.deptno, name, avg(salary) as average_sal
from dept, emp
where emp.deptno = dept.deptno
group by dept.deptno, name;
select * from dept_average_sal;
# metadata
select table_name from user_tables;
select * from tab; (cat)
# manipulation several tables
select distinct building from dept;
select dept.name, sum(emp.salary)
from emp, dept
where emp.deptno = dept.deptno
group by dept.name;
select fname, lname, salary as currentsal,
(salary * 1.25) as proposedsal
from emp;
select d.name as Department,
count(emp.empid) as numberofemployee
from dept d, emp
where d.deptno = emp.deptno
group by d.name;
#employee with max salary
select fname, lname, salary
from emp
where salary = (select max(salary) from emp);
# IN operator
select e.fname, e.lname, d.name
from emp e, dept d
where e.deptno = d.deptno AND d.name IN ('MEN CLOTHING', 'WOMAN CLOTHING');
# GROUP BY; HAVING
select name, avg(salary)
from emp, dept
where emp.deptno = dept.deptno
group by dept.name
having avg(salary) > 33000;
#TO_CHAR
create table employee
(empid NUMBER(5),
fname VARCHAR2(20),
lname VARCHAR2(20),
bdate DATE,
sex VARCHAR2(1),
ssn VARCHAR2(9),
salary NUMBER(8),
managerssn VARCHAR(9),
deptno NUMBER(5)
);
INSERT INTO employee VALUES
(501,'JOHN','DOE','09-JAN-55','M','500000001',30000,'500000002', 4001);
INSERT INTO employee VALUES
(502,'JOHN','SMITH','09-FEB-45','M','500000002',40000,'500000002',4001);
INSERT INTO employee VALUES
(503,'SEAN','LEE','09-MAR-65','M','500000003',30000,'500000002',4001);
INSERT INTO employee VALUES
(504,'EVAN','SEAN','09-APR-66','M','500000004',50000,'500000008',4002);
INSERT INTO employee VALUES
(505,'REBECCA','SEAN','09-MAY-51','F','500000005',30000,'500000008',4002);
INSERT INTO employee VALUES
(506,'TIM','DUNCAN','09-JUN-62','M','500000006',30000,'500000008',4002);
INSERT INTO employee VALUES
(507,'ROBERT','DUVAL','09-JUL-54','M','500000007',30000,'500000008',4002);
INSERT INTO employee VALUES
(508,'CLINT','JOHNSON','09-AUG-34','M','500000008',30000,'500000008', 4002);
INSERT INTO employee VALUES
(509,'SARRAH','MCMILLAN','09-SEP-59','F','500000009',60000,'500000009',4003);
INSERT INTO employee VALUES
(510,'DAVID','LIMB','09-OCT-60','M','500000010',30000,'500000009',4003);
INSERT INTO employee VALUES
(511,'DAVID','BOWE','09-NOV-66','M','500000011',30000,'500000009',4003);
INSERT INTO employee VALUES
(512,'SMITH','CLARK','09-DEC-71','M','500000012',50000,'500000009',4003);
INSERT INTO employee VALUES
(513,'TED','KENNEDY','09-JAN-55','M','',30000,'500000009',4003);
INSERT INTO employee VALUES
(514,'RONALD','REAGAN','09-FEB-54','M','500000014',30000,'500000009',4003);
INSERT INTO employee VALUES
(515,'FRANKLIN','ROSSEVELT','09-MAR-58','M','500000015',30000,'500000009',4003);
INSERT INTO employee VALUES
(516,'George','BUSH','09-JUN-59','M','500000016',30000,'500000016',4004);
INSERT INTO employee VALUES
(517,'SAM','MALONE','09-JUL-63','M','',30000,'500000016',4004);
INSERT INTO employee VALUES
(518,'NANCY','REAGAN','09-AUG-65','F','500000018',30000,'500000016',4004);
INSERT INTO employee VALUES
(519,'HILLARY','CLINTON','09-SEP-65','F','500000019',30000,'500000016',4004);
INSERT INTO employee VALUES
(520,'MARRY','GEORGIA','09-OCT-45','F','500000020',30000,'500000016',4004);
select 'the oldest employee' || to_char(min(bdate)) from employee;
### To check if there are duplicates in fname field
select fname,count(fname) from employee group by fname having count(fname)>1;### check unique items
select unique(fname) from employee
### only those names which are unique
select fname,count(fname) from employee group by fname having count(fname)=1;
#DCL Grant Revoke
grant select, insert, update on table emp to dconnors;
select * from soon.emp;
create synonym ouremp for soon.emp;
select * from ouremp;
revoke select, insert, update on emp from dconnors;
Editing SQL Buffer and file manipulation