SQL

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email varchar(100) UNIQUE NOT NULL,
gender ENUM('Male', 'Female', 'Other'),
date_of_birth DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

SELECT id, email FROM users;
DROP DATABASE startersql


##create DB
CREATE DATABASE startersql;

##Use a DB
USE startersql;

##Create a table 
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    gender VARCHAR(10),
    dob DATE
);


##Insert DATA
INSERT INTO users (name, email, gender, dob) VALUES
('Alice', 'alice@mail.com', 'Female', '2002-07-01'),
('Lily', 'lily@mail.com', 'Female', '2001-06-10'),
('Bob', 'bob@mail.com', 'Male', '2000-09-21');

##View all
SELECT * FROM users;

##Select specific
SELECT name, email FROM users;

##Filter rows
SELECT * FROM users
WHERE gender = 'Female';

##Update record
UPDATE users
SET email = 'alice123@mail.com'
WHERE id = 1;

##Delete Record
DELETE FROM users
WHERE id = 3;

##find unique values 
SELECT DISTINCT gender FROM users;

##add a new column
ALTER TABLE users
ADD COLUMN status VARCHAR(10) DEFAULT 'Active';

____employer table problems for real life examples___

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    salary INT,
    gender VARCHAR(10),
    hire_date DATE,
    city VARCHAR(40)
);
____sample data ___
INSERT INTO employees VALUES
(1,'Rahul','IT',45000,'Male','2021-03-10','Delhi'),
(2,'Priya','HR',52000,'Female','2020-07-18','Mumbai'),
(3,'Amit','IT',60000,'Male','2019-11-02','Delhi'),
(4,'Sneha','Finance',70000,'Female','2018-05-25','Chennai'),
(5,'Karan','IT',30000,'Male','2023-01-15','Pune'),
(6,'Neha','HR',55000,'Female','2022-09-10','Mumbai'),
(7,'Arjun','Finance',65000,'Male','2019-02-20','Delhi');


1️⃣ List all employees who work in the IT department.
2️⃣ Show employees whose salary is greater than 50,000.
3️⃣ Count how many employees are in each department.
4️⃣ Find the highest salary in the company.
5️⃣ Get employees hired after 2020.
6️⃣ Show the average salary of IT employees.
7️⃣ List employees ordered by salary (descending).
8️⃣ Find employees who live in Delhi.
9️⃣ Increase salary of Finance employees by 10% (view output only) using SELECT.
🔟 Show male employees only.
1️⃣1️⃣ Show top 3 highest-paid employees.
1️⃣2️⃣ Find the total salary spent per department.
1️⃣3️⃣ List employees whose name starts with ‘S’.
1️⃣4️⃣ Show employees with salary between 40,000 and 60,000.
1️⃣5️⃣ Delete employees whose salary is below 35,000 (only if asked).

 Answers
1.
SELECT * FROM employees
WHERE department = 'IT';
2.
SELECT * FROM employees
WHERE salary > 50000;

3.
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

4️.
SELECT MAX(salary) AS highest_salary
FROM employees;

5️.
SELECT * FROM employees
WHERE hire_date > '2020-01-01';

6️.
SELECT AVG(salary) AS avg_it_salary
FROM employees
WHERE department = 'IT';

7️.
SELECT * FROM employees
ORDER BY salary DESC;

8️.
SELECT * FROM employees
WHERE city = 'Delhi';

9️.
SELECT name, department,
       salary,
       salary * 1.10 AS revised_salary
FROM employees
WHERE department = 'Finance';

10.
SELECT * FROM employees
WHERE gender = 'Male';

11.
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

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

13.
SELECT * FROM employees
WHERE name LIKE 'S%';

14.
SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;

15. 
DELETE FROM employees
WHERE salary < 35000;