Unit 1: SQL from schema design to advanced queries

Draw the ER diagram (entities, attributes, keys, relationships with cardinality), then convert it: each entity becomes a table, 1:N relationships become foreign keys, and M:N relationships become a separate table.

sqlCREATE TABLE dept (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(30) UNIQUE NOT NULL
);
CREATE TABLE emp (
  emp_id  INT PRIMARY KEY,
  name    VARCHAR(40) NOT NULL,
  salary  DECIMAL(10,2) CHECK (salary > 0),
  city    VARCHAR(20) DEFAULT 'Ludhiana',
  dept_id INT REFERENCES dept(dept_id)
);
ALTER TABLE emp ADD email VARCHAR(50);
RENAME TABLE emp TO employee;   -- MySQL syntax
TRUNCATE TABLE employee;        -- removes all rows, keeps structure

sqlINSERT INTO dept VALUES (10, 'Accounts'), (20, 'IT');
UPDATE employee SET salary = salary * 1.10 WHERE dept_id = 20;
DELETE FROM employee WHERE salary < 15000;

START TRANSACTION;
UPDATE employee SET salary = salary + 1000 WHERE emp_id = 1;
SAVEPOINT s1;
DELETE FROM employee WHERE emp_id = 2;
ROLLBACK TO s1;   -- undo the delete only
COMMIT;

sqlSELECT dept_id, COUNT(*), MIN(salary), MAX(salary), AVG(salary), SUM(salary)
FROM employee GROUP BY dept_id HAVING COUNT(*) > 2 ORDER BY AVG(salary) DESC;

SELECT name FROM employee WHERE name LIKE 'A%';          -- starts with A
SELECT name FROM employee WHERE salary BETWEEN 20000 AND 40000;
SELECT name FROM employee WHERE city IN ('Ludhiana', 'Jalandhar');

sqlSELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employee;

WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 5)
SELECT n FROM nums;

SELECT e.name, d.dept_name FROM employee e
LEFT JOIN dept d ON e.dept_id = d.dept_id;