A SQL developer is bidding for a new contract with a prestigious blue-chip organisation. Part of the selection process is a technical interview. Answer the following questions from the interview panel:

a) For each of the following terms, explain what the term stands for, the essence of the functions it provides and a set of example SQL statements (at least TWO for each) that implement these functions:

(i)DDL.      (5 Marks)

(ii)DML.    (5 Marks)

(iii)DCL.      (5 Marks)

Question 2

Refer to the following Table definitions and then answer the question parts that follow.

Custmer

Column Name       Type               length         Nulls    Key

CustID                      interger              5                No      PK

CustName                varchar             25               No     

Custaddress            vachar              25                No

CustCode                Char                  8                 No

Products

Column Name       Type               Length            Nulls    Key

ProductID              interger           4                      No       PK

Description           VarChar          25                     No    

StorageAddress    VarChar         25                  

StoragePostCode    VarChar        12

Recorderquantity         interger      4                   No

QuantityInStock        interger        4                    No

Unitprice                    decimal         8,3                No

Total_quantitySold       interger      4                   Yes

Orders

Column Name       Type               Length            Nulls    Key

OrderID                   interger            4                    No       PK

CustID                   interger              4                    No

OrderDate            Date                                          No

PromiseDate        Date                                          No

LineItems

Column Name       Type               Length            Nulls    Key

ProductID               interger           4                       No     PK,FK

OrderNo                interger           4                      No       PK,Fk

QuantityOrdered    interger          4                     No        

a) With reference to the above table definitions explain the concept of a Domain. List 2 examples from the above tables of columns that could be defined as a domain.

(5 marks)

b) Suppose you were required to implement (create and populate) the tables listed above using an SQL script containing a set of CREATE TABLE and INSERT

operations. List the logical sequence the tables should be populated to ensure

referential integrity is preserved. (4 marks)

c) TotalQtySold is a column that contains derived data. Write a SQL SELECT statement that computes a value for this column for a specific product. (5 marks)

d) What are the advantages and disadvantages of storing derivable data in a table?

(4 marks)

e) Write an SQL UPDATE statement that updates the column TotalQtySold given a

specific product. (7 marks)