Quiz
Question 1
Multiple types of JOINs (ie: INNER, LEFT , RIGHT ) can be used in the same query.
[x] True
[ ] False
Question 2
We use constraints for which of the following reasons?
[ ] They add an additional level of complexity to the table.
[ ] Because our boss told us to. There is no other reason.
[ ] They remove potential backdoors in our table that hackers could otherwise exploit.
[x] They enhance data integrity and provide adherence to business requirements.
Question 3
Why would normalized tables be preferred over de-normalized tables in a database? (All that apply)
[x] Normalized tables simplify data maintenance.
[ ] They are never preferred. De-normalized tables will always be better.
[x] Because they reduce the amount of redundancy
[ ] Because they require database user authentication to make them secure
Question 4
Why will this query generate an error?
SELECT employee_id, salary
FROM employees INNER JOIN salaries ON employee_id = employee_id
WHERE salary >= 75000;[ ] No employees with a salary greater than or equal to 75000 exist in the database.
[ ] The above query will not return an error.
[ ] >= is not a valid operator.
[x] The join predicate's 'employee_id' field is ambiguous.
Question 5
What will the following query return? Please note that in this scenario, the salary column is not null.
SELECT e.employee_id, e.firstname, e.lastname
FROM employees e LEFT JOIN salaries s ON e.employee_id = s.employee_id
WHERE s.salary = NULL; [ ] A syntax error would be returned.
[ ] All employee_id, firstname, and lastname values in the employees table.
[x] No values would be returned.
[ ] Only employee_id, firstname, and lastname values in the employees table that have no matching entry in the SAL table.
Question 6
The _____________ statement is used to return only distinct (different) values.
[ ] SELECT *
[ ] SELECT UNIQUE
[x] SELECT DISTINCT
[ ] SELECT DIFFERENT
Question 7
You can use a combination GROUP BY, HAVING and WHERE clauses in one SQL statement.
[ ] False - these clauses can never be used together.
[ ] True, but only if the WHERE clause comes last.
[x] True but they must appear in the SQL statement in the order WHERE, GROUP BY, HAVING
[ ] False - these clauses can only be used in an UPDATE statement.
Question 8
Which of the following statement(s) are true regarding a TRANSACTION? (Choose all that apply)
[ ] Transactions are committed using the Select statement.
[ ] Transactions are committed using the ROLLBACK statement.
[x] Changes made within a transaction are invisible to other users of the database until the COMMIT statement is issued.
[x] Multiple Statements(INSERT, UPDATE) can execute in one TRANSACTION
Question 9
Given two tables, EMP and SAL, how would you add a foreign key constraint on the emp_no column in the SAL table, referring to the id column in the EMP table?
[x] Use the ALTER TABLE command with the ADD clause on the SAL table.
[ ] Use the ALTER TABLE command with the ADD clause on the EMP table.
[ ] Use the ALTER TABLE command with the MODIFY clause on the EMP table.
[ ] Use the ALTER TABLE command with the MODIFY clause on the SAL table.
Question 10
How could you modify the following query in order to ONLY count customers with a first_name starting with "T"?
SELECT c.last_name, c.first_name, COUNT(o.orderNumber)
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id; [ ] Use a WHERE clause at the end of the query.
[x] Use a WHERE clause before the GROUP BY.
[ ] Use a HAVING clause before the GROUP BY.
[ ] This cannot be done.
Question 11
What will the following query return?
Assume: The productCode is the primary key in the product table
SELECT *
FROM products p, orderdetails od
WHERE p.productCode = od.productCode and od.productCode is null; [ ] Everything for products table that have an id of null.
[ ] Everything for products table that have no entry in the orderdetails table.
[x] The Query will return NO records.
[ ] This query will result in an error.
Question 12
You are working with very large tables in your database. Which SQL clause do you use to prevent exceedingly large query results?
[ ] DIFFERENT
[x] LIMIT
[ ] UNIQUE
[ ] DISTINCT
Question 13
Why is a primary key important in database tables? (Choose all that apply)
[ ] To secure the relational database
[x] To access database records faster
[x] To easily identify and find unique rows in the database table
[x] To help maintain referential integrity
Question 14
What does the WHERE clause do?
[ ] It defines the table(s) from which data is selected.
[ ] It defines the source of data to be imported into the database.
[ ] WHERE is used to define a JOIN predicate.
[x] It defines one or more conditions that must be met for a row of data to be returned.
Question 15
It is possible for a primary key to consist of multiple fields.
[x] True
[ ] False
Question 16
All tables In a relational database MUST contain at least one Foreign Key.
[ ] True
[x] False
Question 17
The ID column of the Products table corresponds to the Product_ID column of the OrderItems table.
Your client would like to display a list of all orders along with the name and price of the items associated with that order. Which of the following joins would allow you to do that?
[ ] Non-Equi-Join
[ ] Cross Join
[x] Inner Join
[ ] Self Join
Question 18
Given the below statement, what would happen if the inner query returned an empty list?
SELECT *
FROM employees
WHERE employeeId NOT IN( SELECT employeeId FROM employees WHERE departmentId = 14 ); [ ] All values in the employees table for employees with department_id of 14 would be returned.
[ ] No values would be returned.
[ ] A cartesian product would be returned
[x] All of the values in the employees table would be returned for employees who are not in the department with an id of 14.
Question 19
What will be the results of the following statement?
UPDATE PAYMENTS SET status = 'paid' WHERE payment_id NOT IN( SELECT payment_id FROM ACCOUNTS_PAYABLE WHERE status in ('pending', 'rejected') );
[ ] Nothing will happen because the nested query will always return null
[ ] The statement will return an error.
[ ] Payments that are ‘pending’ or ‘rejected’ in the ACCOUNTS_PAYABLE table will have their status set to ‘paid’.
[x] Payments that are neither 'pending' nor 'rejected' in the ACCOUNTS_PAYABLE table will have their status set to 'paid'.
Question 20
Which statement will change the city of a customer with the customer_id of 23 to "NYC"?
[ ] ALTER TABLE customers SET city='NYC' WHERE customer_id=23;
[x] UPDATE customers SET city='NYC' WHERE customer_id=23;
[ ] ALTER TABLE customers MODIFY city='NYC' WHERE customer_id=23;
[ ] UPDATE customers MODIFY city='NYC' WHERE customer_id=23;
Question 21
ORDER BY sorts data in descending order by default.
[ ] True
[x] False
Question 22
which of the following statements is true?
[ ] In the MySQL database, The value of a primary key is automatically generated by using INCREMENT keyword
[ ] In the MySQL database, The value of a primary key is automatically generated by default.
[x] In the MySQL database, The value of a primary key is automatically generated by using AUTO_INCREMENT
[ ] In the MySQL database, The value of a primary key cannot be automatically generated
Question 23
The best practice for declaring/creating a Primary Key must be which of the following?
[ ] Auto increment
[ ] Not Null
[x] All of the mentioned
[ ] Numeric
[ ] Unique
Question 24
You can use a subquery while also using an aggregate function.
[x] True
[ ] False
Question 25
Which SELECT statement will return the largest salary in the salaries table?
[ ] SELECT LARGEST(SALARY) FROM SALARIES;
[ ] SELECT MAXIMUM(SALARY) FROM SALARIES;
[ ] SELECT SALARY FROM SALARIES WHERE SALARY=MAX;
[x] SELECT MAX(SALARY) FROM SALARIES;
Last updated