Section 6 Quiz Database Programming with SQL

1. Which two sets of join keywords create a join that will include unmatched rows from the first table specified in the SELECT statement? LEFT OUTER JOIN and FULL OUTER JOIN (*)
2. What types of joins will return the unmatched values from both tables in the join? Full outer joins (*)
3. You need to join the EMPLOYEE_HIST and EMPLOYEES tables. The EMPLOYEE_HIST table will be the first table in the FROM clause. All the matched and unmatched rows in the EMPLOYEES table need to be displayed. Which type of join will you use? A right outer join (*)
4. EMPLOYEES Table: 
   

   Name	Null?	Type
   EMPLOYEE_ID	NOT NULL	NUMBER(6)
   FIRST_NAME		VARCHAR2(20)
   LAST_NAME	NOT NULL	VARCHAR2(25)
   DEPARTMENT_ID		NUMBER (4)

   DEPARTMENTS Table:

   Name	Null?	Type
   DEPARTMENT_ID	NOT NULL	NUMBER 4
   DEPARTMENT_NAME	NOT NULL	VARCHAR2(30)
   MANAGER_ID		NUMBER (6)

   A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task? SELECT d.department_id, e.first_name, e.last_name 
   FROM employees e
   RIGHT OUTER JOIN departments d
   ON (e.employee_id = d.manager_id);
   
   (*)
5. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? True (*)
6. Which SELECT statement implements a self join? SELECT p.part_id, t.product_id 
   FROM part p, part t
   WHERE p.part_id = t.product_id;
   
   (*)
7. Which of the following database design concepts is implemented with a self join? Recursive Relationship (*)
8. Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers?SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
   FROM employees w , employees m
   WHERE w.manager_id = m.employee_id
   AND w.hire_date < m.hire_date
   (*)
9. Which statement about a self join is true? Table aliases must be used to qualify table names. (*)
10. A NATURAL JOIN is based on: Columns with the same name and datatype (*)
11. You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create? A cross join (*)
12. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? False (*)
13. Below find the structures of the PRODUCTS and VENDORS tables:
PRODUCTS
PRODUCT_ID NUMBER
PRODUCT_NAME VARCHAR2 (25)
VENDOR_ID NUMBER
CATEGORY_ID NUMBER

VENDORS
VENDOR_ID NUMBER
VENDOR_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

You want to create a query that will return an alphabetical list of products, including the product name and associated vendor name, for all products that have a vendor assigned.

Which two queries could you use? 
(Choose all correct answers) 
SELECT p.product_name, v.vendor_name
FROM products p
NATURAL JOIN vendors v
ORDER BY p.product_name;

(*)

14. You can do nonequi-joins with ANSI-Syntax. True or False? True (*)
15. You created the CUSTOMERS and ORDERS tables by issuing these CREATE TABLE statements in sequence:

CREATE TABLE customers
(custid varchar2(5),
companyname varchar2(30),
contactname varchar2(30),
address varchar2(30),
city varchar2(20),
state varchar2(30),
phone varchar2(20),
constraint pk_customers_01 primary key (custid));

CREATE TABLE orders
(orderid varchar2(5) constraint pk_orders_01 primary key,
orderdate date,
total number(15),
custid varchar2(5) references customers (custid));

You have been instructed to compile a report to present the information about orders placed by customers who reside in Nashville. Which query should you issue to achieve the desired results? 

SELECT orderid, orderdate, total
FROM orders o
JOIN customers c ON o.custid = c.custid
WHERE city = 'Nashville';

(*)

Section 3 Quiz Database Programming with SQL

• ·         Which of the following is earliest in the rules of precedence? Arithmetic operator (*)
• ·         Which of the following statements best describes the rules of precedence when using SQL?  The order in which the expressions are evaluated and calculated (*)
• ·         Which logical operator returns TRUE if either condition is true? OR (*)
• ·         Which statement about the ORDER BY clause is true? You can use a column alias in the ORDER BY clause. (*)
• ·         Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column? ORDER BY (*)

• ·         You attempt to query the database with this SQL statement:

SELECT product_id "Product Number", category_id "Category", price "Price"
FROM products
WHERE "Category" = 5570
ORDER BY "Product Number";

This statement fails when executed. Which clause contains a syntax error?

WHERE "Category" = 5570 (*)

• ·         Evaluate this SELECT statement:

SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;

Which clause contains an error?

SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)

• ·         Evaluate this SELECT statement:

SELECT last_name, first_name, email
FROM employees
ORDER BY email;

If the EMAIL column contains null values, which statement is true?

Null email values will be displayed last in the result. (*)

• ·         What clause must you place in a SQL statement to have your results sorted from highest to lowest salary?

·         ORDER BY salary DESC (*)

• ·         Evaluate this SELECT statement:

SELECT *
FROM employees
WHERE salary > 30000
AND department_id = 10
OR email IS NOT NULL;

Which statement is true? 

he AND condition will be evaluated before the OR condition. (*)

• ·         The PLAYERS table contains these columns:

PLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)

You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?

 SELECT last_name, first_name, team_id, salary 
FROM players 
WHERE salary BETWEEN 25000 AND 100000 
AND team_id BETWEEN 1200 AND 1500 
ORDER BY team_id, salary DESC; (*)

• ·         The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(9) PK
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
Compare these two SQL statements:

1.
SELECT DISTINCT department_id DEPT, last_name, first_name
FROM employees
ORDER BY department_id;

2.
SELECT department_id DEPT, last_name, first_name
FROM employees
ORDER BY DEPT;

How will the results differ? 

The statements will sort on different column values. (*)

• ·         The PLAYERS table contains these columns:

PLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)

You want to display all players' names with position 6900 or greater.
You want the players names to be displayed alphabetically by last name and then by first name.
Which statement should you use to achieve the required results?

SELECT last_name, first_name 
FROM players 
WHERE position_id >= 6900 
ORDER BY last_name, first_name;

(*)

• ·         The following statement represents a multi-row function. True or False?

SELECT MAX(salary)
FROM employees 

True (*)

• ·         The following statement represents a multi-row function. True or False?

SELECT UPPER(last_name)
FROM employees;

False (*)