📘 Download PostgreSQL Notes: https://kodyfier.com/notes/database/postgresql.html ---------------------------------------------------------------------------------- In this PostgreSQL practice session, we are going to solve advanced PostgreSQL queries step by step. These queries include important SQL concepts like joins, aggregate functions, GROUP BY, HAVING clause, ORDER BY, FILTER, and more.
This video is perfect for PostgreSQL students who already know the basics and now want to test their real query-solving skills. We will first understand the query requirement, then break it down logically, and finally solve it using PostgreSQL.
If you are learning PostgreSQL, SQL, database management, backend development, or preparing for interviews, this practice session will help you understand how to combine multiple SQL concepts in one real-world query.
In this video, you will learn:
* How to approach advanced PostgreSQL queries * How to use joins in real query problems * How GROUP BY and HAVING work together * How to use aggregate functions like COUNT, SUM, AVG * How to filter and order grouped data * How to think like a database developer while solving SQL queries
Try to solve the queries by yourself before watching the solution. This is the best way to improve your PostgreSQL and SQL query writing skills.
⌛TIMELINE⏳ 0:00 Introduction to PostgreSQL Practice Session 1:00 1st Advanced Query — Theory & Requirement 2:40 Solving the 1st PostgreSQL Query 6:00 2nd Advanced PostgreSQL Query 7:10 Solving the 2nd Query Step by Step 9:20 Outro & Final Thoughts
AI Generated·May contain errors
Video Summary
AI Generated
This video is a follow-up tutorial in a PostgreSQL series, focusing on solving two advanced SQL interview questions. The instructor demonstrates how to retrieve specific data by joining multiple tables, applying aggregate functions, and using filtering clauses like WHERE and HAVING to identify popular premium courses and specific department averages.
Hello, what's up, guys? Welcome to the PostgreSQL series.
In today's video, in the previous video, I gave you some advanced questions.
From which I answered only one
And I said that you have to solve the remaining two questions by yourself.
So I'm going to solve the remaining two questions today
And I hope you have done it too
I will take you directly to our computer screen
And here you can see the questions I'm referring to.
This was your first.
This one
And then this is the third one
OK
Now you will say what is this?
So if you have forgotten, then let me remind you
The one you can see here
PostgreSQL Interview Q&A
This is the second part of this video
And it's going to be very small and simple.
But it's just as effective
Okay, so I hope you guys will at least try this
Even if you didn't do anything in the comment section
You know, I didn't post anything
But I hope you tried it yourself
So see
What was in our second question?
Let me tell you quickly
Which of our expensive courses are actually popular?
So basically we have a lot of courses
In our college management system
So which course is there?
Which is more expensive?
But it is also popular
So basically, which professors are teaching that course?
They want to give them a bonus.
Simple
So here you can see what we need
The title of the course
The name of the professor
And the total number of enrolled students
So I have to show three things
In our output table
So what are we doing in filter 1?
Only include premium courses
You see, they've kept a continuous stream of premium courses.
You know, like a rule
We will call them premium.
Which course will cost more than 3000?
More than 3,000
Or if there is a course price above it
So we have kept it in the premium category.
OK
And what does filter 2 say?
Only show courses
That have at least
That means we have to present such courses.
Who has a price higher than 3000?
And there should be at least two students in that course.
Then
Obviously
Now it is told here
You have to play with three tables
Because in yesterday's video
We saw that there were only two tables.
Which we were seeing here
Right
But today in this video
You have to play with three tables.
And by mixing all three tables
You must obtain this output.
Obviously
One will be here.
Courses
One will be yours
Users table
Which I can't see here
And obviously
Between courses and users
We have maintained a relationship.
And that's your enrollments table
Right
So I showed you both courses and enrollments.
I have pasted the code directly here
So that I don't waste your time
I'll explain it simply.
I had to show three tables
So obviously
Will come inside select
Course name
Your name
Basically
Professor's name
And we are using an aggregate function
To count the student ID
Enrollments.student_id
Right
Now which enrollments are we talking about?
I will tell you
See
From where
We have taken from courses
Now I am using an inner join
With users table
And after on
We have to set the condition.
What is common in both tables?
So the instructor ID in the courses table
It should match
In the users table
Our id
Now here
The table of their mixture will be made.
I am going to inner join it again.
With the enrollment table
Now I will say this in it
Yes
The course ID of our enrollment
It should match the course ID.
In this way
So it happened in this way
That our
Users
Courses
And between the enrollments
Communication
Has been established
A connection has been established between them.
Now I just have to filter
The first filter was this
Where
The fee for the course
It must be greater than 30,000
I mean 3000
So my work is done
Once this is done
Then I have to do grouping here
With whom
On the basis of title
And on the basis of name
We are doing it based on the name.
Because
And by the way
We are doing it based on the title.
Because
Both of these
Which is
I have used
In the SELECT statement
But
It is not within the aggregate function
So first of all
It happened that
In the aggregate function
That column
If not mentioned
Anyway
You have to keep it in the group by
And second
If you keep it in the group by
Then it is also beneficial
Because
There can be many titles.
So a combination of those titles
And
There can be many students.
So their
Basically, how many students
One student has taken
Everything will come there
Their group will be formed.
Basically
Okay
Now
Having counted
This is important
Means
In that enrollment table
Same
On the course
How many students are there?
Many students
Enrolled
So whoever
Enrolled
So we just have to see
That it should be
Greater than 2
So whatever courses
The enrollment in it
Basically
Students
All the students
Enrolled
It must be
Greater than 2
And at the end
Now when the entire result
Will be coming
So I will order it
In descending order
So the biggest
You will see at the top
Okay
If I run it
So guys
You are getting
Output like this
You can see here
So guys
You can see the output here
Advance
Look
And only so departments
Where the average
Course
Fee is strictly
Less than
Okay
Now here
We have to offer such courses.
Whose price is
Less than 4000
It will work.
No problem
Round the average
To two decimal places
Okay
No big deal
Like we have to round
And
Okay
I will keep it too.
Directly with you
Solution here
So that
It's easy for you.
And here
You can see
Look
I want the name
Of the department
Okay
We have already
Rounded it here
We have already
Seen this before
You have to basically
Remove the average
Like this
Aggregate function
Use round
By comma
Decimal places
You will see two points
Two digits
Pasted alias
From departments
We have used
Inner join
With users
Between these two
What is there?
Department's ID
Users
In the table
Department ID
Should be equal to it
Their
Result
Inner join
With courses
In that we say
User ID
Should match
Your
Courses
With their
Instructor's ID
Okay
Here
So now
After so much
Connection
Between them
The first condition is
Such professors
Who are active
So
Users.isActive
True
Group by
Dname
We have to do it.
Department's name
Because
The other
Is already
Mentioned in the
Aggregate function
Okay
Here
I have to see.
Only two names
Calculate the
Mathematical
Average
Course
Fee
For each department
Okay
Every department
Average cost
Required
Every department
Average cost
Okay
Then
We have to
Remove the average
Okay
Having
Obviously
After group by
You have to use
Average
Course
Fee
Here
We have written
See fee
Because
In the same
Department
Multiple
Courses
Can be
So
All
Courses
Average
We are
Taking out
And we
Make sure
It must be
Less than
4000
And whoever
Will come
Order
By
Ascending
Order