Quiz
Week 7 and 8
This quiz has a time limit, so it opens in the usual player.
Questions in this quiz (13)
1Consider the following relational schema (PKs are underlined):
Employee(EmployeeID, FirstName, Surname, Dept, Office, Salary, City)
FK = (Dept) ~> Department(DeptName)
Department(DeptName, Address, City)
Select SQL statement(s) to find employees live in the the same city as another employee.- ASELECT *
FROM Employee AS ee
WHERE ee.City = ANY
(SELECT en.City
FROM Employee AS en
WHERE ee.EmployeeID <> en.EmployeeID) - BSELECT e1.*
FROM Employee AS e1 JOIN Employee AS e2
ON e1.EmployeeID <> e2.EmployeeID
AND e1.City = e2.City - CSELECT *
FROM Employee AS ee
WHERE ee.City IN
(SELECT en.City
FROM Employee AS en
WHERE ee.EmployeeID <> en.EmployeeID) - DSELECT *
FROM Employee AS ee
WHERE EXISTS
(SELECT *
FROM Employee AS en
WHERE ee.City = en.City
AND ee.EmployeeID <> en.EmployeeID)
- ASELECT *
2Given the table tblStudent below:
tblStudent
Name Age Mark Course
John 21 90 IT1001
John 21 60 IT1005
Matt 20 80 IT1001
Peter 20 80 It1001
where Age and Mark are of the integer type, how many tuples will be in the output of the query "SELECT all Age, Mark FROM tblStudent;"?- A2
- B1
- CThere is a syntax error because of 'all'.
- D4
3Given the following table Orders, which SQL query returns the sum of all orders for each customer?
Orders
orderID orderDate total customerName
1 01-09-2008 1000 Adam
2 02-09-2008 1600 Nancy
3 02-09-2008 700 Harry
4 03-09-2008 300 Harry
5 05-09-2008 2000 Adam
6 15-09-2008 200 Nancy
Select one:- ASELECT customerName, SUM(total)
FROM Orders
GROUP BY orderID - BSELECT customerName, SUM(total)
FROM Orders
GROUP BY customerName - CSELECT customerName, SUM(total)
FROM Orders
GROUP BY Orders - DSELECT customerName, SUM(total)
FROM Orders
GROUP BY orderDate
- ASELECT customerName, SUM(total)
4Consider we have a relation/table CareBears to hold the information about care bears. When a care bear is damaged, we put the information of that care bear in another relation/table DamagedBears.
Select the query that will best calculate the number of remaining Care Bears after a dog attack.- ASELECT COUNT(*)
FROM DamagedBears - BSELECT SUM(*)
FROM DamagedBears - CSELECT COUNT(*)
FROM CareBears AS CB LEFT JOIN DamagedBears AS DB
ON CB.careBearID = DB.careBearID - DSELECT COUNT(careBearID)
FROM CareBears
WHERE careBearID NOT IN (SELECT careBearID
FROM DamagedBears)
- ASELECT COUNT(*)
5Which of the following SQL keywords could potentially be used to return the same results as an INNER JOIN between tables?
Select one:- AQuite possibly using any of these keywords
- BANY
- CEXISTS
- DIN
6Consider the following relational schema (PKs are underlined):
Employee(EmployeeID, FirstName, Surname, Dept, Office, Salary, City)
FK = (Dept) ~> Department(DeptName)
Department(DeptName, Address, City)
And we have an instance of this relation as given below:
Finally consider the following stored procedure:
CREATE PROCEDURE getEmployee @City varchar(30), @OfficeNumber integer
AS
SELECT *
FROM Employee
WHERE City = @City OR Office = @OfficeNumber
GO;
EXEC getEmployee @City ='Toulouse', @OfficeNumber = 20;
How many tuple(s) will be displayed after running these SQL statements?
Select one:
- A3
- B4
- C8
- D1
7Which SQL keyword is used with wild cards?
Select one:- ACOMPARE
- BLIKE
- CBETWEEN
- DEXISTS
8Consider the following relational schema (PKs are underlined):
Employee(EmployeeID, FirstName, Surname, Dept, Office, Salary, City)
FK = (Dept) ~> Department(DeptName)
Department(DeptName, Address, City)
Select SQL statement(s) to find the department of the employee earning the lowest salary.
Select one or more:- ASELECT DISTINCT Dept
FROM Employee
WHERE Salary <= ANY (SELECT DISTINCT Salary
FROM Employee) - BSELECT DISTINCT Dept
FROM Employee
WHERE Salary <= ALL (SELECT DISTINCT Salary
FROM Employee) - CSELECT Dept, MIN(Salary)
FROM Employee - DSELECT DISTINCT Dept
FROM Employee
WHERE Salary = (SELECT MIN(Salary)
FROM Employee)
- ASELECT DISTINCT Dept
9Which statement about an update statement is NOT correct?
Select one:- AThe keyword "update" is followed by one and only one table name.
- BThe "where" clause can be omitted.
- CThe keyword "set" can be followed by multiple assignment expressions.
- DIf the join operation is used, an update statement can update multiple tables.
10Consider the relational schema:
Hotel (HNo, Name, Address)
PK: (HNo)
Room (RNo, HNo, Type, Price)
PK: (RNo, Hno)
FK: Hno ~> Hotel (Hno)
Booking (HNo, GNo, DateFrom, DateTo, RNo)
PK: (HNo, GNo, DateFrom)
FK1: Gno ~> Guest(Gno)
FK2: Hno ~> Hotel(Hno)
FK3: (Hno,Rno) ~> Room(Hno, Rno)
Guest (GNo, Name, Address)
PK: GNo
Select the SQL statements for the query
"List the number of rooms in each hotel in London."
Select one or more:- ASELECT h.name, COUNT(rno)
FROM hotel AS h INNER JOIN room AS r
ON h.hno=r.hno
AND h.address LIKE '%London%'
GROUP BY h.name; - BSELECT hno, COUNT(*)
FROM Room
WHERE hno IN (SELECT hno
FROM Hotel
WHERE UPPER(address) LIKE '%LONDON%')
GROUP BY hno; - CSELECT h.name, COUNT(rno)
FROM hotel AS h INNER JOIN room AS r
ON h.hno=r.hno
AND h.address = 'London'
GROUP BY h.name; - DSELECT hno, COUNT(*)
FROM Room
WHERE hno IN (SELECT hno
FROM Hotel
WHERE address LIKE '%LONDON%'
- ASELECT h.name, COUNT(rno)
11Which of the following represent the built-in functions of SQL
Select one- ASUM, SUB, ADD, MULT, AVG
- BCOUNT, AVG, DIFF, MIN, MAX
- CCOUNT, MAX, MIN, SUM, AVG
- DSUM, AVG, MULT, DIV, ADD
12Given the table
Student(Name, Age, Mark)
and a query
"SELECT Name as Student, Age FROM Student m;",
which of the following statements is true?
Select one:- AIt has an error as Student is used twice.
- BThere is no error in the statement.
- CThere is an error because of 'as' between 'Name' and 'Student'
- DIt has error as there is an 'm' at the end.
13Consider we have a relation/table CareBears to hold the information about care bears. When a care bear is damaged, we put the information of that care bear in another relation/table DamagedBears.
Select the query that will determine the number of destroyed care bears after a dog attack.
Select one:- ASELECT COUNT(*) AS damagedCount
FROM DamagedBears AS DB - BSELECT COUNT(DB.careBearID)
FROM CareBears AS CB JOIN DamagedBears AS DB
ON CB.careBearID = DB.careBearID - CSELECT COUNT(DB.careBearID)
FROM DamagedBears AS damagedCount - DSELECT *
FROM CareBears AS CB JOIN DamagedBears AS DB
ON CB.careBearID = DB.careBearID
- ASELECT COUNT(*) AS damagedCount
Information
- Description
- Quiz on Week 7 and 8, created by Dani Nunns on 21/11/2020.
- Author
- Dani Nunns
- Language
- English
- Views
- 0
- Rating
- No ratings yet Rate this quiz