SQL Cheatsheet

CREATE DATABASE company_db;

Create a new database named 'company_db'.

USE company_db;

Switch context to the 'company_db' database.

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

Create an 'employees' table to store employee details.

ALTER TABLE employees ADD email VARCHAR(100);

Add an 'email' column to the 'employees' table.

DROP TABLE employees;

Delete the 'employees' table.

INSERT INTO employees (id, name, department, salary, join_date) VALUES (1, 'Rahul Sharma', 'Sales', 55000.00, '2022-01-15');

Insert data for a new employee.

SELECT * FROM employees;

Retrieve all employee data.

SELECT name, salary FROM employees WHERE department = 'Sales';

Get names and salaries of employees in Sales.

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

Give a 10% raise to Sales department employees.

DELETE FROM employees WHERE id = 3;

Remove the employee with id 3.

SELECT * FROM employees WHERE salary > 60000;

Find employees earning more than ₹60,000.

SELECT COUNT(*) FROM employees WHERE department = 'IT';

Count number of employees in IT department.

SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department;

Average salary by department.

SELECT name FROM employees ORDER BY join_date DESC;

List employee names ordered by most recent join date.

SELECT DISTINCT department FROM employees;

List all unique departments.

CREATE TABLE projects (project_id INT PRIMARY KEY, name VARCHAR(50), start_date DATE);

Create a table for projects.

CREATE TABLE assignments (emp_id INT, project_id INT, role VARCHAR(30), FOREIGN KEY(emp_id) REFERENCES employees(id), FOREIGN KEY(project_id) REFERENCES projects(project_id));

Create assignments linking employees to projects.

SELECT e.name, p.name AS project, a.role FROM employees e INNER JOIN assignments a ON e.id = a.emp_id INNER JOIN projects p ON a.project_id = p.project_id;

List employees, projects, and roles.

SELECT department, COUNT(DISTINCT id) FROM employees GROUP BY department HAVING COUNT(DISTINCT id) > 5;

Departments with more than 5 employees.

SELECT TOP 5 * FROM employees ORDER BY salary DESC;

Find the top 5 highest-paid employees.

SELECT name FROM employees WHERE join_date BETWEEN '2023-01-01' AND '2023-06-30';

Employees who joined in the first half of 2023.

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

Total salary expense per department.

SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e1.department);

Employees earning above their department's average salary.

WITH RecentHires AS (SELECT * FROM employees WHERE join_date > '2023-01-01') SELECT * FROM RecentHires WHERE salary > 50000;

Common Table Expression (CTE) example: recent hires earning above ₹50,000.