MySQL Queries II
Data Management: SQL Commands for delete, update, Clauses, Aggregate Functions,
and JOINS statement.
Using Aggregate functions
33.
1. Display the sum and average of the salaries of all the employees.
Select sum(salary), avg(salary) from employee;
;
34.
2. Display the highest and the lowest salaries being paid in department 10.
Select max(salary), min(salary) from employee where dept=10;
35.
3. Display the number of employees working in department 10.
Select count(*) from employee where dept=10;
Using ORDER BY clause
36.
4. Display the details of all the employees in the ascending order of their
salaries.
Select * from employee order by salary;
37.
5. Display the details of all the employees in the descending order of their
names.
Select * from employee order by name desc;
38.
6. Display the details of all the employees in the ascending order of their grades
and within grades in the descending order of their salaries.
Select * from employee order by grade ASC, salary desc;
Using GROUP BY clause
39.
7. Display the total number of employees in each department.
Select count(*) from employee group by dept;
40.
8. Display the highest salary, lowest salary, and average salary of each zone.
Select max(salary), min(salary), avg(salary) from employee group by zone;
41.
9. Display the average age of employees in each department only for those
departments in which average age is more than 30.
Select avg(age) from employee group by dept having avg(age)>30;
Using UPDATE, DELETE, ALTER TABLE
42.
10. Put the grade B for all those whose grade is NULL.
Update employee set grade= ‘B’ where grade is null;
Select * from employee;
43.
11. Increase the salary of all the employees above 30 years of age by 10%.
Update employee set salary= salary + salary*10/100 where age>30;
Select * from employee where age>30;
44.
12. Delete the records of all the employees whose grade is C and salary is below
30000.
Delete from employee where grade= ‘C’ and salary <30000;
Select* from employee;
45.
13. Delete the records of all the employees of department 10 who are above 40
years of age.
Delete from employee where dept=10 and age>40;
Select * from employee;
46.
14. Add another column HireDate of type Date in the Employee table.
Alter table employee add hire_date date;
Select * from employee;
Some More Queries:
CREATE TABLE emp (
empno decimal(4,0) NOT NULL,
ename varchar(10) default NULL,
job varchar(9) default NULL,
mgr decimal(4,0) default NULL,
hiredate date default NULL,
sal decimal(7,2) default NULL,
comm decimal(7,2) default NULL,
deptno decimal(2,0) default NULL
);
CREATE TABLE dept (
deptno decimal(2,0) default NULL,
dname varchar(14) default NULL,
loc varchar(13) default NULL
);
INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-
20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-
22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-
02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-
28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-
01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-
09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-
09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-
17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-
08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');
INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');
[Link] the average salary for all departments with more than 3 people for a job.
SELECT AVG( sal ) FROM emp GROUP BY deptno HAVING
COUNT(job)>3;
[Link] only the jobs with maximum salary greater than or equal to 3000.
SELECT job FROM emp GROUP BY job HAVING MAX(sal)>=3000;
[Link] out number of employees having “Manager” as Job.
SELECT COUNT(*) FROM emp where job='Manager'
[Link] the count of Employees grouped by deptno. (table EMPL)
SELECT COUNT(*) FROM emp GROUP BY deptno;
[Link] the sum of Employees’ salaries grouped by department. (table EMPL)
SELECT sum(sal) FROM emp GROUP BY deptno;
[Link] the maximum salary of Employee grouped by their department number.
SELECT MAX(sal) FROM emp GROUP BY deptno;
[Link] the department names and the number of their Employees.
SELECT COUNT(*) , [Link] FROM emp
INNER JOIN dept ON [Link] = [Link]
GROUP BY [Link]
[Link] the Employee names and the names of their departments.
SELECT ename, [Link] FROM emp
INNER JOIN dept ON [Link] = [Link];