Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: 2 challenging SQL questions

    Hi

    I need to produce a SQL report and there are two requirement from the company which I have question to complete with. The script can be created by SQL or PL/SQL. It's quite urgent, can someone help me

    1. Create a table HOUSEKEEPING which can be used to record when each room is cleaned. Cleaning may be of two types (i) change of bed linen and thorough clean (done before each stay, and after a resident has been in a room for a week) (ii) make bed and provide fresh towels, normally done daily when a stay extends over more than one night. Ensure that appropriate integrity controls are put in place.

    2. Write an SQL script for the cleaners to record cleaning activity on a daily basis. All required input data is to be taken from the keyboard. Enter enough data so that some occupied rooms get each type of cleaning, and so that at least one long stay room is left without new bedding, and one short stay is left without fresh towels. Also clean one or two rooms that are unoccupied.

    Produce a management exception report to highlight any such problems.


    The following is the SQL data:
    ********************************************
    drop sequence bookings;

    create sequence bookings
    increment by 1
    start with 1
    nocache
    order;

    drop table booking;
    drop table Charges;
    drop table History;
    drop table Guest;
    drop table Accommodation;
    drop table Service;

    create table Service
    (
    ServiceCode char(5) not null,
    Description varchar(35) not null,
    Charge number,
    CONSTRAINT Service_ServiceCode_pk PRIMARY KEY (ServiceCode)
    );

    create table booking
    (
    Booking_No number,
    BookingName char(25) not null,
    ContactNumber varchar(15),
    RoomType char(5) not null,
    ArrivalDate date,
    NightStay integer,
    SpecialRequirement varchar(40),
    CONSTRAINT booking_Booking_No_pk PRIMARY KEY (Booking_No)
    );

    create table Charges
    (
    RoomNumber char(5) not null,
    ChargeDate date,
    ServiceCode char(5) not null,
    Quantity integer,
    amount Number(7,2)
    );

    create table Guest
    (
    RoomNumber char(5) not null,
    ArrivalDate date,
    DepartureDate date,
    Adults integer not null,
    Children integer not null,
    ResidentName char(20) not null,
    Address varchar2(30) not null,
    CarRegistration varchar(10)
    );

    create table History
    (
    RoomNumber char(5) not null,
    ArrivalDate date,
    DepartureDate date,
    Adults integer not null,
    Children integer not null
    );


    create table Accommodation
    (
    RoomNumber char(5) not null,
    RoomType char(5) not null,
    DailyRate number,
    Facilities varchar(30),
    CONSTRAINT Accommodation_RoomNumber_pk PRIMARY KEY (RoomNumber)
    );


    insert into Service values ('A','Airport pickup',10.00);
    insert into Service values ('B','Bar service',null);
    insert into Service values ('C','Continential breakfast',7.5);
    insert into Service values ('D','Dinner',45);
    insert into Service values ('F','Full breakfast',12.5);
    insert into Service values ('L','Lunch',null);
    insert into Service values ('T','Telephone',null);
    insert into Service values ('Y','Launday',null);

    insert into Accommodation values('A1', 'S', 50.00, 'Ground floor');
    insert into Accommodation values('A2', 'F', 80.00, 'Ground floor');
    insert into Accommodation values('A3', 'F', 60.00, 'Ground floor');
    insert into Accommodation values('A4', 'D', 90.00, 'Sea view');
    insert into Accommodation values('B1', 'D', 100.00, 'Spa bath');
    insert into Accommodation values('B2', 'F', 70.00, null);
    insert into Accommodation values('B3', 'S', 50.00, null);
    insert into Accommodation values('B4', 'D', 90.00, 'Sea view');
    insert into Accommodation values('C1', 'D', 100.00, 'Spa bath');
    insert into Accommodation values('C2', 'F', 70.00, null);
    insert into Accommodation values('C3', 'D', 60.00, null);
    insert into Accommodation values('C4', 'D', 90.00, 'Sea view');
    insert into Accommodation values('D1', 'B', 120.00, 'Spa bath');
    insert into Accommodation values('D3', 'B', 120.00, 'Four poster bed');



    insert into booking values(bookings.NEXTVAL,'Isaac Newton','(08) 4089 1883','B',
    '24-MAY-04',4,null);
    insert into booking values(bookings.NEXTVAL,'Tori Spelling','(07) 220 051','F',
    '01-JUN-04',14,'Milk for spa');
    insert into booking values(bookings.NEXTVAL,'Dustin Hoffman','0416 4258 8473','S',
    '15-JUN-04',14,'Require airport pickup');
    insert into booking values(bookings.NEXTVAL,'Rose McGowan','(03) 3463 3438','B',
    '14-AUG-04',12,null);
    insert into booking values(bookings.NEXTVAL,'Alyssa Milano','(02) 1434 5870','B',
    '12-JUN-04',1,null);
    insert into booking values(bookings.NEXTVAL,'Eduardo Verastegui','(08) 8185 1042','B',
    '27-MAY-04',3,null);
    insert into booking values(bookings.NEXTVAL,'Holly Marie Combs','0418 143 325','F',
    '30-MAY-04',1,'Arriving late');
    insert into booking values(bookings.NEXTVAL,'Dustin Hoffman','(08) 8185 1062','S',
    '31-MAY-04',1,'Requires baby sitter');
    insert into booking values(bookings.NEXTVAL,'Betty Grable','0418 729 223','D',
    '21-SEP-04',14,'Arriving late');
    insert into booking values(bookings.NEXTVAL,'Adrienne Frantz','(03) 1436 5876','S',
    '25-JUN-04',5,null);


    insert into Charges values('A5','20-FEB-04','L',null,125.95);
    insert into Charges values('A3','20-FEB-04','A',2,null);
    insert into Charges values('B2','21-FEB-04','A',1,null);
    insert into Charges values('B4','28-FEB-04','Y',null,12.00);
    insert into Charges values('D1','26-FEB-04','D',null,156.10);
    insert into Charges values('A2','27-FEB-04','D',null,44.95);
    insert into Charges values('C3','23-FEB-04','C',2,null);
    insert into Charges values('B4','22-FEB-04','D',null,50.00);
    insert into Charges values('C2','25-FEB-04','C',1,null);
    insert into Charges values('C5','24-FEB-04','F',2,null);

    insert into Charges values('B2','04-MAR-04','T',null,13.25);
    insert into Charges values('B1','28-MAR-04','T',null,38.62);
    insert into Charges values('A3','16-MAR-04','Y',null,259.01);
    insert into Charges values('D3','01-MAR-04','C',3,null);
    insert into Charges values('D1','10-MAR-04','D',null,75.42);

    insert into Guest values('A4','30-APR-04','10-MAY-04',1,1,
    'James Mason','123 High St, Launceston','AH1234');
    insert into Guest values('B1','02-MAY-04',null,2,0,
    'Richenda Carey','1 Cave Ave, Mill Park','HAH345');
    insert into Guest values('B4','08-MAY-04','17-MAY-04',2,0,
    'Dawn Steele','1a Tree Ave, Richmond',null);
    insert into Guest values('D3','27-APR-04','11-MAY-04',2,0,
    'Katherine Brunk','2/32 Wood St, Swan Hill','DVD456');
    insert into Guest values('D1','10-MAY-04','11-MAY-04',3,0,
    'John Travolta','2 Queens Cres, Burnside',null);
    insert into Guest values('B2','01-MAY-04','14-MAY-04',2,2,
    'Edvard Munch','62 Bill Road, Kyabram','AA4567');

    insert into History values('A2','12-NOV-03','18-NOV-03',2,2);
    insert into History values('A3','23-NOV-03','28-NOV-03',2,2);
    insert into History values('A1','12-NOV-03','13-NOV-03',1,0);
    insert into History values('B3','13-NOV-03','15-NOV-03',1,0);
    insert into History values('D1','16-JAN-04','18-JAN-04',2,0);
    insert into History values('C2','23-FEB-04','05-MAR-04',2,1);
    insert into History values('B3','28-NOV-03','08-DEC-03',1,0);
    insert into History values('D3','28-APR-04','01-MAY-04',2,0);
    insert into History values('C2','28-MAR-04','10-APR-04',1,2);
    insert into History values('A2','23-DEC-03','28-DEC-03',2,1);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's quite urgent? are you sure this isn't homework for a school assignment?

    what have you done so far?

    please, let's see some of your attempts to solve the problem...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    2
    This is what I have done. I',m try ing to use the trigger to replace the data that had stay more than seven days. But the system does not allow to continue on. Hope you can help me.

    SET SERVEROUTPUT ON

    drop table Housekeeping;

    create table Housekeeping (
    RoomNumber char(5) not null,
    CleanType char(5),
    Description char(40) );

    INSERT INTO Housekeeping values('A1',null, null);
    INSERT INTO Housekeeping values('A2',null, null);
    INSERT INTO Housekeeping values('A3',null, null);
    INSERT INTO Housekeeping values('A4',null, null);
    INSERT INTO Housekeeping values('B1',null, null);
    INSERT INTO Housekeeping values('B2',null, null);
    INSERT INTO Housekeeping values('B3',null, null);
    INSERT INTO Housekeeping values('B4',null, null);
    INSERT INTO Housekeeping values('C1',null, null);
    INSERT INTO Housekeeping values('C2',null, null);
    INSERT INTO Housekeeping values('C3',null, null);
    INSERT INTO Housekeeping values('C4',null, null);
    INSERT INTO Housekeeping values('D1',null, null);
    INSERT INTO Housekeeping values('D2',null, null);



    CREATE OR REPLACE TRIGGER clean_trig
    BEFORE INSERT OR UPDATE ON Housekeeping


    DECLARE
    v_nightofstay NUMBER;

    BEGIN

    v_nightofstay := :guest.DEPARTUREDATE-:guest.ARRIVALDATE

    IF "V_NIGHTOFSTAY" > 7 THEN
    INSERT INTO Housekeeping values('A1','A', 'ABCDEFG');


    DBMS_OUTPUT.PUT_LINE('|The night have stay: |'||' '||v_nightofstay);
    END IF;


    END;
    /

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where did you get the idea that a trigger was needed?

    what is this trigger supposed to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Your trigger PL/SQL code is not correct:

    Code:
    CREATE OR REPLACE TRIGGER clean_trig
    BEFORE INSERT OR UPDATE ON Housekeeping
    
    
    DECLARE
    v_nightofstay NUMBER;
    
    BEGIN
    
    v_nightofstay := :guest.DEPARTUREDATE-:guest.ARRIVALDATE
    
    IF "V_NIGHTOFSTAY" > 7 THEN
    INSERT INTO Housekeeping values('A1','A', 'ABCDEFG');
    
    
    DBMS_OUTPUT.PUT_LINE('|The night have stay: |'||' '||v_nightofstay);
    END IF;
    
    
    END;
    /

    1. Since the trigger is on housekping table, you cannot use this directly:

    v_nightofstay := :guest.DEPARTUREDATE-:guest.ARRIVALDATE

    you have to use :OLD.housekping-table-column or :NEW.housekping-table-column

    2. You don't need " " here:

    IF "V_NIGHTOFSTAY" > 7 THEN


    Hope that helps,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •