Project 4: 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

Due for Lab 4

Turn in the SQL and output file into the Drop Box for this assignment by 11:55 PM on its due date.

Lab Assignment Steps

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

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.

3.      Show via query how many days remain until Christmas. Round fractional days up using the numeric function ROUND.

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:

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, churches, the post office and brothers. Your results should look similar to this:

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:

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 ||.

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

        The second column will contain the phone number.

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

        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