To answer the question: "what are the properties of good database design?"... We would directly start from ACID property of database. Prior to this characteristic, we would define some principles of good database design such as usability, integrity, security etc. Examples are required in answering scenario-based questions. I would feel nervous though. :-) Overall good. I really appreciate Ivy on this kind of real-time engaging video update. #Happy Learning
That is surely a good tip to start with. Don't feel nervous in your Interviews, be confident, and answer each question with as much clarity you can. That's the Key! Thanks for the appreciation 😀😀
Thank you, but I like to say Excel can deal with only 1000000 row data but SQL can deal with unlimited amount of data. Cartesian might be cross product join
The interview is expecting the candidate to say it is a cross join. This retieves the table based on the Cartesian Product. Which is why it doesn't require identical columns as reference to join tables.
The interview is real but damn, the interviewer drilled the hell of him, sql technical interview can’t be anymore mean or dreaded. His onboarding salary is worth that of the CEO.
Relational database management is advanced version of database. Stored data in tubular formate. A relational database is type of database that Store and provides To access data points that are relate to one another
Eq of relational database management system is there are two tables created 1st table employees no 2nd table employees data. Two tables are both column information to relate by using relational database management system
Too much discussion over cross join ,it is simply every row when needs to map with other tabe records then we use it and filter accordingly. Like in clinical trial each candidate will have all tests for a specific region .
why its too complicated once he join the brand a brand should educate him if he already has an experience .If he get selected what package he can expect on this type of level interview????
Why that export / import wizard would take large amount of time ? Couldn't get that ? I have been performing over millions of records never face that issue strange! And if that feature is not upto mark why Microsoft introduced it in upgrading the tool.
Grouping by employee name itself means..every name will become a group and maximum salary of that group doesn't make sense..if every employee name is unique..this would essentially mean every salary
Hello sir..I have experience in support background..and now I want to switch to development including SQL..how I can achieve thia..means hiw I can prepare
Sir I am fresher i answer most i realize this man is trying to give some ans with half knowledge and miss answering trying to give not related ans he just want to continue flow
select max(salary) from employees where salary e1.salary); Explaination : for nth highest in where condition use (n-1) here 1 is written because 2nd highest is asked this would show that salary only when it is smaller than only one like the inner query would be populated count=1 as only one salary is greater than the output salary
Hi, Different projects might have different challenges. You can take a structured approach to answer this question: 1) Data Prep Phase Challenges - Preparing data / cleaning data to ensure it is ready for analysis is a big task that we do using SQL. If you've worked on any large / real life project, you would have faced several data cleaning challenges. Eg: Data format errors, data type conversion challenges (string to date, string to numeric, demerge columns of data, mis-match date / time format challenges, etc.), missing data problems, etc. 2) Data Import of Large data challenges - At times the data type of the columns you set is not as per the data you import. You get a lot of warnings / errors while importing such large data. You must have had to take steps like splitting the data and investigating why import issues / warnings are coming. 3) Large Data Normalization Challenges - If the data you worked on was very large, you must have had to normalize it. Normalization has a lot of steps involved. What challenges did you face here? 4) Exploratory Data Analysis Challenges - Outlier treatment, denormalization, etc challenges. 5) Finally, SQL Optimization / Tuning Challenges - While working with large data, it becomes even more important to ensure your SQL queries are optimized. What steps did you take to ensure your SQL queries were optimized? Any decent SQL project will have a lot of challenges. You just have to explain them structurally. All the best. Follow @IvyProSchool UA-cam channel to learn more about SQL.
ua-cam.com/video/HhOBUynWPro/v-deo.html When computing the median: with cte1 as( Select < required fields>, add the row_number() over(order by asc) as rownum ) select from cte1 where rownum = (max(rownum) / 2) Just a suggestion.
Basic logic that i l use is create a customer pool with loan and remove fraud and fd. Select m. Cid, from mastertable m Inner join loan l on L. Cid=m.cid Left join fraud f on F. Cid=m.cid Left join fd on Fd. Cid=m.cid Where fd. Cid is null and f. Cid is null
Feedback at the end by interviewer was Damn Impressive. Really impressed by Sir.
Very informative.. Very patiently he answered his questions and this is called confidence.. Didn't feel like interview session
The Interviewer has really got some great endurance 😂...Not even for a single question, the applicant answered directly.
True😃🥴
Correct
It’s a mock interview
Answer for 24:30 question is to use cross join. Interviewer was expecting you to say that
To answer the question: "what are the properties of good database design?"... We would directly start from ACID property of database. Prior to this characteristic, we would define some principles of good database design such as usability, integrity, security etc. Examples are required in answering scenario-based questions. I would feel nervous though. :-)
Overall good. I really appreciate Ivy on this kind of real-time engaging video update. #Happy Learning
That is surely a good tip to start with.
Don't feel nervous in your Interviews, be confident, and answer each question with as much clarity you can. That's the Key!
Thanks for the appreciation 😀😀
Salute to interviewer... And his expertise in all the sections
Thanks! We are glad you liked the video.
Thank you, but I like to say Excel can deal with only 1000000 row data but SQL can deal with unlimited amount of data. Cartesian might be cross product join
Select id
From(Select id, rank() over(order by salary desc) as rownum
From salary)
Where rownum=2
Use denserank bro, rank will not work if employees having similar slary
This won’t work.
Why dense_rank will not work here in fact it will be faster.
Oh man this interviewer is a gem. 🙏🙏🙏
He actually is Saurabh 🤩
@Adeel Ahmed it's lol man.
The interview is expecting the candidate to say it is a cross join. This retieves the table based on the Cartesian Product. Which is why it doesn't require identical columns as reference to join tables.
The interview is real but damn, the interviewer drilled the hell of him, sql technical interview can’t be anymore mean or dreaded. His onboarding salary is worth that of the CEO.
Learnt a lot. Liked. Subscribed 🙂
Welcome aboard!
Feedback is excellent 👌
Thanks
Relational database management is advanced version of database. Stored data in tubular formate. A relational database is type of database that Store and provides To access data points that are relate to one another
Eq of relational database management system is there are two tables created 1st table employees no 2nd table employees data. Two tables are both column information to relate by using relational database management system
Too much discussion over cross join ,it is simply every row when needs to map with other tabe records then we use it and filter accordingly.
Like in clinical trial each candidate will have all tests for a specific region .
Great Insight Vishwas 😊
Good explanation Bro 👍
Great informative video sir!!
Come to know some now contact also..
Great and honest feedback from the Interviewer!
Thanks!!!
Prateek… thank you
Much needed video at this time.. thank you for uploading ❤️
Glad it was helpful
q2) Duplicate emails
select email,count(Email)as cnt
from person
group by email
having count(email)>1
why its too complicated once he join the brand a brand should educate him if he already has an experience .If he get selected what package he can expect on this type of level interview????
Indeed great 👍
Is the solution for problem 4 correct? (51:45)
Thank you so much for this video. It's really really helpful.
Glad it was helpful!
Thank you
You're welcome
like that we want more videos of how interviewer asked question of sql for 2..3 yr exoerience....
Great Idea!! We will surely work on it. Stay tuned!
Wow its really Helpfull
Glad to hear that
Why that export / import wizard would take large amount of time ? Couldn't get that ? I have been performing over millions of records never face that issue strange!
And if that feature is not upto mark why Microsoft introduced it in upgrading the tool.
Hello, do you have any course on SQL?
Not able to find the licence folder in c drive...where should I paste the licence key
for nth highest salary use analytic function itself
Answer to 4th problem..i.e maximum salary of each department seems incorrect..can you explain how does it work
Grouping by employee name itself means..every name will become a group and maximum salary of that group doesn't make sense..if every employee name is unique..this would essentially mean every salary
It has to be like...
Group By department_ID
Could you please post sap hana modeller perspective live interview
What about when there is no second highest salary query should result null
More than answers we learn more than questions and last 10 min
Hello sir..I have experience in support background..and now I want to switch to development including SQL..how I can achieve thia..means hiw I can prepare
You can call us at 7676882222 to get started with a course.
3:07
Hello sir. I have registered for Accenture Innovation Chllenge. Can you please suggest me some tips to win this challenge?
How can we restrict to view limited no of coloums in sql
use WHERE Clause
diff between inner join and equijoin...inner and equijoin name will be same...why u r asking differences?
you dont use ON clause on equi join, the output will be same though as both work on same principal.
Sir I am fresher i answer most i realize this man is trying to give some ans with half knowledge and miss answering trying to give not related ans he just want to continue flow
How can we found 2nd highest or 2nd lowest value in a database
select max(salary) from employees where salary e1.salary);
Explaination :
for nth highest in where condition use (n-1)
here 1 is written because 2nd highest is asked
this would show that salary only when it is smaller than only one
like
the inner query would be populated count=1 as only one salary is greater than the output salary
Use the rank function then filter it out
@@udayjaiswal7918 Great Teamwork!
@@satyajitpramanik6728 Great team work!
@@satyajitpramanik6728 dense rank right ??
we want more videos like that
For sure! Stay tuned.
what is the significance of 1=?...can someone plz explain
1 is for checking that "in whole table only one salary is greater than the salary selected".
Sir after graduation in B.Tech I have 5 years gap can I eligible for IT job now?
Yes your a eligible
Yes, you are eligible.
@@IvyProSchool what are all i need to do for joining IT after 4 long years gap?
Lot of window function questions.!
Parent and child relationship
whether he is a fresher or experienced candidate
He is an experienced candidate!
Seems like Covid 2021 batch pass-out..
But interviewer have really lots of patience..to hear his nonsense..Salute to him👍👍
This is not a nonsense. He actually answered every question with providing some context or example
How much salary will he get?
How to answer what challenges do you faced in sql project? Can anyone explain with example please...
@IvyProSchool
Hi,
Different projects might have different challenges.
You can take a structured approach to answer this question:
1) Data Prep Phase Challenges - Preparing data / cleaning data to ensure it is ready for analysis is a big task that we do using SQL. If you've worked on any large / real life project, you would have faced several data cleaning challenges. Eg: Data format errors, data type conversion challenges (string to date, string to numeric, demerge columns of data, mis-match date / time format challenges, etc.), missing data problems, etc.
2) Data Import of Large data challenges - At times the data type of the columns you set is not as per the data you import. You get a lot of warnings / errors while importing such large data. You must have had to take steps like splitting the data and investigating why import issues / warnings are coming.
3) Large Data Normalization Challenges - If the data you worked on was very large, you must have had to normalize it. Normalization has a lot of steps involved. What challenges did you face here?
4) Exploratory Data Analysis Challenges - Outlier treatment, denormalization, etc challenges.
5) Finally, SQL Optimization / Tuning Challenges - While working with large data, it becomes even more important to ensure your SQL queries are optimized. What steps did you take to ensure your SQL queries were optimized? Any decent SQL project will have a lot of challenges. You just have to explain them structurally.
All the best.
Follow @IvyProSchool UA-cam channel to learn more about SQL.
500 likes from for uploading
ua-cam.com/video/HhOBUynWPro/v-deo.html
When computing the median:
with cte1 as(
Select < required fields>,
add the row_number() over(order by asc) as rownum
)
select
from cte1
where rownum = (max(rownum) / 2)
Just a suggestion.
Thanks for the suggestion
Basic logic that i l use is create a customer pool with loan and remove fraud and fd.
Select m. Cid,
from mastertable m
Inner join loan l on
L. Cid=m.cid
Left join fraud f on
F. Cid=m.cid
Left join fd on
Fd. Cid=m.cid
Where fd. Cid is null and f. Cid is null
💯% this is fake guy...
Hi Prathamesh,
It's really sad that you have got that notion. But tbh he is one of our student.
Regards,
Ivy Pro School Team