Lab – 6

Questions:
1. Create the EMPLOYEE table based on the following table instance chart.
CREATE TABLE EMPLOYEE ( ID NUMBER(4), Last_name VARCHAR2(25), First_name
VARCHAR2(25), USERID VARCHAR2(8), SALARY NUMBER(9,2));
2. Insert the following data
INSERT INTO EMPLOYEE (ID , Last_name, First_name, USERID, SALARY ) VALUES ( 1,
'Patel',

3. Modify the EMP table to allow for longer employee last names. Confirm your
modification.
4. Confirm that EMPLOYEE tables are stored in the data dictionary.
5. Change the last name of employee 3 to Drexler.
6. Change the salary to 1000 for all employees with a salary less than 900.
7. Delete Betty Dancs from the MY_EMPLOYEE table.
8. Rename the EMPLOYEES table as EMP80
9. Drop the FIRST_NAME column from the EMPLOYEE table. Confirm your modification by
checking the description of the table.
10. Create the EMPLOYEE2 table based on the structure of the EMPLOYEE table. Include
only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY.

Answers:
1. CREATE TABLE EMPLOYEE (
ID NUMBER(4), Last_name VARCHAR2(25),First_name VARCHAR2(25),USERID
VARCHAR2(8),SALARY NUMBER(9,2));
2. INSERT INTO EMPLOYEE (ID, Last_name, First_name, USERID, SALARY) VALUES (1, 'Patel',
'Ralph', 'rpatel', 895);
INSERT INTO EMPLOYEE (ID, Last_name, First_name, USERID, SALARY) VALUES (2, 'Dancs',
'Betty', 'bdancs', 860);
INSERT INTO EMPLOYEE (ID, Last_name, First_name, USERID, SALARY) VALUES (3, 'Biri', 'Ben',
'bbiri', 1100);
INSERT INTO EMPLOYEE (ID, Last_name, First_name, USERID, SALARY) VALUES (4, 'Newman',
'Chad', 'cnewman', 750);
3. ALTER TABLE EMPLOYEE MODIFY (Last_name VARCHAR2(50));
4. SELECT table_name FROM user_tables;
5. UPDATE EMPLOYEE SET last_name = 'Drexler' WHERE ID = 3;
6. UPDATE EMPLOYEE SET salary = 1000 WHERE SALARY <= 900;
7. DELETE FROM EMPLOYEE WHERE first_name = 'Betty';
8. RENAME EMPLOYEE TO EMP80;
9. ALTER TABLE EMP80 DROP COLUMN First_name;
DESC EMP80;
11.CREATE TABLE EMPLOYEE2 AS SELECT ID, First_name, last_name, Salary FROM EMPLOYEE

Lab – 7
Questions:
1. Add a table-level PRIMARY KEY constraint to the EMP table on the ID column. The
constraint should be named at creation. Name the constraint my_emp_id_pk. Hint: The
constraint is enabled as soon as the ALTER TABLE command executes successfully.
2. Create a PRIMARY KEY constraint to the DEPT table using the ID column. The
constraint should be named at creation. Name the constraint my_deptid_pk. Hint: The
constraint is enabled as soon as the ALTER TABLE command executes successfully.
3. Add a column DEPT_ID to the EMP table. Add a foreign key reference on the EMP table
that ensures that the employee is not assigned to a nonexistent department. Name the
constraint my_emp_dept_id_fk.
4. Confirm that the constraints were added by querying the USER_CONSTRAINTS view.
Note the types and names of the constraints. Save your statement text in a file called
lab10_4.sql
5. Display the object names and types from the USER_OBJECTS data dictionary view for
the EMP and DEPT tables. Notice that the new tables and a new index were created.
If you have time, complete the following exercise:
6. Modify the EMP table. Add a COMMISSION column of NUMBER data type, precision
2, scale 2. Add a constraint to the commission column that ensures that a commission
value is greater than zero.
Answers:
1. ALTER TABLE emp ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
2. ALTER TABLE dept ADD CONSTRAINT my_deptid_pk PRIMARY KEY(id);
3. ALTER TABLE emp ADD (dept_id NUMBER);
ALTER TABLE emp ADD CONSTRAINT my_emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES
dept(id);
4. SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name IN
('EMP', 'DEPT');
5. SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'EMP%' OR
object_name LIKE 'DEPT%';
6. ALTER TABLE emp ADD commission_pct NUMBER(2,2) CONSTRAINT comm_chk
CHECK(commission_pct >= 0);

Lab – 8
Questions:
1. Write a query to display the last name, department number, and department name for all
employees.
2. Create a unique listing of all jobs that are in department 30. Include the location of
department 90 in the output.
3. Write a query to display the employee last name, department name, location ID, and city of
all employees who earn a commission.
4. Display the employee last name and department name for all employees who have an a
(lowercase) in their last names. Place your SQL statement in a text file named lab4_4.sql.
5. Write a query to display the last name, job, department number, and department name for
all employees who work in Toronto.
6. Display the employee last name and employee number along with their
manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
Place your SQL statement in a text file named lab4_6.sql.
If you want an extra challenge, solve the following problem:
7. Modify lab4_6.sql to display all employees including King, who has no manager. Order the
results by the employee number.
Place your SQL statement in a text file named lab4_7.sql. Run the query in lab4_7.sql.
Answers:
1. SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
2. SELECT DISTINCT job_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND employees.department_id = 30;
3. SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.commission_pct IS NOT NULL;
4. SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND last_name LIKE '%a%';
5. SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
WHERE LOWER(l.city) = 'toronto';
6. SELECT w.last_name AS "Employee", w.employee_id AS "EMP#",
m.last_name AS "Manager", m.employee_id AS "Mgr#"
FROM employees w
JOIN employees m ON (w.manager_id = m.employee_id);
7. SELECT w.last_name AS "Employee", w.employee_id AS "EMP#", m.last_name AS "Manager",
m.employee_id AS "Mgr#" FROM employees w LEFT OUTER JOIN employees m ON
(w.manager_id = m.employee_id);
 Lab – 9
Questions:
1. Write a query to display the last name and hire date of any employee in the same
department as Zlotkey. Exclude Zlotkey.
2. Create a query to display the employee numbers and last names of all employees who earn
more than the average salary. Sort the results in ascending order of salary.
3. Write a query that displays the employee numbers and last names of all employees who
work in a department with any employee whose last name contains a u. Place your SQL
statement in a text file named lab6_3.sql. Run your query.
4. Display the last name, department number, and job ID of all employees whose department
location ID is 1700.
5. Display the last name and salary of every employee who reports to King.
6. Display the department number, last name, and job ID for every employee in the Executive
department.

Answers:
1. SELECT last_name, hire_date
FROM employees
WHERE department_id = (
SELECT department_id FROM employees WHERE last_name = 'Zlotkey'
) AND last_name <> 'Zlotkey';

2. SELECT employee_id, last_name

FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary;
3. SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
SELECT department_id FROM employees WHERE last_name LIKE '%u%' );
4. SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id = 1700 );
5. SELECT last_name, salary
FROM employees
WHERE manager_id = (
SELECT employee_id FROM employees WHERE last_name = 'King');
6. SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE department_name = 'Executive' );

Lab – 11
Questions:
1. Create a view called EMPLOYEES_VU based on the employee numbers, employee names, and
department numbers from the EMPLOYEES table. Change the heading for the employee name
to EMPLOYEE.
2. Display the contents of the EMPLOYEES_VU view.
3. Select the view name and text from the USER_VIEWS data dictionary view.
Note: Another view already exists. The EMP_DETAILS_VIEW was created as part of your
schema.
Note: To see more contents of a LONG column, use the command SET LONG n, where n is the
value of the number of characters of the LONG column that you want to see.
4. Using your EMPLOYEES_VU view, enter a query to display all employee names and
department numbers.
5. Create a view named DEPT50 that contains the employee numbers, employee last names,
and department numbers for all employees in department 50. Label the view columns EMPNO,
EMPLOYEE, and DEPTNO. Do not allow an employee to be reassigned to another department
through the view.
6. Display the structure and contents of the DEPT50 view.
7. Attempt to reassign Matos to department 80.
If you have time, complete the following exercise:
8. Create a view called SALARY_VU based on the employee last names, department names,
salaries, and salary grades for all employees. Use the EMPLOYEES, DEPARTMENTS, and
JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.

Answers:
1. CREATE VIEW EMPLOYEES_VU AS
SELECT `EMPLOYEE_ID`,`LAST_NAME` EMPLOYEE,`DEPARTMENT_ID`
FROM employees;

2. SELECT * FROM employees_vu;

3. SELECT `EMPLOYEE`,`DEPARTMENT_ID` FROM `employees_vu`;

4. CREATE VIEW DEPT50 (EMPNO,EMPLOYEE,DEPTNO)

AS SELECT `EMPLOYEE_ID`,`LAST_NAME`,`DEPARTMENT_ID`
FROM employees
WHERE `DEPARTMENT_ID`= 50
WITH CHECK OPTION;

5. DESCRIBE dept50;
SELECT * FROM dept50;

6. UPDATE DEPT50
SET DEPTNO = 80
WHERE EMPLOYEE = 'MATOS';

7. CREATE VIEW salary_vu(EMPLOYEE,DEPARTMENT,SALARY)

AS SELECT e.last_name, d.department_name, e.salary
FROM employees e JOIN departments d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;