Sure, here are some beginner-level and intermediate-level SQL questions:
### Beginner-Level SQL Questions:
1. Basic Select Queries:
- Retrieve all columns from the
- Retrieve only the
2. Filtering Data:
- Retrieve all customers who live in the city 'New York'.
- Retrieve all orders with a total amount greater than 100.
3. Sorting Data:
- Retrieve all customers sorted by their name in ascending order.
- Retrieve all products sorted by their price in descending order.
4. Aggregation Functions:
- Calculate the total number of orders in the
- Calculate the average price of products in the
5. Joins:
- Retrieve all orders along with the corresponding customer details.
- Retrieve all orders along with the corresponding product details.
### Intermediate-Level SQL Questions:
1. Subqueries:
- Retrieve all customers who have placed orders.
- Retrieve all products with a price higher than the average price.
2. Grouping and Aggregation:
- Calculate the total sales amount for each customer.
- Calculate the total number of orders placed by each customer.
3. Advanced Joins:
- Retrieve all customers along with the total number of orders they have placed.
- Retrieve all customers who have not placed any orders.
4. Data Modification:
- Update the price of a specific product in the
- Insert a new record into the
5. Constraints and Indexing:
- Add a unique constraint on the email column in the
- Create an index on the product name column in the
6. Views and Stored Procedures:
- Create a view that shows the total sales amount for each product.
- Create a stored procedure that inserts a new customer into the
These questions cover a range of SQL concepts and tasks, providing opportunities for beginners to practice basic queries and for intermediate-level users to tackle more complex data manipulation and analysis tasks.
### Beginner-Level SQL Questions:
1. Basic Select Queries:
- Retrieve all columns from the
customers table.- Retrieve only the
name and email columns from the customers table.2. Filtering Data:
- Retrieve all customers who live in the city 'New York'.
- Retrieve all orders with a total amount greater than 100.
3. Sorting Data:
- Retrieve all customers sorted by their name in ascending order.
- Retrieve all products sorted by their price in descending order.
4. Aggregation Functions:
- Calculate the total number of orders in the
orders table.- Calculate the average price of products in the
products table.5. Joins:
- Retrieve all orders along with the corresponding customer details.
- Retrieve all orders along with the corresponding product details.
### Intermediate-Level SQL Questions:
1. Subqueries:
- Retrieve all customers who have placed orders.
- Retrieve all products with a price higher than the average price.
2. Grouping and Aggregation:
- Calculate the total sales amount for each customer.
- Calculate the total number of orders placed by each customer.
3. Advanced Joins:
- Retrieve all customers along with the total number of orders they have placed.
- Retrieve all customers who have not placed any orders.
4. Data Modification:
- Update the price of a specific product in the
products table.- Insert a new record into the
customers table.5. Constraints and Indexing:
- Add a unique constraint on the email column in the
customers table.- Create an index on the product name column in the
products table.6. Views and Stored Procedures:
- Create a view that shows the total sales amount for each product.
- Create a stored procedure that inserts a new customer into the
customers table.These questions cover a range of SQL concepts and tasks, providing opportunities for beginners to practice basic queries and for intermediate-level users to tackle more complex data manipulation and analysis tasks.
Sure, here are some beginner-level and intermediate-level SQL questions:
### Beginner-Level SQL Questions:
1. Basic Select Queries:
- Retrieve all columns from the
- Retrieve only the
2. Filtering Data:
- Retrieve all customers who live in the city 'New York'.
- Retrieve all orders with a total amount greater than 100.
3. Sorting Data:
- Retrieve all customers sorted by their name in ascending order.
- Retrieve all products sorted by their price in descending order.
4. Aggregation Functions:
- Calculate the total number of orders in the
- Calculate the average price of products in the
5. Joins:
- Retrieve all orders along with the corresponding customer details.
- Retrieve all orders along with the corresponding product details.
### Intermediate-Level SQL Questions:
1. Subqueries:
- Retrieve all customers who have placed orders.
- Retrieve all products with a price higher than the average price.
2. Grouping and Aggregation:
- Calculate the total sales amount for each customer.
- Calculate the total number of orders placed by each customer.
3. Advanced Joins:
- Retrieve all customers along with the total number of orders they have placed.
- Retrieve all customers who have not placed any orders.
4. Data Modification:
- Update the price of a specific product in the
- Insert a new record into the
5. Constraints and Indexing:
- Add a unique constraint on the email column in the
- Create an index on the product name column in the
6. Views and Stored Procedures:
- Create a view that shows the total sales amount for each product.
- Create a stored procedure that inserts a new customer into the
These questions cover a range of SQL concepts and tasks, providing opportunities for beginners to practice basic queries and for intermediate-level users to tackle more complex data manipulation and analysis tasks.
### Beginner-Level SQL Questions:
1. Basic Select Queries:
- Retrieve all columns from the
customers table.- Retrieve only the
name and email columns from the customers table.2. Filtering Data:
- Retrieve all customers who live in the city 'New York'.
- Retrieve all orders with a total amount greater than 100.
3. Sorting Data:
- Retrieve all customers sorted by their name in ascending order.
- Retrieve all products sorted by their price in descending order.
4. Aggregation Functions:
- Calculate the total number of orders in the
orders table.- Calculate the average price of products in the
products table.5. Joins:
- Retrieve all orders along with the corresponding customer details.
- Retrieve all orders along with the corresponding product details.
### Intermediate-Level SQL Questions:
1. Subqueries:
- Retrieve all customers who have placed orders.
- Retrieve all products with a price higher than the average price.
2. Grouping and Aggregation:
- Calculate the total sales amount for each customer.
- Calculate the total number of orders placed by each customer.
3. Advanced Joins:
- Retrieve all customers along with the total number of orders they have placed.
- Retrieve all customers who have not placed any orders.
4. Data Modification:
- Update the price of a specific product in the
products table.- Insert a new record into the
customers table.5. Constraints and Indexing:
- Add a unique constraint on the email column in the
customers table.- Create an index on the product name column in the
products table.6. Views and Stored Procedures:
- Create a view that shows the total sales amount for each product.
- Create a stored procedure that inserts a new customer into the
customers table.These questions cover a range of SQL concepts and tasks, providing opportunities for beginners to practice basic queries and for intermediate-level users to tackle more complex data manipulation and analysis tasks.
Sure, here's a sample DataFrame with some intermediate-level questions for practice:
Output:
Now, here are some intermediate-level questions for practicing with this DataFrame:
1. Handling Missing Values:
- Identify and count the missing values in the DataFrame.
- Remove rows with missing values.
2. Aggregation and Grouping:
- Calculate the total sales and profit for each product.
- Calculate the average sales and profit for each region.
3. Data Manipulation:
- Convert the 'Date' column to the month format and create a new column 'Month'.
- Create a new column 'Profit Margin' calculated as (Profit / Sales) * 100.
4. Data Filtering:
- Filter the DataFrame to include only rows where sales are greater than the mean sales.
5. Data Sorting:
- Sort the DataFrame based on the 'Profit'
import pandas as pd
# Sample DataFrame
data = {
'Product': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'],
'Region': ['North', 'North', 'North', 'South', 'South', 'South', 'East', 'East', 'East'],
'Sales': [100, 150, 200, 120, 180, 220, 80, 130, 190],
'Profit': [20, 30, 40, 25, 35, 45, 15, 25, 35],
'Date': ['2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01']
}
df = pd.DataFrame(data)
# Convert 'Date' column to datetime format
df['Date'] = pd.to_datetime(df['Date'])
# Add additional rows for demonstration purposes
df = pd.concat([df]*3, ignore_index=True)
# Add missing values for demonstration purposes
df.loc[10, 'Sales'] = None
df.loc[11, 'Profit'] = None
print(df)
Output:
Product Region Sales Profit Date
0 A North 100.0 20.0 2023-01-01
1 B North 150.0 30.0 2023-01-01
2 C North 200.0 40.0 2023-01-01
3 A South 120.0 25.0 2023-01-01
4 B South 180.0 35.0 2023-01-01
5 C South 220.0 45.0 2023-01-01
6 A East 80.0 15.0 2023-01-01
7 B East 130.0 25.0 2023-01-01
8 C East 190.0 35.0 2023-01-01
9 A North 100.0 20.0 2023-01-01
10 B North NaN 30.0 2023-01-01
11 C North 200.0 NaN 2023-01-01
12 A South 120.0 25.0 2023-01-01
13 B South 180.0 35.0 2023-01-01
14 C South 220.0 45.0 2023-01-01
15 A East 80.0 15.0 2023-01-01
16 B East 130.0 25.0 2023-01-01
17 C East 190.0 35.0 2023-01-01
18 A North 100.0 20.0 2023-01-01
19 B North 150.0 30.0 2023-01-01
20 C North 200.0 40.0 2023-01-01
21 A South 120.0 25.0 2023-01-01
22 B South 180.0 35.0 2023-01-01
23 C South 220.0 45.0 2023-01-01
24 A East 80.0 15.0 2023-01-01
25 B East 130.0 25.0 2023-01-01
26 C East 190.0 35.0 2023-01-01
Now, here are some intermediate-level questions for practicing with this DataFrame:
1. Handling Missing Values:
- Identify and count the missing values in the DataFrame.
- Remove rows with missing values.
2. Aggregation and Grouping:
- Calculate the total sales and profit for each product.
- Calculate the average sales and profit for each region.
3. Data Manipulation:
- Convert the 'Date' column to the month format and create a new column 'Month'.
- Create a new column 'Profit Margin' calculated as (Profit / Sales) * 100.
4. Data Filtering:
- Filter the DataFrame to include only rows where sales are greater than the mean sales.
5. Data Sorting:
- Sort the DataFrame based on the 'Profit'
Sure, here's a sample DataFrame with some intermediate-level questions for practice:
Output:
Now, here are some intermediate-level questions for practicing with this DataFrame:
1. Handling Missing Values:
- Identify and count the missing values in the DataFrame.
- Remove rows with missing values.
2. Aggregation and Grouping:
- Calculate the total sales and profit for each product.
- Calculate the average sales and profit for each region.
3. Data Manipulation:
- Convert the 'Date' column to the month format and create a new column 'Month'.
- Create a new column 'Profit Margin' calculated as (Profit / Sales) * 100.
4. Data Filtering:
- Filter the DataFrame to include only rows where sales are greater than the mean sales.
5. Data Sorting:
- Sort the DataFrame based on the 'Profit'
import pandas as pd
# Sample DataFrame
data = {
'Product': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'],
'Region': ['North', 'North', 'North', 'South', 'South', 'South', 'East', 'East', 'East'],
'Sales': [100, 150, 200, 120, 180, 220, 80, 130, 190],
'Profit': [20, 30, 40, 25, 35, 45, 15, 25, 35],
'Date': ['2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01', '2023-01-01']
}
df = pd.DataFrame(data)
# Convert 'Date' column to datetime format
df['Date'] = pd.to_datetime(df['Date'])
# Add additional rows for demonstration purposes
df = pd.concat([df]*3, ignore_index=True)
# Add missing values for demonstration purposes
df.loc[10, 'Sales'] = None
df.loc[11, 'Profit'] = None
print(df)
Output:
Product Region Sales Profit Date
0 A North 100.0 20.0 2023-01-01
1 B North 150.0 30.0 2023-01-01
2 C North 200.0 40.0 2023-01-01
3 A South 120.0 25.0 2023-01-01
4 B South 180.0 35.0 2023-01-01
5 C South 220.0 45.0 2023-01-01
6 A East 80.0 15.0 2023-01-01
7 B East 130.0 25.0 2023-01-01
8 C East 190.0 35.0 2023-01-01
9 A North 100.0 20.0 2023-01-01
10 B North NaN 30.0 2023-01-01
11 C North 200.0 NaN 2023-01-01
12 A South 120.0 25.0 2023-01-01
13 B South 180.0 35.0 2023-01-01
14 C South 220.0 45.0 2023-01-01
15 A East 80.0 15.0 2023-01-01
16 B East 130.0 25.0 2023-01-01
17 C East 190.0 35.0 2023-01-01
18 A North 100.0 20.0 2023-01-01
19 B North 150.0 30.0 2023-01-01
20 C North 200.0 40.0 2023-01-01
21 A South 120.0 25.0 2023-01-01
22 B South 180.0 35.0 2023-01-01
23 C South 220.0 45.0 2023-01-01
24 A East 80.0 15.0 2023-01-01
25 B East 130.0 25.0 2023-01-01
26 C East 190.0 35.0 2023-01-01
Now, here are some intermediate-level questions for practicing with this DataFrame:
1. Handling Missing Values:
- Identify and count the missing values in the DataFrame.
- Remove rows with missing values.
2. Aggregation and Grouping:
- Calculate the total sales and profit for each product.
- Calculate the average sales and profit for each region.
3. Data Manipulation:
- Convert the 'Date' column to the month format and create a new column 'Month'.
- Create a new column 'Profit Margin' calculated as (Profit / Sales) * 100.
4. Data Filtering:
- Filter the DataFrame to include only rows where sales are greater than the mean sales.
5. Data Sorting:
- Sort the DataFrame based on the 'Profit'
Sure, here are some practice questions related to subqueries:
Beginner Level:
1. Retrieve the names of individuals who are from the same city as 'Bob'.
2. Retrieve the names of individuals whose salary is greater than the average salary of all individuals.
3. Retrieve the cities where individuals aged 30 or older reside.
4. Retrieve the names of individuals who are older than 'Alice'.
5. Retrieve the names of individuals whose salary is greater than the salary of 'David'.
Intermediate Level:
1. Retrieve the names of individuals who are from cities where the average salary is greater than $80,000.
2. Retrieve the names of individuals who are older than the average age of individuals from cities where 'Charlie' resides.
3. Retrieve the names of individuals who are from cities where the total number of individuals is more than 2.
4. Retrieve the names of individuals who are younger than the oldest individual.
5. Retrieve the names of individuals whose salary is within $10,000 of the highest salary.
These questions should help you practice using subqueries in SQL. Feel free to give them a try, and if you need any assistance or have any questions, feel free to ask!
Beginner Level:
1. Retrieve the names of individuals who are from the same city as 'Bob'.
2. Retrieve the names of individuals whose salary is greater than the average salary of all individuals.
3. Retrieve the cities where individuals aged 30 or older reside.
4. Retrieve the names of individuals who are older than 'Alice'.
5. Retrieve the names of individuals whose salary is greater than the salary of 'David'.
Intermediate Level:
1. Retrieve the names of individuals who are from cities where the average salary is greater than $80,000.
2. Retrieve the names of individuals who are older than the average age of individuals from cities where 'Charlie' resides.
3. Retrieve the names of individuals who are from cities where the total number of individuals is more than 2.
4. Retrieve the names of individuals who are younger than the oldest individual.
5. Retrieve the names of individuals whose salary is within $10,000 of the highest salary.
These questions should help you practice using subqueries in SQL. Feel free to give them a try, and if you need any assistance or have any questions, feel free to ask!
Certainly! Below is an example of how you can create a dataframe using SQL and some practice questions suitable for beginners and intermediate learners:
Practice Questions:
Beginner Level:
1. Retrieve all columns for all rows from the dataframe.
2. Retrieve only the names of all individuals from the dataframe.
3. Retrieve the total number of rows in the dataframe.
4. Retrieve the details of individuals who are younger than 35.
5. Retrieve the names and cities of all individuals.
Intermediate Level:
1. Retrieve the average age of individuals in the dataframe.
2. Retrieve the maximum salary from the dataframe.
3. Retrieve the names and ages of individuals who earn more than $80,000.
4. Retrieve the names and salaries of individuals in descending order of salary.
5. Update the salary of 'David' to $95,000.
Feel free to try writing SQL queries to answer these questions! If you need assistance or have any questions, feel free to ask.
-- Create a temporary table to simulate a dataframe
CREATE TEMP TABLE practice_dataframe (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50),
salary DECIMAL(10, 2)
);
-- Insert sample data into the dataframe
INSERT INTO practice_dataframe (name, age, city, salary) VALUES
('Alice', 25, 'New York', 60000.00),
('Bob', 30, 'Los Angeles', 70000.00),
('Charlie', 35, 'Chicago', 80000.00),
('David', 40, 'Houston', 90000.00),
('Emma', 45, 'San Francisco', 100000.00);
Practice Questions:
Beginner Level:
1. Retrieve all columns for all rows from the dataframe.
2. Retrieve only the names of all individuals from the dataframe.
3. Retrieve the total number of rows in the dataframe.
4. Retrieve the details of individuals who are younger than 35.
5. Retrieve the names and cities of all individuals.
Intermediate Level:
1. Retrieve the average age of individuals in the dataframe.
2. Retrieve the maximum salary from the dataframe.
3. Retrieve the names and ages of individuals who earn more than $80,000.
4. Retrieve the names and salaries of individuals in descending order of salary.
5. Update the salary of 'David' to $95,000.
Feel free to try writing SQL queries to answer these questions! If you need assistance or have any questions, feel free to ask.