Sure, here are some beginner-level and intermediate-level SQL questions:

### 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.

Beginner

Sure, here's a sample DataFrame with some intermediate-level questions for practice:

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:

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!

Certainly! Below is an example of how you can create a dataframe using SQL and some practice questions suitable for beginners and intermediate learners:

-- 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.

-- 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);

--select name
--from practice_dataframe
--where city = (
-- select city
--from practice_dataframe
--where name = 'Bob'
--);

--select id, name, salary
--from practice_dataframe
--where salary > (
-- select avg(salary)
-- from practice_dataframe )


select distinct city
from practice_dataframe
where age >= 30;


select name, age
from practice_dataframe
where age > (
select age
from practice_dataframe
where name = 'Alice'
);


select name, age, salary
from practice_dataframe
where salary > (
select salary
from practice_dataframe
where name = 'David');



select * from practice_dataframe;


select name, city, salary
from practice_dataframe
where salary > (
select avg(salary)
from practice_dataframe
where salary > 80000
);

-- in means in cities who can get

SELECT name
FROM practice_dataframe
WHERE city IN (
SELECT city
FROM practice_dataframe
GROUP BY city
HAVING AVG(salary) > 80000
);


select name, age
from practice_dataframe
where age > (
select avg(age)
from practice_dataframe
where name = 'Charlie' );




SELECT name
FROM practice_dataframe
WHERE age > (
SELECT AVG(age)
FROM practice_dataframe
WHERE city = (
SELECT city
FROM practice_dataframe
WHERE name = 'Charlie'
)
);


WITH CityIndividualCounts AS (
SELECT city, COUNT(*) AS total_individuals
FROM practice_dataframe
GROUP BY city
HAVING COUNT(*) > 2
)

SELECT i.name
FROM practice_dataframe i
JOIN CityIndividualCounts cic ON i.city = cic.city;




SELECT name
FROM practice_dataframe
WHERE age < (
SELECT MAX(age)
FROM practice_dataframe
);

-- cumulative salary order by age
select name, age, salary,
SUM(Salary) over (order by age asc) as cumulative_salary
from practice_dataframe;


--second largest salary
select name, salary
from practice_dataframe
order by salary desc
limit 1 offset 1;

--finding out the difference
with dif_sal as (
select max(salary) as max_salary, min(salary) as min_salary
from practice_dataframe
)

select max_salary, min_salary, (max_salary - min_salary) as difference_sal
from dif_sal;
--within 10 000
SELECT name, salary
FROM practice_dataframe i
WHERE EXISTS (
SELECT 1
FROM practice_dataframe
WHERE salary BETWEEN (SELECT MAX(salary) - 10000 FROM practice_dataframe) AND (SELECT MAX(salary) FROM practice_dataframe)
AND name = i.name
);

.info() number of observations

977667170 22147