# Grand Test SQL

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
| By Sudha_test
S
Sudha_test
Community Contributor
Quizzes Created: 12 | Total Attempts: 36,031
Questions: 40 | Attempts: 1,079

Settings

This is a grand SQL test

• 1.

### ) 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?

• A.

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

• B.

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

• C.

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

• D.

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

A. Select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = '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'.

Rate this question:

• 2.

### 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?

• A.

You can change data in Oracle using select statements.

• B.

You can remove data from Oracle using select statements.

• C.

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

• D.

You can truncate tables using select statements

D. You can truncate tables using select statements
• 3.

### 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?

• A.

Column clause

• B.

Table clause

• C.

The DUAL table

• D.

The where clause

C. The DUAL table
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.

Rate this question:

• 4.

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

• A.

. select * from dual;

• B.

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

• C.

Set define ?

• D.

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

C. Set define ?
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.

Rate this question:

• 5.

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

• A.

Tables

• B.

Sequences

• C.

Indexes

• D.

Views

C. Indexes
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.

Rate this question:

• 6.

### 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?

• A.

Select

• B.

From

• C.

Where

• D.

Having

C. Where
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.

Rate this question:

• 7.

### ) 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.30GAS GRILL       APPLIANCE       31-MAR-2001     1234023.88PENCIL              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?

• A.

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

• B.

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

• C.

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

• D.

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

C. . where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001';
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.

Rate this question:

• 8.

### 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?

• A.

Oracle returns zero as the result

• B.

Oracle returns EMPTY as the result.

• C.

Oracle returns NULL as the result

• D.

Oracle returns an error as the result

B. Oracle 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.

Rate this question:

• 9.

### 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?

• A.

Where customer = 'LESLIE';

• B.

Where customer = 'LESLIE' and order_amt < 2700;

• C.

Where customer = 'LESLIE' or order_amt > 2700;

• D.

Where customer = 'LESLIE' and order_amt > 2700;

D. Where customer = 'LESLIE' and order_amt > 2700;
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.

Rate this question:

• 10.

### Use the following output to answer the question ENAME----------SMITH-dog-ALLEN-dog-WARD-dog-dJONES-dog-MARTIN-dogBLAKE-dog-CLARK-dog-SCOTT-dog-KING-dog-dTURNER-dogADAMS-dog-JAMES-dog-FORD-dog-dMILLER-dogWhich of the following choices identifies the SQL statement that produced this output?

• A.

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

• B.

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

• C.

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

• D.

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

B. Select rpad(ename, 10, '-dog') as ename from emp;
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.

Rate this question:

• 11.

### 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?

• A.

Abs( )

• B.

Ceil( )

• C.

Floor( )

• D.

Round( )

A. Abs( )
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.

Rate this question:

• 12.

### 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?

• A.

6

• B.

13

• C.

30

• D.

60

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

Rate this question:

• 13.

### 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?

• A.

Ceil( )

• B.

Floor( )

• C.

Round( )

• D.

Trunc( )

B. Floor( )
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.

Rate this question:

• 14.

### 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?

• A.

Ceil( )

• B.

Floor( )

• C.

.round( )

• D.

Trunc( )

A. Ceil( )
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.

Rate this question:

• 15.

### You issue the following query in Oracle: SQL> select months_between('15-MAR-83', '15-MAR-97') from dual;          What will Oracle return?

• A.

14

• B.

-14

• C.

168

• D.

-168

D. -168
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.

Rate this question:

• 16.

### 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?

• A.

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

• B.

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

• C.

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

• D.

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

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

Rate this question:

• 17.

### 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?

• A.

.Two

• B.

Three

• C.

Four

• D.

Five

B. Three
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.

Rate this question:

• 18.

### 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?

• A.

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

• B.

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

• C.

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

• D.

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

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

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

• A.

Because outer join operations permit NULL values from one of the tables, you do not have to specify equality comparisons to join those tables.

• B.

In outer join statements on tables A and B, you specify the right outer join when you want all of table A's rows, even when no corresponding record exists in table B.

• C.

In outer join statements on tables A and B, you specify the left outer join when you want all of table B's rows, even when no corresponding record exists in table A.

• D.

Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables.

D. Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables.
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.

Rate this question:

• 20.

### 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?

• A.

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'(+);

• B.

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

• C.

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

• D.

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;

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

Rate this question:

• 21.

### 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?

• A.

Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno where a.state = 'TEXAS';

• B.

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

• C.

Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = 'TEXAS';

• D.

Select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno (+) and a.state = 'TEXAS';

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

Rate this question:

• 22.

### Examine the following output from SQL*Plus:PRODUCT.ID PRODUCT.NAME BOX.LOCATION---------- ------------ ------------578-X      WIDGET       IDAHO              TENNESSEE456-Y      WIDGETWhich of the following choices identifies the type of query that likely produced this result?

• A.

Full outer join

• B.

Left outer join

• C.

Right outer join

• D.

Equijoin

A. Full outer join
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.

Rate this question:

• 23.

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

• A.

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

• B.

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

• C.

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

• D.

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

A. Select deptno, job, sum(sal) from emp group by job, deptno;
B. Select sum(sal), deptno, job from emp group by job, deptno;
D. Select deptno, sum(sal), job from emp group by job, deptno;
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.

Rate this question:

• 24.

### 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?

• A.

DEPTNO

• B.

JOB

• C.

LOC

• D.

Sum(A.SAL)

D. Sum(A.SAL)
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.

Rate this question:

• 25.

### 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?

• A.

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

• B.

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

• C.

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

• D.

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

C. Select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000;
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.

Rate this question:

• 26.

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

A single-row subquery

• B.

A multirow subquery

• C.

A from clause subquery

• D.

A multicolumn subquery

A. A single-row subquery
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.

Rate this question:

• 27.

### 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?

• A.

select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';

• B.

Select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') and product = 'TOYS');

• C.

Select sum(profit) from profits where region = ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';

• D.

Select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') , product = 'TOYS';

A. select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS';
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.

Rate this question:

• 28.

### 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?

• A.

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

• B.

Select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where p.prod_name = subq.prod_name;

• C.

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

• D.

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

D. Select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum
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.

Rate this question:

• 29.

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

• A.

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

• B.

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

• C.

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

• D.

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

C. Create temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10));
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, ...);"

Rate this question:

• 30.

### The PROFITS column inside the SALES table is declared as NUMBER(10,2). Which of the following values cannot be stored in that column?

• A.

5392845.324

• B.

871039453.1

• C.

75439289.34

• D.

60079829.25

B. 871039453.1
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.

Rate this question:

• 31.

### 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?

• A.

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

• B.

A default clause on the JOB_WAGE column defined when the table was created specified the value when the row was inserted

• C.

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

• D.

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

B. A default clause on the JOB_WAGE column defined when the table was created specified the value when the row was inserted
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.

Rate this question:

• 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?

• A.

Copy column records to a temporary storage location

• B.

Set the NUMBER column to NULL for all rows.

• C.

Create a temporary location for NUMBER data.

• D.

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

D. Copy column records from the temporary location back to the main table.
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.

Rate this question:

• 33.

### 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?

• A.

Set the VARCHAR2 column to NULL for all rows.

• B.

Create a temporary location for VARCHAR2 data.

• C.

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

• D.

Nothing; the statement is executed automatically.

D. Nothing; the statement is executed automatically.
• 34.

### 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?

• A.

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

• B.

Alter table sales modify product_type varchar2(10));

• C.

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

• D.

Alter table sales drop column product_type;

B. Alter table sales modify product_type 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).

Rate this question:

• 35.

### 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?

• A.

One

• B.

Two

• C.

Three

• D.

NULL

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

Rate this question:

• 36.

### 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?

• A.

ALLEN

• B.

MARTIN

• C.

TURNER

• D.

WARD

C. 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".

Rate this question:

• 37.

### 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?

• A.

ALLEN

• B.

MARTIN

• C.

TURNER

• D.

WARD

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

Rate this question:

• 38.

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

• A.

True

• B.

False

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

Rate this question:

• 39.

### Having can exist with out Group by clause

• A.

True

• B.

False

B. False
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.

Rate this question:

• 40.

### Group by clause is used to exclude the group results

• A.

True

• B.

False

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

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, 2023
Quiz Edited by
ProProfs Editorial Team
• Jan 15, 2010
Quiz Created by
Sudha_test

Related Topics