Question No. 1

Consider the relations shown in the exhibit. Due to restructuring, the sales department has

been eliminated and the employees working in that department have been dismissed. All ID

information is stored as integers. Which SQL statement would be used to return a relation with all

information for the employees who have been dismissed?

ASELECT *
FROM Employee;

BSELECT ID, Last_Name
FROM Employee;
WHERE ID = 0004;

CSELECT *
FROM Employee
WHERE Dept_ID = 022;

DSELECT *
FROM Employee
WHERE Dept_ID = 022;

Show Answer

Correct Answer: C

Question No. 2

A foreign key maps to a:

Aprime key.

Bindirect key.

Cparent key.

Dcomposite key.

Show Answer

Correct Answer: C

Question No. 3

Which mechanism provides database users with controlled access to the database through the

use of virtual tables?

AView

BData dictionary

CDatabase control language

DDatabase management system

Show Answer

Correct Answer: A

Question No. 4

Consider the following relational algebraic expression as well as the Dept1_Parts and

Dept2_Parts relations shown in the exhibit,

Which of the following relations would result from the given relational algebraic expression?

AOption A

BOption B

COption C

DOption D

Show Answer

Correct Answer: C

Question No. 5

Consider the Information Engineering diagram shown in the exhibit for a building

management company. Referential integrity must be maintained such that a building cannot be

deleted when it has residents. Building_ID, R_ID, Room_Count and Room_Num are integer

numbers, whereas Bldg_Name, Location and Res_Name are all represented by variable-length

strings with a maximum of 20 characters. Which SQL statement best implements the relations

shown in this diagram?

ACREATE TABLE BUILDING (
Building_ID INTEGER NOT NULL PRIMARY KEY,
Bldg_Name VARCHAR (20),
Location VARCHAR (20),
Room_Count INTEGER );
CREATE TABLE RESIDENT (
R_ID NOT NULL PRIMARY KEY,
Room_Num INTEGER,
Res_Name VARCHAR (20),
Building_ID INTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES RESIDENT (Building_ID)
ON DELETE NO CHECK);

BCREATE TABLE BUILDING (
Building_ID INTEGER NOT NULL PRIMARY KEY,
Bldg_Name VARCHAR (20),
Location VARCHAR (20),
Room_Count INTEGER );
CREATE TABLE RESIDENT (
R_ID NOT NULL PRIMARY KEY,
Room_Num INTEGER,
Res_Name VARCHAR (20),
Building_ID INTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE);

CCREATE TABLE BUILDING (
Building_ID INTEGER NOT NULL PRIMARY KEY,
Bldg_Name VARCHAR (20),
Location VARCHAR (20),
Room_Count INTEGER );
CREATE TABLE RESIDENT (
R_ID NOT NULL PRIMARY KEY,
Room_Num INTEGER,
Res_Name VARCHAR (20),
Building_ID INTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE);

DCREATE TABLE BUILDING (
Building_ID INTEGER NOT NULL PRIMARY KEY,
Bldg_Name VARCHAR (20),
Location VARCHAR (20),
Room_Count INTEGER );
CREATE TABLE RESIDENT (
R_ID NOT NULL PRIMARY KEY,
Room_Num INTEGER,
Res_Name VARCHAR (20),
Building_ID INTEGER NOT NULL,
FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE);

Show Answer

Correct Answer: C

Free CIW 1D0-541 Exam Actual Questions

The questions for 1D0-541 were last updated On Nov 4, 2024