Database Programming with SQL Midterm Exam
Section 1
(Answer all questions in this section)
1. The DESCRIBE command returns all rows from a table. True or False? Mark for Review
True
False (*)
2. What command can be used to create a new row in a table in the database? Mark for Review
CREATE
ADD
INSERT (*)
NEW
3. SELECT * FROM departments; is a: Mark for Review
Keyword
Statement (*)
Strategy
Declaration
4. In which clause of a SELECT statement would you specify the name of the table or tables being queried? Mark for Review
The FROM clause (*)
The SELECT clause
The WHERE clause
Any of the above options; you can list tables wherever you want in a SELECT statement.
5. Every row in a relational database table is unique. Mark for Review
True (*)
False
Section 2
(Answer all questions in this section)
6. You need to display all the values in the EMAIL column that contains the underscore (_) character as part of that email address. The WHERE clause in your SELECT statement contains the LIKE operator. What must you include in the LIKE operator? Mark for Review
The (+) operator
The ESCAPE option (\)
A percent sign (%)
The ESCAPE option (\) and one or more percent signs (%) (*)
7. What will be the result of the SELECT statement and what will display?
SELECT last_name, salary, salary + 300
FROM employees;
Display the last name, salary, and the results of adding 300 to each salary for all the employees (*)
Display the last name, salary, and the results of adding 300 to the salary of the first employee row
Display the last name and salary of all employees who have a salary greater than 300.
Modify the salary column by adding 300 and only display the last name and the new salary.
8. You need to combine the FIRST_NAME and LAST_NAME columns in the EMPLOYEES table and display the columns as a combined character string. Which operator should you use?
|| (*)
AND
|
+
9. You need to display employees whose salary is in the range of 10000 through 25000 for employees in department 50 . What does the WHERE clause look like? Mark for Review
WHERE department_id < 50
AND salary BETWEEN 10000 AND 25000
WHERE department_id = 50
AND salary BETWEEN 25001 AND 10001
WHERE department_id > 50
AND salary BETWEEN 10000 AND 25000
WHERE department_id = 50
AND salary BETWEEN 10000 AND 25000
(*)
10. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False? Mark for Review
True (*)
False
Correct Correct
Section 2
(Answer all questions in this section)
11. Which of the following WHERE clauses would not select the number 10? Mark for Review
WHERE hours <>10 (*)
WHERE hours IN (8,9,10)
WHERE hours <= 10
WHERE hours BETWEEN 10 AND 20
Correct Correct
Section 3
(Answer all questions in this section)
12. Which statement about the logical operators is true?
Mark for Review
The order of operator precedence is AND, OR, and NOT.
The order of operator precedence is NOT, OR, and AND.
The order of operator precedence is AND, NOT, and OR.
The order of operator precedence is NOT, AND, and OR. (*)
13. A column alias can be specified in an ORDER BY Clause. True or False? Mark for Review
True (*)
False
Correct Correct
14. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id = 34
OR department_id = 45
OR department_id = 67;
Which operator is the equivalent of the OR conditions used in this SELECT statement?
Mark for Review
BETWEEN AND ...
IN (*)
LIKE
AND
15. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
FROM employees e, employees m
ORDER BY e.last_name, e.first_name
WHERE e.employee_id = m.manager_id;
This statement fails when executed. Which change will correct the problem?
Mark for Review
Remove the table aliases in the ORDER BY clause.
Reorder the clauses in the query. (*)
Include a HAVING clause.
Remove the table aliases in the WHERE clause.
Section 4
(Answer all questions in this section)
16. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
HIRE_DATE DATE
EVAL_MONTHS NUMBER(3)
Evaluate this SELECT statement:
SELECT hire_date + eval_months
FROM employees;
The values returned by this SELECT statement will be of which data type?
Mark for Review
DATE (*)
NUMBER
DATETIME
INTEGER
Correct Correct
17. You issue this SQL statement:
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?
Mark for Review
1282.25
1200
1282
1300 (*)
Correct Correct
18. Which query selects the first names of the DJ On Demand clients who have a first name beginning with "A"? Mark for Review
SELECT UPPER(first_name)
FROM d_clients
WHERE first_name LIKE '%a%'
SELECT UPPER(first_name)
FROM d_clients
WHERE first_name LIKE 'a%'
SELECT UPPER(first_name)
FROM d_clients
WHERE first_name LIKE %a%
SELECT UPPER(first_name)
FROM d_clients
WHERE LOWER(first_name) LIKE 'a%'
(*)
Correct Correct
19. Identify the output from the following SQL statement:
SELECT RPAD('SQL',6, '*')
FROM DUAL;
Mark for Review
***SQL
SQL******
******SQL
SQL*** (*)
Correct Correct
Section 5
(Answer all questions in this section)
20. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
Mark for Review
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
Invalid statement.
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Section 5
(Answer all questions in this section)
21. The following script will run successfully. True or False?
SELECT TO_CHAR(TO_DATE('25-Dec-2004','dd-Mon-yyyy'))
FROM dual Mark for Review
True (*)
False
Correct Correct
22. Which statement will return the salary (for example, the salary of 6000) from the Employees table in the following format? $6000.00 Mark for Review
SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM employees
(*)
SELECT TO_CHAR(salary, '99999.00') SALARY
FROM employees
SELECT TO_CHAR(salary, '$99999') SALARY
FROM employees
SELECT TO_CHAR(sal, '$99999.00') SALARY
FROM employees
Correct Correct
23. Sysdate is 12-May-2004.
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true? Mark for Review
Both the YY and RR date formats will interpret the year as 1989
The RR date format will interpret the year as 2089, and the YY date format will interpret the year as 1989
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)
Both the YY and RR date formats will interpret the year as 2089
Correct Correct
24. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
Mark for Review
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
758960 SANDAL 86979
Correct Correct
25. Which statement about group functions is true? Mark for Review
NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
Section 6
(Answer all questions in this section)
26. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?
EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2 (25)
EMAIL NOT NULL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs;
(*)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title);
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id);
Incorrect Incorrect. Refer to Section 6 Lesson 3.
27. Which two sets of join keywords create a join that will include unmatched rows from the first table specified in the SELECT statement? Mark for Review
RIGHT OUTER JOIN and LEFT OUTER JOIN
LEFT OUTER JOIN and FULL OUTER JOIN (*)
OUTER JOIN and USING
USING and HAVING
Correct Correct
28. You need to join two tables that have two columns with the same name, datatype, and precision. Which type of join would you create to join the tables on both of the columns? Mark for Review
Self-join
Outer join
Cross join
Natural join (*)
Correct Correct
29. You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create? Mark for Review
An equijoin
A full outer join
An inner join
A cross join (*)
Correct Correct
30. Which of the following database design concepts is implemented with a self join? Mark for Review
Non-Transferability
Recursive Relationship (*)
Arc
Supertype
Section 6
(Answer all questions in this section)
31. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? Mark for Review
True (*)
False
Correct Correct
32. The primary advantages of using JOIN ON is: (Select two) Mark for Review
(Choose all correct answers)
It will display rows that do not meet the join condition.
The join happens automatically based on matching column names and data types.
It permits columns that donﾒt have matching data types to be joined. (*)
It permits columns with different names to be joined. (*)
Correct Correct
Section 7
(Answer all questions in this section)
33. You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task? Mark for Review
SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND dept_id = 10;
(*)
SELECT last_name, first_name, salary
FROM employees
WHERE salary = 25000 AND dept_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary <= 25000 AND dept_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary > 25000 AND dept_id = 10;
Correct Correct
34. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a equi-join on those two tables? Mark for Review
10
5
50
It depends on how many rows have matching data in each of the two tables. (*)
Correct Correct
35. Which symbol is used to perform an outer join? Mark for Review
*
#
||
(+) (*)
Section 8
(Answer all questions in this section)
36. Group functions can avoid computations involving duplicate values by including which keyword? Mark for Review
DISTINCT (*)
UNLIKE
SELECT
NULL
Correct Correct
37. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
Mark for Review
SUM = 1.85 and COUNT = 6
SUM = .85 and COUNT = 4
SUM = 1.85 and COUNT =4
SUM = .85 and COUNT = 6 (*)
Correct Correct
38. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(30)
PROD_CAT VARCHAR2(30)
PROD_PRICE NUMBER(3)
PROD_QTY NUMBER(4)
The following statement is issued:
SELECT AVG(prod_price, prod_qty)
FROM products;
What happens when this statement is issued?
Mark for Review
Both the average price and the average quantity of the products are returned.
An error occurs. (*)
The values in the PROD_PRICE column and the PROD_QTY column are averaged together.
Only the average quantity of the products is returned.
Correct Correct
Section 9
(Answer all questions in this section)
39. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR(20)
PROD_CAT VARCHAR2(15)
PROD_PRICE NUMBER(5)
PROD_QTY NUMBER(4)
You need to identify the minimum product price in each product category.
Which statement could you use to accomplish this task?
Mark for Review
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;
(*)
SELECT MIN (prod_price), prod_cat
FROM products
GROUP BY MIN (prod_price), prod_cat;
SELECT prod_price, MIN (prod_cat)
FROM products
GROUP BY prod_cat;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;
Correct Correct
40. Group functions can be nested to a depth of? Mark for Review
Four
Group functions cannot be nested.
Two (*)
Three
Section 9
(Answer all questions in this section)
41. The EMPLOYEES table contains these columns:
ID_NUMBER NUMBER Primary Key
NAME VARCHAR2 (30)
DEPARTMENT_ID NUMBER
SALARY NUMBER (7,2)
HIRE_DATE DATE
Evaluate this SQL statement:
SELECT id_number, name, department_id, SUM(salary)
FROM employees
WHERE salary > 25000
GROUP BY department_id, id_number, name
ORDER BY hire_date;
Why will this statement cause an error?
Mark for Review
The HAVING clause is missing.
The SALARY column is NOT included in the GROUP BY clause.
The HIRE_DATE column is NOT included in the GROUP BY clause. (*)
The WHERE clause contains a syntax error.
Correct Correct
42. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
True (*)
False
Correct Correct
43. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Mark for Review
Subtotals for department_id, and grand totals for salary
Subtotals for department_id, job_id and grand totals for salary
Subtotals for department_id, job_id, manager_id and grand totals for salary
The statement will fail. (*)
Correct Correct
44. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query. Mark for Review
ONCE; LAST (*)
IN ALL; LAST
ONCE; FIRST
TWICE; FIRST
Correct Correct
Section 10
(Answer all questions in this section)
45. Group functions can be used in multiple-row subqueries in the HAVING and GROUP BY clauses. True or False? Mark for Review
True (*)
False
Section 10
(Answer all questions in this section)
46. Which operator or keyword cannot be used with a multiple-row subquery? Mark for Review
= (*)
>
ANY
ALL
Correct Correct
47. Evaluate this SQL statement:
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE salary > 30000 AND salary < 50000);
Which values will be displayed?
Mark for Review
All employees who work in a department with employees who earn more than $30,000 and more than $50,000.
All employees who work in a department with employees who earn more than $30,000, but less than $50,000. (*)
Only employees who earn more than $30,000.
Only employees who earn less than $50,000.
Correct Correct
48. The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141) AND
department_id =
(SELECT department_id
FROM departments
WHERE location_id =1500);
Mark for Review
All employees from Location 1500 will be displayed
All employees with the department id of 141
Only the employees whose job id matches employee 141 and who work in location 1500 (*)
An error since you canﾒt get data from two tables in the same subquery
Correct Correct
49. Which operator can be used with subqueries that return only one row? Mark for Review
IN
ALL
LIKE (*)
ANY
Incorrect Incorrect. Refer to Section 10 Lesson 1.
50. A correlated subquery will _______ a candidate row from an outer query, _______ the inner query using candidate row value, and _______ values from the inner query to qualify or disqualify the candidate row. Mark for Review
ROLLUP; GRANT; DROP
GET; EXECUTE; USE (*)
CREATE; EXECUTE; USE
DELETE; UPDATE; INSERT
Comments
Post a Comment