- · A command that suppresses duplicates - DISTINCT
- · Links two columns together to form one character data column -Concatenation Operator
- · A group of character data- Literal Values e.g. string
- · An SQL plus command that displays the structure of a table- DESCRIBE
1. The manager of Global Fast Foods would like to send out coupons for the upcoming sale. He wants to send one coupon to each household. Create the SELECT statement that returns the customer last name and a mailing address.
SELECT last_name as "Last Name", address || ', ' || city || ', ' || state || '-' || zip || CHR(10) || '(Phone-' || phone_number || ')' as "Mailing Address"
2. Each statement below has errors. Correct the errors and execute the query in Oracle
SELECT first name
SELECT first_name as "First Name"
SELECT first_name |" " | last_name AS "DJs on Demand Clients"
SELECT first_name ||' ' || last_name AS "DJs on Demand Clients"
SELECT DISCTINCT f_order_lines
SELECT DISTINCT quantity
SELECT order number
3. Sue, Bob, and Monique were the employees of the month. Using the f_staffs table, create
a SELECT statement to display the results as shown in the Super Star chart.
*** Sue *** Sue ***
*** Bob *** Bob ***
*** Monique *** Monique ***
SELECT '***' || first_name || '***' || first_name || '***' as "Super Star"
4. Which of the following is TRUE about the following query?
SELECT first_name, DISTINCT birthdate
a. Only two rows will be returned.
b. Four rows will be returned.
c. Only Fred 05-Jan-1988 and Lizzie 10-Nov-1987 will be returned.
d. No rows will be returned.
ORA-00936: missing expression
5. Global Fast Foods has decided to give all staff members a 5% raise. Prepare a report that
presents the output as shown in the chart.
EMPLOYEE LAST NAME
SALARY WITH 5% RAISE
SELECT last_name as "EMPLOYEE LAST NAME", salary as "CURRENT SALARY", ROUND(salary*1.05, 2) as "SALARY WITH 5% RAISE"
6. Create a query that will return the structure of the Oracle database EMPLOYEES table.
Which columns are marked “nullable”? What does this mean?
first_name , phone_number, salary, commison_pct, manager_id, department_id and bonus are nullable.
It means that row is valid even if the fields corresponding to these columns do not contain any data.
7. The owners of DJs on Demand would like a report of all items in their D_CDs table with
the following column headings: Inventory Item, CD Title, Music Producer, and Year
Purchased. Prepare this report.
SELECT cd_number as "Inventory Item", title as "CD Title", producer as "Music Producer", year as "Year Purchased"
8. True/False -- The following SELECT statement executes successfully: (True)
SELECT last_name, job_id, salary AS Sal
9. True/False -- The following SELECT statement executes successfully: (True)
10.There are four coding errors in this statement. Can you identify them?
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
SELECT employee_id, last_name, salary*12 "ANNUAL SALARY"
11.In the arithmetic expression salary*12 - 400, which operation will be evaluated first?
* the Multiplication
12.Which of the following can be used in the SELECT statement to return all columns of data
in the Global Fast Foods f_staffs table?
a. column names
c. DISTINCT id
d. both a and b
FROM f_staffs ;
13.Using SQL to choose the columns in a table uses which capability?
SELECT c1, c2
14.SELECT last_name AS "Employee". The column heading in the query result will appear
c because whatever is in “”, is printed as is for column name.
15.Which expression below will produce the largest value? Salary = 20
a. SELECT salary*6 + 100 :: It gives 220
b. SELECT salary* (6 + 100) :: It gives 2120
c. SELECT 6(salary+ 100) :: It gives error
d. SELECT salary+6*100 :: it gives 602
* has higher precedence than +, but this is overcome by ().
16.Which statement below will return a list of employees in the following format?
Mr./Ms. Steven King is an employee of our company.
a. SELECT "Mr./Ms."||first_name||' '||last_name 'is an employee of our company.' AS
FROM employees; - error
b. SELECT 'Mr./Ms. 'first_name,last_name ||' '||'is an employee of our company.'
FROM employees; - two columns
c. SELECT 'Mr./Ms. '||first_name||' '||last_name ||' '||'is an employee of our company.' AS
FROM employees ; - this matches
d. SELECT Mr./Ms. ||first_name||' '||last_name ||' '||"is an employee of our company." AS
17.Which is true about SQL statements?
a. SQL statements are case-sensitive – not always, I can use lower keywords and upper column names, but if I want specific case sensitive column name to be printed I could use “”
b. SQL clauses should not be written on separate lines. – We do
c. Keywords cannot be abbreviated or split across lines. – lock this option
d. SQL keywords are typically entered in lowercase; all other words in uppercase. - no
18.Which queries will return three columns each with UPPERCASE column headings?
a. SELECT "Department_id", "Last_name", "First_name"
b. SELECT DEPARTMENT_ID, LAST_NAME, FIRST_NAME
FROM employees; - yes
c. SELECT department_id, last_name, first_name AS UPPER CASE
d. SELECT department_id, last_name, first_name
FROM employees; - yes
19.Which statement below will likely fail?
a. SELCT * FROM employees; - fail keyword wrong
b. Select * FROM employees; - will do
c. SELECT * FROM EMPLOYEES; - will do
d. SelecT* FROM employees; - will do