Question 18 pts
The best practice for declaring/creating a Primary Key must be which of the following?  
Group of answer choices

Numeric

Not Null

Auto increment

Unique

All of the mentioned
 
Flag question: Question 2
Question 28 pts
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?
Group of answer choices

Use the ALTER TABLE command with the ADD clause on the EMP table.

Use the ALTER TABLE command with the MODIFY clause on the SAL table.

Use the ALTER TABLE command with the MODIFY clause on the EMP table.

Use the ALTER TABLE command with the ADD clause on the SAL table.
 
Flag question: Question 3
Question 38 pts
Multiple types of JOINs (ie: INNER, LEFT , RIGHT ) can be used in the same query.
Group of answer choices

True

False
 
Flag question: Question 4
Question 48 pts
You can use a subquery while also using an aggregate function.

(True/False)

Group of answer choices

True

False
 
Flag question: Question 5
Question 58 pts
You can use a combination GROUP BY, HAVING and WHERE clauses in one SQL statement.

True or False, and why?

Group of answer choices

False - these clauses can never be used together.

True, but only if the WHERE clause comes last.

False - these clauses can only be used in an UPDATE statement.

True but they must appear in the SQL statement in the order WHERE, GROUP BY, HAVING
 
Flag question: Question 6
Question 68 pts
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
);

Group of answer choices

A cartesian product would be returned

All values in the employees table for employees with department_id of 14 would be returned.

All of the values in the employees table would be returned for employees who are not in the department with an id of 14.

No values would be returned.
 
Flag question: Question 7
Question 78 pts
Why is a primary key important in database tables? (Choose all that apply)
Group of answer choices

To easily identify and find unique rows in the database table

To access database records faster

To help maintain referential integrity

To secure the relational database
 
Flag question: Question 8
Question 88 pts
We use constraints for which of the following reasons?
Group of answer choices

They enhance data integrity and provide adherence to business requirements.

They add an additional level of complexity to the table.

They remove potential backdoors in our table that hackers could otherwise exploit.

Because our boss told us to. There is no other reason.
 
Flag question: Question 9
Question 98 pts
which of the following statements is true?
Group of answer choices

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 cannot be automatically generated

In the MySQL database, The value of a primary key is automatically generated by default.

In the MySQL database, The value of a primary key is automatically generated by using AUTO_INCREMENT
 
Flag question: Question 10
Question 108 pts
All tables In a relational database MUST contain at least one Foreign Key.

(True/False)

Group of answer choices

True

False
 
Flag question: Question 11
Question 118 pts
ORDER BY sorts data in descending order by default.
Group of answer choices

True

False
 
Flag question: Question 12
Question 128 pts
Which SELECT statement will return the largest salary in the salaries table?
Group of answer choices

SELECT MAX(SALARY) FROM SALARIES;

SELECT MAXIMUM(SALARY) FROM SALARIES;

SELECT SALARY FROM SALARIES WHERE SALARY=MAX;

SELECT LARGEST(SALARY) FROM SALARIES;
 
Flag question: Question 13
Question 138 pts
Which of the following statement(s) are true regarding a TRANSACTION? (Choose all that apply)
Group of answer choices

Multiple Statements(INSERT, UPDATE) can execute in one TRANSACTION

Changes made within a transaction are invisible to other users of the database until the COMMIT statement is issued.

Transactions are committed using the ROLLBACK statement.

Transactions are committed using the Select statement.
 
Flag question: Question 14
Question 148 pts
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;
Group of answer choices

Everything for products table that have an id of null.

The Query will return NO records.

Everything for products table that have no entry in the orderdetails table.

This query will result in an error.
 
Flag question: Question 15
Question 158 pts
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')
);
Group of answer choices

The statement will return an error.

Nothing will happen because the nested query will always return null

Payments that are ‘pending’ or ‘rejected’ in the ACCOUNTS_PAYABLE table will have their status set to ‘paid’.

Payments that are neither 'pending' nor 'rejected' in the ACCOUNTS_PAYABLE table will have their status set to 'paid'.
 
Flag question: Question 16
Question 168 pts
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?

Group of answer choices

Self Join

Cross Join

Non-Equi-Join

Inner Join
 
Flag question: Question 17
Question 178 pts
You are working with very large tables in your database. Which SQL clause do you use to prevent exceedingly large query results?
 

Group of answer choices

UNIQUE

LIMIT

DISTINCT

DIFFERENT
 
Flag question: Question 18
Question 188 pts
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;
Group of answer choices

Use a WHERE clause at the end of the query.

Use a WHERE clause before the GROUP BY.

Use a HAVING clause before the GROUP BY.

This cannot be done.
 
Flag question: Question 19
Question 198 pts
The _____________  statement is used to return only distinct (different) values.
Group of answer choices

SELECT DIFFERENT

SELECT DISTINCT

SELECT *

SELECT UNIQUE
 
Flag question: Question 20
Question 208 pts
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;
Group of answer choices

All employee_id, ﬁrstname, and lastname values in the employees table.

Only employee_id, ﬁrstname, and lastname values in the employees table that have no matching entry in the SAL table.

A syntax error would be returned.

No values would be returned.
 
Flag question: Question 21
Question 218 pts
Why will this query generate an error?

SELECT employee_id, salary
FROM employees INNER JOIN salaries ON employee_id
    = employee_id
WHERE salary >= 75000;
Group of answer choices

>= is not a valid operator.

The join predicate's 'employee_id' ﬁeld is ambiguous.

The above query will not return an error.

No employees with a salary greater than or equal to 75000 exist in the database.
 
Flag question: Question 22
Question 228 pts
Which statement will change the city of a customer with the customer_id of 23 to "NYC"?
Group of answer choices

ALTER TABLE customers MODIFY city='NYC' WHERE customer_id=23;

UPDATE customers SET city='NYC' WHERE customer_id=23;

UPDATE customers MODIFY city='NYC' WHERE customer_id=23;

ALTER TABLE customers SET city='NYC' WHERE customer_id=23;
 
Flag question: Question 23
Question 238 pts
Why would normalized tables be preferred over de-normalized tables in a database? (All that apply)
Group of answer choices

Because they reduce the amount of redundancy

Because they require database user authentication to make them secure

They are never preferred. De-normalized tables will always be better.

Normalized tables simplify data maintenance.
 
Flag question: Question 24
Question 248 pts
What does the WHERE clause do?
Group of answer choices

It deﬁnes the source of data to be imported into the database.

WHERE is used to deﬁne a JOIN predicate.

It deﬁnes the table(s) from which data is selected.

It deﬁnes one or more conditions that must be met for a row of data to be returned.
 
Flag question: Question 25
Question 258 pts
It is possible for a primary key to consist of multiple ﬁelds.

(True/False)

Group of answer choices

True

False