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

by each company