# Database Discussions > Oracle >  SQL*Plus

## sangeetailor

Hi I have to make a hotel booking data base as part of my university coursework.

For one of the relations I have to store a time. When creating the relation, what data type would i set for the attribute. I'm really confused as what to do. Can anyone help?

----------


## skhanal

Use DATE, it includes both date and time.

----------


## sangeetailor

- thanks for your help everyone, its really appreciated. I finally understand SQL, and actually found my self getting better at it!

----------


## skhanal

you have to provide date part, if not then you have to use to_date() function with time format so that Oracle will put default date value.

insert into table (datecol)
values (to_date('13:49','hh24:mi'));

----------


## sangeetailor

Hi tried what u said, I did the following:

SQL> insert into test (time) values
  2  (to_date('13:49','hh24:mi'));

1 row created.
-------------

But when i view the table, a date shows up.

SQL> select * from test;

TIME
---------
01-FEB-04

-----------

I created the table with the following properties:

SQL> create table test (
  2  time date);

Table created.

----------


## skhanal

That's how Oracle shows date value by default, you have to do to_date while you select also.

select to_date(time, 'hh24:mi') from test;

----------


## YuckFou

to exctract time part from date column use function to_char()

select to_char(time, 'hh24:mi') from test;

----------


## sangeetailor

- thanks for your help everyone, its really appreciated. I finally understand SQL, and actually found my self getting better at it!

----------


## sangeetailor

YECKFOU, THE CODE YOU PROVIDED WORKS
THANKS SO MUCH!

----------


## sangeetailor

- thanks for your help everyone, its really appreciated. I finally understand SQL, and actually found my self getting better at it!

----------


## YuckFou

No, for inserting use to_date()

----------


## sangeetailor

ok thanks,

----------


## sangeetailor

I have the following table;

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RDATE                                              DATE
 ROOM_NO                                            VARCHAR2(2)
 SESSION_B                                          VARCHAR2(9)
 BEVERAGE                                           VARCHAR2(10)
 QTY                                                NUMBER(3)
 TIME                                               DATE


If i wanted to do a select statement, to show all entries in the table, with the Time displaying as an actual time
would I have to put all of the fields into the select statement?

I've used the following:

select rdate, room_no, session_b, beverage, qty, to_char(time, 'hh24:mi') from BEVERAGE_BOOKING;

Is there any other way of performing this operation?

----------


## sangeetailor

two tables with following properties:
SQL> describe test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TIME                                               DATE
 PKTEST                                             VARCHAR2(10)


SQL> describe test2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PKTEST                                    NOT NULL VARCHAR2(10)
 PKNAME                                             VARCHAR2(15)


Im trying to create foreign keys references an attributes in another table, but i keep getting the following error:

SQL> alter table test modify (
  2  pktest varchar2(10),
  3  foreign key (pktest) references test2);
foreign key (pktest) references test2)
*
ERROR at line 3:
ORA-02253: constraint specification not allowed here

----------


## YuckFou

1. check if there's a PK defined on main table where you want to add FK

ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (pktest);

2. to add FK you don't need to modify column, only add constraint

ALTER TABLE test ADD CONSTRAINT test_test2_pktest_fk
FOREIGN KEY (pktest) REFERENCES test2(pktest);

----------


## sangeetailor

hi,

i created tables and have out data in, them.

Now i want to go back and set primary keys to some of the tables, how do i do this?

i have tried:

SQL> alter table ORGANISATION modify (
  2  primary key (orgcode)
  3  );
primary key (orgcode)
*
ERROR at line 2:
ORA-02253: constraint specification not allowed here

and I get the above error. what do i do?

----------


## skhanal

alter table ORGANISATION add constraint PK_ORGANISATION primary key (orgcode);

----------


## sangeetailor

thanks for above help.

I have table Private_Event

(Event (PK), CustomerID, Nosexp)

and table Customer

(CustomerID (PK), FirstName, Surname, Address)

For the Private_Event table i want to state that CustomerID is referenced from Customer table.How do I do this? I forgot to do this during the create table stage. I tried the following, which did not work:

SQL> alter table PRIVATE_EVENT modify (
  2  customerid number(4) references CUSTOMER
  3  );
customerid number(4) references CUSTOMER
                     *
ERROR at line 2:
ORA-02253: constraint specification not allowed here

----------


## skhanal

try

alter table private_event add foreign key (customerid)
references customer(customerid);

----------


## sangeetailor

I have a problem setting foreign keys for one of my tables:

SQL> DESCRIBE EQUIPMENT_BOOKING;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RDATE                                     NOT NULL DATE
 ROOM_NO                                   NOT NULL VARCHAR2(2)
 SESSION_B                                 NOT NULL VARCHAR2(9)
 SERIALNO                                  NOT NULL VARCHAR2(18)

RDATE     RO SESSION_B SERIALNO
--------- -- --------- ------------------
05-JUL-04 A7 Afternoon 444444444444444444
04-JUL-04 A6 Morning   555555555555555555
05-JUL-04 A7 Afternoon 666666666666666666

===========
AND:
===============

SQL> DESCRIBE ROOM_BOOKING;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RDATE                                     NOT NULL DATE
 ROOM_NO                                   NOT NULL VARCHAR2(2)
 SESSION_B                                 NOT NULL VARCHAR2(9)
 EVENT                                              VARCHAR2(30)
 LAYOUT                                             VARCHAR2(1)



RDATE     RO SESSION_B SERIALNO
--------- -- --------- ------------------
05-JUL-04 A7 Afternoon 666666666666666666
05-JUL-04 A7 Afternoon 444444444444444444
04-JUL-04 A6 Morning   555555555555555555


====

I am trying to set a foreign key from EQUIPMENT_BOOKING to ROOM_BOOKING; so that rdate,room_no,session_b FROM EQUIPMENT_BOOKING is referenced from ROOM_BOOKING.

I tried the below code, and got the following error:


SQL> alter table EQUIPMENT_BOOKING add foreign key(rdate) references ROOM_BOOKING(rdate);
alter table EQUIPMENT_BOOKING add foreign key(rdate) references ROOM_BOOKING(rdate)
                                                                             *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

----------


## sangeetailor

In addition to above problem, I have another. I'm trying to run a query which returns reults of bookings tommorow:

SQL> select mdate,event,quantity
  2  from MEAL_BOOKING
  3  WHERE mdate = sysdate+1;

I've tried the above, but it does not work.

----------


## skhanal

It seems you don't have primary key defined in ROOM_BOOKING.

alter table ROOM_BOOKING add constraint PK_ROOM_BOOKING primary key (rdate,room_no,session_b);

alter table EQUIPMENT_BOOKING add foreign key(rdate,room_no,session_b) references ROOM_BOOKING(rdate,room_no,session_b);

----------


## skhanal

You have to consider time part when you use =. Truncate the time part.


select mdate,event,quantity
from MEAL_BOOKING
WHERE TRUNC(mdate) = TRUNC(sysdate+1);

----------


## sangeetailor

hi,

regarding the foreign key issue,

i already had primary keys assigned, but it still wouldnt let me reference a foreign ket to the ROOM_BOOKING table.

I even dropped the pk constraint, and re-entered it, but still same problem. There is just a problem referencing to ROOM_BOOKING table.

Thanks for the help on the sysdate query.

----------


## sangeetailor

in the calculation of the % of occupancy rate, does anyone knw how to stop oracle display the decimal values of the percentage?

eg: 1.11111111111111

removing additional 1's after the decimal point, so only 1 is displayed.


i've done the following to get to the above answer:

select room_no, count(rdate)
FROM ROOM_BOOKING
where rdate in
(select rdate
from ROOM_BOOKING
where rdate between '01APR04' and '30APR04')
group by room_no
having count(rdate) >=0;



SQL> CREATE VIEW room_count
  2  as select room_no as rm_no, count(rdate) as countalais
  3  FROM ROOM_BOOKING
  4  where rdate in
  5  (select rdate
  6  from ROOM_BOOKING
  7  where rdate between '01APR04' and '30APR04')
  8  group by room_no
  9  having count(rdate) >=0;

View created.

SQL> select rm_no, countalais/90*100 ||'%'|| countalais
  2  from room_count;

RM
--
COUNTALAIS/90*100||'%'||COUNTALAIS
--------------------------------------------------------------------------------
A2
1.11111111111111111111111111111111111111%1

A6
1.11111111111111111111111111111111111111%1

B6
2.22222222222222222222222222222222222222%2

----------


## sangeetailor

Im trying to run a query:

select distinct con_room_count.rm_no,con_room_count.totalbookings
from con_room_count, CONFERENCE_ROOM
where con_room_count.rm_no = CONFERENCE_ROOM.room_no
union
select distinct CONFERENCE_ROOM.room_no,con_room_count.totalbookin  gs-con_room_count.totalbookings
from CONFERENCE_ROOM, con_room_count;

the following results are produced:
RM TOTALBOOKINGS
-- -------------
A1             0
A2             0
A2             1
A6             0
A6             2
A7             0
A7             1
B2             0
B3             0
B3             1
B4             0

RM TOTALBOOKINGS
-- -------------
B5             0
B6             0
B6             2
B7             0

15 rows selected

However teh table has duplicated for some room numbers (A7,A6,etc..) we do not want this query to display duplicates.
How can it be done, so that duplicates do not show?

----------


## skhanal

When creating the foreign key you have to use all columns in the primary key. From the example you provided it looks like you only used rdate.

----------


## skhanal

To display only one decimal use TRUNC function

select rm_no, trunc(countalais/90*100,2)) ||'%'|| countalais
from room_count;

----------


## skhanal

For A6 and A7 the room booking numbers are different, they are not duplicate.

----------

