# Getting Started with SQL Queries - Exercises for Beginners Part-4

Here's a selection of the most useful SQL queries every beginner must practice.

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

``````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?

``````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?

``````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?

``````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?

``````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?

``````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?

``````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?

``````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?

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

``````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?

``````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?

``````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?

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

## Q14. What is the most listed manufacture by location?

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

``````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!