1 : 1 SQL Intermediate Interview Session | SQL Interview Q&A | SQL Training | Ivy Pro School
Вставка
- Опубліковано 17 жов 2024
- Most Common SQL Interview Questions are as follows:
1. What is Database?
A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.
2. What is DBMS?
DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation and management of the database. It ensures that our data is consistent, organized and is easily accessible by serving as an interface between the database and its end-users or application software.
3. What is RDBMS? How is it different from DBMS?
RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.
4. What is SQL?
SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.
5. What is the difference between SQL and MySQL?
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle, or IBM DB2, that is used to manage SQL databases.
6. What are Tables and Fields?
A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.
7. What are Constraints in SQL?
Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of a table or after creating using the ALTER TABLE command. The constraints are:
NOT NULL - Restricts NULL value from being inserted into a column.
CHECK - Verifies that all values in a field satisfy a condition.
DEFAULT - Automatically assigns a default value if no value has been specified for the field.
UNIQUE - Ensures unique values to be inserted into the field.
INDEX - Indexes a field providing faster retrieval of records.
PRIMARY KEY - Uniquely identifies each record in a table.
FOREIGN KEY - Ensures referential integrity for a record in another table.
8. What is a Primary Key?
The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.
A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).
9. What is a UNIQUE constraint?
A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike the primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.
10. What is a Join? List its different types.
The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.
There are four different types of JOINs in SQL:
(INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.
LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.
11. What is a Subquery? What are its types?
A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively.
12. What are UNION, MINUS, and INTERSECT commands?
The UNION operator combines and returns the result-set retrieved by two or more SELECT statements.
The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.
Want to learn SQL visit us here to know more: bit.ly/35TkOAL
You can get in touch with us at info@ivyproschool.com or call us at 7676882222
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 😀😀
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
Very informative.. Very patiently he answered his questions and this is called confidence.. Didn't feel like interview session
Feedback at the end by interviewer was Damn Impressive. Really impressed by Sir.
Answer for 24:30 question is to use cross join. Interviewer was expecting you to say that
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.
q2) Duplicate emails
select email,count(Email)as cnt
from person
group by email
having count(email)>1
Oh man this interviewer is a gem. 🙏🙏🙏
He actually is Saurabh 🤩
@Adeel Ahmed it's lol man.
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
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.
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
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????
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 👍
Feedback is excellent 👌
Thanks
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.
Prateek… thank you
Great and honest feedback from the Interviewer!
Thanks!!!
Great informative video sir!!
Come to know some now contact also..
Much needed video at this time.. thank you for uploading ❤️
Glad it was helpful
Learnt a lot. Liked. Subscribed 🙂
Welcome aboard!
Not able to find the licence folder in c drive...where should I paste the licence key
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
More than answers we learn more than questions and last 10 min
Hello, do you have any course on SQL?
Is the solution for problem 4 correct? (51:45)
Indeed great 👍
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.
Thank you so much for this video. It's really really helpful.
Glad it was helpful!
What about when there is no second highest salary query should result null
Hello sir. I have registered for Accenture Innovation Chllenge. Can you please suggest me some tips to win this challenge?
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!
for nth highest salary use analytic function itself
Thank you
You're welcome
Wow its really Helpfull
Glad to hear that
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.
Parent and child relationship
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
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?
Could you please post sap hana modeller perspective live interview
How can we restrict to view limited no of coloums in sql
use WHERE Clause
we want more videos like that
For sure! Stay tuned.
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.
Lot of window function questions.!
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 ??
3:07
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
How much salary will he get?
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
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