Oracle_Test7

1. 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"?

SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';

SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR (ENAME, -1,1) = 'n';

SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, 1,1) = 'n';

SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, -1,1) = 'n';

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'.

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'.

2. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?

ORDER BY SALARY > 5000

GROUP BY SALARY > 5000

HAVING SALARY > 5000

WHERE SALARY > 5000

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.

Explanation

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.

3. Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?

46 and 45

46 and 45.93

50 and 45.93

50 and 45.9

45 and 45.93

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.

Explanation

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.

4. You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department _ id = d. department _id);

SELECT last_name, department_name FROM employees JOIN departments(+);

SELECT last_name, department_name ON (e. department_ id = d. departments_id); FROM employees(+) e JOIN departments d

SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

SELECT last_name, department_name FROM employees(+) , departments ON (e. department _ id = d. department _id);

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).

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).

5. Which two are character manipulation functions?

TRIM

REPLACE

TRUNC

TO_DATE

MOD

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.

Explanation

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.

Submit

6. Which clause should you use to exclude group results?

WHERE

HAVING

RESTRICT

GROUP BY

ORDER BY

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.

Explanation

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.

7. Which is a valid CREATE TABLE statement?

CREATE TABLE EMP9$# (empid number(2));

CREATE TABLE EMP*123 (empid number(2));

CREATE TABLE PACKAGE (packid number(2));

CREATE TABLE 1EMP_TEST (empid number(2));

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.

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.

8. 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?

To find the tax percentage for each of the employees.

To list the name, job id, and manager name for all the employees.

To find the name, salary, and department name of employees who are not working with Smith

To find the number of employees working for the Administrative department and earning less then 4000

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.

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.

9. Which of these set operators will not sort the rows? (Choose the best answer.)

INTERSECT

MINUS

UNION

UNION ALL

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.

Explanation

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.

10. Which SQL statement returns a numeric value?

SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;

SELECT ROUND(hire_date) FROM EMP;

SELECT sysdate-hire_date FROM EMP;

SELECT TO_NUMBER(hire_date + 7) FROM EMP;

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.

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.

11. If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)

The INTERSECT, because INTERSECT has higher precedence than MINUS

The MINUS, because MINUS has a higher precedence than INTERSECT.

The precedence is determined by the order in which they are specified.

It is not possible for a compound query to include both MINUS and INTERSECT

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.

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.

12. 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.)

The rows from each table will be grouped and within each group will be sorted on ENAME

The rows from each table will be grouped but not sorted.

The rows will not be grouped but will all be sorted on ENAME.

The rows will be neither grouped nor sorted.

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.

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.

13. Which two statements are true about WHERE and HAVING clauses?

A WHERE clause can be used to restrict both rows and groups.

A WHERE clause can be used to restrict rows only.

. A HAVING clause can be used to restrict both rows and groups.

A HAVING clause can be used to restrict groups only.

. A HAVING clause CANNOT be used in subqueries.

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.

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.

Submit

14. What is the result of this expression? Select one answer.

TRUNC(ROUND(FLOOR(CEIL(1.2)-.1) + .3445, 3), 2)

0.34

1.345

1.35

1.3445

1.34

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.

Explanation

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.

15. 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?

SELECT employee_id "Emp_id", emp_name "Employee",salary,employee_id "Mgr_id", emp_name "Manager"FROM employees WHERE salary > 4000;

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;

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;

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;

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.

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.

16. 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.)

Because the columns EMPNO and EMP_ID have different names

Because the columns EMP.HIRED and EX_EMP.HIRED are different data types

Because there are two columns in the first query and three columns in the second query

For all the reasons above

The query will succeed.

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.

Explanation

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.

17. In which case would you use a FULL OUTER JOIN?

You want all matched and unmatched data from only one table

You want all unmatched data from one table.

You want all matched data from both tables.

You want all unmatched data from both tables.

One of the tables has more data than the other.

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.

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.

Submit

18. 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?

SELECT emp_name, department_name, city FROM employees e JOIN departments dUSING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;

SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary > 10000;

SELECT emp_name, department_name, city FROM employees eNATURAL JOIN departments, locations WHERE salary > 10000;

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.

Explanation

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.

Submit

19. Which three are true regarding the use of outer joins?

You cannot use IN operator in a condition that involves an outerjoin

You use (+) on both sides of the WHERE condition to perform an outerjoin

. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator

You use an outerjoin to see only the rows that do not meet the join condition.

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.

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.

Submit

20. What is true about joining tables through an equijoin?

You can join a maximum of two tables through an equijoin.

You can join a maximum of two columns through an equijoin.

You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.

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.

Explanation

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.

Submit

Oracle_test7