Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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