22 Comparison of basic operations in Pandas and SQL
Any changes you make to the code on this page, including your solutions to exercises, are temporary. If you leave or reload the page, all changes will be lost. If you would like to keep your work, copy and paste your code into a separate file or editor where it can be saved permanently.
We will use the same database as in the chapter on SQL querying. The Pandas data frames contain the same data, using the values in the id column as labels.
22.1 Subset of columns
List the last name and the first name of all employees.
22.2 Filtering rows
Which employees work at the department with ID 1?
22.3 Filtering rows, cont.
Which employees from the department with ID 1 were born after 1970?
In SQL and Pandas’ query: AND, OR and NOT
In Pandas: &, | and ~ (remember parentheses!)
22.4 Limiting the number of rows
List the last name and the first name of the employees, limit the output to 5 rows.
22.5 Unique rows
Which departments have employees that earn more than 230 NOK per hour?
22.6 Expressions with attributes
Find the first name, the last name and the monthly salary of all employees that make less than 40000 per month. (Assume that each month has 176 working hours.)
22.7 In operator
Which employees work in the departments with IDs 1 and 3?
22.8 Testing for missing data
List the last name and the first name of all supervisors.
22.9 Sorting rows
List the top 5 best paid employees.
Remember that order of rows in relations is not relevant, but we can sort them in result sets.
22.10 Summary statistics
What is the mean of the (hour) salary of all employees?
22.11 Grouping and summary statistics
For each department list the number of its employees and their average salary.
22.12 Filtering groups
What is the maximum salary in each department that has at least 5 employees?
22.13 Unions
List the titles of all departments and all projects in one table / frame.
22.14 Joins
List the first name and the last name of all employees, together with the title of their department.