Accessing MySQL Database and SQL Querying

Introduction

In this hands-on project, you will embark on a journey to master the art of SQL querying by working with the personnel database and its emp table. Through a series of practical exercises, you will acquire the skills necessary to retrieve, filter, and analyze employee data effectively.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access the MySQL database using the terminal
• How to import a SQL file into the MySQL database
• How to query all employee information from the emp table and sort the results
• How to query the department number and total number of employees for departments with more than 4 employees
• How to query the employee with the highest salary in department 10

🏆 Achievements

After completing this project, you will be able to:

• Understand how to work with MySQL databases using the terminal
• Perform basic SQL queries to retrieve and analyze data
• Apply sorting, grouping, and filtering techniques to extract specific information from a database

Skills Graph

Access MySQL and Import the Database

In this step, you will learn how to access the MySQL database and import the personnel.sql file.

1. Open the terminal and start the MySQL service using the following command:

sudo service mysql start

2. Access the MySQL client using the following command:

sudo mysql

3. Import the personnel.sql file into the MySQL database using the following command:

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

This will import the personnel database and the emp table into your MySQL environment.

Write the Query

In this step, you will learn to write three queries, how to query all employees from the emp table and sort the results, how to query the department number and the total number of employees for departments with more than 4 employees, and how to query the highest paid employee in department 10.

1. Create a new file named select_group.sql in the ~/project directory.

2. In the select_group.sql file, add the following SQL query:

3. This query will return all the employee information from the emp table, sorted by department number in ascending order and salary in descending order.

   SELECT * FROM emp
   ORDER BY deptno ASC, sal DESC;

4. This query will return the department number and the total number of employees for the departments with more than 4 employees. The GROUP BY clause is used to group the employees by department number, and the HAVING clause is used to filter the results to only include departments with more than 4 employees.

   SELECT deptno, COUNT(*) AS total_employees
   FROM emp
   GROUP BY deptno
   HAVING total_employees > 4;

5. This query will return the employee number, name, department number, and job information of the employee with department number 10 and the highest salary. The WHERE clause is used to filter the results to only include employees with department number 10, the ORDER BY clause is used to sort the results by salary in descending order, and the LIMIT 1 clause is used to return only the first result, which will be the employee with the highest salary.

   SELECT empno, ename, deptno, job
   FROM emp
   WHERE deptno = 10
   ORDER BY sal DESC
   LIMIT 1;

   Save the file.

Run the SQL Script

In this step, you will learn how to run the SQL script.

1. In the MySQL terminal, run the select_group.sql script:

   source /home/labex/project/select_group.sql;

   This will execute the SQL statement in the select_group.sql file and display the result.

   Example output:

   MariaDB [personnel]> source /home/labex/project/select_group.sql;
   +-------+--------+-----------+------+------------+---------+---------+--------+
   | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
   +-------+--------+-----------+------+------------+---------+---------+--------+
   |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
   |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
   |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 3000.00 |    NULL |     20 |
   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
   |  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100.00 |    NULL |     20 |
   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
   |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
   |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
   |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
   |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
   |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
   |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
   +-------+--------+-----------+------+------------+---------+---------+--------+
   14 rows in set (0.000 sec)
   
   +--------+-----------------+
   | deptno | total_employees |
   +--------+-----------------+
   |     20 |               5 |
   |     30 |               6 |
   +--------+-----------------+
   2 rows in set (0.000 sec)
   
   +-------+-------+--------+-----------+
   | empno | ename | deptno | job       |
   +-------+-------+--------+-----------+
   |  7839 | KING  |     10 | PRESIDENT |
   +-------+-------+--------+-----------+
   1 row in set (0.000 sec)

Summary

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