03 Working with Columns, Characters, and Rows

• ·         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"

FROM f_customers;

2. Each statement below has errors. Correct the errors and execute the query in Oracle

Application Express.

a.

SELECT first name

FROM f_staffs;

SELECT first_name as "First Name"

FROM f_staffs;

b.

SELECT first_name |" " | last_name AS "DJs on Demand Clients"

FROM d_clients;

SELECT first_name ||' ' || last_name AS "DJs on Demand Clients"

FROM d_clients;

c.

SELECT DISCTINCT f_order_lines

FROM quantity;

SELECT DISTINCT quantity

FROM  f_order_lines;

d.

SELECT order number

FROM f_orders;

SELECT order_number

FROM f_orders;

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.

Super  Star

*** Sue *** Sue ***

*** Bob *** Bob ***

*** Monique *** Monique ***

SELECT '***' || first_name || '***' || first_name || '***' as "Super Star"

 FROM f_staffs;

4. Which of the following is TRUE about the following query?

SELECT first_name, DISTINCT birthdate

FROM f_staffs;

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

 CURRENT SALARY

 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"

FROM f_staffs;

6. Create a query that will return the structure of the Oracle database EMPLOYEES table.

Which columns are marked “nullable”? What does this mean?

DESCRIBE employees;

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"

FROM d_cds;

8. True/False -- The following SELECT statement executes successfully: (True)

SELECT last_name, job_id, salary AS Sal

FROM employees;

9. True/False -- The following SELECT statement executes successfully: (True)

SELECT *

FROM job_grades;

10.There are four coding errors in this statement. Can you identify them?

SELECT employee_id, last_name

sal x 12 ANNUAL SALARY

FROM employees;

SELECT employee_id, last_name, salary*12 "ANNUAL SALARY"

FROM employees;

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

b. *

c. DISTINCT id

d. both a and b

SELECT *

FROM f_staffs ;

13.Using SQL to choose the columns in a table uses which capability?

a. selection

b. projection

c. partitioning

d. join

SELECT  c1, c2

14.SELECT last_name AS "Employee". The column heading in the query result will appear

as:

a. EMPLOYEE

b. employee

c. Employee

d. "Employee:

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

"Employees"

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

"Employees"

FROM employees ; - this matches

d. SELECT Mr./Ms. ||first_name||' '||last_name ||' '||"is an employee of our company." AS

"Employees"

FROM employees

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"

FROM employees;

b. SELECT DEPARTMENT_ID, LAST_NAME, FIRST_NAME

FROM employees; - yes

c. SELECT department_id, last_name, first_name AS UPPER CASE

FROM employees

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