- Create the DEPARTMENT tables based on the following: Column Name ID Name Data
ID: 3911824 • Letter: #
Question
- 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.
DEPT TABLE
CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
- 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, DEPT_ID columns. Name the columns in your new table ID, LAST_NAME, and DEPT_ID, 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.
- 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) NOT NULL,
Name VARCHAR2(25));
-- Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need.
SELECT DEPTNO, DNAME INTO DEPARTMENT FROM DEPT;
/*-
DEPT TABLE
CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');*/
/*- 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*/
CREATE TABLE EMPLOYEE (
ID NUMBER(7) NOT NULL,
LAST_NAME VARCHAR2(25),
FIRST_NAME VARCHAR2(25),
DEPT_ID NUMBER(7));
-- Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification.
ALTER TABLE EMPLOYEE
ALTER COLUMN LAST_NAME VARCHAR2(MAX);
--to get table structure use below
desc EMPLOYEE --Oracle
describe EMPLOYEE --MySQL
-- Confirm that both the DEPARTMENT and EMPLOYEE tables are stored in the data dictionary. (Hint: USER_TABLES)
SELECT table_name FROM dba_tables --(gives list of 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.
SELECT EMPO AS ID, ENAME AS LAST_NAME, DEPTNO AS DEPT_ID INTO EMPLOYEE2 FROM EMP;
--Drop the EMPLOYEE table.
Drop TABLE EMPLOYEE;
-- Rename the EMPLOYEE2 table to EMPLOYEE.
ALTER TABLE EMPLOYEE2 RENAME TO EMPLOYEE
Note: As per chegg rules we can only solve 5-7 sub question. please provide other questions as differen questions
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.