Welcome to Database Laboratory
Welcome to Database Laboratory
Having Clause
Create Table
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);
Output :
+--------+--------------+---------------+----------------+-----------+------------+
| 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 SAL from employee having sal > 50000;
Output :
Output :
+-----------+
| SAL |
+-----------+
| 75000.00 |
| 60000.00 |
| 55000.00 |
| 70000.00 |
| 55555.00 |
| 755555.00 |
+-----------+
6 rows in set (0.00 sec)
Output :
select sal,emp_name from employee having sal > (select avg(sal) from employee);
Output :
+-----------+----------+
| sal | emp_name |
+-----------+----------+
| 755555.00 | PQR |
+-----------+----------+
select sal,emp_name,designation from employee having sal > (select avg(sal) from employee);
Output :
+-----------+----------+---------------+
| sal | emp_name | designation |
+-----------+----------+---------------+
| 755555.00 | PQR | IT Specialist |
+-----------+----------+---------------+
1 row in set (0.00 sec)
select sal,emp_name,designation from employee having sal > 50000;
Output :
+-----------+--------------+---------------+
| sal | emp_name | designation |
+-----------+--------------+---------------+
| 75000.00 | John Doe | Manager |
| 60000.00 | Jane Smith | Executive |
| 55000.00 | Mary Major | Accountant |
| 70000.00 | James Junior | IT Specialist |
| 55555.00 | ABC | IT Specialist |
| 755555.00 | PQR | IT Specialist |
+-----------+--------------+---------------+