5) Create table commands of the Normalized tables

CREATE TABLE DEPARTMENT (

DEPT_ID INT PRIMARY KEY,

DEPT_NAME VARCHAR(100) NOT NULL,

);

CREATE TABLE ARMY (

A_ID INT PRIMARY KEY,

A_NAME VARCHAR(100) NOT NULL,

RANK VARCHAR(50),

AGE INT,

JOINING_DATE DATE,

DEPT_ID INT,

FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

);

CREATE TABLE NAVY (

N_ID INT PRIMARY KEY,

N_NAME VARCHAR(100) NOT NULL,

RANK VARCHAR(50),

AGE INT,

DATE_OF_JOINING DATE,

DEPT_ID INT,

FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

);

CREATE TABLE AIRFORCE (

F_ID INT PRIMARY KEY,

F_NAME VARCHAR(100) NOT NULL,

RANK VARCHAR(50),

AGE INT,

JOINING_DATE DATE,

DEPT_ID INT,

FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

);

CREATE TABLE PERSONNEL (

ID INT PRIMARY KEY,

NAME VARCHAR(100) NOT NULL,

RANK VARCHAR(50),

AGE INT,

JOINING_DATE DATE,

DEPT_ID INT,

FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

);

6) 20 queries solved on MySQl in the form (Question, Query,Output) on the

above tables covering
1. Retrieve all personnel aged between 25 and 40.
mysql> SELECT * FROM PERSONNEL WHERE AGE BETWEEN 25 AND 40;
-> //
+----+----------------------+-------------------+------+---------+--------------+----------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+----------------------+-------------------+------+---------+--------------+----------+
| 1 | Vikram Batra | CAPTAIN | 29 | 101 | 1997-06-06 | Army |
| 4 | Abhinandan Varthaman | Wing Commander | 35 | 102 | 2004-01-15 | Airforce |
| 5 | Gunjan Saxena | Flight Lieutenant | 30 | 104 | 2006-09-25 | Airforce |
| 8 | Rakesh Sharma | Squadron Leader | 38 | 102 | 1984-09-01 | Airforce |
| 9 | Harita Kaur Deol | Flight Lieutenant | 26 | 104 | 1994-05-15 | Airforce |
+----+----------------------+-------------------+------+---------+--------------+----------+
5 rows in set (0.00 sec)

2. Find all personnel with names that having second letter as 'A'.
mysql> SELECT * FROM PERSONNEL
-> WHERE NAME LIKE '_A%';
+----+------------------+-------------------+------+---------+--------------+----------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+------------------+-------------------+------+---------+--------------+----------+
| 3 | Rajendra Singh | Admiral | 50 | 103 | 1990-07-20 | Navy |
| 6 | Karambir Singh | Vice Admiral | 48 | 103 | 1995-11-18 | Navy |
| 7 | Sam Manekshaw | Field Marshal | 50 | 101 | 1973-03-20 | Army |
| 8 | Rakesh Sharma | Squadron Leader | 38 | 102 | 1984-09-01 | Airforce |
| 9 | Harita Kaur Deol | Flight Lieutenant | 26 | 104 | 1994-05-15 | Airforce |
+----+------------------+-------------------+------+---------+--------------+----------+
5 rows in set (0.00 sec)

3. List personnel who joined after 2000-01-01.

mysql> SELECT * FROM PERSONNEL
-> WHERE JOINING_DATE > '2000-01-01';
+----+----------------------+-------------------+------+---------+--------------+----------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+----------------------+-------------------+------+---------+--------------+----------+
| 4 | Abhinandan Varthaman | Wing Commander | 35 | 102 | 2004-01-15 | Airforce |
| 5 | Gunjan Saxena | Flight Lieutenant | 30 | 104 | 2006-09-25 | Airforce |
| 10 | Krishna Kant | Commander | 42 | 103 | 2003-12-10 | Navy |
+----+----------------------+-------------------+------+---------+--------------+----------+
3 rows in set (0.00 sec)

4. Display all Army personnel, sorted by their rank in descending order.

mysql> SELECT * FROM ARMY
-> ORDER BY `RANK` DESC;
+------+----------------+------------+------+---------+--------------+
| A_ID | A_NAME | RANK | AGE | DEPT_ID | JOINING_DATE |
+------+----------------+------------+------+---------+--------------+
| 4 | Pooja Reddy | Major | 38 | 101 | 2010-07-21 |
| 6 | Neha Thakur | Lieutenant | 28 | 101 | 2020-04-04 |
| 3 | Vikram Chauhan | Colonel | 45 | 101 | 2005-09-18 |
| 1 | Riya | Captain | 20 | 101 | 2009-09-09 |
| 2 | Shruti | Captain | 30 | 103 | 2010-10-10 |
| 5 | Ramesh Iyer | Captain | 35 | 102 | 2013-01-12 |
| 7 | Akhil Nair | Brigadier | 50 | 104 | 2000-02-14 |
+------+----------------+------------+------+---------+--------------+
7 rows in set (0.00 sec)

5. Count the number of personnel in each department.

mysql> SELECT DEPT_ID, COUNT(*) AS PersonnelCount
-> FROM PERSONNEL
-> GROUP BY DEPT_ID;
+---------+----------------+
| DEPT_ID | PersonnelCount |
+---------+----------------+
| 101 | 2|
| 102 | 3|
| 103 | 3|
| 104 | 2|
+---------+----------------+
4 rows in set (0.00 sec)

6. Generate a Cartesian product of NAVY and DEPARTMENT tables.

mysql> SELECT *
-> FROM NAVY, DEPARTMENT;
+------+----------------+-------------------+------+---------+-----------------+---------+--------------------------+
| N_ID | N_NAME | RANK | AGE | DEPT_ID | DATE_OF_JOINING | DEPT_ID |
DEPT_NAME |
+------+----------------+-------------------+------+---------+-----------------+---------+--------------------------+
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 101 | Army
|
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 101 | Army
|
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 101 | Army
|
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 101 | Army
|
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 101 | Army
|
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 101 | Army
|
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 102 | Navy
|
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 102 | Navy
|
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 102 | Navy
|
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 102 | Navy
|
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 102 | Navy
|
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 102 | Navy
|
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 103 | Airforce
|
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 103 | Airforce
|
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 103 | Airforce
|
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 103 | Airforce
|
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 103 | Airforce
|
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 103 | Airforce
|
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 104 | Infantry
|
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 104 | Infantry
|
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 104 | Infantry
|
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 104 | Infantry
|
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 104 | Infantry
|
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 104 | Infantry
|
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 105 | Artillery
|
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 105 | Artillery
|
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 105 | Artillery
|
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 105 | Artillery
|
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 105 | Artillery
|
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 105 | Artillery
|
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 106 | Naval
Operations |
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 106 | Naval
Operations |
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 106 | Naval
Operations |
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 106 | Naval
Operations |
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 106 | Naval
Operations |
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 106 | Naval
Operations |
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 107 | Air Force
Command |
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 107 | Air Force
Command |
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 107 | Air Force
Command |
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 107 | Air Force
Command |
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 107 | Air Force
Command |
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 107 | Air Force
Command |
| 6 | Meera Kulkarni | Navigator | 30 | 102 | 2018-06-17 | 108 | Research
and Development |
| 5 | Amit Singh | Submarine Officer | 32 | 102 | 2015-08-30 | 108 | Research
and Development |
| 4 | Sneha Patil | Captain | 38 | 102 | 2011-11-11 | 108 | Research and
Development |
| 3 | Aditya Varma | Lieutenant | 35 | 102 | 2012-03-22 | 108 | Research
and Development |
| 2 | Rajesh Sharma | Commander | 40 | 102 | 2009-05-14 | 108 | Research
and Development |
| 1 | SHRAVANI | CAPTAIN | 35 | 102 | 2010-10-10 | 108 | Research and
Development |
+------+----------------+-------------------+------+---------+-----------------+---------+--------------------------+
48 rows in set (0.00 sec)

7. Retrieve personnel details along with their department names.

mysql> SELECT [Link], [Link], [Link], D.DEPT_NAME
-> FROM PERSONNEL P
-> INNER JOIN DEPARTMENT D ON P.DEPT_ID = D.DEPT_ID;
+----+----------------------+-------------------+-----------+
| ID | NAME | RANK | DEPT_NAME |
+----+----------------------+-------------------+-----------+
| 1 | Vikram Batra | Captain | Army |
| 7 | Sam Manekshaw | Field Marshal | Army |
| 2 | Arjan Singh | Marshal | Navy |
| 4 | Abhinandan Varthaman | Wing Commander | Navy |
| 8 | Rakesh Sharma | Squadron Leader | Navy |
| 3 | Rajendra Singh | Admiral | Airforce |
| 6 | Karambir Singh | Vice Admiral | Airforce |
| 10 | Krishna Kant | Commander | Airforce |
| 5 | Gunjan Saxena | Flight Lieutenant | Infantry |
| 9 | Harita Kaur Deol | Flight Lieutenant | Infantry |
+----+----------------------+-------------------+-----------+
10 rows in set (0.00 sec)

8. List all personnel and their departments, including personnel with no

department assigned.
mysql> SELECT [Link], [Link], D.DEPT_NAME
-> FROM PERSONNEL P
-> LEFT JOIN DEPARTMENT D ON P.DEPT_ID = D.DEPT_ID;
+----+----------------------+-----------+
| ID | NAME | DEPT_NAME |
+----+----------------------+-----------+
| 1 | Vikram Batra | Army |
| 2 | Arjan Singh | Navy |
| 3 | Rajendra Singh | Airforce |
| 4 | Abhinandan Varthaman | Navy |
| 5 | Gunjan Saxena | Infantry |
| 6 | Karambir Singh | Airforce |
| 7 | Sam Manekshaw | Army |
| 8 | Rakesh Sharma | Navy |
| 9 | Harita Kaur Deol | Infantry |
| 10 | Krishna Kant | Airforce |
+----+----------------------+-----------+
10 rows in set (0.00 sec)

9. List all departments and the personnel assigned, including departments

with no personnel.
mysql> SELECT D.DEPT_NAME, [Link]
-> FROM DEPARTMENT D
-> RIGHT JOIN PERSONNEL P ON D.DEPT_ID = P.DEPT_ID;
+-----------+----------------------+
| DEPT_NAME | NAME |
+-----------+----------------------+
| Army | Vikram Batra |
| Navy | Arjan Singh |
| Airforce | Rajendra Singh |
| Navy | Abhinandan Varthaman |
| Infantry | Gunjan Saxena |
| Airforce | Karambir Singh |
| Army | Sam Manekshaw |
| Navy | Rakesh Sharma |
| Infantry | Harita Kaur Deol |
| Airforce | Krishna Kant |
+-----------+----------------------+
10 rows in set (0.00 sec)

10. Find personnel who belong to the department with the highest number of
members.
mysql> SELECT *
-> FROM PERSONNEL
-> WHERE DEPT_ID = (
-> SELECT DEPT_ID
 -> FROM PERSONNEL
-> GROUP BY DEPT_ID
-> ORDER BY COUNT(*) DESC
-> LIMIT 1
-> );
+----+----------------------+-----------------+------+---------+--------------+----------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+----------------------+-----------------+------+---------+--------------+----------+
| 2 | Arjan Singh | Marshal | 45 | 102 | 1985-04-14 | Airforce |
| 4 | Abhinandan Varthaman | Wing Commander | 35 | 102 | 2004-01-15 | Airforce |
| 8 | Rakesh Sharma | Squadron Leader | 38 | 102 | 1984-09-01 | Airforce |
+----+----------------------+-----------------+------+---------+--------------+----------+
3 rows in set (0.00 sec)

11. Create a view to list all personnel and their department names.
mysql> CREATE VIEW PersonnelWithDept AS
-> SELECT [Link], [Link], [Link], D.DEPT_NAME
-> FROM PERSONNEL P
-> INNER JOIN DEPARTMENT D ON P.DEPT_ID = D.DEPT_ID;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM PERSONNELWITHDEPT;

+----+----------------------+-------------------+-----------+
| ID | NAME | RANK | DEPT_NAME |
+----+----------------------+-------------------+-----------+
| 1 | Vikram Batra | Captain | Army |
| 2 | Arjan Singh | Marshal | Navy |
| 3 | Rajendra Singh | Admiral | Airforce |
| 4 | Abhinandan Varthaman | Wing Commander | Navy |
| 5 | Gunjan Saxena | Flight Lieutenant | Infantry |
| 6 | Karambir Singh | Vice Admiral | Airforce |
| 7 | Sam Manekshaw | Field Marshal | Army |
| 8 | Rakesh Sharma | Squadron Leader | Navy |
| 9 | Harita Kaur Deol | Flight Lieutenant | Infantry |
| 10 | Krishna Kant | Commander | Airforce |
+----+----------------------+-------------------+-----------+
10 rows in set (0.00 sec)

12. Create an index on the RANK column in the PERSONNEL table for faster
querying.
mysql> CREATE INDEX idx_rank ON PERSONNEL(`RANK`);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

13. Create a trigger to automatically log deletions from the NAVY table.
mysql> CREATE TRIGGER log_navy_deletion
-> AFTER DELETE ON NAVY
-> FOR EACH ROW
-> INSERT INTO DELETION_LOG (TABLE_NAME, RECORD_ID, DELETED_AT)
-> VALUES ('NAVY', OLD.N_ID, NOW());
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM PERSONNEL;

+----+----------------------+-------------------+------+---------+--------------+----------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+----------------------+-------------------+------+---------+--------------+----------+
| 1 | Vikram Batra | Captain | 29 | 101 | 1997-06-06 | Army |
| 2 | Arjan Singh | Marshal | 45 | 102 | 1985-04-14 | Airforce |
| 3 | Rajendra Singh | Admiral | 50 | 103 | 1990-07-20 | Navy |
| 4 | Abhinandan Varthaman | Wing Commander | 35 | 102 | 2004-01-15 | Airforce |
| 5 | Gunjan Saxena | Flight Lieutenant | 30 | 104 | 2006-09-25 | Airforce |
| 6 | Karambir Singh | Vice Admiral | 48 | 103 | 1995-11-18 | Navy |
| 7 | Sam Manekshaw | Field Marshal | 50 | 101 | 1973-03-20 | Army |
| 8 | Rakesh Sharma | Squadron Leader | 38 | 102 | 1984-09-01 | Airforce |
| 9 | Harita Kaur Deol | Flight Lieutenant | 26 | 104 | 1994-05-15 | Airforce |
| 10 | Krishna Kant | Commander | 42 | 103 | 2003-12-10 | Navy |
+----+----------------------+-------------------+------+---------+--------------+----------+
10 rows in set (0.00 sec)

mysql> DELETE FROM PERSONNEL WHERE ID = 10;

Query OK, 1 row affected (0.01 sec)

14. Create a procedure to update the rank of a personnel by their ID.

mysql> DELIMITER //
mysql> CREATE PROCEDURE UpdateRank(IN personnelId INT, IN newRank VARCHAR(20))
-> BEGIN
-> UPDATE PERSONNEL
-> SET `RANK` = newRank
-> WHERE ID = personnelId;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> CALL UpdateRank(1, 'CAPTAIN');

-> //
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM PERSONNEL WHERE ID = 1;

-> //
+----+--------------+---------+------+---------+--------------+--------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+--------------+---------+------+---------+--------------+--------+
| 1 | Vikram Batra | CAPTAIN | 29 | 101 | 1997-06-06 | Army |
+----+--------------+---------+------+---------+--------------+--------+
1 row in set (0.00 sec)

15. Retrieve all AIRFORCE personnel working in the 'Wing Commander'

department.
mysql> SELECT AF.F_ID, AF.F_NAME, D.DEPT_NAME
-> FROM AIRFORCE AF
-> INNER JOIN DEPARTMENT D ON AF.DEPT_ID = D.DEPT_ID
-> WHERE D.DEPT_NAME = 'WING COMMANDER';
-> //
Query OK, 1 row affected (0.01 sec)

16. Retrieve the top 5 oldest personnel in the Army.

mysql> SELECT *
-> FROM ARMY
-> ORDER BY AGE DESC
-> LIMIT 5;
-> //
+------+----------------+-----------+------+---------+--------------+
| A_ID | A_NAME | RANK | AGE | DEPT_ID | JOINING_DATE |
+------+----------------+-----------+------+---------+--------------+
| 7 | Akhil Nair | Brigadier | 50 | 104 | 2000-02-14 |
| 3 | Vikram Chauhan | Colonel | 45 | 101 | 2005-09-18 |
| 4 | Pooja Reddy | Major | 38 | 101 | 2010-07-21 |
| 5 | Ramesh Iyer | Captain | 35 | 102 | 2013-01-12 |
| 2 | Shruti | Captain | 30 | 103 | 2010-10-10 |
+------+----------------+-----------+------+---------+--------------+
5 rows in set (0.00 sec)

17. Find personnel with 'AIRFORCE' anywhere in their BRANCH.

mysql> SELECT *
 -> FROM PERSONNEL
-> WHERE BRANCH LIKE '%AIRFORCE%';
-> //
+----+----------------------+-------------------+------+---------+--------------+----------+
| ID | NAME | RANK | AGE | DEPT_ID | JOINING_DATE | BRANCH |
+----+----------------------+-------------------+------+---------+--------------+----------+
| 2 | Arjan Singh | Marshal | 45 | 102 | 1985-04-14 | Airforce |
| 4 | Abhinandan Varthaman | Wing Commander | 35 | 102 | 2004-01-15 | Airforce |
| 5 | Gunjan Saxena | Flight Lieutenant | 30 | 104 | 2006-09-25 | Airforce |
| 8 | Rakesh Sharma | Squadron Leader | 38 | 102 | 1984-09-01 | Airforce |
| 9 | Harita Kaur Deol | Flight Lieutenant | 26 | 104 | 1994-05-15 | Airforce |
+----+----------------------+-------------------+------+---------+--------------+----------+
5 rows in set (0.00 sec)

18. List all departments that currently have no personnel assigned.

mysql> SELECT DEPT_NAME
-> FROM DEPARTMENT D
-> LEFT JOIN PERSONNEL P ON D.DEPT_ID = P.DEPT_ID
-> WHERE [Link] IS NULL;
-> //
+--------------------------+
| DEPT_NAME |
+--------------------------+
| Artillery |
| Naval Operations |
| Air Force Command |
| Research and Development |
| WING COMMANDER |
+--------------------------+
5 rows in set (0.00 sec)
19. Count personnel in each branch (NAVY, ARMY, AIRFORCE).
mysql> SELECT 'NAVY' AS Branch, COUNT(*) AS PersonnelCount FROM NAVY
-> UNION
-> SELECT 'ARMY' AS Branch, COUNT(*) AS PersonnelCount FROM ARMY
-> UNION
-> SELECT 'AIRFORCE' AS Branch, COUNT(*) AS PersonnelCount FROM AIRFORCE;
-> //
+----------+----------------+
| Branch | PersonnelCount |
+----------+----------------+
| NAVY | 6|
| ARMY | 7|
| AIRFORCE | 6|
+----------+----------------+
3 rows in set (0.01 sec)

20. List all NAVY personnel along with their details from the PERSONNEL
table.
mysql> SELECT N.N_ID, N.N_NAME, [Link], P.JOINING_DATE
-> FROM NAVY N
-> INNER JOIN PERSONNEL P ON N.N_ID = [Link];
-> //
+------+----------------+------+--------------+
| N_ID | N_NAME | AGE | JOINING_DATE |
+------+----------------+------+--------------+
| 1 | SHRAVANI | 29 | 1997-06-06 |
| 2 | Rajesh Sharma | 45 | 1985-04-14 |
| 3 | Aditya Varma | 50 | 1990-07-20 |
| 4 | Sneha Patil | 35 | 2004-01-15 |
| 5 | Amit Singh | 30 | 2006-09-25 |
| 6 | Meera Kulkarni | 48 | 1995-11-18 |
+------+----------------+------+--------------+
6 rows in set (0.00 sec)