Welcome to Database Laboratory
Welcome to Database Laboratory
CREATE TABLE employees (
emp_no INT PRIMARY KEY,
emp_name VARCHAR(50),
designation VARCHAR(50),
designation_no INT,
sal DECIMAL(10, 2),
commission DECIMAL(10, 2)
);
// Insert records
INSERT INTO employees (emp_no, emp_name, designation, designation_no, sal, commission) VALUES
(1, 'XYZ', 'Manager', NULL, 5000.00, 1000.00),
(2, 'PQR', 'Developer', 1, 4000.00, NULL),
(3, 'ABC', 'Salesperson', 2, 3000.00, 500.00),
(10, 'XYZ', 'Manager', NULL, 5000.00, 1000.00),
(12, 'XYZ', 'Manager', NULL, 5000.00, 1000.00),
(13, 'XYZ', 'Manager', NULL, 5000.00, 1000.00),
(101, 'John Doe', 'Manager', 1, 75000.00, 5000.00),
(102, 'Jane Smith', 'Executive', 2, 60000.00, 3000.00),
(103, 'Richard Roe', 'HR Officer', 3, 50000.00, 2000.00),
(104, 'Mary Major', 'Accountant', 4, 55000.00, 2500.00),
(105, 'James Junior', 'IT Specialist', 5, 70000.00, 4000.00),
(106, 'ABC', 'IT Specialist', 5, 55555.00, 333.00),
(107, 'PQR', 'IT Specialist', 5, 755555.00, 7333.00);
+--------+--------------+---------------+----------------+-----------+------------+
| emp_no | emp_name | designation | designation_no | sal | commission |
+--------+--------------+---------------+----------------+-----------+------------+
| 1 | XYZ | Manager | NULL | 5000.00 | 1000.00 |
| 2 | PQR | Developer | 1 | 4000.00 | NULL |
| 3 | ABC | Salesperson | 2 | 3000.00 | 500.00 |
| 10 | XYZ | Manager | NULL | 5000.00 | 1000.00 |
| 12 | XYZ | Manager | NULL | 5000.00 | 1000.00 |
| 13 | XYZ | Manager | NULL | 5000.00 | 1000.00 |
| 101 | John Doe | Manager | 1 | 75000.00 | 5000.00 |
| 102 | Jane Smith | Executive | 2 | 60000.00 | 3000.00 |
| 103 | Richard Roe | HR Officer | 3 | 50000.00 | 2000.00 |
| 104 | Mary Major | Accountant | 4 | 55000.00 | 2500.00 |
| 105 | James Junior | IT Specialist | 5 | 70000.00 | 4000.00 |
| 106 | ABC | IT Specialist | 5 | 55555.00 | 333.00 |
| 107 | PQR | IT Specialist | 5 | 755555.00 | 7333.00 |
+--------+--------------+---------------+----------------+-----------+------------+
select count(emp_name) as count,emp_name from employee group by emp_name;
OUTPUT:
+-------+--------------+
| count | emp_name |
+-------+--------------+
| 4 | XYZ |
| 2 | PQR |
| 2 | ABC |
| 1 | John Doe |
| 1 | Jane Smith |
| 1 | Richard Roe |
| 1 | Mary Major |
| 1 | James Junior |
+-------+--------------+
8 rows in set (0.00 sec)
select count(emp_name) as count,emp_name from employee group by emp_name;
OUTPUT:
+-------+--------------+
select avg(sal) as avg from employee where designation_no = 5;
+---------------+
| avg |
+---------------+
| 293703.333333 |
+---------------+
1 row in set (0.00 sec)
mysql> select avg(sal) as avg,emp_name from employee where designation_no = 5 group by emp_name;
OUTPUT:
+---------------+--------------+
| avg | emp_name |
+---------------+--------------+
| 70000.000000 | James Junior |
| 55555.000000 | ABC |
| 755555.000000 | PQR |
+---------------+--------------+
3 rows in set (0.00 sec)