Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE SCHEMA Golf_Trips;
- SET SEARCH_PATH = 'golf_trips';
- CREATE DOMAIN customerID int;
- CREATE DOMAIN customerName varchar(20);
- CREATE DOMAIN customerSurname varchar(20);
- CREATE DOMAIN address varchar(40);
- CREATE DOMAIN country varchar(40);
- CREATE DOMAIN zip varchar(5);
- CREATE DOMAIN email varchar(40);
- CREATE DOMAIN phone varchar(12);
- CREATE TABLE Employee (
- empID int,
- empName varchar(20),
- empSurname varchar(20),
- dateOfBirth date,
- email varchar(40),
- phone varchar(12),
- address varchar(40),
- zip varchar(5),
- position varchar(10),
- courtID int,
- PRIMARY KEY (empID)
- );
- CREATE INDEX FK_courtID ON Employee (courtID);
- CREATE TABLE Customer (
- customerID customerID,
- custName customerName,
- custSurname customerSurname,
- address address,
- country country,
- zip zip,
- email email,
- phone phone,
- PRIMARY KEY (customerID)
- );
- CREATE TABLE Golf_Court (
- courtID int,
- courtName varchar(50),
- location varchar(50),
- pricePerHour real,
- noOfHoles smallint,
- carrier boolean,
- trainer boolean,
- clubsRent boolean,
- cartRent boolean,
- PRIMARY KEY (courtID)
- );
- CREATE TABLE Cart (
- cartID int,
- noOfSeats smallint,
- courtID int,
- PRIMARY KEY (cartID)
- );
- ALTER TABLE Cart ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID) ON UPDATE CASCADE ON DELETE SET NULL ;
- CREATE TABLE Hole (
- holeID int,
- length real,
- difficulty smallint,
- courtID int,
- PRIMARY KEY (holeID)
- );
- ALTER TABLE Hole ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID) ON UPDATE CASCADE ON DELETE SET NULL ;
- CREATE TABLE Booking (
- bookingID int,
- arrivalDate timestamp,
- departureDate timestamp,
- additionalService varchar(80),
- courtID int,
- customerID int,
- PRIMARY KEY (bookingID)
- );
- ALTER TABLE Booking ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID) ON UPDATE CASCADE ON DELETE SET NULL ;
- ALTER TABLE Booking ADD CONSTRAINT FK_customerID FOREIGN KEY (customerID) REFERENCES customer (customerID) ON UPDATE CASCADE ON DELETE SET NULL ;
- CREATE TABLE Clubs (
- clubsID int,
- material varchar(20),
- courtID int,
- PRIMARY KEY (clubsID)
- );
- ALTER TABLE Clubs ADD CONSTRAINT FK_courtID FOREIGN KEY (courtID) REFERENCES golf_court (courtID) ON UPDATE CASCADE ON DELETE SET NULL ;
- INSERT INTO Customer VALUES (1, 'John', 'Johnson', 'Frederiksgade', 'Denmark', '8700', 'johsenen@gmail.com', '45501142');
- INSERT INTO Customer VALUES (2, 'Anders', 'Dernando', 'Amaliegade', 'Denmark', '9000', 'andersdernando@gmail.com', '45501122');
- INSERT INTO Customer VALUES (3, 'Joanna', 'Pedersen', 'Sundvej', 'Denmark', '1500', 'joannapedersen@gmail.com', '45513142');
- INSERT INTO Customer VALUES (4, 'Mikkel', 'Drytkov', 'Daukanto', 'Lithuania', '20175', 'mikkeldrytkov@gmail.com', '45171142');
- INSERT INTO Customer VALUES (5, 'Peter', 'Spoder', 'Fredericiagade', 'Denmark', '5000', 'peterspoder@gmail.com', '45502442');
- INSERT INTO Customer VALUES (6, 'Smith', 'Deshen', 'Danskegade', 'Denmark', '1770', 'peterspoder@gmail.com', '45312342');
- INSERT INTO Employee values
- (1,'Vladimir','Petkov','01-03-1943','vladimirpetkov@gmail.com','0416235','Flowers Street 4231','4213','Trainer',1),
- (2,'Igor','Chernyi','05-07-1966','igorchernyi@gmail.com','0423152','Castle Street 4231','4213','Trainer',1),
- (3,'Vasiliy','Beliy','09-02-1988','vasilyibeliy@gmail.com','0425123','Glen Park 3215','4213','Carrier',1),
- (4,'Viktor','Zelenyi','02-10-1999','viktorzelenyi@gmail.com','0429182','Jefersson Motel 3215','4111','Trainer',2),
- (5,'Sasha','Seriy','02-22-1988','sashaseriy@gmail.com','0411123','El Corona 3215','4912','Trainer',2),
- (6,'Kiril','Tarakan','09-02-1812','kiriltarakan@gmail.com','0423123','Idlewood 3215','4213','Carrier',2),
- (7,'Gosha','Rubchinskiy','09-12-1712','gosharubchinskyi@gmail.com','0465423','Grove Street 3215','4213','Trainer',3),
- (8,'Komar','Beliy','09-02-1988','komarbeliy@gmail.com','0425123','Glen Park 2231','4213','Carrier',3);
- INSERT INTO Golf_Court VALUES (1, 'Golden Dragon Court', 'Tokyo', 250.0, 14, true, true, true, true);
- INSERT INTO Golf_Court VALUES (2, 'Virgin Beach Court', 'Hawaii', 430.4, 14, true, false, true, true);
- INSERT INTO Golf_Court VALUES (3, 'Stormy Sea Court', 'Copenhagen', 130.0, 14, true, true, false, true);
- INSERT INTO Golf_Court VALUES (4, 'Privjat Russia Court', 'St.Peterburg', 607.5, 14, false, false, false, false);
- INSERT INTO Cart VALUES (1, 2, 1);
- INSERT INTO Cart VALUES (2, 2, 2);
- INSERT INTO Cart VALUES (3, 4, 3);
- INSERT INTO Cart VALUES (4, 6, 1);
- INSERT INTO Cart VALUES (5, 2, 2);
- INSERT INTO Cart VALUES (6, 4, 3);
- INSERT INTO Hole VALUES (1, 275.5, 3, 1);
- INSERT INTO Hole VALUES (2, 100.0, 5, 2);
- INSERT INTO Hole VALUES (3, 125.9, 8, 3);
- INSERT INTO Hole VALUES (4, 367.0, 4, 1);
- INSERT INTO Hole VALUES (5, 525.4, 2, 2);
- INSERT INTO Hole VALUES (6, 232.4, 10, 3);
- INSERT INTO Clubs VALUES (1, 'Wood', 1);
- INSERT INTO Clubs VALUES (2, 'Steel', 2);
- INSERT INTO Clubs VALUES (3, 'Plastic', 3);
- INSERT INTO Clubs VALUES (4, 'Steel', 1);
- INSERT INTO Clubs VALUES (5, 'Steel', 2);
- INSERT INTO Clubs VALUES (6, 'Steel', 3);
- INSERT INTO Booking VALUES (1, '05-12-2018','05-17-2018','club',1,1);
- INSERT INTO Booking VALUES (2, '05-15-2018','05-21-2018','cart',2,2);
- INSERT INTO Booking VALUES (3, '06-07-2018','06-10-2018','null',3,3);
- INSERT INTO Booking VALUES (4, '06-12-2018','06-15-2018','cart',1,4);
- INSERT INTO Booking VALUES (5, '06-20-2018','06-25-2018','null',2,5);
- INSERT INTO Booking VALUES (6, '07-02-2018','07-07-2018','club cart',3,6);
- -- rich clients want to see list of the golf courts with descending price
- SELECT courtID,
- courtname,
- location,
- priceperhour
- FROM golf_court
- WHERE priceperhour <= 720
- ORDER BY priceperhour DESC;
- -- client wants to see holes and difficulties of the court (we can select 1 court {in WHERE} AND golf_court.courtID = 2)
- SELECT golf_court.courtID,
- courtname,
- hole.difficulty
- FROM golf_court, hole
- WHERE golf_court.courtID = hole.courtID
- GROUP BY golf_court.courtID, hole.difficulty;
- --Selecting a relation with a simple rule
- SELECT *
- FROM golf_court
- WHERE priceperhour > 300;
- --Ordering in descension of courtid
- SELECT *
- FROM golf_court
- ORDER BY courtid DESC;
- --Selecting one column of a relation
- SELECT COUNT(courtid), location
- FROM golf_court
- GROUP BY location;
- --Displays what employees are working on a specific court
- SELECT golf_court.courtname, employee.empname
- FROM employee
- INNER JOIN golf_court ON employee.courtid = golf_court.courtid;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement