Oracle_test7
This test is used to test your skills in joins , functions
- 1.
You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?
- A.
SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department _ id = d. department _id);
- B.
SELECT last_name, department_name FROM employees JOIN departments(+);
- C.
SELECT last_name, department_name ON (e. department_ id = d. departments_id); FROM employees(+) e JOIN departments d
- D.
SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
- E.
SELECT last_name, department_name FROM employees(+) , departments ON (e. department _ id = d. department _id);
Correct Answer
A. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department _ id = d. department _id);Explanation
The correct answer is "SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department _ id = d. department _id);". This query uses a LEFT OUTER JOIN to retrieve all employees, whether or not they have matching departments in the departments table. The "LEFT OUTER JOIN" keyword ensures that all records from the left table (employees) are included in the result, regardless of whether there is a match in the right table (departments).Rate this question:
-
- 2.
Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. EMPLOYEES(id,name,deptid) ,DEPT(deptid,dname,locid) , Location(locid,name) Which two SQL statements produce the name, department name, and the city of all the employees who earn more then 10000?
- A.
SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;
- B.
SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 JOIN ON (e.department_id = d.department id) AND (d.location_id =1.location_id) AND salary > 10000;
- C.
SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary > 10000;
- D.
. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000
- E.
SELECT emp_name, department_name, city FROM employees e NATURAL JOIN departments, locations WHERE salary > 10000;
Correct Answer(s)
A. SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;
D. . SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000Explanation
The two SQL statements that produce the name, department name, and city of all the employees who earn more than 10000 are:
1. SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;
2. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000.
These statements join the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables using the appropriate keys and filter the results based on the salary condition.Rate this question:
-
- 3.
Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?
- A.
SELECT employee_id "Emp_id", emp_name "Employee",salary,employee_id "Mgr_id", emp_name "Manager"FROM employees WHERE salary > 4000;
- B.
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;
- C.
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
- D.
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.mgr_id "Mgr_id", m.emp_name "manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
- E.
SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.mgr_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.employee_id = m.employee_id AND e.salary > 4000;
Correct Answer
C. SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;Explanation
The correct answer is the fourth option. This query selects the employee_id, emp_name, and salary from the employees table, and also selects the employee_id and emp_name from the same table, but with an alias of "Mgr_id" and "Manager" respectively. It then joins the employees table with itself using the condition e.mgr_id = m.employee_id, ensuring that the selected employee has a manager. Finally, it filters the results to only include employees with a salary greater than 4000.Rate this question:
-
- 4.
In which case would you use a FULL OUTER JOIN?
- A.
You want all matched and unmatched data from only one table
- B.
You want all unmatched data from one table.
- C.
You want all matched data from both tables.
- D.
You want all unmatched data from both tables.
- E.
One of the tables has more data than the other.
Correct Answer(s)
C. You want all matched data from both tables.
D. You want all unmatched data from both tables.Explanation
A FULL OUTER JOIN is used when you want to retrieve all matched data from both tables, as well as all unmatched data from both tables. This means that you want to include all rows from both tables, regardless of whether they have a match in the other table. This is useful when you need to combine data from two tables and want to include all available information, even if there are unmatched records.Rate this question:
-
- 5.
Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?
- A.
ORDER BY SALARY > 5000
- B.
GROUP BY SALARY > 5000
- C.
HAVING SALARY > 5000
- D.
WHERE SALARY > 5000
Correct Answer
D. WHERE SALARY > 5000Explanation
The WHERE clause is used in a SELECT statement to specify a condition that must be met for a row to be included in the result set. In this case, the condition is that the salary must be greater than 5000. Therefore, the correct answer is WHERE SALARY > 5000.Rate this question:
-
- 6.
Examine the structure of the EMPLOYEES, DEPARTMENTS, and TAX tables. EMPLOYEES (ID ,ename,sal,deptid) DEPARTMENTS(deptid,dname) TAX(minsal ,maxsal, taxpercent) For which situation would you use a nonequijoin query?
- A.
To find the tax percentage for each of the employees.
- B.
To list the name, job id, and manager name for all the employees.
- C.
To find the name, salary, and department name of employees who are not working with Smith
- D.
To find the number of employees working for the Administrative department and earning less then 4000
- E.
To display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned.
Correct Answer
A. To find the tax percentage for each of the employees.Explanation
A nonequijoin query would be used to find the tax percentage for each of the employees. This is because the TAX table contains information about the tax percentage based on the salary range. By joining the EMPLOYEES table with the TAX table using a nonequijoin, we can compare the salary of each employee with the minsal and maxsal columns in the TAX table to determine the applicable tax percentage for each employee.Rate this question:
-
- 7.
Which two are character manipulation functions?
- A.
TRIM
- B.
REPLACE
- C.
TRUNC
- D.
TO_DATE
- E.
MOD
Correct Answer(s)
A. TRIM
B. REPLACEExplanation
TRIM and REPLACE are character manipulation functions. TRIM is used to remove leading or trailing spaces from a string, while REPLACE is used to replace a specified substring with another substring in a string. Both functions assist in manipulating and modifying character data.Rate this question:
-
- 8.
What is true about joining tables through an equijoin?
- A.
You can join a maximum of two tables through an equijoin.
- B.
You can join a maximum of two columns through an equijoin.
- C.
You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
- D.
. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns
- E.
You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions
Correct Answer(s)
C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditionsExplanation
An equijoin is a type of join where the join condition is based on the equality of values in specified columns between two tables. The equijoin condition can be specified in either the SELECT or FROM clauses of a SELECT statement. This means that the equijoin condition can be included in the WHERE clause, JOIN clause, or ON clause of the query.
Additionally, it is possible to join more than two tables through an equijoin. In fact, you can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions. This allows for more complex queries involving multiple tables to be executed.Rate this question:
-
- 9.
Which clause should you use to exclude group results?
- A.
WHERE
- B.
HAVING
- C.
RESTRICT
- D.
GROUP BY
- E.
ORDER BY
Correct Answer
B. HAVINGExplanation
The HAVING clause should be used to exclude group results. The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on a condition that applies to groups of rows. It allows you to specify conditions that must be met by the aggregated values in order for the group to be included in the result set. This is different from the WHERE clause, which is used to filter individual rows before they are grouped. The HAVING clause is specifically designed to filter groups of rows after they have been grouped.Rate this question:
-
- 10.
Which three are true regarding the use of outer joins?
- A.
You cannot use IN operator in a condition that involves an outerjoin
- B.
You use (+) on both sides of the WHERE condition to perform an outerjoin
- C.
. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
- D.
You use an outerjoin to see only the rows that do not meet the join condition.
- E.
In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
Correct Answer(s)
A. You cannot use IN operator in a condition that involves an outerjoin
C. . You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.Explanation
The first statement is true because the IN operator cannot be used in a condition that involves an outer join. The second statement is false because the (+) notation is used to perform an outer join, not on both sides of the WHERE condition. The third statement is true because a condition involved in an outer join cannot be linked to another condition using the OR operator. The fourth statement is false because an outer join is used to see all the rows, including those that do not meet the join condition. The fifth statement is true because the (+) notation is used in the WHERE condition to perform an outer join on a column without matching rows.Rate this question:
-
- 11.
Which two statements are true about WHERE and HAVING clauses?
- A.
A WHERE clause can be used to restrict both rows and groups.
- B.
A WHERE clause can be used to restrict rows only.
- C.
. A HAVING clause can be used to restrict both rows and groups.
- D.
A HAVING clause can be used to restrict groups only.
- E.
. A HAVING clause CANNOT be used in subqueries.
Correct Answer(s)
B. A WHERE clause can be used to restrict rows only.
D. A HAVING clause can be used to restrict groups only.Explanation
The correct answer is that a WHERE clause can be used to restrict rows only, and a HAVING clause can be used to restrict groups only. The WHERE clause is used to filter rows based on a specified condition, while the HAVING clause is used to filter groups based on a specified condition. Therefore, the WHERE clause restricts the result set by filtering individual rows, while the HAVING clause restricts the result set by filtering groups of rows.Rate this question:
-
- 12.
Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?
- A.
46 and 45
- B.
46 and 45.93
- C.
50 and 45.93
- D.
50 and 45.9
- E.
45 and 45.93
Correct Answer
C. 50 and 45.93Explanation
The SQL statement SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; will display the values 50 and 45.93. The ROUND function rounds the number 45.953 to the nearest 10, resulting in 50. The TRUNC function truncates the number 45.936 to 2 decimal places, resulting in 45.93.Rate this question:
-
- 13.
The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?
- A.
SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';
- B.
SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';
- C.
SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, 1,1) = 'n';
- D.
SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, -1,1) = 'n';
Correct Answer
A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';Explanation
The correct answer is the first option: SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n'. This query selects the ENAME, length of ENAME, and the numeric position of the letter "a" in the ENAME column. It filters the results to only include employees whose ENAME ends with the letter "n" by using the SUBSTR function to extract the last character of ENAME and comparing it to 'n'.Rate this question:
-
- 14.
Which SQL statement returns a numeric value?
- A.
SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
- B.
SELECT ROUND(hire_date) FROM EMP;
- C.
SELECT sysdate-hire_date FROM EMP;
- D.
SELECT TO_NUMBER(hire_date + 7) FROM EMP;
Correct Answer
C. SELECT sysdate-hire_date FROM EMP;Explanation
The SQL statement "SELECT sysdate-hire_date FROM EMP;" returns a numeric value because it calculates the difference between the current date (sysdate) and the hire date, which are both numeric values. This calculation will result in a numeric value representing the number of days between the two dates.Rate this question:
-
- 15.
Which is a valid CREATE TABLE statement?
- A.
CREATE TABLE EMP9$# (empid number(2));
- B.
CREATE TABLE EMP*123 (empid number(2));
- C.
CREATE TABLE PACKAGE (packid number(2));
- D.
CREATE TABLE 1EMP_TEST (empid number(2));
Correct Answer
A. CREATE TABLE EMP9$# (empid number(2));Explanation
The answer is "CREATE TABLE EMP9$# (empid number(2));". This is a valid CREATE TABLE statement because it follows the correct syntax for creating a table in a database. The table name "EMP9$#" is alphanumeric and does not contain any special characters that are not allowed. The column "empid" is defined with a data type of "number(2)", which is also valid.Rate this question:
-
- 16.
What is the result of this expression? Select one answer. TRUNC(ROUND(FLOOR(CEIL(1.2)-.1) + .3445, 3), 2)
- A.
0.34
- B.
1.345
- C.
1.35
- D.
1.3445
- E.
1.34
Correct Answer
E. 1.34Explanation
The expression starts by calculating the ceiling of 1.2, which is 2. Then, it subtracts 0.1 from the result, giving 1.9. The floor of 1.9 is 1. Next, it adds 0.3445 to 1, resulting in 1.3445. The round function is then applied to this value, rounding it to 3 decimal places, which gives 1.345. Finally, the trunc function is used to truncate the value to 2 decimal places, resulting in 1.34.Rate this question:
-
- 17.
Which of these set operators will not sort the rows? (Choose the best answer.)
- A.
INTERSECT
- B.
MINUS
- C.
UNION
- D.
UNION ALL
Correct Answer
D. UNION ALLExplanation
The UNION ALL operator combines the result sets of two or more SELECT statements into a single result set, including all rows from all SELECT statements. However, it does not sort the rows in any specific order. Therefore, the UNION ALL operator will not sort the rows.Rate this question:
-
- 18.
If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)
- A.
The INTERSECT, because INTERSECT has higher precedence than MINUS
- B.
The MINUS, because MINUS has a higher precedence than INTERSECT.
- C.
The precedence is determined by the order in which they are specified.
- D.
It is not possible for a compound query to include both MINUS and INTERSECT
Correct Answer
C. The precedence is determined by the order in which they are specified.Explanation
The explanation for the given correct answer is that the precedence of the operators in a compound query is determined by the order in which they are specified. Therefore, if a compound query contains both a MINUS and an INTERSECT operator, the one that is specified first will be applied first.Rate this question:
-
- 19.
Consider this compound query: select empno, hired from emp union all select emp_id,hired,fired from ex_emp; The columns EMP.EMPNO and EX_EMP.EMP_ID are integer; the column EMP.HIRED is timestamp; the columns EX_EMP.HIRED and EX_EMP.FIRED are date. Why will the statement fail? (Choose the best answer.)
- A.
Because the columns EMPNO and EMP_ID have different names
- B.
Because the columns EMP.HIRED and EX_EMP.HIRED are different data types
- C.
Because there are two columns in the first query and three columns in the second query
- D.
For all the reasons above
- E.
The query will succeed.
Correct Answer
C. Because there are two columns in the first query and three columns in the second queryExplanation
The statement will fail because the first query selects two columns (empno and hired) while the second query selects three columns (emp_id, hired, and fired). The number of columns selected in each query must match in a union operation.Rate this question:
-
- 20.
Study this statement: select ename from emp union all select ename from ex_emp; In what order will the rows be returned? (Choose the best answer.)
- A.
The rows from each table will be grouped and within each group will be sorted on ENAME
- B.
The rows from each table will be grouped but not sorted.
- C.
The rows will not be grouped but will all be sorted on ENAME.
- D.
The rows will be neither grouped nor sorted.
Correct Answer
B. The rows from each table will be grouped but not sorted.Explanation
The statement "select ename from emp union all select ename from ex_emp" will return the rows from each table grouped together, but they will not be sorted.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 21, 2023Quiz Edited by
ProProfs Editorial Team -
Jan 21, 2010Quiz Created by
Sudha_test
Back to top