-- ============================================
-- DBMS PRACTICAL
-- EMPLOYEE AND DEPARTMENT DATABASE
-- ============================================
-- 1. CREATE DEPARTMENT TABLE
CREATE TABLE DEPARTMENT (
Dno INTEGER PRIMARY KEY,
Dname VARCHAR(50),
Location VARCHAR(50) DEFAULT 'New Delhi'
);
-- 2. CREATE EMPLOYEE TABLE
CREATE TABLE EMPLOYEE (
Eno CHAR(4) PRIMARY KEY,
Ename VARCHAR(50) NOT NULL,
Job_type VARCHAR(50) NOT NULL,
Manager CHAR(4),
Hire_date DATE NOT NULL,
Dno INTEGER,
Commission DECIMAL(10,2),
Salary DECIMAL(7,2) NOT NULL,
FOREIGN KEY (Manager) REFERENCES EMPLOYEE(Eno),
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dno)
);
-- ============================================
-- 3. INSERT DEPARTMENT DATA
-- ============================================
INSERT INTO DEPARTMENT VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- ============================================
-- 4. INSERT EMPLOYEE DATA
-- ============================================
INSERT INTO EMPLOYEE VALUES
-- PRESIDENT
('7839', 'KING', 'PRESIDENT', NULL,
'1981-11-17', 10, NULL, 5000.00),
-- MANAGERS
('7782', 'CLARK', 'MANAGER', '7839',
'1981-06-09', 10, NULL, 2450.00),
('7566', 'JONES', 'MANAGER', '7839',
'1981-04-02', 20, NULL, 2975.00),
('7698', 'BLAKE', 'MANAGER', '7839',
'1981-05-01', 30, NULL, 2850.00),
-- DEPARTMENT 10 : ACCOUNTING
('7934', 'MILLER', 'CLERK', '7782',
'1982-01-23', 10, NULL, 1300.00),
-- DEPARTMENT 20 : RESEARCH
('7788', 'SCOTT', 'ANALYST', '7566',
'1982-12-09', 20, NULL, 3000.00),
('7902', 'FORD', 'ANALYST', '7566',
'1981-12-03', 20, NULL, 3000.00),
('7369', 'SMITH', 'CLERK', '7902',
'1980-12-17', 20, NULL, 800.00),
('7876', 'ADAMS', 'CLERK', '7788',
'1983-01-12', 20, NULL, 1100.00),
-- DEPARTMENT 30 : SALES
('7499', 'ALLEN', 'SALESMAN', '7698',
'1981-02-20', 30, 300.00, 1600.00),
('7521', 'WARD', 'SALESMAN', '7698',
'1981-02-22', 30, 500.00, 1250.00),
('7654', 'MARTIN', 'SALESMAN', '7698',
'1981-09-28', 30, 1400.00, 1250.00),
('7844', 'TURNER', 'SALESMAN', '7698',
'1981-09-08', 30, 0.00, 1500.00),
('7900', 'JAMES', 'CLERK', '7698',
'1981-12-03', 30, NULL, 950.00);
-- ============================================
-- 5. DISPLAY DEPARTMENT DATA
-- ============================================
SELECT * FROM DEPARTMENT;
-- ============================================
-- 6. DISPLAY EMPLOYEE DATA
-- ============================================
SELECT * FROM EMPLOYEE;
-- ============================================
-- QUERY 1
-- Display Employee Number, Name, Job & Hire Date
-- ============================================
SELECT Eno, Ename, Job_type, Hire_date
FROM EMPLOYEE;
## Experiment 1
**Aim:**
To display Employee Number, Employee Name, Job Type, and Hire Date of all employees.
**SQL Program:**
```sql
SELECT Eno, Ename, Job_type, Hire_date
FROM EMPLOYEE;
```
**Output:**
| Eno | Ename | Job_type | Hire_date |
| ---- | ------ | --------- | ---------- |
| 7839 | KING | PRESIDENT | 1981-11-17 |
| 7782 | CLARK | MANAGER | 1981-06-09 |
| 7566 | JONES | MANAGER | 1981-04-02 |
| 7698 | BLAKE | MANAGER | 1981-05-01 |
| 7934 | MILLER | CLERK | 1982-01-23 |
| 7788 | SCOTT | ANALYST | 1982-12-09 |
| 7902 | FORD | ANALYST | 1981-12-03 |
| 7369 | SMITH | CLERK | 1980-12-17 |
| 7876 | ADAMS | CLERK | 1983-01-12 |
| 7499 | ALLEN | SALESMAN | 1981-02-20 |
| 7521 | WARD | SALESMAN | 1981-02-22 |
| 7654 | MARTIN | SALESMAN | 1981-09-28 |
| 7844 | TURNER | SALESMAN | 1981-09-08 |
| 7900 | JAMES | CLERK | 1981-12-03 |
**Result:**
The Employee Number, Employee Name, Job Type, and Hire Date of all employees were
displayed successfully.
## Experiment 2
**Aim:**
To display all the **unique job types** from the `EMPLOYEE` table.
**SQL Program:**
```sql
SELECT DISTINCT Job_type
FROM EMPLOYEE;
```
**Output:**
| Job_type |
| --------- |
| PRESIDENT |
| MANAGER |
| CLERK |
| ANALYST |
| SALESMAN |
**Result:**
The unique job types of employees were displayed successfully.
## Experiment 3
**Aim:**
To display the Employee Name and Job Type concatenated and separated by a comma.
**SQL Program:**
```sql
SELECT CONCAT(Ename, ', ', Job_type) AS Employee_Job
FROM EMPLOYEE;
```
**Output:**
| Employee_Job |
| ---------------- |
| KING, PRESIDENT |
| CLARK, MANAGER |
| JONES, MANAGER |
| BLAKE, MANAGER |
| MILLER, CLERK |
| SCOTT, ANALYST |
| FORD, ANALYST |
| SMITH, CLERK |
| ADAMS, CLERK |
| ALLEN, SALESMAN |
| WARD, SALESMAN |
| MARTIN, SALESMAN |
| TURNER, SALESMAN |
| JAMES, CLERK |
**Result:**
The Employee Name and Job Type were concatenated and displayed successfully.
## Experiment 4
**Aim:**
To display all employee data in a single column, separated by commas, and name the column
`THE_OUTPUT`.
**SQL Program:**
```sql
SELECT CONCAT(
Eno, ', ', Ename, ', ', Job_type, ', ',
IFNULL(Manager, 'NULL'), ', ', Hire_date, ', ',
Dno, ', ', IFNULL(Commission, 'NULL'), ', ', Salary
) AS THE_OUTPUT
FROM EMPLOYEE;
```
**Output:**
| THE_OUTPUT |
| -------------------------------------------------------------- |
| 7839, KING, PRESIDENT, NULL, 1981-11-17, 10, NULL, 5000.00 |
| 7782, CLARK, MANAGER, 7839, 1981-06-09, 10, NULL, 2450.00 |
| 7566, JONES, MANAGER, 7839, 1981-04-02, 20, NULL, 2975.00 |
| 7698, BLAKE, MANAGER, 7839, 1981-05-01, 30, NULL, 2850.00 |
| 7934, MILLER, CLERK, 7782, 1982-01-23, 10, NULL, 1300.00 |
| 7788, SCOTT, ANALYST, 7566, 1982-12-09, 20, NULL, 3000.00 |
| 7902, FORD, ANALYST, 7566, 1981-12-03, 20, NULL, 3000.00 |
| 7369, SMITH, CLERK, 7902, 1980-12-17, 20, NULL, 800.00 |
| 7876, ADAMS, CLERK, 7788, 1983-01-12, 20, NULL, 1100.00 |
| 7499, ALLEN, SALESMAN, 7698, 1981-02-20, 30, 300.00, 1600.00 |
| 7521, WARD, SALESMAN, 7698, 1981-02-22, 30, 500.00, 1250.00 |
| 7654, MARTIN, SALESMAN, 7698, 1981-09-28, 30, 1400.00, 1250.00 |
| 7844, TURNER, SALESMAN, 7698, 1981-09-08, 30, 0.00, 1500.00 |
| 7900, JAMES, CLERK, 7698, 1981-12-03, 30, NULL, 950.00 |
**Result:**
All employee details were displayed in a single column separated by commas.
Yes. From now on, I’ll include the **Output Table** with every experiment.
## Experiment 5
**Aim:**
To display the Employee Name and Salary of all employees earning more than 2850.
### SQL Program
```sql
SELECT Ename, Salary
FROM EMPLOYEE
WHERE Salary > 2850;
```
### Output Table
| Ename | Salary |
| ----- | ------: |
| KING | 5000.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
**4 rows returned**
### Result
The names and salaries of employees earning more than 2850 were displayed successfully.
## Experiment 6
**Aim:**
To display the **Employee Name and Department Number** for employee number
**7900**.
### SQL Program
```sql
SELECT Ename, Dno
FROM EMPLOYEE
WHERE Eno = '7900';
```
### Output Table
| Ename | Dno |
| ----- | --: |
| JAMES | 30 |
**1 row returned**
### Result
The Employee Name and Department Number for employee number 7900 were displayed
successfully.
## Experiment 7
**Aim:**
To display Employee Name and Salary for all employees whose salary is **not in the range of
$1500 and $2850**.
### SQL Program
```sql
SELECT Ename, Salary
FROM EMPLOYEE
WHERE Salary NOT BETWEEN 1500 AND 2850;
```
### Output Table
| Ename | Salary |
| ------ | ------: |
| KING | 5000.00 |
| JONES | 2975.00 |
| MILLER | 1300.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| SMITH | 800.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| JAMES | 950.00 |
**Result:**
The employees whose salaries are not in the range of $1500 to $2850 were displayed
successfully.
## Experiment 8
**Aim:**
To display the **Employee Name and Department Number** of all employees in
departments **10 and 30**, arranged alphabetically by Employee Name.
### SQL Program
```sql
SELECT Ename, Dno
FROM EMPLOYEE
WHERE Dno IN (10, 30)
ORDER BY Ename;
```
### Output Table
| Ename | Dno |
| ------ | --: |
| ALLEN | 30 |
| BLAKE | 30 |
| CLARK | 10 |
| JAMES | 30 |
| KING | 10 |
| MARTIN | 30 |
| MILLER | 10 |
| TURNER | 30 |
| WARD | 30 |
**9 rows returned**
### Result
The names and department numbers of employees working in departments 10 and 30 were
displayed in alphabetical order.
## Experiment 9
**Aim:**
To display the **Employee Name and Hire Date** of all employees who were hired in
**1981**.
### SQL Program
```sql
SELECT Ename, Hire_date
FROM EMPLOYEE
WHERE YEAR(Hire_date) = 1981;
```
### Output Table
| Ename | Hire_date |
| ------ | ---------- |
| KING | 1981-11-17 |
| CLARK | 1981-06-09 |
| JONES | 1981-04-02 |
| BLAKE | 1981-05-01 |
| FORD | 1981-12-03 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| MARTIN | 1981-09-28 |
| TURNER | 1981-09-08 |
| JAMES | 1981-12-03 |
**10 rows returned**
### Result
The names and hire dates of all employees hired in 1981 were displayed successfully.
## Experiment 10
**Aim:**
To display the **Name and Job** of all employees who do not have a current manager.
### SQL Program
```sql
SELECT Ename, Job_type
FROM EMPLOYEE
WHERE Manager IS NULL;
```
### Output Table
| Ename | Job_type |
| ----- | --------- |
| KING | PRESIDENT |
**1 row returned**
### Result
The name and job of employees who do not have a current manager were displayed
successfully.
## Experiment 11
**Aim:**
To display the Name, Salary and Commission of all employees who earn commission.
### SQL Program
```sql
SELECT Ename, Salary, Commission
FROM EMPLOYEE
WHERE Commission IS NOT NULL
AND Commission > 0;
```
### Output Table
| Ename | Salary | Commission |
| ------ | ------: | ---------: |
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| MARTIN | 1250.00 | 1400.00 |
**3 rows returned**
**Result:**
The name, salary and commission of employees who earn commission were displayed
successfully.
---
## Experiment 12
**Aim:**
To sort the employee data in descending order of Salary and Commission.
### SQL Program
```sql
SELECT Ename, Salary, Commission
FROM EMPLOYEE
ORDER BY Salary DESC, Commission DESC;
```
### Output Table
| Ename | Salary | Commission |
| ------ | ------: | ---------: |
| KING | 5000.00 | NULL |
| SCOTT | 3000.00 | NULL |
| FORD | 3000.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| ALLEN | 1600.00 | 300.00 |
| TURNER | 1500.00 | 0.00 |
| MILLER | 1300.00 | NULL |
| MARTIN | 1250.00 | 1400.00 |
| WARD | 1250.00 | 500.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| SMITH | 800.00 | NULL |
**14 rows returned**
**Result:**
The employee data were sorted in descending order of Salary and Commission.
---
## Experiment 13
**Aim:**
To display the names of all employees whose **third letter is 'A'**.
### SQL Program
```sql
SELECT Ename
FROM EMPLOYEE
WHERE Ename LIKE '__A%';
```
Here, `__` means **any two characters**, so `A` must be the **third character**.
### Output Table
| Ename |
| ----- |
| BLAKE |
| CLARK |
| ADAMS |
**3 rows returned**
**Result:**
The names of employees having `A` as the third letter were displayed successfully.
Below are **Experiments 14–20** using your fixed `EMPLOYEE` table and **MySQL
syntax**.
## Experiment 14
**Aim:**
To display the names of employees who have either two `R`s or two `A`s in their name and
are either in Department 30 or have Manager Employee No. 7788.
### SQL Program
```sql
SELECT Ename
FROM EMPLOYEE
WHERE (Ename LIKE '%R%R%' OR Ename LIKE '%A%A%')
AND (Dno = 30 OR Manager = '7788');
```
### Output Table
With our current dataset:
| Ename |
| ----------- |
| — No rows — |
**Result:**
No employee satisfies all the given conditions in the current data.
---
## Experiment 15
**Aim:**
To display Name, Salary and Commission of employees whose commission is greater than
their salary increased by 5%.
> Your question appears to contain a typo: **“Commission Amount is 14 greater”**.
Interpreting it as “Commission Amount is greater than Salary increased by 5%.”
### SQL Program
```sql
SELECT Ename, Salary, Commission
FROM EMPLOYEE
WHERE Commission > Salary * 1.05;
```
### Output Table
| Ename | Salary | Commission |
| ------ | ------: | ---------: |
| MARTIN | 1250.00 | 1400.00 |
**Result:**
The employee whose commission is greater than the salary increased by 5% was displayed
successfully.
---
## Experiment 16
**Aim:**
To display the current date.
### SQL Program
```sql
SELECT CURDATE() AS Current_Date;
```
### Output Table
If executed on **02 August 2026**:
| Current_Date |
| ------------ |
| 2026-08-02 |
**Result:**
The current date was displayed successfully.
---
## Experiment 17
**Aim:**
To display Employee Name, Hire Date and Salary Review Date, where the review date is the
**first Monday after six months of employment**.
### SQL Program
```sql
SELECT Ename,
Hire_date,
DATE_ADD(
DATE_ADD(Hire_date, INTERVAL 6 MONTH),
INTERVAL (7 - WEEKDAY(DATE_ADD(Hire_date, INTERVAL 6 MONTH))) % 7 DAY
) AS Review_Date
FROM EMPLOYEE;
```
### Output Table
| Ename | Hire_date | Review_Date |
| ------ | ---------- | ----------- |
| KING | 1981-11-17 | 1982-05-17 |
| CLARK | 1981-06-09 | 1981-12-14 |
| JONES | 1981-04-02 | 1981-10-05 |
| BLAKE | 1981-05-01 | 1981-11-02 |
| MILLER | 1982-01-23 | 1982-07-26 |
| SCOTT | 1982-12-09 | 1983-06-13 |
| FORD | 1981-12-03 | 1982-06-07 |
| SMITH | 1980-12-17 | 1981-06-22 |
| ADAMS | 1983-01-12 | 1983-07-18 |
| ALLEN | 1981-02-20 | 1981-08-24 |
| WARD | 1981-02-22 | 1981-08-24 |
| MARTIN | 1981-09-28 | 1982-03-29 |
| TURNER | 1981-09-08 | 1982-03-08 |
| JAMES | 1981-12-03 | 1982-06-07 |
**Result:**
The salary review date for each employee was calculated and displayed successfully.
---
## Experiment 18
**Aim:**
To display Employee Name and calculate the number of months between today and the
employee's hire date.
### SQL Program
```sql
SELECT Ename,
TIMESTAMPDIFF(MONTH, Hire_date, CURDATE()) AS Months_Worked
FROM EMPLOYEE;
```
### Output Table
Since `CURDATE()` changes every day, the output also changes. On **02 August 2026**, it
will be approximately:
| Ename | Months_Worked |
| ------ | ------------: |
| KING | 536 |
| CLARK | 541 |
| JONES | 543 |
| BLAKE | 543 |
| MILLER | 534 |
| SCOTT | 523 |
| FORD | 535 |
| SMITH | 547 |
| ADAMS | 522 |
| ALLEN | 545 |
| WARD | 545 |
| MARTIN | 538 |
| TURNER | 538 |
| JAMES | 535 |
**Result:**
The number of months each employee has worked was calculated and displayed.
---
## Experiment 19
**Aim:**
To display each employee's name, current monthly salary and desired salary equal to three
times the current salary, labeled as `Dream Salary`.
### SQL Program
```sql
SELECT CONCAT(
Ename, ' earns ', Salary,
' monthly but wants ', Salary * 3
) AS `Dream Salary`
FROM EMPLOYEE;
```
### Output Table
| Dream Salary |
| ---------------------------------------------- |
| KING earns 5000.00 monthly but wants 15000.00 |
| CLARK earns 2450.00 monthly but wants 7350.00 |
| JONES earns 2975.00 monthly but wants 8925.00 |
| BLAKE earns 2850.00 monthly but wants 8550.00 |
| MILLER earns 1300.00 monthly but wants 3900.00 |
| SCOTT earns 3000.00 monthly but wants 9000.00 |
| FORD earns 3000.00 monthly but wants 9000.00 |
| SMITH earns 800.00 monthly but wants 2400.00 |
| ADAMS earns 1100.00 monthly but wants 3300.00 |
| ALLEN earns 1600.00 monthly but wants 4800.00 |
| WARD earns 1250.00 monthly but wants 3750.00 |
| MARTIN earns 1250.00 monthly but wants 3750.00 |
| TURNER earns 1500.00 monthly but wants 4500.00 |
| JAMES earns 950.00 monthly but wants 2850.00 |
**Result:**
The current and dream salary of each employee were displayed successfully.
---
## Experiment 20
**Aim:**
To display employee names with the first letter capitalized and remaining letters in
lowercase, along with the length of the name, for employees whose names start with `J`, `A`,
or `M`.
### SQL Program
```sql
SELECT CONCAT(
UPPER(LEFT(Ename, 1)),
LOWER(SUBSTRING(Ename, 2))
) AS Name,
LENGTH(Ename) AS Length
FROM EMPLOYEE
WHERE Ename LIKE 'J%'
OR Ename LIKE 'A%'
OR Ename LIKE 'M%';
```
### Output Table
| Name | Length |
| ------ | -----: |
| Jones | 5|
| Miller | 6|
| Adams | 5|
| Allen | 5|
| Martin | 6|
| James | 5|
**Result:**
The formatted employee names and their lengths were displayed successfully.
## Experiment 21
**Aim:**
To display Employee Name, Hire Date, and the Day of the Week on which the employee
started.
### SQL Program
```sql
SELECT Ename, Hire_date,
DAYNAME(Hire_date) AS Day
FROM EMPLOYEE;
```
### Output Table
| Ename | Hire_date | Day |
| ------ | ---------- | --------- |
| KING | 1981-11-17 | Tuesday |
| CLARK | 1981-06-09 | Tuesday |
| JONES | 1981-04-02 | Thursday |
| BLAKE | 1981-05-01 | Friday |
| MILLER | 1982-01-23 | Saturday |
| SCOTT | 1982-12-09 | Thursday |
| FORD | 1981-12-03 | Thursday |
| SMITH | 1980-12-17 | Wednesday |
| ADAMS | 1983-01-12 | Wednesday |
| ALLEN | 1981-02-20 | Friday |
| WARD | 1981-02-22 | Sunday |
| MARTIN | 1981-09-28 | Monday |
| TURNER | 1981-09-08 | Tuesday |
| JAMES | 1981-12-03 | Thursday |
**Result:**
The employee names, hire dates, and days of joining were displayed successfully.
---
## Experiment 22
**Aim:**
To display Employee Name, Department Name, and Department Number for all employees.
### SQL Program
```sql
SELECT [Link], [Link], [Link]
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON [Link] = [Link];
```
### Output Table
| Ename | Dname | Dno |
| ------ | ---------- | --: |
| KING | ACCOUNTING | 10 |
| CLARK | ACCOUNTING | 10 |
| JONES | RESEARCH | 20 |
| BLAKE | SALES | 30 |
| MILLER | ACCOUNTING | 10 |
| SCOTT | RESEARCH | 20 |
| FORD | RESEARCH | 20 |
| SMITH | RESEARCH | 20 |
| ADAMS | RESEARCH | 20 |
| ALLEN | SALES | 30 |
| WARD | SALES | 30 |
| MARTIN | SALES | 30 |
| TURNER | SALES | 30 |
| JAMES | SALES | 30 |
**Result:**
The employee names with their department names and department numbers were
displayed successfully.
---
## Experiment 23
**Aim:**
To display a unique listing of all jobs in Department No. 30.
### SQL Program
```sql
SELECT DISTINCT Job_type
FROM EMPLOYEE
WHERE Dno = 30;
```
### Output Table
| Job_type |
| -------- |
| MANAGER |
| SALESMAN |
| CLERK |
**Result:**
The unique job types in Department No. 30 were displayed successfully.
---
## Experiment 24
**Aim:**
To display Employee Name and Department Name for all employees who have an `A` in their
name.
### SQL Program
```sql
SELECT [Link], [Link]
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON [Link] = [Link]
WHERE [Link] LIKE '%A%';
```
### Output Table
| Ename | Dname |
| ------ | ---------- |
| CLARK | ACCOUNTING |
| BLAKE | SALES |
| ADAMS | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| JAMES | SALES |
**Result:**
The employee and department names of employees having `A` in their names were
displayed successfully.
---
## Experiment 25
**Aim:**
To display Employee Name, Job, Department Number, and Department Name for all
employees working at the **Dallas** location.
### SQL Program
```sql
SELECT [Link], E.Job_type, [Link], [Link]
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON [Link] = [Link]
WHERE [Link] = 'DALLAS';
```
### Output Table
| Ename | Job_type | Dno | Dname |
| ----- | -------- | --: | -------- |
| JONES | MANAGER | 20 | RESEARCH |
| SCOTT | ANALYST | 20 | RESEARCH |
| FORD | ANALYST | 20 | RESEARCH |
| SMITH | CLERK | 20 | RESEARCH |
| ADAMS | CLERK | 20 | RESEARCH |
**Result:**
The details of all employees working at the Dallas location were displayed successfully.
## Experiment 26
**Aim:**
To display Employee Name and Employee Number along with their Manager's Name and
Manager's Employee Number, including employees who do not have a manager.
### SQL Program
```sql
SELECT [Link] AS Employee_Name,
[Link] AS Employee_No,
[Link] AS Manager_Name,
[Link] AS Manager_No
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M
ON [Link] = [Link];
```
### Output Table
| Employee_Name | Employee_No | Manager_Name | Manager_No |
| ------------- | ----------- | ------------ | ---------- |
| KING | 7839 | NULL | NULL |
| CLARK | 7782 | KING | 7839 |
| JONES | 7566 | KING | 7839 |
| BLAKE | 7698 | KING | 7839 |
| MILLER | 7934 | CLARK | 7782 |
| SCOTT | 7788 | JONES | 7566 |
| FORD | 7902 | JONES | 7566 |
| SMITH | 7369 | FORD | 7902 |
| ADAMS | 7876 | SCOTT | 7788 |
| ALLEN | 7499 | BLAKE | 7698 |
| WARD | 7521 | BLAKE | 7698 |
| MARTIN | 7654 | BLAKE | 7698 |
| TURNER | 7844 | BLAKE | 7698 |
| JAMES | 7900 | BLAKE | 7698 |
**Result:**
The employees and their respective managers, including employees without a manager,
were displayed successfully.
---
## Experiment 27
**Aim:**
To display Name, Department Number and Salary of employees whose department number
and salary match those of an employee who earns a commission.
### SQL Program
```sql
SELECT Ename, Dno, Salary
FROM EMPLOYEE
WHERE (Dno, Salary) IN (
SELECT Dno, Salary
FROM EMPLOYEE
WHERE Commission IS NOT NULL
AND Commission > 0
);
```
### Output Table
| Ename | Dno | Salary |
| ------ | --: | ------: |
| ALLEN | 30 | 1600.00 |
| WARD | 30 | 1250.00 |
| MARTIN | 30 | 1250.00 |
**Result:**
The employees whose department number and salary match employees earning
commission were displayed successfully.
---
## Experiment 28
**Aim:**
To display Employee Name and Salary represented by asterisks, where each `*` represents
**$100**.
### SQL Program
```sql
SELECT Ename,
REPEAT('*', FLOOR(Salary / 100)) AS Salary
FROM EMPLOYEE;
```
### Output Table
| Ename | Salary |
| ------ | -------------------------------------------------- |
| KING | ************************************************** |
| CLARK | ************************ |
| JONES | ***************************** |
| BLAKE | **************************** |
| MILLER | ************* |
| SCOTT | ****************************** |
| FORD | ****************************** |
| SMITH | ******** |
| ADAMS | *********** |
| ALLEN | **************** |
| WARD | ************ |
| MARTIN | ************ |
| TURNER | *************** |
| JAMES | ********* |
Each `*` = **$100**. Since `FLOOR()` is used, for example `$2450 → 24 asterisks`.
**Result:**
The salaries of employees were represented using asterisks successfully.
---
## Experiment 29
**Aim:**
To display the Highest, Lowest, Sum and Average Salaries of all employees.
### SQL Program
```sql
SELECT MAX(Salary) AS Highest_Salary,
MIN(Salary) AS Lowest_Salary,
SUM(Salary) AS Total_Salary,
AVG(Salary) AS Average_Salary
FROM EMPLOYEE;
```
### Output Table
| Highest_Salary | Lowest_Salary | Total_Salary | Average_Salary |
| -------------: | ------------: | -----------: | -------------: |
| 5000.00 | 800.00 | 29025.00 | 2073.21 |
**Result:**
The highest, lowest, total and average salaries were calculated successfully.
---
## Experiment 30
**Aim:**
To display the number of employees performing the same Job Type functions.
### SQL Program
```sql
SELECT Job_type,
COUNT(*) AS No_of_Employees
FROM EMPLOYEE
GROUP BY Job_type;
```
### Output Table
| Job_type | No_of_Employees |
| --------- | --------------: |
| PRESIDENT | 1|
| MANAGER | 3|
| ANALYST | 2|
| CLERK | 4|
| SALESMAN | 4|
**Result:**
The number of employees performing each job type was displayed successfully.
## Experiment 31
**Aim:**
To display the number of managers without listing their names.
### SQL Program
```sql
SELECT COUNT(DISTINCT Manager) AS No_of_Managers
FROM EMPLOYEE
WHERE Manager IS NOT NULL;
```
### Output Table
| No_of_Managers |
| -------------: |
| 6|
**Result:**
The total number of managers was displayed successfully.
---
## Experiment 32
**Aim:**
To display Department Name, Location, Number of Employees and Average Salary for each
department.
### SQL Program
```sql
SELECT [Link],
[Link],
COUNT([Link]) AS No_of_Employees,
AVG([Link]) AS Average_Salary
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link];
```
### Output Table
| Dname | Location | No_of_Employees | Average_Salary |
| ---------- | -------- | --------------: | -------------: |
| ACCOUNTING | NEW YORK | 3| 2916.67 |
| RESEARCH | DALLAS | 5| 2175.00 |
| SALES | CHICAGO | 6| 1566.67 |
| OPERATIONS | BOSTON | 0| NULL |
**Result:**
The department details, number of employees and average salaries were displayed
successfully.
---
## Experiment 33
**Aim:**
To display Name and Hire Date of all employees working in the same department as BLAKE.
### SQL Program
```sql
SELECT Ename, Hire_date
FROM EMPLOYEE
WHERE Dno = (
SELECT Dno
FROM EMPLOYEE
WHERE Ename = 'BLAKE'
);
```
### Output Table
| Ename | Hire_date |
| ------ | ---------- |
| BLAKE | 1981-05-01 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| MARTIN | 1981-09-28 |
| TURNER | 1981-09-08 |
| JAMES | 1981-12-03 |
**Result:**
The names and hire dates of employees working in the same department as BLAKE were
displayed successfully.
---
## Experiment 34
**Aim:**
To display Employee Number and Name of all employees who earn more than the average
salary.
### SQL Program
```sql
SELECT Eno, Ename
FROM EMPLOYEE
WHERE Salary > (
SELECT AVG(Salary)
FROM EMPLOYEE
);
```
Average salary = **2073.21**
### Output Table
| Eno | Ename |
| ---- | ----- |
| 7839 | KING |
| 7782 | CLARK |
| 7566 | JONES |
| 7698 | BLAKE |
| 7788 | SCOTT |
| 7902 | FORD |
**Result:**
The employee numbers and names of employees earning more than the average salary were
displayed successfully.
---
## Experiment 35
**Aim:**
To display Employee Number and Name of all employees who work in a department
containing any employee whose name has a `T`.
### SQL Program
```sql
SELECT Eno, Ename
FROM EMPLOYEE
WHERE Dno IN (
SELECT Dno
FROM EMPLOYEE
WHERE Ename LIKE '%T%'
);
```
Employees containing `T` include **SCOTT, SMITH, MARTIN and TURNER**, so this selects
employees from Departments **20 and 30**.
### Output Table
| Eno | Ename |
| ---- | ------ |
| 7566 | JONES |
| 7698 | BLAKE |
| 7788 | SCOTT |
| 7902 | FORD |
| 7369 | SMITH |
| 7876 | ADAMS |
| 7499 | ALLEN |
| 7521 | WARD |
| 7654 | MARTIN |
| 7844 | TURNER |
| 7900 | JAMES |
**Result:**
The employees working in departments containing employees whose names have `T` were
displayed successfully.
---
## Experiment 36
**Aim:**
To display the names and salaries of all employees who report to KING.
### SQL Program
```sql
SELECT Ename, Salary
FROM EMPLOYEE
WHERE Manager = (
SELECT Eno
FROM EMPLOYEE
WHERE Ename = 'KING'
);
```
KING's employee number is `7839`.
### Output Table
| Ename | Salary |
| ----- | ------: |
| CLARK | 2450.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
**Result:**
The names and salaries of employees who report directly to KING were displayed
successfully.
---
# Experiment 37
**Aim:**
To display Department Number, Employee Name and Job of all employees working in the
SALES department.
### SQL Program
```sql
SELECT [Link],
[Link],
E.Job_type
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON [Link] = [Link]
WHERE [Link] = 'SALES';
```
### Output Table
| Dno | Ename | Job_type |
| --: | ------ | -------- |
| 30 | BLAKE | MANAGER |
| 30 | ALLEN | SALESMAN |
| 30 | WARD | SALESMAN |
| 30 | MARTIN | SALESMAN |
| 30 | TURNER | SALESMAN |
| 30 | JAMES | CLERK |
**Result:**
The department number, employee names and jobs of all employees working in the SALES
department were displayed successfully.