Deck 3: SQL: Data Manipulation and Advanced SQL

Question 1

What are the main advantages and disadvantages of SQL?

Answer 1

Advantages
Satisfies ideals for database language
(Relatively) Easy to learn
Portability
SQL standard exists
Both interactive and embedded access
can be used by specialist and non-specialist.
Disadvantages
Impedance mismatch - mixing programming paradigms with embedded access
Lack of orthogonality - many different ways to express some queries
Language is becoming enormous (SQL2 is 6 times larger than predecessor; SQL3 is even larger again)
Handling of nulls in aggregate functions
Result tables are not strictly relational - can contain duplicate tuples, imposes an ordering on both columns and rows.

Question 2

Consider the following relational schema:

Formulate the following queries using SQL:
(a)
(1) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of description.
(2) List all staff with the skill description 'Programmer' who work in the 'Special Projects' department.
(3) For all projects that were active in July 1995, list the staff name, project number and the date and number of hours worked on the project, ordered by staff name, within staff name by the project number and within project number by date.
(4) How many staff have the skill 'Programmer'?
(5) List all projects that have at least two staff booking to it.
(6) List the average charge out rate.
(7) List all staff with a charge out rate greater than the average charge out rate.
(b) Create a view of staff details giving the staff number, staff name, skill description, and department, but excluding the skill number and charge out rate.

Answer 2

(a)
(1)SELECT *
FROM Skill
WHERE chargeOutRate > 60
ORDER BY description;

(2)SELECT *
FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode AND
description = 'Programmer' AND dept = 'Special Projects';

(3)SELECT name, p.projectNo, dateWorkedOn, timeWorkedOn
FROM Staff s, Project p, Booking b
WHERE s.staffNo = b.staffNo AND
b.projectNo = p.projectNo AND
endDate >= DATE '1995-06-01'
ORDER BY name, p.projectNo, dateWorkedOn;

(4)SELECT COUNT(*)
FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode AND
description = 'Programmer';

(5)SELECT projectNo, COUNT(*)
FROM Booking
GROUP BY projectNo
HAVING COUNT(*) >= 2;

(6)SELECT AVG(chargeOutRate)
FROM Skill;

(7)SELECT s.* FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode AND
chargeOutRate > (SELECT AVG(chargeOutRate)
FROM Skill);

(b)CREATE VIEW SD (staffNo, name, dept, description)
AS SELECT staffNo, name, dept, description
FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode;

Question 3

The following tables form part of a database held in a Relational Database Management System:

(a) (1) List all employees in alphabetical order of surname and within surname, first name.
(2) List all the details of employees who are female.
(3) List the names and addresses of all employees who are Managers.
(4) Produce a list of the names and addresses of all employees who work for the 'IT' department.
(5) Produce a complete list of all managers who are due to retire this year, in alphabetical order of surname.
(6) Find out how many employees are managed by 'James Adams'.
(7) Produce a report of the total hours worked by each employee, arranged in order of department number and within department, alphabetically by employee surname.
(8) For each project on which more than two employees worked, list the project number, project name and the number of employees who work on that project.
(9) List the total number of employees in each department for those departments with more than 10 employees. Create an appropriate heading for the columns of the results table.
(b) Create a view of employee details for all employees who work on project 'MIS Development', excluding department number.

Answer 3

(a)
(1)SELECT *
FROM Employee
ORDER BY lName, fName;

(2)SELECT *
FROM Employee
WHERE sex = 'F';

(3)SELECT fName, lName, address
FROM Employee
WHERE position = 'Manager';
Or
SELECT fName, lName, address
FROM Employee e, Department d
WHERE e.empID = d.mgrEmpID;

(4)SELECT e.lName, e.address
FROM Employee e, Department d
WHERE e.deptNo = d.deptNo AND d.deptName = 'IT';

(5)SELECT lName
FROM Employee e, Department d
WHERE e.empID = d.mgrEmpID AND
date_part('year',DOB) < date_part('year', DATE('2001-10-01') - 65;
(student does not need to know exact date functions - just general idea)

(6)SELECT COUNT(*)
FROM Employees e1,e2, Department d
WHERE e1.lName = 'Adams' AND e1.fName = 'James' AND
e1.empID = d.mgrEmpID AND d.deptNo = e2.deptNo;

(7)SELECT e.empID, e.lName, e.fName, e.deptNo,
SUM(w.hoursWorked)
FROM Employee e, Project p, WorksOn w
WHERE e.deptNo = p.deptNo AND e.empID = w.empID
ORDER BY e.deptNo, e.lName;

(8)SELECT e.projNo, e.projName, COUNT(*)
FROM Project p, WorksOn w
WHERE p.projNo = w. projNo
GROUP BY e.projNo, e.projName
HAVING COUNT(*) > 2;

(9)SELECT deptNo AS departmentNumber, COUNT(empID) AS totalEmployees
FROM Employee
GROUP BY deptNo, empID
HAVING COUNT(empID) > 10

(b)CREATE VIEW ED(eid, fName, lName, address, DOB)
AS SELECT empID, fName, lName, address, DOB
FROM Employee e, Project p
WHERE e.deptNo = p.deptNo AND p.projName = 'MIS Development';

Question 4

The following tables form part of a database held in a Relational Database Management System for a printing company that handles printing jobs for book publishers:
Formulate the following queries using SQL:
(a)
(1) List all publishers in alphabetical order of name.
(2) List all printing jobs for the publisher 'Gold Press'.
(3) List the names and phone numbers of all publisher who have a rush job (jobType = 'R').
(4) List the dates of all the purchase orders for the publisher 'Gold Press'.
(5) How many publisher fall into each credit code category?
(6) List all job type's with at least three printing jobs.
(7) List the average price of all items.
(8) List all items with a price below the average price of an item.
(b) Create a view of publisher details for all publisher who have a rush printing job, excluding their credit code.

Question 5

The relational schema shown below is part of a hospital database. The primary keys are highlighted in bold.
Patient (patientNo, patName, patAddr, DOB)
Ward (wardNo, wardName, wardType, noOfBeds)
Contains (patientNo, wardNo, admissionDate)
Drug (drugNo, drugName, costPerUnit)
Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)
Formulate the following SQL statements:
(1) List all the patients' details, alphabetically by name.
(2) List all the patients contained in the 'Surgical' ward.
(3) List all the patients admitted today.
(4) Find the names of all the patients being prescribed 'Morphine'.
(5) What is the total cost of Morphine supplied to a patient called 'John Smith' ?
(6) What is the maximum, minimum and average number of beds in a ward? Create appropriate column headings for the results table.
(7) For each ward that admitted more than 10 patients today, list the ward number, ward type and number of beds in each ward.
(8) List the numbers and names of all patients and the drugNo and number of units of their medication. The list should also include the details of patients that are not prescribed medication.

Question 6

A relational database contains details about journeys from Paisley to a variety of destinations and contains the following relations:
Operator (opCode, opName)
Journey (opCode, destinationCode, price)
Destination (destinationCode, destinationName, distance)
Each operator is assigned a unique code (opCode) and the relation operator records the association between this code and the operator's name (opName). Each destination has a unique code (destinationCode) and the relation destination records the association between this code and the destination name (destinationName), and the distance of the destination from Paisley. The relation Journey records the price of an adult fare from Paisley to the given destination by as specified operator, several operators may operate over the same route.
Formulate the following queries using SQL (the answers to these queries in relational algebra, tuple relational calculus, and domain relational calculus were given in the previous section):
(a) List the details of journeys less than £100.
(b) List the names of all destinations.
(c) Find the names of all destinations within 20 miles.
(d) List the names of all operators with at least one journey priced at under £5.
(e) List the names of all operators and prices of journeys to 'Ayr'.
(f) List the names of all destinations that do not have any operators.

Question 7

The following tables form part of a database held in a Relational Database Management System:

Formulate the following queries in SQL (the answers to these queries in relational algebra, tuple relational calculus, and domain relational calculus were given in the previous section).
(1) List all Boeing aircraft.
(2) List all Boeing 737 aircraft.
(3) List the employee numbers of pilots certified for Boeing aircraft.
(4) List the names of pilots certified for Boeing aircraft.
(5) List the aircraft that can fly nonstop from Glasgow to New York (flyingRange > flightDistance).
(6) List the employee numbers of employees who have the highest salary.
(7) List the employee numbers of employees who have the second highest salary.
(8) List the employee numbers of employees who are certified for exactly three aircraft.

Question 8

Write SQL*Plus commands to do the following:
• Connect to the database 'OracleDB' with the user name 'SalesTracking' and password 'sales';
• Make sure that the Autocommit is off;
• Display the name of the current user;
• List the names of all tables in this schema;
• Display the structure of the table Branch;
• List the names of all sequences in this schema.

Question 9

Assume that the following table is created and stored in your database:
Staff (staffNo, name, post, salary, sex, DOB)
Write SQL statements to do the following:
(a) Increment the salary of managers by 5%;
(b) Remove the records of all 'salesmen' from the Staff table;
(c) List the Staff table tablespace name, pctfree, and pctused.

Question 10

What is a sequence? Write an SQL statement to create a sequence that starts from 10 and is incremented by 10 up to a maximum value of 10000. The sequence should continue to generate values after reaching its maximum value.

Question 11

(a) What is a 'table' and what is a 'tablespace' in Oracle9i? Write an SQL statement to create a tablespace, say MyTableSpace, with default storage. You can use a datafile called 'MyDataFile.dbf' for this tablespace. (b) Explain what is a DUAL table, where is it stored, and what is it useful for? Give an example of its use.

Question 12

(a) What is a named block in PL/SQL and how many types does PL/SQL support?
(b) Specify the general structure of an anonymous block.
(c)Assume that the following tables are part of a Company database schema
Customer( $\underline{custNo}$ , custName, address, sex, DOB, creditLimit)
HighCredit( $\underline{hcCustNo}$ , hcCustName, hcCreditLimit)
Create a PL/SQL procedure object in your schema. Using a Cursor the procedure code should be able to retrieve records of all customers and check their credit limit. If a customer credit limit is greater than 100000 the procedure should insert a record in the table HighCredits.
(d) Invoke the procedure from SQL*Plus.

Question 13

Assuming that a single-row form is created on the following Country table:
Country( $\underline{\text{{countryId}}}$ , countryName, currencyName, continent, population)
State the steps you would take to execute the following query using the form: List all the African countries whose currency name is the pound and population greater than 20 millions. Order the result by country name.

Question 14

Oracle database consists of logical and physical database structures. Describe each of the following concepts and state to which structure they belong:
(a) Schema;
(b) Data block;
(c) Redo log file.

Question 15

State the three primary uses of Oracle Data Dictionary.

Question 16

(a)SQL*Plus environment variables are set to default values when SQL*Plus is started. State three ways by which users can change the default setting.
(b)Write SQL*Plus commands to do the following:
• List all SQL*Plus commands;
• Connect to the database 'OracleDB' with the user name 'CustomerOrders' and password 'customer';
• Use the COLUMN command to set the output format of the field 'StaffName' to 30 characters.
(c) Write SQL statements to do the following:
(1) List the name and granted roles of the current user;
(2) List name and type of all objects owned by the current user;
(3) List table name and the tablespace name to which the table is assigned of all tables owned by the current user;
(4) List the next value of the sequence Emp_Seq.
(d)Given the following schema of a database table:
Orders( $\underline{\text{{orderNo}}}$ , dateDue, totalValue, status, custNo)
where: custNo is the number of the customer who placed the order.
Write a script with the necessary formatting commands (e.g. COLUMN, BREAK, TTITLE, etc.) and an SQL statement to create a report that lists each customer number, each order number he/she placed, the total value of each order, the sum of the total values of all the orders for each customer, and the grand total of all the orders placed by all customers under the following heading:

Question 17

(a) In PL/SQL what is a Cursor? When do we use an explicit Cursor? What do you do when you declare a Cursor?
(b) Specify the general structure of a named function block.
(c) Assume that the following tables are part of a Library database system
Employee(empNo, fName, lName, street, city, sex, salary, libName)
GlasgowEmployees(gEmpNo, fName, lName, sex, salary)
Create a PL/SQL procedure object in your schema. Using a Cursor the procedure code should be able to retrieve records of all employees and check in which city they live. If an employee lives in Glasgow the procedure should insert a record in the table GlasgowEmployees.
(d) Invoke the procedure from SQL*Plus.(e) Assume that a single-row form is created on the Member table and you are using it to enter data in the table. State the steps you would take to create a trigger that will fire and insert the next card number in the member record when the record is saved. Specify the type of trigger you will use and write the trigger code. Assume that a sequence generator already exists.

Question 18

(a) What is a SQL*Plus script? Why is it a good practice to create a log file while a SQL*Plus script is executed, and how can the log file be created? (b) What can a 'database trigger' be used for? Explain concisely what the code below does.
(c) Assume the following tables:
Order(orderNo, statusCode, customerNo,…)
Item(itemNo, orderNo, price, amount,…)
which capture, among others, the items that are ordered by customers. Explain what the following code is and explain how it works by writing comments against each line of the code:
FUNCTION total_sales (customerNo_in IN orders.customerNo%TYPE,
statusCode_in IN order.statusCode%TYPE:=NULL)