Introduction to SQL
SQL is a standard language for storing, manipulating and retrieving data in databases.
1.1 Select all information from SALGRADE table.
Select* from SALGRADE;
1.2 Select all information from EMP table.
Select * from EMP;
1.3 List all employees who have a salary between 1000 and 2000.
Select * from emp where sal between 1000 and 2000;
1.4 List department numbers and names in department name order.
Select deptno,dname from dept order by dname;
1.5 Display all the different job types.
Select distinct job from emp;
1.6 List the details of the employees in departments 10 and 20 in alphabetical order of names.
Select * from emp where deptno in(10,20) order by ename;
1.7 List names and jobs of all clerks in department 20.
Select ename,job from emp where lower(job)=’clerk’ anddeptno=20;
1.8 Display all employees’ names which have TH or LL in them.
Select enamefrom emp where enamelike (‘%TH%’) or enamelike (‘%LL%’);
1.9 List details for all employees who have a manager.
Select * from emp where lower(job)=’manager’;
1.10 Display names and total remuneration for all employees.
Select ename,comm from emp;
1.11 Display all employees who were hired during 1983.
Select * from emp where hiredate = ’12-JAN-83’;
1.12 Display name, annual salary and commission of all salespeople whose monthly salary is greater than their commission. The output should be ordered by salary, in descending order.
Select ename,sal*12 as annsal ,comm from emp wherelower(job)=’salesman’ and sal>comm order by sal desc;
1.13 Display all employee information in ascending order of manager number. King must be last.
Select * from emp order by mgr;
This exercise covers functions not just in the SELECT but also in WHERE and ORDER BY clauses. Note the column alias, which have been used.
2.1 List the employee name and salary increased by 15% and expressed as a whole number of dollars.
Select ename,sal*0.15as “whole number of dollors” fromemp;
2.2 Display each employee’s name and hiredate from dept. 20.
Select ename,hiredate from emp where deptno=20;
2.3 Display each employee name with hiredate, and salary review date. Assume review date is one year after hiredate. Order the output in ascending review date order.
Select ename,hiredate,hiredate+365 as “salary review date”from emp order by “salary review date”;
2.4 Print a list of employees displaying just the salary amount if more than 1500. If exactly 1500 display ‘On Target’, if less than 1500 display ‘Below 1500’.
Select ename,sal,CASE WHEN(sal<1500) THEN ‘Below’WHEN(sal=1500) THEN ‘On Target’ ELSE ‘’END RESULTfrom emp;
2.5 Write a query which will return the DAY of the week (i.e. MONDAY,) for any date entered in the format: DD.MM.YY.
Select to_char(hiredate,’DAY,DD-MON-YYYY’) from emp;
2.6 Employees hired on or before the 15th of any month are paid on the last Friday of that month. Those hired after the 15th are paid on the last Friday of the following month. Print a list of employees, their hiredate and first pay date. Sort on hiredate.
Select ename,trunc(months_between(sysdate,hiredate)/12)as “Employee Job Years” from emp