Hands-On Assignments: Lesson 10 Create the DEPARTMENT tables based on the follow

Question

Hands-On Assignments:

Lesson 10
Create the DEPARTMENT tables based on the following:
Column Name ID Name
Data Type Number Varchar2
Length 7 25
Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need.
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
Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification.
Confirm that both the DEPARTMENT and EMPLOYEE tables are stored in the data dictionary. (Hint: USER_TABLES)
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.
Drop the EMPLOYEE table.
Rename the EMPLOYEE2 table to EMPLOYEE.
Add a comment to the DEPARTMENT and EMPLOYEE table definitions describing the tables. Confirm your additions in the data dictionary.
Drop the LAST_NAME column from the EMPLOYEE table. Confirm your modification by checking the description of the table.
Create the EMPLOYEE2 table based on the structure of the EMP table. Include only the EMPNO, ENAME, DEPTNO columns. Name the columns in your new table ID, LAST_NAME, and DEPTNO, respectively. Mark the DEPT_ID column in the EMPLOYEE2 table as UNUSED. Confirm your modification by checking the description of the table.
Drop all the UNUSED columns from the EMPLOYEE2 table. Confirm your modifications by checking the description of the table.
Lesson 11
Add a table-level PRIMARY KEY constraint to the EMPLOYEE table using the ID column. The constraint should be named at creation.
Create a PRIMARY KEY constraint on the DEPARTMENT table using the ID column. The constraint should be named at creation.
Add a foreign key reference on the EMPLOYEE table that will ensure that the employee is not assigned to a nonexistent department.
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.
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.
Modify the EMPLOYEE table by adding a SALARY column of NUMBER data type, precision 7.

Explanation / Answer

Create the DEPARTMENT tables based on the following:
Column Name ID Name
Data Type Number Varchar2
Length 7 25

CREATE TABLE DEPARTMENT
(
ID Number(7),
Name varchar2(25),
)

CREATE TABLE EMPLOYEES
(
ID Number(7),
LAST_NAME varchar2(25),

FIRST_NAME varchar2(25),

DEPT_ID Number(7)
)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.