After
completing this lab you should be able to
·
Be
able to use Oracle functions in SELECT statements
·
Understand
the GROUP BY clause
Turn in the SQL and output file into the Drop Box for this assignment by 11:55 PM on its due date.
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