450+ Practice Questions From Pandas, NumPy, and SQL.
Introduction
This notebook has been created for you to practice three of the most common tools used in building any machine learning or data science applications, i.e., Pandas, NumPy, and SQL!
The practice questions provided will serve as a great resource for those who are looking to familiarize themselves with some of the most common functions used in these tools.
Appropriate descriptions have been provided for all the questions in this entire exercise which will help you navigate through this exercise easily. If a dataset is to be loaded in the python environment, that has also been provided for you. You can find it on the right panel in the Files section. Do NOT delete any of the files/folders listed there.
The whole exercise has been divided into nine separate notebooks. Below are the links to all the other notebooks for you to jump from one notebook to another:
How to use this notebook?
At the top right corner, you will find a Duplicate button. This will allow you to create a unique notebook for your own practice and write solutions to the question listed in this notebook.
Let's begin 🚀!
SQL Notebook 1
import pandas as pd
data = pd.read_csv("employee_dataset.csv")
data.head()
Sample Query
select * from data; -- select all rows and columns from the data.
1. Print the first five rows
SELECT * FROM data LIMIT 5;
2. Print the number of rows
select count(*) as row_count from data;
3. Print the first five rows of the Company_Name and Employment_Status Column
select Company_Name,Employment_Status from data LIMIT 5;
4. Print the first five rows where the Employee_Rating > 4.5
SELECT * FROM data where Employee_Rating>4.5 LIMIT 5;
5. Print the number of rows having Employee_Salary > 600000
select count(*) as row_count from data where Employee_Salary>600000;
6. Print the number of rows with Employee_Salary > 600000 and Employee_Rating > 4.5
select count(*) as row_count from data where Employee_Salary>600000 and Employee_Rating>4.5;
7. Print the first five rows with Employee_Salary > 600000 and Employee_Rating > 4.5
select * from data where Employee_Salary>600000 and Employee_Rating>4.5 LIMIT 5;
8. Print all the distinct companies in the dataset
select distinct Company_Name from data
9. Print the number of distinct companies in the dataset
select count(distinct Company_Name) as num_dis_comp from data;
10. Print all the distinct companies, city pairs in the dataset
select DISTINCT Company_Name,Employee_City from data;
11. Print the number of distinct companies, city pairs in the dataset
SELECT count(DISTINCT (Company_Name,Employee_City)) as cd from data;
12. Print the number of Full time employees in the dataset.
select count(*) as num_full_time_emp from data where Employment_Status='Full Time';
13. Print the number of employees with job title either 'Production engineer' or 'New Russellton'.
SELECT count(*) as num_of_emp from data where Employee_Job_Title='Production engineer' or Employee_Job_Title='Russelton';
14. Print the number of employees with job title either 'Production engineer' and company name 'Scott Inc'
SELECT count(*) as num_of_emp_job from data where Employee_Job_Title='Production engineer' and Company_Name='Scott Inc';
15. Print the number of employees with job title either 'Production engineer' or 'New Russellton' and company name either 'Scott Inc' or 'Baker, Allen and Edwards'.
SELECT count(*) as count from data where Employee_Job_Title='Production engineer' or Employee_Job_Title='New Russelton' and Company_Name='Scott Inc' or Company_Name='Baker, Allen and Edwards';
16. Print the number of distinct cities with employees having job title either 'Production engineer' or 'New Russellton' and company name either 'Scott Inc' or 'Baker, Allen and Edwards'.
SELECT count(distinct (Employee_City,Employee_Job_Title)) from data where Employee_Job_Title='Production engineer' or Employee_Job_Title='New Russellton' and Company_Name='Scott Inc' or Company_Name='Baker, Allen and Edwards';
17. Print the number of Intern employees in the dataset.
SELECT count(*) as ci from data where Employee_Job_Title='Intern';
18. Print the number of employees with first name 'Matthew'.
SELECT COUNT(*) AS num_matthews
FROM data
WHERE SUBSTRING(name FROM 1 FOR POSITION(' ' IN name) - 1) = 'Matthew';
19. Print the first five rows corresponding to the employees with highest salary
SELECT *
FROM (
SELECT *
FROM data
WHERE Employee_Salary IN (SELECT DISTINCT Employee_Salary FROM data ORDER BY Employee_Salary DESC LIMIT 5)
) AS highest_salaries
ORDER BY Employee_Salary DESC LIMIT 5;
20. Print the first five rows corresponding to the employees with the highest salary in 'James and Sons' company
SELECT DISTINCT Name,Company_Name,Employee_Salary from data where data.Company_Name='James and Sons' ORDER BY Employee_Salary DESC limit 5;
21. Print the first five rows corresponding to the employees with the highest salary working either in 'James and Sons' company or living in 'Wardfort' city
select DISTINCT Name,Company_Name,Employee_Salary,Employee_City from data where data.Company_Name='James and Sons' OR data.Employee_City='Wardfort' ORDER BY Employee_Salary DESC LIMIT 5;
22. Print the total number of distinct records in the data.
SELECT COUNT(*) AS total_distinct_records
FROM (
SELECT DISTINCT * FROM data
);
23. Print the mean salary of all the employees in the data
SELECT AVG(Employee_Salary) AS mean_salary from data;
24. Print the mean rating of all the employees in the data
select avg(Employee_Rating) as mean_rating from data;
25-27. Print the maximum, minimum and median Employee_Salary.
1. maximum salary
SELECT max(Employee_Salary) as max_sal from data;
2. minimum salary
SELECT min(Employee_Salary) as min_sal from data;
3. median of salary
28-32. Print the distribution of the following columns: (the frequency of individual entries).
1. Company_Name
2. Employee_Job_Title
3. Employee_City
4. Employee_Country
5. Employment_Status