YouTube Transcript:
Solve 70 SQL Questions in 3 hrs | Ultimate SQL Practice | Master SQL

Skip watching entire videos - get the full transcript, search for keywords, and copy with one click.

AutoDub

Understand YouTube Foreign Videos

Immersive YouTube Voice Translation

Break language barriers, embrace global quality content

Solve Foreign Video Barriers Instantly

Video Transcript

Video Summary

Summary

Core Theme

This content is a comprehensive tutorial demonstrating how to solve 70 SQL questions, ranging from easy to hard, using a practical, hands-on approach. It emphasizes the importance of practicing SQL queries manually, even with the availability of AI coding assistants, to maintain technical proficiency and prepare for interviews.

Mind Map

Click to expand

Click to explore the full interactive mind map • Zoom, pan, and navigate

hey there this is akan and welcome back

to a new video and in this video I'm

going to cover 70 SQL questions so we

are going to code together we are going

to read the question solve them write

the query and get it done so whether you

are a beginner you are in college or you

are still working database is a very

important concept uh whether you are

sitting for on campus placements you're

trying for off- campus placements or

you're trying for a switch database

concept will be asked from any software

engineer sd1 2 3 at any level you

database is a very important concept

right and in that also SQL queries are

still very important I know many of us

are writing almost all of us are using

chat GPD and deeps for making our

queries right we are using we are

dependent on co-pilots and cursors and

Devon to write esal queries for us but

still I feel if you write the query on

your own it becomes a very good exercise

for you to actually uh stay connected to

the tech right because uh you actually

use your brain to actually see which

field you need to index which column you

need to pick up how you want to join the

table how you want to get the data in

whichever format that is expected so it

is totally fine if you are dependent on

chat GPD and these agents for writing

code for you but sometimes in between it

is okay to practice these Concepts

because in interview they some

interviewer will be like uh old school

and they might ask you this uh they

might ask you to write a query right and

for such cases you should be ready

because I know agents are there but at

least for the next 5 to six years it

will be difficult to change how

interviews happen so suddenly right so

yes let's get started and do check out

the Discord server that I have created

for all of you where you can get uh some

resources you can get a place where you

can ask questions you can uh see the

opportunities talk to Founders so do

check out that server it is very

important and if you want to get on a

one-on-one uh meeting with me check out

the topmate link in the description and

if you want to get connected with me

check check out my LinkedIn page now

without wasting more time let's get

started okay so let's start and just to

let you know I'm using this platform

called SQL practice.com it's a free

platform where you can practice easy

medium and hard SQL questions so I'll be

using this platform throughout this

video now in this platform when you come

uh every uh setting is reset and you can

see that we I have got like four tables

so I have a patients table which have uh

which has these columns and I can see

there is an admissions table which has

these columns and if you want to see the

schema this is the schema diagram I have

a patient table every patients row is

having a patient and it has some uh some

of the features and I can see that uh

other tables how they are related so I

can see that every patient is going to

get admitted into the hospital and I can

see that information like the admission

date discharge date what was the disease

that the patient got diagnosed with

attending doctor ID and then you have

the doctor's table so all of these

tables are interconnected and we are

going to explore the schema in this

video so this complete database we are

going to use so you can assume that this

is some kind of hospitals database and

these are some of the tables that we

have got amazing okay next thing is

learning resources so if you are if you

a beginner in this SQL and you want to

learn you have also got some good

resources here itself to learn so you

can see query Basics so if you don't

know how select statement works or how

insert statement Works feel free to

click on it and read read about it but

in this video I'll assume that you

already know some basics of SQL and the

basic uh s queries I I may not be

explaining you in detail but we will be

solving all the questions together so if

you are stuck anywhere and you don't

know any of the uh like join or Union

you can feel free to click on it and

just give it a lighter read awesome so

let's get started the first question so

click on view all questions and you can

see that we have some easy questions we

have some medium questions so let's

start with the first question so the

first question says show first name last

name and gender of patient so it is

talking about the patients table that we

have got over here patient table so in

this patient table we have to uh bring

first name last name and gender so this

is a very simple question and the

condition is also given so whenever

there is a condition on string we can

use the where uh Clause okay whose

gender is M okay so let me clear this

and let me write the query so first of

all whenever you have to write an SQL

query start by picking the table so from

which table patient table so patient

table then you write down your condition

which is given so my condition is where

my gender gender is equal to M that is

male so I have to pick all the male

patients and then you write down which

all columns you need to show in your

output so select those columns so select

first name last name and gender so this

should be it and once you hit control

enter your query will be run uh and you

can see that I wrote the correct uh

query great now let's go to the next

question so you can see that first

question is completed now let's go to

the next

question next question is show first

name and last name of patients who does

not have allergies so if you go to this

allergies you can say it's text and if I

actually uh show you you can see this uh

allergies so what I can do is so I can

say that if my allergies column is null

that means that patient does not have

the allergy okay so from patient itself

where allergies is equal to is null

right is null there uh all those rows I

have to pick and I don't need to show

gender I only need to show first name

and last name hit control enter and you

can see that this question is also right

so I just went to that table I wrote the

condition where my allergies is null and

then I selected those respective columns

which I needed to show in my output

great like next

question show first name of patients

that start with the letter c okay so

here I have to write a pattern okay so

wherever you see the question like start

with letter see or end with this

character start with this character so

that's a pattern that you have to write

so again I have to pick from patients

table itself so from patients uh all

those patients uh whose first name start

with letter C so where uh my first name

like so like keyword is used whenever

you need to match your uh some column

with a pattern so my pattern is going to

be so write single quotes and then you

can say uh start with letter C so C and

after that you write the percentage sign

okay so this means the first name select

the first name only wherever your first

name like this pattern is uh you know

like matches this pattern where the

first name starts with capital c and

after that it can be anything I don't

care so hit control enter and you can

see that this query is also right I hope

you are understanding what I'm trying to

say okay let's let's go to the next

question show first name and last name

of patients that wait within the range

of 100 to 120 so over here we have to

write an inclusive range okay and I have

to choose first name and last name from

the patient table so from patient table

um where and you can see that I have

weight uh column in my patient so from

where uh

weight is greater than equal to 100

and weight is less than equal to 120

there uh from those rows I have to pick

first name and last name

columns and you can see this query is

also correct so we just had to write uh

we just had to write the simple

conditional statement we have to again

use where itself from this stable

patients where my weights are greater

than 100 and less than 120 both

inclusive pick first name and last name

very simple query so this is also a very

easy question and you can see that we

have solved around three questions I

guess 1 2 3 four questions great let's

go to the next question so these are

very simple that's why I'm not investing

a lot of time on these queries as soon

as the complexity will increase I'll

invest more time per query okay update

the patient so it is not selecting it is

update query update the patient table

for the allergy column okay if the

patient allergies is null then replace

it with nka okay so update query so I'll

say update my patient table so patient

table uh set allergies set allergies

what you need to modify you need to

modify allergies column so set

allergies what should be the updated

value NK which is given in the question

NK which all rows I need to pick so

where again where we need to use where

your allergies is null that's it very

simple simple query hit control enter

and it is uh it is working perfectly so

update those patients set it set the

allergies to NK where your allergies is

null so wherever the allergies row

allergies column is null for all those

rows set allergies equal to NK that's

what the question says okay very simple

let's go to the next

question show first name and last name

okay concatenated into one column to

show their full name okay okay so this

this one is interesting so what we need

to do we need to uh work on the patients

table itself so I'll say from

patients from patients uh show first

name and last name concatenated okay so

I'll say select uh and you know and

might be maybe you don't know but there

is a SQL keyword called concat okay so

if I show you concat in SQL we have this

keyword and and we can use this keyword

concat first

name first name comma and you can see

there is a space so I'll add a

space um just a second add a space comma

and then the third key third element

will be last name so all these three

keywords or sorry not these three

keywords but these three elements you're

concatenate ating right so select concat

first name comma space comma last name

as full name as full

name from patients that's

it so this should run perfectly and you

can see it is great so you can see the

output select concat first name comma

space comma last name as full name from

patients okay let's go to the next

question I hope you are not just

watching so you have to actually wait uh

when I read the question after that you

have to pause the video try to solve it

on your own and then you see the output

then you see uh the solution okay and

and before we move forward uh you can

check out my channel I been building

projects I've been building mobile apps

web apps AIML stuff blockchain python

data science machine learning a lot of

stuff I've been building on my channel

and all of these projects which I have

built they are all open source you can

check out my GitHub also and but it's a

great place where I've been teaching uh

all of this text stuff in very simple

English language so do check it out now

let's move forward so the next question

is show first name last name and the

full Province name of each patient so

you can see that we have in our table

The Province ID but the province name is

a separate table so this is the point

where you have to understand the concept

of joins whenever you need to work with

two tables together there you need to

use a the concept of joins okay so if

you see the schema in my patients I have

this province ID and I have a separate

Province names table so I have to join

these two tables on Province ID wherever

the province ID is matching I have to

join those rows and then I have to show

The Province name because my Province

name is not in this patient

table okay so let's write down the

solution so first of all I have to pick

the patient table so from patient table

and you can see that in my patient table

I'm having first name and last name okay

but I don't have the province name and

in the solution uh in the question it is

asking me to print the full Province

name also but in my patient stable I

only have the province ID Now using this

province ID I can go to Province name

stable I can match it with the province

ID over here and I can fetch the

province name okay so that means I need

to join these two tables and then I need

to work upon them so from patients join

so pick the patient table join it with

the province names

table on which parameter you need to join

join

on uh Province names you can I need to

match this province names. Province ID

so Province names. Province id id is

equal to from the patients table I need

to pick the province ID is equal to Prov

uh patients do province ID so wherever

these two IDs are matching I need to

pick some columns so select first name

last name and Province name so you can

see that in my patients table I'm having

uh first name and last name these two

but this province name Province name is

going to come from the province names

table and I can use it because I have

created the joined table right this

joint table is going to have this

province name column okay and that I'm

picking up so hit control enter and you

can see the query is 100% right okay

great now let's go to the next

question show how many patients have a

birth date with 2010 as the birth year

okay okay great so again I have to work

on the patients table so in my patients

table I'm having this birth date okay so

uh from patients table patients

table um I need to select all those

those patients so select all the columns

so select

star uh from patients have a birth date

where year so we have this year uh like

a keyword also year birth date provide

the date so it is going to fetch the

year from that birth date is equal to

201 1

0 okay so there is some issue

um show how many okay so we need to show

the count

so select count star so it's going to

count all the rows from patients where

year is 2010 and you can see the query

is 100% right great let's go to the next

question show the first name last name

and height of the patient with the

greatest height okay so what I have to

do I have to return only one single row

of the person with the greatest height

and for that person I need to show first

name and last name and the height of

that person so this is where we use

nested queries we write a query inside a

query okay so you see how I'm going to

write it so first of all let's write the

query which finds out the greatest

height which finds out the maximum

height so it's a very simple query uh

from patients from patients uh I need to

find the person with the maximum height so

select Max

height okay so this is going to give me

the maximum

height you can see it is

226 okay so this is the maximum height

now I need to find out that person that

patient record which has this height so

I'll cut this okay and I'll say from

patients where my height is equal to and

then then you write this uh circular

brackets in that you write your inner

query so paste it so this is my inner

query okay so what it is doing from this

query I'll get 226 so wherever my height is

226 return me that patient record and

from those record I'll pick first name

and last name because that is asked in

the question so first name last

name and height you can see this is the

correct answer so first of all we wrote

the inner query from where you can get

the maximum height then you match it

with the height and then you pick the

columns that you need to return great

let's go to the next

question okay show all the columns for

patients who have one of the following

patient IDs okay great so from patients

again same table from

patients uh

where where my patient ID my patient ID

which is a column you can see this is a

patient ID column you can you have to

use uh keyword in so this in you have to

use and you have to create the set so my

set is this set which is given to me so

I'll copy this

set paste it and you are what you are

doing you are from the patient table you

are picking all the patients where your

patient ID is in this set okay and then

you say select star because you need to

show all the columns and you can see

this is the right uh query okay so this

was very simple uh no explanation needed

I'll go to the next

question select the total number of

admissions okay so this is like super

simple I think I should not even cover

this so I just need to go ahead and

count the number of admissions so for

that you can see we have an admissions

table and I just need to say

um select count

count

star from admissions great so just need

to go ahead uh and uh like collect all

the rows from this admissions table and

just return the count of all the rows

okay so from admissions now no no

patients from admissions select all the

rows and count it and return it that's

it okay next question okay so let's see

the next question uh show all the

columns from admissions okay so

admissions table so okay from admissions

where the patient was admitted and

discharged on the same day so in my

admissions I can I can see uh admission

date and discharge date so I just need

to find out all those rows where these

two are same so select all the columns

right so select star from admissions

where uh admission date is equal to

discharge date and hit control enter and

that's a correct query perfect let's go

to the next

question show the patient ID okay and

the total number of admissions for

patient ID

579 okay so I need to count all the

rows uh in my admissions table where my

patient ID is

579 okay okay so from

admissions um I need to count it and the

condition is where my patient ID is

equal to

5179 um

select I need to return patient ID so

patient ID comma

count of all those rows which are found

and the name of this column should be total

total admissions

admissions total

total

admissions okay so what I'm doing I'm

picking the admissions table from that

admission table I'm finding all the rows

where my patient ID is 579 and then from

all those rows I'm picking the patient

ID column and a new column total total

admissions the logic is Count uh of

those rows control enter okay and there

is an issue total

admissions um maybe because I put it as

a number let's make it string and hit

control enter yes so this was just a

simple issue uh because an ID is a

string okay so let's go to the next question

question

write a query to find the first name

last name and birth date of patients so

patients who has height greater than 160

and weight greater than

70 okay so this one is uh on table

patients so from patients uh

where where my

height is greater

than 160

and my weight is less than okay and

weight is greater than 70 weight is

greater than 70 and from this uh result

I need to

pick first name last name and birth date

birth date perfect so that this is the

right query and we can see the result

awesome let's go to the next

question write a query to find list of

patients first name last name and

allergies okay so I can see this

allergies where allergies are not null

and are from City Hamilton

patients and allergies where allergies

are not null so where

allergies is not

null and

uh are from City Hamilton so city is there

there

and city is equal to Hamilton Hamilton

and from this I need to select

select uh first name last name first

name last

name and

allergies awesome

but there is an issue okay what is the

issue uh first name La okay sorry first

name last name allergies okay great

let's go to the next question I hope you

have understood this okay so if you are

not understanding anything or if you are

having any issue uh with like any doubt

you are having reach out to me in

LinkedIn or you can also book a

one-on-one call with me using topm the

links are in the description but I would

say one thing uh some people are like

just reaching out to me with their with

their doubt hey I am facing this issue

in this Firebase thing or no you don't

have to like Ping others uh with your

issues first of all there is like chat

GPT go and try it out uh try to solve

all your doubts on chat GPT and if there

is really a serious concern or doubt

that you're not able to solve then you

ping others right if you will depend on

others you will never be able to learn

on your own right previously it used to

be stack Overflow people were um like

like lazy to search on stack overl but

now there is chat GPD so before you ping

others first try to solve your doubt on

your own using chat GPD okay let's go to

question okay great uh awesome so if you

see the list we have actually covered

all the easy questions okay one is left

so let's do this uh based on the okay we

did that also I think yeah we had

completed this I guess so I don't know

why it's not reflecting but we had

covered this so we have covered all the

easy questions now I think we should go

ahead uh with the medium question okay

great let's do the first medium question

show unique birth years okay from

patient so my table is

patients and order them by ascending so

two things first of all we need to find

out all the unique years birth years

okay and we need to sort them by uh

ascending so for sort

you have order by order by order by what

value birth year value and this birth

year column we have to create right now

this is not existing right so birth year

now uh we have to find out the unique

birth here so you can see that in skl we

have this distinct keyword which is

actually used to retrieve the unique

values and that is what we need to do

over here so select distinct

distinct okay distinct year fetch the

Year from the birth date so we have this

birth date column and you need to make

this year uh we have you have to fit

this year into a new column which is

called birth year birth year and we are

going to sort this whole column using

this birth year value hit control enter

and you can see this is the right query

awesome let's go to the next question so

we Sol the first medium question okay so

this is the next question show unique

first names from the patient table okay

so from

patients which only occurs once in the

list okay so what you need to do is you

need to find out the unique first names

which the frequency of that first name

should only be

one okay so if two or more people are

named John so if there are two or more

folks who have name John in the first

name column then don't include their

name in the output list why because uh

two people have the same first name if

only one person is named Leo then

include that so we need to find out

those rows okay those rows which have

unique first name and that first name

should not be occurring twice in our

table so for this you need to know uh

two three more Clauses right so one is Group

Group

by Group by in SQL so what this does is

uh Group by is one of the most

frequently used SQL Clause it's a very

important Clause it allows you to

collapse a field into distinct values so

what we are going to do is we we are

going to Club our data table Club our

data table using Group by on the first

name so what will happen if there are

like uh multiple Johns they will be

clubbed in one uh section right so you

can find out the count of that section

and you can see if it is one that means

only one person is there who has named

John and that is your

output right so this group buy is very

important because this helps you to uh

uh bundle up the the data table rows by

a single column name okay and over here

our column name should be first name

okay great and you have another clause

which is called having and with Group by

we usually use this having

Clause so what this does is uh use to

filter the results of a group by query

so we are going to use Group by and then

we need to filter the results because we

need to find out that name which has count

count

one okay query based on the result of an

aggregate function so we are going to

first run Group by whatever bundle we

have got right grouped by group we have

got we'll find out the count of that

group using having and if it is one that

is our output I hope you are

understanding but let's write down the

query so from patients what what I need

to do is uh I need to group by Group by

first name and then I'll check having

count is one so what this is going to do

select uh first name so this will be first

first

name okay so what I'm doing from my

patient table or I'm grouping my

patients table by first name so all the

Johns which are there in my table they

will be clubbed and whatever result I've

got from this group by I'll find out the

count of each group and if it is equal

to one that is my output and from that

output I'll pick the first name so let's

try to run it and you can see this is

the right query Okay so you can assume

that this group buy and this having work

collaboratively they are always found

together so whatever result of your

group bu will be provided to this having

and this having will work on that result

find out the count and from those names

we will just get the first name and

return it so it is actually very simple

once you see the output okay so it is

fine if you are not able to write it on

your own on the first go let's go to the

next question so this is our next

question uh show patient ID and first

name from patients where their first

name and first name start and end with s

and is at least six characters long okay

so first of all select your uh table so from

from

patients uh then you write the we where

Clause where my first name my first name

start and end with s so first name you

will again use like start with s and end

with s so s s are going to to be there

but in between there should be four

characters because total L should be six

so 1 2 3 4 and then you write percentage

so what this means is it will start from

s it should end with s but in between

there can be any four characters any

four characters can be there okay and

then you select patient ID patient ID

and first name uh from your results so

hit control enter and you can see this

is the uh right output and you can see

the solution also so select patient name

patient ID and first name from patients

where first name like this uh let's

write the single codes I think I made a

mistake and hit control enter okay let

me select this

this yes so this is actually the right

query okay awesome let's go to the next

question so this is the next question uh

show patient ID first name last name

from patient patient so let's write down

the table name from patients uh whose

diagnosis is dementia uh so you can see

in my patients uh I don't have any uh

diagnosis table but if you see they have

also written primary diagnosis stored in

the admissions table so in the

admissions table you can see I have this

diagnosis so that means um I have to use

join okay so patients uh join

admissions okay uh on patient ID on

patient ID and I need to see I need to

find out where uh my

admissions dot diagnosis diagnosis is

equal to dementia so

dementia okay and from this I need to

fetch ID first name and last name okay so

select uh patient ID first name

and last

name okay so there is an

issue uh patient okay there is again an

issue uh column patient ID select patient

patient

ID uh patient ID yes patient ID spelling

is correct first name and last name from

patients join admissions oh okay so over

here also because we have joined the

table I need to tell it from which we

need to fetch the patient ID so that's

why it is not able to find it so

patients patients. patient ID uh first

name and last

name and you can see this is the right

query awesome let's go to the next

question uh next question says display

every patient's first name order the

list by the length of each name and then

by alphabetically okay so display every

patient's first name okay so so we are

we need to work on patients table only

so from

patients display every patient's first

name order the list by the length of

each name and then by alphabetically

okay so order

by order by uh how to find out the

length of this so let's see uh let's say

I don't know um find length of column

value in

SQL so we can use condition character

length yes Len we can use order by

length of my first name first name and

then by alphabetically okay so order by

um first name alphabetically select

first name from patients uh I think this

should be good so order by the length of

the first name and then you order it by

the first

name okay so you can see this is the

right query select first name from

patients first order it by the length of

the first name and then order it by the

first name itself so first by length and

then alphabetically great let's jump on

to the next question show the total

amount of male patients and the total

amount of female patients in the patient

table display the two results in the

same row okay so we need to have two

columns okay male count and female count

uh okay so this is my expected output so

over here I need to write a nested query

so you know if I need to find out the

count of my male patients what I will

write select uh count of all the rows uh

counts are of all the rows um where my

let's see what is the gender yes gender

where gender is equal to M right so this

is going to give me all the male

patients uh I have not defined the table

name that why from

patients okay uh from patient select

count star where and this where is going

to come over here you can see 2468 is my

male uh this thing my male uh patients

so select count star from patients and

if I make it

F this gives me the female uh patients

so what I can do is I need to write out

write down the nested query so select

count star from patients where my gender

is f uh count Star as female patients

and as male patients

H okay okay as

male patients and if I make it M over

here this gives me that column name uh

with this 246 it so this is like one

query and I need to write down the next query

query

down the next query which says uh female

patients and this should be F okay so

these are the two queries that I've got

but now uh I need to Club them in one

single table right so my final query

should be uh

select okay uh select and over here what

I'm going to say uh another query which

says select uh count of star uh I mean

count all the rows count

uh from patients from patients where your

your

gender is

male right and this

query sorry this

query will

be stored in male patients column okay

next query so this query is done next

query uh comma

same thing exact same

thing uh from patients where gender is

female female okay um I think this

enough this is not going to be there

select this and yes so this is the right

query I guess yes I think yeah male

patient patent and female patients 2062

and 2

468 okay and this is something that I have

have

written this is another output that you

are using the sum operator and you are

finding the sum of all the male rows

this is also correct and there is a

third one where you use Su and you use

case multiple ways of writing the same

query but I think we are also right okay

great let's go to the next question uh

show first name and last name and

allergies from patients okay so from

patients that's the first thing that we

have got uh which have allergies to

either pencillin or Morphin

Morphin where

where

allergies is equal

pencillin or

Morphin okay show results ordered

ascending by

allergies okay so order by

allergies and then by first name then by

last name okay so first name last name

and then you pick all the columns that

you need to uh show in the output so

select first

name last

name and allergies perfect in first go

itself we got the right query awesome

okay so let's see the next question uh

it's a medium problem and it's a bit

tricky so you need to focus show patient

ID okay diagnosis from admissions okay

so as soon as you get the first thing

your table write it down

admissions find patients admitted

multiple times so you need to find out

from this table who are those patients

who are admitted multiple times in the

hospital for the same diagnosis that

that means whenever you get such words

like same or group some kind of these

words then you need to be sure that you

need to group by your table you need to

group your table on some field so in my

case the field is diagnosis so what I

need to do if I group by my table on

diagnosis then I will in my table I will

have some bundles I will have

groups right and those groups will have

only one uh disease that is that

diagnosis right and if I group the table

not just on diagnosis but also on the

patient ID right and then I find out the

uh count then I can be sure that hey

these are those patients who are

appearing multiple times if their count

is more than one I know it might sound

like very difficult to understand but as

soon as I'll start writing down the

query it will become very easier for you

okay so from admissions if I group by my

table uh on diagnosis so Group by uh

diagnosis diagnosis and also I need to

group by on the patient ID right so that

it create bundles it create groups for

every patient for that diagnosis right

so patient ID comma diagnosis right and

then you know if you have group byy then

you have to use uh having Clause right

so I can

say tell me those uh groups which are

having tell me those rows which are

of the rows should be greater than one

okay so in those groups which are those

groups which are having more than one

rows because that will mean that same

patient is getting admitted in the

hospital for the same diagnosis because

this is why we are doing that group by

because this groups the table on patient

ID and diagnosis both okay so this is

very important Group by patient ID

diagnosis having count more than one and

then you canay up uh patient

ID uh comma

diagnosis and you can see this is the

right uh solution I hope you understood

if not you can just watch this last 4

minutes again okay let's go to the next

question um VI all questions so let's

see which question we haven't solved

yet okay this one show the city and the

total number of patients in the city

okay so show the city and total number

of patients in that City somewhere I'm

again aligning towards group okay so

that if I try to group this table on

City then I can find the rows and they

will be the patients in that

City okay so order from most to least

patients so this again order by Clause I

have to use and then by city name

ascending so there are two orders that I

have to maintain two sorting I have to

do but before that let let uh try to uh

write down the table name so from

patients okay I need to

select uh show the city so

City and the count of the rows that will

appear I I'll come to that so count as

uh what is the name uh as number of

patients you can see this is the number

of patients so as number of patients

number of patients

and then you can write down

um okay um show the city and the total

number of patients in the city

city

so Group by

City okay Group by City And then you

have uh order by so order

by most to least patients okay so number

of patients number of patients

and this order by will be in descending

order so

DEC descending order and then by City

okay so then by city but City in

ascending order so this should be the

answer let's see yes we got it right you

see how I'm solving these questions I'm

trying to break up my question I'm

trying to break down sorry not break up

but I'm trying to break down my question

into small small segments and then at a

time I'm solving one segment okay so

this is the trick what I'm using so

whatever you whenever you see the first

thing that is stable write it down from

patients right and then you can see what

all information you need to print City

and this and then you see you whether

you need to use order by clause or you

need to group by or you need to find out

the count and then you can do it okay so

this is how I'm doing great awesome so

let's go to the next question um but

before that uh again uh one thing I

forgot to tell that time that recently I

have started on my channel this AI

engineering playlist where I'm trying to

cover the AI Engineering in from very

ultimate Basics like the road map

resources uh introduction to a agents

like explanation to just a 5-year-old uh

then how to build a simple AI agent

everything about deep seek then devops

llm Ops mlops differentiation right so

I'm trying to cover this AI engineering

playlist so if you're new on this

channel do check it out um and if you're

preparing for for data science

interviews then I have a very great

playlist over here almost 55,000 folks

have seen this

playlist so in this I'm trying to uh

make projects using Kagel data sets so

I'm trying to do kagle data sets

analysis and this is a great playlist

that I built do check it out show first

name last name and role of every person

that is either a patient or a doctor

okay so this is tricky because in my

datab base I have patients table I have

doctor's table but now in the output you

can see I need to combine uh these two

tables patient and uh doctors okay so

both of the tables need to be combined

and whenever you need to find the

combination of these two tables or like

the union of these two tables we use a

keyword called

Union okay so let's see uh how we can do

it first of all select uh first name

last name first name last name Comm

role and you can see that if the guy is

a patient then you need to have the role

as patient okay so I will have a string

string um

patient as

role okay and this one I'm uh fetching

from the patient

table okay then I will have something

similar first name last name but the

role will be

doctor as role from doctors and then you

need to combine these two right you need

to find a union of these two so just use

Union all okay so the this uh query this

keyword is going to combine or you find

a union of both the tables and you know

that whenever you use Union that means

you are considering all the values from

the left one and from the right one

let's run it and you can see it is

question okay show all the

allergies ordered by

popularity okay so where are my

allergies my allergies are in my uh

patients table itself so from

patients I need to pick the allergies

okay so select

allergies okay uh ordered by it remove

null values from the query remove null

values from the query okay so where uh my

allergies is not null is not null okay

and then uh you can see that you need to

uh find out the count okay and you can

see that I also need to group my table

on this allergies why because I need to

find the count of that group so I can

say allergies uh plus the count of all

fetched where allergies is not null and

then you Group by

allergies uh and I can see that the

order is quite descending so I can say

descending okay so there is a problem uh

Group by allergies descending uh what is

the issue near okay let's say we remove

this and let's see what's the error we

get okay so I can see allergies count

count will be as total diagnosis to total

total

diagnosis okay so now this is there but

this is not in the order so Group by

allergies and order by oh okay so the

issue that I was doing was I was writing

descending over here so that was my

mistake so order by total diagnosis so

I'll copy

this uh and then in the descending order

and you can see we solved it amazing

guys uh let's get to the next question

I'm not clicking on this next because I

don't know why it is not uh working so

let's click on okay and let's go like that

that

itself um yeah awesome uh show all the

patients first name last name and birth

date so it is patients so my table

remains patients then first name last

name and birth date okay so I'll say

first name last name and birth date okay

uh who are both born in the 1970s

1970s

1970s okay decade sought the list

starting from the earliest birth date so

I need to do sorting so that means order

by will be used somewhere so order by is

going to get

used okay uh but before that you can see

I need to find all the people who were

born in this decade that means between

1970 to 19 uh 79 inclusive so

1970 to 1980 if I say from patients uh

first name last name and birth dat who

were born in the 1970s so

where where uh the

year birth date

date

between uh

1970 and

1979 so in this between this one is

inclusive and this one is also inclusive

Okay order by

uh order by sort the list starting from

the earliest birth date so order by will

be on birth date itself and you can see

we are right uh I hope guys you not just

watching this uh video you are also

solving alongside right so don't uh

pause the video whenever I'm uh coming

to the solution so before watching my

solution try to give it a shot let's get

to the next question we want to display

each patient's full name patient so my

my table remains patient display each

patient's full name in single

column okay tricky their last name in

all upper letters must appear first okay

so you can see the output also first of

all we are seeing the last name

so select um okay let's read further

their last name in all upper letters

must appear first then first name in all

lowercase letters separate the last name

and first name with a comma okay so I

need to combine these two so again we we

can use concat I already taught you this

okay so

concat uh first value will be upper

upper

upper upper of what last name and then I

can see a comma so I can see a comma and

then I can see uh

lower first name okay

awesome now separate yeah this one is

also done order the list by the first

name so order

by first name in descending order DC and

you see very fastly we wrapped up this

medium question this was a medium

question not an easy question awesome so

if you also did it in the first go

congrats you are learning it let's get

to the next question uh

okay let's see

yes show The Province IDs sum of the

height where total sum of its patient

height is greater than or equal to 7,000

okay so Province IDs sum of height where

total sum of its patients height is

greater than or equal to 7,000 that

means we will be grouping by province ID

then only we can get all the patients

for that Province okay

so this is the province ID so I need to

pick from patient

itself from

Patients Group by province

ID okay uh and you can see that I that

that I need to uh return the output in

like this format uh select Province ID Province

Province ID

comma um sum of height

okay so

sum of what sum of what so this is

tricky uh but this will be like some height

height

sum height Group by province ID where

the total sum of its patient height is

greater than or equal to 7 so where or

not not where sorry

having um some height some height is

greater than equal to 7,000 greater than

equal equal to

7,000 so this is right where sum of

height sum of height as some height

right so what I'm doing I'm first trying

to group by provin ID then I have groups

of the patients and then uh I see where

so I I'll find the sum of the heights of

all those patients I'll store it in the

sum height variable and wherever this Su

height is greater than 7,000 I need to

return those great this is right right

so all the provinces where the sum

height of all the patients is greater

than 7,000 they are being returned great

let's get to the next

question okay show the difference

between the largest weight and smallest

weight okay for patients with the last

name meroni okay uh from

patients um show the difference between

the largest weight and the smallest

weight for patients with the last name

meroni H okay

where uh last name is equal to meroni so

this is returning me all the

patients with last name meroni you can

see these are all the folks with last

name meroni great show the difference

between the largest weight and the

smallest weight okay and you can see my

output is like this okay so select

uh difference between the largest weight

so if I find the max so select Max of

weight okay and I find the

minimum of weight select maximum High

maximum weight minimum weight

as weight data weight Delta sorry weight

Delta and you can see this is right so

what what I did uh I picked the patients

table I found I found all the names

patients with this last name eroni and

then for that I find out the difference

between the maximum and the minimum

weights and I stored it in the weight

Delta and I'm returning it you see so

easy great so let's get to the next

question I think we are around 31 we

have solved around 30 questions okay so

let's see the next question uh show all

the days of the month uh 1 to 31 okay

uh and how many admission dates so if I

go to the admissions table how many

admission dates I can see the date is

here occurred on that

day okay sought by the day with most

admissions to least admissions so one

thing is I have to order okay so I can

see that some time I'll be

ordering uh my rows sold by the day with

most admissions to least admissions okay

that means descending

how many admissions dates occurred on

that day so that means day I have to

find out from this admission date so

this is a date from that date I have to

find out the day and that we find out

like this

day uh from admissions admission date

okay so this is the date day uh and then

you can see show all the days of the

month 1 to 31 and how many admissions

dates occurred on the day that means I

need to group

by okay I need to group by I can see the

day number so

select uh

day um admission date as day number day

number okay so I'm just finding out the

day from this date and saving it in day

number variable okay so that I can use

this now I don't need this one as day

number comma number of admissions so

comma uh count of all those rows and

save it in the variable number of admissions

admissions so

so number

number

of admissions so what I'm doing first of

all My First Column is day number so

that I have found my second column is

the count just a count so count of all

the rows which I'm going to find out

right now and just save it in this uh

column name First Column name second

column name okay that is done next I

need to uh group Buy on my day number

right because this is the the name

number 114 99 whatever uh and that I

have to group by so first of all you

write down your table name so

admissions uh Group

by Group by on my day number

day number right Group by my day number

and uh I need to sort by the day with

most admissions to least admissions so

order by number of admissions order by

number of admissions in descending order

and you can see so it all depends on

depends on your breaking down skills

like how well you can break down a

bigger problem into smaller chunks

within the first go your answer will be

right okay let's see the next question

so let's go to our next question this

one show all the

columns for patient ID 542 okay where my

patient ID is 542 so this one I have to

be I'll be writing down where my patient

ID is equal to 542 okay so this line is

done um most recent admission date most

recent admission date uh find show all

the columns for the patient ID uh

admission date discharge date okay so

from this admissions table I have to

show all the column names so select

star from

admissions okay select star from

admissions where my patient at is 542 so

all the rows for my patient 542 most recent uh show all the columns

542 most recent uh show all the columns foration most recent admission date so

foration most recent admission date so one row I have to show with the most

one row I have to show with the most recent admission date okay this is my

recent admission date okay this is my admission date uh so what I can do is I

admission date uh so what I can do is I can group bu on this patient ID I can

can group bu on this patient ID I can Group by on the patient ID so that I can

Group by on the patient ID so that I can get uh all of my rows for this patient

get uh all of my rows for this patient in a group so Group by and I can use

in a group so Group by and I can use having also then Group by on this

having also then Group by on this patient ID and then uh you can see that

patient ID and then uh you can see that if I find out the maximum uh from this

if I find out the maximum uh from this table maximum on the admission date if I

table maximum on the admission date if I do a date uh if I wrap it the date with

do a date uh if I wrap it the date with the maximum for that column I'll find

the maximum for that column I'll find out the most recent date okay because

out the most recent date okay because Max is going to give me Max on that date

Max is going to give me Max on that date will give me the most recent date so I

will give me the most recent date so I can say

can say having whichever Row from this group so

having whichever Row from this group so having uh admission

having uh admission date is equal to

date is equal to Max of the admission date so what I'm

Max of the admission date so what I'm doing I'm trying to find out the maximum

doing I'm trying to find out the maximum admission date the most recent date

admission date the most recent date trying to see whichever um uh row has

trying to see whichever um uh row has this admission date and then I'm going

this admission date and then I'm going to return it all the columns so and you

to return it all the columns so and you can see this is right so what I did I

can see this is right so what I did I first of all Group by my whole table on

first of all Group by my whole table on patient ID and I'm only interested in

patient ID and I'm only interested in this patient ID 5 42 then using having I

this patient ID 5 42 then using having I found I found using Max I found the most

found I found using Max I found the most recent date try to find out which row

recent date try to find out which row has that most recent date and then all

has that most recent date and then all the columns for that uh for those rows

the columns for that uh for those rows for that row okay great let's go to the

for that row okay great let's go to the next question so this is actually a

next question so this is actually a difficult question so you can see show

difficult question so you can see show patient ID okay patient ID okay

patient ID okay patient ID okay attending dror ID and diagnosis for

attending dror ID and diagnosis for admissions so I'm interested in the

admissions so I'm interested in the admissions table so from

admissions okay that and in this admissions table I'm interested in

admissions table I'm interested in patient ID attending doctor ID attending

patient ID attending doctor ID attending doctor ID and diagnosis so these three

doctor ID and diagnosis so these three so select uh patient ID and

diagnosis uh from admissions that match one of the two

admissions that match one of the two criteria so there are two criterias that

criteria so there are two criterias that have been given

have been given okay patient ID is an odd number okay so

okay patient ID is an odd number okay so where let's write down our first

where let's write down our first condition where uh my patient ID is an

condition where uh my patient ID is an odd number so my patient ID mod 2 is not

odd number so my patient ID mod 2 is not equal to zero okay patient ID is not

equal to zero okay patient ID is not equal to zero that means it's a patient

equal to zero that means it's a patient ID is an odd number and attending Dr ID

ID is an odd number and attending Dr ID and attending dror ID is either five or

and attending dror ID is either five or 19 um

19 um in 1 comma 5 comma 19 okay so this is

in 1 comma 5 comma 19 okay so this is the first condition what is the second

the first condition what is the second condition so

condition so or

or or right

or right where my attending doctor ID attending

where my attending doctor ID attending doctor ID attending doctor ID contains a

doctor ID attending doctor ID contains a two okay so it should be like

two okay so it should be like contains a two okay contains a two that

contains a two okay contains a two that means percentage to percentage that

means percentage to percentage that means anything can be there over here or

means anything can be there over here or over here okay so attaining Dr ID

over here okay so attaining Dr ID contains a two and the

contains a two and the length and so this should be like this

length and so this should be like this and length of what length of my patient

and length of what length of my patient ID length of my patient ID is three

ID length of my patient ID is three characters okay H is three characters

characters okay H is three characters length of my patient ID is equal to

length of my patient ID is equal to three

three so these are my two conditions so I can

so these are my two conditions so I can uh wrap it up like this where this or

uh wrap it up like this where this or this so these are my two conditions I

this so these are my two conditions I think this should be right no there is

think this should be right no there is something

wrong where near where syntax error okay where patient ID mod 2 is not equal to

where patient ID mod 2 is not equal to zero and is this okay very silly mistake

zero and is this okay very silly mistake uh this where will not come twice so

uh this where will not come twice so this will go away from here this is my

this will go away from here this is my first condition right and this is my

first condition right and this is my second condition so these are the two

second condition so these are the two conditions and over here only once I'm

conditions and over here only once I'm going to write

going to write where right and this one will not be

where right and this one will not be closed over here this will be closed

closed over here this will be closed over here itself okay so these are the

over here itself okay so these are the two conditions right starting from here

two conditions right starting from here ending till here and you can see this is

ending till here and you can see this is right so what I'm doing uh first

right so what I'm doing uh first condition second condition both are by

condition second condition both are by or and where right so only one where and

or and where right so only one where and then you can return all those columns

then you can return all those columns which you want to return so this is the

which you want to return so this is the next question uh which says show first

next question uh which says show first name last name and total number of

name last name and total number of admissions uh okay attended by each

admissions uh okay attended by each doctor attended for each doctor okay so

doctor attended for each doctor okay so first of all I need to show the first

first of all I need to show the first name last name of a doctor so I am

name last name of a doctor so I am interested in this doctor's table so um

interested in this doctor's table so um from this doctor's table I'll be picking

from this doctor's table I'll be picking up for first name last name and total

up for first name last name and total number of admissions okay so total

number of admissions okay so total number of admissions are not in this

number of admissions are not in this table I have to go on this admissions

table I have to go on this admissions table join these two tables and then

table join these two tables and then count the attend count wherever this

count the attend count wherever this attending doctor ID is equal to this

attending doctor ID is equal to this doctor ID and we might have to group

doctor ID and we might have to group also so that I can count the group of

also so that I can count the group of each doctor that will be the total

each doctor that will be the total number of admissions for that doctor so

number of admissions for that doctor so select first name I I can perform a join

select first name I I can perform a join so from admissions I can call this

so from admissions I can call this admissions uh as

admissions uh as a okay you can do that and you can join

a okay you can do that and you can join this admissions table on doctors okay so

this admissions table on doctors okay so join doctors let's call it as D on

join doctors let's call it as D on wherever d. do

wherever d. do ID is equal to a. admissions ID uh

ID is equal to a. admissions ID uh admissions. attending do ID okay um and

admissions. attending do ID okay um and then you can group this by group this by

then you can group this by group this by attending Dr

attending Dr ID okay uh sorry this will be a do

ID okay uh sorry this will be a do attending Dr ID no uh you can remove

attending Dr ID no uh you can remove this you can remove this uh admissions.

this you can remove this uh admissions. attending Dr ID admissions. attending Dr

attending Dr ID admissions. attending Dr ID

ID um and this should be attending doctor

um and this should be attending doctor ID

maybe like this and you need to also show the total

this and you need to also show the total number of count so

number of count so count Star as admissions

total yes you can see this is right first of all I pick the admissions table

first of all I pick the admissions table I joined it with the doctor's table and

I joined it with the doctor's table and doctors I'm assigning in a d variable I

doctors I'm assigning in a d variable I just wanted to teach you how to do that

just wanted to teach you how to do that doctors's D and D do Dr ID wherever this

doctors's D and D do Dr ID wherever this do ID is matching with my admissions

do ID is matching with my admissions attending Dr ID okay I need to pick

attending Dr ID okay I need to pick those uh rows so I'm just joining on

those uh rows so I'm just joining on this doctor ID then Group by on the uh

this doctor ID then Group by on the uh doctor ID so that I can I have a group

doctor ID so that I can I have a group for each doctor and then for all the

for each doctor and then for all the doctors pick first name last name and

doctors pick first name last name and count of those rows that's it okay so

count of those rows that's it okay so read this one or two times and then you

read this one or two times and then you will be uh you will Master this okay

will be uh you will Master this okay let's go to the next question for each

let's go to the next question for each doctor so again doctor uh display their

doctor so again doctor uh display their ID full name okay and first and last

ID full name okay and first and last admission date they attended so in my

admission date they attended so in my doctors uh in my doctor's table I am

doctors uh in my doctor's table I am having doctor ID so again I think we

having doctor ID so again I think we have to join uh doctor and admissions

have to join uh doctor and admissions okay so you can see we have a doctor ID

okay so you can see we have a doctor ID we have full name okay so what I can do

we have full name okay so what I can do is from doctors and we need to select

is from doctors and we need to select concat concat what we need to concat we

concat concat what we need to concat we need to concat uh the first name the

need to concat uh the first name the first

first name um and then a

name um and then a space then a space and then last

space then a space and then last name okay so this will give me uh the

name okay so this will give me uh the full name okay but before that we also

full name okay but before that we also need to pick the doctor ID so doctor ID

need to pick the doctor ID so doctor ID comma the full name and this will

comma the full name and this will be uh column name will be as um full

be uh column name will be as um full name full name okay so two things I've

name full name okay so two things I've got I've got this doctor ID I've got

got I've got this doctor ID I've got this full name and then first admission

this full name and then first admission date and last admission date okay first

date and last admission date okay first admission date and last admission date

admission date and last admission date H okay so I'll keep the space for it

H okay so I'll keep the space for it I'll fill it later so first name first

I'll fill it later so first name first admission date and last admission date

admission date and last admission date so I need to join my

so I need to join my doctors

doctors join my

join my admissions

admissions admissions okay and I need to group by

admissions okay and I need to group by also so join this

also so join this on on drct ID okay so I can call this

on on drct ID okay so I can call this doctor as D so and let's call this

doctor as D so and let's call this admission as a so on a do uh attending

admission as a so on a do uh attending Dr

Dr ID is equal

ID is equal to um I need to also Group by attending

to um I need to also Group by attending do ID so I can remove this remove this

do ID so I can remove this remove this uh join admissions on attending Dr ID

uh join admissions on attending Dr ID attend attending Dr ID will be

attend attending Dr ID will be admissions. attending Dr ID Dr ID is

admissions. attending Dr ID Dr ID is equal to D do do

equal to D do do ID okay and then uh so this is like one

ID okay and then uh so this is like one thing you have created a combined join

thing you have created a combined join table from that joint table you are

table from that joint table you are picking the doctor ID you are picking

picking the doctor ID you are picking the full name and then you will pick the

the full name and then you will pick the first and the last admission date and

first and the last admission date and then you also Group by then you also

then you also Group by then you also Group by uh admissions. attending Dr ID

Group by uh admissions. attending Dr ID admissions. attending Dr

admissions. attending Dr ID right so now I have the combined

ID right so now I have the combined table now I'll pick a

table now I'll pick a maximum H

maximum uh maximum in the admissions I have this admission

have this admission date right so I'll pick the maximum

date right so I'll pick the maximum admission

admission date

date as okay maximum admission date as the

as okay maximum admission date as the last admission date I

last admission date I guess

guess H last

admission date comma minimum admission date

minimum admission date as uh first admission date first

as uh first admission date first admission date I think they should run

admission date I think they should run no there is a problem near Max okay full

no there is a problem near Max okay full name okay one comma is missing and you

name okay one comma is missing and you can see this is right awesome so we

can see this is right awesome so we solved it so what I did uh I first of

solved it so what I did uh I first of all pick the doctor table joined it on

all pick the doctor table joined it on the admissions table so that I can I can

the admissions table so that I can I can work with both of the tables data on

work with both of the tables data on this Dr ID I grouped it by the do ID so

this Dr ID I grouped it by the do ID so that I can get groups for each doctor

that I can get groups for each doctor and then for from that group I'm finding

and then for from that group I'm finding out the doctor ID finding out the full

out the doctor ID finding out the full name maximum admission ID maximum

name maximum admission ID maximum admission date and minimum admission

admission date and minimum admission date as last and first respectively okay

date as last and first respectively okay this is very simple read it you will get

this is very simple read it you will get it very easy let's go to the next

it very easy let's go to the next question so display the total amount of

question so display the total amount of patients okay so now now I think we need

patients okay so now now I think we need to work with

to work with patients for each province okay so

patients for each province okay so Province and then patients so we will be

Province and then patients so we will be working with so if I

working with so if I join

join Province on patients so from

Province on patients so from Province Province names correct Province

Province Province names correct Province names uh let's call it as

names uh let's call it as PR join it on patients

PR join it on patients okay on

okay on patients. Province ID is equal to PR do

patients. Province ID is equal to PR do province

province ID okay now I've got the combined table

ID okay now I've got the combined table uh and then display the total number of

uh and then display the total number of patients for each province so I need to

patients for each province so I need to group also Group

group also Group by pr. province ID no yes Group by

by pr. province ID no yes Group by province ID

province ID yes and you need to pick

yes and you need to pick from this join table I need to pick uh

from this join table I need to pick uh Province name Province

Province name Province name

name H Province

name comma

comma count of all those

count of all those rows

okay I need to group by the province that I've done now I need to count the

that I've done now I need to count the patients so we have grouped by The

patients so we have grouped by The Province ID we have we'll get the

Province ID we have we'll get the patient

patient rows I'm just counting it as

patient count count as patient count Group by

count count as patient count Group by and then we need to order by descending

and then we need to order by descending so order by patient

so order by patient count

count patient

patient count no this is wrong so we have got

count no this is wrong so we have got this uh sending by okay descending so DC

this uh sending by okay descending so DC and yes this is right great we got it so

and yes this is right great we got it so what I did first of all I picked the

what I did first of all I picked the province table Province names table

province table Province names table joined it with the patient table right

joined it with the patient table right then I formed a group using the province

then I formed a group using the province ID so that all the patients for every

ID so that all the patients for every Province are together in a group and

Province are together in a group and then I picked the province name uh I

then I picked the province name uh I found out the count of all the rows

found out the count of all the rows stored in the patient count and then

stored in the patient count and then using this patient count I just sorting

using this patient count I just sorting using uh I'm sorting using order by in

using uh I'm sorting using order by in the descending order simple okay so

the descending order simple okay so that's how I am doing it just breaking

that's how I am doing it just breaking down the problem into smaller smaller

down the problem into smaller smaller segments let's go to the next question

segments let's go to the next question so what is our next question this is the

so what is our next question this is the one

one okay uh for every

okay uh for every admission for every admission admissions

admission for every admission admissions table display the patient full name okay

table display the patient full name okay uh that means Admissions and patients we

uh that means Admissions and patients we need to combine their admission

need to combine their admission diagnosis okay ad do diagnosis we can

diagnosis okay ad do diagnosis we can get from here and their doctor's full

get from here and their doctor's full name who diagnos okay so two joints one

name who diagnos okay so two joints one Admissions and patients and then with

Admissions and patients and then with doctors also so if I

doctors also so if I pick admissions a let's call this

pick admissions a let's call this admission as a uh join

admission as a uh join it on the patient

table on a do patient ID

do patient ID is equal to patients. patient

is equal to patients. patient ID and then join it with

ID and then join it with doctors on a do attending doctor ID

doctors on a do attending doctor ID attending doctor ID is equal to doctors.

attending doctor ID is equal to doctors. Dr

ID okay so two joints which I have done uh their admissions diagnosis and

uh their admissions diagnosis and doctor's full name so now because I have

doctor's full name so now because I have a combined table of containing ad misss

a combined table of containing ad misss patients and doctors now I can uh select

patients and doctors now I can uh select my column names so

my column names so select what I need to select full name

select what I need to select full name of the patient so uh select concat

of the patient so uh select concat concat

concat um first

um first name

name comma

comma space last name as patient name

space last name as patient name patient name this is the first column

patient name this is the first column and then your diagnosis so comma

and then your diagnosis so comma diagnosis second column then the

diagnosis second column then the doctor's full name doctor's name you can

doctor's full name doctor's name you can see great so I'll use the same one uh

see great so I'll use the same one uh same I'll copy it I'll paste it over

same I'll copy it I'll paste it over here first name last name as

here first name last name as doctors what is the name okay sorry what

doctors what is the name okay sorry what did I do yes um doctor name okay so this

did I do yes um doctor name okay so this will become doctor

perfect okay so there is an issue uh amb uh ambiguous column name first name

uh ambiguous column name first name correct so that is a problem so over

correct so that is a problem so over here I need to Define from which table

here I need to Define from which table I'm trying to get this values patients.

I'm trying to get this values patients. first

name doctors. first name and doctors. last

last name no there is

name no there is uh issue there is an issue patient.

uh issue there is an issue patient. first name patient. last name at patient

first name patient. last name at patient name diagnosis then doctors. first name

name diagnosis then doctors. first name doctors. last name doctor name row value

misused okay let's see the solution only let's see the

let's see the solution select concat that we have done

solution select concat that we have done oh okay did we just miss the concat over

oh okay did we just miss the concat over here conat I think this was just the

here conat I think this was just the issue no no no we still are missing okay

issue no no no we still are missing okay okay we saw the solution so we need to

okay we saw the solution so we need to select the question again uh let me just

select the question again uh let me just copy this query VI all questions let me

copy this query VI all questions let me select the question that I was doing

select the question that I was doing this one paste it and if this works that

this one paste it and if this works that means we were just missing this concat

means we were just missing this concat yes we were just missing this concat

yes we were just missing this concat otherwise our solution was correct

otherwise our solution was correct awesome let's go to the next question uh

awesome let's go to the next question uh display uh display the first name last

display uh display the first name last name and number of duplicate patients

name and number of duplicate patients okay duplicate patient based on their

okay duplicate patient based on their first name and last name so if I find a

first name and last name so if I find a value first name and last name and then

value first name and last name and then I group it by okay so if I group by the

I group it by okay so if I group by the full name Group by first name and last

full name Group by first name and last name right and then using having I check

name right and then using having I check their count if it is more than one then

their count if it is more than one then I can see that it it is duplicate name

I can see that it it is duplicate name right patient with identical name can be

right patient with identical name can be considered as a duplicate correct so if

considered as a duplicate correct so if I

I pick the pick the patients table group

pick the pick the patients table group it by first name comma last name and

it by first name comma last name and then I check their count so having

then I check their count so having um number of

um number of duplicates no why why do I need to check

duplicates no why why do I need to check the having why do I need to check the

the having why do I need to check the account so if I select their first name

account so if I select their first name and last name select first name last

and last name select first name last name first name

name first name last name and number of

duplicates H number of duplicates will be where I'm grouping it on first name

be where I'm grouping it on first name and last name so I just need to select

and last name so I just need to select the count count of these rows and I'm

the count count of these rows and I'm going to store it in a column name

going to store it in a column name called number of duplicates right uh if

called number of duplicates right uh if my number of duplicates having

my number of duplicates having duplicates

duplicates name yes is greater than

one okay yes this is right so what I did uh I first figured I first uh like

uh I first figured I first uh like picked my patient table then my patient

picked my patient table then my patient table rows I grouped it on both first

table rows I grouped it on both first name and last name so that so that the

name and last name so that so that the complete name is grouped by and then I'm

complete name is grouped by and then I'm checking if if the duplicate number of

checking if if the duplicate number of duplicates which I'm storing by the

duplicates which I'm storing by the count is greater than one that means

count is greater than one that means then only that group is a valid for for

then only that group is a valid for for this uh query because we have to find

this uh query because we have to find out only those names which are having

out only those names which are having more than one frequency right duplicates

more than one frequency right duplicates we have to find out so greater than one

we have to find out so greater than one and then you just select the first name

and then you just select the first name and last name and this count that's it

and last name and this count that's it simple okay let's go to the next

simple okay let's go to the next question okay uh so let's read the next

question okay uh so let's read the next question uh which says display patient

question uh which says display patient full name okay so we are interested in

full name okay so we are interested in the patient table height in the units

the patient table height in the units feed okay not in the unit that is given

feed okay not in the unit that is given in the question and they have given the

in the question and they have given the conversion also so height in the

conversion also so height in the question is in centimeter in the real

question is in centimeter in the real database we need to convert it to feet

database we need to convert it to feet by dividing the height by

by dividing the height by 30.48 okay weight in the unit pound so

30.48 okay weight in the unit pound so in the question in the table it is in kg

in the question in the table it is in kg we have to convert into pounds rounded

we have to convert into pounds rounded to zero decimals and the first one was

to zero decimals and the first one was in one decimal and gender

in one decimal and gender non-abbreviated non-abbreviated means if

non-abbreviated non-abbreviated means if it is M then we have to uh uh make it

it is M then we have to uh uh make it male like this and if it is f we have to

male like this and if it is f we have to make it female like this okay so two

make it female like this okay so two three things we need to

three things we need to let's do them one by one uh so first of

let's do them one by one uh so first of all let's pick the patients table

all let's pick the patients table patients and what we need to do uh we

patients and what we need to do uh we need to do everything in the select uh

need to do everything in the select uh Clause itself so select first thing is

Clause itself so select first thing is we need to concatenate first name and

we need to concatenate first name and last name so I'll do

last name so I'll do that uh first

that uh first name first name comma

name first name comma space uh space and then your last name

space uh space and then your last name so this is the first thing that we have

so this is the first thing that we have done next thing that we need to do is

done next thing that we need to do is height in the

height in the units okay uh we need to convert

units okay uh we need to convert centimeter to feet by dividing by

centimeter to feet by dividing by 30.48

30.48 so and it should be rounded also so we

so and it should be rounded also so we can use round uh keyword

round height divide by

height divide by 30.48

30.48 and the number of uh decimal places is one so I'll comma uh I'll put

places is one so I'll comma uh I'll put comma and one so this will be my new

comma and one so this will be my new height and I can call it as height uh

height and I can call it as height uh and let's call it as feet so height in

and let's call it as feet so height in feet this is the new column that we have

feet this is the new column that we have created next

created next column so next column is for weight uh

column so next column is for weight uh again round I have to use so round and

again round I have to use so round and this will be weight uh multiplying it by

this will be weight uh multiplying it by 2.205 so weight into

2.205 so weight into 2.205 decimal places is 0o

2.205 decimal places is 0o and this will be called as weight in

and this will be called as weight in pounds so

pounds so weight um pounds so it will

be bounds okay so this is the next column that we had to create and then

column that we had to create and then the birth date so birth date will come

the birth date so birth date will come as it is and

as it is and then gender non-abbreviated so we need

then gender non-abbreviated so we need to see where it is M we need to call it

to see where it is M we need to call it as male where it is f we need to call it

as male where it is f we need to call it as female so we need to use uh cases

as female so we need to use uh cases like in if else over here also in skl we

like in if else over here also in skl we have cases right so I can say um

have cases right so I can say um case

case uh yeah so case uh the first condition

uh yeah so case uh the first condition will be gender gender equal

will be gender gender equal to um M then it should be called as

to um M then it should be called as male um

male um male

male else gender k case will be like this

else gender k case will be like this case when my gender is M then male

case when my gender is M then male else um else it will be female okay so

else um else it will be female okay so else it will be female okay and you need

else it will be female okay and you need to end this case so this is the case

to end this case so this is the case that I've written and this should be

that I've written and this should be stored in a variable in a column name

stored in a variable in a column name called as gender

called as gender type I hope you are uh able to

type I hope you are uh able to understand this whole query I think this

understand this whole query I think this is done if I just run it

is done if I just run it okay there is a problem uh feet okay so

okay there is a problem uh feet okay so there is a problem height feet

there is a problem height feet pounds height feet pounds

pounds height feet pounds okay let's call it as like

okay let's call it as like this feed what is a problem in

feed if we call it like this yeah you can see it is running

this yeah you can see it is running perfectly okay so uh you can see what I

perfectly okay so uh you can see what I have written first of all you need to

have written first of all you need to find your concatenated full name then

find your concatenated full name then you need to uh have your height in feet

you need to uh have your height in feet so I just did the mathematics over here

so I just did the mathematics over here just write the simple logic then your

just write the simple logic then your weight then your birth date and then

weight then your birth date and then using cases uh just a simple case I just

using cases uh just a simple case I just converted M to male and F to female

converted M to male and F to female right and I ended it and saved in the

right and I ended it and saved in the gender type column uh from my table

gender type column uh from my table patients very simple okay let's go to

patients very simple okay let's go to the next question so this is the next

the next question so this is the next question which says show patient ID

question which says show patient ID first name last name from patients okay

first name last name from patients okay so from patients from patients who does

so from patients from patients who does not have any record in the admissions

not have any record in the admissions table okay so you can see in my

table okay so you can see in my admission table I have this patient

admission table I have this patient ready so if I just find out those

ready so if I just find out those patients which whose patient ID does not

patients which whose patient ID does not lie in this admission table that will be

lie in this admission table that will be my answer so I can say uh if I select

my answer so I can say uh if I select those patients whose patient ID do not

those patients whose patient ID do not exist in the admission table I'll be

exist in the admission table I'll be done

done so if I just write another

so if I just write another query select patient ID admissions.

query select patient ID admissions. patient ID patient ID from admissions so

patient ID patient ID from admissions so what does this query give me this query

what does this query give me this query give me uh this query gives me the

give me uh this query gives me the column of the patient ID all the patient

column of the patient ID all the patient IDs which are existing in the admissions

IDs which are existing in the admissions table okay now from this Row from this

table okay now from this Row from this complete data set from this complete

complete data set from this complete data structure if I say

data structure if I say uh not in right not in so all those

uh not in right not in so all those patient IDs which are not in this

patient IDs which are not in this admissions patient IDs I'll be done so

admissions patient IDs I'll be done so this I have written correct this I have

this I have written correct this I have written correct okay so now uh I'll

written correct okay so now uh I'll select my main table from patients and

select my main table from patients and I'll select uh first name I'll select

I'll select uh first name I'll select the last name and I also need to select

the last name and I also need to select the patient ID patient ID first name

the patient ID patient ID first name last name from patients uh where where

last name from patients uh where where my patient patient ID patient ID is not

my patient patient ID patient ID is not in

in this

this um where my patient is not correct I I

um where my patient is not correct I I think this is right not in this uh query

think this is right not in this uh query okay so there is an issue the error is

okay so there is an issue the error is mostly likely because you have two

mostly likely because you have two select statements written please okay I

select statements written please okay I think if I just like write like

think if I just like write like this yes so I think uh we just had to uh

this yes so I think uh we just had to uh wrap our another select statement with

wrap our another select statement with this uh circular brackets and and we are

this uh circular brackets and and we are done so this was the answer I hope you

done so this was the answer I hope you have understood this is this was not a

have understood this is this was not a hard one right let's go ahead and see

hard one right let's go ahead and see the next question um I think we are done

the next question um I think we are done with the medium okay last medium

with the medium okay last medium question we have to see the last medium

question we have to see the last medium question let's try to solve it display a

question let's try to solve it display a single row okay with Max visits Min

single row okay with Max visits Min visits average visits where the maximum

visits average visits where the maximum minimum and average number of admissions

minimum and average number of admissions per day is calculated okay so in my

per day is calculated okay so in my admissions you can see

I have this table and I have to find out for that day so my main field my main um

for that day so my main field my main um column name is going to be the day for

column name is going to be the day for that day that means some grouping is

that day that means some grouping is need some grouping needs to happen on on

need some grouping needs to happen on on day right because if I group my data on

day right because if I group my data on day then I can find out the maximum

day then I can find out the maximum minimum and average visits for uh from

minimum and average visits for uh from that group

that group right H okay let's do that

right H okay let's do that from

from Admissions and I need to

select H three values some uh okay one value

H three values some uh okay one value will be maximum visits so one value will

will be maximum visits so one value will be on maximum visits I need to calculate

be on maximum visits I need to calculate this maximum visits for sure maximum

visits okay then minimum visits I'm for now I'm just writing the variables

now I'm just writing the variables and then average visits

and then average visits average visits okay I'll be calculating

average visits okay I'll be calculating these three from my

these three from my admissions if I group

admissions if I group by uh Group by the admission

by uh Group by the admission date right Group by admission

date right Group by admission date yeah Group by admission date

date yeah Group by admission date display the single row with maximum

display the single row with maximum visits minimum visits average visits

visits minimum visits average visits where the maximum minimum uh number of

where the maximum minimum uh number of admissions per day number of admissions

admissions per day number of admissions per

per day

day count yes I need to work with the count

count yes I need to work with the count I think one query is not enough so I

I think one query is not enough so I need to divide this question into

need to divide this question into multiple

multiple queries I think two queries should be

queries I think two queries should be enough first of all um if I find uh if I

enough first of all um if I find uh if I find from my admissions table if I group

find from my admissions table if I group Buy on the admission date so from from

Buy on the admission date so from from admissions from admissions if I group by

admissions from admissions if I group by on admission

on admission date if I group by on admission date

date if I group by on admission date um

date what will I get so if I wrap it up if I just hit it I can see I get all my

if I just hit it I can see I get all my admissions

admissions date right

date right and if I find out the count of the

and if I find out the count of the number of rows in each group so if I say

number of rows in each group so if I say count of all the rows for each group as

count of all the rows for each group as uh number of admissions number of

uh number of admissions number of admissions so you can see for this date

admissions so you can see for this date I'm having 17 admissions for this date

I'm having 17 admissions for this date I'm having nine admissions for this date

I'm having nine admissions for this date I'm having nine admissions okay now from

I'm having nine admissions okay now from this I can find out

this I can find out the maximum minimum and average on this

the maximum minimum and average on this particular

particular um

um row uh sorry on this particular column

row uh sorry on this particular column so this is my new col this is my new

so this is my new col this is my new table that I have to work on right from

table that I have to work on right from this table I can I can do all the

this table I can I can do all the mathematical uh functions so if I just

mathematical uh functions so if I just wrap this query in circle circular

wrap this query in circle circular brackets and I say pick this table right

brackets and I say pick this table right I am not interested in this patients or

I am not interested in this patients or admissions table I'm interested in this

admissions table I'm interested in this new table right from this table and now

new table right from this table and now I can do all the mathematical functions

I can do all the mathematical functions so if I say select

so if I say select um select

um select Max of the number of

Max of the number of admissions number of admissions Max from

admissions number of admissions Max from this number of admissions

this number of admissions right as Max visits Max

right as Max visits Max visits then I can say minimum from

visits then I can say minimum from number of admissions as Min

number of admissions as Min visits Min visits and then

average uh rounded to two decimal places okay so this average will be wrapped by

okay so this average will be wrapped by round then average of number of

round then average of number of admissions number

admissions number of

of admissions rounded to two decimal

admissions rounded to two decimal places as average

visits I think this should work yes you can see this is the right query and I

can see this is the right query and I hope you understood it so first of all I

hope you understood it so first of all I created a new table uh which has the

created a new table uh which has the required column names on which I need to

required column names on which I need to perform these mathematical functions and

perform these mathematical functions and then I wrote These mathematical

then I wrote These mathematical functions which are very simple so

functions which are very simple so congrats we have covered all the medium

congrats we have covered all the medium questions so if you see we have covered

questions so if you see we have covered all of the easy and the medium questions

all of the easy and the medium questions now we need to start with the hard

now we need to start with the hard questions let's get started okay so this

questions let's get started okay so this is the next question show all the

is the next question show all the patients grouped into weight groups okay

patients grouped into weight groups okay so what is this weight group it is given

so what is this weight group it is given over here so if someone's weight is 109

over here so if someone's weight is 109 we need to consider them in the group

we need to consider them in the group 100 if someone weights is 119 we need to

100 if someone weights is 119 we need to consider them for a group 110 so we need

consider them for a group 110 so we need to make groups of 100 we need to make

to make groups of 100 we need to make groups with an interval of 10 10 10 so

groups with an interval of 10 10 10 so 100 110 12 130 and all the numbers lying

100 110 12 130 and all the numbers lying between uh like 0 to 9 they will be uh

between uh like 0 to 9 they will be uh uh put in that particular group okay

uh put in that particular group okay show the total number of patients total

show the total number of patients total amount of patients in each weight group

amount of patients in each weight group uh order the list by weight group

uh order the list by weight group descending okay so grouping and ordering

descending okay so grouping and ordering these are the two things that I found we

these are the two things that I found we will be doing so group buy and Order bu

will be doing so group buy and Order bu these are the two things that we need to

these are the two things that we need to be that needs to be done okay so all the

be that needs to be done okay so all the patients grouped into weight groups so

patients grouped into weight groups so how do you find out the group so if

how do you find out the group so if someone weight is like let's say seven

someone weight is like let's say seven right so how do you insert the person in

right so how do you insert the person in a group of zero right so 0 10 20 like

a group of zero right so 0 10 20 like this

this right so if I divide 7 by uh 10 and I

right so if I divide 7 by uh 10 and I find out the floor value of this and I F

find out the floor value of this and I F find out the floor value of this this

find out the floor value of this this gives

gives me

me zero right so that's how I can find out

zero right so that's how I can find out the grow group but if someone's weight

the grow group but if someone's weight is like let's say um 109 and if I divide

is like let's say um 109 and if I divide it by 100 right uh if I divide by 100 I

it by 100 right uh if I divide by 100 I get

get 10.9 right 10.9 and if I uh I need to I

10.9 right 10.9 and if I uh I need to I where do I need to reach I need to reach

where do I need to reach I need to reach 100 right right I need to reach 100 so

100 right right I need to reach 100 so if I divide 109 by 100 I get 10.9 if I

if I divide 109 by 100 I get 10.9 if I make the floor of it I get 10 and If I

make the floor of it I get 10 and If I multiply it by 10 I get this 100 value

multiply it by 10 I get this 100 value let's take one more example let's say my

let's take one more example let's say my weight is 119 right so for 119 uh if I

weight is 119 right so for 119 uh if I divide by 100 I get 11.9 and if I make

divide by 100 I get 11.9 and if I make the floor of it I get 11 right and if I

the floor of it I get 11 right and if I multiply 11 uh by 10 I get 110 and

multiply 11 uh by 10 I get 110 and that's the right group right so if I

that's the right group right so if I divide it by 100 floate multiply by 10 I

divide it by 100 floate multiply by 10 I get my group okay awesome so I can say

get my group okay awesome so I can say I can say select uh first I need to find

I can say select uh first I need to find out

out that

that group so select uh for that weight right

group so select uh for that weight right so

so floor of uh the weight of the person

floor of uh the weight of the person weight of the person divide by

weight of the person divide by 100 divide by

100 divide by 100 into

100 into 100 into not 100 into into 10 right into

100 into not 100 into into 10 right into 10 I can call it

10 I can call it as uh group weight group

as uh group weight group weight group okay and this we are

weight group okay and this we are doing on patients table so from

doing on patients table so from patients so if I do like

patients so if I do like this and I hit enter you can see I've

this and I hit enter you can see I've got so for most of them I'm getting zero

got so for most of them I'm getting zero so weight by

so weight by 100 or 10 weight by 10 right yes I did a

100 or 10 weight by 10 right yes I did a mistake so weight by 10 into 10 I'll get

mistake so weight by 10 into 10 I'll get it so if I hit control enter you can see

it so if I hit control enter you can see now I'm getting the correct uh weight

now I'm getting the correct uh weight groups for

groups for everyone okay so now what I need to do I

everyone okay so now what I need to do I need to group uh these folks on this

need to group uh these folks on this weight group right so from Patients

weight group right so from Patients Group by on this weight group on this

Group by on this weight group on this weight

weight group right on this weight group so if I

group right on this weight group so if I hit

hit enter and I now now I need to find out

enter and I now now I need to find out this is the weight group okay weight

this is the weight group okay weight group and if I somehow find

group and if I somehow find out patients in that group that means

out patients in that group that means the

the count

count count I can do that okay so if I

count I can do that okay so if I say count of the

say count of the rows count of the rows and I can call it

rows count of the rows and I can call it as patients in group

as patients in group patients in group so there is a problem

patients in group so there is a problem uh there will be comma yes so patients

uh there will be comma yes so patients in the group is

in the group is 9214 and I need to order them

9214 and I need to order them also

also descending right order them order them

descending right order them order them by patients in the

by patients in the group descending

okay okay so patients in the group I have got this show all the patient the

have got this show all the patient the group into weight groups show the total

group into weight groups show the total amount of patients in each weight

amount of patients in each weight group okay and order them by weight

group okay and order them by weight group

group descending I think what are we missing

descending I think what are we missing patients in the group patients in the

patients in the group patients in the group weight group 140 so we had to

group weight group 140 so we had to weight group okay not by the number of

weight group okay not by the number of patients so weight group

descending yes we got it um yeah and this is the solution so

it um yeah and this is the solution so we did almost same uh we did the count

we did almost same uh we did the count as patient to the group so if we just

as patient to the group so if we just write this one above we will be in the

write this one above we will be in the right uh index of the columns from

right uh index of the columns from Patients Group by and order by hit enter

Patients Group by and order by hit enter okay what is the isue yeah comma will be

okay what is the isue yeah comma will be here it will be removed from here and

here it will be removed from here and you can see patients in the group and

you can see patients in the group and weight of that group

weight of that group let's go to the next question so

let's go to the next question so congrats you solve the first hard

congrats you solve the first hard question so this is the next question uh

question so this is the next question uh show patient ID weight and height okay

show patient ID weight and height okay so from patients we need to work on on

so from patients we need to work on on patient table we need to work on uh

patient table we need to work on uh select patient ID select patient ID then

select patient ID select patient ID then weight and height weight and height is

weight and height weight and height is obese from the patient table and is

obese from the patient table and is obese condition is

obese condition is given uh if my weight by height and the

given uh if my weight by height and the whole square if weight by height square

whole square if weight by height square is greater than equal to 30 I will be

is greater than equal to 30 I will be considered as obese and I need to

considered as obese and I need to convert my centimeters to

convert my centimeters to meters okay let's do that so first of

meters okay let's do that so first of all uh I again need to write a case okay

all uh I again need to write a case okay when uh case when my uh

weight upon uh Power of

upon uh Power of power

power of height by

of height by 100 right power of height upon 100 and

100 right power of height upon 100 and this will be in this circle bracket so I

this will be in this circle bracket so I have found the meters over here I

have found the meters over here I converted centimeters to meters power

converted centimeters to meters power will be two right so me Square so this

will be two right so me Square so this is weight by height in met square if

is weight by height in met square if this is greater than equal to 30 if this

this is greater than equal to 30 if this is greater than equal to

is greater than equal to 30 um then

30 um then then I will be called as I uh my is

then I will be called as I uh my is obese will be

obese will be zero

else one right and I will end this case and I'll save it in a new column is

obes hit enter there is a problem is OB zero query does not match the expected

zero query does not match the expected output okay

0 and one else zero this one weight weight correct upon

weight correct upon height

meter okay um height I found in the meters I found the square of the

meters I found the square of the height if it is greater than equal to

height if it is greater than equal to 30 so for patient 1 Z first four zeros

30 so for patient 1 Z first four zeros first four zeros then one zero okay so

first four zeros then one zero okay so the why why the problem is coming over

the why why the problem is coming over here

here um from the patient displays OB as

um from the patient displays OB as Boolean weight upon height M square is

Boolean weight upon height M square is than equal 30 weight is in units kg

than equal 30 weight is in units kg correct this should be right so what am

correct this should be right so what am I missing uh let's see the solution um

I missing uh let's see the solution um because I'm not able to find out the

because I'm not able to find out the issue what I did wrong so let's find out

issue what I did wrong so let's find out the

the solution select patient ID weight height

solution select patient ID weight height correct case so did I miss just that uh

correct case so did I miss just that uh weight upon power height

100.0 100.0 comma 2 greater thanal 30 then

100.0 comma 2 greater thanal 30 then zero

zero else then one okay so did I

else then one okay so did I just is obese then one okay what was the

just is obese then one okay what was the what was the question obese is defined

what was the question obese is defined okay okay okay sorry so this will be one

okay okay okay sorry so this will be one this will be

this will be zero and I guess uh if we just write

zero and I guess uh if we just write the query like this

the query like this um from patient so if I just copy this

um from patient so if I just copy this let me select the question which we were

let me select the question which we were solving this

solving this one I think yes so this was my mistake I

one I think yes so this was my mistake I just uh swapped the conditions if else

just uh swapped the conditions if else condition silly mistake but yeah uh we

condition silly mistake but yeah uh we got it let's go to the next

got it let's go to the next question um my next question is this one

question um my next question is this one okay show patient ID first name last

okay show patient ID first name last name okay show patient ID so

name okay show patient ID so patient patient ID first name last name

patient patient ID first name last name and attending doctor speciality so

and attending doctor speciality so doctor and patient so patients I'll save

doctor and patient so patients I'll save it in P right and I'll

it in P right and I'll select uh P do uh patient

select uh P do uh patient ID then I'll select P do first name I'll

ID then I'll select P do first name I'll select P do last

select P do last name and attending doctor's speciality

name and attending doctor's speciality and let's save uh this as patient ID

and let's save uh this as patient ID patient

patient ID and I'll

ID and I'll save uh this one as patient first name

save uh this one as patient first name patient first name and I'll save this as

patient first name and I'll save this as patient last name okay and then doctor's

patient last name okay and then doctor's uh attending doctor speciality attending

uh attending doctor speciality attending doctor how will you find out the

doctor how will you find out the attending doctor so if I go to patients

attending doctor so if I go to patients I cannot see any row or the column that

I cannot see any row or the column that can help me get this attending doctor

can help me get this attending doctor speciality but in the admissions I can

speciality but in the admissions I can see uh attending doctor ID so I need to

see uh attending doctor ID so I need to do two joins I need to join my patients

do two joins I need to join my patients on Admissions and then admissions on

on Admissions and then admissions on doctors and then I can find

doctors and then I can find it so from patient P um join on

it so from patient P um join on admissions a

admissions a on uh P do patient ID is equal to a do

on uh P do patient ID is equal to a do admission uh uh sorry patient

admission uh uh sorry patient ID okay and then again

ID okay and then again adjoin uh doors

adjoin uh doors D on D do do

D on D do do ID um is equal to a do attending doctor

ID um is equal to a do attending doctor ID now I have got the table which has uh

ID now I have got the table which has uh both admissions doctors and patients all

both admissions doctors and patients all of these three things and now I can

of these three things and now I can fetch doctors and that means D dot

fetch doctors and that means D dot speciality speciality as attending

speciality speciality as attending attending

attending doctor

doctor speciality no there is an

speciality no there is an issue um patient ID first name last name

issue um patient ID first name last name attending doctor speciality so this

attending doctor speciality so this thing is correct what are we missing do

thing is correct what are we missing do we need to group by also okay we missed

we need to group by also okay we missed uh two two more conditions select only

uh two two more conditions select only those patient who has a diagnosis uh

those patient who has a diagnosis uh epilepsy so this

epilepsy so this where uh my a do

where uh my a do diagnosis is equal to epilepsy and the

diagnosis is equal to epilepsy and the Doctor's first name is Lisa and D

Doctor's first name is Lisa and D do um first name is

do um first name is Lisa yes this is the correct output I

Lisa yes this is the correct output I hope you got it okay let's go to the

hope you got it okay let's go to the next question um this

next question um this one all patients who have gone through

one all patients who have gone through admissions so let me remove it all

admissions so let me remove it all patients who have gone through

patients who have gone through admissions can see their medical

admissions can see their medical documents on our site okay so Admissions

documents on our site okay so Admissions and patients two things are

and patients two things are related those patients are given a

related those patients are given a temporary

temporary password after their first

password after their first admission show the patient ID and

admission show the patient ID and temporary password okay so select uh

temporary password okay so select uh patient

patient ID and I need to find out the temporary

ID and I need to find out the temporary password uh

password uh from if I join patients and

from if I join patients and admissions right and I group by on

admissions right and I group by on patient

patient ID

ID so from patients

admissions on patients. patient ID is equal to

patients. patient ID is equal to admissions. paati

admissions. paati ID so this gives me the join table right

ID so this gives me the join table right so if I just say star I get uh select

so if I just say star I get uh select star uh sorry

star uh sorry from uh right so now I've got the

from uh right so now I've got the combined table now from this combined

combined table now from this combined table

table if I fetch if I first of all group this

if I fetch if I first of all group this table on patient ID so I say Group by

table on patient ID so I say Group by uh patients. patient ID can see their

uh patients. patient ID can see their medical documents on our site those

medical documents on our site those patients are given a temporary password

patients are given a temporary password after their first admission so this

after their first admission so this first admission I need to pick

first admission I need to pick up um show the patient the temporary

up um show the patient the temporary password so password password I can

password so password password I can calculate select uh patient

calculate select uh patient ID and your temporary password so my

ID and your temporary password so my temporary password will be

temporary password will be concat uh concat of my patient ID my

concat uh concat of my patient ID my patient ID comma um this will be

patient ID comma um this will be patients okay patient ID

patients okay patient ID comma um numerical length of the

comma um numerical length of the patient's last name so length

patient's last name so length of uh last name okay and the last thing

of uh last name okay and the last thing is year of the patient's birth date year

is year of the patient's birth date year of

of patient sorry um birth date yes so this

patient sorry um birth date yes so this is the these are the two things and I

is the these are the two things and I can call it as temp

password right um from Patients Group by so if I

right um from Patients Group by so if I just remove this group by for

just remove this group by for now here as concat

now here as concat this will be like

this will be like this yes concat first name last name

this yes concat first name last name column name patient ID patients. patient

ID pa. patient ID this will be patients. patient ID patients. last name and

patient ID patients. last name and patients. birthdate yes so now I have

patients. birthdate yes so now I have got like

got like um for every patient I've got the

um for every patient I've got the temporary password now if I just group

temporary password now if I just group them on patient ID so Group by patient

them on patient ID so Group by patient ID patients. patient

ID patients. patient ID and yes we got it so it all it's it

ID and yes we got it so it all it's it depends on your like uh trial and error

depends on your like uh trial and error like not trial and error but actually

like not trial and error but actually dividing the problem into small small

dividing the problem into small small segments and solving one thing at a time

segments and solving one thing at a time and slowly slowly you form your query

and slowly slowly you form your query right so this is the art that I've have

right so this is the art that I've have been following and it is working out for

been following and it is working out for me you might discover some new art of

me you might discover some new art of writing these queries right great this

writing these queries right great this is this is actually helpful U even if

is this is actually helpful U even if like I know chat GT writes these queries

like I know chat GT writes these queries for us nowadays we don't have to write

for us nowadays we don't have to write these queries on our own but this is a

these queries on our own but this is a very good mental exercise to keep our

very good mental exercise to keep our brain active writing these queries

brain active writing these queries manually great let's see the next uh

manually great let's see the next uh question so this is the next question uh

question so this is the next question uh each admission cost $50 for patients

each admission cost $50 for patients without insurance and $10 for patients

without insurance and $10 for patients with insurance Insurance all the

with insurance Insurance all the patients with an even patient ID have

patients with an even patient ID have insurance okay so all the odd patient

insurance okay so all the odd patient IDs do not have an insurance so for them

IDs do not have an insurance so for them the cost per admission will be $50 and

the cost per admission will be $50 and for all the even patient IDs my cost

for all the even patient IDs my cost will be

will be $10 uh per admission each have patient

$10 uh per admission each have patient give each patient a yes if they have

give each patient a yes if they have insurance and no if they don't and now

insurance and no if they don't and now sum of all the admission cost for yeses

sum of all the admission cost for yeses and for Nos and then I have to show it

and for Nos and then I have to show it so I think uh uh selecting from join

so I think uh uh selecting from join group sum everything is going to get

group sum everything is going to get used so this is a very good question so

used so this is a very good question so let's try to solve

let's try to solve it from patients so if I first uh find

it from patients so if I first uh find the patient table and join it on the

the patient table and join it on the admissions table on uh

patients do patient ID is equal to admissions. patient

admissions. patient idid and if I first of all select

idid and if I first of all select like all of the rows now I've got the

like all of the rows now I've got the combined table now from this combined

combined table now from this combined table what am I interested in I am

table what am I interested in I am interested to see if

interested to see if um okay so you can see one has come to

um okay so you can see one has come to the come in the uh Hospital twice and

the come in the uh Hospital twice and one is an odd so his total cost will be

one is an odd so his total cost will be 100 right 5050

100 right 5050 100 because all do not have a insurance

100 because all do not have a insurance so what I can do uh I can say select

so what I can do uh I can say select patient

patient ID right patient ID

comma I can create a new column so for the new column I need to have uh Case

the new column I need to have uh Case Case right so I'll put this case inside

Case right so I'll put this case inside uh like this right um and this will be

uh like this right um and this will be called as um has

insurance right now you write down your case inside it

case um my case will be if my patient ID mod 2 if my patient ID mod 2 is equal to

mod 2 if my patient ID mod 2 is equal to zero that means it is even then

no okay if it is even then yes if it is uh else then

is uh else then no has insurance and if I hit okay has

no has insurance and if I hit okay has Insurance what is the issue patient ID

Insurance what is the issue patient ID wrong okay I did not write end end of

wrong okay I did not write end end of the case and then as insurance

the case and then as insurance right column name patient ID so I'll say

right column name patient ID so I'll say patients. patient

patients. patient ID patients. patient ID

ID patients. patient ID has Insurance correct yes so now I can

has Insurance correct yes so now I can see uh for this patient ID the guy does

see uh for this patient ID the guy does not have the insurance because it's an

not have the insurance because it's an odd uh patient ID and now if I group

odd uh patient ID and now if I group them on patient

them on patient ID right if I group them on patient

ID right if I group them on patient ID Group by patient ID and I also write

ID Group by patient ID and I also write down the count of the

down the count of the rows count of the rows

rows count of the rows okay so if I hit

okay so if I hit enter patients. patient ID patients dop

enter patients. patient ID patients dop patient ID

patient ID yes so I have the patient ID one is

yes so I have the patient ID one is coming twice and now if I multiply this

coming twice and now if I multiply this two by 50 right and I multiply this uh

two by 50 right and I multiply this uh two by 10 because this is even patient

two by 10 because this is even patient ID and then I sum

ID and then I sum them um right and if I sum them so I

them um right and if I sum them so I group them on patient ID instead of

group them on patient ID instead of patient if I group them on has insurance

patient if I group them on has insurance right if I group them on has

right if I group them on has Insurance yes so you can see I am

Insurance yes so you can see I am actually not interested in this patient

actually not interested in this patient ID so if I just remove this patient ID I

ID so if I just remove this patient ID I can see 2556 and 2511 but

can see 2556 and 2511 but actually I think okay let's do that no

actually I think okay let's do that no uh people this is the count and I need

uh people this is the count and I need to multiply this by

to multiply this by 50 and this by 10 and yes you can see 25

50 and this by 10 and yes you can see 25 1 1 Zer it will be correct so I'm I'm

1 1 Zer it will be correct so I'm I'm actually on the right place right I'm

actually on the right place right I'm actually doing it correctly so Group by

actually doing it correctly so Group by this so you find out the sum count as

this so you find out the sum count as this and if I save the

this and if I save the count

count as uh let's say the count so instead of

as uh let's say the count so instead of this count we have got this count for

this count we have got this count for our own understanding but instead of

our own understanding but instead of this count I need to find out the

this count I need to find out the sum of the multiply lied uh cost okay so

sum of the multiply lied uh cost okay so instead of this I'll write down again

instead of this I'll write down again another like case so this is my first

another like case so this is my first case right and then another

case right and then another Case Case uh and this time this case

Case Case uh and this time this case will be like multiply uh like added also

will be like multiply uh like added also whatever the value I'm going to return

whatever the value I'm going to return so case uh when my patients patient ID

so case uh when my patients patient ID more 2 equal to

more 2 equal to 0 case when this then my value will be

0 case when this then my value will be 50 okay then my value will be uh 50 my

50 okay then my value will be uh 50 my value will be 10 because the person has

value will be 10 because the person has the insurance else it will be

the insurance else it will be 50 right and end it and then it is going

50 right and end it and then it is going to take all of these 10 and add it and

to take all of these 10 and add it and you can save it you can call it as cost

you can save it you can call it as cost after uh

after uh Insurance C after

Insurance C after Insurance yes this is right so this was

Insurance yes this is right so this was actually tricky it was not tricky till

actually tricky it was not tricky till we had reached this part and this part

we had reached this part and this part became tricky because over here we had

became tricky because over here we had to actually we were not interested in

to actually we were not interested in the like the rows number count we were

the like the rows number count we were actually interested in the final sum so

actually interested in the final sum so we wrote the case and whatever value it

we wrote the case and whatever value it is returning we keep on adding it and

is returning we keep on adding it and then we had dried it by the has

then we had dried it by the has insurance so till here it was all easy

insurance so till here it was all easy but this point you might have to like

but this point you might have to like see the solution and get the

see the solution and get the understanding okay uh let's see the next

understanding okay uh let's see the next question so the question says show all

question so the question says show all the provinces that has more patients

the provinces that has more patients identified as male than female must only

identified as male than female must only show full Province

show full Province name okay I think group on the

name okay I think group on the gender group on the gender or group on

gender group on the gender or group on the province

the province name let's

name let's see from where does the province name

see from where does the province name comes up so in the p I have this

comes up so in the p I have this province ID and then I have this

province ID and then I have this province name so most probably I have to

province name so most probably I have to join uh patients and Province name so

join uh patients and Province name so from

patients uh P

P join Province

join Province names

names PR

PR select um patient

ID gender and Province name

name okay uh join on patients

okay uh join on patients do province

do province ID is equal to Province names do

ID is equal to Province names do province

province ID what is the issue uh it will be P Dot

ID what is the issue uh it will be P Dot and this will be P dot okay and this

and this will be P dot okay and this will be P Dot and this will be P Dot and

will be P Dot and this will be P Dot and this will PR Dot

this will PR Dot r dot

r dot okay yes so you can see all my patients

okay yes so you can see all my patients uh their genders and their Province name

uh their genders and their Province name so now what I have to do I have to group

so now what I have to do I have to group them on Province name so if I group them

them on Province name so if I group them PR do province name so I can

PR do province name so I can see patient

ID and let me also see the count

count yes and I'm not interested in the patient ID now so I removed the patient

patient ID now so I removed the patient ID and I can see the genders male in

ID and I can see the genders male in Alberta are

Alberta are 47 female in British Columbia are

47 female in British Columbia are 49 male in manibo Manitoba are 31 so if

49 male in manibo Manitoba are 31 so if I use

I use having having and if I just write a

having having and if I just write a simple case right if I just write the

simple case right if I just write the simple

simple case

case having uh case when my P do gender count

having uh case when my P do gender count of p. gender count of um P do p do

of p. gender count of um P do p do gender is equal to male actually P do

gender is equal to male actually P do gender P do gender is equal to male uh

gender P do gender is equal to male uh this will be when male then I can say as

this will be when male then I can say as one right and

one right and then one else zero so at the end I'm

then one else zero so at the end I'm going to I'm just counting all the males

going to I'm just counting all the males in that uh males for that group and the

in that uh males for that group and the females for that group and if this count

females for that group and if this count is greater

is greater than like male count for that province

than like male count for that province is because I have grouped by on Province

is because I have grouped by on Province right so for this Alberta if the males

right so for this Alberta if the males are more than if the count of the males

are more than if the count of the males are more than the count of case

are more than the count of case when P do gender is equal to

when P do gender is equal to female then call one else zero and let's

female then call one else zero and let's write end over here and end over here

write end over here and end over here right so what I'm doing uh I just first

right so what I'm doing uh I just first of all grouped by on the province name

of all grouped by on the province name now for every group I need to find which

now for every group I need to find which are those groups which are having the

are those groups which are having the male counts more than the female counts

male counts more than the female counts right I think this else is creating the

right I think this else is creating the issue so if I remove this

issue so if I remove this else yeah because we only need to

else yeah because we only need to consider

consider one when the gender is

one when the gender is male right and otherwise I'm not

male right and otherwise I'm not interested so if I hit

interested so if I hit enter uh I don't need the

count um and I don't need this gender yes this is the right one so this

gender yes this is the right one so this part was tricky because because over

part was tricky because because over here what what we are doing we are we

here what what we are doing we are we need to find out those groups so I first

need to find out those groups so I first grouped by on the province name and then

grouped by on the province name and then I'm only interested in those groups

I'm only interested in those groups which have count of the male

which have count of the male genders right more than the female

genders right more than the female genders and for that I use

genders and for that I use count uh I need to count one like one

count uh I need to count one like one will be incremented whenever you find a

will be incremented whenever you find a gender m in the rows right otherwise I'm

gender m in the rows right otherwise I'm not interested

not interested if this count increments uh like is more

if this count increments uh like is more than this one female count then you

than this one female count then you consider that Province name okay so this

consider that Province name okay so this is the next question uh we are looking

is the next question uh we are looking for a specific patient pull all the

for a specific patient pull all the columns for the patient okay so let's

columns for the patient okay so let's remove this and we are interested in the

remove this and we are interested in the patients uh table pull all the columns

patients uh table pull all the columns that means select Star right from

patients uh who matches the following criteria first first name contains an R

criteria first first name contains an R after the first two letters okay from

after the first two letters okay from patients where first name so first name

patients where first name so first name contains an R so first name

contains an R so first name like first two characters after the

like first two characters after the first two letters so uh underscore

first two letters so uh underscore underscore and then contains an r r and

underscore and then contains an r r and then percentage so anything can be after

then percentage so anything can be after this R so this is the first thing

this R so this is the first thing and I identifies the identifies their

and I identifies the identifies their gender as female so gender is equal to F

gender as female so gender is equal to F right

right and born in February May or December so

and born in February May or December so born that means birth date we have to

born that means birth date we have to pick up and we have to find out the

pick up and we have to find out the month from this birth date and

month from this birth date and um month from this birth date is lying

um month from this birth date is lying in uh this array so Feb means two uh May

in uh this array so Feb means two uh May means 5 and December means 12 right and

means 5 and December means 12 right and then it will be and next condition is

then it will be and next condition is their weight would be between 60 and 80

their weight would be between 60 and 80 so weight

so weight between

between 60 uh and

60 uh and 80

80 and uh there patient ID is an odd number

and uh there patient ID is an odd number so patient ID mod 2 mod 2 uh is not zero

so patient ID mod 2 mod 2 uh is not zero so is not

so is not zero they are from City Kingston so

zero they are from City Kingston so City uh is equal to

City uh is equal to Kingston

Kingston King St and it will be

King St and it will be and so there's a problem um okay yeah

and so there's a problem um okay yeah this from will not come yes it is

this from will not come yes it is working perfect so it was simple you

working perfect so it was simple you just have to to work with a lot of

just have to to work with a lot of conditions let's go to the next

conditions let's go to the next question so this is the next question so

question so this is the next question so this is the next question uh show the

this is the next question uh show the percentage of patients that have male as

percentage of patients that have male as their gender and round the answer to the

their gender and round the answer to the nearest 100th number in their percentage

nearest 100th number in their percentage form okay so you can see this uh see

form okay so you can see this uh see this uh example

this uh example 54.4 so nearest 100th number okay okay

54.4 so nearest 100th number okay okay okay so first of all we need to find out

okay so first of all we need to find out all the males in the uh table then we

all the males in the uh table then we need to divide it by total number of

need to divide it by total number of patient so that we get the fraction then

patient so that we get the fraction then we need to multiply it by 100 to get the

we need to multiply it by 100 to get the percentage and then we divide by 100 so

percentage and then we divide by 100 so that we um divide by some some number

that we um divide by some some number that it brings the percentage to the

that it brings the percentage to the nearest 100 uh rounded off to 100 uh 100

nearest 100 uh rounded off to 100 uh 100 uh nearest 100 right so we need to do

uh nearest 100 right so we need to do this so first of all let's write a query

this so first of all let's write a query that helps me get all the males the

that helps me get all the males the number of males in this table so select

number of males in this table so select count uh star so it it uh counts all the

count uh star so it it uh counts all the rows from patients so this is 4530 all

rows from patients so this is 4530 all the patients in my table now if I write

the patients in my table now if I write a condition where

a condition where gender is equal to male this gives me

gender is equal to male this gives me all the males in my table right so this

all the males in my table right so this is one thing now what I need to do I

is one thing now what I need to do I need to wrap it up as an inner query

need to wrap it up as an inner query right I need to wrap it up as an inner

right I need to wrap it up as an inner query and this will be my outer query

query and this will be my outer query select and at the bottom I will have uh

select and at the bottom I will have uh patients right so and if I yes so this

patients right so and if I yes so this is my outer query and this is my inner

is my outer query and this is my inner query now inside I need to also divide

query now inside I need to also divide it by the number of rows in this patient

it by the number of rows in this patient so that I get the fraction of uh ratio

so that I get the fraction of uh ratio of the males right so after this I need

of the males right so after this I need to divide it by the

count Star right and this should be treated as

Star right and this should be treated as float

float right uh this should be treated as the

right uh this should be treated as the uh float uh because I need to round it

uh float uh because I need to round it off so if I say select and this will be

off so if I say select and this will be gone from here this will be over here

gone from here this will be over here and if I say round it off uh round it

and if I say round it off uh round it off this fraction as float and how many

off this fraction as float and how many digits let's say four digits right four

digits let's say four digits right four digits why so that I can divide by 100

digits why so that I can divide by 100 and I can get the nearest 100

and I can get the nearest 100 so that's why like rounded off to four

so that's why like rounded off to four decimal places so that's what I'm doing

decimal places so that's what I'm doing right now and I can uh call it as male

right now and I can uh call it as male ratio okay so the issue is

ratio okay so the issue is round okay this will not come here this

round okay this will not come here this will come over

here as float so when I ran this uh query uh just the inner query you can

query uh just the inner query you can see this is what I've got right so and I

see this is what I've got right so and I can also definitely change the uh column

can also definitely change the uh column name male ratio but now I'm not finding

name male ratio but now I'm not finding the male ratio I'm just finding the

the male ratio I'm just finding the number of males in my table so I need to

number of males in my table so I need to actually divided by the number of uh

actually divided by the number of uh patients right so I'll divide

patients right so I'll divide it by uh I'll divide it by count star

it by uh I'll divide it by count star count Star right I divide it by count

count Star right I divide it by count star and I'll also convert this um to

star and I'll also convert this um to float right because if I do this I get

float right because if I do this I get zero right so I need to get the decimal

zero right so I need to get the decimal uh I need to treat it as float so that

uh I need to treat it as float so that it can actually divide it because this

it can actually divide it because this one will be integer and I need to

one will be integer and I need to actually get in the form of float so

actually get in the form of float so that I can find the percentage so this

that I can find the percentage so this one will be treated like this I can use

one will be treated like this I can use cost this car uh count as float so that

cost this car uh count as float so that it gives me the float value right and I

it gives me the float value right and I need to round round it off you can see

need to round round it off you can see this is what I've got right so

this is what I've got right so previously it was Zero because both were

previously it was Zero because both were integers so it will give me the integer

integers so it will give me the integer only but if I convert one of them as a

only but if I convert one of them as a float then it will actually give me the

float then it will actually give me the float and now I need to round it off

float and now I need to round it off round this

round this off this whole thing off to four decimal

off this whole thing off to four decimal places so that I can divide by 100 and I

places so that I can divide by 100 and I get get to

get get to um the near nearest 100 right so if I do

um the near nearest 100 right so if I do this you can see I got 54 uh sorry

this you can see I got 54 uh sorry 5448 now if I multiply it by

5448 now if I multiply it by 100 right if I multiply this by 100 I

100 right if I multiply this by 100 I get

get 54.4 and then I can call it as uh

54.4 and then I can call it as uh male percent 54.4 and now I also need to

male percent 54.4 and now I also need to attach the percentage sign so for that I

attach the percentage sign so for that I need to use concat so one more thing we

need to use concat so one more thing we need to use con at uh this whole thing

need to use con at uh this whole thing uh till here and then you attach

uh till here and then you attach a

a percent and now our query is correct so

percent and now our query is correct so a lot of things we had to do but

a lot of things we had to do but ultimately we got this right okay let's

ultimately we got this right okay let's move on to the next question so this is

move on to the next question so this is the next question uh for each day

the next question uh for each day display the total number of total amount

display the total number of total amount of admissions on that day display the

of admissions on that day display the amount change from the previous day so

amount change from the previous day so whenever you get such uh questions very

whenever you get such uh questions very actually go back and take the previous

actually go back and take the previous value you use something called as lag

value you use something called as lag lag in SQL okay so this is a lag

lag in SQL okay so this is a lag function this is a window function right

function this is a window function right so what it does it actually helps you so

so what it does it actually helps you so one of the sql's window function that

one of the sql's window function that allows you to create a new column that

allows you to create a new column that accesses a previous Row from the another

accesses a previous Row from the another column okay so it will help you get

column okay so it will help you get access to the previous row and that's

access to the previous row and that's what it is asked in the question right

what it is asked in the question right you need to get the number of admissions

you need to get the number of admissions of the previous day so that you can find

of the previous day so that you can find out the change so you can see on this

out the change so you can see on this day on 7th minus 8 right and on 8th the

day on 7th minus 8 right and on 8th the change is 0o then on 9th the change is 9

change is 0o then on 9th the change is 9 because 18 - 9 is 9 9 - 9 is 0 9 - 17 is

because 18 - 9 is 9 9 - 9 is 0 9 - 17 is - 8 so that's how so for such cases we

- 8 so that's how so for such cases we use that window function called as lag

use that window function called as lag right so we use a combination of lag and

right so we use a combination of lag and over okay so number of admissions so we

over okay so number of admissions so we are interested in the admissions table

are interested in the admissions table admissions uh and we need to group it by

admissions uh and we need to group it by the

the date right need to group it by the

date right need to group it by the date so Group

by um admission date so Group by admission date and for now if I just say

admission date and for now if I just say select star I get all

select star I get all the I get all the admissions which are

the I get all the admissions which are grouped by the admission date and I'm

grouped by the admission date and I'm only interested in the admission

only interested in the admission date

date right and I need to show admission day

right and I need to show admission day uh this is admission date and this is

uh this is admission date and this is not admission date there should be a

not admission date there should be a number of admissions on that day right

number of admissions on that day right the number of admissions on that day so

the number of admissions on that day so I can say admission date and then I can

I can say admission date and then I can say

say count um count of admission day uh sorry

count um count of admission day uh sorry admission uh date uh as admission day so

admission uh date uh as admission day so basically what I'm trying to do I'm

basically what I'm trying to do I'm actually trying to count all the

actually trying to count all the admission dates for that day right so

admission dates for that day right so for six how many six uh Junes are there

for six how many six uh Junes are there in the group right how many seven Junes

in the group right how many seven Junes are there in the group so that I can

are there in the group so that I can actually find out how many admissions

actually find out how many admissions happened on that day right so if I do

happened on that day right so if I do this so you can see uh on 6th 17 on 7th

this so you can see uh on 6th 17 on 7th 9 on 8th 9 now only one thing is left I

9 on 8th 9 now only one thing is left I need another column over here so I just

need another column over here so I just need another column which we need to

need another column which we need to create so that will also be the count

create so that will also be the count but that count will be uh admission day

but that count will be uh admission day date sorry admission date uh but minus

date sorry admission date uh but minus so I need to find the difference from

so I need to find the difference from the previous value so previous value you

the previous value so previous value you will find out by using lag so if you use

will find out by using lag so if you use lag right if you use lag uh lag of count

lag right if you use lag uh lag of count of admission uh date only admission uh

of admission uh date only admission uh date only so lag and then you you say

date only so lag and then you you say over over which column

over over which column over uh ordered by because you need to

over uh ordered by because you need to order it from the uh as per the date

order it from the uh as per the date also order by admission

also order by admission date right and then you can call this as

date right and then you can call this as change yes so you can see the see the

change yes so you can see the see the quer is working right so first of all

quer is working right so first of all you grouped the table by admission date

you grouped the table by admission date then you found out all you you basically

then you found out all you you basically found out how many admissions happened

found out how many admissions happened on that date and then you try to find

on that date and then you try to find out the difference from those admissions

out the difference from those admissions date minus the previous date admissions

date minus the previous date admissions right that's it so lag and over are the

right that's it so lag and over are the two new uh uh keywords that you have

two new uh uh keywords that you have learned in this query so let's go ahead

learned in this query so let's go ahead and see the next uh question sort the

and see the next uh question sort the province names okay so this is the

province names okay so this is the province names table in ascending

province names table in ascending order in such a way that the province on

order in such a way that the province on Ontario is always on the top okay so

Ontario is always on the top okay so first of all if you just not if you were

first of all if you just not if you were not given this part can you do that yes

not given this part can you do that yes very then it's an easy question

very then it's an easy question select um Province names so if first of

select um Province names so if first of all write down the table name from

all write down the table name from Province uh names Province

Province uh names Province names order

names order by province name and then you say select

by province name and then you say select Province

Province name you see all the province names are

name you see all the province names are over here but onario you need at the top

over here but onario you need at the top so you can just write a very very simple

so you can just write a very very simple over here uh from

this case if uh case when Province name is

case if uh case when Province name is equal to

equal to Ontario

onario then you say it as any integer right because integers are kept below uh

right because integers are kept below uh sorry kept kept above alphabetical Alpha

sorry kept kept above alphabetical Alpha alphabets so if you if you found antario

alphabets so if you if you found antario if you make it like zero right uh then

if you make it like zero right uh then your antario will always be considered

your antario will always be considered at the

at the top right so case this and you end it

top right so case this and you end it and you see okay so there's an issue

and you see okay so there's an issue case when Province name is onario then

case when Province name is onario then zero then end um near

case Okay order by this will not be provin names order

by this will not be provin names order by this then and Province

by this then and Province name

name um case is

um case is this like this and this one 30 than zero

this like this and this one 30 than zero else I can say Province name yes so

else I can say Province name yes so basically uh what we are doing over here

basically uh what we are doing over here is uh we are ordering by province name

is uh we are ordering by province name but if your Province name is onario then

but if your Province name is onario then your uh order will be zero that means

your uh order will be zero that means I'm just replacing onario with zero

I'm just replacing onario with zero right otherwise Province name the order

right otherwise Province name the order will be on Province name so for all the

will be on Province name so for all the other provinces order will be on

other provinces order will be on Province name but for onario it will be

Province name but for onario it will be considered as zero or you can write like

considered as zero or you can write like four right uh I have to select the

four right uh I have to select the question again so let me just copy this

question again so let me just copy this I'm just trying to say that whenever you

I'm just trying to say that whenever you need to prioritize this just assign it

need to prioritize this just assign it with an integer because integers are

with an integer because integers are kept uh before an alphabet right so if I

kept uh before an alphabet right so if I show you again and if I just kept keep

show you again and if I just kept keep four uh okay now what is the issue three

four uh okay now what is the issue three is also working four should also work

is also working four should also work actually you need to again select the

question okay yeah congratulations yes so our fourth is also working so I need

so our fourth is also working so I need to again select the question that's why

to again select the question that's why but yeah fourth is also working that

but yeah fourth is also working that means if you keep any integer okay one

means if you keep any integer okay one more time I'll show you if you are in

more time I'll show you if you are in doubt I'll just select this this okay

doubt I'll just select this this okay keep it like this if I make it like nine

keep it like this if I make it like nine you can see it is working so you can

you can see it is working so you can keep any integer because integers are

keep any integer because integers are parze more than the alphabets perfect

parze more than the alphabets perfect let's see the last question of this

let's see the last question of this database so this is the next question uh

database so this is the next question uh we need a

we need a breakdown for the total number of

breakdown for the total number of admissions each doctor has started each

admissions each doctor has started each year so for every doctor so you can see

year so for every doctor so you can see uh in this um hints in the expected

uh in this um hints in the expected output CLA walls uh uh in 2018 he did

output CLA walls uh uh in 2018 he did 105 admissions same doctor in 2019 did

105 admissions same doctor in 2019 did 109 uh admissions so basically we need

109 uh admissions so basically we need to group them on Doctor also and on uh

to group them on Doctor also and on uh ear

ear also so some grouping is is happening

also so some grouping is is happening over here so okay so some grouping is

over here so okay so some grouping is happening so grouping is happening on

happening so grouping is happening on the

the ear and the doctor name so year is year

ear and the doctor name so year is year you can find from the admissions table

you can find from the admissions table but doctor uh um ID you will find in the

but doctor uh um ID you will find in the doctor's table so you need to join

doctor's table so you need to join them okay so from doctors

them okay so from doctors D

D join admissions a and you group them by

join admissions a and you group them by um

um do

do ID right so D do

ID right so D do do ID and you group them by year

admission that means a DOT admission date and then you say select uh

date and then you say select uh star okay so what I've got over here uh

star okay so what I've got over here uh I can see clo walls is so I can see two

I can see clo walls is so I can see two rows uh of clo walls

rows uh of clo walls for admission date yes I can see 2018 29

for admission date yes I can see 2018 29 2019 so I think it has grouped them

2019 so I think it has grouped them correctly but now what I need to do I

correctly but now what I need to do I need to uh I need to show the year right

need to uh I need to show the year right so what I can do I can

so what I can do I can show

show um

year a do admission date as year

um uh let's call it selected year as in the expected output selected

the expected output selected Year yes and I also need to show Doctor

Year yes and I also need to show Doctor full name uh it is asked in the question

full name uh it is asked in the question actually so that's why I'm doing it so

actually so that's why I'm doing it so Dr full name okay so Dr ID and dror full

Dr full name okay so Dr ID and dror full name so I'll say uh d. Dr ID comma

name so I'll say uh d. Dr ID comma concat

concat concat uh d. first name comma and a

concat uh d. first name comma and a space d do last

space d do last name as full name

okay so I have created the doctor ID I have created the full name column and

have created the full name column and I've created the selected

I've created the selected year um what is the issue and this year

year um what is the issue and this year can actually be gone because we can

can actually be gone because we can replace it with the selected

year a or admission date full name d last year okay yeah this is correct your

last year okay yeah this is correct your doctor ID first name selected year

doctor ID first name selected year ear near

ear near ear okay oh yeah this there will be

ear okay oh yeah this there will be comma yes so I can see Cloe walls 2018

comma yes so I can see Cloe walls 2018 CL walls 2019 uh Joshua green 2018

CL walls 2019 uh Joshua green 2018 Joshua green 2019 okay so this is

Joshua green 2019 okay so this is correct now I just need to find out the

correct now I just need to find out the count for each

count for each year right I need to find uh and also we

year right I need to find uh and also we need to print the speciality so I'll

need to print the speciality so I'll also say before year we also need to uh

also say before year we also need to uh say D do

say D do speciality yes now this column is also

speciality yes now this column is also done now I just need to find out the

done now I just need to find out the count so count count count okay so after

count so count count count okay so after the year I can say

the year I can say count

count Star as

Star as total

total admissions okay so 2915 2152 2915

admissions okay so 2915 2152 2915 2152 no so one mistake we are doing what

2152 no so one mistake we are doing what are we doing wrong join the table I

are we doing wrong join the table I think okay so one thing is uh this join

think okay so one thing is uh this join we need to do it on uh on a row on on a

we need to do it on uh on a row on on a column so D do do ID is equal to a do uh

column so D do do ID is equal to a do uh attending doctor ID attending doctor

attending doctor ID attending doctor ID yes so this is correct so the query

ID yes so this is correct so the query is working fine so what we have

written uh let's read rise it I'm also confused now let's revise it uh first of

confused now let's revise it uh first of all I joined uh the doctor's table and

all I joined uh the doctor's table and admissions table on this

admissions table on this ID right then I grouped this table on Dr

ID right then I grouped this table on Dr ID and selected year right because I I

ID and selected year right because I I needed the club values for a doctor uh

needed the club values for a doctor uh and the number of admissions they're

and the number of admissions they're doing for that year okay and then

doing for that year okay and then um and then I created all the columns

um and then I created all the columns doctor ID then full name speciality and

doctor ID then full name speciality and the year

the year right so I I think this is

right so I I think this is correct you got the query correct

correct you got the query correct yeah in the solution I'm just checking

yeah in the solution I'm just checking out there they have used left join and

out there they have used left join and they've also ordered ordered was not

they've also ordered ordered was not asked in the question actually but let

asked in the question actually but let me know uh so if you if you will see the

me know uh so if you if you will see the solution so this is the solution let me

solution so this is the solution let me paste it over here so this is the

paste it over here so this is the solution that they have given and they

solution that they have given and they have ordered it and they have used left

have ordered it and they have used left join let me know in the comment section

join let me know in the comment section why do you think they have used left

why do you think they have used left join and order by it was not asked in

join and order by it was not asked in the question to order it okay so let me

the question to order it okay so let me let me know and yes so this will be good

let me know and yes so this will be good now we have covered everything from the

now we have covered everything from the hospital DB now it's chance let's

hospital DB now it's chance let's quickly do Northwind DB right and the

quickly do Northwind DB right and the good part is for the Northwind

good part is for the Northwind DB if I uh put this filter the questions

DB if I uh put this filter the questions are not much we can actually cover it

are not much we can actually cover it very quickly so let's do that let's wrap

very quickly so let's do that let's wrap all of these questions okay uh so this

all of these questions okay uh so this is the first question uh from the new

is the first question uh from the new database right so let's get started and

database right so let's get started and this is an easy question so we'll try to

this is an easy question so we'll try to cover these easy questions quickly as

cover these easy questions quickly as quickly as possible show the category

quickly as possible show the category name and description from the categories

name and description from the categories so I have this categories stable and I

so I have this categories stable and I have to show category name and

have to show category name and description category name and

description category name and description okay sorted by the category

description okay sorted by the category name great so select star

name great so select star um from

um from categories right from

categories right from categories select category

categories select category name and

name and description right order

description right order by category

by category name yes this is right let's go to the

name yes this is right let's go to the next

next question show all the contact name

question show all the contact name address city of all customers which are

address city of all customers which are not from Germany Mexico and Spain so if

not from Germany Mexico and Spain so if I go to the customers table um I have

I go to the customers table um I have this country yes

this country yes so from

so from customers uh yes

customers uh yes select contact

select contact name

name address

City uh from customers where uh

where uh country

country not

not in this array and array is uh Germany

quotes uh next is Mexico and last is

Mexico and last is Spain yes this is right okay I hope you

Spain yes this is right okay I hope you got it we have already covered these

got it we have already covered these Concepts let's go to the next question

Concepts let's go to the next question show order date

show order date so orders yes order date shipped date

so orders yes order date shipped date order date is over here ship date is

order date is over here ship date is over here customer

over here customer ID okay fright of all orders placed on

ID okay fright of all orders placed on 2018 Feb

26 fright is also here okay so from orders where and

orders where and select order

and fright where

fright where um order

date is equal to uh 2018 FB

to uh 2018 FB 26 either you can compare the date or

26 either you can compare the date or you can

you can say where my year of order

say where my year of order date is equal to uh 2018

date is equal to uh 2018 and my

and my day um order

day um order date is equal to

date is equal to 26 and my

26 and my month order date is equal to

month order date is equal to two and this uh this is G giving me an

two and this uh this is G giving me an issue uh what is the issue ship date

issue uh what is the issue ship date customer ID we forgot so if I just write

customer ID we forgot so if I just write customer

customer ID this is right great let's go to the

ID this is right great let's go to the next

next question show the employee ID order

question show the employee ID order ID customer ID required date ship date

ID customer ID required date ship date from all orderers shipped later than the

from all orderers shipped later than the required

required date from

orders select employee ID order

select employee ID order ID customer

ID customer ID required dat from orders uh

ID required dat from orders uh where

where my ship

my ship date later so greater than required date

date later so greater than required date okay so there is an issue employee ID

okay so there is an issue employee ID order ID customer ID required date and

order ID customer ID required date and we also need to say ship

we also need to say ship date yes this is right great let's go to

date yes this is right great let's go to the next question show all the even

the next question show all the even numbered order ID from orders

numbered order ID from orders table

table orders and

orders and Order ID where your order ID mod 2 equal

Order ID where your order ID mod 2 equal to Z this is right let's go the next

to Z this is right let's go the next question show the city company name

question show the city company name contact name of all

customers show the city company name contact name of all customers from City

contact name of all customers from City from cities

customers city which contain the letter L in the

city which contain the letter L in the city name sorted by the contract name

city name sorted by the contract name okay contain the letter

L from

from customers select City company

customers select City company name contract

name contract name of all customers from cities which

name of all customers from cities which contain the letter L okay from customers

contain the letter L okay from customers where uh your

city like anything below uh sorry anything

like anything below uh sorry anything before capital L and anything after

before capital L and anything after capital L

capital L okay and the city name sorted by so

okay and the city name sorted by so order

order by contact

by contact name this is right let's go to the next

name this is right let's go to the next question show the company name contract

question show the company name contract name fax number of all customers that

name fax number of all customers that has a fax number not null very easy so

has a fax number not null very easy so customers uh company

customers uh company name contract

name fax number uh fax number okay fax fax from

number uh fax number okay fax fax from customers uh where your

customers uh where your fax is not null is

fax is not null is not null this is right next question

not null this is right next question show the first name last name

show the first name last name hire date of the most recently hired

hire date of the most recently hired employee

employee okay so

okay so employees right so from

employees right so from employees I need to select the date

employees I need to select the date which just comes like you know which is

which just comes like you know which is the most recent date uh show the first

the most recent date uh show the first name and last

name and last name first

name first name last

name last name higher date

okay uh order

uh order by higher

by higher date in descending and I'll say limit

date in descending and I'll say limit one this is right so I'm just uh sorting

one this is right so I'm just uh sorting my table employees on higher date in

my table employees on higher date in descending order so that the most recent

descending order so that the most recent date comes at the top and I just want to

date comes at the top and I just want to return one so limit one this is the

return one so limit one this is the solution right or you can see this is

solution right or you can see this is the solution that they have given first

the solution that they have given first name last name Max of the higher dat as

name last name Max of the higher dat as higher dat from employees so that is

higher dat from employees so that is also correct both are correct okay let's

also correct both are correct okay let's go to the next question in this one

go to the next question in this one problem is you have to actually order by

problem is you have to actually order by order by all the rows first and then you

order by all the rows first and then you can return it this is actually better

can return it this is actually better this is the next question show the

this is the next question show the average unit price rounded to decimal

average unit price rounded to decimal places the total units in the stock

places the total units in the stock total discontinued products from the

total discontinued products from the products

products table this is the products

table this is the products table okay

table okay so from

so from products uh I need to select average of

products uh I need to select average of the unit price so average of the unit

the unit price so average of the unit price so I can say and round it to two

price so I can say and round it to two decimal places so round average uh of

decimal places so round average uh of the unit price unit price uh rounded to

the unit price unit price uh rounded to two decimal places so this is the first

two decimal places so this is the first one I can call it as uh average

one I can call it as uh average price right next thing

price right next thing is total units in the stock total

is total units in the stock total units in the stock units in stock so

units in the stock units in stock so sum sum of the units in

sum sum of the units in stock as total

stock right and last total discontinued products discontinued so wherever my

products discontinued so wherever my discon continued is true I need to

discon continued is true I need to increment my count okay so count uh case

increment my count okay so count uh case wherever so case when you're

discontinued is true is true

uh then one end and wherever my discontinu is

one end and wherever my discontinu is true uh my count will increase increase

true uh my count will increase increase by one okay and let's call it as total

by one okay and let's call it as total discontinue total

discontinue total dis discontinued right from products yes

dis discontinued right from products yes this is right so you just have to use

this is right so you just have to use some mathematical aggregate functions

some mathematical aggregate functions and you will be done right great uh

and you will be done right great uh let's go to the next

let's go to the next question um okay my all easy questions

question um okay my all easy questions are done so I need to go to the medium

are done so I need to go to the medium questions

questions now okay uh show the product name

now okay uh show the product name company name category name from the

company name category name from the product suppliers and category

stable okay show the product name name category name product name from the

products uh I'll pick the product name okay company's uh table is there a

okay company's uh table is there a company's table

company's table no supplier

no supplier table yes supplier table is there

table yes supplier table is there company name and categories categories

company name and categories categories table category

table category name okay so I think I need to join

name okay so I think I need to join these three tables

these three tables so I'll say from uh

so I'll say from uh products

products join

join suppliers

suppliers join

join categories

categories right um products and suppliers products

right um products and suppliers products is over here supplier ID so I can join

is over here supplier ID so I can join this

this on products. supplier ID is equal to

on products. supplier ID is equal to suppliers do

suppliers do supplier ID then join uh categories so

supplier ID then join uh categories so categories so if I go to the categories

categories so if I go to the categories uh I can see category ID so if I go to

uh I can see category ID so if I go to the products I I should see category ID

the products I I should see category ID yes so

yes so products dot category ID is equal to

products dot category ID is equal to categories do category ID correct uh

categories do category ID correct uh select product name product name company

select product name product name company name company name and category name

name company name and category name category

category name um what is the

issue syntax issue okay select product name company name category name category

name company name category name category name product name category name yes from

name product name category name yes from products join this products. supplier ID

products join this products. supplier ID is equal to suppliers. supplier

is equal to suppliers. supplier ID join

ID join categories um what is the issue

categories um what is the issue near this near dot on this okay yes here

near this near dot on this okay yes here on is missing and you're right so I just

on is missing and you're right so I just did a silly mistake let's go to the next

did a silly mistake let's go to the next question uh this one show the category

question uh this one show the category name and the average show the category

name and the average show the category name and the average product unit price

name and the average product unit price for each category rounded to two decimal

for each category rounded to two decimal place each category that means grouping

place each category that means grouping by

by category okay average product unit price

category okay average product unit price so if I go to the categories

um show the category name and the average product unit price average

average product unit price average product unit price product unit price

product unit price product unit price product and I can see the unit

product and I can see the unit price so that means joining uh product

price so that means joining uh product table and the category table so if I say

table and the category table so if I say select star from um my table is

select star from um my table is products join

products join categories on products do category ID is

categories on products do category ID is equal to categories dot category ID this

equal to categories dot category ID this is the complete table now if I uh group

is the complete table now if I uh group this

this table

table on category

on category name right category name um Group by

name right category name um Group by category

category name uh now this table is grouped by the

name uh now this table is grouped by the category name right I guess

category name right I guess yes okay and from this I need to

yes okay and from this I need to return average of the product unit price

return average of the product unit price so this from will go down and this will

so this from will go down and this will say round

average uh unit price rounded to decimal

price rounded to decimal places uh and you also need to return

places uh and you also need to return the category name category name

the category name category name comma yes this is right so I'm just

comma yes this is right so I'm just writing the category name and then the

writing the category name and then the average unit price up to two decimal

average unit price up to two decimal places from this uh table which are

places from this uh table which are joined and categories table and products

joined and categories table and products table are joined and then you grouped by

table are joined and then you grouped by the category name awesome next

the category name awesome next question so so this is the next question

question so so this is the next question uh I need to show City company name

uh I need to show City company name contract name from customers table and

contract name from customers table and supply suppliers table merge together

supply suppliers table merge together okay can I merge customers table and

okay can I merge customers table and suppliers table if you see no there is

suppliers table if you see no there is no primary key which connects both of

no primary key which connects both of them right so you cannot do that so you

them right so you cannot do that so you have to you have to directly merge the

have to you have to directly merge the two table result or two table queries

two table result or two table queries right and you can do that using Union

right and you can do that using Union right so I'll go query by query so the

right so I'll go query by query so the first query is going to say select uh

first query is going to say select uh city city uh from customers right what

city city uh from customers right what you need to select from customers table

you need to select from customers table City come compan

City come compan name contract

name contract name from the customers uh

name from the customers uh Union

Union select uh from the suppliers I need to

select uh from the suppliers I need to select uh okay same same uh column City

select uh okay same same uh column City company

company name and contact

name and contact name from

name from suppliers so if you do that you can see

suppliers so if you do that you can see you get uh like for customers also and

you get uh like for customers also and for suppliers also but in the result if

for suppliers also but in the result if you see you have to actually show the

you see you have to actually show the relationship whether this is a customer

relationship whether this is a customer or this is a supplier right so I can say

or this is a supplier right so I can say um

um customers customers as relationship

customers customers as relationship relationship uh and over here

relationship this is correct right so I just show Union to uh like like combine

just show Union to uh like like combine the merge the results of these two

the merge the results of these two queries let's go to the next

queries let's go to the next question so I group by year and month

question so I group by year and month and if I just say select star just to

and if I just say select star just to see we have got this output uh total

see we have got this output uh total total amount of orders total amount of

total amount of orders total amount of orders orders month and Order month okay

orders orders month and Order month okay so order year and Order month so from

so order year and Order month so from this if I pick uh order date okay so if

this if I pick uh order date okay so if I just say year uh sorry

I just say year uh sorry year um

year um order date as

order date as order year right um comma then

order year right um comma then month

month order date as order

order date as order order month sorry yeah no this is

order month sorry yeah no this is correct and this will be order month

correct and this will be order month order month then you can see order year

order month then you can see order year and Order month we have and this is

and Order month we have and this is actually grouped correctly and now I

actually grouped correctly and now I need to just count the number of orders

need to just count the number of orders so I can just

so I can just say count star this is correct see I

say count star this is correct see I first gri by by year and month and then

first gri by by year and month and then I uh created my column that's it simple

I uh created my column that's it simple let's go to the next question hard

let's go to the next question hard question

question okay okay uh show the employees first

okay okay uh show the employees first name and last name okay so employees

name and last name okay so employees first name and last name so I'm

first name and last name so I'm interested in the employees table so

interested in the employees table so from employees show first name and last

from employees show first name and last name select first name comma last

name select first name comma last name a number of or ERS column with a

name a number of or ERS column with a count of the orders taken and a column

count of the orders taken and a column called Shi that displays on time if the

called Shi that displays on time if the order Shi date is less than equal to the

order Shi date is less than equal to the required date late if the order Okay

required date late if the order Okay order by complete okay okay so there is

order by complete okay okay so there is ordering also so order by the employee

ordering also so order by the employee last name then by the first name and

last name then by the first name and then

then descending okay a lot of ordering so

descending okay a lot of ordering so order by uh last name last

order by uh last name last name then by first name first name and

name then by first name first name and then descending by the number of orders

then descending by the number of orders so I need to First create the number of

so I need to First create the number of orders so I need to join it right I need

orders so I need to join it right I need to join the table with the orders table

to join the table with the orders table right um yes I need to do

orders on orders uh on employee Dot

orders uh on employee Dot no uh yes

no uh yes employee employee where is

employee employee where is employees do employee ID is equal to

employees do employee ID is equal to orders

orders dot orders dot employee

dot orders dot employee ID right and order by first name and

ID right and order by first name and last name and then I need

last name and then I need to number of orders count the number of

to number of orders count the number of orders ERS right I need to count this uh

orders ERS right I need to count this uh and then I also need to create the shift

and then I also need to create the shift column but let's see this first so if I

column but let's see this first so if I do

do this I can

this I can see

see Steven right I can see Steven last name

Steven right I can see Steven last name first name and last name I can see but I

first name and last name I can see but I cannot see the number of orders so I

cannot see the number of orders so I need to find out the count of the number

need to find out the count of the number of orders right so if I just say count

of orders right so if I just say count star

star as uh number of

as uh number of orders okay no this is just going to

orders okay no this is just going to count the complete this thing so I need

count the complete this thing so I need to group them I need to group them on

to group them I need to group them on what group them on order ID no no group

what group them on order ID no no group them will not be on order ID group them

them will not be on order ID group them group will be

group will be on show the employees first name last

on show the employees first name last name number of orders column with the

name number of orders column with the account of the order taken and a column

account of the order taken and a column called shipped okay so I have picked uh

called shipped okay so I have picked uh first name last name count will be and I

first name last name count will be and I need to give give these tables as name

need to give give these tables as name so employees e uh and orders let's say o

so employees e uh and orders let's say o and I need to count orders. order

and I need to count orders. order ID as a number of orders right and

ID as a number of orders right and before ordering

before ordering them by last name and first

them by last name and first name uh I've joined these tables I need

name uh I've joined these tables I need to group them I've not yet created the

to group them I've not yet created the Shi column yet I'll do that group by

Shi column yet I'll do that group by um first name and last name only so

um first name and last name only so first name comma last name and also I

first name comma last name and also I need to group them on the ship shipped

need to group them on the ship shipped column right so I'll create the last

column right so I'll create the last column that is shipped and that will be

column that is shipped and that will be on the that will be created using cases

on the that will be created using cases so I'll say case and let's put it

so I'll say case and let's put it inside

inside these so case uh case will

these so case uh case will be orders taken and shi shi column that

be orders taken and shi shi column that displays on time if the order ship date

displays on time if the order ship date is less than or equal to the required

is less than or equal to the required date so if my order dot uh Shi date is

date so if my order dot uh Shi date is less than equal

less than equal to required

to required date then it is on

date then it is on time

time right uh next case will

right uh next case will be case o do if the order ship date is

be case o do if the order ship date is less than equal to required date uh late

less than equal to required date uh late if the order ship date if the order Shi

if the order ship date if the order Shi late

late okay that means uh o. Shi date is

okay that means uh o. Shi date is greater um and Callum ship that displays

greater um and Callum ship that displays on time if the order ship date is less

on time if the order ship date is less than or equal to the required dat late

than or equal to the required dat late if the order shipped

if the order shipped date not shipped if shift date is null

date not shipped if shift date is null it question is quite uh confusing late

it question is quite uh confusing late if the order shipped late uh I think it

if the order shipped late uh I think it is greater than required date itself so

is greater than required date itself so let's do that required date then it is

let's do that required date then it is late right and um else it will be

late right and um else it will be null not shipped actually not shipped if

null not shipped actually not shipped if it is

it is null right so case only let's write case

null right so case only let's write case itself or ship date is

itself or ship date is null then it is not

null then it is not shipped

shipped not shipped okay end perfect so this is

not shipped okay end perfect so this is the cases thing and I'll save it in

the cases thing and I'll save it in Shi uh column so order by Group by on

Shi uh column so order by Group by on Shi also actually

Shi also actually shipped right order by last name first

shipped right order by last name first name uh and then number of orders so I

name uh and then number of orders so I have this number of orders over here so

have this number of orders over here so I'll paste it over here number of

orders in descending order okay there is an issue uh I think

order okay there is an issue uh I think something I'm doing wrong over here uh

something I'm doing wrong over here uh I'm not sure uh let's see the solution

I'm not sure uh let's see the solution and let's get it done so select e do

and let's get it done so select e do first name okay so this one I missed I I

first name okay so this one I missed I I agree to that e do first name e do last

agree to that e do first name e do last name or order

name or order this is correct this is correct this

this is correct this is correct this will be e do first name this will be e

will be e do first name this will be e do last

do last name uh this will be Shi this will be e

name uh this will be Shi this will be e do last name e do first name and this is

do last name e do first name and this is correct

correct okay count of order ID as number of

okay count of order ID as number of order this we did right case okay okay

order this we did right case okay okay okay so this is the syntax error I don't

okay so this is the syntax error I don't know like how I missed this but case you

know like how I missed this but case you don't write multiple times you write

don't write multiple times you write case once case I don't know like why I

case once case I don't know like why I did this in the all of the previous uh

did this in the all of the previous uh 55 60 Questions I did it right but now I

55 60 Questions I did it right but now I messed it up so case is this then this

messed it up so case is this then this uh and when so this is all replaced by

uh and when so this is all replaced by when

when when uh okay um case when order ship

when uh okay um case when order ship date is less than equal to order

date is less than equal to order required date then on time and yes when

required date then on time and yes when or order ship date is greater than the

or order ship date is greater than the required o. required so this is one more

required o. required so this is one more mistake than late and o. ship date is is

mistake than late and o. ship date is is null then not shipped and end as shipped

null then not shipped and end as shipped I think then okay still not working okay

I think then okay still not working okay um orders o this can become e this can

um orders o this can become e this can become

become o okay but at least I'm getting some

o okay but at least I'm getting some output uh and yes I have to just select

output uh and yes I have to just select the question so I'll copy my query and

the question so I'll copy my query and I'll pick my question I'll paste it I'll

I'll pick my question I'll paste it I'll hit enter and it is now working so a lot

hit enter and it is now working so a lot of syntax issues I did in this query I

of syntax issues I did in this query I agree right but I hope you are not

agree right but I hope you are not making the same mistake okay let's do

making the same mistake okay let's do the last question of this

the last question of this video okay uh the question is show I

video okay uh the question is show I hope this question is easy uh show how

hope this question is easy uh show how much money the company lost due to

much money the company lost due to giving discounts each year order the

giving discounts each year order the years from most recent to last recent

years from most recent to last recent round to two decimal

places okay and not that easy so we have to uh in the hint you can see that we

to uh in the hint you can see that we have to work with order

have to work with order details okay because in the order

details okay because in the order details I can get the discount value

details I can get the discount value right uh then I have to work with the

right uh then I have to work with the orders because a company will have

orders because a company will have orders so from this company ID I can

orders so from this company ID I can find the information about that

find the information about that company right so I can see that this one

company right so I can see that this one how much a company law reading supplier

how much a company law reading supplier actually

actually supplier where is the

supplier where is the supplier okay employee ID is there order

supplier okay employee ID is there order dat is there required shipped via fight

dat is there required shipped via fight ship ship ship okay there is no

ship ship ship okay there is no information about the company in the

information about the company in the order details no information of the

order details no information of the company show how much money but in the

company show how much money but in the products I do have information about the

products I do have information about the supplier okay um okay so that means on a

supplier okay um okay so that means on a product so we have to work with a

product so we have to work with a product ID because on a product you can

product ID because on a product you can see a you can see the okay in the orders

see a you can see the okay in the orders you get the order ID

you get the order ID from this order ID you can Fest the

from this order ID you can Fest the order

order details from this product ID you can get

details from this product ID you can get the product details and from that you

the product details and from that you can get the supplier ID anyways we don't

can get the supplier ID anyways we don't have to show any information on the

have to show any information on the supplier uh and our final output we have

supplier uh and our final output we have to show the order year and we have to

to show the order year and we have to show the discount amount okay I got it

show the discount amount okay I got it this actually information is about of

this actually information is about of one company itself I was like in a

one company itself I was like in a misconception that this is a details

misconception that this is a details about an e-commerce platform where

about an e-commerce platform where multiple companies are there so that is

multiple companies are there so that is my mistake so I got it so this is a

my mistake so I got it so this is a complete one company itself okay great

complete one company itself okay great so uh show much how much show how much

so uh show much how much show how much money the company lost due to giving

money the company lost due to giving discounts each year order the years from

discounts each year order the years from most recent okay so from uh I need to

most recent okay so from uh I need to definitely uh join the tables I will be

definitely uh join the tables I will be joining orders order

joining orders order details and products do we need

details and products do we need product quantity price because we want

product quantity price because we want to get the actual selling price maybe

to get the actual selling price maybe that's

that's why right from the orders I can get um

why right from the orders I can get um the product and from the product I can

the product and from the product I can get the unit price right so I need to

get the unit price right so I need to first of all join them so I'll join um

first of all join them so I'll join um orders

orders o join

o join OD on orders that is O do order ID is

OD on orders that is O do order ID is equal to od. order

equal to od. order ID so this is joined and now the joining

ID so this is joined and now the joining should also happen on the products so

should also happen on the products so join products

join products on um o. product ID is equal to this can

on um o. product ID is equal to this can become

become p uh P

p uh P do product

do product ID okay and if I just want to

ID okay and if I just want to select um let's say select star

select um let's say select star o. product

o. product ID

ID order why the order does not have any

order why the order does not have any information on the product I mean which

information on the product I mean which product this is an order of is it there

product this is an order of is it there in the order details yes it's in the

in the order details yes it's in the order

order details so product P od. product ID is

details so product P od. product ID is equal to p. product

equal to p. product ID yes now I can see the data now from

ID yes now I can see the data now from this data I need to fetch

this data I need to fetch I need to fetch I need to group them on

I need to fetch I need to group them on year right I need to group them on

year right I need to group them on year year of the order

date so 3 2018 uh 2017

2018 uh 2017 2016 so from this uh I can actually

2016 so from this uh I can actually say uh okay let's keep start itself for

say uh okay let's keep start itself for now uh but for this uh from this I have

now uh but for this uh from this I have got that there are three years 2016 2017

got that there are three years 2016 2017 and 2018 now I need to calculate now the

and 2018 now I need to calculate now the turn has come for the mathematical

turn has come for the mathematical aggregate functions I need to calculate

aggregate functions I need to calculate the discount amount so discount if you

the discount amount so discount if you see is over here right so this is

see is over here right so this is correct and now if I want to show this

correct and now if I want to show this order here directly uh and I'm only

order here directly uh and I'm only interested in two columns so first I'll

interested in two columns so first I'll say select

say select year uh from o. order date o. order date

year uh from o. order date o. order date as order

as order year right and now the second uh column

year right and now the second uh column how will you find out the discount

how will you find out the discount amount so discount amount should be

amount so discount amount should be um the unit price into quantity into

um the unit price into quantity into this discount uh decimal right so uh it

this discount uh decimal right so uh it should be P do um

should be P do um uh it should be P unit

into order details that is OD do quantity into od.

quantity into od. discount right so if I find

discount right so if I find out the and we also need to round it to

out the and we also need to round it to two decimal places so this will

two decimal places so this will be round

okay so this will be like this two like this and then uh you need to find out

this and then uh you need to find out the sum of for each year right so it

the sum of for each year right so it will be

sum and as discount discount and then you can also

discount discount and then you can also see that we have

see that we have to uh how much money lost in yeah I

to uh how much money lost in yeah I think this is

think this is correct hm I think round round should be

correct hm I think round round should be after the

after the sum let's do

sum let's do that um like this this will be

that um like this this will be gone this is one

gone this is one thing yes so sum of this thing and then

thing yes so sum of this thing and then you found find then you will find the

you found find then you will find the round comma 2 like this so 227 2016 okay

round comma 2 like this so 227 2016 okay I need to like round order them also I

I need to like round order them also I guess so order by

guess so order by order year

order year descending yes we got it guys so awesome

descending yes we got it guys so awesome so we have covered all the questions of

so we have covered all the questions of this table uh plus we also covered the

this table uh plus we also covered the uh questions of our Hospital table also

uh questions of our Hospital table also you can see every question is done so

you can see every question is done so you actually we actually wrapped up this

you actually we actually wrapped up this whole platform there were only two

whole platform there were only two databases and we have done if I'm

databases and we have done if I'm missing anything let me know but we

missing anything let me know but we actually covered almost 70 questions so

actually covered almost 70 questions so I all the all those people who actually

I all the all those people who actually watched this video till the end thank

watched this video till the end thank you and I hope you really enjoyed this

you and I hope you really enjoyed this complete session not session but the

complete session not session but the complete video where I covered 70 escale

complete video where I covered 70 escale questions and believe me there are very

questions and believe me there are very few concept that are left and if you

few concept that are left and if you keep on practicing like this every day

keep on practicing like this every day you will become a master of SQL right

you will become a master of SQL right and don't think that SQL like nowadays

and don't think that SQL like nowadays like chat gbt and this all these models

like chat gbt and this all these models are going to write SQL yes they will

are going to write SQL yes they will write it and it is no problem in that

write it and it is no problem in that there is no problem in that but this skl

there is no problem in that but this skl will actually help you um keep your

will actually help you um keep your brain warm right if you keep if you put

brain warm right if you keep if you put everything on CH GPT that is still fine

everything on CH GPT that is still fine but you will lose the power of your

but you will lose the power of your brain you will stop using your brain and

brain you will stop using your brain and such uh practices such mental maths

such uh practices such mental maths actually help you keep your brain active

actually help you keep your brain active so that's why I did this uh I also like

so that's why I did this uh I also like depend on chat GPT to write the escal

depend on chat GPT to write the escal queries for me whenever I'm building any

queries for me whenever I'm building any project but sometimes doing this is

project but sometimes doing this is actually necessary for your brain to

actually necessary for your brain to stay

stay active Okay so yeah uh you can check out

active Okay so yeah uh you can check out my channel uh I'm I have like some

my channel uh I'm I have like some amazing playlist uh I'm teaching AI

amazing playlist uh I'm teaching AI engineering and I've already taught

engineering and I've already taught flutter around 150 videos are there uh

flutter around 150 videos are there uh some backend videos are there software

some backend videos are there software development patterns are there uh some K

development patterns are there uh some K projects are there so approximately like

projects are there so approximately like 20 videos are there and I keep on

20 videos are there and I keep on shooting such uh project videos I keep

shooting such uh project videos I keep on making projects some tech interview

on making projects some tech interview preparation uh videos are there uh

preparation uh videos are there uh interview uh experiences and some

interview uh experiences and some podcasts are there uh and yes uh the

podcasts are there uh and yes uh the complete data analysis uh playlist is

complete data analysis uh playlist is there where I covered numai pandas

there where I covered numai pandas matplotlib and

matplotlib and cbon I hope uh you enjoyed this video uh

cbon I hope uh you enjoyed this video uh if you have any concern you can book a

if you have any concern you can book a session with me one-on-one using topmate

session with me one-on-one using topmate and you can check out my LinkedIn uh you

and you can check out my LinkedIn uh you can DM me there also so till the next

can DM me there also so till the next video keep coding keep inting and thanks

video keep coding keep inting and thanks a lot and don't forget to be a part of

a lot and don't forget to be a part of the Discord Channel okay Discord server

the Discord Channel okay Discord server okay so yeah keep coding keep innovating

okay so yeah keep coding keep innovating and thanks a lot

Click on any text or timestamp to jump to that moment in the video

Most transcripts ready in under 5 seconds

One-Click Copy125+ LanguagesSearch ContentJump to Timestamps

Paste YouTube URL

Enter any YouTube video link to get the full transcript

Most transcripts ready in under 5 seconds

Get Our Chrome Extension

Get transcripts instantly without leaving YouTube. Install our Chrome extension for one-click access to any video's transcript directly on the watch page.

Add to Chrome — Free

Works with YouTube, Coursera, Udemy and more educational platforms

Get Instant Transcripts: Just Edit the Domain in Your Address Bar!

YouTube

←

→

↻

https://www.youtube.com/watch?v=UF8uR6Z6KLc

YoutubeToText

←

→

↻

https://youtubetotext.net/watch?v=UF8uR6Z6KLc

YouTube TranscriptPreparing your results…

YouTube Transcript:Solve 70 SQL Questions in 3 hrs | Ultimate SQL Practice | Master SQL