Introduction

Hello Again! Welcome back to the last part of the SQL Series for Beginners.

Today we are providing you with the rest of the exercises to test yourself in SQL. We will be using the same dataset for practice (link below).

The questions range from Intermediate to Advanced level, so if you are a beginner or new to our SQL series, we highly recommend you to first start with Part 1, 2 and 3.

You can find all the relevant links below:

Dataset: Used Cars Dataset

Getting Started with SQL Queries - Exercises for Beginners Part-1

Getting Started with SQL Queries - Exercises for Beginners Part-2

Getting Started with SQL Queries - Exercises for Beginners Part-3

To learn how to run your queries on Dbeaver or Python, visit the link below:

We haven't provided you with explanations to the questions provided below, but you can still find the solutions attached. If you get stuck, revisit some of our previous SQL exercises.

So, let's get started.

Q1. Distribute the manufacturers across percentile brackets of 20 points according to the total number of vehicles for each manufacturer?

Answer:

select COUNT(id) as total_vehicle_counts ,manufacturer,
ntile(5) over( order by COUNT(id) )  rank_col
from vehicles
where  (price between 1 and 655000) and (manufacturer!="")  
group by manufacturer

Q2. Mention is the most preferred three colours across various models?

Answer:

select * 
from 
	(select COUNT(id) as
    total_vehicle_counts ,model,paint_color,
    rank() over(partition by model order by
    COUNT(id) desc)  rank_col
    from vehicles
    where  (price between 1 and 655000) and
    (model!="")  and  
    (paint_color!="") 
    group by model,paint_color) 
where rank_col<=3

Q3. What is the average price by vehicle type?

Answer:

select 
	"type", 
    round(avg(cast(price as numeric)), 2) as
    avg
from vehicles
where 
	"type" is not null and trim("type") <>
    ''
group by 
	"type"
order by 
	2 desc
limit 10;

Q4. Which manufacturers with the highest prices?

Answer:

select 
	manufacturer, 
    round(avg(cast(price as numeric)), 2) as
    avg_price
from vehicles
where 
	manufacturer is not null and
    trim(manufacturer) <> ''
group by 
	manufacturer
order by 
	2 desc
limit 10 ;

Q5. Which model with the highest prices?

Answer:

select
	manufacturer,
    model,
    round(avg(cast(price as numeric)), 2) as
    avg_price
from vehicles
where
	(manufacturer is not null and
    trim(manufacturer) <> '') and (model is
    not null and trim(model) <> '')
group by 
	manufacturer, model
order by 
	3 desc
limit 10 ;

Q6. What is the average price by year, manufacture, and model?

Answer:

select 
	manufacturer,
    model,
    "year",
    round(avg(cast(price as numeric)), 2) as avg_price
from vehicles
where 
	(manufacturer is not null and
    trim(manufacturer) <> '') 
    and (model is not null and trim(model)
    <> '') and ("year" is not null and
    trim("year") <> '')
group by 
	manufacturer, model, "year"
order by 
	1, 2, 3 desc
limit 10;

Q7. What cars are under the average price?

Answer:

select id, manufacturer, model, price
from vehicles v
where
	(manufacturer is not null and
    trim(manufacturer) <> '')
    and (model is not null and trim(model)
    <> '')and cast(price as numeric) <
    (select
		round(avg(cast(price as numeric)),
        2) as avg
	from vehicles v2
	where
		v.manufacturer = v2.manufacturer and
		v.model = v2.model
	group by
		manufacturer, model
	)
order by 2, 3
limit 10;

Q8. Which manufacturers with the highest odometer reading?

Answer:

select
	manufacturer,
    round(avg(cast(odometer as numeric)), 2)
    as avg_odometer
from
	vehicles
where
	(manufacturer is not null and
    trim(manufacturer) <> '')
    and (odometer is not null and
    trim(odometer) <> '')
group by
	manufacturer
order by
	2 desc
limit 10
;

Q9. Is there some difference between the vehicle type listed by location?

Answer:

with tab as (
	select
		state,
		"type",
		count(1) as total_by_type
from
	vehicles
where
	(state is not null and trim(state) <>
    '')
    and ("type" is not null and trim("type
    <> '')
group by
	state, "type"
)
select
	distinct state, "type", total_by_type
from
	tab v
where
	v.total_by_type = (select
    max(t.total_by_type) from tab t where
t.state = v.state)
limit 10;

Q10. What is the best-selling model of each manufacture by location? (with limit)

Answer:

with tab as (
	select
		state,
		manufacturer,
		model,
		count(1) as total_listed
	from
		vehicles
	where
		(state is not null and trim(state)
        <> '')and (manufacturer is not null
        and trim(manufacturer) <> '')
        and (model is not null and
        trim(model) <> '')
	group by
		state, manufacturer, model
)
select
	distinct state, manufacturer, model,
    total_listed
from
	tab v
where
	v.total_listed = (select
    max(t.total_listed) from tab t where
    t.state = v.state and t.manufacturer
    v.manufacturer);

Q11. Is there some price variation between similar vehicles with different colors?

Answer:

with tab as (
	select
		manufacturer, model, year,
        paint_color, round(avg(cast(price
        as numeric)), 2) as avg,
		(select
			count(1)
		from
			vehicles v2
		where
			v.manufacturer = v2.manufacturer
            and v.model =
            v2.model and v.year = v2.year
		group by manufacturer, model, year
        ) as total
	from
		vehicles v
	where
		(manufacturer is not null and
        trim(manufacturer) <> '')
        and (model is not null and
        trim(model) <> '')
        and (paint_color is not null and
        trim(paint_color) <> '')
	group by
		manufacturer, model,
        year,paint_color
)
select
	manufacturer, model, year, paint_color,
    avg
from
	tab
where
	total > 1
order by
	1, 2, 3, 4
limit 10;

Q12. Which is the best-selling color by car type?

Answer:

with tab as (
	select
		"type", paint_color,
        round(avg(cast(price as numeric)),
        2) as avg
	from
		vehicles
	where
		(type is not null and trim(type) <>
        '')and (paint_color is not null and
        trim(paint_color) <> '')
	group by
		"type", paint_color
)
select
	*
from
	tab t1
where
	t1.avg = (select max(t2.avg) from tab t2
    where t1.type = t2.type)
limit
	10
;

Q13. What location has more listed cars?

Answer:

select
	state, count(1)
from
	vehicles
group by
	state
order by
	2 desc
limit 10;

Q14. What is the most listed manufacture by location?

Answer:

with tab as(
	select
		state, manufacturer, count(1)
	from
		vehicles
	where
		(state is not null and trim(state)
        <> '')
        and (manufacturer is not null and
        trim(manufacturer) <> '')
	group by
		state, manufacturer
)
select
	state, manufacturer, count
from
	tab t1
where
	t1.count = (select max(t2.count) from
    tab t2 where t2.state =
    t1.state)
limit 10;

Q15. Which day of the week has more posts?

Answer:

select
	extract(dow from cast(posting_date as
    date)) as num_day,
    to_char(cast(posting_date as date),
    'Day') as day_of_week,count(1)
from
	vehicles
where
	posting_date is not null and
    trim(posting_date) <> ''
group by
	num_day,
	day_of_week
order by 1;

Conclusion

With this, we come to an end of our SQL Exercises for Beginners. We have covered a lot of SQL content in this tutorial.

This tutorial is not meant to be the only source of SQL knowledge, but a supplement to other SQL resources you have.

Hope this tutorial helped you to learn and polish your SQL concepts.

Thank you for your support and enthusiasm! We appreciate your interest in SQL.

Stay tuned for more such tutorials!