PROGRAM - 3
QUERIES USING AGGREGATE FUNCTIONS(COUNT,AVG,MIN,MAX,SUM), GROUP BY, ORDER BY.
Employee(E_id, E_name, Age, Salary)
1. Create Employee table containing all Records E_id, E_name, Age, Salary.
mysql> CREATE TABLE employees (
e_id int(10),
ename char(10),
age int(10),
salary int(10)
);
Query OK, 0 rows affected, 3 warnings (1.05 sec)
mysql> DESC employees;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| e_id | int | YES | | NULL | |
| ename | char(10) | YES | | NULL | |
| age | int | YES | | NULL | |
| salary | int | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> INSERT INTO employees VALUES('1','ram',23,10000);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO employees VALUES('2','raj',25,20000);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO employees VALUES('3','roy',35,30000);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO employees VALUES('4','smith',28,40000);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO employees VALUES('5','abhi',29,50000);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM employees;
+------+-------+------+--------+
| e_id | ename | age | salary |
+------+-------+------+--------+
| 1 | ram | 23 | 10000 |
| 2 | raj | 25 | 20000 |
| 3 | roy | 35 | 30000 |
| 4 | smith | 28 | 40000 |
| 5 | abhi | 29 | 50000 |
+------+-------+------+--------+
5 rows in set (0.00 sec)
2. Count number of employee names from employee table
mysql> SELECT COUNT(ename) FROM employees;
+--------------+
| count(ename) |
+--------------+
| 5 |
+--------------+
1 row in set (0.02 sec)
3. Find the Maximum age from employee table.
mysql> SELECT MAX(salary) FROM employees;
+-------------+
| max(salary) |
+-------------+
| 50000 |
+-------------+
1 row in set (0.03 sec)
4. Find the Minimum age from employee table.
mysql> SELECT MIN(age) FROM employees;
+----------+
| min(age) |
+----------+
| 23 |
+----------+
1 row in set (0.00 sec)
5. Find salaries of employee in Ascending Order.
mysql> SELECT salary FROM employees ORDER BY salary ASC;
+--------+
| salary |
+--------+
| 10000 |
| 20000 |
| 30000 |
| 40000 |
| 50000 |
+--------+
5 rows in set (0.00 sec)
6. Find grouped salaries of employees.
mysql> UPDATE EMPLOYEES SET E_ID='1' WHERE ENAME='RAJ';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employees;
+------+-------+------+--------+
| e_id | ename | age | salary |
+------+-------+------+--------+
| 1 | ram | 23 | 10000 |
| 1 | raj | 25 | 20000 |
| 3 | roy | 35 | 30000 |
| 4 | smith | 28 | 40000 |
| 5 | abhi | 29 | 50000 |
+------+-------+------+--------+
5 rows in set (0.00 sec)
mysql> SELECT e_id, AVG(salary), SUM(salary) FROM employees GROUP BY e_id;
+------+-------------+-------------+
| e_id | avg(salary) | sum(salary) |
+------+-------------+-------------+
| 1 | 15000.0000 | 30000 |
| 3 | 30000.0000 | 30000 |
| 4 | 40000.0000 | 40000 |
| 5 | 50000.0000 | 50000 |
+------+-------------+-------------+
4 rows in set (0.00 sec)