SQL PLSQL SERVER PROGRAMMES
4.List all employee names and their salaries, whose salary lies between 1500/- and 3500/- both inclusive.
INPUT
SQL>select ename from emp where sal between 1500 and 3500;
RESULT
ENAME
----------
ALLEN
JONES
BLAKE
CLARK
SCOTT
TURNER
FORD
russel
greg
9 rows selected.
5. List all employee names and their and their manager whose manager is 7902 or 7566 0r 7789.
INPUT SQL>select ename from emp where mgr in(7602,7566,7789);
RESULT
ENAME
-------
SCOTT
FORD
6. List all employees which starts with either J or T.
INPUT SQL>select ename from emp where ename like ‘J%’ or ename like ‘T%’;
RESULT:
ENAME
---------
JONES
TURNER
JAMES
7. List all employee names and jobs, whose job title includes M or P.
INPUT SQL>select ename,job from emp where job like ‘M%’ or job like ‘P%’;
RESULT:
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
KING PRESIDENT
8. List all jobs available in employee table.
INPUT SQL>select distinct job from emp;
RESULT:
JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
assistant
clerk
7 rows selected.
9. List all employees who belongs to the department 10 or 20.
INPUT SQL>select ename from emp where deptno in (10,20);
RESULT:
ENAME
----------
SMITH
JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER
8 rows selected.
10. List all employee names , salary and 15% rise in salary.
INPUT SQL>select ename , sal , sal+0.15* sal from emp;
RESULT:
ENAME SAL SAL+0.15*SAL
---------- ---------- ------------
SMITH 800 920
ALLEN 1600 1840
WARD 1250 1437.5
JONES 2975 3421.25
MARTIN 1250 1437.5
BLAKE 2850 3277.5
CLARK 2450 2817.5
7 rows selected.
11. List minimum , maximum , average salaries of employee.
INPUT SQL>select min(sal),max(sal),avg(sal) from emp;
RESULT:
MIN(SAL) MAX(SAL) AVG(SAL)
--------- ---------- ----------
3 5000 1936.94118
12. Find how many job titles are available in employee table.
INPUT SQL>select count (distinct job) from emp;
RESULT:
COUNT(DISTINCTJOB)
------------------
7
13. What is the difference between maximum and minimum salaries of employees in the organization?
INPUT SQL>select max(sal)-min(sal) from emp;
RESULT:
MAX(SAL)-MIN(SAL)
-----------------
4997
14. Display all employee names and salary whose salary is greater than minimum salary of the company and job title starts with ‘M’.
INPUT SQL>select ename,sal from emp where job like ‘M%’ and sal > (select min (sal) from emp);
RESULT
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
15. Find how much amount the company is spending towards salaries.
INPUT SQL>select sum (sal) from emp;
RESULT
SUM(SAL)
---------
32928
16. Display name of the dept. with deptno 20.
INPUT SQL>select ename from emp where deptno = 20;
RESULT
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
17. List ename whose commission is NULL.
INPUT SQL>select ename from emp where comm is null;
ENAME
RESULT ----------
CLARK
SCOTT
KING
ADAMS
JAMES
FORD
6 rows selected.
18. Find no.of dept in employee table.
INPUT SQL>select count (distinct ename) from emp;
RESULT
COUNT(DISTINCTENAME
--------------------
17
19. List ename whose manager is not NULL.
INPUT SQL>select ename from emp where mgr is not null;
RESULT
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
5 rows selected.
Writing Queries using GROUP BY and other clauses.
To write queries using clauses such as GROUP BY, ORDER BY, etc. and retrieving information by joining tables.
Source tables: emp, dept, programmer, software, study.
Order by : The order by clause is used to display the results in sorted order.
Group by : The attribute or attributes given in the clauses are used to form groups. Tuples with the same value on all attributes in the group by clause are placed in one group.
Having: SQL applies predicates (conditions) in the having clause after groups have been formed, so aggregate function be used.
1. Display total salary spent for each job category.
INPUT SQL>select job,sum (sal) from emp group by job;
RESULT
JOB SUM(SAL)
--------- ----------
ANALYST 6000
CLERK 23050
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
assistant 2200
clerk 2003
7 rows selected.
2. Display lowest paid employee details under each manager.
INPUT SQL>select ename, sal from emp where sal in (select min(sal) from emp group by mgr);
RESULT
ENAME SAL
---------- ----------
chai 3
JAMES 950
MILLER 1000
ADAMS 1100
russel 2200
5 rows selected.
3. Display number of employees working in each department and their department name.
INPUT SQL> select dname, count (ename) from emp, dept where emp.deptno=dept.deptno group by dname;
RESULT
DNAME COUNT(ENAME)
-------------- ------------
ACCOUNTING 3
RESEARCH 5
SALES 9
4. Display the sales cost of package developed by each programmer.
INPUT SQL>select pname, sum(scost) from software group by pname;
RESULT
PNAME SUM(SCOST)
-------------------- ----------
john 12000
kamala 12000
raju 12333
3 rows selected.
5. Display the number of packages sold by each programmer.
INPUT SQL>select pname, count(title) from software group by pname;
RESULT
PNAME COUNT(TITLE)
-------------------- ------------
john 1
kamala 1
raju 1
ramana 1
rani 1
5 rows selected.
6. Display the number of packages in each language for which the development cost is less than thousand.
INPUT SQL>select devin, count(title) from software where dcost < 1000 group by devin;
RESULT
DEVIN COUNT(TITLE)
---------- ------------
cobol 1
7. Display each institute name with number of students.
INPUT SQL>select splace, count(pname) from study group by splace;
RESULT
SPLACE COUNT(PNAME)
-------------------- ------------
BDPS 2
BITS 1
BNRILLIANI 1
COIT 1
HYD 1
5 rows selected.
8. How many copies of package have the least difference between development and selling cost, were sold?
INPUT SQL>select sold from software where scost – dcost=(select min(scost – dcost) from software);
RESULT
SOLD
---------
11
9. Which is the costliest package developed in Pascal.
INPUT SQL>select title from software where devin = ‘PASCAL’ and dcost = (select max(dcost)from software where devin = ‘PASCAL’);
RESULT
no rows selected
10. Which language was used to develop most no .of packages.
INPUT SQL>select devin, count (*) from software group by devin having count(*) = (select max(count(*) ) from software group by devin);
RESULT
DEVIN COUNT(*)
---------- ----------
jsp 2
11.Who are the male programmers earning below the average salary of female programmers?
INPUT SQL>select pname from programmer where sal < (select avg(sal) from programmer where sex = ‘F’) and sex = ‘M’;
RESULT
PNAME
--------------------
vijay
12. Display the details of software developed by the male programmers earning more than 3000/-.
INPUT SQL>select programmer.pname, title, devin from programmer, software where sal > 3000 and sex = ‘M’ and programmer.pname = software.pname;
RESULT
no rows selected
13. Display the details of software developed in c language by female programmers of pragathi.
INPUT SQL>select software.pname, title, devin, scost, dcost, sold from programmer, software, study where devin = ‘c’ and sex =’F’ and splace = ‘pragathi’ and programmer.pname = software.pname and software.pname = study.pname;
14. Which language has been stated by the most of the programmers as proficiency one?
INPUT SQL>select prof1, count(*) from programmer group by prof1 having count (*) = (select max (count (*) ) from programmer group by prof1);
Writing Nested Queries.
To write queries using Set operations and to write nested queries.
Set Operations:
UNION - OR
INTERSECT - AND
EXCEPT - - NOT
NESTED QUERY:- A nested query makes use of another sub-query to compute or retrieve the information.
1. Find the name of the institute in which the person studied and developed the costliest package.
INPUT SQL>select splace, pname from study where pname = (select pname from software where scost = (select max (scost) from software);
RESULT
SPLACE PNAME
------------ -------------
SAHBHARI MARY
2. Find the salary and institute of a person who developed the highest selling package.
INPUT SQL> select study.pname, sal, splace from study, programmer where study.pname = programmer.pname and study.pname = (select pname from software where scost = (select max (scost) from software));
RESULT
PNAME SAL SPLACE
----------- ------ -----------
MARY 4500 SABHARI
3. How many packages were developed by the person who developed the cheapest package.
INPUT SQL>select pname, count (title) from software where dcost = (select min(dcost) from software) group by pname;
RESULT
PNAME COUNT(TITLE)
------------- ----------------------
VIJAY 1
4. Calculate the amount to be recovered for those packages whose development cost has not yet recovered.
INPUT SQL>select title , (dcost-scost) from software where dcost > scost;
5. Display the title, scost, dcost, difference of scost and dcost in the descending order of difference.
INPUT SQL> select title, scost, dcost, (scost - dcost) from software descending order by (scost-dcost);
6. Display the details of those who draw the same salary.
INPUT SQL> select p.pname, p.sal from programmer p, programmer t where p.pname <> t.pname and p.sal = t.sal;(or)
INPUT SQL>select pname,sal from programmer t where pname<>t.pname and sal= t.sal;
Writing Queries using functions.
AIM: To write queries using single row functions and group functions.
1. Display the names and dob of all programmers who were born in january.
INPUT SQL>select pname , dob from programmer where to_char (dob,’MON’)=’JAN’;
2. Calculate the experience in years of each programmer and display along with programmer name in descending order.
INPUT SQL> select pname, round (months_between(sysdate, doj)/12, 2) "EXPERIENCE" from programmer order by months_between (sysdate, doj) desc;
3. List out the programmer names who will celebrate their birthdays during current month.
INPUT SQL>select pname from programmer where to_char(dob,’MON’) like to_char (sysdate, ‘MON’);
4. Display the least experienced programmer’s details.
INPUT SQL>select * from programmer where doj = (select max (doj) from programmer);
5. Who is the most experienced programmer knowing pascal.
INPUT SQL>select pname from programmer where doj = (select min (doj) from programmer);
6. Who is the youngest programmer born in 1965.
INPUT SQL> select pname , dob from programmer where dob = (select max (dob) from programmer where to_char (dob,'yy') = 65);
7. In which year, most of the programmers are born.
INPUT SQL>select to_char (dob , ‘YY’) from programmer group by to_char (dob, ‘YY’) having count(*) = (select max (count(*)) from programmer group by to_char(dob,’YY’);
8. In which month most number of programmers are joined.
INPUT SQL>select to_char (doj,’YY’) from programmer group by to_char (doj,’YY’) having count (*) = (select max (count(*)) from programmer group by to_char (doj,’YY’);
9. What is the length of the shortest name in programmer table ?
INPUT SQL>select length (pname) from programmer where length (pname) = select min ( length (pname) from programmer);
10. Display the names of the programmers whose name contains up to 5 characters.
INPUT SQL>select pname from programmer where length (pname) <=5;
11. Display all packages names in small letters and corresponding programmer names in uppercase letters.
INPUT SQL>select lower (title), upper (pname) from software;
Writing Queries on views.
AIM: To write queries on views.
1. Create a view from single table containing all columns from the base table.
SQL>create view view1 as (select * from programmer);
2. Create a view from single table with selected columns.
SQL>create a view view2 as (select pname,dob,doj,sex,sal from programmer);
3. Create a view from two tables with all columns.
SQL>create view xyz as select * from programmer full natural join software;
4. Create a view from two tables with selected columns.
SQL> create view lmn as (select programmer, pname, title, devin from programmer, software where sal < 3000 and programmer.pname = software.pname);
5. Check all DML commands with above 4 views.
INPUT SQL> insert into view1 values (‘ramu’,’12-sep-03’,’28-jan-85’,’f’,’dbase’,’oracle’,74000);
RESULT
1 row created;
INPUT SQL>update view1 set salary =50000 where pname like ‘raju’;
RESULT 1 row updated.
Note: update command does not works for all queries on views.
INPUT SQL>delete from view1 where pname like ‘raju’;
RESULT 1 row deleted.
6. Drop views which you generated.
INPUT SQL>drop view view1;
RESULT View dropped;
INPUT SQL>drop view view2;
RESULT View dropped;
INPUT SQL>drop view xyz;
Writing PL/SQL block for insertion into a table.
To write a PL/SQL block for inserting rows into EMPDET table with the following Calculations:
HRA=50% OF BASIC
DA=20% OF BASIC
PF=7% OF BASIC
NETPAY=BASIC+DA+HRA-PF
INPUT
DECLARE
ENO1 empdet.eno%type;
ENAME1 empdet.name%type;
DEPTNO1 empdet.deptno%type;
BASIC1 empdet.basic%type;
HRA1 empdet.HRA%type;
DA1 empdet.DA%type;
PF1 empdet.pf%type;
NETPAY1 empdet.netpay%type;
BEGIN
ENO1:=&ENO1;
ENAME1:='&ENAME1';
DEPTNO1:=&DEPTNO1;
BASIC1:=&BASIC1;
HRA1:=(BASIC1*50)/100;
DA1:=(BASIC1*20)/100;
PF1:=(BASIC1*7)/100;
NETPAY1:=BASIC1+HRA1+DA1-PF1;
INSERT INTO EMPDET VALUES (ENO1, ENAME1, DEPTNO1, BASIC1, HRA1, DA1, PF1, NETPAY1);
END;
RESULT:
SQL> @BASIC
Enter value for eno1: 104
old 11: ENO1:=&ENO1;
new 11: ENO1:=104;
Enter value for ename1: SRINIVAS REDDY
old 12: ENAME1:='&ENAME1';
new 12: ENAME1:='SRINIVAS REDDY';
Enter value for deptno1: 10
old 13: DEPTNO1:=&DEPTNO1;
new 13: DEPTNO1:=10;
Enter value for basic1: 6000
old 14: BASIC1:=&BASIC1;
new 14: BASIC1:=6000;
PL/SQL procedure successfully completed.
SQL>/
Enter value for eno1: 105
old 11: ENO1:=&ENO1;
new 11: ENO1:=105;
Enter value for ename1: CIRAJ
old 12: ENAME1:='&ENAME1';
new 12: ENAME1:='CIRAJ';
Enter value for deptno1: 10
old 13: DEPTNO1:=&DEPTNO1;
new 13: DEPTNO1:=10;
Enter value for basic1: 6000
old 14: BASIC1:=&BASIC1;
new 14: BASIC1:=6000;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMPDET;
RESULT
ENO NAME DEPTNO BASIC HRA DA PF NETPAY
--------- ------------------------------ --------- --------- --------- --------- --------- -----------------------
101 SANTOSH 10 5000 2500 1000 350 8150
102 SHANKAR 20 5000 2500 1000 350 8150
103 SURESH 20 5500 2750 1100 385 8965
104 SRINIVASA REDDY 10 6000 3000 1200 420 9780
105 CIRAJ 10 6000 3000 1200 420 9780