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 Interview Books – Don’t Miss

Now we are also available on our website where you can directly download the PDF of the topic you are interested in. At Amazon, each book costs ~299, on our website we have put it at a 60-80% discount. There are ~4000 solved interview questions prepared for you.

10 e-book bundle with 1400 interview questions spread across SQL, Python, Statistics, Case Studies, and Machine Learning Algorithms – Ideal for 0-3 years experienced candidates

23 E-book with ~2000 interview questions spread across AWS, SQL, Python, 10+ ML algorithms, MS Excel, and Case Studies – Complete Package for someone between 0 to 8 years of experience (The above 10 e-book bundle has a completely different set of e-books)

12 E-books for 12 Machine Learning algorithms with 1000+ interview questions – For those candidates who want to include any Machine Learning Algorithm in their resume and to learn/revise the important concepts. These 12 e-books are a part of the 23 e-book package

Individual 50+ e-books on separate topics

Important Resources to crack interviews (Mostly Free)

There are a few things which might be very useful for your preparation

The Data Monk Youtube channel – Here you will get only those videos that are asked in interviews for Data Analysts, Data Scientists, Machine Learning Engineers, Business Intelligence Engineers, Analytics Manager, etc.
Go through the watchlist which makes you uncomfortable:-

All the list of 200 videos
Complete Python Playlist for Data Science
Company-wise Data Science Interview Questions – Must Watch
All important Machine Learning Algorithm with code in Python
Complete Python Numpy Playlist
Complete Python Pandas Playlist
SQL Complete Playlist
Case Study and Guesstimates Complete Playlist
Complete Playlist of Statistics