Test of Basic Knowledge of SQL
Using this test you can assess whether you have basic working knowledge of SQL that is prerequisite for attending
Deloitte’s Data Science Academy – Intermediate program. To verify accuracy of your answers, use the key with
correct answers at the end of this document. In case that your success rate will be lower than 75% (i.e., you will
answer correctly less than 30 questions out of 40), you should consider consulting available on-line resources that
will enable you to become more deeply familiarized with SQL. For that purpose, we have provided you for each
question with a link that will bring you to the website that will help you to grasp and practice given SQL concept.
There is no strict time limit for finishing the test, but we suppose that you will be able to finish it in ~20 minutes.
Some of the questions within the test assume that following Customers, Orders, Products and OrderDetails tables
from the Test database are available to you.
Customers table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Mary A Mary Austin 338 City Road London EC1V 2NX UK
2 KJ Karl Jung Graefestr. 2 Berlin 10967 Germany
3 Jamie_Wats James Watson 3116 Massachusetts Avenue Washington 20004 USA
… … … … … … …
Orders table:
OrderID CustomerID OrderDate
10308 5 2017-01-21
10309 80 2017-01-22
10310 97 2017-01-22
… … …
Products table:
ProductID ProductName SupplierID CategoryID Unit Price
1 Lip Gloss 1 1 2 bottles 20
2 Eyeshadow makeup 1 2 1 box 10
3 Lip Balm 2 1 1 bottle 5
… … … … … …
OrderDetails table:
OrderDetailID OrderID ProductID Quantity
1 12508 5 1
2 12508 6 10
3 12509 25 50
… … … …
01
, Q1) What SQL statement would you use to create SQL database named Test?
a) CREATE DBT Test;
b) CREATE Test;
c) CREATE DATABASE Test;
d) MAKE DATABASE Test;
Q2) What SQL statement would you use to remove SQL database named Test?
a) DROP DATABASE Test;
b) REMOVE Test;
c) DROP DBT Test;
d) REMOVE DATABASE Test;
Q3) Assign individual relationships that one can find in ERD (Entity Relationship Diagram) to their appropriate
descriptions.
a) 1) zero or many (optional)
b) 2) one or more (mandatory)
c) 3) one to one
d) 4) one and only one (mandatory)
e) 5) one to many (mandatory)
f) 6) many
g) 7) zero or one (optional)
Q4) Which kind of relationship best describes relationship between Products (A) and OrderDetails (B) tables?
a)
b)
c)
d)
Q5) Fulfill missing parts in SQL statement below to create Products table.
______________ ________ (
ProductID INT ______________,
ProductName VARCHAR(255),
SupplierID INT,
CategoryID INT,
Unit _____________,
Price _____________
);
Q6) Primary Key is a combination of two types of constraints. Find the correct one in the list below.
a) UNIQUE & DEFAULT
b) CHECK & UNIQUE
c) UNIQUE & NOT NULL
d) CHECK & AUTO INCREMENT
Q7) Which field in the Orders table has a role of FOREIGN KEY in relation to Customers table?
a) CustomerID
b) OrderID
c) OrderDate
d) CustomerName
02
Using this test you can assess whether you have basic working knowledge of SQL that is prerequisite for attending
Deloitte’s Data Science Academy – Intermediate program. To verify accuracy of your answers, use the key with
correct answers at the end of this document. In case that your success rate will be lower than 75% (i.e., you will
answer correctly less than 30 questions out of 40), you should consider consulting available on-line resources that
will enable you to become more deeply familiarized with SQL. For that purpose, we have provided you for each
question with a link that will bring you to the website that will help you to grasp and practice given SQL concept.
There is no strict time limit for finishing the test, but we suppose that you will be able to finish it in ~20 minutes.
Some of the questions within the test assume that following Customers, Orders, Products and OrderDetails tables
from the Test database are available to you.
Customers table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Mary A Mary Austin 338 City Road London EC1V 2NX UK
2 KJ Karl Jung Graefestr. 2 Berlin 10967 Germany
3 Jamie_Wats James Watson 3116 Massachusetts Avenue Washington 20004 USA
… … … … … … …
Orders table:
OrderID CustomerID OrderDate
10308 5 2017-01-21
10309 80 2017-01-22
10310 97 2017-01-22
… … …
Products table:
ProductID ProductName SupplierID CategoryID Unit Price
1 Lip Gloss 1 1 2 bottles 20
2 Eyeshadow makeup 1 2 1 box 10
3 Lip Balm 2 1 1 bottle 5
… … … … … …
OrderDetails table:
OrderDetailID OrderID ProductID Quantity
1 12508 5 1
2 12508 6 10
3 12509 25 50
… … … …
01
, Q1) What SQL statement would you use to create SQL database named Test?
a) CREATE DBT Test;
b) CREATE Test;
c) CREATE DATABASE Test;
d) MAKE DATABASE Test;
Q2) What SQL statement would you use to remove SQL database named Test?
a) DROP DATABASE Test;
b) REMOVE Test;
c) DROP DBT Test;
d) REMOVE DATABASE Test;
Q3) Assign individual relationships that one can find in ERD (Entity Relationship Diagram) to their appropriate
descriptions.
a) 1) zero or many (optional)
b) 2) one or more (mandatory)
c) 3) one to one
d) 4) one and only one (mandatory)
e) 5) one to many (mandatory)
f) 6) many
g) 7) zero or one (optional)
Q4) Which kind of relationship best describes relationship between Products (A) and OrderDetails (B) tables?
a)
b)
c)
d)
Q5) Fulfill missing parts in SQL statement below to create Products table.
______________ ________ (
ProductID INT ______________,
ProductName VARCHAR(255),
SupplierID INT,
CategoryID INT,
Unit _____________,
Price _____________
);
Q6) Primary Key is a combination of two types of constraints. Find the correct one in the list below.
a) UNIQUE & DEFAULT
b) CHECK & UNIQUE
c) UNIQUE & NOT NULL
d) CHECK & AUTO INCREMENT
Q7) Which field in the Orders table has a role of FOREIGN KEY in relation to Customers table?
a) CustomerID
b) OrderID
c) OrderDate
d) CustomerName
02
