Project 2: SQL Functions and Group Expressions

Objectives

After completing this lab you should be able to

Be able to use Oracle functions in SELECT statements

Understand the GROUP BY clause

Description:

1. Write a query to show the current date and time formatted as below. This will be a select statement against the dual table. (10)

30-AUG-2000 22:05:01

2. Write a query to show the current (your) username. This will be a select statement against the dual table. (10)
3. Show via query how many days remain until Christmas. Round fractional days using the numeric function ROUND. (10)
4. Write a query against PAYDAY that will show the number of days between the payday (CYCLEDATE column) and the last day of the month (LAST_DAY function). Format your results as below: (20) Days between

payday and first

Month of next month

--------- ----------------

January 16

February 13

March 16

5. Write a query against the LEDGER table to show the names of all person customers with their last name, first name. Exclude companies, schools, stores, churches, the post office, brothers and value with a single word(e.g. BLACKSMITH). Your results should look similar to this: (15)

PERSONS

-------------------

ARNOLD, MORRIS

AUGUST, GEORGE

AUSTIN, JOHN

6. Write a query to show many addresses there are for each area code in the ADDRESS table. Your results should look similar to this: (15)

AREA_CODE COUNT(*)

--------- ----------

317 11

812 22

219 33

7. Write a query against the ADDRESS table to select a list of names and phone numbers. The output should match these requirements:

The name column should contain both the first and last names with a blank space between them. Use the string concatenation operator ||. (5)

Extra space beyond 50 characters on the right will be filled with dots. Use the RPAD function. (5)

The second column will contain the phone number. (5)

Phone number should be in the format (999) 999-9999. Use the SUBSTR function and ||. (5)

Order the query by last name then first name.

Your results should look similar to this:

NAME PHONE

-------------------------------------------------- --------------

FELICIA SEP....................................... (214) 522-8383

FELICIA SZEP...................................... (214) 522-8383

ARNY WERSCHKY..................................... (415) 235-7387

MARY YARROW....................................... (415) 787-2178

JACK ZACK......................................... (415) 620-6842

FRED ZIMMERMAN.................................... (503) 234-7491