Help shape GoConqr. What should we add next?

Quiz

Week 7 and 8

Dani Nunns 13 questions

Take the quiz

This quiz has a time limit, so it opens in the usual player.

Questions in this quiz (13)

  1. 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.
    1. ASELECT *
      FROM Employee AS ee
      WHERE ee.City = ANY
      (SELECT en.City
      FROM Employee AS en
      WHERE ee.EmployeeID <> en.EmployeeID)
    2. BSELECT e1.*
      FROM Employee AS e1 JOIN Employee AS e2
      ON e1.EmployeeID <> e2.EmployeeID
      AND e1.City = e2.City
    3. CSELECT *
      FROM Employee AS ee
      WHERE ee.City IN
      (SELECT en.City
      FROM Employee AS en
      WHERE ee.EmployeeID <> en.EmployeeID)
    4. DSELECT *
      FROM Employee AS ee
      WHERE EXISTS
      (SELECT *
      FROM Employee AS en
      WHERE ee.City = en.City
      AND ee.EmployeeID <> en.EmployeeID)
  2. 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;"?
    1. A2
    2. B1
    3. CThere is a syntax error because of 'all'.
    4. D4
  3. 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:
    1. ASELECT customerName, SUM(total)
      FROM Orders
      GROUP BY orderID
    2. BSELECT customerName, SUM(total)
      FROM Orders
      GROUP BY customerName
    3. CSELECT customerName, SUM(total)
      FROM Orders
      GROUP BY Orders
    4. DSELECT customerName, SUM(total)
      FROM Orders
      GROUP BY orderDate
  4. 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.
    1. ASELECT COUNT(*)
      FROM DamagedBears
    2. BSELECT SUM(*)
      FROM DamagedBears
    3. CSELECT COUNT(*)
      FROM CareBears AS CB LEFT JOIN DamagedBears AS DB
      ON CB.careBearID = DB.careBearID
    4. DSELECT COUNT(careBearID)
      FROM CareBears
      WHERE careBearID NOT IN (SELECT careBearID
      FROM DamagedBears)
  5. 5Which of the following SQL keywords could potentially be used to return the same results as an INNER JOIN between tables?

    Select one:
    1. AQuite possibly using any of these keywords
    2. BANY
    3. CEXISTS
    4. DIN
  6. 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:
    Image for question 6
    1. A3
    2. B4
    3. C8
    4. D1
  7. 7Which SQL keyword is used with wild cards?

    Select one:
    1. ACOMPARE
    2. BLIKE
    3. CBETWEEN
    4. DEXISTS
  8. 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:
    1. ASELECT DISTINCT Dept
      FROM Employee
      WHERE Salary <= ANY (SELECT DISTINCT Salary
      FROM Employee)
    2. BSELECT DISTINCT Dept
      FROM Employee
      WHERE Salary <= ALL (SELECT DISTINCT Salary
      FROM Employee)
    3. CSELECT Dept, MIN(Salary)
      FROM Employee
    4. DSELECT DISTINCT Dept
      FROM Employee
      WHERE Salary = (SELECT MIN(Salary)
      FROM Employee)
  9. 9Which statement about an update statement is NOT correct?

    Select one:
    1. AThe keyword "update" is followed by one and only one table name.
    2. BThe "where" clause can be omitted.
    3. CThe keyword "set" can be followed by multiple assignment expressions.
    4. DIf the join operation is used, an update statement can update multiple tables.
  10. 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:
    1. 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;
    2. BSELECT hno, COUNT(*)
      FROM Room
      WHERE hno IN (SELECT hno
      FROM Hotel
      WHERE UPPER(address) LIKE '%LONDON%')
      GROUP BY hno;
    3. 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;
    4. DSELECT hno, COUNT(*)
      FROM Room
      WHERE hno IN (SELECT hno
      FROM Hotel
      WHERE address LIKE '%LONDON%'

Information

Description
Quiz on Week 7 and 8, created by Dani Nunns on 21/11/2020.
Language
English
Views
0
Rating
No ratings yet
Rate this quiz
Rate this quiz

More on SELECT Queries

Feedback

What is it about?

What should we add next?