- 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 (*)
- What types of joins will return the unmatched values from both tables in the join? Full outer joins (*)
- 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 (*)
- 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) Name Null? Type DEPARTMENT_ID NOT NULL NUMBER 4 DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER (6)
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.employee_id = d.manager_id);
(*) - Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? True (*)
- 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;
(*) - Which of the following database design concepts is implemented with a self join? Recursive Relationship (*)
- 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
(*) - Which statement about a self join is true? Table aliases must be used to qualify table names. (*)
- A NATURAL JOIN is based on: Columns with the same name and datatype (*)
- 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 (*)
- The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? False (*)
- Below find the structures of the PRODUCTS and VENDORS tables: PRODUCTS
- You can do nonequi-joins with ANSI-Syntax. True or False? True (*)
- You created the CUSTOMERS and ORDERS tables by issuing these CREATE TABLE statements in sequence:
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)
FROM products p
NATURAL JOIN vendors v
ORDER BY p.product_name;
(*)
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?
FROM orders o
JOIN customers c ON o.custid = c.custid
WHERE city = 'Nashville';
(*)