Structured Query Language (SQL) — Notes

SQL is the standard, declarative language of relational databases such as MySQL.

• DDL (structure): CREATE, ALTER, DROP.
• DML (rows): INSERT, UPDATE, DELETE, SELECT.

Data types: CHAR(n) fixed length (padded), VARCHAR(n) variable length, INT, FLOAT, DATE ('YYYY-MM-DD').

Constraints: NOT NULL (value required), UNIQUE (no repeats; several NULLs allowed), PRIMARY KEY (unique and not null; one per table).

CREATE DATABASE vidyalaya;
SHOW DATABASES;
USE vidyalaya;
SHOW TABLES;
DESCRIBE STUDENT;
ALTER TABLE STUDENT ADD Phone CHAR(10);
ALTER TABLE STUDENT DROP Phone;
ALTER TABLE ROOM ADD PRIMARY KEY (RoomNo);
ALTER TABLE ROOM DROP PRIMARY KEY;
DROP TABLE ROOM;
DROP DATABASE hostel;

INSERT: INSERT INTO CLUB VALUES (5, 'Art', 'Ms Das', 250); or with a column list: INSERT INTO CLUB (ClubID, ClubName) VALUES (6, 'Yoga'); (unlisted columns become NULL).

General form: SELECT columns FROM table WHERE condition ORDER BY column;

• * selects all columns; DISTINCT removes repeated rows; AS gives an alias; arithmetic (Price * Stock) is allowed.
• Relational: =, <>, >, <, >=, <=; logical: AND, OR, NOT.
• IN ('Pune', 'Nashik') matches any value in the list; BETWEEN 70 AND 90 includes both ends.
• LIKE: % any run of characters, _ exactly one character.

SELECT Name, Marks FROM STUDENT WHERE Marks BETWEEN 70 AND 90 AND City IN ('Pune', 'Nagpur') ORDER BY Name;

Name    Marks
Aarav   88
Bhavna  74
Farah   79
Hina    83

NULL means missing or unknown. Any comparison with NULL using = or <> is never true, so test with IS NULL or IS NOT NULL.

ORDER BY sorts ascending by default; DESC sorts descending. With two columns, the second sorts within ties of the first. In ascending order MySQL puts NULL first.

SELECT PName, Stock FROM PRODUCT ORDER BY Stock DESC;

PName            Stock
Gel Pen          300
Notebook         120
Sketch Pens      80
Water Bottle     45
Lunch Box        30
Extension Board  20
Desk Lamp        12
Geometry Box     NULL

In descending order the NULL stock comes last.

COUNT, SUM, AVG, MIN, MAX give one value from many rows and skip NULL (only COUNT(*) counts every row). MySQL shows averages with decimal places (for example 85.0000).

GROUP BY col gives one result row per group; HAVING filters groups and may use aggregates; WHERE filters rows before grouping and may not.

Written order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

SELECT City, COUNT(*), MAX(Marks) FROM STUDENT WHERE Stream <> 'Commerce' GROUP BY City HAVING COUNT(*) >= 2 ORDER BY City;

City    COUNT(*)  MAX(Marks)
Nashik  2         95
Pune    2         88

Cartesian product: SELECT * FROM A, B; pairs every row of A with every row of B. Degree = degree(A) + degree(B); cardinality = cardinality(A) × cardinality(B).

Equi-join: WHERE A.col = B.col; the common column appears twice with SELECT *.

Natural join: A NATURAL JOIN B matches the column with the same name and shows it once.

SELECT S.Name, C.ClubName, C.Fee FROM STUDENT S, CLUB C WHERE S.ClubID = C.ClubID AND S.Marks > 80 ORDER BY C.Fee DESC, S.Name;

Name    ClubName  Fee
Gaurav  Robotics  500
Aarav   Music     300
Diya    Debate    200
Hina    Debate    200

UPDATE table SET col = value WHERE …; changes rows; DELETE FROM table WHERE …; removes rows (all rows without WHERE; the table remains).