Unit 2: SQL

sqlCREATE TABLE student (
  roll   INT PRIMARY KEY,
  name   VARCHAR(40) NOT NULL,
  course VARCHAR(10),
  marks  INT CHECK (marks BETWEEN 0 AND 100)
);
INSERT INTO student VALUES (1, 'Ana', 'BCA', 82);
UPDATE student SET marks = 85 WHERE roll = 1;
SELECT name, marks FROM student WHERE course = 'BCA' ORDER BY marks DESC;

sqlALTER TABLE student ADD email VARCHAR(60);
ALTER TABLE student MODIFY marks DECIMAL(5,2);   -- MySQL/Oracle; others use ALTER COLUMN
TRUNCATE TABLE temp_marks;                       -- remove all rows, keep structure
DROP TABLE temp_marks;                           -- remove the table

sqlSELECT DISTINCT s.name, c.title          -- attributes (DISTINCT removes duplicates)
FROM student s, course c                 -- relations (Cartesian product)
WHERE s.course = c.code AND s.marks > 60 -- predicate
ORDER BY s.name;

• Evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. String matching with LIKE ('%' any string, '_' one character).

sqlSELECT roll FROM python_students UNION SELECT roll FROM java_students;      -- either (duplicates removed)
SELECT roll FROM python_students INTERSECT SELECT roll FROM java_students;  -- both
SELECT roll FROM python_students EXCEPT SELECT roll FROM java_students;     -- MINUS in Oracle
SELECT name FROM student WHERE email IS NULL;

• NULL means unknown or missing. Any arithmetic with NULL gives NULL; comparisons give unknown, a third truth value; WHERE keeps only rows that are true. Aggregates (except COUNT(*)) ignore NULLs.

• Aggregate functions: COUNT, SUM, AVG, MIN, MAX.
• Operators and predicates: AND, OR, NOT, BETWEEN, IN, LIKE ('A%' starts with A), IS NULL.
• GROUP BY groups rows; HAVING filters groups; ORDER BY sorts.

sqlSELECT course, COUNT(*) AS students, AVG(marks) AS average
FROM student
GROUP BY course
HAVING AVG(marks) > 60
ORDER BY average DESC;

sql-- Students scoring above the average
SELECT name FROM student WHERE marks > (SELECT AVG(marks) FROM student);

-- IN and NOT IN
SELECT name FROM student WHERE course IN (SELECT code FROM course WHERE dept = 'IT');

-- Correlated subquery with EXISTS: courses having at least one student
SELECT c.title FROM course c
WHERE EXISTS (SELECT 1 FROM student s WHERE s.course = c.code);

-- Comparison with ALL / SOME
SELECT name FROM student WHERE marks >= ALL (SELECT marks FROM student);   -- topper(s)

-- Subquery in FROM (derived table)
SELECT course, avg_m FROM (SELECT course, AVG(marks) AS avg_m FROM student GROUP BY course) t
WHERE avg_m > 70;

sqlINSERT INTO alumni (roll, name) SELECT roll, name FROM student WHERE year = 2026;
UPDATE student SET marks = CASE WHEN marks >= 95 THEN 100 ELSE marks + 5 END WHERE course = 'MSCIT';
DELETE FROM student WHERE marks < (SELECT AVG(marks) FROM student) * 0.3;

sqlSELECT s.name, c.title
FROM student s INNER JOIN course c ON s.course = c.code;

sqlSELECT s.name, c.title FROM student s LEFT OUTER JOIN course c ON s.course = c.code;
SELECT * FROM student NATURAL JOIN enrolment;      -- joins on common column names
SELECT * FROM student JOIN enrolment USING (roll);

sqlCREATE VIEW it_toppers AS
SELECT roll, name, marks FROM student WHERE course = 'MSCIT' AND marks >= 80;
SELECT * FROM it_toppers;
CREATE MATERIALIZED VIEW course_avg AS SELECT course, AVG(marks) avg_m FROM student GROUP BY course;  -- PostgreSQL/Oracle

• A view is a virtual relation — its query runs when used; it hides columns, simplifies queries and adds security. Simple views (one table, no aggregates) are updatable; a materialised view stores results and must be refreshed.

sqlSTART TRANSACTION;
UPDATE account SET balance = balance - 5000 WHERE acc_no = 101;
UPDATE account SET balance = balance + 5000 WHERE acc_no = 202;
COMMIT;            -- or ROLLBACK; to undo both

• SAVEPOINT s1 and ROLLBACK TO s1 undo part of a transaction. Many systems auto-commit each statement unless a transaction is started.

sqlCREATE TABLE course (
  code   VARCHAR(10) PRIMARY KEY,
  title  VARCHAR(60) NOT NULL UNIQUE,
  credits INT DEFAULT 4 CHECK (credits BETWEEN 1 AND 6)
);
CREATE TABLE enrolment (
  roll INT, code VARCHAR(10),
  grade CHAR(2),
  PRIMARY KEY (roll, code),
  FOREIGN KEY (roll) REFERENCES student(roll) ON DELETE CASCADE,
  FOREIGN KEY (code) REFERENCES course(code)
);

• Schemas and catalogs: a database contains schemas (namespaces) that contain tables, views and other objects — CREATE SCHEMA exam; then exam.result.

sqlGRANT SELECT, INSERT ON student TO clerk;
GRANT SELECT ON it_toppers TO hod WITH GRANT OPTION;    -- may pass the privilege on
CREATE ROLE faculty;
GRANT UPDATE (marks) ON enrolment TO faculty;           -- column-level privilege
GRANT faculty TO amit;
REVOKE INSERT ON student FROM clerk CASCADE;

sql-- MySQL syntax
DELIMITER //
CREATE FUNCTION grade_of(m INT) RETURNS CHAR(1) DETERMINISTIC
BEGIN
  RETURN CASE WHEN m >= 75 THEN 'A' WHEN m >= 60 THEN 'B' ELSE 'C' END;
END //

CREATE PROCEDURE raise_marks(IN c VARCHAR(10), IN bonus INT)
BEGIN
  UPDATE student SET marks = LEAST(marks + bonus, 100) WHERE course = c;
END //

CREATE TRIGGER log_marks AFTER UPDATE ON student
FOR EACH ROW
BEGIN
  IF NEW.marks <> OLD.marks THEN
    INSERT INTO marks_log(roll, old_marks, new_marks, changed_on)
    VALUES (OLD.roll, OLD.marks, NEW.marks, NOW());
  END IF;
END //
DELIMITER ;

SELECT name, grade_of(marks) FROM student;
CALL raise_marks('MSCIT', 3);

• A trigger is like a procedure but is never called directly: it fires automatically BEFORE or AFTER an INSERT, UPDATE or DELETE, for each row or each statement — used for audit logs, derived values and complex integrity rules.

javaimport java.sql.*;
public class JdbcDemo {
    public static void main(String[] a) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/college";
        try (Connection con = DriverManager.getConnection(url, "root", "password");
             PreparedStatement ps = con.prepareStatement("SELECT name, marks FROM student WHERE course = ?")) {
            ps.setString(1, "MSCIT");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) System.out.println(rs.getString("name") + " " + rs.getInt("marks"));
        }
    }
}

• Embedded SQL concepts: host variables (prefixed with a colon), SQLCA or SQLSTATE for error codes, and cursors (DECLARE, OPEN, FETCH, CLOSE) to process multi-row results one row at a time.