Exam 3: SQL: Data Manipulation and Advanced SQL

Correct Answer:

Verified

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.

Correct Answer:

Verified

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;

Correct Answer:

Verified

(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';