Structured Query Language — Study Notes

1. Introduction

Structured Query Language, commonly called SQL, is the standard language for defining, manipulating and querying data in a relational database. SQL is used to create database objects such as tables, to insert, update and delete data, and most importantly to ask questions of the data through queries. Although SQL is pronounced "sequel" or "S-Q-L", it is not a general-purpose programming language; it is a declarative language in which you state what you want, and the DBMS decides how to retrieve it.

SQL statements are classified into broad groups. DDL (Data Definition Language) includes CREATE, ALTER, DROP and RENAME, which define and modify the structure of database objects. DML (Data Manipulation Language) includes INSERT, UPDATE and DELETE, which change the data. DQL (Data Query Language) is represented by the SELECT statement, which retrieves data. Additional groups cover transaction control (COMMIT, ROLLBACK) and access control (GRANT, REVOKE).

This chapter uses a small STUDENT database to demonstrate the language. A typical STUDENT table holds columns such as RollNo, Name, Class, City and Marks. Each SQL topic is illustrated with real, executable SQL statements. SQL questions are among the most heavily weighted in the board examination, covering the exact output of queries, writing queries from requirements, and identifying the output of a given result table.

2. Creating and Using a Database

Before tables can be created, the database must exist and be selected. The CREATE DATABASE statement creates a new database, and the USE statement selects it for the current session.

CREATE DATABASE school;
USE school;

In SQLite, the database is a file, so these statements are handled differently, but the standard SQL syntax is used in examinations.

3. DDL: Creating and Modifying Tables

The CREATE TABLE statement defines a new table with its columns, data types and constraints. Common SQL data types include INT for integers, VARCHAR(n) for variable-length strings up to n characters, DATE for dates, FLOAT for decimals, and CHAR(n) for fixed-length strings.

CREATE TABLE student (
  RollNo INT PRIMARY KEY,
  Name VARCHAR(30),
  Class VARCHAR(10),
  City VARCHAR(20),
  Marks FLOAT
);

The ALTER TABLE statement changes the structure of an existing table. It can add a new column, drop a column, or modify a column definition.

ALTER TABLE student ADD COLUMN Phone VARCHAR(10);
ALTER TABLE student DROP COLUMN City;
ALTER TABLE student MODIFY Marks INT;

The DROP TABLE statement removes an entire table together with its data, and RENAME TABLE changes the name of a table.

DROP TABLE student;
RENAME TABLE student TO pupil;

4. DML: Inserting, Updating and Deleting

The INSERT statement adds rows to a table. The INTO clause names the table, and the VALUES clause supplies the data, one set per row.

INSERT INTO student VALUES (1, 'Aarav', 'XII', 'Delhi', 85);
INSERT INTO student VALUES (2, 'Bhavna', 'XII', 'Mumbai', 92);
INSERT INTO student VALUES (3, 'Chirag', 'XII', 'Delhi', 78);

If only some columns must be filled, their names can be listed explicitly, and remaining columns take their DEFAULT or NULL values.

INSERT INTO student (RollNo, Name) VALUES (4, 'Disha');

The UPDATE statement modifies existing rows. The SET clause assigns new values, and the WHERE clause selects which rows change. Without a WHERE clause, all rows are updated, which is a common cause of mistakes.

UPDATE student SET Marks = 88 WHERE RollNo = 3;

The DELETE statement removes rows from a table. As with UPDATE, the WHERE clause controls which rows are deleted; omitting it deletes every row.

DELETE FROM student WHERE RollNo = 4;

5. SELECT: The Query Statement

The SELECT statement retrieves data from tables. Its basic form lists the columns to display and the table to read. The asterisk selects all columns.

SELECT * FROM student;
SELECT Name, Marks FROM student;

5.1 SELECT with WHERE

The WHERE clause filters rows based on conditions. Comparison operators include =, <>, <, >, <= and >=. Logical operators AND, OR and NOT combine conditions.

SELECT Name FROM student WHERE Marks > 80;
SELECT Name FROM student WHERE City = 'Delhi' AND Marks > 80;
SELECT Name FROM student WHERE City = 'Delhi' OR City = 'Mumbai';

5.2 SELECT with DISTINCT

The DISTINCT keyword removes duplicate values from the result, showing each distinct value once.

SELECT DISTINCT City FROM student;

5.3 SELECT with ORDER BY

The ORDER BY clause sorts the result. It defaults to ascending order; DESC gives descending order, and ASC is explicit ascending. Multiple columns can be specified for sorting.

SELECT Name, Marks FROM student ORDER BY Marks DESC;
SELECT Name, City, Marks FROM student ORDER BY City, Marks DESC;

5.4 SELECT with Aggregate Functions

Aggregate functions compute a single value from a column. COUNT counts rows (COUNT() counts all rows, COUNT(column) counts non-null values), SUM adds values, AVG computes the average, MAX finds the largest value and MIN* finds the smallest.

SELECT COUNT(*) FROM student;
SELECT AVG(Marks) FROM student;
SELECT MAX(Marks), MIN(Marks) FROM student;
SELECT SUM(Marks) FROM student WHERE City = 'Delhi';

5.5 SELECT with GROUP BY

The GROUP BY clause groups rows that share a common column value so that aggregate functions apply to each group separately. It answers questions like "average marks per city".

SELECT City, AVG(Marks) FROM student GROUP BY City;
SELECT City, COUNT(*) FROM student GROUP BY City;

The HAVING clause filters groups after aggregation, just as WHERE filters individual rows before grouping. HAVING is used only with GROUP BY.

SELECT City, AVG(Marks) FROM student GROUP BY City HAVING AVG(Marks) > 80;

5.6 SELECT with LIMIT and ALIASES

The LIMIT clause restricts the number of rows returned. Aliases rename a column or table temporarily in the result using the AS keyword.

SELECT Name, Marks AS Score FROM student LIMIT 2;

6. Operators in SQL

SQL supports arithmetic operators (+, -, *, /, %) usable inside SELECT for computed columns, comparison operators, logical operators, the IN operator to test membership in a set, BETWEEN for a range, LIKE for pattern matching, and IS NULL / IS NOT NULL to test for missing values.

SELECT Name, Marks + 5 AS Bonus_Marks FROM student;
SELECT Name FROM student WHERE City IN ('Delhi', 'Mumbai');
SELECT Name FROM student WHERE Marks BETWEEN 80 AND 90;
SELECT Name FROM student WHERE Name LIKE 'A%';
SELECT Name FROM student WHERE Marks IS NULL;

6.1 The LIKE Operator

LIKE matches patterns with wildcard characters. The percent sign % matches any sequence of zero or more characters, and the underscore _ matches exactly one character. 'A%' matches names beginning with A, '%a%' matches names containing the letter a, and 'A__' matches names of exactly three characters starting with A.

7. Joins

A join combines rows from two or more tables based on a related column. The most common form is the inner join, which returns rows where the join condition is satisfied. A natural join joins tables on columns with the same name. The outer join (LEFT/RIGHT) additionally includes unmatched rows with NULLs in the missing columns.

CREATE TABLE marks (
  RollNo INT,
  Subject VARCHAR(15),
  Obtained INT
);

INSERT INTO marks VALUES (1, 'CS', 90), (1, 'Maths', 88), (2, 'CS', 95);

SELECT student.Name, marks.Subject, marks.Obtained
FROM student
INNER JOIN marks ON student.RollNo = marks.RollNo;

Joins require a meaningful relationship, normally a primary key matched with a foreign key. Without a join condition, the query produces a Cartesian product of all row combinations.

8. SQL Functions

Besides aggregates, SQL provides scalar functions that act on single values. Common string functions include UPPER(), LOWER(), LENGTH(), SUBSTRING(), TRIM() and CONCAT(). Numeric functions include ROUND(), ABS() and MOD().

SELECT UPPER(Name) FROM student;
SELECT LENGTH(Name) FROM student;
SELECT CONCAT(Name, ' - ', City) FROM student;
SELECT ROUND(AVG(Marks), 2) FROM student;

9. Views and Indexes

A view is a virtual table defined by a stored query. It appears like a table but holds no data of its own; it shows the result of its SELECT whenever queried. Views simplify complex queries and hide data. An index speeds up searches on a column at the cost of extra storage and slower writes.

CREATE VIEW top_students AS
SELECT Name, Marks FROM student WHERE Marks >= 80;

SELECT * FROM top_students;

Quick Revision Tables

Table 1: SQL Statement Groups

Table 2: SQL Clauses and Their Purpose

Mind Map

Important Diagrams (SVG)

Diagram 1: SQL Query Execution Order

Diagram 2: Inner Join Between Two Tables

Common Mistakes

1. Updating or deleting without WHERE: UPDATE and DELETE without a WHERE clause affect every row of the table.
2. Using HAVING without GROUP BY: HAVING is meant for filtering groups; using it alone or as a substitute for WHERE is wrong.
3. Writing WHERE instead of HAVING for aggregates: Conditions on aggregate results like AVG(Marks) > 80 must be in HAVING, not WHERE.
4. Forgetting that % in LIKE matches many characters: 'A%' matches any string starting with A, not just length 2.
5. Mismatched data types in INSERT: Inserting a string into an INT column or exceeding VARCHAR length causes errors.
6. Cartesian product without join condition: Joining tables without ON produces every row combination, usually an enormous wrong result.
7. Confusing COUNT(*) with COUNT(column): COUNT(*) counts all rows; COUNT(column) counts only non-null values of that column.
8. Aliasing after WHERE: An alias defined in SELECT cannot be used in the same query's WHERE clause.

Exam Tips

1. Write every query in the correct order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
2. Memorise aggregate functions: COUNT, SUM, AVG, MAX, MIN and where each is applicable.
3. Practise exact output questions: trace what SELECT * shows after a series of INSERT, UPDATE and DELETE statements.
4. Know the LIKE wildcards: % matches any sequence, _ matches exactly one character.
5. Distinguish WHERE from HAVING and state that WHERE filters rows before grouping.
6. Write joins explicitly with ON and relate them to the primary key-foreign key relationship.
7. Practise GROUP BY queries with an aggregate per group, as they appear frequently as 3-mark questions.

Conclusion

SQL turns the relational concepts of the previous chapter into a practical, powerful language. Data definition statements create and alter the schema, data manipulation statements maintain the records, and the SELECT statement extracts exactly the information required. Clauses such as WHERE, GROUP BY, HAVING and ORDER BY give queries precision, while aggregate functions summarise columns and joins combine tables across relationships. Operators like IN, BETWEEN and LIKE extend the expressiveness of conditions. Together these tools let a programmer store, retrieve and analyse data with confidence. The next chapter shifts to the world of computer networks, where data travels between machines rather than resting in a single database.