10 Most asked SQL Interview Questions – 1/5

10 Most asked SQL Interview Questions
What is the most important ingredient in a good Analyst?
The ability to crunch numbers and by far SQL has been the most important weapon of any Analytics Professional. it’s easy to use and understand. But, you can easily be surprised by the difficulty level and variety of questions asked in SQL interviews. I highly recommend you to go through the Daily Quiz Questions as well
10 Most asked SQL Interview Questions

10 Most asked SQL Interview Questions

You can answer the question or can upvote the already-answered questions

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. On 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 AlgorithmsIdeal 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 StudiesComplete 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 questionsFor 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 that might be very useful for your preparation

The Data Monk Youtube channel – Here you will get only those videos that are asked in interviews with Data Analysts, Data Scientists, Machine Learning Engineers, Business Intelligence Engineers, Analytics managers, 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

Keep Learning !!

Thanks,

SQL and Statistics Interview Questions | Day 7

SQL and Statistics Interview Questions – Day 7 has solved questions from basic statistics, SQL, and a case study. If there is some mistake in the answers, then do comment below. Try to add more points in the case study section.
SQL and Statistics Interview Questions

Welcome to the 6th Day of Machine Learning Interview Questions.
You can go through the question and answer for
Day 1
Day 2
Day 3
Day 4
Day 5
Day 6

Machine Learning Interview Questions

In this series, all you need to do is to go through the questions and try to attempt it with whatever knowledge you have.

Before or after you attempt the questions, do go through the complete road map about what you need to study to crack an analytics role. We have covered from 3rd year college student to 8 years of analytics or non-analytic experience. Make sure you understand where to emphasize more(after all by the end of the day you want to crack an interview)

Complete Roadmap for cracking an Analytics Role in the next 30 days

Now, coming back to our questions. Remember, these are the most asked questions in any interview, if you are shy to answer the questions in the comment section, then do make sure to Google the answer and jot it down in your notebook.

SQL and Statistics Interview Questions

1. What percentage of value lies between the Mean and one Standard deviation(both positive and negative)
~68%

2. Give the relation of Mean, median, and mode in a positively skewed distribution
Mean > Median > Mode

3. Negatively skewed distribution?
Mode > Median > Mean

4. What is the sum of squared deviation?
The Sum of Squared deviation is a measure of the total variability of a set of scores around a specific number

5. Why do we need to square the terms?
We need to square the terms so that the values above the mean do not cancel those below the mean.

6. How to find the Third highest salary in the Employee table using self-join?
Select * from Employee a Where 3 = (Select Count (distinct Salary) from Employee where a.salary<=b.salary

7. What is the difference between the primary key and the candidate key?
To identify each row of a table, a primary key is used. For a table, there exists only one primary key.
A candidate key is a column or a set of columns that can be used to uniquely identify any record in the database without having to reference any other data.

8. What are the measures of spread?
The measures of spread are
a. Variance
b. Standard Deviation
c. Covariance
d. Sample Correlation

9. What is the use of NVL function in Oracle?
NVL function is the most important function to replace a null value with another value.
Example:
select NVL(null,’ Amit’) from dual;
which will give you output as Amit.

10. Case Study
If you have to recommend a product to a customer who has already filled his cart, then what data will you look for? Basically, how will you recommend a product to an e-commerce customer?
There could be multiple things that we can look for
a. If the customer has brought the product A, then we should look in the data to find out the product that compliments that product. We can do this by looking into the purchase history of other customers who have brought the product A
b. We can get the cart information of the customer and look for those items which he has removed from the cart. At the check-out, you can again ask him if he wants to buy it
c. We can also look for a better product option for the customer. Suppose the customer is willing to pay $3 for soap, then we can show then ads or recommendations of a $5 soap with a better review
d. We can also look for the previous cart history of the customer to see if he is missing something which he used to buy regularly

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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


Unnest and Sequence in Presto SQL

Unnest Sequence in Presto SQL helps you to create a sequence of numbers, dates, etc. as a table and then to use that table. Unnest and sequence are two different functions but when used in a combination it can be very useful

Where to use Unnest and Sequence in Presto?
Suppose, you have a table where you store restaurant id, revenue, and date. Now, there are chances that some restaurants are not functional on a particular date, but in your query, you need to get the total number of restaurants functional(not operational) on a particular day.

This is a standard real-life problem, basically, you need to have a calendar table in your database where you have only dates. With this date column, you will do a cross join on the restaurant table. Now, you will have all the dates for each restaurant. All, you need to do now is to take up the count distinct of the hotel_ids for a particular day.

Sample SQL code to create a sequence

select x
from unnest(sequence(date ‘2020-01-01’, date ‘2022-03-31’)) t(x)

The above command will create a column x with values starting from 2020 till 31st March 2022. Actually, the sequence will create the data in one row, and unnest will explode that row.

Sequence command
sequence (start, stop, step)
In the sequence function, you can also add steps like below

select x
from unnest(sequence(date ‘2020-01-01’, date ‘2022-03-31’, Interval ‘2’ day)) t(x)

The above code will create the column x for dates separated by 2 days.

SELECT x FROM UNNEST(sequence(200, 120, -3)) t(x)

Unnest Sequence in Presto SQL

Sample code to plug it into your report

select t.id,s.dt
from mytable t
cross join unnest(sequence(date(t.start_dt), date(t.end_dt))) as s(dt)

Now, here you took the column id and did a cross join on the sequence date column. This will result in creating two columns i.e. id and all the dates

This is a simple concept but is asked as a part of a larger problem in an interview.

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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

CROSS JOIN UNNEST in Presto

CROSS JOIN UNNEST in Presto

What is Presto and what is the meaning of un-nesting an array?
As the data grows, it becomes trickier to hold

CROSS JOIN UNNEST in Presto

CROSS JOIN UNNEST – It is simply used to flatten an array, flattening means converting an Array, Map or Row in a flat relation by converting it into multiple rows (one row for every value in array)
Sample table

NameEmp_idSubject(Array)Phone
X123[C,JAVA,SQL][123,456]
Y4231[Hive,Presto][542.654]
Z322[Ruby,Perl][12343]
Q421[Python,R][765,987]

Presto

Select Name,Emp_id,expertise
from Employee
CROSS JOIN UNNEST(Subject) as t(expertise)

If there are multiple arrays

Select Name,Emp_id,expertise,phone_num
from Employee
CROSS JOIN UNNEST(Subject,Phone) as t(expertise,phone_num)


Hive Query

Select Name,Emp_id,expertise
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise

If there are two columns to be unnested then

Select Name,Emp_id,expertise,Phone
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise
LATERAL VIEW explode(Phone) myTable2 as Phone


Knowing when and how to do unnesting is very important as there will be multiple instances where a tough problem can be solved using Cross Join Unnest.

We have also created a video on Cross Join unnest to explain it in a better way, Do check out the
Video Link Here

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

Do check out our booklist below for complete preparation in 30 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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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

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

SQL Questions for Business Analyst
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)
11
22
33
4
5
SQL Interview Questions


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
IdPrice
110000
25000
31000
44000

Output

Output
IdDifference
15000
20
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:

IDNameAge
1A21
2B23
2B23
4D22
5E25
6G26
5E25

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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




SQL most asked interview questions

SQL most asked interview questions

In this article, we will deal with ‘almost’ all types of SQL questions asked in any Analytics interview. These questions are mostly asked from candidates in the experience range of 0 to 6 years.

SQL questions are asked mostly in the 1st and 2nd rounds of your interviews and good feedback in it will definitely help you sail through the screening round.

SQL most asked interview questions
SQL most asked interview questions



How to use this tutorial?

There will be 4 sections to this article:-

1. General introduction on the types of questions asked

2. A few solved examples which you should first try and then gauge through the answer

3. Link to a few youtube videos which are created only on the most asked concepts in SQL

4. A few unsolved questions which you should attempt after going through 1,2 and 3

5. Optional – There will be a few books listed on our website with SQL most asked interview questions. If you think you need to master the concepts even further, then please go through it

What is the expectation of the SQL interviewer?


In a SQL interview, there are three types of questions asked:-

1. Simple definition like, what is the primary key, what is 1NF, 2NF, BCNF, etc.

Thumb rule – Don’t worry a lot about these questions, if you give a reading of any website on ‘SQL most asked questions’ then you will get answers to these questions. These articles are flooded with subjective questions. Do give it a read. We, at The Data Monk concentrate only on tricky and advance interview questions and we would not like to go that road. Having said that, it’s definitely an added advantage if you answer these questions. So, just go through it. Some sample questions will be given below

Weightage in interview -> 10-15%

2. Write query type questions

You will be recited or given a group of tables and you are supposed to write a query to find a given output. A very simple example would be to

‘Write a query to extract the 2nd most ordered item from the hotel with the least revenue’

Well, these are simple questions, all you need to do is to play around ranking, group by, order by, etc.

Weightage in interview -> 40-50%

In these types of questions, you are expected to provide the correct answer either on a server or you are supposed to write the complete code on a shared document/notepad.

We will surely look into these questions in the below section

3. Solving a real project problem

Every interviewer has solved a handful of complicated problems using SQL. These projects or queries are written in a span of a month or so, the interviewer will very wisely pick up a snippet or subset of the problem and would like to have a discussion on the approach to solve the problem.

Here, you are not expected to come up with a solution, but you need to think aloud with your logic. Most probably there will be multiple levels of the problem and you need to solve it in a methodological way.

Example:-

I am working for a multiplex and I need to create a logic to find out where to allot a seat to a group of people. The solution needs to be dynamic. Suppose, there are 4 people who came to buy the ticket, then how you code will provide options to that person.

I started an application and overnight it got 100000 clicks. Now each day the data is growing and we are processing all the data at once. How will you design the database infrastructure?
(These questions are mostly asked to 3+ years, experienced candidates)

4. Creating or designing a complete dashboard pipeline

SQL is not the only thing that is practiced in the analytics domain. Analysts are required to create complete pipeline that should help create a working dashboard/ or report.

There are multiple tools to create pipeline like Airflow, Oozie, etc.
SQL is the backbone of all these pipelines, you create codes that will refresh for every day. These SQL codes contain variable like date between ‘D-1’ and ‘D-2’ where D-1 and D-2 is for yesterday and day before yesterday, this way the code runs everyday and refreshes numbers for the last two days.

Now in the interview you might be asked to create a simple pipeline where there are 3 codes A,B,C and it should run in the same sequence.

Try to practice such questions (at least two)

5. Understanding how SQL works

You should know the importance of optimization, partition, table and view creation, etc.
You will get a few questions from the interviewer to check if you have worked on Big Data because you need to know the concepts of partition and optimization.

For example, if you want to read the data for last one week then you need to add clause like
year>= 2022 and month >= 2 (If you are in March’22)
Now these columns should be included in the partition while creating the table.

You must have observed that with one question, we can check if you know the importance of scanning only relevant data and to scan this data you need to have partition in your table and that you should also know how to create a table. Boom !! The interviewer knows how much you know about SQL .

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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







SQL Multiple Choice Questions


In this section of SQL Multiple Choice Questions, we will have set of 5 questions for basic and intermediate level question revision. Do post your scores below.
SQL Multiple Choice Questions

Quiz 1 – Beginner
No. of Questions – 5
Level of Questions – Easy
Target Score – 4 to 5

[quiz-cat id=”5019″]


Quiz 2 – Beginner+
No. of Questions – 5
Level of Questions – Beginner+
Target Score – 3 to 4
Let’s start the SQL Interview Questions

Please comment your score in the comment box 🙂

[quiz-cat id=”5025″]

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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

NoSQL vs Relational Database

NoSQL vs Relational Database
If you are already working in the analytics domain then you might have heard words like PostgreSQL, MongoDB, MySQL, Oracle, etc. We also do have a fair bit of idea about the fact that we store our tables and databases in these places. But why can’t we have everything under one hood?
We should have heard people saying that ABC is a NoSQL database and we should store these data in it rather than a traditional Relational database, what does that mean?

Does NoSQL mean that you don’t have to write SQL on top of it? If yes, then what is the use?
Let’s try to understand NoSQL vs Relational Database

NoSQL stands for Not Only SQL
Not Only SQL – Every item in the database stands on its own

NoSQL vs Relational Database
NoSQL vs Relational Database

Relational databases are like predefined table structures where you can surely add as many rows as possible but adding a new column is a pain as you have to change the schema of the table, thus it is a vertically scaling database. Whereas NoSQL is built in such a way that you can add any number of rows but at the same time you can add any additional information pertaining to a row.

Example – Suppose you have an employee table and you have a NoSQL database, in this table, there will be only 2 columns i.e. a primary key and a values column where you can add as many details as possible, for one employee you can add only name and phone number whereas for another employee you can add the complete profile ranging from name to spouse’s work status, how?

In general, the value thing is stored in a JSON format which again is a key-value pair and you can add any number of attributes corresponding to an employe



Relational Database – Vertical scaling Ex. PostgreSQL, SQLite, MySql
NoSQL – Vertical and Horizontal scaling – Each item in the database only has two things – unique key and values

Different formates in which you can store data in your NoSQL database

1. Document Database – JSON
2. Key-Value store
3. Graphical database


How does this scaling makes database life easier?
As discussed, Scaling in NoSQL can work in a horizontal way

Let’s take an example – A company XYZ runs a NoSQL database which has 10000 servers, All these servers work as a partition i.e. your data which you want to fetch is not present in only one server, it can be present in any partition.

Won’t it be too cumbersome for the database to check all the partition to find a detail ?How do find where is the data stored?

NoSQL databases are key-value stores

It’s not just a key-value pair – It is a key-hash-value where each key is associated with a hash value and this hash value works as a partition identifier 😛

Suppose the hash value is in the range of 0 to 1000 and all your data is stored in one DB. Now, if you want to double your DB performance, you can add another server, and now
Server 1 – Hash value 0 to 500
Server 2 – Hash value 501 to 1000

This range of 0 to 1000 is called a keyspace. Keyspace tells you where to store new items and where to find the existing ones. 

NoSQL is schemaless – anyways you are storing everything in a JSON format and you can increase or decrease the amount of information in each field. In a relational database, you have to define the schema of the table and have to adhere to it

Example of NoSQL –
AWS – DynamoDB
Google Cloud – Big Table
Azure – CosmoDB

You can also run NoSQL DB yourself by using software like Cassandra, CouchDB, MongoDB.

We will try to create a video on the same to give a glimpse of what is JSON and NoSQL

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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

How to prepare SQL for Analytics Jobs in 15 days?

Which is the most important concept you need to prepare for Analytics Jobs?
SQL

In this post, we will try to help you by providing all the resources needed to cover SQL from very basic to advance level.

How to prepare SQL for Analytics Jobs?

Total time required to prepare – 15 days
Daily time to invest – 3 to 4 hours

prepare SQL for Analytics Jobs

SQL Topics to cover

  • All the basic syntax
  • Window functions
  • Complex queries
  • Interview questions

SQL resources

  • SQLZoo
  • Tutorials Point
  • The Data Monk
  • Youtube videos
  • Hacker Rank

Day wise schedule

Day 1 and 2 – Go to Tutorials Point and cover the complete tutorial in 4-6 hours, as quickly as you can

Day 3 and 4 – Using the same knowledge, go to SQLZoo and solve at least 30-40 questions, these questions are very basic. It will enhance your knowledge

Day 5,6 and 7 – Go through any video on Youtube for advance questions. We felt that there are not a lot of videos in sequential order, so we have created one (Check the videos which you have not prepared till now, mostly window functions) – Cover 6-8 videos per day

List of videos

  1. Fetch Duplicate Rows in SQL – https://youtu.be/MGHNrxsHKcg
  2. Like Operator in sql – https://youtu.be/IqMMbH6Jw0w
  3. Substring and Replace in SQL – https://youtu.be/Pzb9tBdXKyc
  4. Aggregate Functions in SQL – https://youtu.be/6H5neKpUz9E
  5. Cumulative Sum in SQL – https://youtu.be/COEEGPXjNbk
  6. Row number() in SQL – https://youtu.be/1actE8UL4yM
  7. Ntile() – https://youtu.be/xbjyXk7oSdY
  8. Rank vs Dense Rank – https://youtu.be/f32Yne_4G00
  9. Where vs Having – https://youtu.be/rtZpbyyQtVk
  10. Lead and Lag – https://youtu.be/U_OQD3XpagQ
  11. Joins – https://youtu.be/_V38OgS1s5M
  12. Keys – https://youtu.be/yPZG6qurO_Q
  13. 1NF and 2NF – https://youtu.be/7CyxIOj9zTs
  14. 3 NF and BCNF – https://youtu.be/VuJ7gnaas5k
  15. Most Asked Interview Questions – https://youtu.be/UGTsPRUIkYg
  16. Odd even rows – https://youtu.be/-tLTAuvHAU0
  17. Delete vs Truncate vs Drop in SQL – https://youtu.be/cSM86aZRhMo
  18. SQL Tricky Analytics Interview Questions – https://youtu.be/MRgZKeYx1NY
  19. Binary Tree in SQL – https://youtu.be/oAD-YUafmB8
  20. Joins Advance Interview Questions – https://youtu.be/pf_RdiCBQw4
  21. Facebook SQL Interview Questions – https://youtu.be/LDYdcH82IO0
  22. Tricky SQL Interview Questions  Part 2 – https://youtu.be/B8Zy9bcqai0

Day 8 and 9 – Go to Hacker Rank and solve Medium difficulty problems (15-20)

Day 10,11 and 12 – Solve as many questions as possible from our list of interview questions
List of 200+ questions

Day 13,14 and 15 – If you feel you need to practice more then get our 10 e-books bundle and solve as many questions as possible from the SQL book, also go through Python and Statistics to revise the concept.
This is completely optional and you can revise it even before your interview, but its always better to start early and beat the race

Link to the 10 e-book bundle



We assure you that if you spend 15 days as mentioned above, you will turn into a really good candidate in any SQL interview

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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

Cross Join Unnest and Lateral View Explode

Cross Join Unnest and Lateral View Explode
This is the growing age of data and it’s a challenge to store the data efficiently. What would you do if you have to store phone numbers or courses in a table against every student in your class?
There is also a possibility that one student can have 1/2/3/4 phone numbers.

Will you create 4/5 columns with the name Phone number 1, Ph_2,Ph_3 ?
Or will you just ask for the most used phone number?

Right now in all the industries, people are storing data in arrays and maps.
When you create an array type column then you can put as many values in it as you have.

Ex – Phone[12345,13443,5456]

But how to extract these values while querying the table?

We will learn this in Cross Join Unnest and Lateral View Explode

Youtube video on the UNNEST and EXPLODE using the same example – https://youtu.be/vTv8AJis1aE

NameEmp_idSubject(Array)
Rizwan123[C,JAVA,SQL]
Nitin4231[Hive,Presto]
Gaurab322[Ruby,Perl]
Amod421[Python,R]

The column subject is of Array type. How will you query the above table?
What if you want to know which person is reading Perl?



Select * from table where Subject = ‘Perl’ won’t work for obvious reasons !!



Depending upon what tool you use, you can explore the command.
In this article, we will be taking PrestoSQL and Hive into account

Basically, we need to flatten this column Subject and we will be creating as many rows as there are Subject elements in the filed. Our final output for the above should be

NameEmp_idexpertise
Rizwan123C
Rizwan123JAVA
Rizwan123SQL
Nitin4231Hive
Nitin4231Prest
Gaurab322Ruby
Gaurab322Perl
Amod421Python
Amod421R

Now you can easily query the

CROSS JOIN UNNEST

Select Name,Emp_id,expertise
from Employee
CROSS JOIN UNNEST(Subject) as t(expertise)

If there are multiple arrays

Select Name,Emp_id,expertise,phone_num
from Employee
CROSS JOIN UNNEST(Subject,Phone) as t(expertise,phone_num)

What we are doing here is that we are cross joining the table with all the value present in every row depending on the number of elements in the array

HIVE

Select Name,Emp_id,expertise
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise

If there are two columns to be unnested then

Select Name,Emp_id,expertise,Phone
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise
LATERAL VIEW explode(Phone) myTable2 as Phone

When you just explode a column like

EXPLODE(Subject) then it will provide you the list of all the elements present in the column, like
C,JAVA,SQL,etc.

Practice a few questions

What is CROSS JOIN UNNEST?
What is LATERAL VIEW EXPLODE in SQL?

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

  1. 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
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. 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
  4. 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
  5. 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