Sql Project 1 Question And Answers From Ifsm 411 Class Fall 2009

Front 1

1. Create a table named DEPT (Departments) having the following structure:

DEPTNO (Department Number) - number(2), primary key
DNAME (Department Name) - varchar(10)

Back 1

1.
CREATE TABLE DEPT (
DEPTNO NUMBER(2),
DNAME VARCHAR(10),
CONSTRAINT DEPTNO_PK PRIMARY KEY (DEPTNO) );

Front 2

2. Create a table named EMP (Employees) having the following structure:

EMPNO (Employee Number) - number(4), primary key
ENAME (Employee Name) - varchar(10)
JOB - varchar(10)
MGR (Manager) - number(4), referencing table EMP, field EMPNO
HIREDATE - date, having as default the current date
SAL (salary) - number(7, 2), NOT NULL
COMM (commision) - number(7, 2)
DEPTNO (Department Number) - Foreign Key from the DEPT table, NOT NULL

Back 2

2.
CREATE TABLE EMP (
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(10),
MGR NUMBER(4) CONSTRAINT EMP_MGR REFERENCES EMP(EMPNO),
HIREDATE DATE DEFAULT SYSDATE,
SAL NUMBER(7,2) NOT NULL,
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL CONSTRAINT DEPTNO_DEPT REFERENCES DEPT(DEPTNO) );

Front 3

3. Create a table named SALGRADE (Salary grade) having the following structure:

GRADE - number(2), primary key
LOSAL - number(7, 2)
HISAL - number(7, 2)

Back 3

3.
CREATE TABLE SALGRADE (
GRADE NUMBER(2) PRIMARY KEY,
LOSAL NUMBER(7,2),
HISAL NUMBER(7,2) );

Front 4

4. Insert into table DEPT the following records:

DEPTNO DNAME
------ ----------
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES

Back 4

4.
INSERT INTO DEPT (DEPTNO, DNAME)
VALUES (10, 'ACCOUNTING');
INSERT INTO DEPT (DEPTNO, DNAME)
VALUES (40, 'OPERATIONS');
INSERT INTO DEPT (DEPTNO, DNAME)
VALUES (20, 'RESEARCH');
INSERT INTO DEPT (DEPTNO, DNAME)
VALUES (30, 'SALES');

Front 5

5. Insert into table EMP the following records (the insertion order is important and must be respected):

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ----- ---- ---- -------- -------- ------- ------
7839 KING PRESIDENT 09-JUL-84 5,000.00 10
7782 CLARK MANAGER 7839 14-MAY-84 2,450.00 10
7566 JONES MANAGER 7839 31-OCT-83 2,975.00 20
7698 BLAKE MANAGER 7839 11-JUL-84 2,850.00 30
7934 MILLER CLERK 7782 21-NOV-83 1,300.00 10
7902 FORD ANALYST 7566 05-DEC-83 3,000.00 20
7788 SCOTT ANALYST 7566 05-MAR-84 3,000.00 20
7499 ALLEN SALESMAN 7698 15-AUG-83 1,600.00 300.00 30
7654 MARTIN SALESMAN 7698 05-DEC-83 1,250.00 1,400.00 30
7521 WARD SALESMAN 7698 26-MAR-84 1,250.00 500.00 30
7844 TURNER SALESMAN 7698 04-JUN-84 1,500.00 .00 30
7900 JAMES CLERK 7698 23-JUL-84 950.00 30
7369 SMITH CLERK 7902 13-JUN-83 800.00 20
7876 ADAMS CLERK 7788 04-JUN-84 1,100.00 20

Back 5

5.
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)
VALUES (7839,'KING','PRESIDENT','09-JUL-84',5000.00,10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7782,'CLARK','MANAGER', 7839, '14-MAY-84', 2450.00, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7566,'JONES','MANAGER',7839,'31-OCT-83',2975.00,20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7698,'BLAKE','MANAGER',7839,'11-JUL-84',2850.00,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7934,'MILLER','CLERK',7782,'21-NOV-83',1300.00,10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7902,'FORD','ANALYST',7566,'05-DEC-83',3000.00,20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7788,'SCOTT','ANALYST',7566,'05-MAR-84',3000.00,20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7499,'ALLEN','SALESMAN',7698,'15-AUG-83',1600.00,300.00,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7654,'MARTIN','SALESMAN',7698,'05-DEC-83',1250.00,1400.00,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7521,'WARD','SALESMAN',7698,'26-MAR-84',1250.00,500.00,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7844,'TURNER','SALESMAN',7698,'04-JUN-84',1500.00,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7900,'JAMES','CLERK',7698,'23-JUL-84',950.00,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7369,'SMITH','CLERK',7902,'13-JUN-83',800.00,20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES (7876,'ADAMS','CLERK',7788,'04-JUN-84',1100.00,20);

Front 6

6. Insert into table SALGRADE the following records:

GRADE LOSAL HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

Back 6

6.
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (1,700,1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (2,1201,1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (3,1401,2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (4,2001,3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (5,3001,9999);

Front 7

7. Select all informations from table SALGRADE.

GRADE LOSAL HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

Back 7

7.
SELECT * FROM SALGRADE;

Front 8

8. Print all employees having salary between 1000 and 2000.
ENAME DEPTNO SAL
----- ------ --------
ALLEN 30 1,600.00
WARD 30 1,250.00
MARTIN 30 1,250.00
TURNER 30 1,500.00
ADAMS 20 1,100.00
MILLER 10 1,300.00

Back 8

8.
SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000
ORDER BY DEPTNO DESC;

Front 9

9. Print the numbers of all department and their names in the order of department names.

DEPTNO DNAME
------ ----------
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES

Back 9

9.
SELECT *
FROM DEPT
ORDER BY DNAME;

Front 10

10. Print all different types of jobs.

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

Back 10

10.
SELECT DISTINCT JOB
FROM EMP
ORDER BY JOB ASC;

Front 11

11. Print all employees from department 10 and 20 in order of their names.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ----- ----- ---- --------- -------- ---- ------
7876 ADAMS CLERK 7788 04-JUN-84 1,100.00 20
7782 CLARK MANAGER 7839 14-MAY-84 2,450.00 10
7902 FORD ANALYST 7566 05-DEC-83 3,000.00 20
7566 JONES MANAGER 7839 31-OCT-83 2,975.00 20
7839 KING PRESIDENT 09-JUL-84 5,000.00 10
7934 MILLER CLERK 7782 21-NOV-83 1,300.00 10
7788 SCOTT ANALYST 7566 05-MAR-84 3,000.00 20
7369 SMITH CLERK 7902 13-JUN-83 800.00 20

Back 11

11.
SELECT *
FROM EMP
WHERE DEPTNO = 10
OR DEPTNO = 20
ORDER BY ENAME;

Front 12

12. Print all employees having TH or LL in their names.

ENAME
------
SMITH
ALLEN
MILLER

Back 12

12.
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%LL%'
OR ENAME LIKE '%TH%' ;

Front 13

13. Print for each employee from department 20 the name and the hiring date. Make sure that you specify the alias 'DATE_HIRED' after your expression.

ENAME DATE_HIRED
---------------------------
SMITH June,Thirteenth 1983
JONES October,Thirty-First 1983
SCOTT March,Fifth 1984
ADAMS June,Fourth 1984
FORD December,Fifth 1983

Back 13

13.
SELECT ENAME,
TO_CHAR(HIREDATE,'MONTH,DDSPTH YYYY') "DATE_HIRED"
FROM EMP
WHERE DEPTNO = 20;

Front 14

14. Print for each employee the name, the hiring date and add a year to the hiring date for each one. Order the output by the hiring date to which you added one year.

ENAME HIREDATE REVIEW
---------------------------
SMITH 13-jun-83 13-jun-84
ALLEN 15-aug-83 15-aug-84
JONES 31-oct-83 31-oct-84
MILLER 21-nov-83 21-nov-84
MARTIN 05-dec-83 05-dec-84
FORD 05-dec-83 05-dec-84
SCOTT 05-mar-84 05-mar-85
WARD 26-mar-84 26-mar-85
CLARK 14-may-84 14-may-85
TURNER 04-jun-84 04-jun-85
ADAMS 04-jun-84 04-jun-85
BLAKE 11-jul-84 11-jul-85
KING 09-jul-84 09-jul-85
JAMES 23-jul-84 23-jul-85

Back 14

14.
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,12)"REVIEW"
FROM EMP
ORDER BY HIREDATE;