INF60009 Test 2 – Practice Questions
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
INF60009 Test 2 – Practice Questions
Test 2 – Sample Questions
The test questions will be a combination of
Fill in the blank
Multiple Choice
Combine the code segments (see Q21)
The questions cover all weeks 1-7 but primarily concentrating on weeks 5 to 7
(but no SQL aggregate functions / group by / having).
These questions are typical of those that may be found in the test.
1. Consider the following ER Diagram:.
a) What is the identifier of the Entity named A?
Answer: ______________________________
b) What is the identifier of the Entity named B?
Answer: ______________________________
c) What is the FK (if any) in any of the following Entities?
Entity A: ______________________________
Entity B: ______________________________
d) Write the relational schema based on this ERD. Indicate all PKs and FKs
2. Consider the following ER Diagram:.
a) What is the identifier of the Entity named E?
Answer: ______________________________
b) What is the identifier of the Entity named F?
Answer: ______________________________
c) What is the FK (if any) of the following Entities?
Entity E: ______________________________
Entity F: ______________________________
d) Write the relational schema based on this ERD. Indicate all PKs and FKs
3. Consider the following ER Diagram:.
a) What is the identifier of the Entity named X? Answer: ______________________________
b) What is the identifier of the Entity named Y? Answer: ______________________________
c) What is the identifier of the Entity named Z? Answer: ______________________________
d) What is the FK (if any) of the following Entities?
Entity X: ______________________________
Entity Y: ______________________________
Entity Z: ______________________________
4. Consider the following ER Diagram:.
a) What is the identifier of the Entity named X?
Answer: ______________________________
b) What is the identifier of the Entity named Y?
Answer: ______________________________
c) What is the identifier of the Entity named Z?
Answer: ______________________________
d) What is the FK (if any) of the following Entities?
Entity X: ______________________________
Entity Y: ______________________________
Entity Z: ______________________________
e) Write the relational schema based on this ERD.
Indicate all PKs and FKs
5. Consider the following ER Diagram:.
When converted to a relational schema
a. How many Foreign Keys are there in total?
Answer (circle one) 0 1 2 3 4 5
b. Which relation has the most Foreign Keys?
Answer (circle one) Place Person Grouping
6. Consider the following ER Diagram:
When converted to a relational schema
a. How many tables will have no Foreign Keys?
Answer (circle one) 0 1 2 3 4
b. Which table will have the most attributes?
Answer (circle one) Department Function Employee Station
7. Consider the following ER Diagram:.
a) What is the identifier of the Entity named A?
Answer: ______________________________
b) What is the identifier of the Entity named B?
Answer: ______________________________
c) What is the identifier of the Entity named C?
Answer: ______________________________
d) What is the FK (if any) of the following Entities?
Entity A: ______________________________
Entity B: ______________________________
Entity C: ______________________________
8. Consider the following ER Diagram:.
a) What is the identifier of the Entity named D?
Answer: ______________________________
b) What is the identifier of the Entity named E?
Answer: ______________________________
c) What is the identifier of the Entity named F?
Answer: ______________________________
d) What is the FK (if any) of the following Entities?
Entity D: ______________________________
Entity E: ______________________________
Entity F: ______________________________
9. Consider the following ER Diagram:.
a) What is the identifier of the Entity named X?
Answer: ______________________________
b) What is the identifier of the Entity named Y?
Answer: ______________________________
c) What is the identifier of the Entity named Z?
Answer: ______________________________
d) What is the FK (if any) of the following Entities?
Entity X: ______________________________
Entity Y: ______________________________
Entity Z: ______________________________
10. Consider the following ER Diagram:
a) What is the identifier of the Entity named K? Answer: ______________________________
b) What is the identifier of the Entity named J?
Answer: ______________________________
c) What is the identifier of the Entity named H?
Answer: ______________________________
d) What is the identifier of the Entity named G?
Answer: ______________________________
e) What is the FK (if any) of the following Entities?
Entity G: ______________________________
Entity H: ______________________________
Entity J: ______________________________
Entity K: ______________________________
Consider the following tables and data:
GRADE (gradeID, description)
Primary Key (gradeID)
TYPE (TypeCode, title, gradeID, catid)
Primary Key (TypeCode)
Foreign Key (gradeID) references GRADE
CUSTOMER (custno, custname, typecode, sales, location)
11. How many rows are returned by the following SQL statements?
a. Select * from customer where location = 'Kew';
b. Select * from customer where location = 'HAW';
c. Select * from customer where location = 'Haw';
d. Select * from customer where Upper(location) = 'Haw';
e. Select * from customer where Lower(location) = 'haw';
f. Select * from customer where location = 'Kew' AND sales < 100;
g. Select * from customer where location = 'Kew';
h. Select * from customer where location = 'HAW';
i. Select * from customer where location = 'Haw';
j. Select * from customer where location = 'Kew' OR sales < 200;
k. Select * from customer where sales < 100 OR sales > 400 AND typecode > 1;
l. Select * from customer where (sales < 100 OR sales > 400) AND typecode > 1;
m. Select * from customer where typecode = 1 OR typecode = 3;
n. Select * from customer where typecode = 1 OR typecode = 3 AND sales > 500
o. Select * from customer where sales >= 0 OR sales <= 500 0 AND location = 'Haw'
p. Select * from Customer where UPPER(custname) like '%d%' or LOWER(custname) like '%N%'
q. Select c.custname, t.title from customer c
inner join type t
on c.typecode = t.typecode;
r. Select c.custname, t.title from customer c
inner join type t
on c.typecode = t.typecode
where sales < 100 ;
s. Select c.custname, t.title from customer c
inner join type t
on c.typecode = t.typecode
where sales > 100 OR sales < 400
and t.typecode = 1;
t. Select c.custname, t.title, g.description from customer c
inner join type t
on c.typecode = t.typecode
inner join grade g
on t.gradeid = g.gradeid;
12. Which name is listed 2nd when this SQL statement is executed? SELECT custname FROM CUSTOMER
WHERE typecode >1
ORDER BY custname
13. Which name is listed last when this SQL statement is executed? SELECT *
FROM CUSTOMER
ORDER BY typecode, location
14. Write a single SQL statement to list all customer names and sales amounts in descending sales amount sequence
15. Write a single SQL statement to list all
customer numbers, customer names and
locations in ascending location sequence.
If two or more rows have the same location,
list those rows in ascending customer name
sequence
16. Write a single SQL statement to list the names of customers who have a sales value in one the following ranges:
a. 25- 100 inclusive
b. 500-900 inclusive
17. Write a single SQL statement to list the names of customers have the letter N in their name. Allow for both upper case and lower case characters.
18. Write a single SQL statement to list the customer name of any customer that has a null sales amount value.
19. Write a single SQL statement to list each customer name the title that he/she has
and
20. Write a single SQL statement to list each
customer name only if they have a title
that contains the word 'Under'
You may use the SQL code below to create
the tables and populate them with data so that you can check your answers to the above questions.
21. Complete this SQL statement from the parts below that lists the customer name and type title for every row in the customer table.
SELECT C.CUSTNAME, T.TITLE
FROM CUSTOMER C
INNERJOIN TYPE T
a. ON C.CUSTNAME = T.TITLE
b. ON T.CUSTNAME = T.TITLE
c. ON T.CUSTNAME = C.TITLE
d. ON C.TYPECODE = T.TYPECODE
e. ON T.TYPECODE = T.TYPECODE
f. ON C.TYPECODE = C.TYPECODE
g. ON C.CUSTNO = T.TYPECODE
h. ON C.CUSTNO = C.TYPECODE
i. ON T.CUSTNO = T.TYPECODE
22. How many rows will be selected by this SQL statement?
SELECT T.TITLEE, G.DESCRIPTION
FROM TYPE T
INNERJOIN GRADE G
ON T.GRADEID = S.GRADEID
ANSWER 0 1 2 3 4 5 6 7 8 9 10
SQL DDL statements and SQL DML statements
Drop table customer;
Drop table type;
Drop table grade;
create table GRADE (
gradeID varchar(2),
description varchar(20),
Primary Key (gradeID) );
create table TYPE (
TypeCode number,
Title varchar2(20),
gradeID varchar(2),
category varchar(1),
Primary Key (TypeCode),
Foreign Key (gradeID) references GRADE );
create table customer(
custno number,
custname varchar(20),
typecode number,
sales number(7,2),
location varchar(20),
Primary Key (custno),
Foreign Key (typecode) references TYPE );
Insert into grade (gradeID,description) values ('WC','Women and Children');
Insert into grade (gradeID,description) values ('M','Men');
Insert into TYPE (TypeCode,Title,gradeID,category) values (1,'Under 20','WC','X');
Insert into TYPE (TypeCode,Title,gradeID,category) values (2,'Under 20','M','Z');
Insert into TYPE (TypeCode,Title,gradeID,category) values (3,'20 plus','WC','Z');
Insert into TYPE (TypeCode,Title,gradeID,category) values (4,'20 plus','M','X');
Insert into TYPE (TypeCode,Title,gradeID,category) values (5,'Special','M','S');
Insert into customer (custno, custname, TypeCode,sales,location) values (21,'Wendy',1,300,'Haw'); Insert into customer (custno, custname, TypeCode,sales,location) values (24,'Sue',3,700,'Haw'); Insert into customer (custno, custname, TypeCode,sales,location) values (27,'Nick',2,NULL,'Kew'); Insert into customer (custno, custname, TypeCode,sales,location) values (22,'Dave',4,95,'Richmond'); Insert into customer (custno, custname, TypeCode,sales,location) values (25,'Jenny',1,525,'Haw'); Insert into customer (custno, custname, TypeCode,sales,location) values (29,'Bob',4,15,'Kew'); Insert into customer (custno, custname, TypeCode,sales,location) values (23,'Helen',3,200,'Haw'); Insert into customer (custno, custname, TypeCode,sales,location) values (28,'Linda',1,25,'Kew');
Insert into customer (custno, custname, TypeCode,sales,location) values (26,'Xena',3,Null, 'Richmond');
2022-05-06