Question 1

What are the main advantages and disadvantages of SQL?

Accepted Answer

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

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.

Accepted Answer

A sequence is a database object that is used to create and maintain a sequence of numbers for tables.
 CREATE SEQUENCE StaffSeq
START WITH 10
 INCREMENT BY 10
MAXVALUE 10000
CYCLE;

Question 3

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.

Accepted Answer

(a)
(1)SELECT pubName 
FROM Publisher 
ORDER BY pubName;

(2)SELECT jobID 
 FROM BookJob b, Publisher p 
 WHERE b.pubID = p.pubID AND pubName = 'Gold Press';

(3) SELECT pubName, telNo 
 FROM BookJob b, Publisher p 
 WHERE b.pubID = p.pubID AND jobType = 'R';

(4)SELECT poID, poDate 
 FROM PurchaseOrder po, BookJob b, Publisher p 
 WHERE po.jobID = b.jobID AND b.pubID = p.pubID AND 
 pubName = 'Gold Press';

(5)SELECT creditCode, COUNT(*) 
 FROM Publisher 
 GROUP BY creditCode;

(6)SELECT jobType, COUNT(*) 
 FROM BookJob
 GROUP BY jobType
 HAVING COUNT(*) >= 3;

(7) SELECT AVG(price) 
 FROM Item;

(8)SELECT * 
 FROM Item
 WHERE price < (SELECT AVG(price) FROM Item);

(b) CREATE VIEW PB (pubID, pubName, street, city, postcode, telNo)
 AS SELECT p.pubID, pubName, street, city, postcode, telNo
 FROM BookJob b, Publisher p 
 WHERE b.pubID = p.pubID AND jobType = 'R';

Question 4

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.

Accepted Answer

The answer of Consider the following relational schema:
 
 Formulate...

Question 5

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.

Accepted Answer

The answer of Assuming that a single-row form is created...

Question 6

(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.

Accepted Answer

The answer of (a) In PL/SQL what is a Cursor?...

Question 7

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.

Accepted Answer

The answer of The relational schema shown below is part...

Question 8

(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:

Accepted Answer

The answer of (a)SQL*Plus environment variables are set to default...

Question 9

(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.

Accepted Answer

The answer of (a) What is a 'table' and what...

Question 10

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.

Accepted Answer

The answer of Oracle database consists of logical and physical...

Question 11

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.

Accepted Answer

The answer of A relational database contains details about journeys...

Question 12

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.

Accepted Answer

The answer of Write SQL*Plus commands to do the following:...

Question 13

(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)

Accepted Answer

The answer of (a) What is a SQL*Plus script? Why...

Question 14

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.

Accepted Answer

The answer of The following tables form part of a...

Question 15

State the three primary uses of Oracle Data Dictionary.

Accepted Answer

The answer of State the three primary uses of Oracle...

Question 16

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.

Accepted Answer

The answer of The following tables form part of a...

Question 17

(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.

Accepted Answer

The answer of (a) What is a named block in...

Question 18

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.

Accepted Answer

The answer of Assume that the following table is created...

Exam 3: SQL: Data Manipulation and Advanced SQL

What are the main advantages and disadvantages of SQL?

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.

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.

State the three primary uses of Oracle Data Dictionary.