Introduction to Structured Query Language (SQL)

Written and reviewed by Rohit Singh, Computer Science & Commerce faculty at UniDrill (MCA, Osmania University · 6 years teaching). Based on NCERT Informatics Practices Class XI, Chapter 8.

You say what you want, not how to get it

With a file you must write a program that loops, compares and prints. With a database you write one sentence: "give me the names of students born in 2003". SQL (Structured Query Language) is the language for that sentence. You describe what data you want, and the DBMS works out how to fetch it. SQL is the standard language of relational databases such as MySQL, Oracle and SQL Server, and although it is called a query language it can also define tables, change data and set constraints.

Four habits of SQL are tested directly:

The chapter works on the StudentAttendance database from Class XI Chapter 7, and then on an EMPLOYEE table for queries.

Data types and constraints

A data type decides what values a column can hold and what you can do with them. Arithmetic works on numbers, not on text.

Why GUID is CHAR(12) and not INT. A guardian ID is a 12 digit code of fixed length, and you will never add or average it. Identifiers, phone numbers and similar codes are labels, not quantities, so a fixed length text type fits. Roll number, on the other hand, is a small whole number, so INT is right, and a name uses VARCHAR(20) because names vary in length.

Constraints are restrictions that keep data accurate and reliable. They are optional for each column.

By default every column can take NULL, except the primary key.

Data definition: building the structure

Commands that define or change the structure of a database form Data Definition Language (DDL).

CREATE DATABASE makes the database, USE selects it as the active one, and SHOW TABLES lists its tables (empty at first). Tables come next.

Each column needs a name and a data type; constraints are optional. The number of columns is the degree of the table. DESC STUDENT; (short for DESCRIBE) then shows its structure:

Only the primary key column says NO for null, which confirms the default rule above.

ALTER TABLE changes the structure of an existing table. One command, many jobs:

Three rules for a foreign key: the referenced table must already exist, the referenced column must be part of that table's primary key, and the data type and size of both columns must match. That is why GUARDIAN is created before the STUDENT table can point at it. Note also that MODIFY always repeats the data type, even when you only want to add NOT NULL or DEFAULT.

DROP removes a table or a whole database permanently: DROP TABLE table_name; and DROP DATABASE database_name;. It cannot be undone, and dropping a database removes every table in it.

Beyond the textbook. For the exam: DROP removes the whole table, while DELETE removes some or all rows but keeps the table. In reality: the difference is also one of category. DDL commands (CREATE, ALTER, DROP) work on structure and generally cannot be rolled back, while data manipulation commands (INSERT, UPDATE, DELETE) work on rows. Why it matters: "Which command removes the table itself?" always has the answer DROP, never DELETE.

Data manipulation: INSERT, UPDATE, DELETE

A new table has structure but no rows. INSERT INTO adds a row. If you give a value for every column, in table order, you can skip the column list.

The second form names the columns it fills; the others (here GPhone) get NULL or their default. The values must follow the order of the column list. When a row has a foreign key, the referenced table must already contain the matching row, so you fill GUARDIAN before STUDENT. If a row has no guardian yet, you can supply NULL for the foreign key.

UPDATE changes values in existing rows, and DELETE removes rows. Both need a WHERE condition to pick the rows.

Pause and predict: what does UPDATE STUDENT SET GUID = '101010101010'; do if you forget the WHERE clause?

Check your answer: It changes the GUID of every row in the table to 101010101010. Without WHERE, UPDATE applies to all rows, and DELETE FROM STUDENT would empty the whole table. Always write the condition first and check which rows it selects.

The SELECT statement

SELECT retrieves data and always displays it as a table. The skeleton is:

FROM is always present. WHERE is optional and keeps only the rows that satisfy the condition. SELECT * means all columns. All the examples below use this EMPLOYEE table.

Worked example: alias and calculation. Show each employee's name as Name and annual salary as Annual Salary, for those earning 40000 or more, highest first.

DISTINCT removes repeated values. SELECT DISTINCT DeptId FROM EMPLOYEE; returns each department once: D02, D01, D04, D03, D05, five values instead of ten.

Conditions are built from relational operators (=, <, <=, >, >=, !=) and logical operators (AND, OR, NOT). Text values need quotes.

BETWEEN 20000 AND 45000 is the same as Salary >= 20000 AND Salary <= 45000. The bounds are inclusive, which is why Shabbir with exactly 45000 appears. IN is a shorter way to write a chain of ORs.

Pause and predict: which employees does SELECT Ename FROM EMPLOYEE WHERE Salary BETWEEN 45000 AND 50000; return?

Check your answer: Shabbir (45000), Sanya (48000) and Tanya (50000). Both ends are included, so Shabbir and Tanya, who earn exactly the two boundary amounts, are part of the result.

Beyond the textbook. For the exam: AND needs both conditions true and OR needs either. In reality: when a WHERE clause mixes the two without brackets, AND is evaluated first. WHERE DeptId = 'D01' OR DeptId = 'D02' AND Salary > 40000 returns all D01 employees (Kritika, Shabbir, Vergese) plus only the D02 employee with a high salary (Sanya), which is probably not what you meant. Why it matters: use brackets to state your intention.

ORDER BY sorts the output. Ascending is the default, DESC reverses, and you can sort on a second column to break ties: ORDER BY DeptId, Salary DESC sorts by department and, inside each department, highest salary first.

NULL means unknown or not applicable. It is not zero, and any arithmetic with NULL gives NULL (5 + NULL is NULL). You cannot test for NULL with =; you use IS NULL and IS NOT NULL.

Pattern matching with LIKE. When you do not know the whole value, = is useless. LIKE uses two wildcards: percent (%) stands for any number of characters, including none, and underscore (_) stands for exactly one character.

Worked example: write the query. List name and salary of employees whose name has exactly five letters and ends in "anya", in decreasing salary order.

The result is Tanya 50000 and then Sanya 48000. The wrong step is LIKE 'anya', which matches only a name that is exactly "anya".

Beyond the textbook. For the exam: wildcards are % and _, and values are compared as typed. In reality: in MySQL's default setup comparisons on text are not case sensitive, so WHERE Ename = 'AALIYA' still returns Aaliya. Why it matters: a wrong case in a text value usually does not cause an error, but the data inside quotes must still match in spelling.

Command sheet

Exam traps

60-second revision

Where this chapter connects

The tables and keys here are the ones designed in Database Concepts. Class XII extends the same SELECT statement with built in functions, GROUP BY and joins in Querying and SQL Functions, and the other Class XII SQL chapter is Structured Query Language (SQL).