SQL Tricky Interview Questions – Day 1

SQL Tricky Interview Questions
SQL, which stands for Structured Query Language, is a domain-specific programming language used for managing and manipulating relational databases. It is the standard language for interacting with relational database management systems (RDBMS), which are software systems designed to store, retrieve, and manage structured data.

Here are some key aspects of SQL:

1. Data Retrieval: SQL allows users to retrieve data from a database using queries. With SQL queries, you can specify the data you want to retrieve, filter it based on certain criteria, and sort it in a particular order.
2. Data Manipulation: SQL provides commands for inserting, updating, and deleting data in a database. These commands allow users to modify the content of a database while maintaining data integrity.
3. Schema Definition: SQL allows users to define the structure of a database, including tables, columns, data types, constraints, and relationships between tables. This is done using Data Definition Language (DDL) statements.
4. Data Modification: SQL provides Data Manipulation Language (DML) statements, such as INSERT, UPDATE, and DELETE, to modify the data stored in database tables.
5. Data Querying: SQL’s SELECT statement is used to query and retrieve data from one or more database tables. Users can use various clauses and operators to filter, aggregate, and manipulate the retrieved data.
6. Transaction Control: SQL supports transactions, which are sequences of one or more SQL statements that are treated as a single unit of work. Transactions ensure data consistency and integrity by allowing operations to be either fully completed or fully rolled back in case of errors.
7. Indexing: SQL allows the creation of indexes on database tables to improve query performance. Indexes help the database system quickly locate and retrieve data rows.
8. Security: SQL provides mechanisms for controlling access to the database, including user authentication and authorization. Database administrators can grant or revoke permissions to specific users or roles.
9. Reporting: SQL is commonly used for generating reports from a database. It allows users to aggregate and summarize data, perform calculations, and format results for presentation.

SQL is used in a wide range of applications and industries, from web development to business intelligence and data analysis. Popular relational database management systems that use SQL as their query language include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite, among others. SQL is an essential skill for database administrators, data analysts, software developers, and anyone working with structured data in a relational database.
SQL Tricky Interview Questions

Day 1 – SQL Tricky Interview Questions

1.Common Table Expressions (CTEs):

A CTE is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs make complex queries more readable and maintainable.
Use CTEs when you need to break down a complex query into smaller, more understandable parts

2.Subqueries vs. JOINs:

-Subqueries are nested queries within another query and are used to retrieve data for further processing.
-JOINs combine rows from two or more tables based on a related column.
-Use subqueries when you need to retrieve a single value or a small set of values, and use JOINs when you need to combine data from multiple tables.

3.How do the SQL commands flow at the back end?

Ans.
Order of execution for an SQL query
1) FROM, including JOINs
2) WHERE
3) GROUP BY
4) HAVING
5) WINDOW Functions
6) SELECT
7) DISTINCT
8) UNION
9) ORDER BY
10) LIMIT AND OFFSET

4.Write a SQL query to find all the student names Nitin in a table

SELECT name
FROM student
WHERE lower(name) like ‘%nitin%’

Now the trick is to make sure you convert the name in lower for the complete column

What will get you wrong output?
name like ‘%nitin%’

As this will not capture Nitin, niTin, etc.

5.Write a query to get all the student with name length 10, starting with K and ending with z.

SELECT name
FROM student
WHERE length(name)=10 and lower(name) like ‘k%z’

7.ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability of database transactions.
Atomicity ensures that a transaction is treated as a single, indivisible unit.
Consistency guarantees that a transaction brings the database from one consistent state to another.
Isolation ensures that transactions are executed independently.
Durability guarantees that once a transaction is committed, its effects are permanent.

8.Convert ‘2023-10-15′ to ’15-Oct-2023’.
You can use the TO_CHAR function to format the date:

SELECT TO_CHAR(TO_DATE('2023-10-15', 'YYYY-MM-DD'), 'DD-Mon-YYYY') AS formatted_date;

9.Calculate the date that is 90 days from today.
Use the CURRENT_DATE and INTERVAL for date arithmetic:

SELECT CURRENT_DATE + INTERVAL '90 days' AS future_date;

10.Determine the day of the week for ‘2023-11-20’.
Use the TO_CHAR function to extract the day of the week

SELECT TO_CHAR(TO_DATE('2023-11-20', 'YYYY-MM-DD'), 'Day') AS day_of_week;

11.Display ‘N/A’ for employees with no ‘hire_date’.
Use the COALESCE function to provide a default value for NULL dates:

SELECT COALESCE(TO_CHAR(hire_date, 'YYYY-MM-DD'), 'N/A') AS formatted_hire_date FROM employees;

12.Convert a timestamp from one time zone to another.
Use the AT TIME ZONE clause to perform the conversion:

SELECT timestamp_column AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS converted_timestamp 
FROM table_name;

TDM Youtube Video – Day 1

Go through the below 3 videos without fail on Day 1
Fetch Duplicate Records in SQL
LIKE operator in SQL
Substring and Replace in SQL

TDM Instagram Post – Day 1 (Click and Follow the page)

How The Data Monk can help you?

We have created products and services on different platforms to help you in your Analytics journey irrespective of whether you want to switch to a new job or want to move into Analytics.

Our services

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
   Link – The Data E-shop Page
   
4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts)
   Link – The Data Monk Instagram page
   
5. Mock Interviews
   Book a slot on Top Mate
   
6. Career Guidance/Mentorship
   Book a slot on Top Mate
   
7. Resume-making and review
   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:

✅ Data Analyst and Product Analyst -> 1100+ Most Asked Interview Questions

✅ Business Analyst -> 1250+ Most Asked Interview Questions

✅ Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions

✅ Full Stack Analytics Professional – 2200 Most Asked Interview Questions

The Data Monk – 30 Days Mentorship program

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer website charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
– 2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
– 4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
– 4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
– Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000

How to avail of this offer?
Send a mail to [email protected]