WORKSHEET – B
11. Write a query that produces the following for each employee:<empname> earns <salary> monthly but wants <3 times salary> label the column salaries.
SQL> select ename ||' earns '|| sal ||' monthly but wants '|| 3*sal as "salaries" from emp;
salaries
----------------------------------------
SMITH earns 800 monthly but wants 2400
ALLEN earns 1600 monthly but wants 4800
WARD earns 1250 monthly but wants 3750
JONES earns 2975 monthly but wants 8925
MARTIN earns 1250 monthly but wants 3750
BLAKE earns 2850 monthly but wants 8550
CLARK earns 2450 monthly but wants 7350
SCOTT earns 3000 monthly but wants 9000
KING earns 5000 monthly but wants 15000
TURNER earns 1500 monthly but wants 4500
ADAMS earns 1100 monthly but wants 3300
JAMES earns 950 monthly but wants 2850
FORD earns 3000 monthly but wants 9000
MILLER earns 1300 monthly but wants 3900
14 rows selected.
12. Create a query to display name and salary for all employees. Format that salary to be 15 characters long padded with ‘$’.
SQL> select ename, lpad(sal,15,'$') from emp;
ENAME LPAD(SAL,15,'$'
---------- ---------------
SMITH $$$$$$$$$$$$800
ALLEN $$$$$$$$$$$1600
WARD $$$$$$$$$$$1250
JONES $$$$$$$$$$$2975
MARTIN $$$$$$$$$$$1250
BLAKE $$$$$$$$$$$2850
CLARK $$$$$$$$$$$2450
SCOTT $$$$$$$$$$$3000
KING $$$$$$$$$$$5000
TURNER $$$$$$$$$$$1500
ADAMS $$$$$$$$$$$1100
ENAME LPAD(SAL,15,'$'
---------- ---------------
JAMES $$$$$$$$$$$$950
FORD $$$$$$$$$$$3000
MILLER $$$$$$$$$$$1300
14 rows selected.
13. Write a query that will display the employee’s name with first letter capitalized and all other leters lower case and length of their name, for all employees whose name starts with J, A or M.
SQL> select initcap(ename), length(ename) from emp where substr(ename,1,1) in ('A','J','M');
INITCAP(EN LENGTH(ENAME)
---------- -------------
Allen 5
Jones 5
Martin 6
Adams 5
James 5
Miller 6
6 rows selected.
14. Display the name, hiredate and day of the week on which the employee started. Label the column as DAY and order the results by day of the week.
SQL> select ename, to_char(hiredate,'day') from emp;
ENAME TO_CHAR(H
---------- ---------
SMITH wednesday
ALLEN friday
WARD sunday
JONES thursday
MARTIN monday
BLAKE friday
CLARK tuesday
SCOTT sunday
KING tuesday
TURNER tuesday
ADAMS saturday
JAMES thursday
FORD thursday
MILLER saturday
14 rows selected.
15. Create a query that display the employee name and commission amount. If the employee does not earn commission put “NO COMMISSION”. Label the column comm.
SQL> select ename, decode(comm,NULL,'NO COMMISSION') comm from emp;
ENAME COMM
---------- -------------
SMITH NO COMMISSION
ALLEN
WARD
JONES NO COMMISSION
MARTIN
BLAKE NO COMMISSION
CLARK NO COMMISSION
SCOTT NO COMMISSION
KING NO COMMISSION
TURNER
ADAMS NO COMMISSION
JAMES NO COMMISSION
FORD NO COMMISSION
MILLER NO COMMISSION
14 rows selected.
16. Display all employees including King, who has no manager.
SQL> select ename from emp where mgr is NULL;
ENAME
----------
KING
17. Create a query that will display the employee name, department number and all the employees who work in the same department as a given employee.
SQL> select ename, deptno from emp order by deptno;
ENAME DEPTNO
---------- ----------
CLARK 10
KING 10
MILLER 10
SMITH 20
ADAMS 20
FORD 20
SCOTT 20
JONES 20
ALLEN 30
BLAKE 30
MARTIN 30
JAMES 30
TURNER 30
WARD 30
14 rows selected.
18. Use SALGRAD table. Display grades for all employees with salary, name and hire-date of any employee.
SQL> select e.ename,e.sal,e.hiredate, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
ENAME SAL HIREDATE GRADE
---------- ---------- --------- ----------
SMITH 800 17-DEC-80 1
ADAMS 1100 23-MAY-87 1
JAMES 950 03-DEC-81 1
WARD 1250 22-FEB-81 2
MARTIN 1250 28-SEP-81 2
MILLER 1300 23-JAN-82 2
ALLEN 1600 20-FEB-81 3
TURNER 1500 08-SEP-81 3
JONES 2975 02-APR-81 4
BLAKE 2850 01-MAY-81 4
CLARK 2450 09-JUN-81 4
SCOTT 3000 19-APR-87 4
FORD 3000 03-DEC-81 4
KING 5000 17-NOV-81 5
14 rows selected.
19. Create a query to display the name and hire-date of any employee hired after employee Blake.
SQL> select ename, hiredate from emp where hiredate>(select hiredate from emp where ename='BLAKE');
ENAME HIREDATE
---------- ---------
MARTIN 28-SEP-81
CLARK 09-JUN-81
SCOTT 19-APR-87
KING 17-NOV-81
TURNER 08-SEP-81
ADAMS 23-MAY-87
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
9 rows selected.
20. Display all employee’s names and hire-date along with their manager’s name and hire-date for all employees who have hired before their manager.
SQL> select e.ename, e.hiredate, m.ename, m.hiredate from emp e, emp m where e.mgr=m.empno and e.hiredate<m.hiredate;
ENAME HIREDATE ENAME HIREDATE
---------- --------- ---------- ---------
ALLEN 20-FEB-81 BLAKE 01-MAY-81
WARD 22-FEB-81 BLAKE 01-MAY-81
JONES 02-APR-81 KING 17-NOV-81
CLARK 09-JUN-81 KING 17-NOV-81
BLAKE 01-MAY-81 KING 17-NOV-81
SMITH 17-DEC-80 FORD 03-DEC-81
6 rows selected.
21. Create a query that displays the employees name and the amount of the salaries of the employees are indicated through asterisks. Each asterisk signifies a hundred dollars. Sort the data in descending order of salary. Label the column EMPLOYEE_AND_THEIR_SALARIES.
SQL> select rpad(ename,15,' ')||lpad(' ',(sal/100)+1,'*') "EMPLOYEE_AND_THEIR_SALARIES" from emp order by sal;
EMPLOYEE_AND_THEIR_SALARIES
--------------------------------------------------------------------------------
SMITH ********
JAMES *********
ADAMS ***********
WARD ************
MARTIN ************
MILLER *************
TURNER ***************
ALLEN ****************
CLARK ************************
BLAKE ****************************
JONES *****************************
SCOTT ******************************
FORD ******************************
KING **************************************************
14 rows selected.
0 comments:
Post a Comment