Lab 07: SQL Data Definition

Copy and paste the content of the text file Lab07_Data.txt into your SQL Editor, then execute it. This code will create the tables Departments, Employees, Locations, and Grades, then insert sample data into these tables.

Take a moment to check the contents of the four tables and determine how they are related.

1- Execute the following SQL statement that creates the view Employees_VW1.

CREATE VIEW Employees_VW1

AS SELECT * FROM Employees;

2- Check the content of the view Employees_VW1.

SELECT * FROM Employees_VW1;

3- Create the view Employees_VW2 that lists all attributes of employees working in the department with deptID = 100.

4- Use Employees_VW2 to create the view Employees_VW3 that lists the employee ID, the first name, the last name, the salary, and the manager ID of employees working in the department ID = 100.

5- Using the view Employees_VW3, give a 10% salary increase to employees working in the department ID = 100.

6- Check and make sure the new salary values are updated in the base table Employees.

7- Create the view Employees_VW4 that lists the employee ID, the first name, the last name, the hire date, and the department name of all employees.

8- Using the view Employees_VW4, try to rename the IT department using the following

SQL statement.

UPDATE Employees_VW4

SET deptName = 'Information Technology'

WHERE deptName = 'IT';

9- Explain why the department name is not updated through the view Employees_VW4.

10- Create the view Employees_VW5 that lists the employee ID, the first name, the last name, the salary, and the department name of employees with a salary higher than the average salary of all employees.

11- Create the view Employees_VW6 that lists, for each department ID, the number of employees and the total of their salaries.

Note: This info can be retrieved from the Employees table only. No need to join multiple tables.

12- Using the view Employees_VW6, try to update the value of the department ID 80 with the value 150 using the following SQL statement.

UPDATE Employees_VW6

SET deptID = 150

WHERE deptID = 80;

13- Explain why the department ID is not updated through the view Employees_VW6.

14- Create the view Employees_VW7 that lists the first name, the last name, the department name, the city, the state and the country for all employees.

15- Create the view Employees_VW8 that lists the first name, the last name, and the last name of the manger for all employees.

16- Create the view Employees_VW9 that lists the first name, the last name, the salary, and the grade level for all employees.

Note: The grade levels exist in the Grades table.

CREATE TABLE Departments ( deptID NUMBER(4), deptNAME VARCHAR2(50), managerID NUMBER(4), locationID NUMBER(4), PRIMARY KEY (deptID)); CREATE TABLE Locations ( locationID NUMBER(4), address VARCHAR2(80), zipCode VARCHAR2(20), city VARCHAR2(30), state VARCHAR2(30), country VARCHAR2(20), PRIMARY KEY (locationID)); CREATE TABLE Employees ( employeeID NUMBER(4), fName VARCHAR2(20), lName VARCHAR2(20), email VARCHAR2(20), phone VARCHAR2(20), hireDate VARCHAR2(20), jobID VARCHAR2(20), salary NUMBER, commissionPCT NUMBER, managerID NUMBER(4), deptID NUMBER(4), PRIMARY KEY (employeeID)); CREATE TABLE Grades ( gradeLevel VARCHAR2(1), lowestSalary NUMBER, highestSalary NUMBER, PRIMARY KEY (gradeLevel)); INSERT INTO Departments VALUES(10,'Administration',200,1700); INSERT INTO Departments VALUES (20,'Marketing',201,1800); INSERT INTO Departments VALUES (30,'Purchasing',114,1700); INSERT INTO Departments VALUES (40,'Human Resources',203,2600); INSERT INTO Departments VALUES (50,'Shipping',121,1500); INSERT INTO Departments VALUES (60,'IT',103,1400); INSERT INTO Departments VALUES (70,'Public Relations',204,2700); INSERT INTO Departments VALUES (80,'Sales',145,2500); INSERT INTO Departments VALUES (90,'Executive',100,1700); INSERT INTO Departments VALUES (100,'Finance',108,1700); INSERT INTO Departments VALUES (110,'Accounting',199,1700); INSERT INTO Locations VALUES('1400','2014 Jabberwocky Rd','26192','Southlake','Texas','United States'); INSERT INTO Locations VALUES('1500','2011 Interiors Blvd','99236','South San Francisco','California','United States'); INSERT INTO Locations VALUES('1600','2007 Zagora St','50090','South Brunswick','New Jersey','United States'); INSERT INTO Locations VALUES('1700','2004 Charade Rd','98199','Seattle','Washington','United States'); INSERT INTO Locations VALUES('1800','147 Spadina Ave','M5V 2L7','Toronto','Ontario','Canada'); INSERT INTO Locations VALUES('1900','6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','Canada'); INSERT INTO Locations VALUES('2500','Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','United Kingdom'); INSERT INTO Locations VALUES('2600','9702 Chester Road','9629850293','Stretford','Manchester','United Kingdom'); INSERT INTO Locations VALUES('2700','Schwanthalerstr. 7031','80925','Munich','Bavaria','Germany'); INSERT INTO Employees VALUES('100','Steven','King','SKING','515.123.4567','2003-06-17','AD_PRES','24000.00','0.00','0','90'); INSERT INTO Employees VALUES('101','Neena','Kochhar','NKOCHHAR','515.123.4568','2005-09-21','AD_VP','17000.00','0.00','100','90'); INSERT INTO Employees VALUES('102','Lex','DeHaan','LDEHAAN','515.123.4569','2001-01-13','AD_VP','17000.00','0.00','100','90'); INSERT INTO Employees VALUES('103','Alexander','Hunold','AHUNOLD','590.423.4567','2006-01-03','IT_PROG','9000.00','0.00','102','60'); INSERT INTO Employees VALUES('104','Bruce','Ernst','BERNST','590.423.4568','2007-05-21','IT_PROG','6000.00','0.00','103','60'); INSERT INTO Employees VALUES('105','David','Austin','DAUSTIN','590.423.4569','2005-06-25','IT_PROG','4800.00','0.00','103','60'); INSERT INTO Employees VALUES('106','Valli','Pataballa','VPATABAL','590.423.4560','2006-02-05','IT_PROG','4800.00','0.00','103','60'); INSERT INTO Employees VALUES('107','Diana','Lorentz','DLORENTZ','590.423.5567','2007-02-07','IT_PROG','4200.00','0.00','103','60'); INSERT INTO Employees VALUES('108','Nancy','Greenberg','NGREENBE','515.124.4569','2002-08-17','FI_MGR','12000.00','0.00','101','100'); INSERT INTO Employees VALUES('109','Daniel','Faviet','DFAVIET','515.124.4169','2002-08-16','FI_ACCOUNT','9000.00','0.00','108','100'); INSERT INTO Employees VALUES('110','John','Chen','JCHEN','515.124.4269','2005-09-28','FI_ACCOUNT','8200.00','0.00','108','100'); INSERT INTO Employees VALUES('111','Ismael','Sciarra','ISCIARRA','515.124.4369','2005-09-30','FI_ACCOUNT','7700.00','0.00','108','100'); INSERT INTO Employees VALUES('112','JoseManuel','Urman','JMURMAN','515.124.4469','2006-03-07','FI_ACCOUNT','7800.00','0.00','108','100'); INSERT INTO Employees VALUES('113','Luis','Popp','LPOPP','515.124.4567','2007-12-07','FI_ACCOUNT','6900.00','0.00','108','100'); INSERT INTO Employees VALUES('114','Den','Raphaely','DRAPHEAL','515.127.4561','2002-12-07','PU_MAN','11000.00','0.00','100','30'); INSERT INTO Employees VALUES('115','Alexander','Khoo','AKHOO','515.127.4562','2003-05-18','PU_CLERK','3100.00','0.00','114','30'); INSERT INTO Employees VALUES('116','Shelli','Baida','SBAIDA','515.127.4563','2005-12-24','PU_CLERK','2900.00','0.00','114','30'); INSERT INTO Employees VALUES('117','Sigal','Tobias','STOBIAS','515.127.4564','2005-07-24','PU_CLERK','2800.00','0.00','114','30'); INSERT INTO Employees VALUES('118','Guy','Himuro','GHIMURO','515.127.4565','2006-11-15','PU_CLERK','2600.00','0.00','114','30'); INSERT INTO Employees VALUES('119','Karen','Colmenares','KCOLMENA','515.127.4566','2007-08-10','PU_CLERK','2500.00','0.00','114','30'); INSERT INTO Employees VALUES('120','Matthew','Weiss','MWEISS','650.123.1234','2004-07-18','ST_MAN','8000.00','0.00','100','50'); INSERT INTO Employees VALUES('121','Adam','Fripp','AFRIPP','650.123.2234','2005-04-10','ST_MAN','8200.00','0.00','100','50'); INSERT INTO Employees VALUES('122','Payam','Kaufling','PKAUFLIN','650.123.3234','2003-05-01','ST_MAN','7900.00','0.00','100','50'); INSERT INTO Employees VALUES('123','Shanta','Vollman','SVOLLMAN','650.123.4234','2005-10-10','ST_MAN','6500.00','0.00','100','50'); INSERT INTO Employees VALUES('124','Kevin','Mourgos','KMOURGOS','650.123.5234','2007-11-16','ST_MAN','5800.00','0.00','100','50'); INSERT INTO Employees VALUES('125','Julia','Nayer','JNAYER','650.124.1214','2005-07-16','ST_CLERK','3200.00','0.00','120','50'); INSERT INTO Employees VALUES('126','Irene','Mikkilineni','IMIKKILI','650.124.1224','2006-09-28','ST_CLERK','2700.00','0.00','120','50'); INSERT INTO Employees VALUES('127','James','Landry','JLANDRY','650.124.1334','2007-01-14','ST_CLERK','2400.00','0.00','120','50'); INSERT INTO Employees VALUES('128','Steven','Markle','SMARKLE','650.124.1434','2008-03-08','ST_CLERK','2200.00','0.00','120','50'); INSERT INTO Employees VALUES('129','Laura','Bissot','LBISSOT','650.124.5234','2005-08-20','ST_CLERK','3300.00','0.00','121','50'); INSERT INTO Employees VALUES('130','Mozhe','Atkinson','MATKINSO','650.124.6234','2005-10-30','ST_CLERK','2800.00','0.00','121','50'); INSERT INTO Employees VALUES('131','James','Marlow','JAMRLOW','650.124.7234','2005-02-16','ST_CLERK','2500.00','0.00','121','50'); INSERT INTO Employees VALUES('132','TJ','Olson','TJOLSON','650.124.8234','2007-04-10','ST_CLERK','2100.00','0.00','121','50'); INSERT INTO Employees VALUES('133','Jason','Mallin','JMALLIN','650.127.1934','2004-06-14','ST_CLERK','3300.00','0.00','122','50'); INSERT INTO Employees VALUES('134','Michael','Rogers','MROGERS','650.127.1834','2006-08-26','ST_CLERK','2900.00','0.00','122','50'); INSERT INTO Employees VALUES('135','Ki','Gee','KGEE','650.127.1734','2007-12-12','ST_CLERK','2400.00','0.00','122','50'); INSERT INTO Employees VALUES('136','Hazel','Philtanker','HPHILTAN','650.127.1634','2008-02-06','ST_CLERK','2200.00','0.00','122','50'); INSERT INTO Employees VALUES('137','Renske','Ladwig','RLADWIG','650.121.1234','2003-07-14','ST_CLERK','3600.00','0.00','123','50'); INSERT INTO Employees VALUES('138','Stephen','Stiles','SSTILES','650.121.2034','2005-10-26','ST_CLERK','3200.00','0.00','123','50'); INSERT INTO Employees VALUES('139','John','Seo','JSEO','650.121.2019','2006-02-12','ST_CLERK','2700.00','0.00','123','50'); INSERT INTO Employees VALUES('140','Joshua','Patel','JPATEL','650.121.1834','2006-04-06','ST_CLERK','2500.00','0.00','123','50'); INSERT INTO Employees VALUES('141','Trenna','Rajs','TRAJS','650.121.8009','2003-10-17','ST_CLERK','3500.00','0.00','124','50'); INSERT INTO Employees VALUES('142','Curtis','Davies','CDAVIES','650.121.2994','2005-01-29','ST_CLERK','3100.00','0.00','124','50'); INSERT INTO Employees VALUES('143','Randall','Matos','RMATOS','650.121.2874','2006-03-15','ST_CLERK','2600.00','0.00','124','50'); INSERT INTO Employees VALUES('144','Peter','Vargas','PVARGAS','650.121.2004','2006-07-09','ST_CLERK','2500.00','0.00','124','50'); INSERT INTO Employees VALUES('145','John','Russell','JRUSSEL','011.44.1344.429268','2004-10-01','SA_MAN','14000.00','0.40','100','80'); INSERT INTO Employees VALUES('146','Karen','Partners','KPARTNER','011.44.1344.467268','2005-01-05','SA_MAN','13500.00','0.30','100','80'); INSERT INTO Employees VALUES('147','Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','2005-03-10','SA_MAN','12000.00','0.30','100','80'); INSERT INTO Employees VALUES('148','Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','2007-10-15','SA_MAN','11000.00','0.30','100','80'); INSERT INTO Employees VALUES('149','Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','2008-01-29','SA_MAN','10500.00','0.20','100','80'); INSERT INTO Employees VALUES('150','Peter','Tucker','PTUCKER','011.44.1344.129268','2005-01-30','SA_REP','10000.00','0.30','145','80'); INSERT INTO Employees VALUES('151','David','Bernstein','DBERNSTE','011.44.1344.345268','2005-03-24','SA_REP','9500.00','0.25','145','80'); INSERT INTO Employees VALUES('152','Peter','Hall','PHALL','011.44.1344.478968','2005-08-20','SA_REP','9000.00','0.25','145','80'); INSERT INTO Employees VALUES('153','Christopher','Olsen','COLSEN','011.44.1344.498718','2006-03-30','SA_REP','8000.00','0.20','145','80'); INSERT INTO Employees VALUES('154','Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','2006-12-09','SA_REP','7500.00','0.20','145','80'); INSERT INTO Employees VALUES('155','Oliver','Tuvault','OTUVAULT','011.44.1344.486508','2007-11-23','SA_REP','7000.00','0.15','145','80'); INSERT INTO Employees VALUES('156','Janette','King','JKING','011.44.1345.429268','2004-01-30','SA_REP','10000.00','0.35','146','80'); INSERT INTO Employees VALUES('157','Patrick','Sully','PSULLY','011.44.1345.929268','2004-03-04','SA_REP','9500.00','0.35','146','80'); INSERT INTO Employees VALUES('158','Allan','McEwen','AMCEWEN','011.44.1345.829268','2004-08-01','SA_REP','9000.00','0.35','146','80'); INSERT INTO Employees VALUES('159','Lindsey','Smith','LSMITH','011.44.1345.729268','2005-03-10','SA_REP','8000.00','0.30','146','80'); INSERT INTO Employees VALUES('160','Louise','Doran','LDORAN','011.44.1345.629268','2005-12-15','SA_REP','7500.00','0.30','146','80'); INSERT INTO Employees VALUES('161','Sarath','Sewall','SSEWALL','011.44.1345.529268','2006-11-03','SA_REP','7000.00','0.25','146','80'); INSERT INTO Employees VALUES('162','Clara','Vishney','CVISHNEY','011.44.1346.129268','2005-11-11','SA_REP','10500.00','0.25','147','80'); INSERT INTO Employees VALUES('163','Danielle','Greene','DGREENE','011.44.1346.229268','2007-03-19','SA_REP','9500.00','0.15','147','80'); INSERT INTO Employees VALUES('164','Mattea','Marvins','MMARVINS','011.44.1346.329268','2008-01-24','SA_REP','7200.00','0.10','147','80'); INSERT INTO Employees VALUES('165','David','Lee','DLEE','011.44.1346.529268','2008-02-23','SA_REP','6800.00','0.10','147','80'); INSERT INTO Employees VALUES('166','Sundar','Ande','SANDE','011.44.1346.629268','2008-03-24','SA_REP','6400.00','0.10','147','80'); INSERT INTO Employees VALUES('167','Amit','Banda','ABANDA','011.44.1346.729268','2008-04-21','SA_REP','6200.00','0.10','147','80'); INSERT INTO Employees VALUES('168','Lisa','Ozer','LOZER','011.44.1343.929268','2005-03-11','SA_REP','11500.00','0.25','148','80'); INSERT INTO Employees VALUES('169','Harrison','Bloom','HBLOOM','011.44.1343.829268','2006-03-23','SA_REP','10000.00','0.20','148','80'); INSERT INTO Employees VALUES('170','Tayler','Fox','TFOX','011.44.1343.729268','2006-01-24','SA_REP','9600.00','0.20','148','80'); INSERT INTO Employees VALUES('171','William','Smith','WSMITH','011.44.1343.629268','2007-02-23','SA_REP','7400.00','0.15','148','80'); INSERT INTO Employees VALUES('172','Elizabeth','Bates','EBATES','011.44.1343.529268','2007-03-24','SA_REP','7300.00','0.15','148','80'); INSERT INTO Employees VALUES('173','Sundita','Kumar','SKUMAR','011.44.1343.329268','2008-04-21','SA_REP','6100.00','0.10','148','80'); INSERT INTO Employees VALUES('174','Ellen','Abel','EABEL','011.44.1644.429267','2004-05-11','SA_REP','11000.00','0.30','149','80'); INSERT INTO Employees VALUES('175','Alyssa','Hutton','AHUTTON','011.44.1644.429266','2005-03-19','SA_REP','8800.00','0.25','149','80'); INSERT INTO Employees VALUES('176','Jonathon','Taylor','JTAYLOR','011.44.1644.429265','2006-03-24','SA_REP','8600.00','0.20','149','80'); INSERT INTO Employees VALUES('177','Jack','Livingston','JLIVINGS','011.44.1644.429264','2006-04-23','SA_REP','8400.00','0.20','149','80'); INSERT INTO Employees VALUES('178','Kimberely','Grant','KGRANT','011.44.1644.429263','2007-05-24','SA_REP','7000.00','0.15','149','0'); INSERT INTO Employees VALUES('179','Charles','Johnson','CJOHNSON','011.44.1644.429262','2008-01-04','SA_REP','6200.00','0.10','149','80'); INSERT INTO Employees VALUES('180','Winston','Taylor','WTAYLOR','650.507.9876','2006-01-24','SH_CLERK','3200.00','0.00','120','50'); INSERT INTO Employees VALUES('181','Jean','Fleaur','JFLEAUR','650.507.9877','2006-02-23','SH_CLERK','3100.00','0.00','120','50'); INSERT INTO Employees VALUES('182','Martha','Sullivan','MSULLIVA','650.507.9878','2007-06-21','SH_CLERK','2500.00','0.00','120','50'); INSERT INTO Employees VALUES('183','Girard','Geoni','GGEONI','650.507.9879','2008-02-03','SH_CLERK','2800.00','0.00','120','50'); INSERT INTO Employees VALUES('184','Nandita','Sarchand','NSARCHAN','650.509.1876','2004-01-27','SH_CLERK','4200.00','0.00','121','50'); INSERT INTO Employees VALUES('185','Alexis','Bull','ABULL','650.509.2876','2005-02-20','SH_CLERK','4100.00','0.00','121','50'); INSERT INTO Employees VALUES('186','Julia','Dellinger','JDELLING','650.509.3876','2006-06-24','SH_CLERK','3400.00','0.00','121','50'); INSERT INTO Employees VALUES('187','Anthony','Cabrio','ACABRIO','650.509.4876','2007-02-07','SH_CLERK','3000.00','0.00','121','50'); INSERT INTO Employees VALUES('188','Kelly','Chung','KCHUNG','650.505.1876','2005-06-14','SH_CLERK','3800.00','0.00','122','50'); INSERT INTO Employees VALUES('189','Jennifer','Dilly','JDILLY','650.505.2876','2005-08-13','SH_CLERK','3600.00','0.00','122','50'); INSERT INTO Employees VALUES('190','Timothy','Gates','TGATES','650.505.3876','2006-07-11','SH_CLERK','2900.00','0.00','122','50'); INSERT INTO Employees VALUES('191','Randall','Perkins','RPERKINS','650.505.4876','2007-12-19','SH_CLERK','2500.00','0.00','122','50'); INSERT INTO Employees VALUES('192','Sarah','Bell','SBELL','650.501.1876','2004-02-04','SH_CLERK','4000.00','0.00','123','50'); INSERT INTO Employees VALUES('193','Britney','Everett','BEVERETT','650.501.2876','2005-03-03','SH_CLERK','3900.00','0.00','123','50'); INSERT INTO Employees VALUES('194','Samuel','McCain','SMCCAIN','650.501.3876','2006-07-01','SH_CLERK','3200.00','0.00','123','50'); INSERT INTO Employees VALUES('195','Vance','Jones','VJONES','650.501.4876','2007-03-17','SH_CLERK','2800.00','0.00','123','50'); INSERT INTO Employees VALUES('196','Alana','Walsh','AWALSH','650.507.9811','2006-04-24','SH_CLERK','3100.00','0.00','124','50'); INSERT INTO Employees VALUES('197','Kevin','Feeney','KFEENEY','650.507.9822','2006-05-23','SH_CLERK','3000.00','0.00','124','50'); INSERT INTO Employees VALUES('198','Donald','OConnell','DOCONNEL','650.507.9833','2007-06-21','AC_ACCOUNT','2600.00','0.00','199','110'); INSERT INTO Employees VALUES('199','Shelley','Higgins','SHIGGINS','515.123.8080','2002-06-07','AC_MGR','12008.00','0.00','101','110'); INSERT INTO Grades VALUES('A','1000','2999'); INSERT INTO Grades VALUES('B','3000','5999'); INSERT INTO Grades VALUES('C','6000','9999'); INSERT INTO Grades VALUES('D','10000','14999'); INSERT INTO Grades VALUES('E','15000','24999'); INSERT INTO Grades VALUES('F','25000','40000');