Unit 3: Relational algebra and 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;

• 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;

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

• View: a virtual table defined by a query: CREATE VIEW toppers AS SELECT * FROM student WHERE marks > 80;
• Cursor: a pointer that processes the rows of a query result one at a time (in PL/SQL or stored procedures).
• Stored procedure: a named block of SQL statements stored in the database and executed with CALL/EXEC.
• Trigger: a block that runs automatically on INSERT, UPDATE or DELETE (for example logging changes).
• Dynamic SQL: SQL statements built and executed at run time.
• Analytical queries use window functions such as RANK() OVER (ORDER BY marks DESC); hierarchical and recursive queries (CONNECT BY or WITH RECURSIVE) process tree data like an employee–manager chart.