Top 50 C++ Keywords & Functions

#CPP #Basics #IO

#1. #include <iostream>
A preprocessor directive that includes the input/output stream library.

#include <iostream>

int main() {
    std::cout << "This requires iostream!";
    return 0;
}

This requires iostream!

#2. int main()
The main function where program execution begins.

#include <iostream>

int main() {
    std::cout << "Program starts here.";
    return 0;
}

Program starts here.

#3. std::cout
Used to output data (print to the console).

#include <iostream>

int main() {
    std::cout << "Hello, C++!";
    return 0;
}

Hello, C++!

#4. std::cin
Used to get input from the user.

#include <iostream>
#include <string>

int main() {
    int age;
    std::cout << "Enter your age: ";
    std::cin >> age;
    std::cout << "You are " << age << " years old.";
    return 0;
}

Enter your age: 25
You are 25 years old.

#5. using namespace std;
Tells the compiler to use the std (standard) namespace. Avoids prefixing std:: to every standard function.

#include <iostream>
using namespace std;

int main() {
    cout << "No std:: prefix needed.";
    return 0;
}

No std:: prefix needed.

---
#CPP #DataTypes #Variables

#6. int
Declares an integer variable.

#include <iostream>

int main() {
    int number = 100;
    std::cout << "The number is: " << number;
    return 0;
}

The number is: 100

#7. double
Declares a floating-point number variable (can hold decimals).

#include <iostream>

int main() {
    double price = 19.99;
    std::cout << "The price is: " << price;
    return 0;
}

The price is: 19.99

#8. char
Declares a character variable.

#include <iostream>

int main() {
    char grade = 'A';
    std::cout << "Your grade is: " << grade;
    return 0;
}

Your grade is: A

#9. bool
Declares a boolean variable, which can only have the value true or false.

#include <iostream>

int main() {
    bool isRaining = false;
    std::cout << "Is it raining? " << isRaining; // Outputs 0 for false
    return 0;
}

Is it raining? 0

#10. std::string
Declares a variable that can hold a sequence of characters. Requires #include <string>.

#include <iostream>
#include <string>

int main() {
    std::string greeting = "Hello, World!";
    std::cout << greeting;
    return 0;
}

Hello, World!

---
#CPP #Keywords #Operators

#11. const
Declares a variable as a constant, meaning its value cannot be changed.

#include <iostream>

int main() {
    const double PI = 3.14159;
    std::cout << "The value of PI is: " << PI;
    // PI = 4; // This would cause a compile error
    return 0;
}

The value of PI is: 3.14159

#12. sizeof()
An operator that returns the size (in bytes) of a data type or variable.

#include <iostream>

int main() {
    int myInt;
    std::cout << "Size of int is: " << sizeof(myInt) << " bytes.";
    return 0;
}

Size of int is: 4 bytes.

#13. auto
A keyword that lets the compiler automatically deduce the data type of a variable at compile-time.

#include <iostream>

int main() {
    auto number = 10; // Compiler deduces int
    auto pi = 3.14;   // Compiler deduces double
    std::cout << "Type of 'number' is deduced.";
    return 0;
}

Type of 'number' is deduced.

#14. & (Address-of Operator)
Returns the memory address of a variable.

#include <iostream>

int main() {
    int var = 20;
    std::cout << "Memory address of var: " << &var;
    return 0;
}

Memory address of var: 0x61ff08 
(Note: Address will vary)

#15. * (Dereference Operator)
Accesses the value stored at a memory address held by a pointer.

#include <iostream>

int main() {
    int var = 50;
    int* ptr = &var; // ptr holds the address of var
    std::cout << "Value at address " << ptr << " is " << *ptr;
    return 0;
}

Value at address 0x61ff04 is 50
(Note: Address will vary)

---
#CPP #ControlFlow #Conditional

#16. if
Executes a block of code if a specified condition is true.

#include <iostream>

int main() {
    int age = 18;
    if (age >= 18) {
        std::cout << "You are an adult.";
    }
    return 0;
}

You are an adult.

#17. else
Executes a block of code if the condition in the if statement is false.

#include <iostream>

int main() {
    int age = 16;
    if (age >= 18) {
        std::cout << "You are an adult.";
    } else {
        std::cout << "You are not an adult.";
    }
    return 0;
}

You are not an adult.

#18. else if
Specifies a new condition to test, if the first if condition is false.

#include <iostream>

int main() {
    int score = 85;
    if (score >= 90) {
        std::cout << "Grade: A";
    } else if (score >= 80) {
        std::cout << "Grade: B";
    } else {
        std::cout << "Grade: C";
    }
    return 0;
}

Grade: B

#19. switch / case
Selects one of many code blocks to be executed.

#include <iostream>

int main() {
    int day = 3;
    switch (day) {
        case 1:
            std::cout << "Monday";
            break;
        case 2:
            std::cout << "Tuesday";
            break;
        case 3:
            std::cout << "Wednesday";
            break;
    }
    return 0;
}

Wednesday

#20. break
Used to exit a switch statement or a loop.

#include <iostream>

int main() {
    for (int i = 0; i < 10; ++i) {
        if (i == 5) {
            break; // Exit the loop when i is 5
        }
        std::cout << i << " ";
    }
    return 0;
}

0 1 2 3 4

---
#CPP #Loops

#21. for
Executes a block of code a specified number of times.

#include <iostream>

int main() {
    for (int i = 0; i < 5; ++i) {
        std::cout << i << " ";
    }
    return 0;
}

0 1 2 3 4

#22. while
Loops through a block of code as long as a specified condition is true.

#include <iostream>

int main() {
    int i = 0;
    while (i < 5) {
        std::cout << i << " ";
        i++;
    }
    return 0;
}

0 1 2 3 4

#23. do-while
Similar to a while loop, but the code block is executed at least once before the condition is tested.

#include <iostream>

int main() {
    int i = 5;
    do {
        std::cout << "This will run once.";
        i++;
    } while (i < 5);
    return 0;
}

This will run once.

#24. Range-based for loop
Iterates over all elements in a range, such as an array or vector, without using an index.

#include <iostream>
#include <vector>

int main() {
    int numbers[] = {10, 20, 30};
    for (int num : numbers) {
        std::cout << num << " ";
    }
    return 0;
}

10 20 30

#25. continue
Skips the current iteration of a loop and continues with the next iteration.

#include <iostream>

int main() {
    for (int i = 0; i < 5; ++i) {
        if (i == 2) {
            continue; // Skip printing 2
        }
        std::cout << i << " ";
    }
    return 0;
}

0 1 3 4

---
#CPP #Functions

#26. Function Declaration (Prototype)
Declares a function's name, return type, and parameters, allowing it to be used before it's defined.

#include <iostream>

void sayHello(); // Function declaration

int main() {
    sayHello(); // Call the function
    return 0;
}

void sayHello() { // Function definition
    std::cout << "Hello from function!";
}

Hello from function!

#27. void
A keyword specifying that a function does not return any value.

#include <iostream>

void printMessage(std::string message) {
    std::cout << message;
}

int main() {
    printMessage("This function returns nothing.");
    return 0;
}

This function returns nothing.

#28. return
Terminates a function and can return a value to the caller.

#include <iostream>

int add(int a, int b) {
    return a + b; // Return the sum
}

int main() {
    int result = add(5, 3);
    std::cout << "Result: " << result;
    return 0;
}

Result: 8

#29. #include <cmath>
Includes the C++ math library for complex mathematical operations.

#include <iostream>
#include <cmath> // Include for sqrt()

int main() {
    double number = 25.0;
    std::cout << "Square root is: " << sqrt(number);
    return 0;
}

Square root is: 5

#30. pow()
A function from <cmath> that returns the base raised to the power of the exponent.

#include <iostream>
#include <cmath>

int main() {
    double result = pow(2, 3); // 2 to the power of 3
    std::cout << "2^3 is: " << result;
    return 0;
}

2^3 is: 8

---
#CPP #STL #Vector

#31. #include <vector>
Includes the library for std::vector, a dynamic array.

#include <iostream>
#include <vector>

int main() {
    std::vector<int> myVector;
    myVector.push_back(1);
    std::cout << "Vector is ready.";
    return 0;
}

Vector is ready.

#32. std::vector
A sequence container that encapsulates dynamic size arrays.

#include <iostream>
#include <vector>

int main() {
    std::vector<int> numbers = {10, 20, 30};
    std::cout << "First element: " << numbers[0];
    return 0;
}

First element: 10

#33. .push_back()
Member function of std::vector that adds an element to the end.

#include <iostream>
#include <vector>

int main() {
    std::vector<int> numbers;
    numbers.push_back(5);
    numbers.push_back(10);
    std::cout << "Vector size: " << numbers.size();
    return 0;
}

Vector size: 2

#34. .size()
Member function that returns the number of elements in a container like std::vector or std::string.

#include <iostream>
#include <vector>

int main() {
    std::vector<std::string> fruits = {"Apple", "Banana"};
    std::cout << "There are " << fruits.size() << " fruits.";
    return 0;
}

There are 2 fruits.

#35. .length()
A member function of std::string that returns its length. It's synonymous with .size().

#include <iostream>
#include <string>

int main() {
    std::string text = "C++";
    std::cout << "The length of the string is: " << text.length();
    return 0;
}

The length of the string is: 3

---
#CPP #STL #Algorithms

#36. #include <algorithm>
Includes the standard library algorithms, like sort, find, copy, etc.

#include <iostream>
#include <vector>
#include <algorithm> // Required for std::sort

int main() {
    std::vector<int> nums = {3, 1, 4};
    std::sort(nums.begin(), nums.end());
    std::cout << "Sorting is possible with <algorithm>.";
    return 0;
}

Sorting is possible with <algorithm>.

#37. std::sort()
Sorts the elements in a range (e.g., a vector).

#include <iostream>
#include <vector>
#include <algorithm>

int main() {
    std::vector<int> nums = {50, 20, 40, 10, 30};
    std::sort(nums.begin(), nums.end());
    for(int n : nums) {
        std::cout << n << " ";
    }
    return 0;
}

10 20 30 40 50

#38. .begin()
Returns an iterator pointing to the first element in a container.

#include <iostream>
#include <vector>

int main() {
    std::vector<int> nums = {100, 200, 300};
    auto it = nums.begin();
    std::cout << "First element: " << *it;
    return 0;
}

First element: 100

#39. .end()
Returns an iterator referring to the past-the-end element in the container.

#include <iostream>
#include <vector>
#include <algorithm>

int main() {
    std::vector<int> nums = {1, 2, 3};
    // .end() points after the last element, used as a boundary
    std::cout << "Vector has elements until the end.";
    return 0;
}

Vector has elements until the end.

#40. #define
A preprocessor directive used to create macros or symbolic constants.

#include <iostream>
#define PI 3.14159

int main() {
    std::cout << "The value of PI is " << PI;
    return 0;
}

The value of PI is 3.14159

---
#CPP #OOP #Classes

#41. class
A keyword used to declare a class, which is a blueprint for creating objects.

#include <iostream>

class Dog {
public:
    void bark() {
        std::cout << "Woof!";
    }
};

int main() {
    Dog myDog;
    myDog.bark();
    return 0;
}

Woof!

#42. struct
Similar to a class, but its members are public by default.

#include <iostream>

struct Point {
    int x;
    int y;
};

int main() {
    Point p;
    p.x = 10;
    p.y = 20;
    std::cout << "Point: (" << p.x << ", " << p.y << ")";
    return 0;
}

Point: (10, 20)

#43. public
An access specifier that makes class members accessible from outside the class.

#include <iostream>

class MyClass {
public: // Accessible from anywhere
    int myNum = 10;
};

int main() {
    MyClass obj;
    std::cout << obj.myNum;
    return 0;
}

10

#44. private
An access specifier that makes class members accessible only from within the class itself.

#include <iostream>

class MyClass {
private:
    int secret = 42;
public:
    void printSecret() {
        std::cout << secret; // Accessible from within the class
    }
};

int main() {
    MyClass obj;
    // std::cout << obj.secret; // This would cause a compile error
    obj.printSecret();
    return 0;
}

42

#45. Constructor
A special member function of a class that is executed whenever a new object of that class is created.

#include <iostream>

class Car {
public:
    // Constructor
    Car() {
        std::cout << "Car object created.";
    }
};

int main() {
    Car myCar; // Constructor is called here
    return 0;
}

Car object created.

---
#CPP #OOP #MemoryManagement

#46. Destructor
A special member function that is executed automatically when an object is destroyed.

#include <iostream>

class MyClass {
public:
    // Destructor
    ~MyClass() {
        std::cout << "Object destroyed.";
    }
};

int main() {
    MyClass obj;
    // Destructor is called when main() ends
    return 0;
}

Object destroyed.

#47. this
A keyword that refers to the current instance of the class.

#include <iostream>

class Box {
private:
    int length;
public:
    Box(int length) {
        this->length = length; // Use 'this' to distinguish member from parameter
    }
    void printLength() {
        std::cout << "Length: " << this->length;
    }
};

int main() {
    Box b(10);
    b.printLength();
    return 0;
}

Length: 10

#48. new
An operator that allocates memory on the heap and returns a pointer to it.

#include <iostream>

int main() {
    int* ptr = new int; // Allocate an integer on the heap
    *ptr = 100;
    std::cout << "Value from heap: " << *ptr;
    delete ptr; // Must deallocate memory
    return 0;
}

Value from heap: 100

#49. delete
An operator that deallocates memory previously allocated with new.

#include <iostream>

int main() {
    int* ptr = new int(55);
    std::cout << *ptr << " allocated. ";
    delete ptr; // Deallocate the memory
    std::cout << "Memory freed.";
    // Accessing ptr now is undefined behavior
    return 0;
}

55 allocated. Memory freed.

#50. nullptr
Represents a null pointer literal. It indicates that a pointer does not point to any valid memory location.

#include <iostream>

int main() {
    int* ptr = nullptr;
    if (ptr == nullptr) {
        std::cout << "The pointer is null.";
    }
    return 0;
}

The pointer is null.

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

✅Python Interview Questions with Answers 🧑‍💻👩‍💻

1️⃣ Write a function to remove outliers from a list using IQR.

import numpy as np

def remove_outliers(data):
    q1 = np.percentile(data, 25)
    q3 = np.percentile(data, 75)
    iqr = q3 - q1
    lower = q1 - 1.5 * iqr
    upper = q3 + 1.5 * iqr
    return [x for x in data if lower <= x <= upper]

2️⃣ Convert a nested list to a flat list.

nested = [[1, 2], [3, 4],]
flat = [item for sublist in nested for item in sublist]

3️⃣ Read a CSV file and count rows with nulls.

import pandas as pd

df = pd.read_csv('data.csv')
null_rows = df.isnull().any(axis=1).sum()
print("Rows with nulls:", null_rows)

4️⃣ How do you handle missing data in pandas?
⦁ Drop missing rows: df.dropna()
⦁ Fill missing values: df.fillna(value)
⦁ Check missing data: df.isnull().sum()

5️⃣ Explain the difference between loc[] and iloc[].
⦁ loc[]: Label-based indexing (e.g., row/column names) 
  Example: df.loc[0, 'Name']
⦁ iloc[]: Position-based indexing (e.g., row/column numbers) 
  Example: df.iloc

💬 Tap ❤️ for more!

Top 100 SQL Interview Questions & Answers

#SQL #InterviewQuestions #DataAnalysis #Database #SQLQueries

Part 1: Basic Queries & DML/DDL (Q1-20)

#1. Select all columns and rows from a table named products.
A: Use SELECT * to retrieve all columns.

SELECT *
FROM products;

Output: All data from the 'products' table.

#2. Select only the product_name and price columns from the products table.
A: List the desired column names.

SELECT product_name, price
FROM products;

Output: A table with two columns: 'product_name' and 'price'.

#3. Find all products with a price greater than 50.
A: Use the WHERE clause with a comparison operator.

SELECT product_name, price
FROM products
WHERE price > 50;

Output: Products whose price is more than 50.

#4. Find all products that are red or have a price less than 20.
A: Use WHERE with OR to combine conditions.

SELECT product_name, color, price
FROM products
WHERE color = 'Red' OR price < 20;

Output: Products that are red OR cheaper than 20.

#5. Find all products that are red AND have a price greater than 100.
A: Use WHERE with AND to combine conditions.

SELECT product_name, color, price
FROM products
WHERE color = 'Red' AND price > 100;

Output: Products that are red AND more expensive than 100.

#6. Select all unique category values from the products table.
A: Use the DISTINCT keyword.

SELECT DISTINCT category
FROM products;

Output: A list of unique categories (e.g., 'Electronics', 'Books').

#7. Count the total number of products in the products table.
A: Use the COUNT(*) aggregate function.

SELECT COUNT(*) AS total_products
FROM products;

Output: A single number representing the total count.

#8. Find the average price of all products.
A: Use the AVG() aggregate function.

SELECT AVG(price) AS average_price
FROM products;

Output: A single number representing the average price.

#9. Find the highest and lowest price among all products.
A: Use the MAX() and MIN() aggregate functions.

SELECT MAX(price) AS highest_price, MIN(price) AS lowest_price
FROM products;

Output: Two numbers: the maximum and minimum price.

#10. Sort products by price in ascending order.
A: Use ORDER BY with ASC (or omit ASC as it's the default).

SELECT product_name, price
FROM products
ORDER BY price ASC;

Output: Products listed from cheapest to most expensive.

#11. Sort products by price in descending order.
A: Use ORDER BY with DESC.

SELECT product_name, price
FROM products
ORDER BY price DESC;

Output: Products listed from most expensive to cheapest.

#12. Sort products by category (ascending), then by price (descending).
A: Specify multiple columns in ORDER BY.

SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Output: Products grouped by category, then sorted by price within each category.

#13. Get the first 10 products when ordered by product_id.
A: Use LIMIT (MySQL/PostgreSQL) or TOP (SQL Server).

-- For MySQL/PostgreSQL
SELECT *
FROM products
ORDER BY product_id
LIMIT 10;

-- For SQL Server
SELECT TOP 10 *
FROM products
ORDER BY product_id;

Output: The first 10 products by their ID.

#14. Update the price of a product named 'Laptop' to 1200.
A: Use the UPDATE statement with a WHERE clause.

UPDATE products
SET price = 1200
WHERE product_name = 'Laptop';

Output: The 'Laptop' product's price is updated in the table.

#15. Insert a new product: ('TV', 'Electronics', 800, 'Black').
A: Use the INSERT INTO statement.

INSERT INTO products (product_name, category, price, color)
VALUES ('TV', 'Electronics', 800, 'Black');

Output: A new row is added to the 'products' table.

#16. Delete all products with a category of 'Clothing'.
A: Use the DELETE FROM statement with a WHERE clause.

DELETE FROM products
WHERE category = 'Clothing';

Output: All products in the 'Clothing' category are removed.

#17. Delete all rows from the products table without logging individual row deletions.
A: Use TRUNCATE TABLE. This is a DDL command.

TRUNCATE TABLE products;

Output: All rows from 'products' are removed, table structure remains.

#18. Remove the entire products table from the database.
A: Use DROP TABLE. This is a DDL command.

DROP TABLE products;

Output: The 'products' table and its data are permanently deleted.

#19. Add a new column stock_quantity of type INT to the products table.
A: Use the ALTER TABLE statement.

ALTER TABLE products
ADD COLUMN stock_quantity INT;

Output: The 'products' table now has a new 'stock_quantity' column.

#20. Rename the column color to product_color in the products table.
A: Use ALTER TABLE with RENAME COLUMN (PostgreSQL) or CHANGE COLUMN (MySQL) or RENAME COLUMN (SQL Server).

-- For PostgreSQL
ALTER TABLE products
RENAME COLUMN color TO product_color;

-- For MySQL
ALTER TABLE products
CHANGE COLUMN color product_color VARCHAR(50); -- Must specify type again

Output: The 'color' column is renamed to 'product_color'.

---
Part 2: Joins (Q21-40)

#21. What is the default type of JOIN in SQL?
A: The default JOIN type is INNER JOIN. If you just write JOIN, it's equivalent to INNER JOIN.

#22. Explain INNER JOIN and give an example.
A: INNER JOIN returns only the rows that have matching values in both tables. Rows that do not have a match in both tables are excluded.

-- Tables: employees (employee_id, name, department_id), departments (department_id, dept_name)
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Output: Employees matched with their respective department names.

#23. Explain LEFT JOIN (or LEFT OUTER JOIN) and give an example.
A: LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If there's no match in the right table, NULL values are returned for columns from the right table.

-- Show all employees, and their department if they have one
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Output: All employees, with NULL for 'dept_name' if no department is assigned.

#24. Explain RIGHT JOIN (or RIGHT OUTER JOIN) and give an example.
A: RIGHT JOIN returns all rows from the right table, and the matching rows from the left table. If there's no match in the left table, NULL values are returned for columns from the left table.

-- Show all departments, and any employees assigned to them
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Output: All departments, with NULL for 'name' if no employee is in that department.

#25. Explain FULL JOIN (or FULL OUTER JOIN) and give an example.
A: FULL JOIN returns all rows when there is a match in one of the tables. It effectively combines the results of LEFT JOIN and RIGHT JOIN. If a row doesn't have a match in one table, NULLs are returned for that table's columns.

-- Show all employees and all departments, matching where possible
SELECT e.name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;

Output: All employees and all departments, with NULLs where there's no match.

#26. What is a SELF JOIN? Provide a use case.
A: A SELF JOIN is a regular join, but the table is joined with itself. It is used to combine and compare rows within the same table.
Use Case: Finding employees who report to the same manager.

-- Table: employees (employee_id, name, manager_id)
SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

Output: A list of employees and their respective managers.

#27. How would you find all employees who do NOT have a department assigned?
A: Use a LEFT JOIN and filter for NULL values in the right table's key.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Output: Names of employees without an assigned department.

#28. How would you find all departments that currently have no employees?
A: Use a RIGHT JOIN and filter for NULL values in the left table's key.

SELECT d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

Output: Names of departments with no employees.

#29. What is a CROSS JOIN? When is it used?
A: A CROSS JOIN produces the Cartesian product of the two tables involved. This means it returns all possible combinations of rows from both tables. If Table A has m rows and Table B has n rows, a CROSS JOIN will return m * n rows.
When used: It's rare but can be used for generating all possible combinations, such as all possible time slots for a meeting, or all product-color combinations.

-- Tables: colors (color_name), sizes (size_name)
SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;

Output: Every color combined with every size (e.g., Red-Small, Red-Medium, Blue-Small, etc.).

#30. What is the purpose of the ON clause in a JOIN statement?
A: The ON clause specifies the join condition, i.e., which columns from the two tables should be used to match rows. It determines how rows from one table are related to rows in another.

-- Here, `e.department_id = d.department_id` is the ON clause
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

---
#31. Difference between JOIN and UNION?
A:
• JOIN: Combines columns from two or more tables based on a related column between them. It operates horizontally.
• UNION: Combines rows from two or more tables with compatible column structures. It operates vertically.

#32. How would you find employees who share the same salary?
A: Use a SELF JOIN to compare employees' salaries.

SELECT e1.name AS employee1, e2.name AS employee2, e1.salary
FROM employees e1
JOIN employees e2 ON e1.salary = e2.salary AND e1.employee_id <> e2.employee_id;

Output: Pairs of employees who have the same salary.

#33. Retrieve customer names and their order IDs, including customers who have not placed any orders.
A: This is a classic LEFT JOIN scenario.

-- Tables: customers (customer_id, customer_name), orders (order_id, customer_id, order_date)
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Output: All customer names, with their order IDs (NULL if no orders).

#34. Find all products that have been ordered at least once.
A: Use an INNER JOIN between products and order_items.

-- Tables: products (product_id, product_name), order_items (order_id, product_id, quantity)
SELECT DISTINCT p.product_name
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id;

Output: Unique names of products that appear in at least one order.

#35. Find products that have NEVER been ordered.
A: Use a LEFT JOIN and filter for NULL values in the joined table.

SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

Output: Names of products that have not been ordered.

#36. Get a list of all employees and the names of their managers.
A: Use a SELF JOIN on the employees table.

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Output: Each employee and their manager's name (NULL if no manager).

#37. List all orders and the name of the customer who placed each order.
A: Use an INNER JOIN between orders and customers.

SELECT o.order_id, c.customer_name, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Output: Order details with corresponding customer names.

#38. Find employees who earn more than their manager.
A: Another SELF JOIN scenario.

SELECT e.name AS employee_name, e.salary AS employee_salary, m.name AS manager_name, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Output: Employees who earn more than their direct manager.

#39. Retrieve all orders placed by customers from 'New York'.
A: Join orders with customers and filter on customer_city.

-- Tables: orders (order_id, customer_id, order_date), customers (customer_id, customer_name, customer_city)
SELECT o.order_id, c.customer_name, c.customer_city
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_city = 'New York';

Output: Orders placed by customers residing in 'New York'.

#40. What is the purpose of an OUTER JOIN?
A: An OUTER JOIN (which includes LEFT JOIN, RIGHT JOIN, and FULL JOIN) is used when you want to retrieve all records from one or both tables, even if there are no matching records in the other table. It's used to include non-matching rows by padding with NULLs.
---
Part 3: Aggregation & Grouping (Q41-60)

#41. What is the difference between WHERE and HAVING?
A:
• WHERE clause is used to filter individual rows before they are grouped. It cannot use aggregate functions.
• HAVING clause is used to filter groups of rows after the GROUP BY clause has been applied and aggregate functions have been calculated.

#42. Count the number of employees in each department.
A: Use COUNT() with GROUP BY.

SELECT department_id, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id;

Output: A list of department IDs and the count of employees in each.

#43. Find the total sales_amount for each customer.
A: Use SUM() with GROUP BY.

-- Table: sales (sale_id, customer_id, sale_amount, sale_date)
SELECT customer_id, SUM(sale_amount) AS total_customer_sales
FROM sales
GROUP BY customer_id;

Output: Each customer ID with their total sales amount.

#44. Get the maximum salary in each department.
A: Use MAX() with GROUP BY.

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

Output: Each department ID with its highest salary.

#45. List departments that have more than 5 employees.
A: Use COUNT() with GROUP BY and HAVING.

SELECT department_id, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5;

Output: Department IDs that have more than 5 employees.

#46. Find customers whose total_sales_amount is greater than 1000.
A: Aggregate sales by customer_id and use HAVING.

SELECT customer_id, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY customer_id
HAVING SUM(sale_amount) > 1000;

Output: Customer IDs whose total sales exceed 1000.

#47. Calculate the average salary for employees hired after '2020-01-01'.
A: Filter with WHERE first, then calculate AVG().

SELECT AVG(salary) AS avg_salary_after_2020
FROM employees
WHERE hire_date > '2020-01-01';

Output: A single number for the average salary of relevant employees.

#48. How would you count the number of distinct products in each category?
A: Use COUNT(DISTINCT column) with GROUP BY.

SELECT category, COUNT(DISTINCT product_name) AS distinct_product_count
FROM products
GROUP BY category;

Output: Each category with the count of its distinct products.

#49. Find the category with the most expensive product.
A: Group by category, find max price, then order and limit.

SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category
ORDER BY max_price DESC
LIMIT 1;

Output: The category with the single highest product price.

#50. Get the total sales_amount for each month of the year.
A: Extract the month from the sale_date and GROUP BY it.

-- For PostgreSQL/MySQL
SELECT EXTRACT(MONTH FROM sale_date) AS sales_month, SUM(sale_amount) AS monthly_sales
FROM sales
GROUP BY sales_month
ORDER BY sales_month;

-- For SQL Server
SELECT MONTH(sale_date) AS sales_month, SUM(sale_amount) AS monthly_sales
FROM sales
GROUP BY MONTH(sale_date)
ORDER BY sales_month;

Output: Each month number with its total sales.

#51. Find departments where the average salary is below 40000.
A: Group by department, calculate AVG() and use HAVING.

SELECT department_id, AVG(salary) AS avg_dept_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) < 40000;

Output: Department IDs where the average salary is less than 40000.

#52. Calculate the number of orders each customer has placed.
A: Group by customer_id and COUNT() orders.

SELECT customer_id, COUNT(order_id) AS num_orders
FROM orders
GROUP BY customer_id;

Output: Each customer ID with the count of their orders.

#53. List products whose category is 'Electronics' and have an average_rating above 4.0.
A: Filter by category with WHERE, then group and filter by AVG() with HAVING.

-- Table: products (product_id, product_name, category), reviews (product_id, rating)
SELECT p.product_name, AVG(r.rating) AS average_rating
FROM products p
JOIN reviews r ON p.product_id = r.product_id
WHERE p.category = 'Electronics'
GROUP BY p.product_name
HAVING AVG(r.rating) > 4.0;

Output: Names of electronic products with an average rating > 4.0.

#54. How to perform an aggregate calculation across all rows without grouping?
A: Just use the aggregate function without a GROUP BY clause.

SELECT SUM(sale_amount) AS total_company_sales
FROM sales;

Output: A single number representing the sum of all sales.

#55. What happens if you use COUNT(column_name) instead of COUNT(*)?
A: COUNT(*) counts all rows, including those with NULL values in any column. COUNT(column_name) counts only the non-NULL values in the specified column_name.

-- Assuming 'commission' can be NULL
SELECT COUNT(*), COUNT(commission)
FROM employees;

Output: Two counts: total employees vs. employees with a commission.

#56. Find the number of employees for each manager_id.
A: Group by manager_id and COUNT() employees. Filter out NULL manager IDs if desired.

SELECT manager_id, COUNT(employee_id) AS employees_managed
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

Output: Each manager ID with the number of employees they manage.

#57. Get the number of orders placed on each order_date.
A: Group by order_date and COUNT() orders.

SELECT order_date, COUNT(order_id) AS orders_per_day
FROM orders
GROUP BY order_date
ORDER BY order_date;

Output: Each date with the count of orders placed on that date.

#58. List customers who have placed exactly 3 orders.
A: Group by customer_id, COUNT() orders, and filter with HAVING.

SELECT customer_id, COUNT(order_id) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) = 3;

Output: Customer IDs who have placed exactly 3 orders.

#59. Find the average order value for each customer.
A: Group by customer_id and AVG() of total_amount.

SELECT customer_id, AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id;

Output: Each customer ID with their average order value.

#60. Calculate the total quantity of each product_id sold.
A: Group by product_id and SUM() quantity from order_items.

SELECT product_id, SUM(quantity) AS total_quantity_sold
FROM order_items
GROUP BY product_id;

Output: Each product ID with its total quantity sold.

---
Part 4: Subqueries & CTEs (Q61-80)

#61. What is a subquery? What are its types?
A: A subquery (or inner query) is a query nested inside another SQL query.
Types:
• Scalar Subquery: Returns a single value (one row, one column). Can be used almost anywhere an expression is valid.
• Multi-row Subquery: Returns multiple rows. Used with operators like IN, NOT IN, ANY, ALL, EXISTS.
• Multi-column Subquery: Returns multiple columns.
• Correlated Subquery: A subquery that depends on the outer query for its values, executing once for each row processed by the outer query.

#62. Find employees whose salary is greater than the average salary of all employees.
A: Use a scalar subquery to find the overall average salary.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Output: Employees whose salary is above the company average.

#63. Select product_name for products that have never been ordered (using a subquery).
A: Use a subquery with NOT IN.

SELECT product_name
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_items);

Output: Names of products that have not been ordered.

#64. Find the department with the highest average salary.
A: Use a subquery to find the max average salary, then join or filter.

SELECT d.dept_name
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    ORDER BY avg_salary DESC
    LIMIT 1
) AS top_dept ON d.department_id = top_dept.department_id;

Output: The name of the department with the highest average salary.

#65. What is a CTE (Common Table Expression)? Why use it?
A: A CTE is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
Why use it:
• Readability: Breaks down complex queries into logical, readable steps.
• Reusability: A CTE can be referenced multiple times within the same query.
• Recursion: CTEs can be self-referencing (recursive CTEs).
• Avoids subquery nesting: Can make queries less deeply nested than complex subqueries.

#66. Rewrite the query to find the department with the highest average salary using a CTE.
A: Define the average salaries in a CTE, then query it.

WITH DepartmentAvgSalary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.dept_name
FROM departments d
JOIN DepartmentAvgSalary das ON d.department_id = das.department_id
ORDER BY das.avg_salary DESC
LIMIT 1;

Output: The name of the department with the highest average salary.

#67. Find customer_names who placed more than 2 orders.
A: Use a subquery or CTE to count orders per customer, then join or filter.

SELECT c.customer_name
FROM customers c
WHERE c.customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(order_id) > 2
);

Output: Names of customers who have placed more than 2 orders.

#68. Select employees who report to 'John Doe' (assuming 'John Doe' is also an employee).
A: Use a subquery to find 'John Doe's employee_id.

SELECT name
FROM employees
WHERE manager_id = (SELECT employee_id FROM employees WHERE name = 'John Doe');

Output: Employees who directly report to 'John Doe'.

#69. Find the product_name of the product with the highest price.
A: Use a subquery to find the MAX(price).

SELECT product_name
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Output: The name of the most expensive product.

#70. Retrieve all orders placed on the same order_date as order_id 101.
A: Use a subquery to find the order_date of order_id 101.

SELECT *
FROM orders
WHERE order_date = (SELECT order_date FROM orders WHERE order_id = 101);

Output: All orders placed on the same date as order ID 101.

---
#71. Find customer_names who have placed an order in the last 30 days.
A: Use a subquery to find customer_ids with recent orders.

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days' -- For PostgreSQL/MySQL
    -- WHERE order_date >= DATEADD(day, -30, GETDATE()) -- For SQL Server
);

Output: Names of customers who ordered recently.

#72. Using a CTE, list all employees and their department_name.
A: Join employees and departments within a CTE, then select from the CTE.

WITH EmployeeDepartments AS (
    SELECT
        e.name AS employee_name,
        d.dept_name AS department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;

Output: Each employee matched with their department name.

#73. How would you find duplicate emails in the customers table using a CTE?
A: Use a CTE to count occurrences, then filter.

WITH EmailCounts AS (
    SELECT email, COUNT(customer_id) AS email_count
    FROM customers
    GROUP BY email
    HAVING COUNT(customer_id) > 1
)
SELECT c.customer_name, c.email
FROM customers c
JOIN EmailCounts ec ON c.email = ec.email;

Output: Customers and their emails if the email is duplicated.

#74. What is a correlated subquery? Give a simple example.
A: A correlated subquery is a subquery that depends on the outer query for its values. It executes once for each row of the outer query.
Example: Find employees who earn more than the average salary in their own department.

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id -- Correlated part
);

Output: Employees earning above their departmental average.

#75. What is the EXISTS operator used for with subqueries?
A: The EXISTS operator tests for the existence of rows in a subquery. It returns TRUE if the subquery returns any rows, and FALSE otherwise. It's often more efficient than IN for subqueries that return large result sets.

-- Select departments that have at least one employee
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

Output: Names of departments with at least one employee.

#76. Select product_names for products that have been ordered in order_id 105.
A: Use a subquery with IN.

SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM order_items WHERE order_id = 105);

Output: Products included in order ID 105.

#77. Find the employee_id and name of employees who are also managers.
A: Use DISTINCT on manager_id or EXISTS.

SELECT DISTINCT e.employee_id, e.name
FROM employees e
WHERE EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.employee_id);

Output: Employees who appear as managers for other employees.

#78. List the customer_names who have placed an order with a total_amount greater than 500.
A: Use a subquery to find customer_ids of relevant orders.

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 500
);

Output: Names of customers with at least one order over 500.

#79. Get the order_ids where all products in that order cost more than 100.
A: Use NOT EXISTS or ALL with a correlated subquery.

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE oi.order_id = o.order_id AND p.price <= 100
);

Output: Order IDs where every product in the order is priced above 100.

#80. What is the execution order of a SQL query?
A: The logical processing order is generally:
• FROM / JOIN
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• ORDER BY
• LIMIT / OFFSET
---
Part 5: Window Functions & Advanced Topics (Q81-100)

#81. What is a Window Function? How is it different from an aggregate function?
A: Window functions perform a calculation across a set of table rows that are related to the current row (a "window").
• Difference: Unlike aggregate functions (SUM, COUNT, AVG) which group rows and return a single value per group, window functions do not collapse rows. They return a value for each row, based on the window.

#82. Calculate the running total of sales_amount for each customer, ordered by sale_date.
A: Use SUM() as a window function with PARTITION BY and ORDER BY.

SELECT
    customer_id,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS running_total
FROM sales;

Output: Each sale, with a cumulative sum of sales for that customer up to that date.

#83. Find the second highest salary using a window function.
A: Use DENSE_RANK() or RANK() in a CTE.

WITH RankedSalaries AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT DISTINCT salary
FROM RankedSalaries
WHERE rnk = 2;

Output: The second highest salary value.

#84. Rank employees by salary within each department.
A: Use RANK() or DENSE_RANK() with PARTITION BY department_id.

SELECT
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Output: Employees ranked by salary within their department.

#85. What is the difference between RANK() and DENSE_RANK()?
A:
• RANK(): Assigns a rank to each row within its partition. If there are ties, it assigns the same rank and skips the next rank(s). (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Assigns a rank to each row within its partition. If there are ties, it assigns the same rank but does not skip the next rank(s). (e.g., 1, 2, 2, 3).

#86. Get the previous order_date for each customer's orders.
A: Use the LAG() window function.

SELECT
    customer_id,
    order_id,
    order_date,
    LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date
FROM orders;

Output: Each order, with the date of the customer's preceding order.

#87. What is the NTILE(n) window function?
A: NTILE(n) divides the rows in a partition into n groups, and assigns a number from 1 to n to each group. It attempts to make the groups as equal in size as possible.

-- Divide employees into 4 salary quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

Output: Employees assigned to one of four salary quartiles.

#88. What is the ROW_NUMBER() window function?
A: ROW_NUMBER() assigns a unique, sequential integer to each row within its partition, starting from 1. It does not skip numbers in case of ties.

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

Output: Employees with a unique row number within their department, based on salary.

#89. What is an INDEX in SQL? Why is it important?
A: An index is a special lookup table that the database search engine can use to speed up data retrieval. It works like an index in a book, allowing the database to quickly find data without scanning the entire table.
Importance:
• Significantly improves the performance of SELECT queries, especially on large tables.
• Speeds up WHERE clauses, ORDER BY, GROUP BY, and JOIN operations.
• However, it can slow down INSERT, UPDATE, and DELETE operations as the index also needs to be updated.

#90. What is a VIEW in SQL? When would you use it?
A: A VIEW is a virtual table based on the result-set of a SQL query. It does not store data itself but instead retrieves it from the underlying tables whenever the view is queried.
Use Cases:
• Security: Restrict access to specific rows or columns.
• Simplicity: Simplify complex queries (e.g., joins, aggregations) into a single logical table.
• Consistency: Present a consistent view of data even if underlying table structures change.
---
#91. What is the COALESCE() function?
A: The COALESCE() function returns the first non-NULL expression in a list of arguments. It's useful for providing default values when a column might be NULL.

SELECT name, COALESCE(commission, 0) AS actual_commission
FROM employees;

Output: Employee names, with 0 instead of NULL if commission is missing.

#92. What is UNION vs UNION ALL?
A:
• UNION: Combines the result sets of two or more SELECT statements and removes duplicate rows. It sorts the combined result.
• UNION ALL: Combines the result sets of two or more SELECT statements and includes all rows, including duplicates. It does not sort the result, making it generally faster.

#93. Find all employee_ids that appear in both employees and contractors tables.
A: Use the INTERSECT operator.

-- Tables: employees (employee_id, name), contractors (contractor_id, name)
SELECT employee_id FROM employees
INTERSECT
SELECT contractor_id FROM contractors;

Output: Employee/contractor IDs that exist in both tables.

#94. Find employee_ids that are in employees but NOT in contractors tables.
A: Use the EXCEPT (or MINUS in Oracle) operator.

SELECT employee_id FROM employees
EXCEPT
SELECT contractor_id FROM contractors;

Output: Employee IDs present in 'employees' but not in 'contractors'.

#95. What is a PRIMARY KEY?
A: A PRIMARY KEY is a column (or set of columns) that uniquely identifies each row in a table.
• It must contain unique values.
• It cannot contain NULL values.
• A table can have only one primary key.

#96. What is a FOREIGN KEY?
A: A FOREIGN KEY is a column (or set of columns) in one table that refers to the PRIMARY KEY in another table. It establishes a link or relationship between two tables, enforcing referential integrity.

#97. How do you detect duplicate records in a table?
A: Use GROUP BY with COUNT() and HAVING.

SELECT col1, col2, COUNT(*)
FROM my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Output: Rows (or groups of columns) that appear more than once.

#98. How would you delete duplicate records from a table, keeping one instance?
A: This can be done using a CTE with ROW_NUMBER().

WITH CTE_Duplicates AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY (SELECT NULL)) as rn -- Partition by columns that define a duplicate
    FROM my_table
)
DELETE FROM CTE_Duplicates
WHERE rn > 1;

Output: Duplicate rows are removed, leaving one instance for each set of duplicates.

#99. What is NULL in SQL?
A: NULL represents a missing or unknown value. It is not equivalent to zero, an empty string, or false. Any arithmetic or comparison operation involving NULL generally results in NULL. Special operators like IS NULL and IS NOT NULL are used to check for NULL values.

#100. Write a query to find the gap between order_dates for each customer.
A: Use LAG() to get the previous order_date and then calculate the difference.

SELECT
    customer_id,
    order_id,
    order_date,
    LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date,
    order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last_order
FROM orders
ORDER BY customer_id, order_date;

Output: Each order, with the date of the previous order and the number of days between them.

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨