Oracle Database Development

This practical assignment focuses on Oracle database development, covering various aspects like creating tablespaces and users, writing SQL queries for data manipulation and retrieval, generating reports using SQL*Plus, and developing PL/SQL procedures. The assignment includes tasks involving creating tables (Publisher, PrintJob, PurchaseOrder, PrintItem, RequestItem, SuperEmployee, Employee), inserting data, implementing constraints, writing complex SQL queries (including aggregate functions and joins), generating reports using SQL*Plus with formatting and pagination, and creating and executing PL/SQL procedures. A critical appraisal section reflects on the experience using Oracle 10g, highlighting its backward compatibility, efficient memory management, advanced SQL engine, and the ease of managing complex data. The assignment demonstrates proficiency in SQL, PL/SQL, and SQL*Plus for database management and reporting.

TABLESPACE AND USER
Task 1
Table Space 1
a)CREATE USER 2222_publisherDB
IDENTIFIED BY pwd4publisherDB
DEFAULT TABLESPACE 2222_publisherDB
TEMPORARY TABLESPACE 2222_publisherDB
QUOTA 20M on 2222_publisherDB;
/*
*The above CREATE USER statement would create a new user called
*2222_publisherDB in the Oracle database whose password is
*pwd4publisherDB, the default tablespace would be 2222_publisherDB
*with a quota of 20MB, and the temporary tablespace would be
*2222_publisherDB.
*/
Table Space 2
CREATE USER 2222_publisherDB
IDENTIFIED BY pwd4publisherDB
DEFAULT TABLESPACE 3333_publisherDB
TEMPORARY TABLESPACE 3333_publisherDB
QUOTA 20M on 3333_publisherDB;
/*
*The USER 2222_publisherDB in the Oracle database whose password is
*pwd4publisherDB, has access the default tablespace would be
*3333_publisherDB with a quota of 20MB, and the temporary tablespace
*would be 3333_publisherDB.
*/
GRANT create session TO publisherDB;
GRANT create table TO publisherDB;
GRANT create view TO publisherDB;
GRANT create any trigger TO publisherDB;
GRANT create any procedure TO publisherDB;
GRANT create sequence TO publisherDB;
GRANT create synonym TO publisherDB;
/* the above gave full pemission to the user created the statement*/
b) /*PUBLISHER TABLE*/

CREATE TABLE Publisher
( publisher_name varchar2(50) NOT NULL,
city varchar2(50),
street varchar2(25),
post_code varchar2(10),
phone_no varchar2(12) NOT NULL,
fax_no varchar(20) NOT NULL ,
CONSTRAINT Publisher_unique UNIQUE (phone_no, fax_no),
CONSTRAINT Publisher_pk PRIMARY KEY (publisher_name)
);
/*
*the created publisher table with publiher_name as primary key,
*city ,street, postcode, phoneno and faxno, has been populated with
*values inserted to it respectively.
*/
INSERT ALL
INTO Publisher (publisher_name, city, street, post_code, phone_no, fax_no)
VALUES ('john new','glasgow','15','213123','124124124124','124124124134')
INTO Publisher (publisher_name, city, street, post_code, phone_no, fax_no)
VALUES ('Mathew smith','edinburg','14','213121','124124124121','124124124131')
INTO Publisher (publisher_name, city, street, post_code, phone_no, fax_no)
VALUES ('Marcus ali','paisley','13','213120','124124124662','124124124132')
INTO Publisher (publisher_name, city, street, post_code, phone_no,
fax_no)VALUES('umer farooq','paisley','11','213170','124124124122','12
4124124152')
SELECT * FROM dual;
/*PRINT JOB TABLE*/
CREATE TABLE PrintJob
( job_no number(10) NOT NULL,
start_date date(10),
completion_date date),
printing_cost number(10),
publisher_name varchar2(50)
FOREIGN KEY (publisher_name)
REFERENCES Publisher(publisher_name),
CONSTRAINT PrintJob_pk PRIMARY KEY (job_no)
);
/*
*the created PrintJob table with Job_no as primary key, start_date,
*completion_date, printing_cost and publisher_name, has been populated
*with values inserted to it respectively.
*/

INSERT ALL
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name) VALUES ('J10','12-1-2015','12-1-
2016',11233,'john new')
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name) VALUES ('J12','11-5-2015','11-1-
2017',1164,'Mathew smith')
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name) VALUES (''J11','12-1-2017','12-1-
2018',1000,'Marcus ali')
INTO PrintJob (job_no,start_date, completion_date,
printing_cost,publisher_name)VALUES('J19','11-5-2016','11-1-
2018',1777333,'umer farooq')
SELECT * FROM dual;
/*PURCHASE ORDER TABLE*/
CREATE TABLE PurchaseOrder
( order_no number(10) NOT NULL,
order_date date,
job_no number(10),
FOREIGN KEY (job_no)
REFERENCES PrintJob(job_no)
CONSTRAINT PurchaseOrder_pk PRIMARY KEY (order_no)
);
/*
*the created PurchaseOrder table with ‘orderno’ as primary key,
*orderdate, jobno has values assigned to it respectively.
*/
INSERT ALL
INTO PurchaseOrder (order_no,order_date, job_no) VALUES ('O10','12-1-
2015','J10')
INTO PurchaseOrder (order_no,order_date, job_no) VALUES ('O11','6-2-
2016','J11')
INTO PurchaseOrder (order_no,order_date, job_no) VALUES ('O12','2-10-
2016','J12')
SELECT * FROM dual;
/*PRINT ITEM TABLE*/
CREATE TABLE PrintItem
( item_Code number(10) NOT NULL,
type varchar2(50),
price varchar2(50),
CONSTRAINT PrintItem_pk PRIMARY KEY (item_Code)

);
/*
*created a new printitem table with ‘item_Code’ as primary key,
*type, price has values assigned to it respectively.
*/
INSERT ALL
INTO PrintItem (item_Code, type, price) VALUES ('I10','part',1500)
INTO PrintItem (item_Code, type, price) VALUES ('I11',' vehicle
part',2500)
INTO PrintItem (item_Code, type, price) VALUES ('I12',' motor
part',3500)
SELECT * FROM dual;
/*REQUEST ITEM TABLE*/
CREATE TABLE RequestItem
( item_Code number(10) NOT NULL,
order_no number(10) NOT NULL,
quantity varchar2(50),
FOREIGN KEY (order_no)
REFERENCES PrintJob(order_no),
FOREIGN KEY (item_Code)
REFERENCES PrintItem(item_Code)
CONSTRAINT RequestItem_pk PRIMARY KEY (item_Code, order_no)
);
/*
*the created RequestItem table with ‘orderno, itemcode as
*primary key and quantity has values inserted respectively, hence.
*/
INSERT ALL
INTO RequestItem (item_Code,order_no,quantity) VALUES ('O10','I10',2)
INTO RequestItem (item_Code,order_no,quantity) VALUES ('O11','I11',3)
INTO RequestItem (item_Code,order_no,quantity) VALUES ('O12','I12',6)
SELECT * FROM dual;
/*SUPERVISOR EMPLOYEE TABLE*/
CREATE TABLE SuperEmployee
( super_emp_no number(50) NOT NULL,

sf_name varchar2(50),
sl_name varchar2(25),
sgender varchar2(10),
sdob date,
scity varchar2(50),
ssalary varchar2(20) NOT NULL,
publisher_name varchar2(50) NOT NULL,
FOREIGN KEY (publisher_name)
REFERENCES Publisher(publisher_name),
CONSTRAINT SuperEmployee_pk PRIMARY KEY (super_emp_no)
);
/*
*the SUPERVIVISORS EMPLOYEE table has more columns with super_emp_no as
PRIMARY KEY and other *columns normal
*/
INSERT ALL
INTO SuperEmployee (super_emp_no,
sf_name,sl_name,sgender,sdob,scity,ssalary,publisher_name) VALUES
('S10','bush','jones','M','11-2-2016','Glasglow',50000,'john new')
INTO SuperEmployee (super_emp_no,
sf_name,sl_name,sgender,sdob,scity,ssalary,publisher_name) VALUES
('S11','michel','richard','F','11-2-1922','Paisley',80000,'Marcus
ali')
INTO SuperEmployee (super_emp_no,
sf_name,sl_name,sgender,sdob,scity,ssalary,publisher_name) VALUES
('S12','zack','stay','M','1-6-2017','Edingurgh',65000,'Mathew smith')
INTO SuperEmployee (super_emp_no,
sf_name,sl_name,sgender,sdob,scity,ssalary,publisher_name) VALUES
('S18','carme','joe','M','1-6-2017','Edingurgh',45000,'john new')
SELECT * FROM dual;
/*EMPLOYEE TABLE*/
CREATE TABLE Employee
( emp_no number(50) NOT NULL,
f_name varchar2(50),
l_name varchar2(25),

gender varchar2(10),
dob date,
city varchar2(50),
salary varchar(20) NOT NULL,
super_emp_no number(10) NOT NULL,
publisher_name varchar2(50) NOT NULL,
FOREIGN KEY (publisher_name)
REFERENCES Publisher(publisher_name),
FOREIGN KEY(super_emp_no) REFERENCES Employee(super_emp_no),
CONSTRAINT Employee_pk PRIMARY KEY (emp_no)
);
/*
*the EMPLOYEE table has more columns with emp_no as PRIMARY KEY and other
*columns normal
*/
INSERT ALL
INTO Employee (emp_no,
f_name,l_name,gender,dob,city,salary,publisher_name,super_emp_no) VALUES
('E10','hayley','Williams','F','11-2-2016','Glasglow',25000,'john new','E10')
INTO Employee (emp_no,
f_name,l_name,gender,dob,city,salary,publisher_name,super_emp_no)
VALUES ('E11','martin','richards','M','11-2-
2017','Paisley',80000,'Marcus ali','E11')
INTO Employee (emp_no,
f_name,l_name,gender,dob,city,salary,publisher_name,super_emp_no)
VALUES ('E12','jack','storm','M','1-6-2017','Edingurgh',63000,'Mathew
smith','E12')
INTO Employee (emp_no,
f_name,l_name,gender,dob,city,salary,publisher_name,super_emp_no)
VALUES ('E12','jack','storm','M','1-6-2017','Edingurgh',25000,'john
new','E12')
SELECT * FROM dual;
TASK2

a) SELECT f_name,l_name,gender, AVG(salary) AS “Avg salaries”
FROM Employee
WHERE salary > 25000 AND publisher_name = 'john new';
RESULT
f_name l_name gender Avg salaries
------- ------- -------- --------------
Hayley Williams F 25000
jack storm M 25000
b)SELECT emp_no as EmpNo, l_name as Supervisee, super_emp_no as
SupervisorNo, slname as Supervisor
FROM Employee, SuperEmployee
WHERE publisher_name = 'john new';
RESULT
EmpNo Supevisee SupervisorNo Supervisor
------- ---------- -------------- --------------
E10 Williams S10 jones
E12 Storm S18 joe
c)SELECT publisher_name AS PublisherName,
COUNT(*) total,
COUNT(DECODE (gender, F,1)) AS Female
COUNT(DECODE (gender, M,1)) AS Male
FROM Employee
GROUP BY publisher_name;
RESULT
PublisherName Male Female
--------------- ------ ------
John new 1 1
Marcus ali 1 0
Mathew smith 1 0
d)i)

/*To view table indexes with each line formatted not to overflow into double lines for Publisher Table*/
SET linesize 100
column table_owner format a30
column table_name format a20
column index_name format a20
column column_name format a20
column column_position format 999
SELECT index_name, column_name, column_position
FROM 2222_publisherDB_ind_columns
WHERE table_name='Publisher'
ORDER BY index_name, column_position
RESULT
TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
----------------- ----------- ------------ ---------------- ---------------
2222_publisherDB Publisher PK_Publisher publisher_name 1
ii)
/*
*This should List table name, column name and with the enforced constraint *on
it
*/
SELECT index_name, column_name
FROM 2222_publisherDB_constraints
WHERE table_name = 'Publisher'
AND constraint_name = publisher_name;
TASK 3 – SQL * Plus report
/*For each PrintJob
*List the details
*/
SELECT publisher_name,job_no,start_date, completion_date, printing_cost
FROM PrintJob;
/*SQL PLUS COMMNAD*/
SQL>>start c:\orabook\printingreport.sql
rem ***********************************************
rem Purpose : Script to generate Printing Cost of all Job Report
rem Date : 10- April-2016

rem Place : Miami
rem ***********************************************
rem set break and compute settings
break on report on publisher_name skip page on job_no skip 2
compute sum of printing_cost on job_no publisher_name report
set pagesize 24
set linesize 90
set feedback off
column printing_cost format 99,999
column start_date heading 'start_date'
column start_date heading 'completion_date'
ttitle skip 1 right 'Page:' format 99 sql.pno s
kip 1 center 'PUBLISHER PRINTING COST' skip 2
spool printing_costreport.lst
select p.publisher_name publisher_name, j.job_no,
printing_cost
from publisher_name p, job_no j, printing_cost pc
order by publisher_name, job_no;
spool off
set feedback on
rem clear settings
clear compute
clear break
clear column
ttitle off
RESULT
Page:1
PUBLISHER PRINTING COST
Publisher_name job_no start_date completion_date printing_costs
-------------- ------ ---------- --------------- --------------
john new J10 12-1-2015 12-1-2016 11233
J19 11-1-2016 12-1-2018 1777333
**** -----
sum 1788566
***** -------
sum 1788566
Mathew smith J12 11-5-2015 11-1-2017 1164
**** ---------
sum 1164
***** ------

sum 1164
Marcus ali J11 12-1-2017 12-1-2018 1000
**** ----
sum 1000
**** ------
sum 1000
TASK 4 PL/SQL PROCEDURE
/*
*Publisher Table
*/
CREATE TABLE Publisher
( publisher_name varchar2(50) NOT NULL,
city varchar2(50),
street varchar2(25),
post_code varchar2(10),
phone_no varchar2(12) NOT NULL,
fax_no varchar(20) NOT NULL ,
CONSTRAINT Publisher_unique UNIQUE (phone_no, fax_no)
CONSTRAINT Publisher_pk PRIMARY KEY (publisher_name)
);
/*A stored procedure, accept 3 IN parameters an insert it into table
*“Publisher”
*/
CREATE OR REPLACE PROCEDURE insertPublisher(
p_publishername IN PUBLISHER.PUBLISHER_NAME%TYPE,
p_city IN PUBLISHER.CITY%TYPE,
p_phoneno IN PUBLISHER.PHONE_NO%TYPE)
IS
BEGIN
INSERT INTO Publisher ("publisher_name", "city", "phone_no")
VALUES (p_publishername, p_city, p_phoneno);
COMMIT;
END;
/
/*Call from the above PL/SQL procedure is thus*/
BEGIN
insertPublisher('Academicbooks', 'New york', '333-333-333');
END;
/*This show publishers details output*/

DECLARE
p_name printjob.publisher_name%type := 'john new';
p_jobno printjob.job_no%type;
p_startdate printjob.start_date%type;
p_completion printjob.completion_date;
BEGIN
SELECT publisher_name,job_no, start_date , completion_date
FROM Printjob
WHERE publisher_name = p_name;
DBMS_OUTPUT.PUT_LINE ('Publisher Name: '|| p_name);
DBMS_OUTPUT.PUT_LINE ('Job No: ' || p_jobno);
DBMS_OUTPUT.PUT_LINE ('Start Date: ' || p_startdate);
DBMS_OUTPUT.PUT_LINE ('Completion Date: ' || p_completiondate);
/*
*A NO-DATA-FOUND exception when no dat can be pulled from the table in
*question
*/
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such Publisher!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
RESULT
Publisher Name: john new
Job No: J10
Start Date: 12-1-2015
Completion Date: 12-1-2016
Publisher Name: john new
Job No: J19
Start Date: 11-5-2016
Completion Date: 11-1-2018
PL/SQL procedure successfully completed.
TASK 4
b)
/*SQL satetment that clears the content of the table PublisherDetails*/
DELETE FROM Printjob;
/*PL/SQL anonymous block statement to invoke (execute) the PL/SQL procedure*/

SELECT publisher_name, city, phone_no FROM insertPublisher WHERE
publisher_name = 'john new';
-- call procedure
exec insertPublisher(publisher_name,city, phone_no );
/*
*A SELECT STATEMENT.TO SELECT ALL THE RECORDS IN PUBLISHER DETAILS TABLE
*/
SELECT * FROM insertPublsiher;
TASK 5- FORMS
CREATE TABLE `PrintJob`(
`id` NUMBER(5) NOT NULL AUTO_INCREMENT UNIQUE,
`jobNo` NUMBER(6) NOT NULL PRIMARY KEY,
`startDate` TIMESTAMP,
`completionDate` TIMESTAMP,
`printingCost` NUMBER(9,2),
`publisherName` VARCHAR2(50) NOT NULL,
FOREIGN KEY (publisherName) REFERENCES Publisher(name) ON DELETE CASCADE
);
CREATE TABLE `PurchaseOrder`(
`id` NUMBER(10) NOT NULL AUTO_INCREMENT UNIQUE,
`orderNo` NUMBER(10) NOT NULL PRIMARY KEY,
`orderDate` TIMESTAMP,
`jobNo` NUMBER(10),
FOREIGN KEY (jobNo) REFERENCES PrintJob(jobNo) ON DELETE CASCADE
);
TASK 6 – CRITICAL APPRAISAL