Table Dual

1
Table Dual

• Table schema of dual
• Dual (dummyvarchar2(1))
• Table instance
• Dummy
• x

2
Function SysDate

• How to display date/time in SQLPlus?
• Select SysDate
• From Dual
• Select to_char(SysDate, 'Mon-dd-yyyy hhmiss')
• From Dual

3
Formatting Columns

• select room., price 0.9 "Discount" from room
• Select avg(price) from room
• column discount format 999.00 heading "Discount
  Price
• column avg(price) format 999.00 heading Average
  Price
• Clear columns

4
Select Clause

• Select SysDate
• From Booking
• Select 'The system date function', SysDate
• From Booking
• Select SysDate
• From Booking
• Where Hotel_No 'H01'

5
Comparing Dates

• The latest (largest) Date_To
• Select Max(Date_To)
• From Booking
• -- Date and String (char)
• Select Max(To_char(Date_To, 'dd Mon yyyy'))
• From Booking

6
Comparing Dates

• Bookings for April 2005
• Select
• From Booking
• Where Date_from between '1-Apr-05' and
  '30-Apr-05'
• Or Date_to between '1-Apr-05' and '30-Apr-05
  '
• -- Correct?

7
Comparing Dates

• Bookings for April 2005
• Select
• From Booking
• Where Date_from between '1-Apr-05' and
  '30-Apr-05'
• Or Date_to between '1-Apr-05' and '30-Apr-05'
• Or Date_from lt '1-Apr-05' and
• Date_To gt '30-Apr-05 '

8
Comparing Dates

• Bookings for April 2005
• Select
• From Booking
• Where not Date_from gt '30-Apr-05'
• and not Date_to lt '1-Apr-05 '

9
Comparing Dates

• Bookings for April 2005
• Select
• From Booking
• Where not (Date_from gt '30-Apr-05' Or
• Date_to lt '1-Apr-05')
• -- DeMorgans Law

10
Comparing Dates

• Bookings for April 2005
• Select
• From Booking
• Where Date_from lt '30-Apr-05'
• and Date_to gt '1-Apr-05'

11
Comparing Dates

• Bookings this year
• Select
• From Booking
• Where To_char(Date_from, 'yyyy')
  To_Char(SysDate, 'yyyy')
• Or To_Char(Date_to, 'yyyy')
  To_Char(SysDate, 'yyyy')
• -- Assuming no bookings are more than one year

12
Function Last_Day

• Bookings of the current month
• -- Assuming bookings could be more than one month
• Select
• From Booking
• Where Date_From lt Last_Day(SysDate)
• and Date_To gt (SysDate - To_Number(To_Char(Sys
  Date, 'dd')) 1)

13
Function Add_Months

• Bookings of the previous/next month
• -- Assuming bookings could be more than one month
• Select
• From Booking
• Where Date_From lt Last_Day(Add_Months(SysDate,
  -1))
• and Date_To gt ((Add_Months(SysDate, -1)) -
  
• To_Number(To_Char(Add_Months(SysDate,
  -1), 'dd')) 1)

14
Function Next_Day

• select sysdate, next_day(sysdate, 'wednesday')
• from dual

15
Assignment 8

• Some updates
• Due Monday before class
• Drop file
• Turn in hard copy