📦 Subqueries & Nested Queries

A subquery is a query inside another query. You can use it in SELECT, FROM, or WHERE clauses to solve complex problems step-by-step.

1. Subquery in WHERE Clause
Use this when you need to filter results based on another query.

SELECT name
FROM employees
WHERE department_id = (
SELECT id FROM departments WHERE name = 'Sales'
);

This finds all employees who work in the Sales department.

2. Subquery in SELECT Clause
This lets you fetch calculated or related values for each row.

SELECT name,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Shows each employee’s name along with the company’s average salary.

3. Subquery in FROM Clause (Inline View)
Used when you want to treat the subquery like a temporary table.

SELECT department, total
FROM (
SELECT department, SUM(salary) AS total
FROM employees
GROUP BY department
) AS dept_summary;

This groups salaries by department in a subquery, then fetches from it.

Important:

- Always alias your subqueries (especially in the FROM clause).

- Avoid correlated subqueries if possible; they’re slower.


React with ❤️ if you want me to cover the next topic: 🏷 Aliases & Case Statements.

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

🏷 Aliases & CASE Statements

1. Aliases (AS keyword)
Aliases let you rename columns or tables temporarily to make your output cleaner or more readable.

Column Alias Example:

SELECT first_name AS name, salary AS monthly_income
FROM employees;

You’ll see name and monthly_income as column headers instead of raw column names.

Table Alias Example:

SELECT e.name, d.name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id;

Here e and d are shortcuts for table names, making complex queries more readable.

2. CASE Statements
CASE is SQL’s way of doing if-else logic inside queries.

Example 1: Categorizing Data

SELECT name,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;

This assigns each employee a salary category.

Example 2: Conditional Aggregation

SELECT department,
COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male_count,
COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS female_count
FROM employees
GROUP BY department;

Counts males and females per department.

Use aliases to simplify your SQL, and CASE when you need decision-making logic in queries.

React with ❤️ if you're excited for the next topic :🧾 Views & Indexes

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Many people still aren't fully utilizing the power of Telegram.

There are numerous channels on Telegram that can help you find the latest job and internship opportunities?

Here are some of my top channel recommendations to help you get started 👇👇

Latest Jobs & Internships: https://news.1rj.ru/str/getjobss

Jobs Preparation Resources:
https://news.1rj.ru/str/jobinterviewsprep

Data Science Jobs:
https://news.1rj.ru/str/datasciencej

Interview Tips:
https://news.1rj.ru/str/Interview_Jobs

Data Analyst Jobs:
https://news.1rj.ru/str/jobs_SQL

AI Jobs:
https://news.1rj.ru/str/AIjobz

Remote Jobs:
https://news.1rj.ru/str/jobs_us_uk

FAANG Jobs:
https://news.1rj.ru/str/FAANGJob

Software Developer Jobs: https://news.1rj.ru/str/internshiptojobs

If you found this helpful, don’t forget to like, share, and follow for more resources that can boost your career journey!

Let me know if you know any other useful telegram channel

ENJOY LEARNING👍👍

Now, let’s dive into a couple of powerful but often overlooked SQL features:

🧾 Views & Indexes (Basics)

1. Views
A View is a virtual table based on a SQL query. It doesn’t store data itself — it just stores the query logic.

Why use Views?

- Simplifies complex queries

- Improves code reusability

- Adds a layer of security by hiding certain columns


Creating a View:

CREATE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 80000;

Using the View:

SELECT * FROM high_salary_employees;

Updating a View:

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT name, salary, department
FROM employees
WHERE salary > 80000;

2. Indexes
An Index is like a book’s table of contents — it helps the database find data faster, especially in large tables.

Why use Indexes?

- Speeds up SELECT queries

- Great for columns used in WHERE, JOIN, and ORDER BY


Creating an Index:

CREATE INDEX idx_employee_name ON employees(name);

Indexes can slow down INSERT, UPDATE, DELETE because they need to update the index too.

Don’t overuse them — only on frequently searched or joined columns.


React with ❤️ if you’re ready for the next interesting concept: 🧠 Common Table Expressions (CTEs).

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Here comes one of the cleanest and most powerful features in SQL:

🧠 Common Table Expressions (CTEs)

A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It makes your queries more readable and modular, especially when dealing with complex logic.

Syntax:

WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;

Example: Let’s say we want to get the top 3 highest-paid employees from each department.

WITH ranked_employees AS (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
)
SELECT *
FROM ranked_employees
WHERE rank <= 3;

Why to use CTEs:

- Easier to break down complex queries

- You can use them multiple times in the main query

- Readable and cleaner than subqueries


You can chain multiple CTEs together and even write recursive CTEs for hierarchical data.

React with ❤️ if you're excited for the next one: 🔄 Window Functions (ROW_NUMBER, RANK, PARTITION BY).

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

🔄 Window Functions (ROW_NUMBER, RANK, PARTITION BY)

Window functions perform calculations across rows related to the current row — but unlike GROUP BY, they don’t collapse your data!

They are super useful for running totals, rankings, and finding duplicates.


1. ROW_NUMBER()
Gives a unique number to each row within a partition of a result set.

SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Ranks employees by salary within each department.

2. RANK() vs DENSE_RANK()

RANK() leaves gaps after ties.

DENSE_RANK() doesn’t.


SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;


3. PARTITION BY
It’s like a GROUP BY, but for window functions.

SELECT department, name, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

This shows each employee's salary alongside the average salary of their department — without collapsing the rows.


Other Useful Window Functions:

NTILE(n) – Divides rows into n buckets

LAG() / LEAD() – Look at previous/next row’s value

SUM() / AVG() over a window – for running totals


React with ❤️ if you're pumped for the next one: ⚙️ Data Manipulation (INSERT, UPDATE, DELETE).

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Let’s now cover a hands-on and frequently used part of SQL:

⚙️ Data Manipulation (INSERT, UPDATE, DELETE)

Data Manipulation Language (DML) commands are used to add, modify, or remove data from your tables.


1. INSERT – Add new records to a table.

INSERT INTO employees (name, department, salary)
VALUES ('John Doe', 'HR', 60000);

Multiple rows:

INSERT INTO employees (name, department, salary)
VALUES
('Alice', 'IT', 70000),
('Bob', 'Finance', 65000);


2. UPDATE – Modify existing records.

UPDATE employees
SET salary = 75000
WHERE name = 'John Doe';

With multiple fields:

UPDATE employees
SET salary = 80000, department = 'IT'
WHERE id = 101;

Always use WHERE in UPDATE to avoid accidental mass updates.


3. DELETE – Remove records from a table.

DELETE FROM employees
WHERE department = 'Temporary';

Again, make sure to use WHERE — or you’ll delete all rows!


Pro Tips:

- Test your WHERE clause with a SELECT first.

- Use BEGIN TRANSACTION and ROLLBACK if supported — for safety.


React with ❤️ if you're ready to learn how to create and structure your database with: 🧱 Data Definition (CREATE, ALTER, DROP).

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

9 tips to master Power BI for Data Analysis:

📥 Learn to import data from various sources

🧹 Clean and transform data using Power Query

🧠 Understand relationships between tables using the data model

🧾 Write DAX formulas for calculated columns and measures

📊 Create interactive visuals: bar charts, slicers, maps, etc.

🎯 Use filters, slicers, and drill-through for deeper insights

📈 Build dashboards that tell a clear data story

🔄 Refresh and schedule your reports automatically

📚 Explore Power BI community and documentation for new tricks

Power BI Free Resources: https://news.1rj.ru/str/PowerBI_analyst

Hope it helps :)

#powerbi

Let’s move on to the backbone of any SQL database:

🧱 Data Definition (CREATE, ALTER, DROP)

Data Definition Language (DDL) is used to define and manage database structures like tables, columns, and schemas.


1. CREATE – Used to create new tables, databases, or other objects.

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

You can also create other things like databases, indexes, or views:

CREATE DATABASE company_db;


2. ALTER – Modify an existing table’s structure.

Add a column:

ALTER TABLE employees
ADD date_of_joining DATE;

Modify column data type:

ALTER TABLE employees
ALTER COLUMN salary TYPE FLOAT;

Drop a column:

ALTER TABLE employees
DROP COLUMN date_of_joining;


3. DROP – Permanently delete a table, view, or database.

DROP TABLE employees;

Caution: This deletes everything — structure and data. Use with care!


Bonus: TRUNCATE

TRUNCATE TABLE employees;

Deletes all data from the table but keeps the structure intact. It's faster than DELETE but not recoverable.


React with ❤️ if you're ready for the next one: 🔐 Constraints & Relationships (PK, FK, UNIQUE, CHECK).

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

🔐 Constraints & Relationships (PK, FK, UNIQUE, CHECK)

Constraints are rules applied to columns to ensure valid and consistent data in your tables.


1. PRIMARY KEY (PK)
Uniquely identifies each row in a table.

Only one per table

Cannot be NULL

Often applied to an id column


CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);


2. FOREIGN KEY (FK)
Establishes a relationship between tables.
It links a column to the PRIMARY KEY of another table.

CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Now, employees.dept_id must match a valid departments.dept_id.


3. UNIQUE
Ensures that all values in a column are different (can have one NULL if not restricted).

CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);


4. CHECK
Ensures a condition is true for data being inserted or updated.

CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10, 2),
CHECK (price > 0)
);


5. NOT NULL
Prevents NULL values in a column.

CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL
);

Using constraints helps keep your data clean, accurate, and relational.

React with ❤️ if you're ready for the final (and most practical) chapter: 🧪 Real-world SQL Scenarios & Challenges.

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Most of you responded with first option PRIMARY KEY

Yes, a PRIMARY KEY does enforce: Uniqueness & NOT NULL


So technically, it's correct — making email a primary key will satisfy the condition that emails must be unique & not null


But... here’s the catch:

In real-world database design, email is rarely used as a primary key, because:

- It can change (users may update emails).

- It’s not an ideal unique identifier like a user_id (which is usually an auto-incrementing integer).

Using email as PK can make foreign key relationships messy and inefficient, so we'll have to explicitly use UNIQUE & NOT NULL constraints

Hope it clear most of the doubts :)

🧪 Real-world SQL Scenarios & Challenges

Let’s dive into the types of real-world problems you’ll encounter as a data analyst, data scientist , data engineer, or developer.


1. Finding Duplicates

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Perfect for data cleaning and validation tasks.


2. Get the Second Highest Salary

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);


3. Running Totals

SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

Essential in dashboards and financial reports.


4. Customers with No Orders

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Very common in e-commerce or CRM platforms.


5. Monthly Aggregates

SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS total_orders
FROM orders
GROUP BY month
ORDER BY month;

Great for trends and time-based reporting.


6. Pivot-like Output (Using CASE)

SELECT
department,
COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male_count,
COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS female_count
FROM employees
GROUP BY department;

Super useful for dashboards and insights.


7. Recursive Queries (Org Hierarchy or Tree)

WITH RECURSIVE employee_tree AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;

Used in advanced data modeling and tree structures.


You don’t just need to know how SQL works — you need to know when to use it smartly!

React with ❤️ if you’d like me to explain more data analytics topics

Share with credits: https://news.1rj.ru/str/sqlspecialist

SQL Roadmap: https://news.1rj.ru/str/sqlspecialist/1340

Hope it helps :)