SQL Questions for Data and Business Analyst
Topic – SQL Questions for Business Analyst
Number of questions – 10
Topics – Basic and Tricky
Probability of getting asked in an interview – 8/10
Let’s start with the SQL Questions for Business Analyst
Though we have mentioned that the probability of these questions coming in your interview is 8/10 but we are being modest at that part 😛
There is a literally high probability of facing these concepts in your interviews. SQL is a funny language, there is a great amount of restriction as far as concepts and function are concerned. You only have a handful of functions like LEAD(), LAG(), RANK(),ROW_NUMBER,COUNT(), etc. But that is also the beauty of this language. You can convert a complex-looking 200 lines of R/Python code into 50 lines of SQL code with better performance(okay !! far better )
Coming to the concepts – We will be dealing with 10 questions in each blog. If the concept is hard or complex to understand, then we will be having some video also. Every link will be provided in the blogs
Let’s start SQL interview question (1-10)
1) Suppose there are two tables, X and Y, X has just one column A and Y has B.
These are the two tables
| X(A) | Y(B) |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | |
| 5 |
Questions – How many rows will be populated if you do
-X left join Y
-X inner join Y
-X cross join Y
-X right join Y
The answer is 3,3,3 and 5
Remember – Join will only take place when the values match
Link to video solution – https://youtu.be/aqpkrJvvqxs
Link to the source code
2) There are two columns in a table – id and price
| Table – ABC | |
| Id | Price |
| 1 | 10000 |
| 2 | 5000 |
| 3 | 1000 |
| 4 | 4000 |
Output
| Output | |
| Id | Difference |
| 1 | 5000 |
| 2 | 0 |
| 3 | -4000 |
| 4 | -1000 |
Link to video explanation to this question – https://youtu.be/wor2osrIjzc
Link to Github repository for the code to create table and query
Approach and solution
First take the average and name it as A, then cross join this value with the complete table ABC and take the difference
select y.id,(y.salary-a) as diff from
(select avg(salary) as a from abc) x
cross join abc y
;
3. What is RDBMS? How is it different from DBMS?
A relational database management system (RDBMS) is a set of applications and features that allow IT professionals and others to develop, edit, administer, and interact with relational databases. Most commercial relational database management systems use Structured Query Language (SQL) to access the database, which is stored in the form of tables.
The RDBMS is the most widely used database system in businesses all over the world. It offers a stable means of storing and retrieving massive amounts of data.
Databases, in general, hold collections of data that may be accessed and used in other applications. The development, administration, and use of database platforms are all supported by a database management system.
A relational database management system (RDBMS) is a type of database management system (DBMS) that stores data in a row-based table structure that links related data components. An RDBMS contains functions that ensure the data’s security, accuracy, integrity, and consistency. This is not the same as the file storage utilized by a database management system.
The following are some further distinctions between database management systems and relational database management systems:
4. What is a Self-Join?
A self-join is a type of join that can be used to connect two tables. As a result, it is a unary relationship. Each row of the table is attached to itself and all other rows of the same table in a self-join. As a result, a self-join is mostly used to combine and compare rows from the same database table.
5. What are the differences between OLTP and OLAP?
OLTP stands for online transaction processing, whereas OLAP stands for online analytical processing. OLTP is an online database modification system, whereas OLAP is an online database query response system.
6. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.
The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.
7. NoSQL vs SQL
In summary, the following are the five major distinctions between SQL and NoSQL:
Relational databases are SQL, while non-relational databases are NoSQL.
SQL databases have a specified schema and employ structured query language. For unstructured data, NoSQL databases use dynamic schemas.
SQL databases scale vertically, but NoSQL databases scale horizontally.
NoSQL databases are document, key-value, graph, or wide-column stores, whereas SQL databases are table-based.
SQL databases excel in multi-row transactions, while NoSQL excels at unstructured data such as documents and JSON.
8. How to remove duplicate rows in SQL?
If the SQL table has duplicate rows, the duplicate rows must be removed.
Let’s assume the following table as our dataset:
| ID | Name | Age |
| 1 | A | 21 |
| 2 | B | 23 |
| 2 | B | 23 |
| 4 | D | 22 |
| 5 | E | 25 |
| 6 | G | 26 |
| 5 | E | 25 |
The following SQL query removes the duplicate ids from the table:
DELETE FROM table WHERE ID IN (
SELECT
ID, COUNT(ID)
FROM table
GROUP BY ID
HAVING
COUNT (ID) > 1);
9. What are Constraints?
Constraints in SQL are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are:
NOT NULL
CHECK
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
10. What are Entities and Relationships?
Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables store data that represents one type of entity. For example – A bank database has a customer table to store customer information. The customer table stores this information as a set of attributes (columns within the table) for each customer.
Relationships: Relation or links between entities that have something to do with each other. For example – The customer name is related to the customer account number and contact information, which might be in the same table. There can also be relationships between separate tables (for example, customer to accounts).
SQL Questions for Business Analyst
Now, if you are confused about starting SQL as a beginner and to reach to an interview ready state then do read this blog on How to prepare for SQL in 15 days
The Data Monk services
We are well known for our interview books and have 70+ e-book across Amazon and The Data Monk e-shop page . Following are best-seller combo packs and services that we are providing as of now
- YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
Link – The Data Monk Youtube Channel - Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
Link – The Data Monk website - E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions. Do check it out
Link – The Data E-shop Page - Instagram Page – It covers only Most asked Questions and concepts (100+ posts). We have 100+ most asked interview topics explained in simple terms
Link – The Data Monk Instagram page - Mock Interviews/Career Guidance/Mentorship/Resume Making
Book a slot on Top Mate
The Data Monk e-books
We know that each domain requires a different type of preparation, so we have divided our books in the same way:
1. 2200 Interview Questions to become Full Stack Analytics Professional – 2200 Most Asked Interview Questions
2.Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions
3. 30 Days Analytics Course – Most Asked Interview Questions from 30 crucial topics
You can check out all the other e-books on our e-shop page – Do not miss it
For any information related to courses or e-books, please send an email to nitinkamal132@gmail.com
