tasks

DATABASE FUNDAMENTALS
DDL and other DML statements
Q – 1: Write an SQL statement create the EMPLOYEE table with the following attributes and constraints.
EMP_NUM (PK) number,
EMP_TITLE Characters of length up to 5,
EMP_LNAME Characters of length up to 25 (can’t be NULL),
EMP_FNAME Characters of length up to 25 (can’t be NULL),
EMP_INITIAL Only one character, (can’t be NULL)
EMP_DOB Date,
EMP_HIRE_DATE Date,
EMP_AREACODE Characters of length 3 (default value of ‘001’)
EMP_PHONE Characters of length 8,
EMP_MGR number
As your answer to this question, please include (i) your SQL statement to create the EMPLOYEE table, and (ii) a screenshot of the result of the following SQL statement
DESC EMPLOYEE;
Ans:
Create Table EMPLOYEE (EMP_NUM int PRIMARY KEY,
EMP_TITLE varchar(5),
EMP_LNAME varchar(25) not NULL,
EMP_FNAME varchar(25) not NULL,
EMP_INITIAL varchar(1) NOT NULL,
EMP_DOB DATE,
EMP_HIREDATE DATE,
EMP_AREACODE varchar(3)DEFAULT ‘001’,
EMP_PHONE varchar(8),
EMP_MGR int);


Q – 2: Write an SQL statement to change the column name ‘EMP_MGR’ to ‘EMP_MGR_NUM’. [Hint: ALTER TABLE to rename column, discussed in the class].
As your answer to this question, please include (i) your SQL statement to alter the EMPLOYEE table, and (ii) a screenshot of the result of the following SQL statement:
DESC EMPLOYEE;
Ans:
ALTER TABLE EMPLOYEE RENAME COLUMN EMP_MGR TO EMP_MGR_NUM;

Q – 3: Write an SQL statement to ADD FOREIGN KEY CONSTRAINT to EMP_MGR_NUM to reference to EMP_NUM (self-reference). This...