- · A function that returns the current date and time of the database server.-SYSDATE
- · Add calendar months to date- ADD_MONTHS
- · Last day of the month- LAST_DAY
- · Next day of the date specified- NEXT_DAY
- · Number of months between due dates- MONTHS_BETWEEN
1. For DJs on Demand, display the number of months
between the event_date of the Vigil wedding and today’s date. Round to the
nearest month.
SELECT name, event_date, ROUND(MONTHS_BETWEEN(SYSDATE, event_date)) as
"number of months"
FROM d_events
WHERE name = 'Vigil wedding';
2. Display the days between the start of last
summer’s school vacation break and the day school started this year. Assume
30.5 days per month. Name the output “Days.”
SELECT TO_DATE('20-Sep-2016',
'dd-Mon-yyyy') - TO_DATE('05-Jun-2016', 'dd-Mon-yyyy') as "Actual
Days", ROUND(
MONTHS_BETWEEN(TO_DATE('20-Sep-2016', 'dd-Mon-yyyy'), TO_DATE('05-Jun-2016',
'dd-Mon-yyyy'))*30.5, 0) as "Days"
FROM dual;
3. Display the days between January 1 and
December 31.
SELECT TO_DATE('31-Dec-2016', 'dd-Mon-yyyy') - TO_DATE('01-Jan-2016',
'dd-Mon-yyyy') as "Actual Days"
FROM dual;
4. Using one statement, round today's date to the
nearest month and nearest year, and truncate it to the nearest month and
nearest year. Use an alias for each column.
SELECT ROUND(SYSDATE, 'Month') as "nearest first day of
month", ROUND(SYSDATE, 'Year') as "nearest first day of Year",
TRUNC(SYSDATE, 'Month') as "current month's 1st day", TRUNC(SYSDATE,
'Year') as "current year's 1st day"
FROM dual;
5. What is the last day of the month for June
2005? Use an alias for the output.
SELECT LAST_DAY(To_date('01-Jun-2005', 'dd-Mon-yyyy'))
FROM dual;
6. Display the number of years between the Global
Fast Foods employee Bob Miller’s birth-day and today. Round to the nearest
year.
SELECT first_name, last_name , ROUND(MONTHS_BETWEEN(SYSDATE,
birthdate)/12) "No of Years"
FROM f_staffs
WHERE first_name || ' ' || last_name = 'Bob Miller';
7. Your next appointment with the dentist is six
months from today. On what day will you go to the dentist? Name the output,
“Appointment.”
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6),'dd-Mon-yyyy (DY)') as
"Appointment"
FROM dual;
8. The teacher said you have until the last day
of this month to turn in your research paper. What day will this be? Name the
output, “Deadline.”
SELECT TO_CHAR(LAST_DAY(SYSDATE),'dd-Mon-yyyy (Day)') as
"Deadline"
FROM dual;
9. How many months between your birthday this
year and January 1 next year?
SELECT
TO_DATE('11/05/2016','mm/dd/yyyy') "B'Day current year", ADD_MONTHS(TO_DATE('11/05/2016','mm/dd/yyyy'),12) "B'Day next year", TRUNC( ADD_MONTHS(TO_DATE('11/05/2016','mm/dd/yyyy'),12),
'Year') "1st day next
Year" , ROUND(MONTHS_BETWEEN( TRUNC(
ADD_MONTHS(TO_DATE('11/05/2016','mm/dd/yyyy'),12), 'Year') , TO_DATE('11/05/2016','mm/dd/yyyy'))) "Rounded Months to next 1st
jan"
FROM dual;
10. What’s the date
of the next Friday after your birthday this year? Name the output, “First
Friday.”
SELECT
TO_DATE('11/05/2016','mm/dd/yyyy') "B'Day current year", NEXT_DAY(TO_DATE('11/05/2016','mm/dd/yyyy'),
'Friday') "First Friday"
FROM dual;
11. Name a date
function that will return a number.
MONTHS_BETWEEN
12. Name a date
function that will return a date.
ADD_MONTHS
13. Give one example
of why it is important for businesses to be able to manipulate date data?
Say, my credit card payment is due on 09th every month. But
if 09th is on weekend or bank holiday, this due date has to be
shifted to next Monday while triggering late payment workflow. This is a very
common and nearest to me example of why business needs to manipulate date data.
1. Using DUAL, write
a statement that will convert 86.678 to 86.68.
SELECT ROUND(86.678, 2)
FROM dual;
2. Write a statement that will display the DJs on
Demand CD titles for cd_numbers 90 and 91 in uppercase in a column headed “DJs
on Demand Collections.”
SELECT UPPER(title) "DJs on
Demand Collections"
FROM d_cds
WHERE cd_number IN( 90, 91);
3. Write a statement that will create computer
usernames for the DJs on Demand partners. The usernames will be the lowercase
letters of the last name + the uppercase first letter in the first name. Title
the column “User Passwords.” For example, Mary Smythers would be smythersM.
SELECT LOWER(last_name) || UPPER(SUBSTR(first_name, 1,1) ) "User
Passwords"
FROM d_partners;
4. Write a statement that will convert “It’s a
small world” to “HELLO WORLD.”
SELECT UPPER(REPLACE( 'It''s a small world' , 'It''s a small', 'hello'
)) as converted
FROM dual;
5. Write a statement that will remove the
“fiddle” from “fiddledeedee” and the “dum” from “fiddledeedum.” Display the
result “fiddledeedeedee” in a column with the heading “Non-sense.”
SELECT REPLACE('fiddledeedum',
'dum') || REPLACE('fiddledeedee',
'fiddle') "Non-sense"
FROM dual;
6. Replace every “i” in Mississippi with “$.”
SELECT REPLACE('Mississippi ',
'i', '$') "Converted"
FROM dual;
7. Using DUAL, convert 5332.342 to 5300.
SELECT TRUNC(5332.342, -2) as
"Truncated", ROUND(5332.342, -2) as "Rounded"
FROM dual;
Both will work here. But if input would have been 5362.342,
trunc only had given desired output.
8. Using DUAL, convert 3.14159 to 3.14.
SELECT TRUNC(3.14159 , 2) as
"Truncated", ROUND(3.14159 , 2) as "Rounded"
FROM dual;
Both will work here too.
9. Using DUAL, convert 73.892 to 73.8.
SELECT TRUNC(73.892 , 1) as "Truncated",
ROUND(73.892 , 1) as "Rounded"
FROM dual;
Note: Here only trunc
works. Round gives 73.9
10. What is the next
Friday six months from now? Label the column “Future.”
SELECT TO_CHAR( NEXT_DAY( ADD_MONTHS(SYSDATE,6),
'Friday'), 'dd-Mon-yyyy (Day)') "Future"
FROM dual;
11. What is the date
10 years from now? Label the column “Future.”
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 10*12), 'dd-Mon-yyyy (Day)')
"Future"
FROM dual;
12. Leap years occur
every four years. Remember, 2004 was a leap year. Now create a function that
will show the date of the next leap year as 29-Feb-2008. Label the column “Future.”
SELECT TO_CHAR( LAST_DAY( To_Date('01-Feb-2008', 'dd-Mon-yyyy') )
, 'dd-Mon-yyyy (Day)') "Future"
FROM dual;
13. Write a statement
that will find any of the DJs on Demand CD themes that have an “ie” in their names.
SELECT description
FROM d_themes
WHERE description
like '%ie%';
14. Write a statement
that will return only the DJs on Demand CDs with years greater than 2000 but
less than 2003. Display both the title and year.
SELECT title, year
FROM d_cds
WHERE year > 2000 AND year < 2003;
15. Write a statement
that will return the employer database employee’s employee ID and his starting
hire dates between January 1, 1997 and today. Display the result ordered from
most recently hired to the oldest.
SELECT employee_id,
hire_date
FROM employees
WHERE hire_date
BETWEEN TO_DATE( '01-Jan-1997',
'dd-Mon-yyyy') AND SYSDATE
ORDER BY hire_date DESC;
No comments:
Post a Comment