AGGREGATE FUN/GROUP BY SOLVED EXAMPLE

Q1) Conside The following table

EID	FNAME	LNAME	EMAIL	PHONENO	HIREDATE	JOB_ID	SALARY	COMMISSION	MANAGER_ID	DEPTID
100	Steven	King	SKING	9811234567	17-Jun-87	PRES	24000	null	null	90
101	Neena	Kochhar	NKOCHHAR	9711124568	21-Sep-89	VP	17000	null	100	90
102	Lex	De Haan	LDEHAAN	9345124569	13-Jan-93	VP	17000	null	100	90
103	Alexander	Hunold	AHUNOLD	9823424567	3-Jan-90	IT	9000	null	102	60

·         displays number of employees work in each department

SELECT COUNT(*) FROM EMPLOYEE GROUP BY DEPTID;

·         DISPLAY total salary paid to employees work in each department.

SELECT SUM(SALARY),DEPTID FROM EMPLOYEE GROUP BY DEPTID;

·         displays number of employees, total salary paid to employees work in each department

SELECT COUNT(EID),SUM(SALARY),DEPTID FROM EMPLOYEE GROUP BY DEPTID;

·         displays the total salary paid to employees IN EACH DEPARTMENT department_id AND WITHIN IT FOR EACH RESPECTIVE JOB.

SELECT SUM(SALARY),JOBID,DEPTID  FROM  EMPLOYEE GROUP BY JOBID,DEPTID;

·         displays the department id, number of employees of those groups that have more than 2                employees

SELECT COUNT(EID),DEPTID FROM EMPLOYEE GROUP BY DEPTID HAVING COUNT(EID)>2;

·         Calculate the number of employee in all departments except DEPTNO 30, show ONLY departments with more than ten employees, and sort by DEPTNO

         SELECT COUNT(EID), DEPTID FROM EMPLOYEE WHERE DEPTID<>30 GROUP BY DEPTID ORDER BY DEPTID;

·         Display only those Departments where Average salary is greater than 80.

SELECT DEPTID, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPTID HAVING AVG(SALARY)>80;

Display the difference of highest and lowest salary of each department having maximum  

    salary>400.

SELECT MAX(SALARY)-MIN(SALARY) ,DEPTID FROM EMPLOYEE GROUP BY DEPTID HAVING MAX(SALARY)>400;

Display the jobs where the number of employees is less than 3.

SELECT COUNT(EID) ,JOBID FROM EMPLOYEE GROUP BY JOBID HAVING COUNT(EID)<3;

     

Display average sal of each department for the jobs ‘’ VP’ only.

SELECT AVG(SALARY) ,DEPTID FROM EMPLOYEE WHERE JOBID=”VP” GROUP BY DEPTID;

DISPLAY NAME OF EMPLOYEES WHOSE NAME CONATINS EITHER “TT” OR “LL”

SELECT * FROM EMPLOYEE WHERE ENAME LIKE “%TT%” OR ENAME LIKE “%LL%”;

INSERT A RECORD WITH SUITABLE DATA IN THE TABLE HAVING SYSTEM DATE AS THE HIRE DATE.

INSERT INTO EMPLOYEE VALUES(106,”RAHUL””SOOD”,RAHUL@GMSAIL.COM, 9847754555,SYSDATE(),”CLERK”,NULL,102,80);

DISPLAY ALL THE EMPLOYEES WHO WERE HIRED IN YEAR 1995

SELECT * FROM EMPLOYEE WHERE YEAR(HIREDATE)=1995;

FIND ALL THE EMPLOYEES WHO HAVE NO MANAGER

SELECT * FROM EMPLOYEE WHERE MGRID IS NULL;

FIND OUT NUMBER OF EMPLOYEES HAVING “ VP” AS JOB

SELECT  COUNT(EID) FROM EMPLOYEE GROUP BY JOBID HAVING JOBID=”VP”;

LIST MINIMUM AND MAXIMUM SALARY FOR EACH JOB TYPE

SELECT MIN(SALARY),MAX(SALARY) FROM EMPLOYEE GROUP BY JOBID;

CLASS XI SQL TEST

Answer the following questions:
(a)	Compare DDL and DML statements of SQL.
(b)	Swati needs to remove all the rows from SALES table to release the storage space. But she does not want to remove the table structure. Which statement should she use?
(c)	Meena uses a EMP table with following columns: NAME ,SAL,ID,DNAME She needs to display names of employees who have not been assigned any department or have been assigned “pathology” department. Pathology course’s names end with “Pathology”. She wrote the following query: SELECT NAME,SAL FROM EMP,COURSE WHERE DNAME = NULL OR DNAME = “%pathology”; But the query is not producing result. Identify the problem.
(d)	What is the importance of primary key in a table? Explain with example.
A table ACCOUNT in a database has 6 columns and 60 rows. The DBA has added 3 more columns and 50 more rows to the table. But the table has about 15 records where balance is null. What is the degree and cardinality of this table now ?

·         Consider the following tables PRODUCT and answer (b) and (c) part of this question
Relation :PRODUCT
PCODE PNAME  COMPANY    PRICE    STOCK    MANUFACTURE    WARRANTY
P001  TV      BPL       10000     200     12-JAN-2008         3
P002  TV      SONY      12000     150     23-MAR-2007         4
P003  PC      LENOVO    39000     100     09-APR-2008         2
P004  PC      COMPAQ    38000     120     20-JUN-2009         2
P005  HANDYCAM  SONY    18000     250     23-MAR-2007         3 


Write MySQL commands for following Statements
i.To show details of all the PC with stock more than 110.       
ii.To list the company which gives warranty for more than 2 years


iii.To show the PRODUCT name which are within warranty as on date.
iv) Show the products whose warranty is over

Q1)Consider the table Hospital given below.

No	Name	Age	DEPARTMENT	DateOfAdm	Charges	Sex
1	Sandeep	64	Surgery	23/02/98	300	M
2	Ravina	24	Orthopedic	20/01/98	200	F
3	Karan	45	Orthopedic	10/02/98	200	M
4	Tarun	12	Surgery	01/01/98	300	F
5	Zubin	36	ENT	12/01/98	250	M
6	Ketaki	16	ENT	12/02/98	300	F
7	Ankita	29	Cardiology	20/02/98	800	F
8	Zareen	45	Gynecology	22/02/98	Null	F
9	Ankit	19	Cardiology	13/01/98	800	M
10	Shailya	31	Medicine	19/02/97	400	F

—  To show all information about the patients of cardiology department.

—  To list the names of female patients who are in orthopaedic department.

—  To display Patient’s name, charges, Age for  male and female patients.

—  Increase the charges of male patient in ENT department by 3%.

—  Add another column email_id with suitable data type.

—   Delete the records of all female patients in Surgery department.

—  Display a report listing name, age, charges and amount of charges including VAT as 2%  on charges name the column as total charges and keep the data in ascending order of name.

—    Display department name where charges are null.

Insert new row with following values dept="ENT"  patient name="mohit"

—   Display the details of all the patients who are hospitalised in 1998.

—   Display the charges of various departments .A charge amount should appear only once.  

QGive output:

SELECT ASCII('Q')
FROM DUAL;

SELECT CONCAT(name, ' is a good boy') "Result"
FROM hospital
WHERE dept=’ENT’;

SELECT SUBSTR('SPIDERMAN',7,3);

SELECT instr('oralce apps','app');

SELECT round(123.67,-1) FROM DUAL;

SELECT trunc(123.67) FROM DUAL;

Q) Extract ‘my’ from ‘Mysql’ string.

q) Extract month from date(12-03-2012’);

q) select lower(substr(“MYSQLCLASSXI”,3,4));

Q)Select length(lower(substr(“CLASS XI”,-2,2)));