275 Lab5 Part 1

gistfile1.txt

DROP TABLE Resort CASCADE CONSTRAINTS;
DROP TABLE Resort_type CASCADE CONSTRAINTS;
DROP TABLE Accomodation CASCADE CONSTRAINTS;
CREATE TABLE Resort (
	resort_id varchar2(2) PRIMARY KEY,
	resort_name varchar(50) NOT NULL,
	FK_resort_type_id varchar2(2) NOT NULL
);

CREATE TABLE Resort_type(
	resort_type_id varchar2(2) PRIMARY KEY,
	resort_type varchar2(10)
);

CREATE TABLE Accomodation(
	accomodation_id varchar2(10) PRIMARY KEY,
	FK_resort_id varchar(2) NOT NULL,
	description varchar2(256) NOT NULL,
	cost_per_night Number(10,2)
);

ALTER TABLE Resort
ADD CONSTRAINT FK_ResortType
FOREIGN KEY(FK_resort_type_id)
REFERENCES Resort_type(resort_type_id);

ALTER TABLE Accomodation
ADD CONSTRAINT FK_Accomodation
FOREIGN KEY(FK_resort_id)
REFERENCES Resort(resort_id);

INSERT INTO Resort_type (resort_type_id,resort_type) VALUES('F','Foodie');
INSERT INTO Resort_type (resort_type_id,resort_type) VALUES('S','Spa');
INSERT INTO Resort_type (resort_type_id,resort_type) VALUES('E','Ecological');
INSERT INTO Resort_type (resort_type_id,resort_type) VALUES('G','Golf');

INSERT INTO Resort(resort_id,resort_name,FK_resort_type_id) VALUES('G1','Golfing by the Sea','G');
INSERT INTO Resort(resort_id,resort_name,FK_resort_type_id) VALUES('S1','Atlantis Spa','G');
INSERT INTO Resort(resort_id,resort_name,FK_resort_type_id) VALUES('E1','Amazing Oregon','E');
INSERT INTO Resort(resort_id,resort_name,FK_resort_type_id) VALUES('L1','Gentle Waves Spa','S');

INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Bunk 1','Cabin on far North Beach','G1',324);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Bunk 2','Cabin on Middle Beach','G1',324);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Bunk 3','Cabin at South Beach','G1',425);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Chamber 1','Room First Floor','E1',250);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Chamber 2','Room Second Floor','E1',250);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Chamber 3','Room Third Floor','E1',300);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Chamber 4','Room Fourth Floor,Ocean view','E1',350);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Room 10','Room First Floor, Spa Side','S1',200);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id) VALUES('Room 15','Room First Floor, Family Area','S1');
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Suite 1','Suite First Floor,Ocean View','L1',450);
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id) VALUES('Suite 2','Suite Second Floor, Ocean View','L1');
INSERT INTO Accomodation (accomodation_id,description,FK_resort_id,cost_per_night) VALUES('Suite 3','Suite Third Floor,Ocean View','L1',400);


/* Select Count */
SELECT count(accomodation_id) AS Total_Accomadations FROM Accomodation;

/* Lowest Cost Accomodation*/
SELECT MIN (cost_per_night) FROM Accomodation;

/* How many Resorts and their type*/
SELECT Resort_type.resort_type_id,Resort_type.resort_type,
	COUNT(Resort.resort_id) as nr_resorts
FROM Resort_type
JOIN Resort ON Resort_type.resort_type_id = Resort.FK_resort_type_id
GROUP BY Resort_type.resort_Type_id, Resort_type.resort_type
ORDER BY nr_resorts DESC;

/* Number of Accomodations Resort by */
SELECT Resort_type.resort_type_id, resort_type.resort_type,
	COUNT(Accomodation.accomodation_id) AS nr_accomdations
FROM Resort_type
JOIN Resort on Resort_type.resort_type_id = Resort.FK_resort_type_id
JOIN Accomodation on Accomodation.FK_resort_id = Resort.resort_id
GROUP BY Resort_type.resort_type_id,Resort_Type.resort_type
HAVING COUNT (Accomodation.accomodation_id) < 4;