Database Programming with SQL Midterm Exam
Which SQL statement will return an error?
SELECT
star FROM sky;
SEL *
FR sky; (*)
SELECT
* FROM sky;
select
star from sky;
SELECT * FROM departments; is
a:
Strategy
Statement
(*)
Keyword
Declaration
The basic storage structure in
a Relational Database is a _________:
Row
Field
Key
Table
(*)
The DESCRIBE command returns
all rows from a table. True or False?
True
False
(*)
You cannot use computers unless
you completely understand exactly how they work. True or False?
True
False
(*)
Which example would limit the number of rows returned?
SELECT
title FROM d_songs WHERE type_code = 88; (*)
SELECT
title FROM d_songs WHEN type_code = = 88;
SELECT
title FROM d_songs WHERE type_code = = 88;
SELECT
title FROM d_songs WHEN type_code = 88;
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?
The (+)
operator
A percent
sign (%)
The
ESCAPE option (\)
The
ESCAPE option (\) and one or more percent signs (%) (*)
The EMPLOYEES table includes
these columns:
EMPLOYEE_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
HIRE_DATE DATE NOT NULL
You want to produce a report that provides the last names,
first names, and hire dates of those employees who were hired between March 1,
2000, and August 30, 2000. Which statements can you issue to accomplish this
task?
SELECT
last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '30-Aug-2000' AND '01-Mar-2000';
SELECT
last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-Mar-2000' AND '30-Aug-2000';
(*)
SELECT
last_name, first_name, hire_date
FROM employees
AND hire_date >= '01-Mar-2000' and hire_date <=
'30-Aug-2000';
SELECT
last_name, first_name, hire_date
FROM employees
GROUP BY hire_date >= '01-Mar-2000' and hire_date <=
'30- Aug-2000';
Which of the following WHERE
clauses would not select the number 10?
WHERE
hours <>10 (*)
WHERE
hours BETWEEN 10 AND 20
WHERE
hours IN (8,9,10)
WHERE
hours <= 10
Which of the following elements
cannot be included in a WHERE clause?
A
constant
A
column name
A
column alias (*)
A
comparison condition
When
using the LIKE condition, which symbol represents any sequence of characters of
any length--zero, one, or more characters?
&
_
% (*)
#
Which of the following are TRUE
regarding the logical AND operator?
TRUE
AND TRUE return FALSE
TRUE
AND FALSE return FALSE (*)
FALSE
AND TRUE return NULL
TRUE
AND FALSE return TRUE
What clause must you place in a
SQL statement to have your results sorted from highest to lowest salary?
ORDER
BY salary DESC (*)
ORDER
BY salary ASC
ORDER
salary BY DESC
None,
the database always sorts from highest to lowest on the salary column.
Evaluate this SELECT statement:
SELECT first_name, last_name, email
FROM employees
ORDER BY last_name;
Which statement is true?
The
rows will be sorted alphabetically by the LAST_NAME values. (*)
The
rows will be sorted alphabetically by the FIRST_NAME and then the LAST_NAME
values
The
rows will be sorted in reverse alphabetical order by the LAST_NAME values.
The
rows will not be sorted.
The conversion function TO_CHAR
is a single row function. True or False?
True
(*)
False
Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?
The
current date plus 30 hours.
The
current date plus 30 months.
No
value is returned because the SELECT statement generates an error.
The
current date plus 30 days. (*)
Evaluate this function: MOD (25,
2) Which value is returned?
0
25
1 (*)
2
You query the database with this
SQL statement:
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4)))
"Default Password"
FROM employees;
Which function will be evaluated first?
CONCAT
SUBSTR
LOWER
(*)
All
three will be evaluated simultaneously.
Which query would return a user
password combining the ID of an employee and the first 4 digits of the last
name?
SELECT
CONCAT (employee_id, INSTR(last_name,1,4))
AS "User Passwords"
FROM employees
SELECT
CONCAT (employee_id, SUBSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT
CONCAT (employee_id, INSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT
CONCAT (employee_id, SUBSTR(last_name,1,4))
AS "User Passwords"
FROM employees
(*)
Which statement will return a
listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or
'Excellent' depending on the salary value?
SELECT
last_name,sal,
(CASE WHEN sal<5000 THEN 'Low'
WHEN sal<10000
THEN 'Medium'
WHEN sal<20000
THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT
last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN salary<10000
THEN 'Medium'
WHEN
salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
(*)
SELECT
last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN sal
<10000 THEN 'Medium'
WHEN sal
<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT
last_name,salary,
(RATING WHEN salary<5000 THEN 'Low'
WHEN
salary<10000 THEN 'Medium'
WHEN
salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
When executed, which statement displays a zero if the
TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
SELECT
TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance
+ housing_balance "Balance Due"
FROM student_accounts;
SELECT
tuition_balance + housing_balance
FROM student_accounts;
SELECT
NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts;
(*)
SELECT
NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance +
housing_balance "Balance Due"
FROM student_accounts;
Which statement about group
functions is true?
NVL and
COALESCE, but not NVL2, 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.
NVL and
NVL2, but not COALESCE, can be used with group functions to replace null
values.
NVL,
NVL2, and COALESCE can be used with group functions to replace null values. (*)
The following script will run
successfully. True or False?
SELECT TO_CHAR(TO_DATE('25-Dec-2004','dd-Mon-yyyy'))
FROM dual
True
(*)
False
Which statement will return the
salary (for example, the salary of 6000) from the Employees table in the
following format? $6000.00
SELECT
TO_CHAR(salary, '$99999.00') SALARY
FROM employees
(*)
SELECT
TO_CHAR(sal, '$99999.00') SALARY
FROM employees
SELECT
TO_CHAR(salary, '$99999') SALARY
FROM employees
SELECT
TO_CHAR(salary, '99999.00') SALARY
FROM employees
You have been asked to create a
report that lists all customers who have placed orders of at least $2,500. The
report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004
).
Which statement should you issue?
SELECT
companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)
SELECT
companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT
companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT
companyname, TO_DATE (date, 'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
Which of the following database design concepts is
implemented with a self join?
Arc
Recursive
Relationship (*)
Non-Transferability
Supertype
Which SELECT statement implements
a self join?
SELECT
p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id;
(*)
SELECT
p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id;
SELECT
p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id (+);
SELECT
p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id =! t.product_id;
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?
A full
outer join
A cross
join (*)
An
inner join
An equijoin
Which of the following conditions
will cause an error on a NATURAL JOIN?
If it
selects rows from the two tables that have equal values in all matched columns.
If the
columns having the same names have different data types. (*)
When
the NATURAL JOIN clause is based on all columns in the two tables that have the
same name.
When
you attempt to use two tables that have a common field.
You can do nonequi-joins with
ANSI-Syntax. True or False?
True
(*)
False
Which query will retrieve all the rows in the EMPLOYEES
table, even if there is no match in the DEPARTMENTS table?
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id =
d.department_id);
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d USING (e.department_id =
d.department_id);
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id =
d.department_id);
(*)
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
NATURAL JOIN departments d;
You need to display all the rows
(both matching and non-matching) from both the EMPLOYEE and EMPLOYEE_HIST
tables. Which type of join would you use?
A left
outer join
A full
outer join (*)
A right
outer join
An
inner join
The EMPLOYEE_ID column in the
EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
The EMPLOYEE_ID column in the ORDERS table contains null
values for rows that you need to display.
Which type of join should you use to display the data?
Natural
join
Equijoin
Outer
join (*)
Self-join
Oracle proprietary JOINS can use
the WHERE clause for conditions other than the join-condition. True or False?
True
(*)
False
You have been asked to create a
report that lists all corporate customers and all orders that they have placed.
The customers should be listed alphabetically beginning with the letter 'A',
and their corresponding order totals should be sorted from the highest amount
to the lowest amount.
Which of the following statements should you issue?
SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname ASC, amount ASC;
SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount;
SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY amount DESC, companyname;
SELECT
c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount DESC;
(*)
Evaluate this SELECT statement:
SELECT COUNT(*)
FROM employees
WHERE salary > 30000;
Which result will the query display?
The
total of the SALARY column for all employees that have a salary greater than
30000
The
query generates an error and returns no results.
The
number of rows in the EMPLOYEES table that have a salary greater than 30000 (*)
The
number of employees that have a salary less than 30000
You can use GROUP functions in
all clauses of a SELECT statement. True or False?
True
False
(*)
You need to calculate the
standard deviation for the cost of products produced in the Birmingham
facility. Which group function will you use?
VAR_SAMP
STDDEV
(*)
STDEV
VARIANCE
GROUPING SETS is another
extension to the GROUP BY clause and is used to specify multiple groupings of
data but provide a single result set. True or False?
True
(*)
False
Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the
following list:
GROUP
BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id),
(department_id, manager_id)
GROUP
BY GROUPING SETS ((department_id, manager_id), (department_id, job_id),
(manager_id, job_id)) (*)
GROUP
BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary),
(manager_id, job_id))
GROUP
BY GROUPING SETS (department_id, salary), (department_id, job_id),
(department_id, manager_id)
MINUS will give you rows from the first query that are not
present in the second query. (True or False?)
True
(*)
False
Which statement about group
functions is true?
Group
functions can be used in a WHERE clause.
Group
functions can only be used in a SELECT list.
A query
that includes a group function in the SELECT list must include a GROUP BY
clause.
Group
functions ignore null values. (*)
What is the best explanation as
to why this SQL statement will NOT execute?
SELECT department_id "Department", AVG
(salary)"Average"
FROM employees
GROUP BY Department;
Salaries
cannot be averaged as not all the numbers will divide evenly.
You
cannot use a column alias in the GROUP BY clause. (*)
The
department id is not listed in the departments table.
The
GROUP BY clause must have something to GROUP.
Which of the following SQL
statements could display the number of people with the same last name:
SELECT
first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
SELECT
employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT
employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT
last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
(*)
You need to create a report to
display the names of products with a cost value greater than the average cost
of all products. Which SELECT statement should you use?
SELECT
product_name
FROM products
WHERE cost > AVG(cost);
SELECT
product_name
FROM products
WHERE cost > (SELECT AVG(cost)
FROM products);
(*)
SELECT
AVG(cost), product_name
FROM products
WHERE cost > AVG(cost)
GROUP by product_name;
SELECT
product_name
FROM (SELECT AVG(cost) FROM product)
WHERE cost > AVG(cost);
Examine the data in the PAYMENT table:
PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT
86590586 8908090 10-Jun-2003 BASIC 859.00
89453485 8549038 15-Feb-2003 INTEREST 596.00
85490345 5489304 20-Mar-2003 BASIC 568.00
This statement fails when executed:
SELECT customer_id, payment_type
FROM payment
WHERE payment_id =
(SELECT payment_id
FROM payment
WHERE
payment_amount = 596.00 OR payment_date = '20-Mar-2003');
Which change could correct the problem?
Remove
the quotes surrounding the date value in the OR clause.
Change
the comparison operator to a single-row operator.
Remove
the parentheses surrounding the nested SELECT statement.
Change
the outer query WHERE clause to 'WHERE payment_id IN'. (*)
Which statement about the ANY
operator, when used with a multiple-row subquery, is true?
The ANY
operator can be used with the LIKE and IN operators.
The ANY
operator can be used with the DISTINCT keyword.
The ANY
operator compares every value returned by the subquery. (*)
The ANY
operator is a synonym for the ALL operator.
Evaluate this SELECT statement
that includes a subquery:
SELECT last_name, first_name
FROM customer
WHERE area_code IN
(SELECT area_code
FROM sales
WHERE
salesperson_id = 20);
Which statement is true about the given subquery?
The
outer query executes before the nested subquery.
Both
the inner and outer queries must return a value, or an error occurs.
An
error occurs if either the inner or outer queries do not return a value.
The
results of the inner query are returned to the outer query. (*)
Single row subqueries may not
include this operator:
>
ALL (*)
<>
=
The WITH clause enables a SELECT
statement to define the subquery block at the start of the query, process the
block just once, label the results, and then refer to the results multiple
times. True or False?
True
(*)
False
No comments:
Post a Comment