sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES (1, 'Santosh K', 30, 'Engineering'),
       (2, 'T V Santosh', 25, 'Marketing'),
       (3, 'Dhruv', 35, 'HR');

SELECT * FROM employees;

SELECT name, age FROM employees;

SELECT name, department FROM employees
WHERE age > 30;

SELECT name, age FROM employees
ORDER BY age DESC;

UPDATE employees
SET age = 32
WHERE name = 'John Doe';

SELECT * FROM employees;

DELETE FROM employees
WHERE name = 'Dhruv K';

SELECT * FROM employees;

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name)
VALUES (1, 'Engineering'),
       (2, 'Marketing'),
       (3, 'HR');

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department = departments.department_name;

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

SELECT AVG(age) AS average_age FROM employees;

SELECT MAX(age) AS oldest_employee_age FROM employees;

SELECT MIN(age) AS youngest_employee_age FROM employees;

SELECT SUM(age) AS total_age FROM employees;

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

SELECT name FROM employees
WHERE name LIKE 'J%';

SELECT name, age FROM employees
WHERE age IS NULL;

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

SELECT name, age FROM employees WHERE department = 'Engineering'
UNION
SELECT name, age FROM employees WHERE department = 'Marketing';

SELECT name, age FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT name, age FROM employees WHERE department = 'Marketing';

CREATE VIEW employee_summary AS
SELECT name, department, age
FROM employees;

SELECT * FROM employee_summary;

CREATE INDEX idx_department ON employees(department);

SELECT * FROM employees WHERE department = 'Engineering';

BEGIN;

UPDATE employees SET age = 40 WHERE name = 'Santosh K';

COMMIT;

-- ROLLBACK example:
-- BEGIN;
-- DELETE FROM employees WHERE name = 'Santosh K';
-- ROLLBACK;

-- Left Join
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department = departments.department_name;

-- Right Join
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department = departments.department_name;

-- Full Join (if supported by your DBMS)
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department = departments.department_name;

SELECT name, age,
       CASE
         WHEN age >= 30 THEN 'Senior'
         WHEN age < 30 THEN 'Junior'
         ELSE 'Unknown'
       END AS experience_level
FROM employees;

DELIMITER //

CREATE PROCEDURE get_employee_by_department(dept VARCHAR(50))
BEGIN
  SELECT name, age
  FROM employees
  WHERE department = dept;
END //

DELIMITER ;

-- Call the stored procedure:
CALL get_employee_by_department('Engineering');