SQL Complete Tutorial – 7 Days Analytics Course

SQL Complete Tutorial

The link to FREE SQL e-book will be sent to all the users on The Data Monk Instagram , follow the page to get access to the 100 SQL interview questions

SQL, or Structured Query Language, is a programming language designed for managing and manipulating data stored in relational databases. In simple terms, SQL allows you to communicate with a database to perform various operations such as:

1. Querying Data: Retrieve information from a database by specifying conditions and filters.
2. Inserting Data: Add new records or rows to a database.
3. Updating Data: Modify existing records or rows in a database.
4. Deleting Data: Remove records or rows from a database.
5. Creating and Modifying Tables: Define the structure of a database by creating tables and altering their properties.
6. Ensuring Data Integrity: Enforce rules and relationships to maintain the accuracy and consistency of data.

SQL is used across a wide range of applications and industries to interact with and manage data efficiently in a structured manner. It serves as a common language for communication between applications and relational database management systems (RDBMS). Whether you’re working with a small business database or a large-scale enterprise system, SQL provides a standardized way to interact with and manipulate data.

SQL Complete Tutorial

SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. Here are some basic concepts and common SQL commands:

1. SQL Basics:

Database: A collection of tables that are related to each other.

Table: A collection of rows and columns to store data.

Column: Represents a single attribute of data in a table.

Row: A single record in a table.

Primary Key: A unique identifier for a record in a table.

2. SQL 20 Questions

1. What is SQL?
   • SQL stands for Structured Query Language. It is a standard programming language used for managing and manipulating relational databases.
2. Explain the difference between SQL and NoSQL databases.
   • SQL databases are relational databases, whereas NoSQL databases are non-relational and can store and retrieve data in ways other than tabular relations.
3. What is a primary key?
   • A primary key is a unique identifier for each record in a table. It must contain unique values and cannot have NULL values.
4. What is a foreign key?
   • A foreign key is a field in a table that refers to the primary key in another table, establishing a link between the two tables.
5. What is normalization?
   • Normalization is the process of organizing data in a database to reduce redundancy and dependency.
6. Explain the difference between INNER JOIN and LEFT JOIN.
   • INNER JOIN returns only the matching rows in both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table.
7. What is the purpose of the GROUP BY clause?
   • The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows.
8. Explain the difference between WHERE and HAVING clauses.
   • WHERE is used to filter rows before grouping, and HAVING is used to filter groups after grouping has occurred.
9. What is an index, and why is it important?
   • An index is a data structure that improves the speed of data retrieval operations on a database table. It is important for efficient querying.
10. What is a stored procedure?
    • A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit.
11. Explain ACID properties in the context of database transactions.
    • ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably.
12. What is a subquery?
    • A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition.
13. What is a view?
    • A view is a virtual table based on the result of a SELECT query. It allows users to query the data in the view as if it were a table.
14. How do you find duplicate values in a table?
    • Using the GROUP BY and HAVING clauses or by using the DISTINCT keyword.
15. What is the difference between UNION and UNION ALL?
    • UNION combines the result sets of two SELECT statements, removing duplicate rows, while UNION ALL includes all rows, including duplicates.
16. Explain the concept of a trigger.
    • A trigger is a set of instructions that are automatically executed or fired in response to certain events on a particular table or view.
17. What is the purpose of the COMMIT statement?
    • The COMMIT statement is used to save all changes made during the current transaction.
18. What is the purpose of the ROLLBACK statement?
    • The ROLLBACK statement is used to undo transactions that have not been saved to the database.
19. How do you handle NULL values in SQL?
    • NULL is a special marker used to indicate that a data value does not exist in the database. To handle NULL values, you can use the IS NULL or IS NOT NULL conditions.
20. What is the difference between CHAR and VARCHAR data types?
    • CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. VARCHAR is more flexible as it only uses storage for the actual characters entered.

3. SQL Queries

Creating a Database:

CREATE DATABASE database_name;

Selecting a Database:

USE database_name;

Creating a Table:

CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (one_or_more_columns) );

Inserting Data:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Querying Data:

SELECT column1, column2, ... FROM table_name WHERE condition;

Updating Data:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Deleting Data:

DELETE FROM table_name WHERE condition;

Filtering Results:

SELECT column1, column2, ... FROM table_name WHERE condition;

Sorting Results:

SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC/DESC;

Aggregating Data:

SELECT COUNT(column) FROM table_name; SELECT AVG(column) FROM table_name; SELECT SUM(column) FROM table_name;

Joins:

SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;

4. SQL Tricky Interview Questions

1. How do you find the second highest salary from an Employee table?
   • SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
2. What is the purpose of the COALESCE function?
   • COALESCE returns the first non-null expression among its arguments. It is useful for replacing NULL values with alternative values.
3. Explain the difference between a correlated subquery and a non-correlated subquery.
   • A correlated subquery depends on values from the outer query, while a non-correlated subquery can run independently of the outer query.
4. How can you generate row numbers in a result set?
   • Using the ROW_NUMBER() window function.
     For example:
     SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_num, column_name
FROM table_name;
5. What is the purpose of the RANK() function?
   • The RANK() function assigns a rank to each row within the result set based on the values in the specified column.
6. Explain the concept of a self-join.
   • A self-join is a regular join, but the table is joined with itself. It’s used when records in a table have a relationship with other records in the same table.
7. How can you prevent SQL injection in your queries?
   • Use parameterized queries or prepared statements to ensure that user inputs are treated as data and not executable code.
8. What is the purpose of the LAG() and LEAD() window functions?
   • LAG() accesses data from a previous row, and LEAD() accesses data from a subsequent row within the result set.
9. Explain the differences between the EXISTS and IN clauses.
   • EXISTS is used to check the existence of a result set from a subquery, while IN is used to compare a value to a list of values.
10. What is a CTE (Common Table Expression), and when would you use it?
    • A CTE is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is often used for recursive queries or simplifying complex queries.
11. How do you retrieve alternate records from a table using a single SQL query?
    • Using the ROW_NUMBER() function and a modulo operation.
      For example:
      
      SELECT * FROM
( SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
FROM table_name ) AS temp
WHERE row_num % 2 = 1;/
12. What is the purpose of the HAVING clause, and when is it used?
    • The HAVING clause filters results after the GROUP BY clause has been applied. It is used to filter aggregated data.
13. Explain the purpose of the NULLIF function.
    • NULLIF returns NULL if the two specified expressions are equal; otherwise, it returns the first expression.
14. How can you pivot rows to columns in SQL?
    • Using the PIVOT operator.
      For example:
      SELECT * FROM (
SELECT category, value FROM table_name ) AS SourceTable PIVOT ( MAX(value) FOR category IN ([Category1], [Category2], [Category3]) ) AS PivotTable;
15. What is the purpose of the MERGE statement?
    • The MERGE statement performs insert, update, or delete operations on a target table based on the results of a source table.
16. How can you find the top N records from a table?
    • Using the LIMIT or TOP clause, depending on the database system.
      For example:
      SELECT * FROM table_name
LIMIT N;
17. Explain the difference between a clustered and a non-clustered index.
    • A clustered index determines the physical order of data in a table, while a non-clustered index does not affect the physical order.
18. How can you concatenate strings from multiple rows into a single string?
    • Using the STRING_AGG function.
      For example:
      SELECT column_name, STRING_AGG(value, ', ') AS concatenated_values FROM table_name
GROUP BY column_name;

5. SQL Complex Concepts

1. Recursive Queries:
   • Recursive queries involve a table that references itself, creating a hierarchy or tree structure. Commonly used with the WITH RECURSIVE clause, allowing a query to refer to its own output.
   Example:
   
   WITH RECURSIVE EmployeeHierarchy AS
( SELECT employee_id, manager_id
FROM employees WHERE manager_id IS NULL

UNION ALL

SELECT e.employee_id, e.manager_id FROM employees e JOIN EmployeeHierarchy eh ON eh.employee_id = e.manager_id )

SELECT * FROM EmployeeHierarchy;
   
2. Window Functions (Advanced):
   • Advanced usage of window functions involves more complex window specifications, like framing. The ROWS BETWEEN clause allows you to define a range of rows to include in the window for aggregate functions.
   Example:
   
   SELECT order_date,
order_amount,
SUM(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM orders;
   
3. Materialized Views:
   • A materialized view is a precomputed table that stores the result of a query. It can be refreshed periodically, providing faster access to aggregated or complex data.
   Example:
   
   CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT product, SUM(sales) AS total_sales
FROM sales
GROUP BY product;
   
4. Database Transactions (Isolation Levels):
   • Isolation levels define the degree to which one transaction must be isolated from the effects of other transactions. Common isolation levels include READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
   
   Example:
   
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Your SQL statements
COMMIT;
   
5. Triggers:
   • Triggers are special stored procedures that are automatically executed (or “triggered”) in response to events, such as data modifications (INSERT, UPDATE, DELETE).
   
   Example:
   
   CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW INSERT INTO audit_table
(action, employee_id, action_date) VALUES (TG_OP, NEW.employee_id, NOW());
   
6. Dynamic SQL:
   • Dynamic SQL involves generating and executing SQL statements at runtime. It’s often used in scenarios where the structure of the SQL query is not known until the program runs.
   
   Example (in a stored procedure):
   
   DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM your_table WHERE column_name = ''your_value'''; EXEC sp_executesql @sql;
   
7. Full-Text Search:
   • Full-Text Search is a feature in SQL that enables you to perform complex queries on large amounts of text data. It allows searching for words and phrases, and it supports linguistic analysis for different languages.
   
   Example:
   
   SELECT * FROM articles
WHERE CONTAINS(article_text, 'SQL AND Full-Text Search');
   
8. Materialized Path Pattern:
   • The materialized path pattern is a way of representing hierarchies in a table using a single string column that contains the path from the root to the node.
   Example:
   Table structure: node_id, node_name, path

SELECT * FROM nodes
WHERE path LIKE '/root/parent%';

5. SQL Window Functions

Window functions in SQL allow you to perform calculations across a set of rows related to the current row within the result set. They are used with the OVER clause and can help you analyze and compare values in a more advanced way than regular aggregate functions. Here’s a simple explanation:

Basic Structure:

SELECT column1,
column2,
window_function(column3) OVER (PARTITION BY partition_column ORDER BY order_column)
FROM your_table;

Explanation:

1. window_function: This is the window function you want to apply, such as ROW_NUMBER(), SUM(), AVG(), etc.
2. PARTITION BY: This clause divides the result set into partitions to which the window function is applied. It’s like grouping your data within each partition.
3. ORDER BY: This clause determines the order of rows within each partition. The window function is applied in this order.

Example:

Let’s say you have a table with sales data:

SELECT product,
sales,
SUM(sales) OVER (PARTITION BY product ORDER BY date) AS running_total
FROM sales_table;

In this example, SUM(sales) OVER (PARTITION BY product ORDER BY date) calculates the running total of sales for each product, ordered by date. The PARTITION BY product ensures that the running total restarts for each new product.

Common Window Functions:

• ROW_NUMBER(): Assigns a unique number to each row based on the specified order.
• RANK(): Assigns a rank to each row based on the specified order, with ties receiving the same rank and leaving gaps in the ranking.
• DENSE_RANK(): Similar to RANK(), but without gaps in the ranking for tied values.
• SUM(), AVG(), MIN(), MAX(): Calculate aggregate values over a window of rows.

In Simple Terms:

Window functions let you perform calculations that involve multiple rows at once. They are like looking through a “window” of data and applying a function to that subset of rows. This is useful for running calculations over specific groups of data or for creating rankings and running totals within your result set.

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 websites 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