SQL Questions & Answers

SQL Coding Questions & Answers

Q1: Retrieve all employees’ first and last names

SELECT first_name, last_name
FROM employees;

Q2: Retrieve employees with salary greater than 50000

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

Q3: Retrieve employees in Sales or IT department
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (1, 2);  -- assuming 1=Sales, 2=IT

Q4: Count the total number of employees
SELECT COUNT(*) AS total_employees
FROM employees;

Q5: List employees ordered by salary descending
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Q6: Find the average salary per department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Q7: Find employees earning above the average salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

Q8: Top 3 highest paid employees
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Q9: Count employees per department with more than 5 employees
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Q10: Join employees with departments to get department names
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Q11: Rank employees by salary within their department
SELECT first_name, last_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Q12: Find employees whose salary is higher than their manager’s salary
SELECT e.first_name, e.last_name, e.salary, m.first_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;
Q13: Retrieve employees with the second highest salary
SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (
        SELECT MAX(salary) FROM employees
    )
);
Q14: Cumulative salary per department
SELECT first_name, last_name, department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS cumulative_salary
FROM employees;

Q15: Find employees who do not have a department
SELECT first_name, last_name
FROM employees
WHERE department_id IS NULL;

Q16: List departments with total salary expenditure
SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name;
Q17: Find employees whose first name starts with ‘B’
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'B%';

Q18: Delete all employees with salary less than 30000
DELETE FROM employees
WHERE salary < 30000;

Q19: Update salaries by 10% for IT department
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 2;  -- assuming 2=IT

Q20: Create a view for high earners
CREATE VIEW HighEarners AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 70000;

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more