WorksheetsSQL Fundamentals 1 Evaluation Exam by Focus IT Corp
Total questions: 21
Worksheet time: 5hrs 15mins
Name
Class
Date
1.
Which three statements are true regarding subqueries? (Choose two.)
A. Main query and subquery can get data from different tables
B. Main query and subquery must get data from the same tables
C. Subqueries can contain GROUP BY and ORDER BY clauses
D. Subqueries can contain ORDER BY but not the GROUP BY clause
E. Only one column or expression can be compared between the main query and subqeury
F. Multiple columns or expressions can be compared between the main query and subquery
A. Main query and subquery can get data from different tables
B. Main query and subquery must get data from the same tables
C. Subqueries can contain GROUP BY and ORDER BY clauses
D. Subqueries can contain ORDER BY but not the GROUP BY clause
E. Only one column or expression can be compared between the main query and subqeury
F. Multiple columns or expressions can be compared between the main query and subquery
a)
A, F
b)
B, F
c)
A, C
d)
D, E
2.
You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?
A. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level = NULL;
B. UPDATE customers SET cust_credit_limit = TO_NUMBER(NULL) WHERE cust_income_level = TO_NUMBER(NULL);
C. UPDATE customers SET cust_credit_limit = TO_NUMBER(' ',9999) WHERE cust_income_level IS NULL;
D. UPDATE customers SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;
A. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level = NULL;
B. UPDATE customers SET cust_credit_limit = TO_NUMBER(NULL) WHERE cust_income_level = TO_NUMBER(NULL);
C. UPDATE customers SET cust_credit_limit = TO_NUMBER(' ',9999) WHERE cust_income_level IS NULL;
D. UPDATE customers SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;
a)
A
b)
B
c)
C
d)
D
3.
Evaluate the following SQL statements:
SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales
MINUS
SELECT prod_id FROM costs;
Which statement is true regarding the above compound query?
A. It shows products that were sold but have no cost recorded
B. It reduces an error
C. It shows products that were sold and have a cost recorded
D. It shows products that have a cost recorded irrespective of sales
SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales
MINUS
SELECT prod_id FROM costs;
Which statement is true regarding the above compound query?
A. It shows products that were sold but have no cost recorded
B. It reduces an error
C. It shows products that were sold and have a cost recorded
D. It shows products that have a cost recorded irrespective of sales
a)
A
b)
B
c)
C
d)
D
4.
Examine the structure of the MARKS table:
Which two statements would execute successfully? (Choose two.)
A. SELECT student_name, subject1 FROM marks WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
C. SELECT SUM (subject1+subject2+subject3) FROM marks WHERE student_name IS NULL
D. SELECT SUM (DISTINCT NVL(subject1,0)),MAX(subject1) FROM marks WHERE subject1 > subject2;
Which two statements would execute successfully? (Choose two.)
A. SELECT student_name, subject1 FROM marks WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
C. SELECT SUM (subject1+subject2+subject3) FROM marks WHERE student_name IS NULL
D. SELECT SUM (DISTINCT NVL(subject1,0)),MAX(subject1) FROM marks WHERE subject1 > subject2;
a)
A, D
b)
C, D
c)
A, C
d)
B, C
5.
Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001 starting with the latest promo. Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;
B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC;
D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1 DESC;
A. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;
B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC;
D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1 DESC;
a)
A only
b)
B only
c)
A, B
d)
C
6.
Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERs table?
A. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city,cust_income_level,cust_credit_limit;
B. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city,cust_income_level;
C. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city;
D. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level,MAX(cust_credit_limit);
A. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city,cust_income_level,cust_credit_limit;
B. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city,cust_income_level;
C. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city;
D. SELECT cust_city,cust_income_level,MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level,MAX(cust_credit_limit);
a)
A
b)
B
c)
C
d)
D
7.
Which statement is true regarding the output of the above query?
A. It gives the details of promos for which there have been sales
B. It gives the details of promos for which there have been no sales
C. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
D. It gives details of all promos irrespective of whether they have resulted in a sale or not
A. It gives the details of promos for which there have been sales
B. It gives the details of promos for which there have been no sales
C. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
D. It gives details of all promos irrespective of whether they have resulted in a sale or not
a)
A
b)
B
c)
C
d)
D
8.
Evaluate the following SQL query.
What would be the outcome?
What would be the outcome?
a)
16
b)
100
c)
200
d)
156
9.
You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'King' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'King'. Which statement is true regarding the outcome of the above query?
a)
It executes and shows the required result
b)
It produces an error and the < operator should be replaced by < ALL to get the required output
c)
It produces an error and the < operator should be replaced by < ANY to get the required output
d)
It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
10.
Evaluate the following SQL statements:
DELETE FROM SALES;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?
DELETE FROM SALES;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?
a)
It would not remove the rows if the table has a primary key
b)
It removes all the rows as well as the structure of the table
c)
It removes all the rows in the table and deleted rows can be rolled back
d)
It removes all the rows in the table and deleted rows cannot be rolled back
11.
Examine the structure of CUSTOMERS table: Evaluate the following SQL statement.
Which statement is true regarding the outcome of the above query?
Which statement is true regarding the outcome of the above query?
a)
It executes successfully
b)
It returns an error because the BETWEEN operator cannot be used in the HAVING clause
c)
It returns an error because WHERE and HAVING clause cannot be used in the same SELECT statement
d)
It returns an error because WHERE and HAVING clause cannot be used to apply conditions on the same column
12.
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: Which statement is true regarding this SQL statement?
a)
It executes successfully and generates the required result
b)
It produces an error because COUNT (*) should be specified the SELECT clause also
c)
It produces an error because COUNT (*) should be only the HAVING clause and not in the WHERE clause
d)
It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*)
13.
Examine the structures of the PRODUCTS, SALES AND CUSTOMERS table. You need to generate a report that gives details of the customer's last name, name of the product and the quantity sold for all customers in 'Tokyo'. Which query give the required result?
a)
SELECT c.cust_last_name,p.prod_name,s.quantity_sold FROM sales s JOIN products p USING (prod_id) JOIN customers c USING (cust_id) WHERE c.cust_city='Tokyo';
b)
SELECT c.cust_last_name,p.prod_name,s.quantity_sold FROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id) ON(s.cust_id=c.cust_id) WHERE c.cust_city='Tokyo';
c)
SELECT c.cust_last_name,p.prod_name,s.quantity_sold FROM products p JOIN sales s USING (prod_id) ON(p.prod_id=s.prod_id) JOIN customers c USING(cust_id) WHERE c.cust_city='Tokyo';
14.
Which two statements are true regarding the COUNT function?
A. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
B. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
C. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
D. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
A. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
B. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
C. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
D. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
a)
A only
b)
A, B
c)
B,C
d)
C, D
15.
You issue the following command to drop the PRODUCTS table:
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data along with the table structure is deleted
B. The pending transaction in the session is committed
C. All indexes on the table will remain but they are invalidated
D. All view and synonyms will remain but they are invalidated
E. All data in the table are deleted but the table structure will remain
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data along with the table structure is deleted
B. The pending transaction in the session is committed
C. All indexes on the table will remain but they are invalidated
D. All view and synonyms will remain but they are invalidated
E. All data in the table are deleted but the table structure will remain
a)
A, B
b)
A,B,D
c)
A,D
d)
A,D,E
16.
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name FROM customers
WHERE cust_credit_limit IN (select cust_credit_limit
FROM customers WHERE cust_city='Srngapore'):
Which statement is true regarding the above query if one of the values generated by the subquery is NULL
SQL> SELECT cust_id. cust_last_name FROM customers
WHERE cust_credit_limit IN (select cust_credit_limit
FROM customers WHERE cust_city='Srngapore'):
Which statement is true regarding the above query if one of the values generated by the subquery is NULL
a)
It produces an error.
b)
It executes but returns no rows.
c)
It generates output for NULL as well as the other values produced by the subquery.
d)
It ignores the NULL value and generates output for the other values produced by the subquery.
17.
Evaluate the following SQL statements that are executed in a user session in the specified order. What would be the outcome of the above statements?
a)
The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified
b)
The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified
c)
All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101
d)
All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20
18.
Which two statements describe the consequence of issuing the command in the session?
SQL> ROLLBACK TO SAVEPOINT a;
SQL> ROLLBACK TO SAVEPOINT a;
a)
Both the DELETE statements and the UPDATE statement are rolled back
b)
Only the seconds DELETE statement is rolled back
c)
Only the DELETE statements are rolled back
d)
The rollback generates an error and no SQL statements are rolled back
19.
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.Which UPDATE statement will accomplish the task?
a)
UPDATE customers SET (cust_income_level,cust_credit_ limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id = 2560 OR cust_id = 2566) WHERE cust_id=2360;
b)
UPDATE customers SET (cust_income_level,cust_credit_ limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN (2560, 2566) WHERE cust_id=2360;
c)
UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
d)
UPDATE customers SET (cust_income_level,cust_credit_ limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id = 2560 AND cust_id = 2566) WHERE cust_id=2360;
20.
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
a)
INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000):
b)
INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
c)
INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. o.ordertotal FROM orders o. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
d)
INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600).order_total) VALUES(l.'10-mar-2007\ 'direct'. &customer_id. 1000):
21.
The above query produces an error on execution. What is the reason for the error?
a)
The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
b)
An alias cannot be used in an expression
c)
The alias NAME should not be enclosed within double quotation marks
d)
The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
100 %
