@Codingdidi
Open in Telegram
Free learning Resources For Data Analysts, Data science, ML, AI, GEN AI and Job updates, career growth, Tech updates
Show more9 056
Subscribers
No data24 hours
-347 days
-8030 days
Posts Archive
9 056
Which of the following matches the definition given below: It is an artificial key that aims to uniquely identify each record.
9 056
Hurry up and enroll within Navratri offer and get low prices! 🫶🏻😍
WhatsApp at +91-9910986344
9 056
Which of the following statements about TCL (Transaction Control Language) in SQL is FALSE?
9 056
Are you done with watching 𝐒𝐐𝐋 tutorials but don't know where to practice it?
Check out these top 11 online sources that provide practical exercises and challenges to help you master SQL:
1. SQL Zoo: https://sqlzoo.net/wiki/SQL_Tutorial
2. SQLBolt : https://sqlbolt.com/
3. SQLPad: https://sqlpad.io/
4. Mode: https://mode.com/
5. Strata Scratch: https://www.stratascratch.com/
6. LeetCode: https://leetcode.com/problemset/all/
7. HackerRank: https://www.hackerrank.com/domains/sql
8. W3 Schools: https://www.w3schools.com/sql/default.asp
9. SQL Roadmap: https://t.me/sqlspecialist/386
10. Learnsql: https://learnsql.com/?ref=analyst
9 056
SQL books won’t teach you this.
Natural Keys vs. Autoincrement IDs vs. Public IDs. (or maybe all together)
𝗡𝗮𝘁𝘂𝗿𝗮𝗹 𝗞𝗲𝘆𝘀
Natural keys carry intrinsic meaning because they are part of the domain.
They are directly related to the data, making them intuitive and easy to understand. Examples include email addresses or employee IDs.
The problem is that they are usually not good for performance, but they can also be a security risk if you expose them.
𝗔𝘂𝘁𝗼𝗶𝗻𝗰𝗿𝗲𝗺𝗲𝗻𝘁 𝗜𝗗𝘀
Autoincrement IDs automatically generate unique integers to identify rows within a table.
They are often used as primary keys.
Simple integers are fast for the database to index and query. They provide optimal performance.
However, they are vulnerable to enumeration attacks since predicting the next or previous record is easy.
𝗣𝘂𝗯𝗹𝗶𝗰 𝗜𝗗𝘀 (𝗨𝗨𝗜𝗗𝘀)
UUIDs (Universally Unique Identifiers) are 128-bit identifiers used to uniquely identify information without relying on a centralized authority.
They are difficult to guess, making them suitable for public exposure in APIs.
The problem is they are larger and more complex than integers. This can impact performance, particularly in indexing and storage.
𝗙𝗶𝗻𝗱𝗶𝗻𝗴 𝘁𝗵𝗲 𝗦𝘄𝗲𝗲𝘁 𝗦𝗽𝗼𝘁: 𝗔 𝗠𝗶𝘅𝗲𝗱 𝗔𝗽𝗽𝗿𝗼𝗮𝗰𝗵
Combining different types of keys can offer a balanced solution:
• InternalID: Used for internal operations and relationships between tables.
• PublicID: Used in API responses and endpoints to securely reference user records.
• Email (Natural Key): Used to ensure unique identification of users within the business logic.
The mixed approach keeps your system fast, secure, and easy to understand.
9 056
Hello everyone!!
I hope this 30 day sql series helped you all.
Looking forward to your feedback for future series!! 😍
9 056
🔹 Final Challenge: Solve an Advanced SQL Problem
Problem Statement:
You have a table with user transactions. Find the second highest transaction amount for each user.
Dataset: Transactions Table
| TransactionID | UserID | Amount |
|--------------|--------|--------|
| 1 | 101 | 500 |
| 2 | 101 | 700 |
| 3 | 102 | 900 |
| 4 | 101 | 800 |
| 5 | 102 | 1000 |
Solution Using Window Function
SELECT UserID, Amount
FROM (
SELECT UserID, Amount,
RANK() OVER (PARTITION BY UserID ORDER BY Amount DESC) AS rnk
FROM Transactions
) Ranked
WHERE rnk = 2;9 056
Day 30: Final Review & SQL Projects – Apply Your Knowledge! 🚀
🎉 Congratulations! You've reached the final day of your 30-day SQL journey. Today, we’ll:
✅ Review all key SQL concepts learned over the past 30 days.
✅ Work on real-world SQL projects to apply your skills.
✅ Solve case studies & challenges to test your knowledge.
By the end of this lesson, you’ll be confident in writing SQL queries and solving real-world data problems!
---
🔹 Quick Recap: Key SQL Concepts You Learned
| Topic | Key Learnings |
|-----------|------------------|
| Day 1-5: Basics | SQL syntax, SELECT, WHERE, ORDER BY, GROUP BY, HAVING, Aggregate Functions |
| Day 6-10: Joins & Subqueries | INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, Subqueries |
| Day 11-15: Advanced Queries | Common Table Expressions (CTEs), Window Functions, Case Statements |
| Day 16-20: Views, Transactions, Indexing | Creating Views, Indexing, Transactions & ACID properties, Error Handling |
| Day 21-25: Database Design & Security | Normalization (1NF, 2NF, 3NF, BCNF), Constraints, Backup & Restore, Role-Based Permissions |
| Day 26-29: Performance Optimization | Pivoting, JSON/XML in SQL, Stored Procedures, Triggers, Query Performance Tuning |
If you’ve completed all the lessons, you now have a solid SQL foundation! 🏆
---
🔹 Real-World SQL Projects & Case Studies
Now, let’s apply what you’ve learned by working on SQL projects.
Project 1: Analyzing Sales Data 📊
Scenario: You work for an e-commerce company, and management wants to analyze sales performance.
Dataset: Sales Table
| SaleID | Date | CustomerID | ProductID | Quantity | Price | TotalAmount |
|--------|------|------------|------------|---------|------|------------|
| 101 | 2024-01-10 | 1 | 1001 | 2 | 500 | 1000 |
| 102 | 2024-01-11 | 2 | 1002 | 1 | 300 | 300 |
| 103 | 2024-01-12 | 1 | 1003 | 5 | 200 | 1000 |
Tasks:
✔️ Find total sales per customer
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM Sales
GROUP BY CustomerID
ORDER BY TotalSpent DESC;SELECT ProductID, SUM(TotalAmount) AS Revenue
FROM Sales
GROUP BY ProductID
ORDER BY Revenue DESC
LIMIT 5;SELECT DATE_FORMAT(Date, '%Y-%m') AS Month, SUM(TotalAmount) AS Revenue
FROM Sales
GROUP BY Month
ORDER BY Month;SELECT DISTINCT CustomerID
FROM Sales
WHERE DATE_FORMAT(Date, '%Y-%m') = '2024-01'
AND CustomerID NOT IN (
SELECT DISTINCT CustomerID FROM Sales
WHERE DATE_FORMAT(Date, '%Y-%m') = '2024-02'
);SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;SELECT Name, JoinDate
FROM Employees
WHERE JoinDate <= DATE_SUB(CURDATE(), INTERVAL 3 YEAR);DELIMITER //
CREATE PROCEDURE UpdateSalaries()
BEGIN
UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';
END //
DELIMITER ;9 056
❌ Bad Practice: Using Large Data Types
CREATE TABLE Users (
UserID BIGINT,
Name VARCHAR(500),
Age INT
);CREATE TABLE Users (
UserID INT,
Name VARCHAR(100),
Age TINYINT
);CREATE TABLE Sales (
SaleID INT,
SaleDate DATE,
Amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(SaleDate)); EXPLAIN SELECT * FROM Employees WHERE Name = 'John Doe';9 056
Day 29: Query Performance Tuning – Optimize Your SQL Queries 🚀
Welcome to Day 29 of your SQL journey! Today, we’ll cover:
✔️ How to optimize SQL queries for faster execution.
✔️ Common performance tuning techniques to improve efficiency.
✔️ Best practices for writing optimized SQL queries.
By the end of this lesson, you’ll be able to write SQL queries that run faster and handle large datasets efficiently!
---
🔹 Why is Query Performance Tuning Important?
When working with databases, slow queries can affect application performance. Optimizing queries helps:
✔️ Reduce execution time ⏳
✔️ Handle large amounts of data efficiently 📊
✔️ Improve database performance 🚀
---
🔹 1. Use SELECT Only What You Need
❌ Bad Query: Selecting All Columns
SELECT * FROM Employees;SELECT EmployeeID, Name, Salary FROM Employees;CREATE INDEX idx_employee_name ON Employees(Name);SELECT * FROM Employees WHERE Name = 'John Doe';SELECT * FROM Employees WHERE LOWER(Name) = 'john doe';SELECT * FROM Employees WHERE Name = 'John Doe';SELECT Orders.OrderID, Customers.CustomerName
FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID;SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;SELECT * FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Salaries WHERE Salary > 50000);SELECT * FROM Employees
WHERE EXISTS (SELECT 1 FROM Salaries WHERE Salaries.EmployeeID = Employees.EmployeeID AND Salary > 50000);CREATE INDEX idx_salary ON Employees(Salary);SELECT * FROM Employees ORDER BY Salary;SELECT * FROM Employees LIMIT 10; -- MySQL, PostgreSQL
SELECT TOP 10 * FROM Employees; -- SQL Server9 056
💡 What’s Next?
Tomorrow, we will focus on Query Performance Tuning & SQL Optimization Techniques! 🚀
💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊
9 056
Day 28: Integrating SQL with Other Tools & SQL in Big Data (NoSQL) 🚀
Welcome to Day 28 of your SQL journey! Today, we will cover:
✔️ How SQL integrates with other tools like Python, Power BI, and Tableau.
✔️ SQL in Big Data – Introduction to NoSQL databases.
Let’s break everything down step by step! 👇
---
🔹 Part 1: Integrating SQL with Other Tools
SQL is often used in combination with other tools for data analysis, reporting, and visualization.
🔹 Why Integrate SQL with Other Tools?
✔️ Automate Data Extraction 📥
✔️ Analyze Data in Python 🐍
✔️ Visualize Data in Power BI & Tableau 📊
---
🔹 SQL with Python
Python is widely used for data analysis, machine learning, and automation. To connect Python with SQL, we use libraries like:
✔️ sqlite3 – For SQLite databases
✔️ pyodbc – For MS SQL Server
✔️ mysql-connector-python – For MySQL
✔️ psycopg2 – For PostgreSQL
🔹 Connecting Python to SQL (Example: MySQL)
1️⃣ Install MySQL Connector
pip install mysql-connector-pythonimport mysql.connector
# Connect to database
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="company"
)
cursor = conn.cursor()
# Execute a query
cursor.execute("SELECT * FROM Employees")
# Fetch and display data
for row in cursor.fetchall():
print(row)
# Close the connection
conn.close()9 056
🔹 How it Works?
✔️ When a new customer is added, an entry is automatically made in CustomerLog.
---
🔹 Creating an AFTER UPDATE Trigger
Let’s create a trigger to track salary changes in an Employees table.
CREATE TRIGGER trg_AfterSalaryUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
IF UPDATE(Salary)
BEGIN
INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
SELECT i.ID, d.Salary, i.Salary, GETDATE()
FROM INSERTED i
JOIN DELETED d ON i.ID = d.ID;
END
END;CREATE TRIGGER trg_InsteadOfDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
UPDATE Employees
SET IsActive = 0
WHERE ID IN (SELECT ID FROM DELETED);
END;9 056
Day 27: Writing Stored Procedures and Functions & Automating Processes with Triggers 🚀
Welcome to Day 27 of your SQL journey! Today, we will learn:
✔️ Stored Procedures – Predefined SQL code that can be executed multiple times.
✔️ Functions – SQL code that returns a single value or table.
✔️ Triggers – Automated actions that execute when a certain event occurs.
Let’s break these down step by step! 👇
---
🔹 Part 1: Writing Stored Procedures
✅ What is a Stored Procedure?
A Stored Procedure is a set of SQL statements stored in the database that can be executed whenever needed.
🔹 Why Use Stored Procedures?
✔️ Reusability – Write once, use multiple times.
✔️ Security – Prevent SQL injection.
✔️ Performance – Optimized query execution.
---
🔹 Creating a Simple Stored Procedure
Let’s create a stored procedure to retrieve all customers from a Customers table.
CREATE PROCEDURE GetAllCustomers
AS
BEGIN
SELECT * FROM Customers;
END;EXEC GetAllCustomers;CREATE PROCEDURE GetCustomersByCountry
@Country NVARCHAR(50)
AS
BEGIN
SELECT * FROM Customers WHERE Country = @Country;
END;EXEC GetCustomersByCountry 'USA';CREATE PROCEDURE GetTotalCustomers
@Total INT OUTPUT
AS
BEGIN
SELECT @Total = COUNT(*) FROM Customers;
END;DECLARE @TotalCustomers INT;
EXEC GetTotalCustomers @TotalCustomers OUTPUT;
PRINT @TotalCustomers;CREATE FUNCTION CalculateTax(@Price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Price * 1.10; -- Adding 10% tax
END;SELECT dbo.CalculateTax(100) AS PriceWithTax;CREATE FUNCTION GetCustomersFromCountry(@Country NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Customers WHERE Country = @Country
);SELECT * FROM dbo.GetCustomersFromCountry('USA');CREATE TABLE CustomerLog (
LogID INT IDENTITY PRIMARY KEY,
CustomerID INT,
ActionTaken NVARCHAR(50),
ActionDate DATETIME DEFAULT GETDATE()
);CREATE TRIGGER trg_AfterCustomerInsert
ON Customers
AFTER INSERT
AS
BEGIN
INSERT INTO CustomerLog (CustomerID, ActionTaken)
SELECT ID, 'Inserted' FROM INSERTED;
END;9 056
CREATE TABLE Orders (
ID INT PRIMARY KEY,
OrderDetails XML
);INSERT INTO Orders (ID, OrderDetails)
VALUES (1, '<Order><Product>Laptop</Product><Price>1000</Price></Order>');.value() function:
SELECT OrderDetails.value('(/Order/Product)[1]', 'NVARCHAR(50)') AS Product
FROM Orders;SELECT ID, Name FROM Customers
FOR JSON AUTO;SELECT ID, Name FROM Customers
FOR XML AUTO;9 056
Day 26: Pivoting and Unpivoting Data & Working with JSON and XML in SQL 🚀
Welcome to Day 26 of your SQL learning journey! Today, we will cover two important advanced topics:
✔️ Pivoting and Unpivoting Data – How to transform rows into columns and vice versa.
✔️ Working with JSON and XML in SQL – How to store, query, and manipulate structured data formats.
---
🔹 Part 1: Pivoting and Unpivoting Data in SQL
In many real-world scenarios, we need to reshape data for better analysis and reporting.
👉 Pivoting: Converting rows into columns (Summarizing data in a more readable format).
👉 Unpivoting: Converting columns back into rows (Making data easier to process).
---
🔹 Understanding Pivoting with an Example
Imagine we have a Sales Table like this:
| SalesPerson | Month | SalesAmount |
|------------|--------|------------|
| John | Jan | 1000 |
| John | Feb | 1200 |
| Jane | Jan | 1500 |
| Jane | Feb | 1300 |
Goal: Convert it into this format using PIVOT:
| SalesPerson | Jan | Feb |
|------------|------|------|
| John | 1000 | 1200 |
| Jane | 1500 | 1300 |
---
✅ How to Use PIVOT in SQL?
SELECT SalesPerson, [Jan], [Feb]
FROM
(
SELECT SalesPerson, Month, SalesAmount
FROM Sales
) AS SourceTable
PIVOT
(
SUM(SalesAmount)
FOR Month IN ([Jan], [Feb])
) AS PivotTable;SELECT SalesPerson, Month, SalesAmount
FROM
(
SELECT SalesPerson, [Jan], [Feb]
FROM SalesPivotTable
) AS PivotedTable
UNPIVOT
(
SalesAmount
FOR Month IN ([Jan], [Feb])
) AS UnpivotTable;NVARCHAR data type to store JSON.
CREATE TABLE Customers (
ID INT PRIMARY KEY,
Name NVARCHAR(50),
OrderDetails NVARCHAR(MAX) -- Stores JSON data
);INSERT INTO Customers (ID, Name, OrderDetails)
VALUES (1, 'John', '{"Product": "Laptop", "Price": 1000, "Quantity": 2}');JSON_VALUE() function:
SELECT Name, JSON_VALUE(OrderDetails, '$.Product') AS Product
FROM Customers;$.Product extracts the Product value from JSON.
---
🔹 Parsing Complex JSON with OPENJSON
If JSON contains nested arrays, OPENJSON helps extract data into tabular format.
SELECT *
FROM OPENJSON('[
{"Product": "Laptop", "Price": 1000},
{"Product": "Phone", "Price": 500}
]')
WITH (Product NVARCHAR(50), Price INT);9 056
✅ 3. Grant Read & Write Access
GRANT SELECT, INSERT, UPDATE, DELETE ON Customers TO dev_user;GRANT ALL PRIVILEGES ON Customers TO dev_user;REVOKE SELECT ON Customers FROM dev_user;DROP USER dev_user;
DROP LOGIN dev_user;db_owner, db_datareader, and db_datawriter in SQL Server.
✔️ Regularly review user access to ensure security.
✔️ Use stored procedures instead of granting direct access to tables.
---
🔹 Summary of Today's Topics
| Concept | Key Takeaways |
|------------|-------------------|
| Backups | Protect data from accidental loss and corruption. |
| Restore | Recovers database from full, differential, and transaction log backups. |
| User Permissions | Control database access for different users. |
| Best Practices | Automate backups, store in multiple locations, follow least privilege for security. |
---
🔹 Your Task for Today
✅ Take a full backup of a sample database.
✅ Restore the database from the backup.
✅ Create a new user and assign permissions.
✅ Practice granting and revoking permissions for better security.
---
💡 What’s Next?
Tomorrow, we will learn Pivoting & Unpivoting Data and Working with JSON & XML in SQL. Stay tuned! 🚀
💬 Comment below if you have questions! Like ❤️ & Share if you're enjoying this SQL series! 😊9 056
Day 25: Backup and Restore Strategies in SQL & Role-Based Permissions 🚀
Welcome to Day 25 of your SQL learning journey! Today, we’ll explore two important topics:
✔️ Backup and Restore Strategies – How to protect and recover your database.
✔️ Role-Based Permissions – How to manage user access in SQL.
---
🔹 Part 1: Backup and Restore Strategies in SQL
Imagine you are working on a critical database with customer information. What if:
🔴 The server crashes?
🔴 Someone accidentally deletes data?
🔴 A cyber-attack corrupts your data?
⚡️ Solution: Always take regular backups so you can restore data when needed!
---
🔹 Why Are Backups Important?
✔️ Data Protection – Prevents data loss due to failures.
✔️ Disaster Recovery – Restores data if something goes wrong.
✔️ Version Control – Helps retrieve past data when required.
---
🔹 Types of Backups in SQL
| Backup Type | Description |
|--------------|----------------|
| Full Backup | Copies the entire database (all tables, indexes, views, etc.). |
| Differential Backup | Saves only the changes made since the last full backup. |
| Transaction Log Backup | Captures all changes (INSERT, UPDATE, DELETE) since the last backup. |
---
🔹 How to Take a Backup in SQL?
✅ 1. Full Backup (Recommended for complete database protection)
BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT;.bak file.
✅ 2. Differential Backup (Stores only changes after the last full backup)
BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL;BACKUP LOG MyDatabase
TO DISK = 'C:\Backup\MyDatabase_Log.bak';RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE;RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH NORECOVERY;
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH RECOVERY;RESTORE LOG MyDatabase
FROM DISK = 'C:\Backup\MyDatabase_Log.bak'
WITH RECOVERY;CREATE LOGIN dev_user WITH PASSWORD = 'StrongPassword123';
CREATE USER dev_user FOR LOGIN dev_user;GRANT SELECT ON Customers TO dev_user;