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' ﬁeld 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, ﬁrstname, and lastname values in the employees table.
[x] No values would be returned.
[ ] Only employee_id, ﬁrstname, 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 ﬁrst_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 deﬁnes the table(s) from which data is selected.
[ ] It deﬁnes the source of data to be imported into the database.
[ ] WHERE is used to deﬁne a JOIN predicate.
[x] It deﬁnes 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 ﬁelds.

[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;

PreviousDemonstration - Named Queries in Hibernate NextWeek 7

Last updated 1 year ago