Blog Archives
1 2

00: 18+ SQL best practices interview Q&As

It is a must to know the order in which the SQL clauses are executed. This is demonstrated with an example below in #5. Have this order of execution visibly pinned and understood. SQL is very easy to learn, but lots of hands-on experience is required to master:

1) to translate business requirements into SQL.
2) to write efficient & maintainable queries.
3) to break-down & reverse engineer complex SQL queries into business requirements to enhance or modify.

Let’s start with the best practices.

#1. Use uppercase for the keywords like SELECT, FROM, JOIN, GROUP BY, WHERE, etc. It’s …

Tags:

00: 25+ SQL interview questions & answers – beginner

SQL interview Questions & Answers is a must for any developer as all non-trivial applications need to talk to a database with CRUD operations. Q3 – Q15 are very popular with the interviewers.

If you want to quickly practice your SQL skills try DB Fiddle or install MySQL locally as shown in the MySQL database getting started.

Q1. Can you explain DDL statements in regards to SQL?
A1. DDL stands for Data Definition Language, which are statements used to define the database structure or schema. CREATE – to create objects in the database, ALTER – alters the structure of …

Tags:

00: 25+ SQL interview questions & answers – intermediate to experienced

This continues 25+ SQL interview questions & answers – beginner Q16. Why do you have CASE statements in SQL? A16. CASE statements in SQL are similar to IF and ELSE conditions in programming languages. CASE statements are used to fetch particular values based on certain conditions.



01: 50+ SQL scenarios based interview Q&As on identifying & deleting duplicate records

50+ SQL interview questions and answers to solve real business scenarios. SQL is widely used in building microservices & Big Data projects. Learning SQL syntax is easy, but being able to convert a given business requirement into a query takes lots of practice. These scenarios based interview questions can assesses your experience.

Considerations & Tips

It is important to first understand the problem statement, and then ask the right questions to solve the problem.

1) Break down a complex business requirement into sub sections.

2) Think about the grain of the data. For example, account level, user level, department level, …

Tags: ,

02: 50+ SQL scenarios based interview Q&As on joins, CDC and recursive CTE

This extends 50+ SQL scenarios based interview questions answered on identifying & deleting duplicate records. You practice these in SQLs by setting up the data on db-fiddle MySQL V8.0 or a database server & SQL client of your choice. Q01 When you have a table that maintains history of record…



02: SQL analytic functions interview questions – Part 1

Data Engineers & analysts must know the analytic functions applied over a window of rows is a powerful tool to analyse the data. This post compliments “Apache Spark window functions” by focusing on MySQL server. You can also apply this on Big Data SQL engines like Hive & impala, but…



02: SQL analytic functions interview questions – Part 2

This extends SQL window function for analytical queries interview questions – Part 1 Analytic function construct

The analytic function names can be RANK(), DENSE_RANK(), ROW_NUMBER(), MIN(column), MAX(column), SUM(column), COUNT(), AVG(column), FIRST_VALUE(column), LAST_VALUE(column), NTH_VALUE(column, n), LEAD(return_value ,offset [,default]), LAG(return_value ,offset [,default]), NTILE(n), CUME_DIST(), PERCENT_RANK(), etc. These functions can be applied…

Members Only Content


Login



02: SQL analytic functions interview questions – Part 3

This extends SQL analytic functions interview questions – Part 2 and SQL analytic functions interview questions – Part 1. The below example was created in IMPALA SQL engine with Common Table Expression (i.e CTE).



03: 50+ SQL scenarios based interview Q&As on familiarising various features

This extends 50+ SQL scenarios based interview Q&As on joins, CDC and recursive CTE. Q01 In What order the SELECT SQL statements are executed? A01 Here is the the order in which the SQL statements are evaluated. 1) FROM (including JOIN, etc.) 2) WHERE 3) GROUP BY (can remove duplicates)…



04: 50+ SQL scenarios based interview Q&As on Nth value

This SQL scenarios based interview questions & answers focus on nth highest value or nth lowest value. These examples were done on MySQL v8.0 at www.db-fiddle.com. This will answer questions like:

Problem statements & hints

1) Who gets the 2nd highest salary? [You need to have salary data]

2) Who got the 2nd lowest mark in Maths? [You need to have student marks]

3) Which region has the 3rd highest sales volume? [You need to have the sales data]

4) Which country has the 4th lowest literacy rate? [You need to have the …



05: 50+ SQL scenarios based interview Q&As on N items per group

This extends 15+ SQL scenarios based interview Q&As on Nth value – part 4. This SQL scenarios based interview questions & answers focus on N items per group. These examples were done on MySQL v8.0 at www.db-fiddle.com. This will answer questions like: Problem statements & hints 1) Find the N…



06: 50+ SQL scenarios based interview Q&As on unique N items per group

This extends 15+ SQL scenarios based interview Q&As on N items per group – part 5 This SQL scenarios based interview questions & answers focus on N items per group. These examples were done on MySQL v8.0 at www.db-fiddle.com. This will answer questions like: Problem statements & hints 1) Find…



07: 50+ SQL scenarios based interview Q&As on Pivot Vs. Unpivot

Q01 Often you will have a requirement to report on a certain dimension, which requires you to convert rows into columns or columns into rows. How will you go about accomplishing this using SQL?
A01 The process of converting rows into columns is know as “PIVOTing”. The reverse process of converting the columns into rows is known as “UNPIVOTing”.

Some databases provide PIVOT & UNPIVOT functions, but let’s use SQL to achieve this. Let’s use DB Fiddle to practice SQL using MySQL V8.0 as depicted below.

SQL – PIVOT

PIVOTing is also known as transposing



08: 50+ SQL scenarios based interview Q&As on CASE statements & NULL safe join

Below question is popular in SQL coding interviews. SQL syntaxes are very easy to learn, but lots of practice is required to be able to understand the business intent, and convert that into SQL statements with proper what if considerations. Break down the requirements to construct the SQL statements.

Problem Statement & considerations

Q: Given the below table with win versus loss between 2 teams, how will you create a points table with played, won & lost?



09: 50+ SQL scenarios based interview Q&As on GROUP_CONCAT() & STRING_AGG()

This post covers an SQL scenario, and how to solve it with NON ANSI 92 aggregate functions. Refer to your database documentation for the additional aggregate functions. In this post let’s look at how to implode or concatenate multiple row column values into a single column. Problem statement & considerations…



10: 50+ SQL scenarios based interview Q&As on split delimited column values to rows

This extends 09: 50+ SQL scenarios based interview Q&As on GROUP_CONCAT() & STRING_AGG().

In the previous post we looked at how to implode or concatenate multiple row column values into a single column. In this post let’s look how we can explode or split delimited column value to multiple rows.



11: 50+ SQL scenarios based interview Q&As on LEAD(..) & LAG(..) window functions

LEAD(..) & LAG(..) are Window functions, which perform operations for each row of the partition or window. LEAD() and LAG() are used to accesses a value stored in a row below and a value stored in a row above respectively. An ORDER BY clause is required when working with LEAD…



12: 50+ SQL scenarios based interview Q&As on Nth value with variations

This extends 50+ SQL scenarios based interview Q&As on Nth value with some variations to practice. Problem statement & considerations Find the 5th highest salary for each department. If the number of highest value for a particular department is less than 5, then display the lowest salary for that department….



13: 50+ SQL scenarios based interview Q&As on self-join

Let’s try solving a problem statement given below by creating sample data & then an SQL query on db-fiddle using MySQL v8 database. You can also look at a hierarchical (i.e. tree structure) employees example using a self-join at 50+ SQL scenarios based interview Q&As on joins, CDC and recursive…



14: 50+ SQL scenarios based interview Q&As on order of SQL executions

This post reiterates the importance of understanding the order of execution of the SQL statements. Also, always think of the data granularity (aka grain). This post also demonstrates that the final query may look complex, but if you break it down it is much easier to comprehend.

1) FROM (including JOIN, etc.)
2) WHERE
3) GROUP BY (can remove duplicates)
4) Aggregations like SUM(), COUNT(), etc (aggregates the grouped data)
5) HAVING (filters the grouped & aggregated data)
6) Window functions (aka SQL analytic functions)
7) SELECT
8) DISTINCT (can remove duplicates)
9) UNION, INTERSECT, EXCEPT (can …



1 2

300+ Java & Big Data FAQs - Quick Prep

Java & Big Data Tutorials

Top