quiz
QUESTION 1
When would result set from a LEFT OUTER JOIN have more rows than an INNER JOIN?
• When there are NULL values in the joined column in the left table
• When there are NULL values in the joined column in the right table
• Both types of joins will have the same number of rows
• An INNER JOIN will always have more rows than an OUTER JOIN
QUESTION 2
Using the bank database, which query could be use to tell you what city Louis Blake lives in?
• SELECT city FROM customer CROSS JOIN individual WHERE fname = 'Louis' AND lname = 'Blake';
• SELECT city FROM customer INNER JOIN individual WHERE fname = 'Louis' AND lname = 'Blake';
• SELECT city FROM customer NATURAL JOIN individual WHERE fname = 'Louis' AND lname = 'Blake';
• SELECT city FROM customer RIGHT OUTER JOIN individual WHERE fname = 'Louis' AND lname = 'Blake';
QUESTION 3
It is possible to do a LEFT OUTER join of a table with itself.
• True
• False
QUESTION 4
A single SELECT command can be used to join three different tables
• True
• False
QUESTION 5
The ELSE clause in an SQL statement is optional. What happens when you do not supply the ELSE clause and none of the WHEN conditions are matched? (One way to find out would be to try it your Shell or Workbench)
• Nothing happens
• Null is returned
• An error is generated
• A zero is returned
QUESTION 6
What goes in the blank?
SELECT lname, fname, CASE title WHEN 'President' _____ 'Head Honcho' ELSE title END FROM employee;
• =
• THEN
• USE
• SET
QUESTION 7
What makes the most sense to go in the blank?
SELECT fname,lname, CASE WHEN superior_emp_id ___ NULL THEN 'No Boss' ELSE superior_emp_id END Boss FROM employee;
• =
• IS
• LIKE
• EQUALS
QUESTION 8
The conditional in the previous question (7) is an example of
• Simple Case
• Searched Case
• Conditional Case
• Complex Case
QUESTION 9
How many people can request a WRITE LOCK on a table at the same time?
• 1
• 2
• More than 2
• Zero
QUESTION 10
If the first SELECT command returns 18, what value will be returned by the second one?
USE bank;
SELECT COUNT(*) FROM employee;
START TRANSACTION; INSERT INTO employee VALUES (19, 'Peter', 'Fales', '2019-11-11', NULL, 16, 1, 'IT', 4); ROLLBACK;
SELECT COUNT(*) FROM employee;
• zero
• 19
• 18
• 17
QUESTION 11
If the first SELECT command returns 18, what value will be returned by the second one?
USE bank;
SELECT COUNT(*) FROM employee;
START TRANSACTION; INSERT INTO employee VALUES (19, 'Peter', 'Fales', '2019-11-11', NULL, 16, 1, 'IT', 4); INSERT INTO employee VALUES(20, 'John','Smith', '2019-11-11',NULL, 16, 1, 'IT', 4); SAVEPOINT savepoint1; INSERT INTO employee VALUES(21,'Jane','Doe','2019-11-11',NULL,16, 1,'IT', 4);
ROLLBACK TO SAVEPOINT savepoint1; COMMIT;
SELECT COUNT(*) FROM employee;
• 18
• 19
• 20
• 21
QUESTION 12
The MyISAM Storage Engine supports transactions.
• True
• False
QUESTION 13
A view can be JOINed with which of the following (Select all that apply)
• A view
• A table
• A subquery
• Itself
QUESTION 14
A view can be used to allow user access to only certain columns of a table
• True
• False
QUESTION 15
A view cannot include a WHERE clause
• True
• False
QUESTION 16
When is it possible to an UPDATE on a view?
• You can always update the data in a view
• You can never update the data in a view
• You can update a view when certain conditions are met
• You can update a view if it includes a GROUP BY clause
QUESTION 17
What is the name of the special view in MySQL that contains the schema's metadata?
• information_schema
• metadata
• mysql
• performance_schema
QUESTION 18
Metadata can be used check for the existence of a particular table. (For example, check if the employee table exists in the bank database)
• True
• False
QUESTION 19
Which metadata table contains information about indexes?
• information_schema.indexes
• information_schema.keys
• information_schema.statistics
• information_schema.rows
QUESTION 20
Which command could be used to get a list of all the tables/views in the bank database?
• show tables in bank;
• SELECT table_name FROM information_schema.tables WHERE table_schema = 'bank';
• Both of these
• Neither of these
QUESTION 21
For up to 10 points extra credit, supply a question that could be used in the Final Exam. It should be a multiple choice, mutliple answer, or true/false question, and it should indicate which answer (or answers) are correct.
Remember that person taking the final exam has no access to the book, notes, or internet.
I'll look over the answers before I decide how to score this. My intent at this point is to give 5 points for a suitable question, and 5 points if you provide the correct answer.