--CREATE Database Rental_1402620;

use Rental_1402620;

CREATE TABLE Owner (

ownerID INTEGER PRIMARY KEY,
email VARCHAR(50),
phone VARCHAR(10),
street VARCHAR(50),
town VARCHAR(50),
parish VARCHAR(50)
);

Insert into Owner values

(1001, 'jansenq@gmail.com', '8765552210', '33 Clue Way',
'May Pen', 'Clarendon'),
(1002, 'devindoor@gmail.com', '8767779865', 'Baxter Street',
'Black River', 'St.Elizabeth'),
(1003, 'timronne@gmail.com', '8763314567', 'Tron Avenue',
'Montego Bay', 'St.James'),
(1004, 'ellioth@gmail.com', '8764447012', '22 Foster Road',
'Santa Cruz', 'St.Elizabeth'),
(1005, 'qbeelah@gmail.com', '8762105578', '77 Albert Road',
'Junction', 'St.Elizabeth')

INSERT into Owner (email,phone,street) VALUES ('jenmanson@gmail.com',8769980101,"Hope Way")


CREATE TABLE tenant(

TRN INTEGER PRIMARY KEY,
fName VARCHAR(20),
mNmae VARCHAR(20),
lName VARCHAR(20),
referee VARCHAR(30),
employer VARCHAR(50)
);

INSERT into tenant (fName,mNmae,lName)VALUES ('Johnn','Raviolo',"Tastee")

CREATE TABLE tenant_phone (
phone VARCHAR(10) PRIMARY KEY,
tax_rn INTEGER,
CONSTRAINT fk_tenant_phone FOREIGN KEY (tax_rn) REFERENCES tenant(TRN)

);

CREATE TABLE Property (
code INTEGER PRIMARY KEY,
dscription VARCHAR(90),
street VARCHAR(50),
town VARCHAR(50),
parish VARCHAR(50),
rPrice MONEY
);

INSERT into Owner (rPrice,town,street) VALUES (16000.00,'Liguanea','Hope Pastures')


CREATE TABLE Residential (
bdrm INTEGER,
bthrm INTEGER,
feature VARCHAR(60),
property_code INTEGER,

CONSTRAINT fk_residential FOREIGN KEY (property_code) REFERENCES property(code)

);

CREATE TABLE persona (

ID INT IDENTITY (9000,1) PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20)
);

Insert into persona (first_name,last_name)
Select fName, lName from tenant


CREATE TABLE Commercial (
sq_ft NUMERIC,
comm_property_code INTEGER,

CONSTRAINT fk_commercial FOREIGN KEY (comm_property_code) REFERENCES property(code)

);

CREATE TABLE Employee (
empID INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(25),
gender VARCHAR(3),
commision MONEY,
position VARCHAR(20),
email VARCHAR(50),
SupID INTEGER,

CONSTRAINT fk_EMPLOYEE FOREIGN KEY (SupID) REFERENCES Employee(empID)

);

CREATE TABLE Dependent (
name VARCHAR(50) PRIMARY KEY,
dob DATE,
employee_ID INTEGER,

CONSTRAINT fk_dependent FOREIGN KEY (employee_ID) REFERENCES Employee(empID)

);

CREATE TABLE Owns (
ownersID INTEGER,
prop_code INTEGER,
date_bought DATE PRIMARY KEY,
date_sold DATE,

CONSTRAINT fk_owns FOREIGN KEY (ownersID) REFERENCES Owner(ownerID),
CONSTRAINT fk_owns_rship FOREIGN KEY (prop_code) REFERENCES Property(code)

);

CREATE TABLE Rental (
TRN INTEGER,
code INTEGER,
rental_date DATE,
balance MONEY,
amt_collected MONEY,
empID INTEGER,

CONSTRAINT fk_rental FOREIGN KEY (TRN) REFERENCES tenant(TRN),
CONSTRAINT fk_rental2 FOREIGN KEY (code) REFERENCES Property(code),
CONSTRAINT fk_rental3 FOREIGN KEY (empID) REFERENCES Employee(empID)
);

ALTER TABLE Property
DROP COLUMN dscription;

ALTER TABLE Owner
ADD fullname VARCHAR(50);

ALTER TABLE tenant
ADD gender CHAR(1);


CREATE TABLE dummy_employee (
empID INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(25),
gender VARCHAR(3),
commision MONEY,
parish VARCHAR(50),
position VARCHAR(20),
email VARCHAR(50),
SupID INTEGER,

CONSTRAINT fk_dum_EMPLOYEE FOREIGN KEY (SupID) REFERENCES Employee(empID)

);

UPDATE dummy_employee SET parish = 'Kingston'

UPDATE dummy_employee SET lname = 'Wise' where lname = 'Katt'

DELETE FROM dummy_employee WHERE empID = 2550

--lab manual 3

USE Rental_1402620;

--SELECT * FROM Commercial;
--SELECT * FROM Dependent;
--SELECT * FROM Employee;
--SELECT * FROM Owner;
--SELECT * FROM Owns;
--SELECT * FROM Property;
--SELECT * FROM Rental;
--SELECT * FROM Residential;
--SELECT * FROM tenant;
--SELECT * FROM tenant_phone;
SELECT  email, phone FROM Owner;

SELECT * FROM Employee where commision > 15

SELECT * FROM tenant where gender = 'm'

SELECT * FROM tenant where referee = NULL OR employer = NULL

SELECT * FROM Owner where parish = 'St.Catherine' OR (parish='St.Andrew') OR (parish='Kingston')

SELECT * FROM Property where rPrice > 10000 AND parish = 'Kingston' OR (parish = 'St.Andrew')

SELECT fullname,phone FROM owner

SELECT * FROM Employee WHERE NOT (lname = 'Allen') AND (position = NULL)

SELECT * FROM Dependent ORDER BY dob DESC

SELECT fname,lname,position,gender FROM Employee ORDER BY lname DESC, fname DESC, email DESC

SELECT * FROM Property ORDER BY rPrice ASC, parish ASC

SELECT * FROM Employee where commision > 15 ORDER BY gender ASC, lname ASC, fname ASC

SELECT * FROM Property where parish LIKE 'St.%'
--ALTER TABLE Employee
--ADD parish VARCHAR(50);
--SELECT * FROM Employee WHERE NOT (parish LIKE 'St.%')

SELECT * FROM Owner WHERE email LIKE '%yahoo' OR (email LIKE '%gmail') AND NOT (parish = 'Kingston') OR NOT (parish = 'St.Andrew')

SELECT fName,mNmae,gender,employer FROM tenant WHERE gender ='f' AND(fName LIKE 'a%') ORDER BY lName
SELECT fName,mNmae,gender,employer FROM tenant WHERE gender = 'm' AND (fName LIKE '%aun') order by lName