Creating and Modifying Tables

1. Create a new table called JOBS with two field, a SMALLINT called JOBNO and a 15 character text field called JOB.

CREATE TABLE jobs ( jobno SMALLINT, job VARCHAR(15) );

2. Fill your new JOBS table with null values for the JOBNO and the job values from the EMP table.There should be only one row with each job type (ie. no repeats).

INSERT INTO jobs (job) SELECT DISTINCT job FROM emp;

3. Give a unique job number to each job type.

 UPDATE jobs SET jobno= 10 WHERE job = 'ANALYST';
 UPDATE jobs SET jobno= 20 WHERE job = 'CLERK';
 UPDATE jobs SET jobno= 30 WHERE job = 'MANAGER';
 UPDATE jobs SET jobno= 40 WHERE job = 'SALESMAN';
 UPDATE jobs SET jobno= 50 WHERE job = 'PRESIDENT';

4. Create a new empty table called EMP1. This table should have the same _elds as EMP but  with an additional _field called JOBNO of type SMALLINT. (Note|also make EMPNO and DEPTNO type SMALLINT.

CREATE TABLE "emp1" (
"empno" SMALLINT,
"ename" VARCHAR(15),
"job" VARCHAR(15),
"mgr" SMALLINT,
"hiredate" DATE,
"sal" SMALLINT,
"comm" SMALLINT,
"deptno" SMALLINT,
"jobno" SMALLINT);

5. Fill your new EMP1 table with the data from EMP and JOBS.

INSERT INTO emp1 SELECT emp.*, jobno FROM emp INNER JOIN jobs using(job);

6. Remove the JOB column from your EMP1 table.

ALTER TABLE emp1 DROP COLUMN job;
“OR”
SELECT empno, ename, mgr, hiredate, sal, comm, deptno, jobno
INTO temp FROM emp1;
DROP TABLE emp1;
ALTER TABLE temp RENAME TO emp1;

7. Display the data from the EMP1 and JOBS tables so that the output is identical to the original EMP table.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp1
INNER JOIN jobs ON emp1.jobno = jobs.jobno;


8. Add a new Department to the DEPT table, and add a Manager and two Clerks to the EMP table that will belong to the new department.

INSERT INTO dept VALUES (50, 'NEWDEPT', 'LONDON');
INSERT INTO emp VALUES (8001, 'FRED', 'MANAGER', 7839, '14/01/1984', 3100, null, 50);
INSERT INTO emp VALUES (8002, 'JIM', 'CLERK', 8001, '18/04/1984', 1020, null, 50);
INSERT INTO emp VALUES (8003, 'SHEILA', 'CLERK', 8001, '08/12/1984', 955, null, 50);

9. Transfer one of the new clerks to a different department and transfer one of the previously existing clerks to your new department.
UPDATE emp SET deptno = 40, mgr = 7788 WHERE empno = 8002;
UPDATE emp SET deptno = 50, mgr = 8001 WHERE empno = 7876;


I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: