Find out all the names of the client from Client_Mast table:

                        
mysql> SELECT Name FROM Client_Mast;
+----------+
| Name     |
+----------+
| Procurez |
| BMW      |
| Takenote |
| Teoco    |
| ASAP     |
+----------+
                        
                    

Retrieve all the records fro Client_Mast table:

                        
mysql> SELECT * FROM Client_Mast;
+-----------+----------+-----------------------+--------------+---------+----------+---------+
| Client_no | Name     | Address               | City         | Pincode | State    | Bal_due |
+-----------+----------+-----------------------+--------------+---------+----------+---------+
| B001      | Procurez | 12 Sunbay street      | Glainsville  |    1233 | Florida  | 3500.00 |
| B002      | BMW      | 6 Rocky creek         | Jacksonville |    1234 | Florida  | 3488.00 |
| B003      | Takenote | 7 Hudson bay          | Puria        |    6454 | Illinois | 4555.00 |
| B004      | Teoco    | 1243 Princeton circle | Fairfax      |    3433 | Virginia | 4433.00 |
| B005      | ASAP     | 23 North city         | Puria        |    4354 | Illinois | 3600.00 |
+-----------+----------+-----------------------+--------------+---------+----------+---------+
                        
                    

Retrieve the list of names, address and city of all the clients from Client_Mast:

                        
mysql> SELECT Name, Address, City FROM Client_Mast;
+----------+-----------------------+--------------+
| Name     | Address               | City         |
+----------+-----------------------+--------------+
| Procurez | 12 Sunbay street      | Glainsville  |
| BMW      | 6 Rocky creek         | Jacksonville |
| Takenote | 7 Hudson bay          | Puria        |
| Teoco    | 1243 Princeton circle | Fairfax      |
| ASAP     | 23 North city         | Puria        |
+----------+-----------------------+--------------+
                        
                    

List all the clients who are staying in Florida from Client_Mast:

                        
mysql> SELECT * FROM Client_Mast WHERE State LIKE 'FLorida';
+-----------+----------+------------------+--------------+---------+---------+---------+
| Client_no | Name     | Address          | City         | Pincode | State   | Bal_due |
+-----------+----------+------------------+--------------+---------+---------+---------+
| B001      | Procurez | 12 Sunbay street | Glainsville  |    1233 | Florida | 3500.00 |
| B002      | BMW      | 6 Rocky creek    | Jacksonville |    1234 | Florida | 3488.00 |
+-----------+----------+------------------+--------------+---------+---------+---------+
                        
                    

List the names of the employee who have a salary less than Rs 3000 from employee table:

                        
mysql> SELECT Ename 
FROM EMPLOYEE 
WHERE Basic_Sal < 3000;

+-------+
| Ename |
+-------+
| Kelly |
| Amold |
+-------+
                        
                    

List the employee name, job, and department no, of everyone whose name fall in the alphabetical 'C' to 'L' from employee table:

                        
mysql> SELECT Ename, Job, Dept_no 
FROM EMPLOYEE 
WHERE Ename BETWEEN 'C' AND 'L';

+-----------+---------+---------+
| Ename     | Job     | Dept_no |
+-----------+---------+---------+
| Kelly     | Admin   | D003    |
| Kim       | Manager | D001    |
| Holvfield | Tester  | D002    |
+-----------+---------+---------+
                        
                    

List all the employees whose name start with the letter 'K' from employee table.

                        
mysql> SELECT * FROM EMPLOYEE
    WHERE Ename LIKE 'K%';

+-------+-------+---------+------------+-----------+--------+---------+
| Empno | Ename | Job     | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-------+---------+------------+-----------+--------+---------+
| B0005 | Kelly | Admin   | 1999-04-11 |   2000.00 | 200.00 | D003    |
| E0001 | Kim   | Manager | 2002-12-15 |   5000.00 | 500.00 | D001    |
+-------+-------+---------+------------+-----------+--------+---------+
                        
                    

List the department name which is located in Noida and Rocky Creek from Dept table.

                        
mysql> SELECT Dname
    FROM DEPT
    WHERE Loc IN  ('Noida', 'Rocky Creek');

+-------------+
| Dname       |
+-------------+
| Development |
| R & D       |
| Production  |
+-------------+
                        
                    

List all the employee name working in department D002, D003 from employee table.

                        
mysql> SELECT Dname
FROM DEPT
WHERE Deptno IN ('D0002', 'D0003');

+-------+
| Dname |
+-------+
| R & D |
| Admin |
+-------+
                        
                    

List all employee whose name start with 'A' and end with 'D' from employee table:

                        
mysql> SELECT *
FROM EMPLOYEE
WHERE Ename LIKE 'A%D';

+-------+-------+-------+------------+-----------+--------+---------+
| Empno | Ename | Job   | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-------+-------+------------+-----------+--------+---------+
| E0003 | Amold | Clerk | 2001-01-10 |   2500.00 | 250.00 | D004    |
+-------+-------+-------+------------+-----------+--------+---------+
                        
                    

List all managers and salesman with salary over 2500 from employee table:

                        
mysql> SELECT * 
FROM EMPLOYEE 
WHERE Job IN ('Manager', 'Salesman') AND Basic_Sal > 2500;

+-------+-------+---------+------------+-----------+--------+---------+
| Empno | Ename | Job     | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-------+---------+------------+-----------+--------+---------+
| E0001 | Kim   | Manager | 2002-12-15 |   5000.00 | 500.00 | D001    |
+-------+-------+---------+------------+-----------+--------+---------+
                        
                    

Display all the employee names in the ascending order of their data of joining from employee table.

                        
mysql> SELECT Ename
FROM EMPLOYEE 
ORDER BY Hiredate ASC;

+-----------+
| Ename     |
+-----------+
| Kelly     |
| Bruce     |
| Amold     |
| Holvfield |
| Kim       |
+-----------+
                        
                    

Display all the employee in alphabetical order from employee table:

                        
mysql> SELECT * 
FROM EMPLOYEE
ORDER BY Ename ASC;

+-------+-----------+---------+------------+-----------+--------+---------+
| Empno | Ename     | Job     | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-----------+---------+------------+-----------+--------+---------+
| E0003 | Amold     | Clerk   | 2001-01-10 |   2500.00 | 250.00 | D004    |
| E0002 | Bruce     | Analyst | 1999-04-24 |   4000.00 | 400.00 | D002    |
| E0004 | Holvfield | Tester  | 2001-10-10 |   3000.00 | 300.00 | D002    |
| B0005 | Kelly     | Admin   | 1999-04-11 |   2000.00 | 200.00 | D003    |
| E0001 | Kim       | Manager | 2002-12-15 |   5000.00 | 500.00 | D001    |
+-------+-----------+---------+------------+-----------+--------+---------+
                        
                    

List all employee who were hired during 1999 from employee table:

                        
mysql> SELECT * 
FROM EMPLOYEE 
WHERE YEAR(Hiredate) = 1999;

+-------+-------+---------+------------+-----------+--------+---------+
| Empno | Ename | Job     | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-------+---------+------------+-----------+--------+---------+
| B0005 | Kelly | Admin   | 1999-04-11 |   2000.00 | 200.00 | D003    |
| E0002 | Bruce | Analyst | 1999-04-24 |   4000.00 | 400.00 | D002    |
+-------+-------+---------+------------+-----------+--------+---------+
                        
                    

List all the employees whose commission is more than Rs. 300 from employee table:

                        
mysql> SELECT *
FROM EMPLOYEE
WHERE Comm > 300;

+-------+-------+---------+------------+-----------+--------+---------+
| Empno | Ename | Job     | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-------+---------+------------+-----------+--------+---------+
| E0001 | Kim   | Manager | 2002-12-15 |   5000.00 | 500.00 | D001    |
| E0002 | Bruce | Analyst | 1999-04-24 |   4000.00 | 400.00 | D002    |
+-------+-------+---------+------------+-----------+--------+---------+
                        
                    

List the Dept_no & total salary table in each Dept.

                    
mysql> SELECT Dept_no, SUM(Basic_Sal) FROM EMPLOYEE GROUP BY Dept_no;
+---------+----------------+
| Dept_no | SUM(Basic_Sal) |
+---------+----------------+
| D003    |        3000.00 |
| D001    |        5000.00 |
| D002    |        7500.00 |
| D004    |        3000.00 |
+---------+----------------+
                    
                

• GROUP BY is used in SQL when we need to group rows or tuples in a table based on the values in one or more columns. It allows us to perform aggregate operations on these groups, such as counting, summing, or averaging the values within each group.

List the job and number of employee in each job and the result should be in descending order of the number of employee.

                    
mysql> SELECT JOB, COUNT(*) FROM EMPLOYEE GROUP BY JOB ORDER BY 2 DESC;
+---------+----------+
| JOB     | COUNT(*) |
+---------+----------+
| Admin   |        1 |
| Manager |        1 |
| Analyst |        1 |
| Clerk   |        1 |
| Tester  |        1 |
+---------+----------+
5 rows in set (0.00 sec)
                    
                

In this query, we used '2' in the ORDER BY clause because the count column is not physically available in the original EMPLOYEE table. Instead of referring to the column by name, we can use the position of the column in the result set. Here, '2' refers to the second column in the result set, which is the count of employees for each job.

• It's important to note that when ordering by a column not present in the original table, using the position in the result set is a valid approach.

If you want to order the result based on the job titles in descending order instead, you can use the following query:

                    
mysql> SELECT JOB, COUNT(*) FROM EMPLOYEE GROUP BY JOB ORDER BY JOB DESC;
                    
                

• In this query, we order the result by the JOB column itself in descending order. This will arrange the job titles in reverse alphabetical order.

When working with columns not available in the original table, it's crucial to know the sequence or position of the column in the result set to perform the desired ordering or sorting.

List the average salary of each job excluding manager.

                    
mysql> SELECT Job, AVG(Basic_Sal) FROM EMPLOYEE WHERE Job != 'Manager' GROUP BY Job;
+---------+----------------+
| Job     | AVG(Basic_Sal) |
+---------+----------------+
| Admin   |    3000.000000 |
| Analyst |    4000.000000 |
| Clerk   |    3000.000000 |
| Tester  |    3500.000000 |
+---------+----------------+
4 rows in set (0.00 sec)
                    
                

• Note: WHERE clause always comes before ORDER BY clause

Change the city of client_no 'B001' from 'Gainesville' to 'Paul Street' from client_mast table.

                        
mysql> UPDATE Client_Mast 
SET City = 'Paul Street' 
WHERE Client_no = 'B001';

Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Client_Mast 
WHERE Client_no = 'B001';

+-----------+----------+------------------+-------------+---------+---------+---------+
| Client_no | Name     | Address          | City        | Pincode | State   | Bal_due |
+-----------+----------+------------------+-------------+---------+---------+---------+
| B001      | Procurez | 12 Sunbay street | Paul Street |    1233 | Florida | 3500.00 |
+-----------+----------+------------------+-------------+---------+---------+---------+
                        
                    

Change the bal_due of client_no B005 to Rs. 2000 from Client_Mast table:

                        
mysql> UPDATE Client_Mast 
SET Bal_due = 2000 
WHERE Client_no = 'B005';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Client_Mast 
WHERE Client_no = 'B005';

+-----------+------+---------------+-------+---------+----------+---------+
| Client_no | Name | Address       | City  | Pincode | State    | Bal_due |
+-----------+------+---------------+-------+---------+----------+---------+
| B005      | ASAP | 23 North city | Puria |    4354 | Illinois | 2000.00 |
+-----------+------+---------------+-------+---------+----------+---------+
                        
                    

Change the name to 'infospace' of Client_no B004 in the table Client_Mast:

                        
mysql> UPDATE Client_Mast 
SET Name = 'infospace' 
WHERE Client_no = 'B004';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Client_Mast 
WHERE Client_no = 'B004';

+-----------+-----------+-----------------------+---------+---------+----------+---------+
| Client_no | Name      | Address               | City    | Pincode | State    | Bal_due |
+-----------+-----------+-----------------------+---------+---------+----------+---------+
| B004      | infospace | 1243 Princeton circle | Fairfax |    3433 | Virginia | 4433.00 |
+-----------+-----------+-----------------------+---------+---------+----------+---------+
                        
                    

Change the Client_no 'B004' to 'B009' in the table Client_Mast:

                        
mysql> UPDATE Client_Mast 
SET Client_no = 'B009' 
Where Client_no = 'B004';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Client_Mast 
WHERE Client_no = 'B009';

+-----------+-----------+-----------------------+---------+---------+----------+---------+
| Client_no | Name      | Address               | City    | Pincode | State    | Bal_due |
+-----------+-----------+-----------------------+---------+---------+----------+---------+
| B009      | infospace | 1243 Princeton circle | Fairfax |    3433 | Virginia | 4433.00 |
+-----------+-----------+-----------------------+---------+---------+----------+---------+
                        
                    

Change the city of sales man from 'Jacksonville' to 'Hutson' from Salesman_Mast table:

                        
mysql> UPDATE Salesman_Mast 
SET City = 'Huston' 
WHERE Sales_no = 'SA001';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Salesman_Mast 
WHERE Sales_no = 'SA001';

+----------+-------+---------+--------+---------+---------+------------+--------+
| Sales_no | Name  | Address | City   | Pincode | State   | Sal_amount | Target |
+----------+-------+---------+--------+---------+---------+------------+--------+
| SA001    | Sammy | 43 NW   | Huston |    1234 | Florida |    2500.00 | 200.00 |
+----------+-------+---------+--------+---------+---------+------------+--------+
                        
                    

Change the basic salary R 3000 where basic salary less than 2500 from employee table.

                        
mysql> UPDATE EMPLOYEE 
SET Basic_Sal = 3000 
WHERE Basic_sal < 2500;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM EMPLOYEE 
WHERE Basic_sal = 3000;

+-------+-----------+--------+------------+-----------+--------+---------+
| Empno | Ename     | Job    | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-----------+--------+------------+-----------+--------+---------+
| B0005 | Kelly     | Admin  | 1999-04-11 |   3000.00 | 200.00 | D003    |
| E0004 | Holvfield | Tester | 2001-10-10 |   3000.00 | 300.00 | D002    |
+-------+-----------+--------+------------+-----------+--------+---------+
                        
                    

Change the basic_sal = 3000 where job is Clerk from employee table:

                        
mysql> UPDATE EMPLOYEE 
SET Basic_Sal = 3000 
WHERE Job = 'Clerk';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM EMPLOYEE 
WHERE Job = 'Clerk';

+-------+-------+-------+------------+-----------+--------+---------+
| Empno | Ename | Job   | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-------+-------+------------+-----------+--------+---------+
| E0003 | Amold | Clerk | 2001-01-10 |   3000.00 | 250.00 | D004    |
+-------+-------+-------+------------+-----------+--------+---------+
                        
                    

Change the basic salary of employee number E004 to Rs 3500 from EMPLOYEE table:

                        
mysql> UPDATE EMPLOYEE 
SET Basic_Sal = 3500 
WHERE Empno = 'E0004';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM EMPLOYEE 
WHERE Empno = 'E0004';

+-------+-----------+--------+------------+-----------+--------+---------+
| Empno | Ename     | Job    | Hiredate   | Basic_Sal | Comm   | Dept_no |
+-------+-----------+--------+------------+-----------+--------+---------+
| E0004 | Holvfield | Tester | 2001-10-10 |   3500.00 | 300.00 | D002    |
+-------+-----------+--------+------------+-----------+--------+---------+
                        
                    

Change the Department name to 'Sales' from DEPT Table where Deptno is 'D0004':

                        
mysql> UPDATE DEPT 
SET Dname = 'Sales' 
WHERE Deptno = 'D0004';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM DEPT;

+--------+-------------+-------------+
| Deptno | Dname       | Loc         |
+--------+-------------+-------------+
| D0001  | Development | Noida       |
| D0002  | R & D       | Rocky Creek |
| D0003  | Admin       | Seattle     |
| D0004  | Sales       | Seattle     |
| D0005  | Production  | Noida       |
+--------+-------------+-------------+
                        
                    

Change the Description of Product Number 'PR065' to AC in the Product_Mast table:

                        
mysql> UPDATE Product_Mast 
SET Description = 'AC' 
WHERE Product_no = 'PR065';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Product_Mast 
WHERE Product_no = 'PR065';

+------------+-------------+-------------+-------+---------------+------------+------------+
| Product_no | Description | Profit_perc | Units | Qty_Available | Sell_Price | Cost_Price |
+------------+-------------+-------------+-------+---------------+------------+------------+
| PR065      | AC          |       12.00 | Piece |            40 |     504.00 |     450.00 |
+------------+-------------+-------------+-------+---------------+------------+------------+
                        
                    

Change the profit percent of product number 'PR065' to 25% in the Product_Mast table:

                        
mysql> UPDATE Product_Mast 
SET Profit_perc = 25 
WHERE Product_no = 'PR065';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Product_Mast 
WHERE Product_no = 'PR065';

+------------+-------------+-------------+-------+---------------+------------+------------+
| Product_no | Description | Profit_perc | Units | Qty_Available | Sell_Price | Cost_Price |
+------------+-------------+-------------+-------+---------------+------------+------------+
| PR065      | AC          |       25.00 | Piece |            40 |     504.00 |     450.00 |
+------------+-------------+-------------+-------+---------------+------------+------------+
                        
                    

Change the available quantity of product number 'PR065' to 120 in Product_Mast table:

                        
mysql> UPDATE Product_Mast 
SET Qty_Available = 120 
WHERE Product_no = 'PR065';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Product_Mast 
WHERE Product_no = 'PR065';

+------------+-------------+-------------+-------+---------------+------------+------------+
| Product_no | Description | Profit_perc | Units | Qty_Available | Sell_Price | Cost_Price |
+------------+-------------+-------------+-------+---------------+------------+------------+
| PR065      | AC          |       25.00 | Piece |           120 |     504.00 |     450.00 |
+------------+-------------+-------------+-------+---------------+------------+------------+
                        
                    

Change the cost price and selling price of product number 'PR065' to 5000 and 6250 in Product_Mast table:

                        
mysql> UPDATE Product_Mast 
SET Cost_Price = 5000, Sell_Price = 6250 
WHERE Product_no = 'PR065';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Product_Mast 
WHERE Product_no = 'PR065';                               
+------------+-------------+-------------+-------+---------------+------------+------------+
| Product_no | Description | Profit_perc | Units | Qty_Available | Sell_Price | Cost_Price |
+------------+-------------+-------------+-------+---------------+------------+------------+
| PR065      | AC          |       25.00 | Piece |           120 |    6250.00 |    5000.00 |
+------------+-------------+-------------+-------+---------------+------------+------------+
                        
                    

Change the units where product number 'PR065' to 'pack of 10' in the Product_Mast table:

                        
mysql> UPDATE Product_Mast 
SET Units = 'Pack of 10' 
WHERE Product_no = 'PR065';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
FROM Product_Mast 
WHERE Product_no = 'PR065';

+------------+-------------+-------------+------------+---------------+------------+------------+
| Product_no | Description | Profit_perc | Units      | Qty_Available | Sell_Price | Cost_Price |
+------------+-------------+-------------+------------+---------------+------------+------------+
| PR065      | AC          |       25.00 | Pack of 10 |           120 |    6250.00 |    5000.00 |
+------------+-------------+-------------+------------+---------------+------------+------------+
                        
                    

Create a sequence name counter which incremented by 1 and starts with 1.

Create a sequence name counter which incremented by 5 and starts with 25.

Delete all Salesman_Mast whose salaries are equal to Rs. 2000

                    
mysql> set autocommit = off;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from Salesman_Mast where sal_amount = 2000;
Query OK, 0 rows affected (0.00 sec)
                    
                

Write a query to undo above deleted query.

                    
rollback;
                    
                

Delete all products from Product_Mast where Qty_Available = 100;

                    
mysql> DELETE FROM Product_Mast WHERE Qty_Available = 100;
Query OK, 2 rows affected (0.00 sec)
                    
                

Write a query such that above query canot be undone.

                    
commit;
                    
                

Delete from Client_Mast where state = 'Illinois'.

                    
mysql> DELETE FROM Client_Mast where state = "Illinois";
Query OK, 2 rows affected (0.00 sec)
                    
                

Delete all employees from Employee where Basic_Sal is less than 2000;

                    
mysql> DELETE FROM EMPLOYEE WHERE Basic_Sal < 2000;
Query OK, 0 rows affected (0.00 sec)
                    
                

Delete employee from EMPLOYEE table where Basic_Sal = 3000 and job = 'Clerk'.

                    
mysql> DELETE FROM EMPLOYEE WHERE Basic_Sal = 3000 AND Job = "Clerk";
Query OK, 1 row affected (0.00 sec)
                    
                

Delete employee from employee table where job='clerk'or 'manager';

                    
mysql> DELETE FROM EMPLOYEE WHERE Job = "Clerk" OR Job = "Manager";
Query OK, 1 row affected (0.00 sec)
                    
                

Delete all records from dept.

                    
mysql> DELETE FROM DEPT;
Query OK, 5 rows affected (0.00 sec)
                    
                

Delete all managers and salesman with salary over 1500 from employee table.

                    
mysql> DELETE FROM EMPLOYEE WHERE Basic_Sal > 1500;
Query OK, 3 rows affected (0.00 sec)
                    
                

Delete all employees whose name fall in alphabe cal range 'c' to 'l'

                    
mysql> select * from EMPLOYEE;
Empty set (0.00 sec)
                    
                

Write a query so the following statement can be achieved 'Kim is working as manager since 15-12-2002 where employee number is E0001.

                        
SELECT * FROM EMPLOYEE WHERE Empno = "E0001";
                        
                    

Rename table name from Client_Mast to T_Client_Mast.

                    
mysql> RENAME TABLE Client_Mast TO T_Client_Mast;
Query OK, 0 rows affected (0.08 sec)
                    
                

Write a query in which column name should be displayed as employee name from employee table.

                        
SELECT Ename as "Employee Name" FROM EMPLOYEE;
                        
                    

Add a column "Telephone_No" of data type 'int' and size='10' to EMPLOYEE table.

                        
mysql> ALTER TABLE EMPLOYEE ADD Telephone_No INT(10);
Query OK, 0 rows affected, 1 warning (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc EMPLOYEE;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| Empno        | varchar(6)   | NO   | PRI | NULL    |       |
| Ename        | varchar(20)  | NO   |     | NULL    |       |
| Job          | char(10)     | YES  |     | NULL    |       |
| Hiredate     | date         | YES  |     | NULL    |       |
| Basic_Sal    | decimal(9,2) | YES  |     | NULL    |       |
| Comm         | decimal(7,2) | YES  |     | NULL    |       |
| Dept_no      | varchar(4)   | YES  |     | NULL    |       |
| Telephone_No | int          | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
                        
                    

Add a column "Country" of datatype 'varchar' and size='15'to Client_Mast table.

                        
mysql> ALTER TABLE Client_Mast ADD Country varchar(15);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Client_Mast;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Client_no | varchar(6)   | NO   | PRI | NULL    |       |
| Name      | varchar(20)  | NO   |     | NULL    |       |
| Address   | varchar(25)  | YES  |     | NULL    |       |
| City      | varchar(20)  | YES  |     | NULL    |       |
| Pincode   | decimal(6,0) | YES  |     | NULL    |       |
| State     | varchar(15)  | YES  |     | NULL    |       |
| Bal_due   | decimal(8,2) | YES  |     | NULL    |       |
| Country   | varchar(15)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
                        
                    

Increase the side of 'Description' field from 14 to 16 in Product_Mast table.

                    
mysql> ALTER TABLE Product_Mast MODIFY COLUMN Description varchar(16);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Product_Mast;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Product_no    | varchar(6)   | YES  |     | NULL    |       |
| Description   | varchar(16)  | YES  |     | NULL    |       |
| Profit_perc   | decimal(5,2) | YES  |     | NULL    |       |
| Units         | varchar(10)  | YES  |     | NULL    |       |
| Qty_Available | decimal(6,0) | YES  |     | NULL    |       |
| Sell_Price    | decimal(7,2) | YES  |     | NULL    |       |
| Cost_Price    | decimal(7,2) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
                    
                

Modify 'Product_No' key as a primary key from Product_Mast table.

                    
mysql> ALTER TABLE Product_Mast ADD primary key(Product_No);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Product_Mast;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Product_no    | varchar(6)   | NO   | PRI | NULL    |       |
| Description   | varchar(16)  | YES  |     | NULL    |       |
| Profit_perc   | decimal(5,2) | YES  |     | NULL    |       |
| Units         | varchar(10)  | YES  |     | NULL    |       |
| Qty_Available | decimal(6,0) | YES  |     | NULL    |       |
| Sell_Price    | decimal(7,2) | YES  |     | NULL    |       |
| Cost_Price    | decimal(7,2) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
                    
                

Drop the primary key of T_Client_Mast table.

                    
mysql> ALTER TABLE Product_Mast ADD primary key(Product_No);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Product_Mast;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Product_no    | varchar(6)   | NO   | PRI | NULL    |       |
| Description   | varchar(16)  | YES  |     | NULL    |       |
| Profit_perc   | decimal(5,2) | YES  |     | NULL    |       |
| Units         | varchar(10)  | YES  |     | NULL    |       |
| Qty_Available | decimal(6,0) | YES  |     | NULL    |       |
| Sell_Price    | decimal(7,2) | YES  |     | NULL    |       |
| Cost_Price    | decimal(7,2) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
                    
                

Define 'Deptno' as NOT NULL constraint from DEPT table

                    
mysql> ALTER TABLE DEPT MODIFY Deptno varchar(8) NOT NULL;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Deptno | varchar(8)  | NO   |     | NULL    |       |
| Dname  | varchar(20) | YES  |     | NULL    |       |
| Loc    | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
                    
                

Display Empno, Ename and 'Anual Salary' from EMPLOYEE table.

                    
mysql> SELECT Empno, Ename, Basic_Sal AS 'Annual Salary' FROM EMPLOYEE;
+-------+-----------+---------------+
| Empno | Ename     | Annual Salary |
+-------+-----------+---------------+
| B0005 | Kelly     |       2000.00 |
| E0001 | Kim       |       5000.00 |
| E0002 | Bruce     |       4000.00 |
| E0003 | Arnold    |       2500.00 |
| E0004 | Holtfield |       3000.00 |
+-------+-----------+---------------+
5 rows in set (0.00 sec)
                    
                

Add two columns 'Hod' and 'Strength' of datatype varchar and size = 15 to DEPT table.

                    
mysql> ALTER TABLE DEPT ADD Hod varchar(15), ADD Strength varchar(15);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC DEPT;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Deptno   | varchar(8)  | NO   |     | NULL    |       |
| Dname    | varchar(20) | YES  |     | NULL    |       |
| Loc      | varchar(20) | YES  |     | NULL    |       |
| Hod      | varchar(15) | YES  |     | NULL    |       |
| Strength | varchar(15) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
                    
                

Create a view as Emp_View from EMPLOYEE table where Basic_Sal = 2500 and Job = 'Clerk'.

• A view in a relational database is a virtual table that is based on the result of a SELECT query. It does not store the actual data but provides a way to represent the data stored in one or more tables in a specific way. Views are useful for simplifying complex queries, abstracting the underlying structure of the database, and providing a security mechanism by allowing users to access specific columns or rows.

                    
mysql> CREATE VIEW Emp_View AS SELECT Basic_Sal FROM EMPLOYEE WHERE Basic_Sal = 2500 AND Job = 'Clerk';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM Emp_View;
+-----------+
| Basic_Sal |
+-----------+
|   2500.00 |
+-----------+
1 row in set (0.00 sec)
                    
                

Drop above created Emp_View.

                    
DROP VIEW IF EXISTS Emp_View;
                    
                

Create an index for Client_No on T_Client_Mast table.

                    
mysql> CREATE INDEX Ind_Client_No on T_Client_Mast(Client_No);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
                    
                

Create a view as Em_View from EMPLOYEE table with Basic_Sal,Empno,Ename.

                    
mysql> CREATE VIEW Emp_View AS SELECT Basic_Sal, Empno, Ename FROM EMPLOYEE;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM Emp_View;
+-----------+-------+-----------+
| Basic_Sal | Empno | Ename     |
+-----------+-------+-----------+
|   2000.00 | B0005 | Kelly     |
|   5000.00 | E0001 | Kim       |
|   4000.00 | E0002 | Bruce     |
|   2500.00 | E0003 | Arnold    |
|   3000.00 | E0004 | Holtfield |
+-----------+-------+-----------+
5 rows in set (0.00 sec)
                    
                

Create a view as ClientView from T_Client_Mast with all columns of table.

                        
mysql> CREATE VIEW Client_View AS SELECT * FROM T_Client_Mast;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM Client_View;
+-----------+----------+-----------------------+--------------+---------+----------+---------+---------+
| Client_no | Name     | Address               | City         | Pincode | State    | Bal_due | Country |
+-----------+----------+-----------------------+--------------+---------+----------+---------+---------+
| B001      | Procurez | 12 Sunbay street      | Glainsville  |    1233 | Florida  | 3500.00 | NULL    |
| B002      | BMW      | 6 Rocky creek         | Jacksonville |    1234 | Florida  | 3488.00 | NULL    |
| B004      | Teoco    | 1243 Princeton circle | Fairfax      |    3433 | Virginia | 4433.00 | NULL    |
+-----------+----------+-----------------------+--------------+---------+----------+---------+---------+
3 rows in set (0.00 sec)
                        
                    

Drop above created Client_View view.

                    
DROP VIEW Client_View;
                    
                

Select the average salaries of each department where DeptNo is either 'D001' or 'D002' from EMPLOYEE table.

Select the name of the lowest paid employee from EMPLOYEE table.

Select the highest paid employee from EMPLOYEE table.

                    
SELECT * FROM EMPLOYEE WHERE Basic_Sal = (SELECT MAX(Basic_Sal) FROM EMPLOYEE);
                    
                

Display the total number of rows in the EMPLOYEE table.

Display only those jobs where max sal >= 3000 in the employee table.

List the name of employees whose hiredate is in the month of December using Substr function.

Count the number of people in department number 'D003'.

Produce the following output using EMPLOYEE table:

• EMPLOYEE
  Kim(Manager)
  Bruce(Analyst)

Find out the difference between highest and lowest salaries.

                    
SELECT MAX(Basic_Sal) - MIN(Basic_Sal) AS Difference FROM EMPLOYEE;
                    
                

How many months has the manager worked for the company (Round value).

Display the top three earners with name and salary in the company.

Display the Sin and Sinh value of 45.

Display the ASCII value of "ORACLE".

Display all job types in smaller case from EMPLOYEE TABLE.

Select those clients whose Bal_Due is greater than value 2000 from Client_Mast table.

Count the total number of orders from Product_Mast table.

Calculate the average price of all the products from Products_Mast table.

Calculate the maximum and minimum product prices and rename the output as max_price and min_price respectively from product_mast table.

Count the number of products having price greater than or equal to 1200.

Display the employee number and day on which employee hired from EMPLOYEE table.

Display the date, 20 days after today's date.

Count all distinct job types from employee table.

Using LPAD function pad the job types with 15 characters by '*' symbol.

Display the length of each employee name from employee table.

Display the value of 3 raised of 4 using power function.

Dislay the square root of 81.

Display the number of bytes used by ename from employee table.

Display the ename of those who are having sound like Kim from EMPLOYEE table.

Select the average salaries of each department where Dept_No is either 'D001' or 'D002'.

Find average salary per job in each department_no.

Find avg, min, max salaries of employee for each dept_no.

Display the dept_no where more thatn two clerks are wokring.

Who was the first employee hired in each department_no?

Display the average monthly salary bill for each job type within a department_no.

Find all departments which have more than 3 employees.

Display only those jobs where max sal >= 3000;

Find out the employees who are doing the same job as 'KIM'.

List the emp_name and minimum salary earned by employee in each dept_no.

List names of employees who do not work in the marketing department.

Select all the fields of the table Dept and the name from EMPLOYEE for those employees who has basic_sal less than the average salary.

List all the employees in descending order of salaries where the first employee should be the last one to join the company.