Database SQL.sql

-- Generated by Oracle SQL Developer Data Modeler 20.2.0.167.1538
--   at:        2021-12-13 08:55:38 GMT
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



DROP TABLE bookingreceipt CASCADE CONSTRAINTS;

DROP TABLE custbooktable CASCADE CONSTRAINTS;

DROP TABLE customer CASCADE CONSTRAINTS;

DROP TABLE managers CASCADE CONSTRAINTS;

DROP TABLE penaltybill CASCADE CONSTRAINTS;

DROP TABLE restaurant CASCADE CONSTRAINTS;

DROP TABLE stafftable CASCADE CONSTRAINTS;

DROP TABLE "Table" CASCADE CONSTRAINTS;

DROP TABLE waiters CASCADE CONSTRAINTS;

-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

CREATE TABLE bookingreceipt (
    bookingid              NUMBER(8) NOT NULL,
    guestcount             NUMBER(3) NOT NULL,
    time                   TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,
    book_customer_custrid  NUMBER(8) NOT NULL,
    table_tableid          NUMBER(8) NOT NULL,
    managers_managers_id   NUMBER NOT NULL,
    waiters_waiters_id     NUMBER NOT NULL
)
LOGGING;

ALTER TABLE bookingreceipt
    ADD CONSTRAINT bookingreceipt_ck_1 CHECK ( guestcount BETWEEN 1 AND 6 );

ALTER TABLE bookingreceipt ADD CONSTRAINT bookingreceipt_pk PRIMARY KEY ( bookingid );

CREATE TABLE custbooktable (
    customer_customerid  NUMBER(8) NOT NULL,
    fname                VARCHAR2(20) NOT NULL,
    lname                VARCHAR2(20) NOT NULL,
    address              VARCHAR2(50) NOT NULL,
    email                VARCHAR2(50) NOT NULL,
    age                  NUMBER(3) NOT NULL
)
LOGGING;

ALTER TABLE custbooktable ADD CONSTRAINT custbooktable_ck_2 CHECK ( email LIKE '%@%' );

ALTER TABLE custbooktable ADD CONSTRAINT custbooktable_ck_1 CHECK ( age >= 18 );

ALTER TABLE custbooktable ADD CONSTRAINT custbooktable_pk PRIMARY KEY ( customer_customerid );

CREATE TABLE customer (
    customerid     NUMBER(8) NOT NULL,
    fname          VARCHAR2(20) NOT NULL,
    lname          VARCHAR2(20) NOT NULL,
    email          VARCHAR2(50) NOT NULL,
    phonenumber    NUMBER(9) NOT NULL,
    timearrival    TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,
    table_tableid  NUMBER(8) NOT NULL
)
LOGGING;

ALTER TABLE customer ADD CONSTRAINT customer_ck_1 CHECK ( email LIKE '%@%' );

ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( customerid );

CREATE TABLE managers (
    managers_id         NUMBER NOT NULL,
    stafftable_staffid  NUMBER(8) NOT NULL
)
LOGGING;

CREATE UNIQUE INDEX managers__idx ON
    managers (
        stafftable_staffid
    ASC )
        LOGGING;

ALTER TABLE managers ADD CONSTRAINT managers_pk PRIMARY KEY ( managers_id );

ALTER TABLE managers ADD CONSTRAINT managers__un UNIQUE ( stafftable_staffid );

CREATE TABLE penaltybill (
    penaltyid            NUMBER(8) NOT NULL,
    customer_customerid  NUMBER(8) NOT NULL,
    timearrival          TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,
    timeleaving          TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,
    fee                  NUMBER(5, 2) NOT NULL
)
LOGGING;

ALTER TABLE penaltybill ADD CONSTRAINT penaltybill_pk PRIMARY KEY ( penaltyid );

CREATE TABLE restaurant (
    restaurantid  NUMBER(8) NOT NULL,
    address       VARCHAR2(50) NOT NULL,
    phonenumber   NUMBER(9) NOT NULL
)
LOGGING;

ALTER TABLE restaurant ADD CONSTRAINT restaurant_pk PRIMARY KEY ( restaurantid );

CREATE TABLE stafftable (
    staffid                  NUMBER(8) NOT NULL,
    fname                    VARCHAR2(20) NOT NULL,
    lname                    VARCHAR2(20) NOT NULL,
    address                  VARCHAR2(50) NOT NULL,
    phonenumber              NUMBER(9) NOT NULL,
    email                    VARCHAR2(30) NOT NULL,
    dob                      TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,
    startemployment          TIMESTAMP WITH LOCAL TIME ZONE NOT NULL,
    restaurant_restaurantid  NUMBER(8) NOT NULL
)
LOGGING;

ALTER TABLE stafftable ADD CONSTRAINT stafftable_ck_1 CHECK ( email LIKE '%@burgershack.com%' );

CREATE UNIQUE INDEX stafftable__idx ON
    stafftable (
        staffid
    ASC )
        LOGGING;

ALTER TABLE stafftable ADD CONSTRAINT stafftable_pk PRIMARY KEY ( staffid );

CREATE TABLE "Table" (
    tableid                  NUMBER(8) NOT NULL,
    tabletype                NUMBER(3) NOT NULL,
    tablelocation            VARCHAR2(20) NOT NULL,
    restaurant_restaurantid  NUMBER(8) NOT NULL
)
LOGGING;

ALTER TABLE "Table" ADD CONSTRAINT table_pk PRIMARY KEY ( tableid );

CREATE TABLE waiters (
    waiters_id          NUMBER NOT NULL,
    stafftable_staffid  NUMBER(8) NOT NULL
)
LOGGING;

ALTER TABLE waiters ADD CONSTRAINT waiters_pk PRIMARY KEY ( waiters_id );

ALTER TABLE waiters ADD CONSTRAINT waiters__un UNIQUE ( stafftable_staffid );

ALTER TABLE bookingreceipt
    ADD CONSTRAINT bookingreceipt_managers_fk FOREIGN KEY ( managers_managers_id )
        REFERENCES managers ( managers_id )
    NOT DEFERRABLE;

ALTER TABLE bookingreceipt
    ADD CONSTRAINT bookingreceipt_table_fk FOREIGN KEY ( table_tableid )
        REFERENCES "Table" ( tableid )
    NOT DEFERRABLE;

ALTER TABLE bookingreceipt
    ADD CONSTRAINT bookingreceipt_waiters_fk FOREIGN KEY ( waiters_waiters_id )
        REFERENCES waiters ( waiters_id )
    NOT DEFERRABLE;

ALTER TABLE bookingreceipt
    ADD CONSTRAINT bookrec_custbook_fk FOREIGN KEY ( book_customer_custrid )
        REFERENCES custbooktable ( customer_customerid )
    NOT DEFERRABLE;

ALTER TABLE custbooktable
    ADD CONSTRAINT custbooktable_customer_fk FOREIGN KEY ( customer_customerid )
        REFERENCES customer ( customerid )
    NOT DEFERRABLE;

ALTER TABLE customer
    ADD CONSTRAINT customer_table_fk FOREIGN KEY ( table_tableid )
        REFERENCES "Table" ( tableid )
    NOT DEFERRABLE;

ALTER TABLE managers
    ADD CONSTRAINT managers_stafftable_fk FOREIGN KEY ( stafftable_staffid )
        REFERENCES stafftable ( staffid )
    NOT DEFERRABLE;

ALTER TABLE penaltybill
    ADD CONSTRAINT penaltybill_customer_fk FOREIGN KEY ( customer_customerid )
        REFERENCES customer ( customerid )
    NOT DEFERRABLE;

ALTER TABLE stafftable
    ADD CONSTRAINT stafftable_restaurant_fk FOREIGN KEY ( restaurant_restaurantid )
        REFERENCES restaurant ( restaurantid )
    NOT DEFERRABLE;

ALTER TABLE "Table"
    ADD CONSTRAINT table_restaurant_fk FOREIGN KEY ( restaurant_restaurantid )
        REFERENCES restaurant ( restaurantid )
    NOT DEFERRABLE;

ALTER TABLE waiters
    ADD CONSTRAINT waiters_stafftable_fk FOREIGN KEY ( stafftable_staffid )
        REFERENCES stafftable ( staffid )
    NOT DEFERRABLE;

INSERT INTO restaurant VALUES (101, '5 Deerpark Rise, Ashbourne', 0894248282);
INSERT INTO restaurant VALUES (102, '10 Brokey Rise, Athboy', 0894144224);
INSERT INTO restaurant VALUES (103, '15 Slane Road, Phibsborough', 0894113222);
INSERT INTO restaurant VALUES (104, '20 Park Lane, Glasnevin', 0892142311);
INSERT INTO restaurant VALUES (105, '25 Fake Street, Finglas', 0894124221);

INSERT INTO "Table" VALUES (721, 2, 'window', 101);
INSERT INTO "Table" VALUES (722, 4, 'interior', 102);
INSERT INTO "Table" VALUES (723, 2, 'window', 103);
INSERT INTO "Table" VALUES (724, 8, 'interior', 104);
INSERT INTO "Table" VALUES (725, 6, 'window', 105);


INSERT INTO customer VALUES (1006, 'Ryan', 'Deguara', 'ryanuser@gmail.com', 0892728201, '02-JUN-21 08:30:55', 721);
INSERT INTO customer VALUES (1007, 'Tony', 'Stark', 'tonyuser@gmail.com', 0892221201, '01-JUL-21 18:33:51', 722);
INSERT INTO customer VALUES (1008, 'Steve', 'Rodgers', 'steveuser@gmail.com', 0822748311, '22-APR-21 12:14:35', 723);
INSERT INTO customer VALUES (1009, 'Peter', 'Parker', 'peteruser@gmail.com', 0822748202, '01-DEC-21 22:22:51', 724);
INSERT INTO customer VALUES (1010, 'Moff', 'Gideon', 'moffuser@gmail.com', 0892228202, '12-JAN-21 13:45:21', 725);

INSERT INTO penaltybill VALUES (1901, 1006,'02-JUN-21 08:22:55', '02-JUN-21 10:37:12', 50.30);
INSERT INTO penaltybill VALUES (1902, 1007,'06-APR-21 09:27:21', '06-APR-21 11:22:52', 155.40);
INSERT INTO penaltybill VALUES (1903, 1008,'03-JUN-21 03:31:31', '03-JUN-21 07:24:51', 150.30);
INSERT INTO penaltybill VALUES (1904, 1009,'12-JUL-21 01:11:15',  '12-JUL-21 05:21:25', 540.30);
INSERT INTO penaltybill VALUES (1905, 1010,'20-SEP-20 04:33:11',  '20-SEP-21 06:12:32',  78.42);


INSERT INTO custbooktable VALUES ( 1006,'David', 'Jones', '5 Brookworth Grove, Slane Road', 'daviduser@gmail.com', 20);
INSERT INTO custbooktable VALUES ( 1007,'Brandon', 'Ross', '10 Ashbrook Rise, Athboyne', 'brandonuser@gmail.com', 55);
INSERT INTO custbooktable VALUES ( 1008,'Adam', 'Marley','2 Cluan Ri, Ashbourne', 'adamuser@gmail.com', 24);
INSERT INTO custbooktable VALUES ( 1009,'Luke', 'Harley', '4 Alderbrook Street, Slane Road', 'lukeuser@gmail.com', 20);
INSERT INTO custbooktable VALUES ( 1010,'Emma', 'Reece','72 Ashwood Avenue, Finglas', 'emmauser@gmail.com', 29);


INSERT INTO staffTable VALUES( 301, 'Keith', 'Gore', '5 Keithpark Rise, Ballyfermot', 0862411899, 'keithuser@burgershack.com', '27-JAN-2000', '20-JAN-2021', 101 );
INSERT INTO staffTable VALUES( 302, 'Adam', 'Leroy', '10 Smithfield Park, Ballymun', 0862231249, 'adamuser@burgershack.com', '22-MAR-2002', '22-APR-2020', 102 );
INSERT INTO staffTable VALUES( 303, 'John', 'McKenna','15 Whitsworth Lane, Balbriggan', 0862412219, 'johnuser@burgershack.com', '11-APR-2000', '05-SEP-2015', 103);
INSERT INTO staffTable VALUES( 304, 'Logan', 'Poulsen', '23 Alderbrook Street, Ashbourne', 0862411229, 'loganuser@burgershack.com', '03-AUG-2001', '22-DEC-2018', 104);
INSERT INTO staffTable VALUES( 305, 'James', 'Pearse','42 Johnswood Drive, Athboy', 0862412122, 'jamesuser@burgershack.com', '15-JUN-1999', '11-JUL-2020', 105);
INSERT INTO staffTable VALUES( 201, 'Amy', 'Brian', '8 Keithpark Rise, Ballyfermot', 0862563899, 'amyuser@burgershack.com', '27-JAN-2000', '20-JAN-2021', 101 );
INSERT INTO staffTable VALUES( 202, 'Elena', 'Jane', '13 Smithfield Park, Ballymun', 0862230459, 'elenauser@burgershack.com', '22-MAR-2002', '22-APR-2020', 102);
INSERT INTO staffTable VALUES( 203, 'Jamie', 'Russel','15 Whitsworth Lane, Balbriggan', 0861862219, 'jamieuser@burgershack.com', '11-APR-2000', '05-SEP-2015', 103);
INSERT INTO staffTable VALUES( 204, 'Jackie', 'Silva', '20 Alderbrook Street, Ashbourne', 0862411864, 'jackieuser@burgershack.com', '03-AUG-2001', '22-DEC-2018', 104);
INSERT INTO staffTable VALUES( 205, 'Rian', 'Smith','42 Johnswood Drive, Athboy', 0862413712, 'riansuser@burgershack.com', '15-JUN-1999', '11-JUL-2020', 105);


INSERT INTO waiters VALUES (66720000,201);
INSERT INTO waiters VALUES (74560000,202);
INSERT INTO waiters VALUES (83179000,203);
INSERT INTO waiters VALUES (98714000,204);
INSERT INTO waiters VALUES (10128000,205);

INSERT INTO managers VALUES (1654, 301);
INSERT INTO managers VALUES (2450, 302);
INSERT INTO managers VALUES (3368, 303);
INSERT INTO managers VALUES (4234, 304);
INSERT INTO managers VALUES (5453, 305);

INSERT INTO bookingreceipt VALUES (1901, 4, '02-JUN-21 10:37:12', 1006, 721, 1654, 66720000);
INSERT INTO bookingreceipt VALUES (1902, 6, '24-JUL-21 16:22:43', 1007, 722, 2450, 74560000);
INSERT INTO bookingreceipt VALUES (1903, 2, '22-DEC-21 01:21:03', 1008, 723, 3368, 83179000);
INSERT INTO bookingreceipt VALUES (1904, 5, '18-MAR-21 10:34:09', 1009, 724, 4234, 98714000);
INSERT INTO bookingreceipt VALUES (1905, 3, '12-APR-21 13:30:04', 1010, 725, 5453, 10128000);


SELECT * FROM bookingreceipt;
SELECT * FROM custbooktable;
SELECT * FROM customer;
SELECT * FROM penaltybill;
SELECT * FROM restaurant;
SELECT * FROM stafftable;
SELECT * FROM "Table";
SELECT * FROM waiters;
SELECT * FROM managers;

-- RIGHT OUTER JOIN to find all the outer values and the ones in common of waiters and staff table

SELECT
    fname,
    lname,
    address,
    email
FROM
    waiters
RIGHT OUTER JOIN staffTable ON
    waiters_id = staffid;

-- UNION to find what times where the number of people is less than 3 or greater than 5 will come

SELECT guestcount, time,
CASE
    WHEN guestcount <= 3 THEN 'Less than/Equal 3 guests'
END
FROM bookingreceipt
WHERE guestcount <= 3
UNION
SELECT guestcount, time,
CASE
    WHEN guestcount >= 5 THEN 'Greater than/Equal 5 guests'
END
FROM bookingreceipt
WHERE guestcount >= 5;

-- INTERSECT to find which customers are assigned to specific tables

SELECT  table_tableID, customerID, fname
   FROM customer
   LEFT JOIN "Table"
   ON table_tableID = tableID
INTERSECT
   SELECT  table_tableID, customerID, fname
   FROM "Table"
   RIGHT JOIN customer
   ON tableID = table_tableID;

-- VIEW create a view to check which customers are at a specific table
DROP VIEW CustomersAtTable;
CREATE VIEW CustomersAtTable AS
SELECT  table_tableID, customerID, fname
   FROM customer
   LEFT JOIN "Table"
   ON table_tableID = tableID
INTERSECT
   SELECT  table_tableID, customerID, fname
   FROM "Table"
   RIGHT JOIN customer
   ON tableID = table_tableID;

select * from CustomersAtTable;

-- LEFT OUTER JOIN to find all the outer ID'S and ID'S in common of the table staffTable's names, addresses, emails of the managers

SELECT
    fname,
    lname,
    address,
    email
FROM
    staffTable
LEFT OUTER JOIN managers ON
    staffID = stafftable_staffID;

-- INNER JOIN to find what time waiters provide the booking receipt

SELECT waiters.waiters_ID, bookingreceipt.time
FROM waiters
INNER JOIN bookingreceipt ON waiters.waiters_ID = bookingreceipt.waiters_waiters_ID;

-- Update guestcount where it is equal to 2 to increment it by 1

UPDATE bookingreceipt 
       SET   guestcount = guestcount + 1
       WHERE guestcount <= 2;  
 
   SELECT guestcount 
       FROM bookingreceipt  
       ORDER BY bookingID;  

-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             9
-- CREATE INDEX                             2
-- ALTER TABLE                             27
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0