LAB 5
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;