home study engineering computer science questions and an

home / study / engineering / computer science / questions and answers / -- echo building demo tables. please wait. drop ...

Your question has been answered

Let us know if you got a helpful answer. Rate this answer

Question: -- echo Building demo tables. Please wait. DROP TA...

Bookmark

-- echo Building demo tables. Please wait.

DROP TABLE EMP CASCADE CONSTRAINTS;

DROP TABLE DEPT;

DROP TABLE SALGRADE;

DROP TABLE Prod CASCADE CONSTRAINTS;

DROP TABLE Vend;

CREATE TABLE DEPT (

DEPTNO NUMBER(2) NOT NULL,

DNAME CHAR(14),

LOC CHAR(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 TABLE EMP (

EMPNO NUMBER(4) NOT NULL,

ENAME CHAR(10),

JOB CHAR(9),

MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY

REFERENCES EMP (EMPNO),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2) NOT NULL,

CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),

CONSTRAINT EMP_PRIM_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,\'KING\',\'PRESIDENT\',NULL,\'17-NOV-1981\',5000,NULL,10);

INSERT INTO EMP VALUES (7698,\'BLAKE\',\'MANAGER\',7839,\'1-MAY-1981\',2850,NULL,30);

INSERT INTO EMP VALUES (7782,\'CLARK\',\'MANAGER\',7839,\'9-JUN-1981\',2450,NULL,10);

INSERT INTO EMP VALUES (7566,\'JONES\',\'MANAGER\',7839,\'2-APR-1981\',2975,NULL,20);

INSERT INTO EMP VALUES (7654,\'MARTIN\',\'SALESMAN\',7698,\'28-SEP-1981\',1250,1400,30);

INSERT INTO EMP VALUES (7499,\'ALLEN\',\'SALESMAN\',7698,\'20-FEB-1981\',1600,300,30);

INSERT INTO EMP VALUES (7844,\'TURNER\',\'SALESMAN\',7698,\'8-SEP-1981\',1500,0,30);

INSERT INTO EMP VALUES (7900,\'JAMES\',\'CLERK\',7698,\'3-DEC-1981\',950,NULL,30);

INSERT INTO EMP VALUES (7521,\'WARD\',\'SALESMAN\',7698,\'22-FEB-1981\',1250,500,30);

INSERT INTO EMP VALUES (7902,\'FORD\',\'ANALYST\',7566,\'3-DEC-1981\',3000,NULL,20);

INSERT INTO EMP VALUES (7369,\'SMITH\',\'CLERK\',7902,\'17-DEC-1980\',800,NULL,20);

INSERT INTO EMP VALUES (7788,\'SCOTT\',\'ANALYST\',7566,\'09-DEC-1982\',3000,NULL,20);

INSERT INTO EMP VALUES (7876,\'ADAMS\',\'CLERK\',7788,\'12-JAN-1983\',1100,NULL,20);

INSERT INTO EMP VALUES (7934,\'MILLER\',\'CLERK\',7782,\'23-JAN-1982\',1300,NULL,10);

CREATE TABLE SALGRADE (

GRADE NUMBER,

LOSAL NUMBER,

HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,700,1200);

INSERT INTO SALGRADE VALUES (2,1201,1400);

INSERT INTO SALGRADE VALUES (3,1401,2000);

INSERT INTO SALGRADE VALUES (4,2001,3000);

INSERT INTO SALGRADE VALUES (5,3001,9999);

CREATE TABLE Prod

( ProdNo NUMBER(4) NOT NULL,

PName CHAR(10),

Type CHAR(4),

Family NUMBER(4),

Price NUMBER(7,2),

Disc NUMBER(3,1),

IntroDate DATE,

VendNo NUMBER(4),

Inv NUMBER(3),

PRIMARY KEY (ProdNo));

INSERT INTO Prod VALUES (

4186, \'Lotus 123\', \'SPSH\', 2215, 399.95, 25, \'08-MAY-1990\' ,26, 35);

INSERT INTO Prod VALUES (

2215, \'Windows\', \'OS\', 7224, 129, 40, \'15-JUN-1990\' ,12, 123);

INSERT INTO Prod VALUES (

6240, \'AmiPro\', \'WP\', 2215, 295.5, 33.3, \'01-JUN-1990\' ,26, 17);

INSERT INTO Prod VALUES (

7224, \'MS-DOS\', \'OS\', NULL, 99.95, 30, \'03-MAR-1991\' ,12, 88);

INSERT INTO Prod VALUES (

3055, \'Lotus 123\', \'SPSH\', 3088, 399.95, 0, \'18-OCT-1990\' ,26, 12);

INSERT INTO Prod VALUES (

3088, \'Macintosh\', \'OS\', NULL, 149.95, NULL, \'12-DEC-1989\' ,41, 142);

INSERT INTO Prod VALUES (

1108, \'Finance\', \'BUS\', 4186, 99.95, NULL, \'22-APR-1991\' ,82, 16);

INSERT INTO Prod VALUES (

9167, \'Lotus 123\', \'SPSH\', 7224, 399.95, 35, \'26-FEB-1989\' ,26, 71);

INSERT INTO Prod VALUES (

4925, \'Paradox\', \'DBMS\', 7224, 345, 25, \'21-SEP-1989\' ,55, 64);

INSERT INTO Prod VALUES (

1067, \'Finance\', \'BUS\', 9167, 99.95, NULL, \'07-MAR-1989\' ,82, 0);

INSERT INTO Prod VALUES (

6482, \'BusPlan\', \'BUS\', 4186, 54.5, 10, \'05-JAN-1991\' ,82, 41);

INSERT INTO Prod VALUES (

7190, \'BusPlan\', \'BUS\', 9167, 54.5, 10, \'14-FEB-1989\' ,82, NULL);

INSERT INTO Prod VALUES (

6888, \'BusPlan\', \'BUS\', 3055, 54.5, 0, \'14-FEB-1989\' ,82, 26);

INSERT INTO Prod VALUES (

3981, \'SQL*Report\', \'DBMS\', 5476, 149.5, 0, \'22-SEP-1990\' ,58, 12);

INSERT INTO Prod VALUES (

9482, \'Quattro\', \'SPSH\', 7224, 199.95, 30, \'24-AUG-1990\' ,55, 53);

INSERT INTO Prod VALUES (

5476, \'Oracle\', \'DBMS\', 7224, 895, 5, \'12-SEP-1990\' ,58, 38);

INSERT INTO Prod VALUES (

3007, \'Finance\', \'BUS\', 9482, 99.95, NULL, \'06-NOV-1990\' ,82, 17);

INSERT INTO Prod VALUES (

8120, \'Inventory\', \'BUS\', 9482, 199.5, 10, \'06-NOV-1990\' ,82, 0);

INSERT INTO Prod VALUES (

1830, \'SQL*Plus\', \'DBMS\', 5476, 199.5, 5, \'06-OCT-1990\' ,58, 19);

CREATE TABLE Vend

( VName CHAR(10),

VState CHAR(2),

VendNo NUMBER(2) NOT NULL,

Acct CHAR(5),

PRIMARY KEY (VendNo));

INSERT INTO Vend VALUES (

\'Apple\', \'CA\', 41, \'COD\');

INSERT INTO Vend VALUES (

\'Oracle\', \'CA\', 58, \'30\');

INSERT INTO Vend VALUES (

\'Lotus\', \'UT\', 26, \'30\');

INSERT INTO Vend VALUES (

\'Microsoft\', \'WA\', 12, \'10\');

INSERT INTO Vend VALUES (

\'Acme\', \'UT\', 82, \'COD\');

INSERT INTO Vend VALUES (

\'Borland\', \'CA\', 55, \'30\');

INSERT INTO Vend VALUES (

\'Ace\', \'OR\', 67, \'30\');

COMMIT;

#1. Write a query to display employee number, employee name, hiredate, manager\'s name for those employees, whose manager\'s name starts with K or M or S. Label the columns Employee Number, Employee Name, Hiredate, Mgr Name.

Solution

Hi,

Here\'s the sql query

select t1.EMPNO as \"Employee Number\", t1.ENAME as \"Employee Name\", t1.HIREDATE as Hiredate, t1.MGR as \"Mgr Name\" from EMP t1 INNER JOIN EMP t2 on (t1.MGR=t2.EMPNO) where t2.ENAME like \"K%\" or \"M%\" or \"S%\";

Here we are using inner join, say t1 is EMP and another EMP is t2, we are joining with the condition t1.MGR=t2.EMPNO i.e for a employee retrieving his manager then applying LIKE operator as we want manager\'s name start with K or M or S.

Here\'s the sample of my local sql output.

mysql> select * from EMP;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-28 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-09-02 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-05-12 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

mysql> select t1.EMPNO as \"Employee Number\", t1.ENAME as \"Employee Name\", t1.HIREDATE as Hiredate, t1.MGR as \"Mgr Name\" from EMP t1 INNER JOIN EMP t2 on (t1.MGR=t2.EMPNO) where t2.ENAME like \"K%\" or \"M%\" or \"S%\";
+-----------------+---------------+------------+----------+
| Employee Number | Employee Name | Hiredate | Mgr Name |
+-----------------+---------------+------------+----------+
| 7566 | JONES | 1981-04-02 | 7839 |
| 7698 | BLAKE | 1981-05-01 | 7839 |
| 7782 | CLARK | 1981-06-09 | 7839 |
+-----------------+---------------+------------+----------+
3 rows in set, 2 warnings (0.00 sec)