Question

A) DDL

For Company DB Schema, Answer the below questions:

Emp (Empno, Ename, Job, Hiredate, Sal, Comm, Deptno)

Dept (Deptno, Dname, loc)

1. Retrieve the data of all employees.

2. Retrieve the data of all Salemanemployees.

3. Retrieve the name and Salary of all employees.

4. Retrieve the name and salary of Salemanemployees.

5. Retrieve the name and salary of all employees with Salary more than 2000.

6. Retrieve the Name and Salary of all employees who work in department no 20.

7. Retrieve the name and Job of all employees with Salary more than 2000 and hiredate after 01-01-1981.

8. Retrieve the Job and Salary of all employees with Name = FORD or MARTIN.

9. Retrieve the Name and Number of all Departments.

10. Retrieve the Employee Name and Department number for all employees.

11. Retrieve the Employee Name and Department name for all employees.

12. Retrieve the Employee Name and who works in Department name = RESEARCH.

13. Retrieve the Employee names who workin one of these Departments: RESEARCHand SALES.

14. Retrieve the Employee name who works in department located in NEW YORK.

please write in computer word not on paper

Answer 1

1. Retrieve the data of all employees.
         select *from emp

2. Retrieve the data of all salesman employees.

      select *from emp where job='salesman'

3. Retrieve the name and salary of all employees

    select ename, sal from emp

4. Retrieve the name and salary of salesman employees
    select ename, sal from emp where job='salesman'

5. Retrieve the name and salary of all employees with salary more than 2000

    select ename, sal from emp where sal>2000

6. Retrieve the name and salary of all employees who work in dept no 20

    select ename, sal from emp where deptno=20

7. Retrieve the name and job of all employees with salary more than 2000 and hire date after 01-01-1991

    select ename, job from emp where sal > 2000 AND hiredate > '01-jan-1991'

8. Retrieve the Job and Salary of all employees with name= FORD or MARTIN
    select job,sal from emp where ename='FORD' OR ename= 'MARTIN'

9. Retrieve the name and number of all departments

    select dbname,deptno from dept

10. Retrieve the employee name and dept no of all employees

    select ename,deptno from emp

11. Retrieve the employee name and dept name of all employees

    select e.ename,d.dbname from emp e,dept d where e.deptno=d.deptno

12. Retrieve the employee name who works in department name=RESEARCH

    select ename from emp where deptno in (select deptno from dept where dbname='RESEARCH')

13. Retrieve the employee name who works in department name=RESEARCH and SALES

select ename from emp where deptno in (select deptno from dept where dbname='RESEARCH' OR dbname='SALES')

14. Retrieve the employee name who works in department located in NEW YORK

    select ename from emp where deptno in (select deptno from dept where loc='NEW YORK')