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
Skills Graph
%%%%{init: {'theme':'neutral'}}%%%%
flowchart RL
mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"])
sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"])
sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"])
sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"])
sql(("`SQL`")) -.-> sql/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"])
mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`")
sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`")
sql/DataManipulationandQueryingGroup -.-> sql/where("`WHERE clause`")
sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`")
mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`")
sql/DataDefinitionandIntegrityGroup -.-> sql/data_types("`Data Types`")
sql/DatabaseManagementandOptimizationGroup -.-> sql/using_indexes("`Using Indexes`")
subgraph Lab Skills
mysql/source -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
sql/select -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
sql/where -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
sql/in -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
mysql/select -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
sql/data_types -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
sql/using_indexes -.-> lab-301336{{"`SQL Querying for Employee Data`"}}
end
Start MySQL and Import Data
In this step, you will learn how to start the MySQL server and import the personnel database data.
Start the MySQL server:
sudo /etc/init.d/mysql start
Log into the MySQL terminal:
mysql -uroot
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.
Create a new file named answer.sql in the /home/labex/project directory using your preferred text editor.
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';
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.
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;
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.
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;
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.
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;
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:
We use cookies for a number of reasons, such as keeping the website reliable and secure, to improve your experience on our website and to see how you interact with it. By accepting, you agree to our use of such cookies. Privacy Policy