Comprehensive MySQL Data Manipulation

Introduction

In this project, you will learn how to perform comprehensive DML (Data Manipulation Language) operations on the Employees table (emp) in the Employees database. You will learn how to insert new records, update existing records, and delete records from the table.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data from a SQL file into the MySQL database
• How to insert new records into the emp table using a single SQL statement
• How to update the supervisor of an employee in the emp table
• How to update all records in the emp table with a NULL comm value to set the comm to 0
• How to delete the record with the highest employee number from the emp table
• How to delete all employees who directly report to a specific manager in the emp table

🏆 Achievements

After completing this project, you will be able to:

• Perform comprehensive DML operations on a MySQL database table
• Understand how to use SQL statements to insert, update, and delete data
• Gain experience in managing and manipulating data in a relational database
• Develop problem-solving skills by completing the various tasks in the project

Access MySQL and Import Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from /home/labex/project/personnel.sql into the personnel database.

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. Import the data from /home/labex/project/personnel.sql into the personnel database:

SOURCE /home/labex/project/personnel.sql;

4. Switch to the personnel database:

USE personnel;

Insert New Records

In this step, you will learn how to add two new records to the emp table using a single SQL statement.

Add the following two records to the emp table:

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (9878, 'JOHN', 'CLERK', 7499, '2022-01-05', 1800, 0, 10),
       (8868, 'JANE', 'CLERK', 7566, '2022-02-12', 2500, NULL, 20);

Update Employee Supervisor

In this step, you will learn how to change the direct manager of employee SCOTT in the emp table to the direct manager of employee BLAKE.

UPDATE emp
SET mgr = (SELECT mgr
           FROM emp
           WHERE ename = 'BLAKE')
WHERE ename = 'SCOTT';

Update Null Commission Values

In this step, you will learn how to update all entries in the emp table which have a comm value of NULL to set the comm as 0.

UPDATE emp
SET comm = 0
WHERE comm IS NULL;

Delete the Highest Employee Number

In this step, you will learn how to remove the entry with the highest employee number from the emp table.

DELETE FROM emp
WHERE empno = (SELECT MAX(empno) FROM emp);

Delete Employees Reporting to BLAKE

In this step, you will learn how to delete all employees who directly report to BLAKE in the emp table.

DELETE FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'BLAKE');

After the above steps, you can refer to the following results to verify:

MariaDB [personnel]> select * from emp;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | 0.00 |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | 0.00 |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | 0.00 |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | 0.00 |     10 |
|  7788 | SCOTT  | ANALYST   | 7839 | 1987-06-13 | 3000.00 | 0.00 |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | 0.00 |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100.00 | 0.00 |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | 0.00 |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | 0.00 |     10 |
|  8868 | JANE   | CLERK     | 7566 | 2022-02-12 | 2500.00 | 0.00 |     20 |
+-------+--------+-----------+------+------------+---------+------+--------+
10 rows in set (0.000 sec)

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.