Database Programming -Section 5 Exam Review - Teacher
1. What is the most efficient way to display all of the columns and rows in the table shown below?
DEPARTMENTS
SELECT * FROM employees;
2. Which clause in a SELECT statement retrieves information using projection?
SELECT column list
3. Which clause in a SELECT statement retrieves information using selection?
WHERE to limit rows
4. Which of the following are TRUE about SQL statements?
T____a. SELECT or select can be used to name columns
F____b. All keywords such as ORDER BY and DESCENDING can be abbreviated. For example ORDER BY can be abbreviated as ORD BY, and DESCENDING can be abbreviated as DESC.
F____c. Separate lines and indents organize code so it executes faster
F____d. The order of the clauses in a SELECT statement doesn't matter as long as SELECT and FROM and WHERE are included.
5. The SELECT * statement is used to:
F____a. Show only the column names in the table
T____b. View all the data in a table
F____c. Update information in a table
F____d. Show the table structure and data types.
6. Write a statement that includes an example of “SELECTION.”
Answers will vary but must include WHERE clause to choose rows
7. A relational database is made up of __tables______which have horizontal ___rows______and vertical __columns______. The intersection of a horizontal ___row_____and a vertical _column______is called a ___field______.
8. Which of the following are TRUE about Relational Databases.
T____a. Entries in columns are single-valued.
F____b. Entries in columns can be of more than one kind of data (datatype)
F____c. As long as a column has a primary key, identical rows can exist in the same table
T____d. The order of the rows stored in a database table is insignificant
F____e. When naming Ccolumns in a table with the same name,the table names need aliases
9. Give an example of a SQL command that belongs to each category below:
______Data Manipulation Language insert, update, delete, merge
______Data Definition Language create, alter, drop, rename, truncate
______Transaction Control commit, rollback, savepoint
______Data Control Language grant, revoke
10. The System Development Life Cycle is a process similar to:
_____a. Assembling a model of an airplane
__x__b. Designing and building a new type of satellite
_____e. Learning how to use a computer
_____f. Watching a puppy grow up to a dog
11. Write the code that would show the structure of DJ On Demand, d_cds table. Then show the code that would select all data from the DEPT table.
DESC DEPT
SELECT * FROM DEPT
12. Create a query to display unique client numbers from the DJ On Demand d_clients table.
SELECT DISTINCT client_number
FROM d_clients
13. Create a query to display the name, job id, hire date, and employee id for each Oracle employee, with employee id appearing last.
SELECT first_name, last_name, job_id, hire_date, employee_id
FROM employees
14. Write a SELECT statement that will return the first name, last name, salary and the salary with a 5% bonus and $100.00 added (4 columns).
SELECT first_name, last_name, salary, (salary*1.05) + 100
15. Which of the following SELECT statements could represent a transaction of making a car payment with a late fee of 0.1% on unpaid balance. a. SELECT unpaid balance *.001 - payment AS "Transaction"
b. SELECT unpaid balance - payment * .001 AS "Transaction"
c. SELECT balance* 0.001 = (remaining balance - payment) AS "Transaction"
a
16. Using the Oracle database EMPLOYEES table, write a SELECT statement to produce the following output
"Ernst has a monthly salary of 6000 dollars
SELECT last_name ||' has a monthly salary of ' || salary || ' dollars' AS Pay
17. Which of the following is TRUE about the following SELECT statement:
SELECT DISTINCT color, style
FROM shirts
_____a. Blue shirts will only be listed once
__T__ b. All distinct combinations of blue shirts and styles will be listed
_____ c. The DISTINCT keyword only applies to color
_____d. Distinct color and style combinations can be listed more than once or each style.
18. Create a query to display the name and salary of the Global Fast Foods employees earning more than $7.00 an hour.
SELECT first_name, last_name
FROM f_staffs
WHERE salary >7.00
19. Display the name and birthdate of every Global Fast Foods employee who was born in1980;
SELECT first_name, last_name, birthdate
FROM f_staffs
WHERE birthdate LIKE ‘%80’
20. Display the names of all Global Fast Foods employees where the second letter of their name is an "o"
SELECT last_name
FROM f_staffs
WHERE last_name LIKE ‘_o%’
21. For each employee in the Oracle database, display the employee last name and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.
SELECT last_name, ROUND (MONTHS_BETWEEN
(SYSDATE, hire_date)) MONTHS_WORKED
FROM employees
ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date)
This is another way to do it:
SELECT last_name, ROUND (MONTHS_BETWEEN
(SYSDATE, hire_date),0) MONTHS_WORKED
FROM employees
ORDER BY MONTHS_WORKED
22. Each SQL statement below that queries the Global Fast Foods database has errors. Correct them and execute the query in HTML DB.
a. SELECT first name, last name AS Global Staff
FROM f_staff;
SELECT first_name, last_name AS "Global Staff"
FROM f_staff;
b. SELECT first_name |" " | last_name AS 'DJ On Demand Clients'
FROM d_clients;
SELECT first_name |' ' | last_name AS "DJ On Demand Clients"
FROM d_clients;
c. SELECT DISTINCT f_order_lines, DISTINCT order_number
FROM quantity;
SELECT DISTINCT quantity, order_number
FROM f_order_lines;
d. SELECT order number, address, city, state
FROM f_orders;
SELECT order_number
FROM f_orders;
23.Which expression below will produce the largest value?
a. SELECT salary* (6 + 100)/2
b. SELECT salary*6 + 100/2
c. SELECT salary + (100/2)*6
d. SELECT salary+6*100/2
a. SELECT salary* (6 + 100)/2
24. Which SELECT statement will produce the following results?
Oldest"The 1997 recording in our database is The Celebrants Live in Concert"
a. SELECT ||The year recording in our database is ' || title AS "Oldest"
FROM d_cds WHERE year=1997;
b. SELECT "The 1997 recording in our database is ||title AS "Oldest"
FROM d_cds WHERE year=1997;
c. SELECT ' ||The year recording in our database is|| ' title AS "Oldest"
FROM d_cds WHERE year=1997;
d. SELECT 'The '||year||' recording in our database is ' ||title AS "Oldest"
FROM d_cds WHERE year=1997;
d. SELECT 'The '||year||' recording in our database is ' ||title AS "Oldest"
FROM d_cds WHERE year=1997;
25.Which WHERE clauses are invalid? Mark an N for not valid.
N____a, WHERE quantity > NULL;
N____b. WHERE quantity = NULL;
____c. WHERE quantity IS NULL;
N____d. WHERE quantity != NULL;
26. Write a query that produces the following for each Global Fast Food employee: <employee last name> earns<salary> monthly but wants <10% raise>.
Label the column Promotion Salary.
SELECT last_name||' earns '||salary || ' hourly but wants ' ||(salary *1.10) ||'.' "Promotion Salary."
FROM f_staffs;
27. Display the last name, hire date, and day of the week on which the Oracle employees started. Label the column DAY. Order the results by the day of the week starting with Monday.
SELECT last_name, hire_date,
TO_CHAR(hire_date, 'DAY') DAY
FROM employees
ORDER BY TO_CHAR(hire_date - 1, 'd')
28. Which venues did DJ On Demand play that were not in Private Homes and not in Hotels?
SELECT loc_type
FROM d_venues
WHERE loc_type NOT IN('Private Home','Hotel')
29. In the following statement, how will the column data be displayed?
SELECT department_id, last_name, manager_id
FROM employees
WHERE employee_id < 125
ORDER BY department_id DESC, last_name
The department_id's will be listed largest to smallest and within each department the employee last names will be listed from A _ Z
30.Starting with the string 'Oracle Internet Academy', pad the string to createOracle****Internet****Academy****
SELECT RPAD('Oracle',6,'*')||LPAD('Internet',12,'*')||RPAD(LPAD('Academy',11,'*'),15,'*')AS "OIA"
FROM DUAL
31. Use the DUAL table to process the following numbers:
845.559 - round to two decimal place
SELECT round(845.553,2)
FROM dual;
30695.348 - round to zero decimal places
SELECT round(30695.348)
FROM dual;
30695.348 - round to -2 decimal places
SELECT ROUND(30695.348,-2)
FROM dual;
2.3454 - truncate the 54 from the decimal place
SELECT TRUNC(2.3454,2)
FROM dual;
32. Write the code to display the number of months between your birthday this year and your birthday next year?
SELECT ROUND(MONTHS_BETWEEN('10-JAN-05', '10-JAN-04'))
FROM DUAL
Sample answer: 12 MONTHS
33.What is the last day of the month for November 2005?
SELECT LAST_DAY('30-NOV-05') AS "LAST DAY"
FROM DUAL;
34. Your term paper is due one month from today, What day do need to have your paper completed?
SELECT ADD_MONTHS(SYSDATE,1)AS "Due Date"
FROM DUAL
35. Replace every 'e' in "For your eyes only" with an @
SELECT REPLACE('For your eyes only','e','@')
FROM DUAL;
36. Convert your birthday to the following formats:
a. January First, Two Thousand Eighty
SELECT TO_CHAR(TO_DATE('01-JAN-80','DD-MON-YY'),'Month Ddspth, Yyyysp')
FROM DUAL
b. January 1st
SELECT TO_CHAR(TO_DATE('01-JAN-80','dd-MON-yy'),'fmMonth ddth')
FROM DUAL
37. Convert SEPTEMBER302005 to the default date format using the fx format model.
SELECT TO_DATE('SEPTEMBER302005','fxMONTHDDYYYY') AS BIRTHDAY
FROM DUAL
38. You want to be able to replace all null values in the title column of your CD collection table with "need to buy". Write the code that could be used to accomplish this task. Your CDs table has the columns: cd_number and title.
SELECT cd_number, NVL(title, 'need to buy')
From d_cds;
39. You want to compare the manufacturers id number to your product id number. If they are the same, you want to mark the result column marked "null". If they are not the same, return our product id number.
PRODUCTSproduct_id / manuf_id / bin_number
19199 / 09199 / 998
91990 / 91990 / 889
SELECT product_is AS "Product ID", manuf_id AS "Manufacturer ID"
NULLIF(product_id, manuf_id) "Result"
40. From the DJ On Demand d_songs table, create a DECODE query and a CASE query that replaces the 2 min songs with 'omit', the 5 min songs with "short" and the 10 minute songs with "long". All other durations should return the original column value. Label the output column "Play Times"
SELECT id, title, duration,
DECODE(duration, '2 min', 'omit','5 min', 'short','10 min', 'long', duration)
AS "Play Times"
FROM d_songs;
SELECT id, title, duration,
CASE duration WHEN '2 min' THEN 'omit'
WHEN '5 min' THEN 'short'
WHEN '10 min' THEN 'long'
ELSE duration END "As Play Times"
FROM d_songs;
41. Display the first name, last name, auth_expense_amt for all DJ On Demand employees. If they don't have an auth_expense_amt ,display the manager id, if they don't have a manager, then display '99999' as "See Manager"
SELECT first_name, last_name, auth_expense_amt,
COALESCE(auth_expense_amt, manager_id, 99999) AS "See Manager"
FROM d_partners
42. Use any database to create a join that illustrates each of the following. Be prepared to show your answer to the class.
a. Cartesian Product or Cross Join
SELECT last_name, department_name
FROM employees, departments;
SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
b. Equijoin
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
c. Nonequijoin
SELECT e.employee_id, e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary < j.lowest_sal;
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
d. Outer Joins
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id (+) = d.department_id;
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
e. Self Join
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;
f. Natural Joins
SELECT employee_id, last_name, department_name
FROM employees NATURAL JOIN departments;
g. Joins Using
SELECT employee_id, last_name, department_name
FROM employees JOIN departments
USING (department_id);
h. Join On
SELECT e.employee_id, e.last_name, d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
i. Outer Joins Right, Left, Full
SELECT e.employee_id, 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.employee_id, 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.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
Oracle Academy11-May-2005