Oracle Database Programming with SQL Section 6
1. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;
SELECT e.employee_id, e.last_name, e.first_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?
♦FROM employees e, departments d
♦SELECT e.employee_id, e.last_name, e.first_name, d.department_name
♦ORDER BY 4;
♦WHERE e.department_id = d.department_id
♦AND employees.department_id > 5000 (*)
♦SELECT e.employee_id, e.last_name, e.first_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 prefixed by table names in join syntax?
♦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
♦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?
♦5
♦15
♦10
♦50 (*)
♦15
♦10
♦50 (*)
4. You need to provide a list of the first 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 first. 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)
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
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?
♦SELECT e.employee_id, e.last_name, e.first_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;
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.first_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;
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.first_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;
(*)
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.first_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;
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?
♦3
♦0
♦2 (*)
♦1
♦0
♦2 (*)
♦1
6. What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?
♦An equijoin
♦A Cartesian product (*)
♦A self-join
♦An outer join
♦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)
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)
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?
♦SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
(*)
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;
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;
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 (+);
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
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)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee’s bonus as a calculation of salary times bonus. Which of the following queries should you issue?
♦SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)
♦SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
♦SELECT first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;
FROM employees, bonus NATURAL JOIN;
♦SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;
FROM employees, bonus
WHERE e.employee_id = b.employee_id;
9. The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?
♦Self join
♦Nonequi-Join
♦Outer join (*)
♦Equijoin
♦Nonequi-Join
♦Outer join (*)
♦Equijoin
10. Evaluate this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
Which join is evaluated first?
♦The join between the player table and the team table on MANAGER_ID
♦The self-join of the player table (*)
♦The join between the player table and the team table on TEAM_ID
♦The join between the player table and the team table on PLAYER_ID
♦The self-join of the player table (*)
♦The join between the player table and the team table on TEAM_ID
♦The join between the player table and the team table on PLAYER_ID
11. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
♦OR and =
♦AND and = (*)
♦IN and =
♦BETWEEN…AND… and IN
♦AND and = (*)
♦IN and =
♦BETWEEN…AND… and IN
12. You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?
♦A full outer join
♦It is not possible to join these two tables.
♦A non-equijoin (*)
♦An equijoin
♦It is not possible to join these two tables.
♦A non-equijoin (*)
♦An equijoin
13. Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
♦*
♦(+) (*)
♦=
♦+
♦(+) (*)
♦=
♦+
14. Which statement about outer joins is true?
♦The OR operator cannot be used to link outer join conditions. (*)
♦The tables must be aliased.
♦Outer joins are always evaluated before other types of joins in the query.
♦The FULL, RIGHT, or LEFT keyword must be included.
♦The tables must be aliased.
♦Outer joins are always evaluated before other types of joins in the query.
♦The FULL, RIGHT, or LEFT keyword must be included.
15. The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
♦True
♦False (*)
♦False (*)
16. When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause?
♦0
♦3
♦1
♦2 (*)
♦3
♦1
♦2 (*)
17. What is the result of a query that selects from two tables but includes no join condition?
♦A syntax error
♦A Cartesian product (*)
♦A selection of matched rows from both tables
♦A selection of rows from the first table only
♦A Cartesian product (*)
♦A selection of matched rows from both tables
♦A selection of rows from the first table only
18. Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?
♦The FROM clause represents the join criteria.
♦The JOIN keyword must be included.
♦The WHERE clause represents the join criteria. (*)
♦The ON keyword must be included.
♦The JOIN keyword must be included.
♦The WHERE clause represents the join criteria. (*)
♦The ON keyword must be included.
19. You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
Which query should you issue?
Which query should you issue?
♦SELECT first_name, last_name, sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
♦SELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
♦SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
♦SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
(*)
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
(*)
20. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a equi-join on those two tables?
♦It depends on how many rows have matching data in each of the two tables. (*)
♦5
♦50
♦10
♦5
♦50
♦10
21. 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
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)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee’s bonus as a calculation of salary times bonus. Which of the following queries should you issue?
♦SELECT first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;
FROM employees, bonus NATURAL JOIN;
♦SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;
FROM employees, bonus
WHERE e.employee_id = b.employee_id;
♦SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
♦SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)
22. The PATIENTS and DOCTORS tables contain these columns:
PATIENTS
PATIENT_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
PATIENTS
PATIENT_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DOCTORS
DOCTOR_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DOCTOR_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
You issue this statement:
SELECT patient_id, doctor_id
FROM patients, doctors;
SELECT patient_id, doctor_id
FROM patients, doctors;
Which result will this statement provide?
♦A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values (*)
♦A report containing each patient’s id value and his doctor’s id value
♦A report with NO duplicate PATIENT_ID or DOCTOR_ID values
♦A syntax error
♦A report containing each patient’s id value and his doctor’s id value
♦A report with NO duplicate PATIENT_ID or DOCTOR_ID values
♦A syntax error
23. You need to provide a list of the first 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 first. 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)
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
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?
♦SELECT e.employee_id, e.last_name, e.first_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;
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.first_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;
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;
♦SELECT e.employee_id, e.last_name, e.first_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;
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.first_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;
(*)
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;
(*)
24. Which statement about joining tables with a non-equijoin is false?
♦A WHERE clause must specify a column in one table that is compared to a column in the second table (*)
♦The number of join conditions required is always one less than the number of tables being joined
♦The columns being joined must have compatible data types
♦None of the above
♦None of the above
25. The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?
♦Self join
♦Nonequi-Join
♦Equijoin
♦Outer join (*)
♦Nonequi-Join
♦Equijoin
♦Outer join (*)
26. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
♦IN and =
♦BETWEEN…AND… and IN
♦AND and = (*)
♦OR and =
♦BETWEEN…AND… and IN
♦AND and = (*)
♦OR and =
27. To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
♦e.department_id = d.department_id
♦e.department_id(+) = d.department_id(+)
♦e.department_id = d.department_id(+)
♦e.department_id(+) = d.department_id (*)
♦e.department_id(+) = d.department_id(+)
♦e.department_id = d.department_id(+)
♦e.department_id(+) = d.department_id (*)
28. The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?
♦Natural join
♦Self-join
♦Equijoin
♦Outer join (*)
♦Self-join
♦Equijoin
♦Outer join (*)
29. Which operator is typically used in a nonequijoin?
♦>=, <=, or BETWEEN …AND (*)
♦NOT
♦*
♦OR
♦IN
♦NOT
♦*
♦OR
♦IN
30. Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
♦(+) (*)
♦=
♦+
♦ *
♦=
♦+
♦ *
31. Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
♦No, Oracle will return a Column Ambiguously Defined error. (*)
♦Yes, Oracle will resolve which department_id colum comes from which table.
♦Yes, there are no syntax errors in that statement
♦No, Oracle will not allow joins in the WHERE clause
♦Yes, Oracle will resolve which department_id colum comes from which table.
♦Yes, there are no syntax errors in that statement
♦No, Oracle will not allow joins in the WHERE clause
32. When must column names be prefixed by table names in join syntax?
♦Only when query speed and database performance is a concern
♦When the more than two tables participate in the join
♦When the same column name appears in more than one table of the query (*)
♦Never
♦When the more than two tables participate in the join
♦When the same column name appears in more than one table of the query (*)
♦Never
33. Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?
♦True (*)
♦False
♦False
34. What is the minimum number of join conditions required to join 5 tables together?
♦4 (*)
♦3
♦5
♦One more than the number of tables
♦3
♦5
♦One more than the number of tables
35. What happens when you create a Cartesian product?
♦All rows from one table are joined to all rows of another table (*)
♦The table is joined to another equal table
♦All rows that do not match in the WHERE clause are displayed
♦The table is joined to itself, one column to the next column, exhausting all possibilities
♦The table is joined to another equal table
♦All rows that do not match in the WHERE clause are displayed
♦The table is joined to itself, one column to the next column, exhausting all possibilities
36. Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?
♦The FROM clause represents the join criteria.
♦The WHERE clause represents the join criteria. (*)
♦The ON keyword must be included.
♦The JOIN keyword must be included.
♦The WHERE clause represents the join criteria. (*)
♦The ON keyword must be included.
♦The JOIN keyword must be included.
37. What is the result of a query that selects from two tables but includes no join condition?
♦A selection of matched rows from both tables
♦A selection of rows from the first table only
♦A Cartesian product (*)
♦A syntax error
♦A selection of rows from the first table only
♦A Cartesian product (*)
♦A syntax error
38. The CUSTOMERS and SALES tables contain these columns:
CUSTOMERS
CUST_ID NUMBER(10) PRIMARY KEY
COMPANY VARCHAR2(30)
LOCATION VARCHAR2(20)
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)
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?
♦SELECT cust_id, company, total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
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;
(*)♦SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id (+);
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 (+);
♦SELECT cust_id, company, total_sales
FROM customers, sales
WHERE cust_id = cust_id;
FROM customers, sales
WHERE cust_id = cust_id;
39. You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?
♦A non-equijoin (*)
♦It is not possible to join these two tables.
♦An equijoin
♦A full outer join
♦It is not possible to join these two tables.
♦An equijoin
♦A full outer join
40. Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
♦(+) (*)
♦+
♦*
♦=
♦+
♦*
♦=
41. The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?
♦Natural join
♦Self-join
♦Equijoin
♦Outer join (*)
♦Self-join
♦Equijoin
♦Outer join (*)
42. To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
♦e.department_id = d.department_id(+)
♦e.department_id(+) = d.department_id(+)
♦e.department_id = d.department_id
♦e.department_id(+) = d.department_id (*)
♦e.department_id(+) = d.department_id(+)
♦e.department_id = d.department_id
♦e.department_id(+) = d.department_id (*)
43. Nonequijoins are normally used with which of the following? (Choose two)
♦Ranges of numbers (*)
♦Ranges of rowids
♦Ranges of text
♦ranges of columns
♦Ranges of dates (*)
♦Ranges of rowids
♦Ranges of text
♦ranges of columns
♦Ranges of dates (*)
44. Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
♦BETWEEN…AND… and IN
♦IN and =
♦AND and = (*)
♦OR and =
♦IN and =
♦AND and = (*)
♦OR and =
45. Which statement about joining tables with a non-equijoin is false?
♦A WHERE clause must specify a column in one table that is compared to a column in the second table (*)
♦The number of join conditions required is always one less than the number of tables being joined
♦The columns being joined must have compatible data types
♦None of the above
InfoCad Blog 