Unit 2 of 4 · M.Sc IT Sem 1

Unit 2: SQL

Relational Database Management System notes · PTU syllabus (PGCA1904)

8 min read13 topics10 exam questions
On this page
  1. Unit summary
  2. SQL data definition and command categories
  3. Basic query structure
  4. Set operations and null values
  5. Aggregate functions, grouping and HAVING
  6. Nested subqueries
  7. Modification of the database
  8. Join expressions
  9. Views
  10. Transactions
  11. Integrity constraints, data types and schemas
  12. Authorisation
  13. Functions, procedures and triggers
  14. Embedded SQL, dynamic SQL, JDBC and SQLJ
  15. Key terms
  16. Quick revision
  17. Important questions

Unit summary

SQL is the standard language of relational databases. This unit covers DDL, DML and DCL, data definition, basic query structure, set operations, null values, aggregate functions, nested subqueries, database modification, join expressions, views, transactions, integrity constraints, data types and schemas, authorisation, functions, procedures and triggers, and embedded SQL, dynamic SQL, JDBC and SQLJ.

After this unit you can

  • Define and modify schemas with SQL DDL
  • Write queries with set operations, nulls, aggregates, subqueries and joins
  • Use views, transactions, constraints and authorisation
  • Write functions, procedures and triggers and access SQL from programs

PTU syllabus topics

  • DCL/DDL/DML
  • SQL data definition
  • basic query structure
  • set operations
  • null values
  • aggregate functions
  • nested subqueries
  • database modification
  • join expressions
  • views
  • transactions
  • integrity constraints
  • SQL data types and schemas
  • authorization
  • functions/procedures/triggers
  • introduction to embedded SQL
  • dynamic SQL
  • JDBC and SQLJ
1

Topic 1

SQL data definition and command categories

ComparisonSQL sub-languages
Purpose
Commands

DDL

Define structure

CREATE, ALTER, DROP, TRUNCATE, RENAME

DML

Change data

INSERT, UPDATE, DELETE

DQL

Query data

SELECT

DCL

Control access

GRANT, REVOKE

TCL

Manage transactions

COMMIT, ROLLBACK, SAVEPOINT

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
2

Topic 2

Basic query structure

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).

Example

A query with a join: SELECT s.name, c.title FROM student s JOIN enrol e ON s.roll = e.roll JOIN course c ON e.code = c.code WHERE c.title = 'Python'; lists students enrolled in Python.

Exam tip

SQL clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

3

Topic 3

Set operations and null values

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.

Example

NULL needs IS NULL: SELECT * FROM student WHERE email = NULL; returns nothing, because comparisons with NULL are unknown. The correct query is WHERE email IS NULL. Also, AVG(marks) ignores NULL marks.

Exam tip

UNION removes duplicates; UNION ALL keeps them.

4

Topic 4

Aggregate functions, grouping and HAVING

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

Exam tip

WHERE filters rows before grouping; HAVING filters groups after grouping. This difference is asked very often.

Example

WHERE vs HAVING: SELECT course, AVG(marks) FROM student WHERE year = 2026 GROUP BY course HAVING AVG(marks) > 70; filters rows by year before grouping, then keeps only courses averaging above 70.

Exam tip

WHERE filters rows before grouping; HAVING filters groups after aggregation.

5

Topic 5

Nested subqueries

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

Topic 6

Modification of the database

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;

Example

Careful updates: UPDATE student SET marks = marks + 5 WHERE course = 'MSc IT'; adds grace marks only to M.Sc IT students. Forgetting the WHERE clause would change every student's marks.

Exam tip

Always test UPDATE and DELETE conditions with a SELECT first.

7

Topic 7

Join expressions

JoinReturns
Inner joinOnly rows with matching values in both tables
Natural joinInner join on all columns with the same name
Left outer joinAll rows of the left table, with matches or NULLs
Right outer joinAll rows of the right table, with matches or NULLs
Full outer joinAll rows of both tables
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);

Example

Inner vs left join: a LEFT JOIN of student to fees lists every student, with NULL fee details for those who have not paid; an inner join lists only students with a matching fee record.

Exam tip

Inner join = matching rows only; left/right/full outer joins keep unmatched rows with NULLs.

8

Topic 8

Views

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.

Example

A view for security: the HOD is given access to the view it_toppers (roll, name, marks of top IT students) but not to the full student table with phone numbers and addresses.

Exam tip

A view is a stored query (virtual table); it simplifies queries and restricts access.

9

Topic 9

Transactions

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.

Example

A complete transaction: START TRANSACTION; UPDATE account SET balance = balance − 5000 WHERE acc_no = 1; UPDATE account SET balance = balance + 5000 WHERE acc_no = 2; COMMIT; — if any step fails, ROLLBACK; undoes both.

Exam tip

COMMIT makes changes permanent; ROLLBACK undoes them; SAVEPOINT allows partial rollback.

10

Topic 10

Integrity constraints, data types and schemas

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)
);
Key termsSQL data types
CHAR(n) and VARCHAR(n)
Fixed- and variable-length strings
INT, SMALLINT, BIGINT
Integers
NUMERIC(p, d) and DECIMAL
Exact numbers — money
FLOAT, REAL, DOUBLE
Approximate numbers
DATE, TIME, TIMESTAMP, INTERVAL
Dates and times
BOOLEAN, BLOB, CLOB
Logical values, large binary and text objects
User-defined types and domains
CREATE DOMAIN or CREATE TYPE
  • Schemas and catalogs: a database contains schemas (namespaces) that contain tables, views and other objects — CREATE SCHEMA exam; then exam.result.

Example

Constraints catching errors: marks INT CHECK (marks BETWEEN 0 AND 100) rejects 150; a FOREIGN KEY rejects an enrolment for a course code that does not exist; UNIQUE on email rejects a duplicate address.

Exam tip

Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT.

11

Topic 11

Authorisation

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;

Example

Granting and revoking: GRANT SELECT ON it_toppers TO hod WITH GRANT OPTION; lets the HOD read the view and pass the right on. REVOKE INSERT ON student FROM clerk; removes the clerk's insert permission.

Exam tip

Grant only the privileges a user needs — the principle of least privilege.

12

Topic 12

Functions, procedures and triggers

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.
ComparisonFunction, procedure and trigger
Function
Procedure

Returns

A single value; usable in SELECT

Zero or more values through OUT parameters

Invoked by

Within an SQL expression

CALL or EXEC

Side effects

Should not change data (in most DBMSs)

May change data and control transactions

13

Topic 13

Embedded SQL, dynamic SQL, JDBC and SQLJ

ComparisonAccessing SQL from programs
How it works
Example

Embedded SQL

SQL statements written in a host language (C, COBOL) with EXEC SQL; a precompiler converts them to library calls

EXEC SQL SELECT name INTO :n FROM student WHERE roll = :r;

Dynamic SQL

SQL string built and prepared at run time

PREPARE stmt FROM @q; EXECUTE stmt;

JDBC

Java API: DriverManager, Connection, PreparedStatement, ResultSet

See the program below

SQLJ

Static SQL embedded in Java using #sql clauses, checked at compile time

#sql { SELECT name INTO :n FROM student WHERE roll = :r };

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.

Example

Using JDBC safely: a Java program uses PreparedStatement ps = con.prepareStatement("SELECT * FROM student WHERE roll = ?"); ps.setInt(1, 101); — the placeholder prevents SQL injection.

Exam tip

Use prepared statements with parameters instead of building SQL strings.

Key terms

Subquery
Query nested inside another query
Correlated subquery
Subquery referring to the outer query's row
View
Virtual relation defined by a query
Trigger
Procedure run automatically on a data change
JDBC
Java API for connecting to relational databases

Quick revision

  • DDL: CREATE, ALTER, DROP, TRUNCATE; query structure and evaluation order.
  • UNION, INTERSECT, EXCEPT; NULL and three-valued logic; aggregates, GROUP BY, HAVING.
  • IN, EXISTS, ALL, SOME, derived tables; INSERT…SELECT, UPDATE with CASE, DELETE.
  • Joins; views and materialised views; transactions; constraints; data types; schemas; GRANT, REVOKE, roles.
  • Functions, procedures, triggers; embedded SQL, cursors, dynamic SQL, JDBC, SQLJ.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.Distinguish UNION and UNION ALL.
  2. Q2.What is three-valued logic?
  3. Q3.Distinguish WHERE and HAVING.
  4. Q4.What is a correlated subquery?
  5. Q5.Distinguish a function and a stored procedure.
  6. Q6.What is a host variable in embedded SQL?

Long-answer questions

  1. Q1.Explain set operations, null values and aggregate functions with SQL examples.
  2. Q2.Explain nested subqueries with examples.
  3. Q3.Explain views, transactions, integrity constraints and authorisation in SQL.
  4. Q4.Explain functions, procedures, triggers and JDBC with examples.

Stuck on this unit?

Message SBS on WhatsApp for help with Relational Database Management System, or to ask about studying M.Sc IT at Synetic.

Planning your M.Sc IT in Ludhiana? See the M.Sc IT at SBS →

WhatsApp us