ERD & Normalisation & SQL

Question 1

One of the disadvantage of the traditional file-based system is:

Accepted Answer

Incompatible file formats

Answer

Unfixed queries

Answer

Easy to update and change structure

Answer

Ad hoc queries

Question 2

Which of the following is true in RDBMS

Accepted Answer

A & C

Answer

A) Data Definition Language is used to identify data types and structures

Answer

B) Data Definition Language is used to write query language (e.g. SQL)

Answer

C) Data Manipulation Language is used to write query language (e.g. SQL)

Question 3

Which of the following is true in RDBMS

Accepted Answer

An atomic place in a table is a cell

Answer

A table is a tuple

Answer

A row is a relation

Answer

A column is a attribute

Answer

A column is an entity

Question 4

As part of the relation properties:

Accepted Answer

Each attribute has a distinct name

Answer

The number of columns is called cardinality

Answer

The number of tuples is called degree

Answer

All of the above

Answer

None of the above

Question 5

What is the difference between a Superkey and Candidate key

Accepted Answer

A superkey is a combination of columns that uniquely identifies any row within RDBMS, while a candidate key can be any column or a combination of columns that can qualify as unique key in RDBMS

Answer

A candidate key is the superkey key that is selected to identify rows uniquely within the relation

Answer

A superkey is the candidate key that is selected to identify rows uniquely within the relation

Answer

A candidate is a combination of columns that uniquely identifies any row within RDBMS, while a superkey can be any column or a combination of columns that can qualify as unique key in RDBMS

Question 6

Which of the following is true in RDBMS:

Accepted Answer

A) There can be many superkeys. Some of the superkeys are candidate keys. One of the candidate keys is the primary key

Answer

B) A superkey is a candidate key such that no proper subset of it is a primary key within the relation

Answer

C) When we introduce a new artificial key to solve a duplicate foreign key problem, this is called a surrogate key

Answer

A & B are true

Question 7

Which of the following is true in RDBMS

Accepted Answer

All of the above is true

Answer

Relational integrity is a set of rules which ensures that the data is accurate

Answer

A foreign key is an attribute (or a set of attributes) that matches the candidate key of some relation

Answer

The purpose of the foreign key is to ensure referential integrity of the data

Question 8

What is the difference between an Entity and an Entity Type

Accepted Answer

An entity is something about which information needs to be recorded, while an entity type is a concept that is identified as having an independent existence

Answer

An entity type is something about which information needs to be recorded, while an Entity is a concept that is identified as having an independent existence

Answer

Both are true

Answer

Both are false

Question 9

Which of the following examples refer to a composite attribute

Accepted Answer

Address attribute is a composite one, as it contains number, street, town, zip code, and other components

Answer

Tel_No attribute is a composite one, as one may have more than one telephone number

Answer

Both are composite attributes

Answer

Both are not composite attributes

Question 10

Which of the following examples refer to a multivalued attribute

Accepted Answer

Tel_No attribute is a multivalued one, as one may have more than one telephone number

Answer

Address attribute is a multivalued one, as it contains number, street, town, zip code, and other components

Answer

Both are multivalued

Answer

Both are not multivalued

Question 11

What does the following two shapes indicate in an ER diagram

Accepted Answer

"Weak Entity" & "Relationship"

Answer

"Multivalued Attribute" & "Relationship"

Answer

"Derived Attribute" & "Relationship"

Answer

"Derived Entity" & "Relationship"

Question 12

What does the following two shapes indicate in an ER diagram

Accepted Answer

"Derived Attribute" & "Composite Attribute"

Answer

"Multivalued Attribute" & "Total Participation"

Answer

"Derived Attribute" & "Multivalued Attribute"

Answer

"Weak Entity" & "Multivalued Attribute"

Question 13

What does the following two shapes indicate in an ER diagram

Accepted Answer

"Multivalued Attribute" & "Total Participation"

Answer

"Multivalued Attribute" & "Weak Entity"

Answer

"Weak Entity" & "Multivalued Attribute"

Answer

"Derived Attribute" & "Total Participation"

Question 14

What is the original cardinality ratio of the following relationship between Student & Module

Accepted Answer

Many to Many (M:N)

Answer

One to Many (1:M)

Answer

One to One (1:1)

Answer

Many to One (M:1)

Question 15

Which of the following statements is true for he below ER diagram

Accepted Answer

Code is a primary key, and BANK BRANCH is a weak entity with a total participation with BANK

Answer

BranchNo is a primary key, and BRANCHES is a weak entity with a partial participation with BANK

Answer

BranchNo is a foreign key, and BANK is a derived entity with a total participation with BANK BRANCH

Answer

BranchNo is a primary key and BANK BRANCH is a multivalued entity with a partial participation

Question 16

Which of the following languages is responsible for granting and revoking users permission on the Database

Accepted Answer

Data Control Langauge

Answer

Data Definition Language

Answer

Data Manipulation Language

Answer

Data Policy Langauge

Answer

None of the above

Question 17

Which of the following is true to create a table called staff, with the following fields: 
StaffNo as an Integer, Salary as a Float, Lname as a text field with 20 characters

Accepted Answer

CREATE TABLE Staff (StaffNo INTEGER, Salary FLOAT, Lname VARCHAR(20));

Answer

CREATE Staff (StaffNo, Salary, Lname, AS INTEGER, FLOAT, STRING (20));

Answer

CREATE Staff (StaffNo INTEGER, Salary FLOAT, Lname CHAR(20));

Answer

CREATE TABLE Staff (StaffNo INTEGER, Salary FLOAT, Lname CHAR(20));

Answer

None of the above

Question 18

Which of the following is true to insert into table called staff a row with the following fields:
StaffNo of 32, Salary of 25000.0, Last name of Smith

Accepted Answer

INSERT INTO Staff VALUES (32, 25000.0, 'Smith');

Answer

INSERT INTO Staff (32, 25000.0, 'Smith');

Answer

INSERT INTO Staff ("32","25000.0","Smith");

Answer

INSERT INTO STAFF VALUES ("32, 25000.0, Smith");

Answer

None of the above

Question 19

Look at the table given. Choose the correct answer which display the first name, last name, and city for everyone that's not from Payson

Accepted Answer

C) SELECT first, last, city FROM empinfo WHERE city <> 'Payson';

Answer

A) SELECT first, last, city FROM empinfo WHERE city != 'Payson';

Answer

B) SELECT first, last, city FROM empinfo WHERE city == "payson";

Answer

D) SELECT first, last, city FROM empinfo WHERE State = 'Arizona';

Answer

A & D

Question 20

Look at the given table. Choose the correct answer which displays the first and last names for everyone whose last name ends in an "ay", and are over 25 years of age

Accepted Answer

SELECT first, last FROM empinfo WHERE (last LIKE '%ay') AND (age > 25);

Answer

SELECT first, last FROM empinfo WHERE (last NOT LIKE "%ay") AND (age >= 25);

Answer

SELECT first, last FROM empinfo WHERE (last IN '%ay%') AND (age > 25);

Answer

SELECT first, last FROM empinfo WHERE (last IS LIKE '%ay%') OR (age > 25);

Answer

None of the above

Question 21

Look at the table given. Choose the correct answer which display all columns for everyone whose first name contains "Mary" or last name equals "Jones"

Accepted Answer

SELECT * FROM empinfo WHERE (first LIKE '%Mary%') OR (last = 'Jones');

Answer

SELECT * FROM empinfo WHERE (first IS LIKE '_Mary_') OR (last = 'Jones');

Answer

SELECT * FROM empinfo WHERE (first IS LIKE "_Mary_") OR (last == "Jones");

Answer

SELECT * FROM empinfo WHERE (first LIKE '_Mary_') OR (last == "Jones");

Answer

SELECT * FROM empinfo WHERE (first LIKE %Mary%) OR (last = "Jones");

Question 22

Look at the tale given to use a computed field on the column Salary, which of the following SQL statements are not correct:

Accepted Answer

B) SELECT Fname, Lname, [Salary/12] AS [MonthlySalary] FROM Staff

Answer

A) SELECT Fname, Lname, Salary/12 AS MonthlySalary FROM Staff;

Answer

C) SELECT Salary/12 AS MSalary FROM Staff;

Answer

D) SELECT Fname, Salary/12 as MonthlySalary FROM Staff;

Answer

B & D

Question 23

Look at the given table. In order to show the first name and second name together with a space separating them, under one column called FullName, which of the following is correct;

Accepted Answer

SELECT concat(Fname,' ',Lname) AS FullName FROM Staff;

Answer

SELECT conc(Fname, ,Lname) AS FullName FROM Staff;

Answer

SELECT concatenate(Fname," ", Lname) AS FullName FROM Staff;

Answer

SELECT concat("Fname, ,Lname") AS FullName FROM Staff;

Answer

SELECT conc("Fname, ,Lname" AS FullName FROM Staff;

Question 24

Look at the table above, which of the following shows all the fields with the salary between 10000 - 40000, and the staff position is either CEO, Director or Cleaner, and the first name begins with H and has 3 characters following it or the last name begins with W

Accepted Answer

SELECT * FROM Staff WHERE (Salary BETWEEN 10000 AND 40000) AND (Position IN('CEO','Director','Cleaner')) AND (Fname LIKE 'H___' OR Lname LIKE 'W%');

Answer

SELECT * FROM Staff WHERE (Salary IS BETWEEN 10000 AND 40000) AND (Position IN('CEO','Director','Cleaner')) AND (Fname LIKE 'H___' OR Lname LIKE 'W%');

Answer

SELECT * FROM Staff WHERE (Salary BETWEEN 10000 AND 40000) AND (Position IN ('CEO','Director','Cleaner') AND (Fname LIKE 'H___' or Lname LIKE 'W%');

Answer

SELECT * FROM Staff WHERE (Salary BETWEEN 10000 AND 40000) AND (Position IN ('CEO','Director','Cleaner') AND (Fname LIKE 'H___' OR Lname LIKE "W%"));

Answer

SELECT * FROM Staff WHERE (Salary IS BETWEEN 10000 AND 40000) AND (Position IN ('CEO','Director','Cleaner')) AND (Fname LIKE 'H___' OR Lname LIKE 'W%');

Question 25

Consider the following schema:

STUDENTS(student_code, first_name, last_name, email, phone_no, date_of_birth, honours_subject, percentage_of_marks);

Which of the following would display names of all the students whose honours subject is English and percentage of marks are more than 80, or honours subject is Spanish and percentage of marks more than 80

Accepted Answer

SELECT first name, last name FROM students WHERE (honours_subject = "English" OR honours_subject = "Spanish") AND percentage_of_marks > 80;

Answer

SELECT first name, last name FROM students WHERE honours_subject = "English" OR honours_subject = "Spanish" AND percentage_of_marks > 80;

Answer

SELECT first name, last name FROM students WHERE (honours_subject = "English" OR honours_subject = "Spanish" AND percentage_of_marks > 80);

Answer

SELECT first name, last name FROM students WHERE (honours_subject = "English") OR honours_subject = "Spanish" AND percentage_of_marks > 80;

Answer

None of the above

Question 26

Consider the following schema:

STUDENTS(student_code, first_name, last_name, email, phone_no, date_of_birth, honours_subject, percentage_of_marks);

Which of the following would display names and percentage of marks of all students sorted by honours subject, and then order by percentage of marks

Accepted Answer

SELECT first_name, last name FROM students WHERE (honours_subject = "English" OR honours_subject = "Spanish") AND percentage_of_marks > 80;

Answer

SELECT first_name, last name FROM students WHERE honours_subject = "English" OR honours_subject = "Spanish" AND percentage_of_marks > 80;

Answer

SELECT first_name, last name FROM students WHERE (honours_subject = "English" OR honours_subject = "Spanish" AND percentage_of_marks > 80);

Answer

SELECT first_name, last name FROM students WHERE (honours_subject = "English") OR honours_subject = "Spanish" AND percentage_of_marks > 80;

Answer

None of the above

Question 27

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"

Accepted Answer

SELECT * FROM Persons WHERE FirstName LIKE 'a&'

Answer

SELECT * FROM Persons WHERE FirstName='%a%'

Answer

SELECT * FROM Persons WHERE FirstName IS LIKE '&a'

Answer

SELECT * FROM Persons WHERE FirstName="&a"

Answer

None of the above

Question 28

With SQL, regardless of what type of manager, or which specific manager it is, how do you find those members of staff who are not managers, yet they earn more than the other staff members who are managers

Accepted Answer

SELECT * FROM Staff WHERE Position <> 'Manager' AND Salary > ANY (SELECT Salary FROM Staff WHERE Position = 'Manager');

Answer

SELECT * FROM Staff WHERE Position <> 'Manager' AND Salary > ALL (SELECT Salary FROM Staff WHERE Position = 'Manager');

Answer

SELECT * FROM Staff WHERE Position IN ('Manager', 'Supervisor', 'Chief Officer') AND Salary > ANY (SELECT Salary FROM Staff WHERE Position ='Manager');

Answer

SELECT staffNo, fName, lName, position FROM Staff s WHERE EXISTS (SELECT * FROM Staff b WHERE s.position=b.position AND position='Manager';

Question 29

Using SQL, look at the given tables, and choose the correct answer which displays the following table given in the answers

Accepted Answer

A & D

Answer

A)

Answer

B)

Answer

C)

Answer

D)

Question 30

Using SQL, look at the given table, and choose the correct answer which displays the following table given  below in the answers

Question 31

Which of the following statements is true for Inner Join:

Accepted Answer

Returns all rows when there is at least one match in BOTH tables

Answer

Return all rows from the left table, and the matched rows from the right table

Answer

Return all rows when there is a match in ONE of the tables

Answer

Is structured in such a way that columns with the same name of associated tables will appear only once

Answer

None of the above

Question 32

Which of the following statements is true for Natural Join:

Accepted Answer

Is structured in such a way that, columns with the same name of associated tabled will appear once only

Answer

Returns all rows when there is at least one match in BOTH tables

Answer

Return all rows from the left table, and the matched rows from the right table

Answer

Return all rows when there is a match in ONE of the tables

Answer

None of the above

Question 33

Which of the following statements is true for Full Join:

Accepted Answer

Return all rows when there is a match in ONE of the tables

Answer

Returns all rows when there is at least one match in BOTH tables

Answer

Return all rows from the right table, and the matched rows from the left table

Answer

Is structured in such a way that, columns with the same name of associated tables will appear once only

Answer

None of the above

Question 34

Which of the following statements is true for Left Join:

Accepted Answer

Return all rows from the left table, and the matched rows from the right table

Answer

Returns all rows when there is at least one match in BOTH tables

Answer

Return all rows from the left table, and the matched rows from the left table

Answer

Return all rows when there is a match in ONE of the tables

Answer

None of the above

Question 35

Which of the following statements is true for Right Join:

Accepted Answer

Return all rows from the right table, and the matched rows from the left table

Answer

Returns all rows when there is at least one match in BOTH tables

Answer

Return all rows from the right table, and the matched rows from the right table

Answer

Return all rows when there is a match in ONE of the tables

Answer

None of the above

Question 36

Using SQL, look at the given table, and choose the correct answer which displays the following result: {11, 12}

Accepted Answer

SELECT * FROM tab1 INNER JOIN tab2 WHERE tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 RIGHT OUTER JOIN tab2 WHERE tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 LEFT OUTER JOIN tab2 WHERE tab1.numid = tab2.numid

Answer

SELECT * FROM tab1 FULL OUTER JOIN tab2 WHERE tab1.numid = tab2.numid;

Answer

None of the above

Question 37

Using SQL, look at the given tabl, and choose the correct answer which displays the following result: {11, 12, 13, 15}

Accepted Answer

SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 INNER JOIN tab2 ON tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 LEFT OUTER JOIN tab ON tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 FULL OUTER JOIN tab2 ON tab1.numid = tab2.numid

Answer

None of the above

Question 38

Using SQL, look at the given tables, and choose the correct answer which displays the following result: {10, 14, 11, 12}

Accepted Answer

SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 INNER JOIN tab2 ON tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.numid = tab2.numid;

Answer

SELECT * FROM tab1 FULL OUTER JOIN tab2 ON tab1.numid = tab2.numid;

Answer

None of the above

ERD & Normalisation & SQL

Create a free account to finish this quiz

Questions in this quiz (38)

Information

Feedback

What do you think of the new look?