Comprehensive MySQL Database Lab

Introduction

In this project, you will learn how to access a MySQL database, import data, and perform various SQL queries to retrieve information from the emp table in the personnel database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service and access the MySQL client using the sudo command
• How to import data from a SQL script into the MySQL database
• How to write SQL queries to retrieve job information, employee names and doubled salaries, and employee numbers, names, and department numbers from the emp table

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic operations of starting and accessing a MySQL database
• Import data from a SQL script into a MySQL database
• Write SQL queries to retrieve specific information from a database table
• Apply SQL concepts such as column selection, aliasing, and data manipulation

Start MySQL and Import Data

In this step, you will learn how to start the MySQL service and import the necessary data into the database.

1. Start the MySQL service using the sudo command without any password:

sudo service mysql start

1. Access the MySQL client using the sudo command without any password:

sudo mysql

1. Import the data from the provided personnel.sql script into the MySQL database:

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

Retrieve Job Information for All Employees

In this step, you will learn how to retrieve the job information for all employees from the emp table.

1. Create a new file named answer.sql in the /home/labex/project directory.

2. In the answer.sql file, add the following SQL query to retrieve the job information for all employees:

SELECT job FROM emp;

Retrieve Employee Names and Doubled Salaries

In this step, you will learn how to retrieve the name (ename) and doubled salary (sal) for all employees in the emp table.

1. In the answer.sql file, add the following SQL query to retrieve the employee names and their salaries doubled:

SELECT ename, sal * 2 AS 'sal*2' FROM emp;

Retrieve Employee Numbers, Names, and Department Numbers

In this step, you will learn how to select all empno, ename, and deptno from the emp table, and rename ename as "Name" and deptno as "Department Number" in the output.

1. In the answer.sql file, add the following SQL query to retrieve the employee numbers, names, and department numbers:

SELECT empno, ename AS 'Name', deptno AS 'Department Number' FROM emp;

1. Save the answer.sql file.

Run the SQL Script

In this final step, you will run the SQL script you created in the previous steps.

1. In the MySQL client, run the following command to execute the answer.sql script:

SOURCE /home/labex/project/answer.sql

This will execute the SQL queries in the answer.sql file and display the results.

Congratulations! You have completed the project. You should see the following output:

MariaDB [personnel]> SOURCE /home/labex/project/answer.sql
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
14 rows in set (0.000 sec)

+--------+----------+
| ename  | sal*2    |
+--------+----------+
| SMITH  |  1600.00 |
| ALLEN  |  3200.00 |
| WARD   |  2500.00 |
| JONES  |  5950.00 |
| MARTIN |  2500.00 |
| BLAKE  |  5700.00 |
| CLARK  |  4900.00 |
| SCOTT  |  6000.00 |
| KING   | 10000.00 |
| TURNER |  3000.00 |
| ADAMS  |  2200.00 |
| JAMES  |  1900.00 |
| FORD   |  6000.00 |
| MILLER |  2600.00 |
+--------+----------+
14 rows in set (0.000 sec)

+-------+--------+-------------------+
| empno | Name   | Department Number |
+-------+--------+-------------------+
|  7369 | SMITH  |                20 |
|  7499 | ALLEN  |                30 |
|  7521 | WARD   |                30 |
|  7566 | JONES  |                20 |
|  7654 | MARTIN |                30 |
|  7698 | BLAKE  |                30 |
|  7782 | CLARK  |                10 |
|  7788 | SCOTT  |                20 |
|  7839 | KING   |                10 |
|  7844 | TURNER |                30 |
|  7876 | ADAMS  |                20 |
|  7900 | JAMES  |                30 |
|  7902 | FORD   |                20 |
|  7934 | MILLER |                10 |
+-------+--------+-------------------+
14 rows in set (0.000 sec)

Summary

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