Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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
- --lab manual 4
- --exercise 1
- -- 1
- Select(rPrice * 0.1) as Maintenance from Property
- --2
- Select (sq_ft * 4000) as rental_price from Commercial where sq_ft > 100
- --3
- Select ((40 * 1500) + commision) as salary from Employee
- --4
- --exercise 2
- --a
- --number of residential
- SELECT COUNT(property_code) from Residential
- SELECT COUNT(comm_property_code) from Commercial
- SELECT COUNT(code) from Property
- --b
- SELECT AVG(rPrice) as avg_rentalPrice from Property
- --c
- SELECT MAX(rPrice) from Property
- --d
- SELECT MIN(rPrice) from Property
- --e
- SELECT SUM(rPrice) from Property
- --exercise 3
- --1
- SELECT COUNT(parish) from Property GROUP BY parish
- --2
- SELECT COUNT(code) from Property GROUP BY parish
- --3
- SELECT SUM(rPrice) from Property GROUP BY parish
- --SELECT GETDATE()
- --1
- insert into Employee (empID,fname,lname,gender,date_hired,dob,parish,commision,position,email) values
- (2001,'jay','carter','m','2004-08-12','1980-12-12','St.Elizabeth',0.2,'Clerk','j.carter@gmail.com');
- insert into Employee (empID,fname,lname,gender,date_hired,dob,parish,commision,position,email) values
- (2002,'Trish','Tardair','f','2009-07-08','1990-09-12','St.Andrew',0.2,'Clerk','t.tardair@gmail.com'),
- (2003,'Cameron','Khalid','m','2012-01-09','1985-01-15','Manchester',0.2,'Clerk','c.kh@gmail.com'),
- (2004,'Tammi','Bell','f','2003-11-22','1991-10-05','St.Catherine',0.2,'Clerk','t.bell@gmail.com'),
- (2005,'Niklaus','Cordair','m','2017-10-05','1992-01-02','St.James',0.2,'Clerk','n.cordair@gmail.com');
- insert into Employee (empID,fname,lname,gender,date_hired,dob,parish,commision,position,email) values
- (2006,'walter','white','m','2017-09-12','1998-02-04','St.Andrew',0.2,'Clerk','w.white@gmail.com');
- --2
- SELECT * from Employee where DateDiff(year,dob,GETDATE()) > 21
- --3
- insert into Employee (empID,fname,lname,gender,date_hired,dob,parish,commision,position,email) values
- (2007,'Tisha','Aneque','f','2017-09-11','2001-02-04','St.Andrew',0.2,'Intern','t.neque@gmail.com');
- SELECT * from Employee where DateDiff(year,dob,GETDATE()) > 12 AND(DateDiff(year,dob,GETDATE()) < 18)
- --4
- insert into Employee (empID,fname,lname,gender,date_hired,dob,parish,commision,position,email) values
- (2008,'Trina','Migo','f','2017-08-13','2001-02-28','Clarendon',0.2,'Intern','third_migo@gmail.com');
- SELECT(DateDiff(year,dob,GETDATE())) as age ,fname,lname from EMPLOYEE where Month(dob) = 2 AND (DAY(dob) = 28)
- --5
- Select * from Employee where DateDiff(year,date_hired,GETDATE()) > 10
- insert into Employee (empID,fname,lname,gender,date_hired,dob,parish,commision,position,email) values
- (2009,'Kelly','Angelo','f','2007-05-28','2000-10-06','Manchester',0.2,'Intern','k.ange@gmail.com');
- select fname,lname from Employee where DateDiff(year,DateADD(month,5,date_hired),GETDATE()) > 10
- SELECT * from Employee where DateDiff(year,dob,'2017-09-30') >= 18 AND (DateDiff(year,date_hired,GETDATE()) > 10)
- SELECT ownersID, prop_code, DateDiff(month,date_sold,GETDATE()) from Owns
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement