Grand Test SQL

1. You want to increase the size of the PRODUCT_TYPE column, declared as a VARCHAR(5) column, to VARCHAR2(10) in the SALES table. Which of the following commands is useful for this purpose?

Alter table sales add (product_type varchar2(10));

Alter table sales modify product_type varchar2(10));

Alter table sales set unused column product_type varchar2(10));

Alter table sales drop column product_type;

The correct answer is "alter table sales modify product_type varchar2(10));". This command is used to modify the structure of a table, including changing the data type and size of a column. In this case, it is used to increase the size of the PRODUCT_TYPE column from VARCHAR(5) to VARCHAR2(10).

Explanation

The correct answer is "alter table sales modify product_type varchar2(10));". This command is used to modify the structure of a table, including changing the data type and size of a column. In this case, it is used to increase the size of the PRODUCT_TYPE column from VARCHAR(5) to VARCHAR2(10).

2. You want to obtain data from the ORDERS table, which contains three columns:
CUSTOMER, ORDER_DATE, and ORDER_AMT.

Which of the following choices identifies how you would formulate the where clause in a query against the ORDERS table when you want to see orders for customer LESLIE that exceed 2700?

Where customer = 'LESLIE';

Where customer = 'LESLIE' and order_amt < 2700;

Where customer = 'LESLIE' or order_amt > 2700;

Where customer = 'LESLIE' and order_amt > 2700;

The correct answer is "where customer = 'LESLIE' and order_amt > 2700;" because it specifies that the query should retrieve orders for the customer named LESLIE and only include orders with an order amount greater than 2700. This ensures that only the desired orders are included in the query results.

Explanation

The correct answer is "where customer = 'LESLIE' and order_amt > 2700;" because it specifies that the query should retrieve orders for the customer named LESLIE and only include orders with an order amount greater than 2700. This ensures that only the desired orders are included in the query results.

3. Review the following SQL statement:
SQL> select a.deptno, a.job, b.loc, sum(a.sal)
           from emp a, dept b
           where a.deptno = b.deptno
           group by a.deptno, a.job, b.loc         order by sum(a.sal);
Which of the following choices identifies the column upon which the order of output from this query will be returned?

DEPTNO

JOB

LOC

Sum(A.SAL)

The order of output from this query will be returned based on the column "sum(A.SAL)". This is because the "order by" clause is used to specify the column by which the result set should be sorted in ascending or descending order. In this case, the "sum(a.sal)" column is used in the "order by" clause, so the output will be sorted based on the sum of salaries in ascending order.

Explanation

The order of output from this query will be returned based on the column "sum(A.SAL)". This is because the "order by" clause is used to specify the column by which the result set should be sorted in ascending or descending order. In this case, the "sum(a.sal)" column is used in the "order by" clause, so the output will be sorted based on the sum of salaries in ascending order.

4. Having can exist with out Group by clause

True

False

The statement "Having can exist without Group by clause" is false. The "Having" clause is used in conjunction with the "Group by" clause in SQL queries. It is used to filter the results of a query based on a condition applied to a group of rows. Without the "Group by" clause, there is no grouping of rows, and therefore the "Having" clause cannot be used.

Explanation

The statement "Having can exist without Group by clause" is false. The "Having" clause is used in conjunction with the "Group by" clause in SQL queries. It is used to filter the results of a query based on a condition applied to a group of rows. Without the "Group by" clause, there is no grouping of rows, and therefore the "Having" clause cannot be used.

5. For a certain row in a table, a VARCHAR2 column contains the value SMITHY, padded to the right with seven spaces by the application.

When the length( ) function processes that column value, what will be the value returned?

6

13

30

60

When the length( ) function processes the column value "SMITHY ", it will count all the characters including the padded spaces. Therefore, the value returned will be 13.

Explanation

When the length( ) function processes the column value "SMITHY ", it will count all the characters including the padded spaces. Therefore, the value returned will be 13.

6. Group by clause is used to exclude the group results

True

False

The group by clause is used to group rows based on a specified column or columns. It does not exclude the group results, but rather it combines them and allows for aggregation functions to be applied to each group. Therefore, the statement that the group by clause is used to exclude the group results is false.

Explanation

The group by clause is used to group rows based on a specified column or columns. It does not exclude the group results, but rather it combines them and allows for aggregation functions to be applied to each group. Therefore, the statement that the group by clause is used to exclude the group results is false.

7. You need to filter return data from your query on the PROFITS table according to the PRODUCT_NAME column.

Which of the following clauses in your SQL query will contain reference to the appropriate filter criteria?

Select

From

Where

Having

The WHERE clause in the SQL query is used to filter the return data from the query based on specific conditions. In this case, the appropriate filter criteria is the PRODUCT_NAME column, so the WHERE clause will contain the reference to this column in order to filter the data accordingly.

Explanation

The WHERE clause in the SQL query is used to filter the return data from the query based on specific conditions. In this case, the appropriate filter criteria is the PRODUCT_NAME column, so the WHERE clause will contain the reference to this column in order to filter the data accordingly.

8. ) A partial listing of output from the PROFITS table is shown in the following code block:

PRODUCT_NAME PRODUCT_TYPE QTR_END_DATE        PROFIT
------------ ------------ ------------ ------------- BARNEY           DOLL TOY          31-MAR-2001     6575430.30
GAS GRILL       APPLIANCE       31-MAR-2001     1234023.88
PENCIL              OFFICE                30-JUN-2001       34039.99

Which of the following choices identifies the proper setup of a where clause for a query that calculates the total profits for all appliances sold in the six-month period from January 1 to June 30, 2001?

. where product_name = 'GAS GRILL' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';

Where product_type = 'APPLIANCE' and product_name = 'GAS GRILL' and qtr_end_date ='31-JAN-2001' or '30-JUN-2001';

. where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';

Where product_name = 'GAS GRILL' and qtr_end_date = '01-JAN-2001' or '30-JUN-2001';

The correct answer is "where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001'". This is the proper setup of a where clause for the query because it specifies that the product type should be 'APPLIANCE' and the quarter end date should be between '01-JAN-2001' and '01-JUL-2001'. This ensures that only appliances sold within the specified six-month period are included in the calculation of total profits.

Explanation

The correct answer is "where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001'". This is the proper setup of a where clause for the query because it specifies that the product type should be 'APPLIANCE' and the quarter end date should be between '01-JAN-2001' and '01-JUL-2001'. This ensures that only appliances sold within the specified six-month period are included in the calculation of total profits.

9. You are manipulating data in Oracle. Which of the following is not a SQL command?

. select * from dual;

Update emp set empno = 6543 where ename = 'SMITHERS';

Set define ?

Create table employees (empid varchar2(10) primary key);

The SQL command "set define ?" is not a valid SQL command. This command is used in Oracle SQL*Plus, which is a command-line tool used for executing SQL commands and scripts. It is used to define a substitution variable in SQL*Plus. However, it is not a standard SQL command that can be used in other SQL environments or database systems.

Explanation

The SQL command "set define ?" is not a valid SQL command. This command is used in Oracle SQL*Plus, which is a command-line tool used for executing SQL commands and scripts. It is used to define a substitution variable in SQL*Plus. However, it is not a standard SQL command that can be used in other SQL environments or database systems.

10. Use the following code block to answer the question:
SQL> select _____(-45) as output from dual;

OUTPUT
------
-45

Which of the following choices identifies a single-row function that could not have produced this output?

Abs( )

Ceil( )

Floor( )

Round( )

The abs() function in SQL returns the absolute value of a number, so it would not be able to produce a negative output like -45. ceil(), floor(), and round() functions can all produce negative outputs, so they could potentially be the correct answer.

Explanation

The abs() function in SQL returns the absolute value of a number, so it would not be able to produce a negative output like -45. ceil(), floor(), and round() functions can all produce negative outputs, so they could potentially be the correct answer.

11. You are developing a query on the PROFITS table, which stores profit information by company region, product type, and quarterly time period.

Which of the following choices identifies a query that will obtain the average profits greater than $100,000 by product type, region, and time period?

Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 group by region, prod_type, period;

Select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 order by region, prod_type, period;

Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000;

Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) < 100000;

The correct answer is to select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000. This query uses the GROUP BY clause to group the data by region, product type, and time period. The AVG function is then used to calculate the average profit for each group. The HAVING clause is used to filter the results and only include groups where the average profit is greater than $100,000.

Explanation

The correct answer is to select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000. This query uses the GROUP BY clause to group the data by region, product type, and time period. The AVG function is then used to calculate the average profit for each group. The HAVING clause is used to filter the results and only include groups where the average profit is greater than $100,000.

12. Group by column need not be a part of the select column list

True

False

In SQL, when using the GROUP BY clause, it is not necessary for the column being grouped by to be included in the select column list. The purpose of the GROUP BY clause is to group rows based on a specific column or columns, and the select column list is used to determine which columns to display in the result set. Including the group by column in the select column list is optional and depends on whether you want to display the grouped column in the result set or not. Therefore, the statement "Group by column need not be a part of the select column list" is true.

Explanation

In SQL, when using the GROUP BY clause, it is not necessary for the column being grouped by to be included in the select column list. The purpose of the GROUP BY clause is to group rows based on a specific column or columns, and the select column list is used to determine which columns to display in the result set. Including the group by column in the select column list is optional and depends on whether you want to display the grouped column in the result set or not. Therefore, the statement "Group by column need not be a part of the select column list" is true.

13. A table called TEST contains two columns:
TESTCOL, defined as a NUMBER(10) datatype; and
TESTCOL_2, defined as a VARCHAR2(10) datatype.

You issue the following statement on Oracle:
insert into test (testcol, testcol_2) values (null, 'FRANCIS').

You then issue the following query against that table:
select nvl(testcol,'EMPTY') as testcol from test where testcol_2 = 'FRANCIS'.

Which of the following choices correctly identifies the result?

Oracle returns zero as the result

Oracle returns EMPTY as the result.

Oracle returns NULL as the result

Oracle returns an error as the result

The statement "insert into test (testcol, testcol_2) values (null, 'FRANCIS')" inserts a null value into the TESTCOL column and the value 'FRANCIS' into the TESTCOL_2 column of the TEST table. The query "select nvl(testcol,'EMPTY') as testcol from test where testcol_2 = 'FRANCIS'" retrieves the value of the TESTCOL column using the NVL function, which returns the value of TESTCOL if it is not null, otherwise it returns 'EMPTY'. Since the value of TESTCOL is null in this case, the NVL function returns 'EMPTY' as the result.

Explanation

The statement "insert into test (testcol, testcol_2) values (null, 'FRANCIS')" inserts a null value into the TESTCOL column and the value 'FRANCIS' into the TESTCOL_2 column of the TEST table. The query "select nvl(testcol,'EMPTY') as testcol from test where testcol_2 = 'FRANCIS'" retrieves the value of the TESTCOL column using the NVL function, which returns the value of TESTCOL if it is not null, otherwise it returns 'EMPTY'. Since the value of TESTCOL is null in this case, the NVL function returns 'EMPTY' as the result.

14. Use the following output to answer the question

ENAME
----------
SMITH-dog-
ALLEN-dog-
WARD-dog-d
JONES-dog-
MARTIN-dog
BLAKE-dog-
CLARK-dog-
SCOTT-dog-
KING-dog-d
TURNER-dog
ADAMS-dog-
JAMES-dog-
FORD-dog-d
MILLER-dog

Which of the following choices identifies the SQL statement that produced this output?

Select trim(trailing '-dog' from ename) as ename from emp;

Select rpad(ename, 10, '-dog') as ename from emp;

Select substr(ename, 1, 10) as ename from emp;

Select lpad(ename, 10, '-dog') as ename from emp;

The correct answer is "select rpad(ename, 10, '-dog') as ename from emp;". This is because the output shows each employee name followed by "-dog". The RPAD function is used to right-pad the employee name with "-dog" to a total length of 10 characters.

Explanation

The correct answer is "select rpad(ename, 10, '-dog') as ename from emp;". This is because the output shows each employee name followed by "-dog". The RPAD function is used to right-pad the employee name with "-dog" to a total length of 10 characters.

15. Use the following code block to answer the question:

SQL> select deptno, job, avg(sal) from emp group by deptno, job having avg(sal) > ( select sal from emp where ename = 'MARTIN');

Which of the following choices identifies the type of subquery used in the preceding statement?

A single-row subquery

A multirow subquery

A from clause subquery

A multicolumn subquery

The given code block is using a single-row subquery because it is comparing the average salary (avg(sal)) of each department and job combination to the salary of an employee named 'MARTIN'. This indicates that the subquery is expected to return a single value, which is then used for comparison in the outer query.

Explanation

The given code block is using a single-row subquery because it is comparing the average salary (avg(sal)) of each department and job combination to the salary of an employee named 'MARTIN'. This indicates that the subquery is expected to return a single value, which is then used for comparison in the outer query.

16. You are defining an outer join statement. Which of the following choices is true concerning outer join statements?

Outer join operations allow NULL values from one of the tables, but it is still necessary to specify equality comparisons to join those tables. This means that even though NULL values are allowed, the join will only occur when there is a match based on the specified equality condition. Without specifying this condition, the join will not be performed correctly, and the desired results may not be obtained.

Explanation

Outer join operations allow NULL values from one of the tables, but it is still necessary to specify equality comparisons to join those tables. This means that even though NULL values are allowed, the join will only occur when there is a match based on the specified equality condition. Without specifying this condition, the join will not be performed correctly, and the desired results may not be obtained.

17. The JOB table contains three columns: JOB_NAME, JOB_DESC, and JOB_WAGE. You insert a new row into the JOB_DESC table using the following command:

SQL> insert into job (job_name, job_desc) 2 values ('LACKEY','MAKES COFFEE');

Later, you query the table, and receive the following result:

SQL> select * from job where job_name = 'LACKEY';

JOB_NAME JOB_DESC JOB_WAGE
--------- ------------ --------
LACKEY MAKES COFFEE 35
Which of the following choices identifies how JOB_WAGE was populated with data?

The row for LACKEY in the JOB table already existed with JOB_WAGE set to 35.

The values clause in the insert statement contained a hidden value that was added when the row was added.

The only possible explanation is that a later update statement issued against the JOB table added the JOB_WAGE value.

The correct answer is that a default clause on the JOB_WAGE column, defined when the table was created, specified the value when the row was inserted. This means that when the row was inserted into the JOB table, the default value for the JOB_WAGE column was automatically assigned to the new row.

Explanation

The correct answer is that a default clause on the JOB_WAGE column, defined when the table was created, specified the value when the row was inserted. This means that when the row was inserted into the JOB table, the default value for the JOB_WAGE column was automatically assigned to the new row.

18. You want to increase the size of a non-NULL VARCHAR2(5) column to VARCHAR2(10). Which of the following steps must be accomplished after executing the appropriate alter table command?

Set the VARCHAR2 column to NULL for all rows.

Create a temporary location for VARCHAR2 data.

Copy the column records from the temporary location back to the main table.

Nothing; the statement is executed automatically.

not-available-via-ai

Explanation

not-available-via-ai

19. You are defining SQL queries in Oracle. Which of the following database objects cannot be referenced directly from a select statement?

Tables

Sequences

Indexes

Views

Indexes cannot be referenced directly from a select statement in Oracle. While tables, sequences, and views can be directly referenced in a select statement, indexes are used to improve the performance of queries by providing faster access to data in a table. They are not directly used in select statements but are utilized by the Oracle optimizer to optimize the execution of queries.

Explanation

Indexes cannot be referenced directly from a select statement in Oracle. While tables, sequences, and views can be directly referenced in a select statement, indexes are used to improve the performance of queries by providing faster access to data in a table. They are not directly used in select statements but are utilized by the Oracle optimizer to optimize the execution of queries.

20. You issue the following statement in SQL*Plus:

SQL> select ceil(256.342), floor(256.342), round(256.342,0), trunc(256.342) from dual;
Which of the following choices identifies the function that will not return 256 as the result?

Ceil( )

Floor( )

.round( )

Trunc( )

The ceil() function returns the smallest integer greater than or equal to a given number. In this case, ceil(256.342) will return 257, not 256. Therefore, the ceil() function will not return 256 as the result.

Explanation

The ceil() function returns the smallest integer greater than or equal to a given number. In this case, ceil(256.342) will return 257, not 256. Therefore, the ceil() function will not return 256 as the result.

21. Examine the following output from SQL*Plus:

PRODUCT.ID PRODUCT.NAME BOX.LOCATION
---------- ------------ ------------
578-X      WIDGET       IDAHO
              TENNESSEE
456-Y      WIDGET

Which of the following choices identifies the type of query that likely produced this result?

Full outer join

Left outer join

Right outer join

Equijoin

The output shows that there are two rows with different product IDs and names. The first row has a box location in Idaho, while the second row does not have a box location specified. This indicates that the query likely used a full outer join, which returns all rows from both tables, including unmatched rows. In this case, the unmatched row from the second table does not have a box location.

Explanation

The output shows that there are two rows with different product IDs and names. The first row has a box location in Idaho, while the second row does not have a box location specified. This indicates that the query likely used a full outer join, which returns all rows from both tables, including unmatched rows. In this case, the unmatched row from the second table does not have a box location.

22. The company's sales database has two tables. The first, PROFITS, stores the amount of profit made on products sold by the different corporate regions in different quarters.

The second, REGIONS, stores the name of each departmental region, the headquarter location for that region, and the name of the region's vice president.

Which of the following queries will obtain total profits on toys for regions headed by SMITHERS, FUJIMORI, and LAKKARAJU?

The correct answer is the query "select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';". This query selects the sum of profits from the PROFITS table where the region is in the set of regions obtained from the REGIONS table where the reg_head is either 'SMITHERS', 'FUJIMORI', or 'LAKKARAJU', and the product is 'TOYS'. This query specifically filters for the regions headed by SMITHERS, FUJIMORI, and LAKKARAJU and calculates the total profit for the 'TOYS' product in those regions.

Explanation

The correct answer is the query "select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';". This query selects the sum of profits from the PROFITS table where the region is in the set of regions obtained from the REGIONS table where the reg_head is either 'SMITHERS', 'FUJIMORI', or 'LAKKARAJU', and the product is 'TOYS'. This query specifically filters for the regions headed by SMITHERS, FUJIMORI, and LAKKARAJU and calculates the total profit for the 'TOYS' product in those regions.

23. You issue a query in the Oracle database.

Which of the following choices does not identify a component of your query if you want the query to execute a mathematical operation on user-defined static expressions?

Column clause

Table clause

The DUAL table

The where clause

The DUAL table is not a component of the query that would execute a mathematical operation on user-defined static expressions. The DUAL table is a special one-row, one-column table in Oracle that is typically used for selecting expressions or calling functions without accessing any tables. It does not directly relate to executing mathematical operations on user-defined static expressions.

Explanation

The DUAL table is not a component of the query that would execute a mathematical operation on user-defined static expressions. The DUAL table is a special one-row, one-column table in Oracle that is typically used for selecting expressions or calling functions without accessing any tables. It does not directly relate to executing mathematical operations on user-defined static expressions.

24.

25)Two tables, PRODUCT and STORAGE_BOX, exist in a database.

Individual products are listed in the table by unique ID number, product name, and the box a particular product is stored in.

Individual storage boxes (identified by number) listed in the other table can contain many products, but each box can be found in only one location.

Which of the following statements will correctly display the product ID, name, and box location of all widgets in this database?

Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.prod_id = b.prod_id and prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc fromproduct p, storage_box b where prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET';

Select prod_id, prod_name, box_loc from product, storage_box where stor_box_num = stor_box_num and prod_name = 'WIDGET'

The correct answer is "select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET';" This statement correctly joins the PRODUCT and STORAGE_BOX tables using the condition p.stor_box_num = b.stor_box_num, which ensures that the product and storage box being referenced are related. It also includes the condition p.prod_name = 'WIDGET', which filters the results to only include products with the name 'WIDGET'. This statement will display the product ID, name, and box location of all widgets in the database.

Explanation

The correct answer is "select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET';" This statement correctly joins the PRODUCT and STORAGE_BOX tables using the condition p.stor_box_num = b.stor_box_num, which ensures that the product and storage box being referenced are related. It also includes the condition p.prod_name = 'WIDGET', which filters the results to only include products with the name 'WIDGET'. This statement will display the product ID, name, and box location of all widgets in the database.

25. ) You issue the following select statement in Oracle:

SQL> select e.empno, e.ename, d.loc from emp e, dept d
where e.deptno = d.deptno and substr(e.ename,1,1) = 'S';

Which of the following statements identifies an ANSI-compliant equivalent statement usable on the Oracle database?

Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';

Select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S';

Select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';

Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';

The correct answer is "select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S'." This statement is the ANSI-compliant equivalent because it uses the "JOIN" keyword to join the "emp" and "dept" tables based on the "deptno" column. It also includes the condition "substr(emp.ename,1,1) = 'S'" to filter the results where the first letter of the "ename" column is 'S'.

Explanation

The correct answer is "select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S'." This statement is the ANSI-compliant equivalent because it uses the "JOIN" keyword to join the "emp" and "dept" tables based on the "deptno" column. It also includes the condition "substr(emp.ename,1,1) = 'S'" to filter the results where the first letter of the "ename" column is 'S'.

26. Two tables, PRODUCT and STORAGE_BOX, exist in a database.

Individual products are listed in the table by unique ID number, product name, and the box a particular product is stored in.

Individual storage boxes (identified by number) listed in the other table can contain many products, but the box can be found in only one location.

Which of the following statements will correctly display the product ID, name, and box location of all widgets in this database that have or have not been assigned to a storage box?

Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'(+);

Select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET';

Select p.prod_id, p.prod_name, b.box_loc from product p full outer join storage_box b on p.stor_box_num = b.stor_box_num where b.stor_box_num is NULL;

The correct answer is select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'. This statement uses a left outer join to combine the PRODUCT and STORAGE_BOX tables, ensuring that all products are included in the result set, even if they have not been assigned to a storage box. The WHERE clause filters the result set to only include widgets.

Explanation

The correct answer is select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'. This statement uses a left outer join to combine the PRODUCT and STORAGE_BOX tables, ensuring that all products are included in the result set, even if they have not been assigned to a storage box. The WHERE clause filters the result set to only include widgets.

27. Use the contents of the EMP table shown in the following code block to answer the question: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- -------- --------- ----- --------- ---- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Which of the following choices identifies the third employee listed from the top of the output from the following SQL command:

select ename, sal from emp where job = 'SALESMAN' order by empno desc?

ALLEN

MARTIN

TURNER

WARD

The correct answer is WARD. The SQL command selects the employees with the job title 'SALESMAN' from the EMP table and orders them in descending order based on their employee number (empno). The third employee listed from the top of the output is WARD.

Explanation

The correct answer is WARD. The SQL command selects the employees with the job title 'SALESMAN' from the EMP table and orders them in descending order based on their employee number (empno). The third employee listed from the top of the output is WARD.

28. Use the contents of the EMP table shown in the following code block to answer the question: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- -------- --------- ----- --------- ---- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER 7782 23-JAN-82 1300 10

Which of the following choices identifies the value that would be returned from the following query: select count(mgr) from emp where deptno = 10?

One

Two

Three

NULL

The query "select count(mgr) from emp where deptno = 10" counts the number of rows in the EMP table where the deptno is equal to 10 and the mgr column is not null. Since there are two rows in the EMP table where deptno is equal to 10 and the mgr column is not null (rows with empno 7782 and 7934), the value returned from the query would be Two.

Explanation

The query "select count(mgr) from emp where deptno = 10" counts the number of rows in the EMP table where the deptno is equal to 10 and the mgr column is not null. Since there are two rows in the EMP table where deptno is equal to 10 and the mgr column is not null (rows with empno 7782 and 7934), the value returned from the query would be Two.

29. You issue the following statement in SQL*Plus:
SQL> select ceil(-97.342), floor(-97.342), round(-97.342,0),
trunc(-97.342) from dual;

Which of the following choices identifies the function that will not return -97 as the result?

Ceil( )

Floor( )

Round( )

Trunc( )

The floor() function returns the largest integer that is less than or equal to a given number. In this case, the given number is -97.342. The floor() function will return -98 as the result, not -97. Therefore, the floor() function will not return -97 as the result.

Explanation

The floor() function returns the largest integer that is less than or equal to a given number. In this case, the given number is -97.342. The floor() function will return -98 as the result, not -97. Therefore, the floor() function will not return -97 as the result.

30. You want to join information from three tables as part of developing a report.

The tables are EMP, DEPT, and SALGRADE.

Only records corresponding to employee, department location, and salary range are required for employees in grades ten and higher for the organization.

How many comparison operations are required for this query?

.Two

Three

Four

Five

To join information from three tables (EMP, DEPT, and SALGRADE), we need to perform three comparison operations. The first comparison operation is to select records for employees in grades ten and higher. The second comparison operation is to match the employee records with their corresponding department location. The third comparison operation is to match the employee records with their corresponding salary range. Therefore, a total of three comparison operations are required for this query.

Explanation

To join information from three tables (EMP, DEPT, and SALGRADE), we need to perform three comparison operations. The first comparison operation is to select records for employees in grades ten and higher. The second comparison operation is to match the employee records with their corresponding department location. The third comparison operation is to match the employee records with their corresponding salary range. Therefore, a total of three comparison operations are required for this query.

31. You issue the following command in Oracle:
SQL> select e.ename, a.street_address, a.city, a.state, a.post_code
from emp e, addr a
where e.empno = a.empno (+) and a.state = 'TEXAS';

Which of the following choices shows the ANSI/ISO equivalent statement?

The correct answer is ". select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = 'TEXAS'". This is the ANSI/ISO equivalent statement because it uses the "LEFT OUTER JOIN" syntax to join the "emp" and "addr" tables, and specifies the join condition using "ON". It also includes the condition "a.state = 'TEXAS'" to filter the results based on the state column in the "addr" table.

Explanation

The correct answer is ". select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = 'TEXAS'". This is the ANSI/ISO equivalent statement because it uses the "LEFT OUTER JOIN" syntax to join the "emp" and "addr" tables, and specifies the join condition using "ON". It also includes the condition "a.state = 'TEXAS'" to filter the results based on the state column in the "addr" table.

32. You want to reduce the size of a non-NULL NUMBER(10) column to NUMBER(6). Which of the following steps must be completed after the appropriate alter table command is issued?

Copy column records to a temporary storage location

Set the NUMBER column to NULL for all rows.

Create a temporary location for NUMBER data.

Copy column records from the temporary location back to the main table.

After issuing the appropriate alter table command to reduce the size of the non-NULL NUMBER(10) column to NUMBER(6), the step that must be completed is to copy the column records from the temporary location back to the main table. This is necessary to ensure that the data is preserved and restored correctly after the alteration.

Explanation

After issuing the appropriate alter table command to reduce the size of the non-NULL NUMBER(10) column to NUMBER(6), the step that must be completed is to copy the column records from the temporary location back to the main table. This is necessary to ensure that the data is preserved and restored correctly after the alteration.

33. You are creating tables in the Oracle database. Which of the following statements identifies a table-creation statement that is not valid?

Create table cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));

Create table my_cats as select * from cats where owner = 'ME';

Create temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));

Create table cats-over-5-lbs as select c_name, c_weight from cats where c_weight > 5;

The statement "create temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));" is not valid because it is missing the keyword "table" before the table name "temp_cats". In Oracle database, the correct syntax for creating a table is "create table table_name (column1 datatype, column2 datatype, ...);"

Explanation

The statement "create temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));" is not valid because it is missing the keyword "table" before the table name "temp_cats". In Oracle database, the correct syntax for creating a table is "create table table_name (column1 datatype, column2 datatype, ...);"

34. You issue the following query in Oracle:
SQL> select months_between('15-MAR-83', '15-MAR-97') from dual;

What will Oracle return?

14

-14

168

-168

The query is using the function "months_between" to calculate the number of months between two dates: '15-MAR-83' and '15-MAR-97'. The function returns a negative value because the second date is earlier than the first date. Therefore, Oracle will return -168 as the result.

Explanation

The query is using the function "months_between" to calculate the number of months between two dates: '15-MAR-83' and '15-MAR-97'. The function returns a negative value because the second date is earlier than the first date. Therefore, Oracle will return -168 as the result.

35. The PROFITS column inside the SALES table is declared as NUMBER(10,2).

Which of the following values cannot be stored in that column?

5392845.324

871039453.1

75439289.34

60079829.25

The column is declared as NUMBER(10,2), which means it can store a maximum of 10 digits with 2 decimal places. The value 871039453.1 has 11 digits before the decimal point, which exceeds the maximum limit of 10 digits. Therefore, it cannot be stored in that column.

Explanation

The column is declared as NUMBER(10,2), which means it can store a maximum of 10 digits with 2 decimal places. The value 871039453.1 has 11 digits before the decimal point, which exceeds the maximum limit of 10 digits. Therefore, it cannot be stored in that column.

36. )Which of the following choices identifies a group by query that will not result in an error from Oracle when run against the database?

Select deptno, job, sum(sal) from emp group by job, deptno;

Select sum(sal), deptno, job from emp group by job, deptno;

Select deptno, job, sum(sal) from emp;

Select deptno, sum(sal), job from emp group by job, deptno;

All of the given choices will not result in an error from Oracle when run against the database. This is because they all include the necessary elements for a valid group by query - the columns being selected (deptno, job, sum(sal)), and the grouping columns (job, deptno). The order of the columns in the select statement does not matter as long as the grouping columns are included. The sum(sal) function is also valid as it is an aggregate function that can be used with the group by clause.

Explanation

All of the given choices will not result in an error from Oracle when run against the database. This is because they all include the necessary elements for a valid group by query - the columns being selected (deptno, job, sum(sal)), and the grouping columns (job, deptno). The order of the columns in the select statement does not matter as long as the grouping columns are included. The sum(sal) function is also valid as it is an aggregate function that can be used with the group by clause.

Submit

37. You want to join the contents of two tables, PRODUCT and STORAGE, to list the location of all boxes containing widgets.

PRODUCT has three columns: ID, NAME, and BOX#.

STORAGE has two columns: BOX# and LOC.

Which of the following choices will not give the desired result?

Select product.id, product.name, storage.loc from product, storage where product.box# = storage.box#;

Select product.id, product.name, storage.loc from product join storage on product.box# = storage.box#;

Select product.id, product.name, storage.loc from product natural join storage on product.box# = storage.box#;

Select product.id, product.name, storage.loc from product natural join storage;

not-available-via-ai

Explanation

not-available-via-ai

38. You are trying to manipulate data on the Oracle database.

Which of the following choices identifies a capacity of select statements in Oracle and does not require the use of a subquery?

You can change data in Oracle using select statements.

You can remove data from Oracle using select statements.

You can create a table with the contents of another using select statements.

You can truncate tables using select statements

not-available-via-ai

Explanation

not-available-via-ai

39. Use the contents of the EMP table shown in the following code block to answer the question: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- -------- --------- ----- --------- ---- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Which of the following choices identifies the third employee listed from the top in the output generated from the following SQL command: select ename, job from emp where job = 'SALESMAN' order by 1 desc?

ALLEN

MARTIN

TURNER

WARD

The SQL command "select ename, job from emp where job = 'SALESMAN' order by 1 desc" is used to retrieve the names and job titles of employees from the "emp" table who have the job title "SALESMAN". The "order by 1 desc" clause is used to sort the results in descending order based on the first column (ename). Therefore, the third employee listed from the top in the output would be "TURNER".

Explanation

The SQL command "select ename, job from emp where job = 'SALESMAN' order by 1 desc" is used to retrieve the names and job titles of employees from the "emp" table who have the job title "SALESMAN". The "order by 1 desc" clause is used to sort the results in descending order based on the first column (ename). Therefore, the third employee listed from the top in the output would be "TURNER".

40. Your company's sales database contains one table, PROFITS, which stores profits listed by product name, sales region, and quarterly time period. If you wanted to obtain a listing of the five best-selling products in company history,

which of the following SQL statements would you use?

Select p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc) where rownum

Select prod_name, profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum

Select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum

The correct answer is "select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum". This statement first calculates the sum of profits for each product using the subquery (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc), and then selects the product name and profit from this subquery. The "where rownum" clause limits the result to the top row, which represents the best-selling product.

Explanation

The correct answer is "select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum". This statement first calculates the sum of profits for each product using the subquery (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc), and then selects the product name and profit from this subquery. The "where rownum" clause limits the result to the top row, which represents the best-selling product.