Complete topics & subtopics of hashtag #SQL for Data Analyst role:-

𝟭. 𝗕𝗮𝘀𝗶𝗰 𝗦𝗤𝗟 𝗦𝘆𝗻𝘁𝗮𝘅:
SQL keywords
Data types
Operators
SQL statements (SELECT, INSERT, UPDATE, DELETE)

𝟮. 𝗗𝗮𝘁𝗮 𝗗𝗲𝗳𝗶𝗻𝗶𝘁𝗶𝗼𝗻 𝗟𝗮𝗻𝗴𝘂𝗮𝗴𝗲 (𝗗𝗗𝗟):
CREATE TABLE
ALTER TABLE
DROP TABLE
Truncate table

𝟯. 𝗗𝗮𝘁𝗮 𝗠𝗮𝗻𝗶𝗽𝘂𝗹𝗮𝘁𝗶𝗼𝗻 𝗟𝗮𝗻𝗴𝘂𝗮𝗴𝗲 (𝗗𝗠𝗟):
SELECT statement (SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, JOINs)
INSERT statement
UPDATE statement
DELETE statement

𝟰. 𝗔𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗲 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀:
SUM, AVG, COUNT, MIN, MAX
GROUP BY clause
HAVING clause

𝟱. 𝗗𝗮𝘁𝗮 𝗖𝗼𝗻𝘀𝘁𝗿𝗮𝗶𝗻𝘁𝘀:
Primary Key
Foreign Key
Unique
NOT NULL
CHECK

𝟲. 𝗝𝗼𝗶𝗻𝘀:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
Self Join
Cross Join

𝟳. 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀:
Types of subqueries (scalar, column, row, table)
Nested subqueries
Correlated subqueries

𝟴. 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀:
String functions (CONCAT, LENGTH, SUBSTRING, REPLACE, UPPER, LOWER)
Date and time functions (DATE, TIME, TIMESTAMP, DATEPART, DATEADD)
Numeric functions (ROUND, CEILING, FLOOR, ABS, MOD)
Conditional functions (CASE, COALESCE, NULLIF)

𝟵. 𝗩𝗶𝗲𝘄𝘀:
Creating views
Modifying views
Dropping views

𝟭𝟬. 𝗜𝗻𝗱𝗲𝘅𝗲𝘀:
Creating indexes
Using indexes for query optimization

𝟭𝟭. 𝗧𝗿𝗮𝗻𝘀𝗮𝗰𝘁𝗶𝗼𝗻𝘀:
ACID properties
Transaction management (BEGIN, COMMIT, ROLLBACK, SAVEPOINT)
Transaction isolation levels

𝟭𝟮. 𝗗𝗮𝘁𝗮 𝗜𝗻𝘁𝗲𝗴𝗿𝗶𝘁𝘆 𝗮𝗻𝗱 𝗦𝗲𝗰𝘂𝗿𝗶𝘁𝘆:
Data integrity constraints (referential integrity, entity integrity)
GRANT and REVOKE statements (granting and revoking permissions)
Database security best practices

𝟭𝟯. 𝗦𝘁𝗼𝗿𝗲𝗱 𝗣𝗿𝗼𝗰𝗲𝗱𝘂𝗿𝗲𝘀 𝗮𝗻𝗱 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀:
Creating stored procedures
Executing stored procedures
Creating functions
Using functions in queries

𝟭𝟰. 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲 𝗢𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻:
Query optimization techniques (using indexes, optimizing joins, reducing subqueries)
Performance tuning best practices

𝟭𝟱. 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 𝗖𝗼𝗻𝗰𝗲𝗽𝘁𝘀:
Recursive queries
Pivot and unpivot operations
Window functions (Row_number, rank, dense_rank, lead & lag)
CTEs (Common Table Expressions)
Dynamic SQL

𝗝𝗼𝗶𝗻 𝗺𝘆 𝗧𝗲𝗹𝗲𝗴𝗿𝗮𝗺 𝗖𝗵𝗮𝗻𝗻𝗲𝗹 - https://t.me/codingdidi

If you've read so far, do LIKE the post👍

https://www.linkedin.com/posts/akansha-yadav24_100-dbms-questions-activity-7239652499761086465-gJbH?utm_source=share&utm_medium=member_android


100 DBMS interview Questions

Top 10 Advanced SQL Queries for Data Mastery

1. Recursive CTE (Common Table Expressions)
Use a recursive CTE to traverse hierarchical data, such as employees and their managers.

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT employee_id, employee_name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.employee_name, e.manager_id
  FROM employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * 
FROM EmployeeHierarchy;

2. Pivoting Data
Turn row data into columns (e.g., show product categories as separate columns).

SELECT *
FROM (
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, product_category, sales_amount
    FROM sales
) AS pivot_data
PIVOT (
    SUM(sales_amount)
    FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS pivoted_sales;

3. Window Functions
Calculate a running total of sales based on order date.

SELECT 
  order_date, 
  sales_amount, 
  SUM(sales_amount) OVER (ORDER BY order_date) AS running_total
FROM sales;

4. Ranking with Window Functions
Rank employees’ salaries within each department.

SELECT 
  department, 
  employee_name, 
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

5. Finding Gaps in Sequences
Identify missing values in a sequential dataset (e.g., order numbers).

WITH Sequences AS (
  SELECT MIN(order_number) AS start_seq, MAX(order_number) AS end_seq
  FROM orders
)
SELECT start_seq + 1 AS missing_sequence
FROM Sequences
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.order_number = Sequences.start_seq + 1
);

6. Unpivoting Data
Convert columns into rows to simplify analysis of multiple attributes.

SELECT 
  product_id, 
  attribute_name, 
  attribute_value
FROM products
UNPIVOT (
  attribute_value FOR attribute_name IN (color, size, weight)
) AS unpivoted_data;

7. Finding Consecutive Events
Check for consecutive days/orders for the same product using LAG().

WITH ConsecutiveOrders AS (
  SELECT 
    product_id, 
    order_date,
    LAG(order_date) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date
  FROM orders
)
SELECT product_id, order_date, prev_order_date
FROM ConsecutiveOrders
WHERE order_date - prev_order_date = 1;

8. Aggregation with the FILTER Clause
Calculate selective averages (e.g., only for the Sales department).

SELECT 
  department,
  AVG(salary) FILTER (WHERE department = 'Sales') AS avg_salary_sales
FROM employees
GROUP BY department;

9. JSON Data Extraction
Extract values from JSON columns directly in SQL.

SELECT 
  order_id, 
  customer_id,
  order_details ->> 'product' AS product_name,
  CAST(order_details ->> 'quantity' AS INTEGER) AS quantity
FROM orders;

10. Using Temporary Tables
Create a temporary table for intermediate results, then join it with other tables.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_product_sales AS
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

-- Use the temp table
SELECT p.product_name, t.total_sales
FROM products p
JOIN temp_product_sales t ON p.product_id = t.product_id;

Why These Matter
Advanced SQL queries let you handle complex data manipulation and analysis tasks with ease. From traversing hierarchical relationships to reshaping data (pivot/unpivot) and working with JSON, these techniques expand your ability to derive insights from relational databases.

Keep practicing these queries to solidify your SQL expertise and make more data-driven decisions!

Here you can find essential Pyspark Resources👇
https://www.instagram.com/codingdidi

Like this post if you need more 👍❤️

Hope it helps :)

#sql #dataanalyst