SQL Fundamentals

Question 1

View the Exhibit and examine the structure of the customers table.
Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

Accepted Answer

SELECT NVL(TO_CHAR(cust_credit_limit*0.15), 'Not Available')) "NEW CREDIT"
FROM customers;

Answer

SELECT NVL(cust_credit_limit, 'Not Available')*0.15 "NEW CREDIT"
FROM customers;

Answer

SELECT NVL(cust_credit_limit*0.15, 'Not Available') "NEW CREDIT"
FROM customers;

Answer

SELECT TO_CHAR(NVL(cust_credit_limit*0.15, 'Not Available')) "NEW CREDIT"
FROM customers;

Question 2

View the Exhibit and examine the structure of the promotions table.
Evaluate the following SQL statement:
SQL>SELECT promo_name, 
                       CASE WHEN promo_cost >= (SELECT AVG(promo_cost) FROM promotions WHERE promo_category='TV')
                                 THEN 'HIGH'
                                 ELSE 'LOW'
                        END COST_REMARK
FROM promotions;

Which statement is true regarding the outcome of the above query?

Accepted Answer

It shows COST_REMARK for all the promos in the table.

Answer

It produces an error because the SUBQUERY gives an error.

Answer

It shows COST_REMARK for all the promos in the promo category 'TV'.

Answer

It produces an error because SUBQUERIES cannot be used with the case expression.

Question 3

View the Exhibit and examine the structure of the customers table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement:

INSERT INTO new_customers(cust_id, cust_name, cust_city)
VALUES(SELECT cust_id, cust_first_name || ' ' || cust_last_name, cust_city
FROM customers
WHERE cust_id > 23004);

The insert statement fails when executed.
What could be the reason?

Accepted Answer

The values clause cannot be used in an INSERT with a subquery.

Answer

Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.

Answer

The where clause cannot be used in a subquery embedded in an INSERT statement.

Answer

The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.

Question 4

Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1)
FROM DUAL;
What would be the outcome?

Accepted Answer

160

Answer

16

Answer

100

Answer

200

Answer

150

Question 5

Which two statements are true regarding single row functions?

Accepted Answer

TRUNC: can be used with number and date values

Accepted Answer

SYSDATE: returns the database server current date and time

Answer

MOD: returns the quotient of a division

Answer

CONCAT: can be used to combine any number of values

Answer

INSTR: can be used to find only the first occurrence of a character in a string

Answer

TRIM: can be used to remove all the occurrences of a character from a string

Question 6

Evaluate the following SQL statement:

SQL> SELECT promo_id, promo_category
FROM promotions
where promo_category = 'Internet' ORDER BY 2 DESC
UNION
SELECT promo_id, promo_category
FROM promotions
where promo_category = 'TV'
UNION
SELECT promo_id, promo_category
FROM promotions
where promo_category = 'Radio';

Which statement is true regarding the outcome of the above query?

Accepted Answer

It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement

Answer

It executes successfully and displays rows in the descending order of PROMO_CATEGORY

Answer

It produces an error because positional notation cannot be used in the order by clause with set operators

Answer

It executes successfully but ignores the order by clause because it is not located at the end of the compound statement

Question 7

View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>20
GROUP BY prod_id
HAVING COUNT(*) > 10;

Accepted Answer

It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.

Answer

It executes successfully and generates the required result.

Answer

It produces an error because count(*) should be specified in the SELECT clause also.

Answer

It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).

Question 8

Which two statements are true regarding subqueries?

Accepted Answer

A subquery can retrieve zero or more rows.

Accepted Answer

A subquery can appear on either side of a comparison operator.

Answer

Only two subqueries can be placed at one level.

Answer

A subquery can be used only in SQL query statements.

Answer

There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Question 9

View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.
Which query would accomplish this task?

Accepted Answer

SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city desc, cust_income_level DESC;

Answer

SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_income_level desc, cust_city DESC;

Answer

SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY (cust_city, cust_income_level) DESC;

Answer

SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city, cust_income_level DESC;

Question 10

View the Exhibit and examine the data in the products table.
You need to display product names from the products table that belong to the 'software/other' category with minimum prices as either 2000 or 4000 and no unit
of measure.
You issue the following query:

SQL> SELECT prod_name, prod_category, prod_min_price
FROM products
WHERE prod_category like '%Other%' 
AND (prod_min_price=2000 OR prod_min_price=4000)
AND prod_unit_of_measure <> '';

Which statement is true regarding the above query?

Accepted Answer

It executes successfully but returns no result.

Answer

It executes successfully and returns the required result.

Answer

It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

Answer

It generates an error because the condition specified for the prod category column is not valid.

Question 11

You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?

Accepted Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 
AND SUBSTR(cust_firs_name, -2, 1)='a';

Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' 
AND SUBSTR(cust_firs_name, -2, 1)='a';

Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e') is not null
AND SUBSTR(cust_firs_name, 1, -2)='a';

Answer

SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 
AND SUBSTR(cust_firs_name, length(cust_first_name), -2)='a';

Question 12

Which two statements are true regarding constraints?

Accepted Answer

A table can have only one primary key but multiple foreign keys.

Accepted Answer

Both primary key and foreign key constraints can be defined at both column and table levels.

Answer

A table can have only one primary key and one foreign key.

Answer

Only the primary key can be defined at the column and table levels.

Answer

The foreign key and parent table primary key must have the same name.

Question 13

In which three situations does a transaction complete?

Accepted Answer

When a ROLLBACK command is executed

Accepted Answer

When a data definition language (DDL) statement is executed

Accepted Answer

When a TRUNCATE statement is executed after the pending transaction

Answer

When a DELETE statement is executed

Answer

When a PL/SQL anonymous block is executed

Question 14

You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true?

Accepted Answer

The employees table is moved to the recycle bin.

Answer

All uncommitted transactions are committed.

Answer

All indexes and constraints defined on the table being dropped are also dropped.

Answer

Sequences used in the employees table become invalid.

Answer

The space used by the employees table is reclaimed immediately.

Answer

The employees table can be recovered using the rollback command.

Question 15

Which statement is true regarding the UNION operator?

Accepted Answer

The number of columns selected in all select statements need not be the same.

Answer

By default, the output is not sorted.

Answer

Null values are not ignored during duplicate checking.

Answer

Names of all columns must be identical across all select statements.

SQL Fundamentals

Create a free account to finish this quiz

Questions in this quiz (15)

Information

Feedback

What do you think of the new look?