Revision Summary: Structured Query Language (SQL)
Chapter at a glance
- SQL is the most popular query language used by RDBMS such as MySQL; statements use descriptive English words, are case-insensitive and end with a semicolon.
- Data types (CHAR(n), VARCHAR(n), INT, FLOAT, DATE) decide the kind of values an attribute can hold and the operations permitted on them.
- Constraints (NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY) enforce restrictions to ensure correctness of data.
- DDL statements (CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP) are used to define, modify or remove database/table structures.
- DML statements (INSERT, UPDATE, DELETE) are used to populate, modify or remove records from tables.
- SELECT is used for data retrieval; it supports clauses such as WHERE, DISTINCT, ORDER BY, GROUP BY, HAVING and pattern matching with LIKE.
- Single-row functions (numeric, string, date) operate on one value and return one value; aggregate functions (MAX, MIN, AVG, SUM, COUNT) operate on a group of rows.
- Relations can be combined using UNION (∪), INTERSECT (∩), MINUS (−), Cartesian product (X) and JOIN; N−1 JOINs are required to combine N tables on equality conditions.
Key terms and definitions
- RDBMS: Relational Database Management System that stores data in relations (tables) and allows creation, storage, retrieval and manipulation of data through queries.
- SQL: Structured Query Language – the most popular query language for major RDBMS; statements comprise descriptive English words, are case-insensitive and do not require specifying how to obtain data.
- DDL (Data Definition Language): SQL statements used for defining, modifying and deleting relation schemas (CREATE, ALTER, DROP).
- DML (Data Manipulation Language): SQL statements used for insertion, modification or removal of data (INSERT, UPDATE, DELETE).
- Data type: Indicates the type of data value an attribute can hold and decides operations permitted on that data.
- Constraint: Restriction on data values an attribute can have to ensure correctness (NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY).
- PRIMARY KEY: Column (or set of columns) that can uniquely identify each row/record in a table.
- FOREIGN KEY: Column that refers to the value of an attribute defined as primary key in another table.
- Composite primary key: Primary key formed by more than one attribute.
- NULL: Special value representing missing/unknown/not applicable data; different from zero.
- Single-row (Scalar) function: Function applied on a single value that returns a single value.
- Aggregate (Multiple-row) function: Function applied on a group of rows that returns a single value.
- Cartesian product (X): Operation that combines every tuple of one relation with every tuple of another, producing all possible pairs.
- JOIN: Operation that combines tuples from two tables on specified conditions (usually equality on common attributes); NATURAL JOIN removes the redundant common column.
Syntax and constructs
sql
CREATE DATABASE StudentAttendance;
sql
USE StudentAttendance;
sql
CREATE TABLE STUDENT(
RollNumber INT PRIMARY KEY,
SName VARCHAR(20) NOT NULL,
SDateofBirth DATE NOT NULL,
GUID CHAR(12) FOREIGN KEY REFERENCES GUARDIAN(GUID));
sql
DESCRIBE STUDENT; -- or DESC STUDENT;
sql
ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
ALTER TABLE ATTENDANCE ADD PRIMARY KEY(AttendanceDate, RollNumber);
ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);
ALTER TABLE GUARDIAN ADD UNIQUE(GPhone);
ALTER TABLE GUARDIAN ADD Income INT;
ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);
ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;
ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT '2000-05-15';
ALTER TABLE GUARDIAN DROP income;
ALTER TABLE GUARDIAN DROP PRIMARY KEY;
sql
DROP TABLE tablename;
DROP DATABASE databasename;
sql
INSERT INTO GUARDIAN VALUES(444444444444,'Amit Ahuja',5711492685,'G-35,Ashok Vihar,Delhi');
INSERT INTO GUARDIAN(GUID,GName,GAddress) VALUES(333333333333,'Danny Dsouza','S-13,Ashok Village,Daman');
sql
UPDATE STUDENT SET GUID=101010101010 WHERE RollNumber=3;
sql
DELETE FROM STUDENT WHERE RollNumber=2;
sql
SELECT SName,SDateofBirth FROM STUDENT WHERE RollNumber=1;
SELECT DISTINCT DeptId FROM EMPLOYEE;
SELECT EName AS Name,Salary*12 AS 'Annual Income' FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE Salary BETWEEN 20000 AND 50000;
SELECT * FROM EMPLOYEE WHERE DeptId IN('D01','D02','D04');
SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';
SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
SELECT * FROM EMPLOYEE ORDER BY Salary DESC;
sql
SELECT CustID,COUNT(*) FROM SALE GROUP BY CustID HAVING COUNT(*)>1;
sql
SELECT * FROM DANCE D,MUSIC M WHERE D.Name=M.Name; -- JOIN via WHERE
SELECT * FROM UNIFORM NATURAL JOIN COST; -- NATURAL JOIN
sql
SELECT POWER(2,3), ROUND(2912.564,1), MOD(21,2);
SELECT UCASE('informatics'), MID('informatics',3,4), LENGTH('informatics');
SELECT NOW(), MONTHNAME('2003-11-28'), DAYNAME('2019-07-11');
sql
SELECT MAX(Price), MIN(Price), AVG(Price), SUM(Price), COUNT(*) FROM INVENTORY;
Algorithms and worked logic
-
Creating and populating a database:
1. CREATE DATABASE.
2. USE the database.
3. Identify data types and constraints for each attribute of every table.
4. CREATE TABLE (optionally without constraints).
5. ALTER TABLE to add PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, new columns, modify data types.
6. INSERT records into referenced tables first, then referencing tables.
7. Verify with SELECT * or DESCRIBE.
-
Retrieving data with conditions and grouping:
1. Write SELECT list of columns / *.
2. FROM clause lists tables (apply Cartesian product if >1 table).
3. WHERE clause applies row-level conditions (relational, logical, BETWEEN, IN, LIKE, IS NULL).
4. GROUP BY groups rows on common column values.
5. HAVING filters groups.
6. ORDER BY sorts the final result (ASC/DESC).
-
Joining two relations:
1. List both tables in FROM (Cartesian product).
2. Add equality condition on common attribute(s) in WHERE or use explicit JOIN … ON / NATURAL JOIN.
3. Use table aliases to qualify common columns.
4. For N tables, N−1 joins are required on equality conditions.
-
Applying functions:
- Single-row: apply directly on column/expression inside SELECT/WHERE.
- Aggregate: combine with GROUP BY when per-group results are needed; use HAVING for group conditions.
Common errors and exam pitfalls
- Forgetting the semicolon at the end of every statement.
- Omitting WHERE clause in UPDATE or DELETE (affects all rows).
- Using = with NULL instead of IS NULL / IS NOT NULL.
- Writing column names with incorrect case or forgetting quotes around string/date literals.
- Applying aggregate functions without GROUP BY when both grouped and non-grouped columns are selected.
- Forgetting that FOREIGN KEY values must already exist in the referenced table.
- Using Cartesian product without a join condition when two tables are listed.
- Incorrect placement of table aliases or using original table name after an alias is declared.
- Missing the data-type specification when using MODIFY … NOT NULL or DEFAULT.
- Assuming DROP can be undone; it permanently removes objects.
- Overlooking that PRIMARY KEY implies NOT NULL and UNIQUE; composite primary key must be declared together.