Unit 1: SQL implementation and database design

sqlCREATE DATABASE university;
USE university;
CREATE TABLE department (
  dept_id   INT PRIMARY KEY,                       -- entity integrity
  dept_name VARCHAR(40) NOT NULL UNIQUE,           -- not null and unique
  budget    DECIMAL(12,2) CHECK (budget > 0)       -- check
);
CREATE TABLE student (
  roll     INT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  dept_id  INT,
  cgpa     DECIMAL(3,2) CHECK (cgpa BETWEEN 0 AND 10),
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)   -- referential integrity
    ON DELETE SET NULL ON UPDATE CASCADE
);
ALTER TABLE student ADD email VARCHAR(60) UNIQUE;
ALTER TABLE student MODIFY name VARCHAR(80) NOT NULL;     -- MySQL
ALTER TABLE student DROP COLUMN email;
ALTER TABLE student ADD CONSTRAINT chk_name CHECK (CHAR_LENGTH(name) > 1);
RENAME TABLE student TO pg_student;  RENAME TABLE pg_student TO student;
DROP TABLE IF EXISTS temp;

sqlINSERT INTO department VALUES (1, 'Computer Applications', 2500000), (2, 'Management', 1800000);
INSERT INTO student (roll, name, dept_id, cgpa) VALUES (101, 'Aman', 1, 8.4), (102, 'Riya', 1, 9.1), (103, 'Kabir', 2, 7.2);
UPDATE student SET cgpa = cgpa + 0.1 WHERE dept_id = 1 AND cgpa < 9.9;
DELETE FROM student WHERE roll = 103;

CREATE USER 'clerk'@'localhost' IDENTIFIED BY 'Str0ng#Pass';
GRANT SELECT, INSERT ON university.student TO 'clerk'@'localhost';
REVOKE INSERT ON university.student FROM 'clerk'@'localhost';

• Test each constraint by inserting a violating row (duplicate key, cgpa 11, dept_id 99) and noting the error.

sqlSELECT UPPER(name), LENGTH(name), SUBSTRING(name, 1, 3), CONCAT(name, '-', roll) FROM student;  -- string
SELECT ROUND(cgpa, 1), CEIL(cgpa), FLOOR(cgpa), MOD(roll, 2), POWER(2, 5) FROM student;        -- numeric
SELECT CURDATE(), NOW(), DATEDIFF('2026-12-31', CURDATE()), DATE_FORMAT(NOW(), '%d-%m-%Y');     -- date
SELECT dept_id, COUNT(*), AVG(cgpa), MAX(cgpa), MIN(cgpa) FROM student GROUP BY dept_id;        -- aggregate
SELECT name, IFNULL(email, 'not given'), CASE WHEN cgpa >= 9 THEN 'O' WHEN cgpa >= 8 THEN 'A+' ELSE 'A' END FROM student;

sqlSELECT name FROM student WHERE cgpa > (SELECT AVG(cgpa) FROM student);
SELECT dept_name FROM department d WHERE NOT EXISTS (SELECT 1 FROM student s WHERE s.dept_id = d.dept_id);
SELECT s.name, d.dept_name FROM student s INNER JOIN department d ON s.dept_id = d.dept_id;
SELECT d.dept_name, COUNT(s.roll) AS strength
FROM department d LEFT JOIN student s ON s.dept_id = d.dept_id GROUP BY d.dept_name;
SELECT a.name, b.name FROM student a JOIN student b ON a.dept_id = b.dept_id AND a.roll < b.roll;  -- self join

sqlDELIMITER //
CREATE PROCEDURE list_toppers()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_name VARCHAR(80); DECLARE v_cgpa DECIMAL(3,2);
  DECLARE c CURSOR FOR SELECT name, cgpa FROM student WHERE cgpa >= 9;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN c;
  read_loop: LOOP
    FETCH c INTO v_name, v_cgpa;
    IF done THEN LEAVE read_loop; END IF;
    SELECT CONCAT(v_name, ' : ', v_cgpa) AS topper;
  END LOOP;
  CLOSE c;
END //
DELIMITER ;
CALL list_toppers();

sqlDELIMITER //
CREATE PROCEDURE transfer(IN from_acc INT, IN to_acc INT, IN amt DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
  UPDATE account SET balance = balance - amt WHERE acc_no = from_acc AND balance >= amt;
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
  END IF;
  UPDATE account SET balance = balance + amt WHERE acc_no = to_acc;
  COMMIT;
END //

CREATE FUNCTION net_salary(basic DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
  RETURN basic + basic * 0.42 + basic * 0.10 - basic * 0.12;   -- DA 42%, HRA 10%, PF 12% (illustrative)
END //

CREATE TRIGGER before_issue BEFORE INSERT ON issue
FOR EACH ROW
BEGIN
  IF (SELECT copies FROM book WHERE book_id = NEW.book_id) = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No copies available';
  END IF;
END //

CREATE TRIGGER after_issue AFTER INSERT ON issue
FOR EACH ROW UPDATE book SET copies = copies - 1 WHERE book_id = NEW.book_id //
DELIMITER ;

c/* Pro*C style embedded SQL in C */
EXEC SQL BEGIN DECLARE SECTION;
  int  roll;  char name[50];  float cgpa;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;

EXEC SQL CONNECT :user IDENTIFIED BY :pwd;
roll = 101;
EXEC SQL SELECT name, cgpa INTO :name, :cgpa FROM student WHERE roll = :roll;
if (sqlca.sqlcode == 0) printf("%s %.2f\n", name, cgpa);
EXEC SQL DECLARE c1 CURSOR FOR SELECT name FROM student;
EXEC SQL OPEN c1;
/* EXEC SQL FETCH c1 INTO :name; in a loop until NOT FOUND */
EXEC SQL CLOSE c1;
EXEC SQL COMMIT WORK RELEASE;

• A precompiler converts EXEC SQL statements into library calls; in labs without Pro*C, the same exercise is done through JDBC or Python's mysql-connector with parameterised queries.