Consider the following sql table bills that stores

This preview shows page 5 - 9 out of 25 pages.

5. Consider the following SQL table Bills that stores some customers’ bills, and currently has the following contents: name Bill A1 1000 A2 NULL A3 800 A4 700 Which customers’ names will appear in the result of the following query: SELECT name FROM Bills WHERE (bill > (SELECT AVG(Bill) FROM Bills)); (a) None of the customers (b) A1 only (c) A1 and A3 only (d) A1, A2, and A3 only (e) All of the customers
6. Which SQL keyword has the same effect as the Projection operator π of relational algebra.
SEE OVER
Semester 2 ***Sample*** Examinations XXX 2016 6. CITS1402/4405 7. Consider the relations R ( A, B ) and S ( B, C ) which currently have the following data A B 1 2 1 3 3 2 B C 2 5 2 6 7 8 How many rows will be returned from the following MySQL query? SELECT * FROM R FULL OUTER JOIN S ON R.B=S.B;
8. Suppose that a SQL table Data(A,B) contains 2 tuples (not necessarily distinct) and that none of the entries are NULL . If the following query is made SELECT D1.A, D1.B, D2.A, D2.B FROM Data D1, Data D2 WHERE D1.A = D2.A and D1.B = D2.B then how many tuples will be in the result? (Choose the most precise answer)
SEE OVER
Semester 2 ***Sample*** Examinations XXX 2016 7. CITS1402/4405 9. Consider a database used by a university that contains three relations: Create Table Student (id int Primary Key, name Varchar(32)); Create Table Unit(code Varchar(10) Primary Key, name Varchar(32)); Create Table Enrolled(sid int, ucode Varchar(10), mark int, Foreign Key (sid) References Student (id), Foreign Key (ucode) References Unit (code)); and a view is defined as CREATE VIEW AcademicRecord AS SELECT S.name as S_Name, U.name as U_Name, mark FROM Student S JOIN Unit U JOIN Enrolled E On S.id=E.sid and U.code=E.ucode; If the tables are initially empty, then which of the following sequences of state- ments creates a new tuple accessible by using the view? I. INSERT INTO Student VALUES(101, ’Rob’); INSERT INTO Unit VALUES(’202’, ’Java’); INSERT INTO Enrolled VALUES(101,’201’,80); II. INSERT INTO Student VALUES(101, NULL); INSERT INTO Unit VALUES(’201’, NULL); INSERT INTO Enrolled VALUES(101,’201’,80); III. INSERT INTO Student VALUES(101, ’Rob’); INSERT INTO Unit VALUES(’201’, Java); INSERT INTO Enrolled VALUES(101,’201’,NULL); (a) None of them (b) Just III
QUESTION 9 CONTINUES OVER THE PAGE
Semester 2 ***Sample*** Examinations XXX 2016 9 (Continued) 8. CITS1402/4405

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture