SQL Querying for Employee Data

Introduction

In this project, you will learn how to use SQL queries to filter and retrieve data from a database table. You will practice using the SELECT, FROM, WHERE, and LIMIT clauses, as well as various comparison and logical operators, to query the employee information stored in the emp table of the personnel database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to query the job and hire date information for a specific employee
• How to query all employee information excluding a specific department
• How to query employee information based on a salary threshold
• How to query employee information with a specific job title, and limit the results

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use SQL queries to filter and retrieve data from a database table
• Apply various SQL clauses and operators to refine your data queries
• Demonstrate your ability to write efficient and effective SQL queries to meet specific data retrieval requirements

Start MySQL and Import Data

In this step, you will learn how to start the MySQL server and import the personnel database data.

1. Start the MySQL server:

   sudo /etc/init.d/mysql start
   

2. Log into the MySQL terminal:

   mysql -uroot
   

3. Import the personnel.sql data into the MySQL database:

   SOURCE ~/project/personnel.sql
   

Now the personnel database is ready for you to query.

Query Job and Hire Date for Employee 'SMITH'

In this step, you will learn how to use the SELECT, FROM, and WHERE clauses to query the job and hire date information for the employee 'SMITH' from the emp table.

1. Create a new file named answer.sql in the /home/labex/project directory using your preferred text editor.
2. Add the following code to the answer.sql file:

-- Query the job information (`job`) and hire date (`hiredate`) of the employee `SMITH`
SELECT job, hiredate FROM emp WHERE ename = 'SMITH';

3. Save the answer.sql file.

Query Employee Information Excluding Department 20

In this step, you will learn how to use the SELECT, FROM, and WHERE clauses with the != operator to query all employee information that does not belong to department number 20.

1. Add the following code to the answer.sql file:

-- Query all employee information that does not belong to department number `deptno` 20
SELECT * FROM emp WHERE deptno != 20;

2. Save the answer.sql file.

Query Employee Information with Salary Greater Than 2000

In this step, you will learn how to use the SELECT, FROM, and WHERE clauses with the > operator to query all employee information whose salary is greater than 2000.

1. Add the following code to the answer.sql file:

-- Query all employee information whose salary (`sal`) is greater than 2000
SELECT * FROM emp WHERE sal > 2000;

2. Save the answer.sql file.

Query Sales Employee Information with Limit

In this step, you will learn how to use the SELECT, FROM, WHERE, and LIMIT clauses to query the information of all employees with the job of "sales" and limit the result to three.

1. Add the following code to the answer.sql file:

-- Query the information of all employees with the job of "sales" and limit the result to three
SELECT * FROM emp WHERE job = 'SALESMAN' LIMIT 3;

2. Save the answer.sql file.

Now you have completed the project. You can execute the answer.sql script in the MySQL terminal to see the results:

SOURCE ~/project/answer.sql

Summary

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