Quiz
QUIZ 6
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (15)
1Evaluate this SELECT statement:
SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission
FROM patient a
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
Which clause generates an error?- AON (b.physician_id = c.physician_id);
- BJOIN physician b
- CON (a.patient_id = c.patient_id)
- DJOIN admission c
2Below 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?- ASELECT p.product_name, v.vendor_name
FROM products p
NATURAL JOIN vendors v
ORDER BY p.product_name; - BSELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
ON (vendor_id)
ORDER BY p.product_name; - CSELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (p.vendor_id)
ORDER BY p.product_name; - DSELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (vendor_id)
ORDER BY p.product_name; - ESELECT p.product_name, v.vendor_name
FROM products p
LEFT OUTER JOIN vendors v
ON p.vendor_id = v.vendor_id
ORDER BY p.product_name;
- ASELECT p.product_name, v.vendor_name
3You 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?- ASELECT custid, companyname
FROM customers
WHERE city = 'Nashville'; - BSELECT orderid, orderdate, total
FROM orders
WHERE city = 'Nashville'; - CSELECT orderid, orderdate, total
FROM orders o
JOIN customers c ON o.custid = c.custid
WHERE city = 'Nashville'; - DSELECT orderid, orderdate, total
FROM orders o
NATURAL JOIN customers c ON o.custid = c.custid
WHERE city = 'Nashville';
- ASELECT custid, companyname
4Which 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?
- ASELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees w
WHERE w.manager_id = w.employee_id
AND w.hire_date < w.hire_date - BSELECT 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 - CSELECT 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 - DSELECT 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
- ASELECT w.last_name, w.hire_date, m.last_name, m.hire_date
5Which of the following database design concepts is implemented with a self join?
- ASupertype
- BNon-Transferability
- CRecursive Relationship
- DArc
6 Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
- ATrue
- BFalse
7Which of the following database design concepts do you need in your tables to write Hierarchical queries?
- ASupertype
- BNon-Transferability
- CRecursive Relationship
- DArc
8Which statement about a self join is true?
- AA self join must be implemented by defining a view.
- BTable aliases cannot be used to qualify table names.
- CThe NATURAL JOIN clause must be used.
- DTable aliases must be used to qualify table names.
9Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?
EMPLOYEES Table:
- ASELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title); - BSELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id); - CSELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id); - DSELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs; - ESELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
- ASELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
10A 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?
- ASELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.employee_id = d.manager_id); - BSELECT d.department_id, e.first_name, e.last_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.employee_id = d.manager_id); - CSELECT d.department_id, e.first_name, e.last_name
FROM employees e, departments d
WHERE e.employee_id
RIGHT OUTER JOIN d.manager_id; - DSELECT d.department_id, e.first_name, e.last_name
FROM employees e
LEFT OUTER JOIN departments d
WHERE (e.department_id = d.department_id);
- ASELECT d.department_id, e.first_name, e.last_name
11If you select rows from two tables (employees and departments) using the outer join specified in the example, what will you get?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);- AAll employees that do not have a department_id assigned to them
- BAll employees including those that do not have a departement_id assigned to them
- CNo employees as the statement will fail
- DNone of the above
12 What is another name for a simple join or an inner join?
- AEquijoin
- BOuter Join
- CSelf Join
- DNonequijoin
13A NATURAL JOIN is based on:
- AColumns with the same datatype and width
- BColumns with the same name
- CColumns with the same name and datatype
- DTables with the same structure
14A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three)
- ASelf join
- BFull outer join
- CSimple join
- DEquijoin
- ENonequijoin
15Which of the following conditions will cause an error on a NATURAL JOIN?
- AWhen the NATURAL JOIN clause is based on all columns in the two tables that have the same name.
- BWhen you attempt to write it as an equijoin.
- CIf it selects rows from the two tables that have equal values in all matched columns.
- DIf the columns having the same names have different data types, then an error is returned.
Information
- Description
- Quiz on QUIZ 6, created by Claudia Piscociu on 14/01/2017.
- Author
- Claudia Piscociu
- Language
- English
- Views
- 8
- Rating
- No ratings yet Rate this quiz