1. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.fifirst_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;
Which clause contains a syntax error?
Mark for Review
(1) Points
FROM employees e, departments d
SELECT e.employee_id, e.last_name, e.fifirst_name, d.department_name
ORDER BY 4;
WHERE e.department_id = d.department_id
AND employees.department_id > 5000 (*)
2. When must column names be prefifixed by table names in join syntax? Mark for
Review
(1) Points
When the more than two tables participate in the join
Never
When the same column name appears in more than one table of the query (*)
Only when query speed and database performance is a concern
3. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you
perform a cartesian join on those two tables? Mark for Review
(1) Points
5
15
10
50 (*)
4. You need to provide a list of the fifirst and last names of all employees who work in the
Sales department who earned a bonus and had sales over $50,000. The company president
would like the sales listed starting with the highest amount fifirst. The EMPLOYEES table and
the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY
Which SELECT statement will accomplish this task?
Mark for Review
(1) Points
SELECT e.employee_id, e.last_name, e.fifirst_name, s.employee_id, s.bonus, s. sales
WHERE e.employee_id = s.employee_id
FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
SELECT e.employee_id, e.last_name, e.fifirst_name, s.employee_id, s.bonus, s.sales
FROM employees e, sales_dept s
ORDER BY sales DESC
WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;
SELECT e.employee_id, e.last_name, e.fifirst_name, s.employee_id, s.bonus, s. sales
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
(*)
SELECT e.employee_id, e.last_name, e.fifirst_name, s.employee_id, s.bonus, s. sales
ORDER BY sales DESC
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
5. When joining 3 tables in a SELECT statement, how many join conditions are needed in
the WHERE clause? Mark for Review
(1) Points
3
0
2 (*)
1
6. What is produced when a join condition isnot specifified in a multiple-table query using
Oracle proprietary Join syntax? Mark for Review
(1) Points
An equijoin
A Cartesian product (*)
A self-join
An outer join
7. The CUSTOMERS and SALES tables contain these columns:
CUSTOMERS
CUST_ID NUMBER(10) PRIMARY KEY
COMPANY VARCHAR2(30)
LOCATION VARCHAR2(20)
SALES
SALES_ID NUMBER(5) PRIMARY KEY
CUST_ID NUMBER(10) FOREIGN KEY
TOTAL_SALES NUMBER(30)
Which SELECT statement will return the customer ID, the company and the total sales?
Mark for Review
(1) Points
SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
(*)
SELECT cust_id, company, total_sales
FROM customers, sales
WHERE cust_id = cust_id;
SELECT cust_id, company, total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id (+);
8. You have the following EMPLOYEES table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
The BONUS table includes the following columns:
BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
No comments:
Post a Comment