Day 3 | Complex JOINS and SUBQUERIES | Solving TRICKY SQL Interview Queries #sqlinterviewquestions
Video Title: Day 3 | Complex JOINS and SUBQUERIES | Solving TRICKY SQL Interview Queries #sqlinterviewquestions
Welcome back to another video on crack Concepts and this is the third video where I am solving SQL interview queries very important queries two videos are already up and total of five videos are going to be up and if at all you’re watching the video a little late and all
The five videos are already up if in case you will find those videos in the description box or in the comment section so make sure to check it out if if at all F links are not in the description box the videos are coming very soon so stay tuned for that and
Without further Ado I’m going to start with question number three but before I start with the question number three I am going to give a refresher of this entity diagram and the tables so that you know you are well versed with it and in case you already know it and you’re
Watching the videos in continuity and you already know it you can skip that part and you can go directly to the part where I’m solving the question so yeah let’s do that okay so there are total five tables account balance account type account type undor account
Name and names now as you can see there are lines between them so what this means is these two tables are connected why PK PK means primary key and these two are also connected these two are connected then these two are connected and these two but these two aren
Connected these two aren connected these two aren connected because there are no between them and if you want to see how they are connected always always remember that PK or FK will be written in Brackets so PK means primary key and FK means foreign key okay so between these two tables
What are the common table common columns that you can find so this is account underscore I and account underscore I so these two are common between the two and uh and between these two between these two what you can find account underscore I account underscore I again is common between these two and
Between these two account underscore type underscore C is common between the two between these two um first underscore name underscore I and last underscore name underscore I and name underscore I these are common so also remember that primary keys or primary keys in two tables so suppose one is a
Foreign key and the other is primary key does not necessarily mean they will have the same names they can have different names as well okay and um between these two accountor I is common okay now let’s see the tables this is the account balance table which has accountor I means account
Numbers date and the balance amount then we have account type so account I and then account typeor C so these are just letters then we have another table which is accountor typeor d so this has accountor typeor c means means these this column this and this column these
Two are common between the two so between these two tables account underscore I is common and between these two tables account underscore typeor C is common so as you can see c d HS these are common and they have corresponding descriptions so C corresponds to credit card D corresponds to check account H
Corresponds to home loan s corresponds to saving account and then we have account name account _ I again this is the primary key this is the common key between the various tables we have first name I last name I and now you will wonder why we have integers why do we
Have numbers in first name and last name we have 1 6 2 5 3 5 4 7 there should be names so we have another table at the bottom which which is names and here we have these numbers 1 2 3 4 5 6 7 and these have corresponding
Names so 1 2 3 4 5 is actually one means Ross So Ross first name is Ross then we have six six is here which is SC so 1 2 3 4 5 account number 1 2 3 4 5 the name for account number 1 2 3 4 5 is I mean
The account holder is Ross Carter Okay so 1 six cross gut this is how the tables are interconnected okay so it’s little complicated and the questions are also complicated and hence there’s a lot of learning involved in these five videos that are going to come so stay tuned for
It and in this video obviously I’m going to solve the first question but yeah the table tables are quite complicated they are interconnected and you will understand a lot all about SQL queries and how to solve them so do not miss these five videos I will insist on that okay so
Let’s begin with the first question which is write the SQL statement to show the most recent balance for each account question number three is write the SQL statement to show the full name first name then space then last name of all customers who have a credit card
Account now as we can see this question has two parts the first one is we need to find the full name first name then space then last name of all customers this is the first part this the first part is to figure out the first name and last name and the second
Part is of all the customers who have a credit card account of all the customers who have a credit card account which means we need to filter the data filter the data means maybe probably we have to use wear Clause not sure we might have
To so let’s figure out what we can do let’s figure let’s find out these columns first of all in the entity diagram so we have first name last name where is first name and last name it’s over here first name and last name and as you can already see first name and
Last name first name underscore I and last name underscore I these columns only have numbers like 1 2 3 4 5 6 obviously those are not real names so those two columns are actually connected to this table where the actual names are there so we actually need
These two tables these two tables need to be joined together in order to get the first name and last name okay so first name and last name we can find that out over here and then we have this part of all the customers who have a credit card account now who have a
Credit card account credit card account means account description which is over here typeor description so this needs to be join join with these two tables now these two there’s no no direct rout between the account typeor D and these two tables so we have to go via this
Room we need to connect them through account and risk type we need to join them via accountor Ty okay so I have given few hints over here how about you pause the video and try to solve this query give yourself good 5 to 10 minutes try to figure this query out and once
You’re done giving a shot at this query return back to this video and see the answer this way you will you will understand queries better you will understand you will know the thought process behind writing queries better so let’s do that pause the video for a
Couple of minutes and try to solve this query our question is write the sequel statement to show the full name first name then space last name of all all customers who have a credit card account okay so credit card is over here column typeor description and this is the
Account type c and that needs to be connected with this table which is accountor type and here we can get the account numbers accountor I and the accountor I can be joined over here and here we can get first name and last name and first name and last name over here has number
This needs to be connected with our next table which is names and here we have 1 2 3 4 5 6 and these are the actual names so there’s a long route that we need to take let’s do it let’s begin writing the query okay let’s start joining the tables
First I’m going to write select star from account uncore name and I’m going to give it an alias am and I will join the joining by names I’m going to join this tabl okay first I’m going to start with a very basic tables okay which is this one accountor name okay
And the table names okay which has Raw read B all of that okay so yeah I’m going to give it an alias FN on do first name underscore I is equal to F dot name I okay oops okay now okay here is our table account name and we’re selecting star from account name
And we’re joining first underscore name underscore I this column which has one 2 3 4 with this table names and we are joining it to name UND describe this column that’s exactly what I’ve written over here a and do first name uncore I is
Equal to FN do name I this is how both the tables are going to Jo but here’s the catch we have we need to join this table two times because we have two columns first name and last name and these two columns are joining to this one single column name undor I
In the table names so what I’m going to do is again I’m going to write join names Ellen means last name on a do first name _ I is equal to Len do name uncore I this is how you can you can join one single table two times okay so
Just in case you did not know if a single table can be joined multiple times this is your answer yes you can and here it is you have joined the table two times now I do not need all the columns I just need need fn. name as first
Name and Ellen do name as last name and here we have the first names and first and last names we have joined the two tables and we are taking the name the name column from them okay the first part is almost completed we have the first name and the last name we need
To I know we need to merge them and have a space between them that we’ll do in the later part but let’s get on to the next part which is we need to filter it by all customers who have a credit card account okay so we need to use fa Clause
Over here now we need to find all those customers who have a credit card account credit card can be found over here in this table which is account typeor D but this table needs to be joined with this and this table needs to be joined with this
Because there’s no other way we can join because this table does not have account numbers so we need to join this by this table and then this by this as this is our base table as accountor name is our base table this is our base table this
Needs to be joined with this table and then this table needs to be joined with this table the three tables okay where a _ i is in and then we’re going to use a subquery over here select account I from accountor type where count could type discour C is in from type D
Where type underscore description is equal to credit card we are going to select accountor I accountor I accountor I from this table account type where account type see this this column isn’t we are we are then again using another subquery okay the subquery is select account typeor C from account type _ D
This is the table name where type description is credit card okay now we need to join these three tables as we already know okay we have the account number over here we have the account number over here we need to find the respective account typeor C this column in order to
Correctly write the query for that we need to join it to this table because we know our output needs credit card description I mean the account description need to be credit card so this needs to be our base base query like the base subquery so this is our
Base subquery select account typeor C from account typeor D where type description credit c credit card so here we will get C this inner subquery this query the output of this is going to be C so just imagine C over here so our output over here will be select
Accountor I from accountor type accountor type c is is in C so wherever the account type is C all this account numbers will be the output in this case Okay so the output for this will be 1 2 3 4 5 and 3 4 5 6 7
Why because these two have the account type description sorry account typeor C account typeor c means account codes account Cod code AS C okay and how did we find the the account code is c for credit card using this subquery okay so I hope this is clear okay so just one
Last time this inner query will give us the code the account code the account code for credit card because our question has only given us this hint that all the customers should have the credit should have credit card account so the description the account description should be credit card so
Which is over here so this inner query will give us the code account code which is C which will we will use in the next query which is this in order to get the account numbers account account numbers which have account code AS C so we get all the
Account accounts over which we will use in the outer query in the we Clause because we need to filter it so this is it and now this is our output let’s see if this is correct now that we have run the query okay the output is okay Ross Ross
Okay something is wrong just a sec okay here this needs to be last name why because this we’re joining it two times one on first name and the second time is it needs to be join on last name so first name underscore I is equal to name
Underscore I last name underscore I is equal to name underscore I and now if I run it here it is this is our output let’s just check if our output is correct okay credit card what is the code for it C let’s go on to the next
Table which is accountor Type C there are two account IDs which have account code AS SE 1 2 3 4 5 and 3 4 56 7 let’s go on top again 1 2 3 4 5 and 3 4 5 6 7 what are the first names and last names
One and six and three and five now let’s see the respective names for 1 six and three and five here 1 six here three and five suszie C so our output is correct our output is correct this is the required answer that’s it that’s it for this video this query was particularly quite
Complicated and it had a lot of Concepts involved I hope this was clear and if at all you all have any questions any queries comment down and I will be happy to help you all and thank you for watching this video make sure you watch all the five
Videos in the series all the five questions all the five queries are very informative very important so do watch it and you will find the link in the description box like share and subscribe to my channel and I will see you guys next time thank you so much for watching
Video Keywords: SQL
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99
Transact sql or mysql ?
is there any way where I can get you notes from u r end?
Hello mam.If I created a table with 3 columns and insert into the table.after I have added another columns salary using alter.Is it possible to insert all salary at a time..
Can you please provide this tables for practice
thank you,,
Thankyou