> For the complete documentation index, see [llms.txt](https://peterlulu666.gitbook.io/tek-system-java-developer/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://peterlulu666.gitbook.io/tek-system-java-developer/week-6/quiz.md).

# 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?

```sql
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. 

```sql
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"?

```sql
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

```sql
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?

```sql
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;