Hands-On Assignments: Lesson 10 (Review Lesson 10 pdf above.. 1. )Create the DEP
ID: 3594677 • Letter: H
Question
Hands-On Assignments:
Lesson 10 (Review Lesson 10 pdf above..
1. )Create the DEPARTMENT tables based on the following:
Column Name ID Name
Data Type Number Varchar2
Length 7 25
2. Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need.
3. Create the EMPLOYEE table based on the following table chart:
Column Name ID LAST_NAME FIRST_NAME DEPT_ID
Data Type Number Varchar2 Varchar2 Number
Length 7 25 25 7
4. Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification.
5. Confirm that both the DEPARTMENT and EMPLOYEE tables are stored in the data dictionary. (Hint: USER_TABLES)
6. Create the EMPLOYEE2 table based on the structure of the EMP table. Include only the EMPO, ENAME, DEPTNO columns. Name the columns in your new table ID, LAST_NAME, and DEPT_ID, respectively.
7. Drop the EMPLOYEE table.
8. Rename the EMPLOYEE2 table to EMPLOYEE.
9. Add a comment to the DEPARTMENT and EMPLOYEE table definitions describing the tables. Confirm your additions in the data dictionary.
10. Drop the LAST_NAME column from the EMPLOYEE table. Confirm your modification by checking the description of the table.
Lesson 11
11. Add a table-level PRIMARY KEY constraint to the EMPLOYEE table using the ID column. The constraint should be named at creation. (Which means you'll need to drop the table, then recreate.)
12. Create a PRIMARY KEY constraint on the DEPARTMENT table using the ID column. The constraint should be named at creation. (Which means you'll need to drop the table, then recreate.)
13. Add a foreign key reference on the EMPLOYEE table that will ensure that the employee is not assigned to a nonexistent department.
14. Confirm that the constraints were added by querying USER_CONSTRAINTS. Note the types and names of the constraints and include them in your statement comments.
15. Display the object names and types from the USER_OBJECTS data dictionary view for EMPLOYEE and DEPARTMENT tables. You will want to format the columns for readability. Provide comments that include the new tables and indexes created.
16. Modify the EMPLOYEE table by adding a SALARY column of NUMBER data type, precision 7.
Explanation / Answer
1) Column Name ID NAME
Key Type
Nulls/Unique
Fk Table
Fk Column
Data Type Number Varchar2
Length 7 25
create Table dept
(id Number(7);
Name varchar(25) );
DESCRIBE dept
............................................................................................................................................................
2) Insert into Dept
select department_id, department_Name; from departments;
.......................................................................................................................................................................
3) column Name Last_Name First_Name DEPT_id
Key Type
Nulls/Unique
Fk Table
Fk Column
Data Type Number VARCHAR2 VARCHAR2 Number
Length 7 25 25 7
................................................................................................................................................................
4)
.....................................................................................................................................................................
5) Select table_name FROM user_table WHERE table_name IN (’DEPT’, ’EMP’);
.......................................................................................................................................................
6) CREATE TABLE employees2 AS SELECT employee_id id, first_name, last_name, salary,department_id dept_id FROM employees;
........................................................................................................................................................................................
7) DROP TABLE emp
.................................................................................................................................................................................
8) RENAME employees2 TO emp;
9) SELECT *FROM user_tab_comments WHERE table_name = ' DEPT' OR table_name ='DEPT';
10) ALTER TABLE emp DROP COLUMN LAST_NAME
DESCRIBE emp
.......................................................................................................................................................................................
11) ALTER TABLE employee 2 ADD CONSTRAINT employee_id_pk PRIMARY KEY(id);
.....................................................................................................................................................................
12) ALTER TABLE Department 2 ADD CONSTRAINT depoartment_id_pk PRIMARY KEY(id);
..........................................................................................................................................................................
.................................................................................................................................................................................
14) SELECT constraint_name constraint_type FROM User-constraints WHERE Table_Name IN ('EMPLOYEE ' ,'DEPARTMENT')
........................................................................................................................................................................................16) ALTER TABLE employee ADD(Salary NUMBER ( 7) );
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.