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'