SQL: Defining and Querying Data

SQL stands for Structured Query Language. It is the standard language that every relational database — MySQL, Oracle, PostgreSQL, SQL Server — understands. The important idea is that you do not tell SQL how to search. You describe what you want, and the database engine decides how to fetch it. That is why SQL is called a non-procedural or declarative language, unlike Python where you write the loop yourself.

Three mechanical rules first, because they cost marks when forgotten:

• Every SQL statement ends with a semicolon.
• Keywords are not case-sensitive. SELECT, select and Select all work. Writing keywords in capitals is only a readability convention — a good one to keep in the exam.
• Text values go inside single quotes: 'Delhi'. Numbers do not: 52000.

SQL is divided into sub-languages. Two of them are on your syllabus.

The one line that decides marks in this question: DDL changes the skeleton, DML changes the contents. DROP TABLE is DDL because it removes the table itself. DELETE is DML because it removes rows and leaves the empty table standing. Note that SELECT is grouped under DML in most Class 12 material; a few books list it separately as DQL (Data Query Language). Either grouping is accepted as long as you are consistent.

Database-level commands. A table has to live inside a database, and MySQL needs to be told which database you are working in. These five commands are the start of every practical session.

Worked example. Everything below was run against MySQL 8.0.41. Two databases are created so you can watch one of them disappear:

CREATE DATABASE cs12_sql_basics;
CREATE DATABASE demo_practice;
SHOW DATABASES;

Output:

+-------------------------------+
| Database                      |
+-------------------------------+
| cs12_database_concepts        |
| cs12_sql_basics               |
| cs12_sql_joins_and_aggregates |
| demo_practice                 |
| information_schema            |
| mysql                         |
| performance_schema            |
| sys                           |
+-------------------------------+

Your list will look different, because SHOW DATABASES lists every database on that server. The last four — information_schema, mysql, performance_schema and sys — are MySQL's own system databases. They are always there and you must never modify them. The cs12_ entries were other practice databases that already existed on this machine.

Now remove the throwaway one:

DROP DATABASE demo_practice;
SHOW DATABASES;

+-------------------------------+
| Database                      |
+-------------------------------+
| cs12_database_concepts        |
| cs12_sql_basics               |
| cs12_sql_joins_and_aggregates |
| information_schema            |
| mysql                         |
| performance_schema            |
| sys                           |
+-------------------------------+

demo_practice is gone. Understand what that means: DROP DATABASE deletes the database together with every table and every row inside it, with no confirmation and no undo.

Next, enter the database you want to work in and confirm where you are:

USE cs12_sql_basics;
SELECT DATABASE() AS current_db;

+-----------------+
| current_db      |
+-----------------+
| cs12_sql_basics |
+-----------------+

SHOW TABLES;

0 rows returned — a brand-new database contains no tables at all. We build one in the next section.

Two errors worth recognising. Both were produced deliberately. Creating a database that already exists is rejected — the failure is reported against the second line, the one that repeated the name:

CREATE DATABASE cs12_tmp_check;
CREATE DATABASE cs12_tmp_check;
ERROR 1007 (HY000) at line 2: Can't create database 'cs12_tmp_check'; database exists

And running a table command before choosing a database gives the single most common beginner error — you forgot USE:

SHOW TABLES;
ERROR 1046 (3D000) at line 1: No database selected

A table is a grid of rows and columns. Before MySQL will build one, you must state two things for every column: its data type — what kind of value is allowed — and any constraints — what values are forbidden.

The five data types on your syllabus.

char(n) versus varchar(n) — the difference that carries marks. Both hold text, so students assume they are interchangeable. They are not, and the difference is real storage. Here is a demonstration that was actually run. Two columns, same width, and the same value is inserted into both — once plain, once with five trailing spaces:

CREATE TABLE PADDEMO (c CHAR(10), v VARCHAR(10));
INSERT INTO PADDEMO VALUES ('Delhi', 'Delhi');
INSERT INTO PADDEMO VALUES ('Delhi     ', 'Delhi     ');
SELECT CONCAT('[', c, ']') AS char_value,
       CONCAT('[', v, ']') AS varchar_value,
       LENGTH(c) AS char_len,
       LENGTH(v) AS varchar_len
FROM PADDEMO;

Result:

+------------+---------------+----------+-------------+
| char_value | varchar_value | char_len | varchar_len |
+------------+---------------+----------+-------------+
| [Delhi]    | [Delhi]       |        5 |           5 |
| [Delhi]    | [Delhi     ]  |        5 |          10 |
+------------+---------------+----------+-------------+

Read the second row carefully. Both columns were handed 'Delhi' plus five spaces. The varchar column kept all ten characters. The char column returned only Delhi. That is because a char column pads every value out to its full declared width when storing it, and strips the trailing spaces again when reading it back. The padding is invisible to you, but the space is still reserved on disk.

So the examinable statement is: char(10) costs the same for 'Delhi' as it does for 'Chandigarh', because it is fixed-length. varchar(10) costs only what you store. In exchange, char is very slightly faster because every row is the same size. Rule of thumb: use char(n) when every value genuinely has the same length — a grade letter, a two-letter state code, a 6-digit PIN code — and varchar(n) for anything that varies, such as names, cities or email addresses.

Note: CONCAT and LENGTH above are helper functions used only to make the padding visible. They are not part of this chapter's syllabus.

Constraints. A constraint is a rule the database enforces itself. Even if the application code has a bug, bad data is refused at the door.

The compact way to remember it: PRIMARY KEY = UNIQUE + NOT NULL. A table may have only one primary key, although that key is allowed to span more than one column.

Worked example — building the table used for the rest of this chapter. A company called Nova keeps employee records:

CREATE TABLE EMPLOYEE (
  EmpID  INT,
  Name   VARCHAR(25) NOT NULL,
  City   CHAR(12),
  Dept   VARCHAR(15),
  Salary FLOAT,
  DOJ    DATE,
  Email  VARCHAR(35) UNIQUE,
  PRIMARY KEY (EmpID)
);
DESCRIBE EMPLOYEE;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EmpID  | int         | NO   | PRI | NULL    |       |
| Name   | varchar(25) | NO   |     | NULL    |       |
| City   | char(12)    | YES  |     | NULL    |       |
| Dept   | varchar(15) | YES  |     | NULL    |       |
| Salary | float       | YES  |     | NULL    |       |
| DOJ    | date        | YES  |     | NULL    |       |
| Email  | varchar(35) | YES  | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Read the DESCRIBE output like a checklist. Key reads PRI for the primary key and UNI for the unique column. And look at EmpID: its Null column says NO even though we never wrote NOT NULL there. Declaring it the PRIMARY KEY did that automatically — that is the NOT NULL half of the primary key showing up.

Watch the constraints refuse bad data. Each of these was run against the finished table once the twelve rows of the next section were in place, and each was rejected:

INSERT INTO EMPLOYEE VALUES (101,'Nikhil Verma','Surat','HR',50000,'2024-01-05','nikhil.verma@nova.in');
ERROR 1062 (23000) at line 1: Duplicate entry '101' for key 'employee.PRIMARY'

INSERT INTO EMPLOYEE VALUES (113,NULL,'Surat','HR',50000,'2024-01-05','nikhil.verma@nova.in');
ERROR 1048 (23000) at line 1: Column 'Name' cannot be null

INSERT INTO EMPLOYEE VALUES (113,'Nikhil Verma','Surat','HR',50000,'2024-01-05','diya.nair@nova.in');
ERROR 1062 (23000) at line 1: Duplicate entry 'diya.nair@nova.in' for key 'employee.Email'

INSERT INTO EMPLOYEE (EmpID,Name) VALUES (NULL,'Ghost User');
ERROR 1048 (23000) at line 1: Column 'EmpID' cannot be null

Four different rules, four different refusals: duplicate primary key, missing NOT NULL value, duplicate UNIQUE value, and NULL in a primary key. Note the last one especially — a primary key rejects NULL, but a UNIQUE column accepts it, which we prove in the next section.

The table exists but it is empty. INSERT is the DML command that puts rows in, and it comes in three forms.

Form 1 — all columns, in order. You give one value for every column, in exactly the order DESCRIBE showed:

INSERT INTO EMPLOYEE VALUES (101,'Aarav Sharma','Delhi','Sales',52000,'2019-06-15','aarav.sharma@nova.in');

Form 2 — chosen columns. You name the columns first, then supply matching values. Any column you leave out is filled with NULL:

INSERT INTO EMPLOYEE (EmpID, Name, City, Dept) VALUES (111,'Rehan Ali','Hyderabad','IT');

Form 3 — several rows at once. One statement, one semicolon, rows separated by commas. A second table is created first, so the example can actually be run:

CREATE TABLE TRAINEE (
  TID      INT,
  TName    VARCHAR(20) NOT NULL,
  Stream   CHAR(10),
  Stipend  FLOAT,
  JoinDate DATE
);
INSERT INTO TRAINEE VALUES (1,'Ananya Das','CS',12000,'2024-01-08'),
                           (2,'Farhan Khan','ECE',10000,'2024-02-19'),
                           (3,'Pooja Bhatt','CS',11000,'2024-03-04');
SELECT * FROM TRAINEE;

+------+-------------+--------+---------+------------+
| TID  | TName       | Stream | Stipend | JoinDate   |
+------+-------------+--------+---------+------------+
|    1 | Ananya Das  | CS     |   12000 | 2024-01-08 |
|    2 | Farhan Khan | ECE    |   10000 | 2024-02-19 |
|    3 | Pooja Bhatt | CS     |   11000 | 2024-03-04 |
+------+-------------+--------+---------+------------+

Worked example — the dataset for the whole chapter. Ten full rows were inserted with Form 1 and two partial rows with Form 2, then read back:

SELECT * FROM EMPLOYEE;

+-------+--------------+-----------+----------+--------+------------+----------------------+
| EmpID | Name         | City      | Dept     | Salary | DOJ        | Email                |
+-------+--------------+-----------+----------+--------+------------+----------------------+
|   101 | Aarav Sharma | Delhi     | Sales    |  52000 | 2019-06-15 | aarav.sharma@nova.in |
|   102 | Diya Nair    | Kochi     | HR       |  47500 | 2020-01-10 | diya.nair@nova.in    |
|   103 | Rohan Mehta  | Mumbai    | Sales    |  61000 | 2018-03-05 | rohan.mehta@nova.in  |
|   104 | Ishita Rao   | Bengaluru | IT       |  78000 | 2021-07-19 | ishita.rao@nova.in   |
|   105 | Kabir Singh  | Delhi     | IT       |  69500 | 2020-11-23 | kabir.singh@nova.in  |
|   106 | Meera Iyer   | Chennai   | HR       |  45000 | 2022-02-14 | meera.iyer@nova.in   |
|   107 | Arjun Patel  | Ahmedabad | Sales    |  58000 | 2017-09-01 | arjun.patel@nova.in  |
|   108 | Sanya Gupta  | Delhi     | Sales    |  52000 | 2023-05-08 | sanya.gupta@nova.in  |
|   109 | Vikram Bose  | Kolkata   | IT       |  84000 | 2016-12-30 | vikram.bose@nova.in  |
|   110 | Neha Joshi   | Pune      | Accounts |  49000 | 2022-08-25 | neha.joshi@nova.in   |
|   111 | Rehan Ali    | Hyderabad | IT       |   NULL | NULL       | NULL                 |
|   112 | Tara Menon   | Jaipur    | Accounts |   NULL | NULL       | NULL                 |
+-------+--------------+-----------+----------+--------+------------+----------------------+

Rows 111 and 112 are the Form 2 inserts. We supplied only four columns, so Salary, DOJ and Email came out as NULL. Two things follow from that, and both are examinable. First, this is why partial inserts fail on a NOT NULL column — there would be nothing to put there. Second, look at Email: it is declared UNIQUE, yet two rows both hold NULL and both were accepted. UNIQUE forbids duplicate values, and NULL is not a value, so the rule never fires.

Dates must be in order. Writing an Indian-style date is rejected outright:

INSERT INTO TRAINEE VALUES (4,'Ravi Kumar','ME',9000,'15-06-2024');
ERROR 1292 (22007) at line 1: Incorrect date value: '15-06-2024' for column 'JoinDate' at row 1

ALTER TABLE — reshaping a table that already has data. This is DDL. Your syllabus needs four operations, and all four are shown running below.

Adding a column. The company decides to record a bonus:

ALTER TABLE EMPLOYEE ADD Bonus FLOAT;
DESCRIBE EMPLOYEE;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EmpID  | int         | NO   | PRI | NULL    |       |
| Name   | varchar(25) | NO   |     | NULL    |       |
| City   | char(12)    | YES  |     | NULL    |       |
| Dept   | varchar(15) | YES  |     | NULL    |       |
| Salary | float       | YES  |     | NULL    |       |
| DOJ    | date        | YES  |     | NULL    |       |
| Email  | varchar(35) | YES  | UNI | NULL    |       |
| Bonus  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

The new column is appended at the end, and — this is the part students forget — it is NULL for every row that already existed:

SELECT EmpID, Name, Salary, Bonus FROM EMPLOYEE WHERE EmpID IN (101,102,111);

+-------+--------------+--------+-------+
| EmpID | Name         | Salary | Bonus |
+-------+--------------+--------+-------+
|   101 | Aarav Sharma |  52000 |  NULL |
|   102 | Diya Nair    |  47500 |  NULL |
|   111 | Rehan Ali    |   NULL |  NULL |
+-------+--------------+--------+-------+

Removing a column. A Grade column is added and then thrown away again. The COLUMN keyword is optional in MySQL:

ALTER TABLE EMPLOYEE ADD Grade CHAR(1);
ALTER TABLE EMPLOYEE DROP COLUMN Grade;

Dropping a column destroys the data in it permanently — there is no recycle bin.

Removing a primary key.

ALTER TABLE EMPLOYEE DROP PRIMARY KEY;
DESCRIBE EMPLOYEE;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EmpID  | int         | NO   |     | NULL    |       |
| Name   | varchar(25) | NO   |     | NULL    |       |
| City   | char(12)    | YES  |     | NULL    |       |
| Dept   | varchar(15) | YES  |     | NULL    |       |
| Salary | float       | YES  |     | NULL    |       |
| DOJ    | date        | YES  |     | NULL    |       |
| Email  | varchar(35) | YES  | UNI | NULL    |       |
| Bonus  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Compare this against the previous DESCRIBE. The PRI against EmpID has gone, exactly as expected. But notice what did not change: Null still reads NO. Dropping the primary key removes only the key. The NOT NULL property it installed stays behind.

Adding a primary key. Notice you do not name the column in DROP PRIMARY KEY (a table has only one), but you must name it when adding:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EmpID);

That restores PRI against EmpID. This only works because EmpID values are all different. On a column holding duplicates it is refused — here is that failure, run on a scratch table:

CREATE TABLE PKDEMO (Rno INT, Nm VARCHAR(10));
INSERT INTO PKDEMO VALUES (1,'Asha'),(1,'Bina'),(2,'Chetan');
SELECT * FROM PKDEMO;

+------+--------+
| Rno  | Nm     |
+------+--------+
|    1 | Asha   |
|    1 | Bina   |
|    2 | Chetan |
+------+--------+

ALTER TABLE PKDEMO ADD PRIMARY KEY (Rno);
ERROR 1062 (23000) at line 1: Duplicate entry '1' for key 'pkdemo.PRIMARY'

DROP TABLE. This removes the rows and the structure:

SHOW TABLES;

+---------------------------+
| Tables_in_cs12_sql_basics |
+---------------------------+
| employee                  |
| paddemo                   |
| pkdemo                    |
| trainee                   |
+---------------------------+

DROP TABLE PKDEMO;
DROP TABLE PADDEMO;
SHOW TABLES;

+---------------------------+
| Tables_in_cs12_sql_basics |
+---------------------------+
| employee                  |
| trainee                   |
+---------------------------+

They are gone for good — asking again proves it:

DROP TABLE PKDEMO;
ERROR 1051 (42S02) at line 1: Unknown table 'cs12_sql_basics.pkdemo'

SELECT is the command you will write most often. Its shape never changes, and the clause order is fixed:

SELECT   which columns
FROM     which table
WHERE    which rows
ORDER BY how to sort

Get that order wrong — ORDER BY before WHERE, for example — and the query is a syntax error. SELECT picks columns, WHERE picks rows. Hold on to that sentence.

Choosing columns, and aliasing. SELECT * means every column. Naming columns instead is called projection:

SELECT Name, Dept, Salary FROM EMPLOYEE;

+--------------+----------+--------+
| Name         | Dept     | Salary |
+--------------+----------+--------+
| Aarav Sharma | Sales    |  52000 |
| Diya Nair    | HR       |  47500 |
| Rohan Mehta  | Sales    |  61000 |
| Ishita Rao   | IT       |  78000 |
| Kabir Singh  | IT       |  69500 |
| Meera Iyer   | HR       |  45000 |
| Arjun Patel  | Sales    |  58000 |
| Sanya Gupta  | Sales    |  52000 |
| Vikram Bose  | IT       |  84000 |
| Neha Joshi   | Accounts |  49000 |
| Rehan Ali    | IT       |   NULL |
| Tara Menon   | Accounts |   NULL |
+--------------+----------+--------+

An alias renames a column in the output only — the table is untouched. Use it when a heading is ugly or when the column is a calculation:

SELECT Name AS "Employee Name", Salary*12 AS Annual_Salary FROM EMPLOYEE;

+---------------+---------------+
| Employee Name | Annual_Salary |
+---------------+---------------+
| Aarav Sharma  |        624000 |
| Diya Nair     |        570000 |
| Rohan Mehta   |        732000 |
| Ishita Rao    |        936000 |
| Kabir Singh   |        834000 |
| Meera Iyer    |        540000 |
| Arjun Patel   |        696000 |
| Sanya Gupta   |        624000 |
| Vikram Bose   |       1008000 |
| Neha Joshi    |        588000 |
| Rehan Ali     |          NULL |
| Tara Menon    |          NULL |
+---------------+---------------+

Two things to take from that. The keyword AS is optional, but an alias containing a space must be quoted. And look at the last two rows: NULL * 12 gave NULL, not 0. Section 5 explains why.

Mathematical operators are +, -, *, / and % (remainder). They work inside the SELECT list and inside WHERE:

SELECT Name, Salary, Salary*0.05 AS PF, Salary-Salary*0.05 AS Take_Home
FROM EMPLOYEE WHERE Dept='IT';

+-------------+--------+------+-----------+
| Name        | Salary | PF   | Take_Home |
+-------------+--------+------+-----------+
| Ishita Rao  |  78000 | 3900 |     74100 |
| Kabir Singh |  69500 | 3475 |     66025 |
| Vikram Bose |  84000 | 4200 |     79800 |
| Rehan Ali   |   NULL | NULL |      NULL |
+-------------+--------+------+-----------+

Relational operators compare two values: =, >, <, >=, <= and <> (not equal, also written !=). Note that equality is a single = in SQL, not == as in Python:

SELECT Name, Dept, Salary FROM EMPLOYEE WHERE Salary > 60000;

+-------------+-------+--------+
| Name        | Dept  | Salary |
+-------------+-------+--------+
| Rohan Mehta | Sales |  61000 |
| Ishita Rao  | IT    |  78000 |
| Kabir Singh | IT    |  69500 |
| Vikram Bose | IT    |  84000 |
+-------------+-------+--------+

Logical operators — AND, OR, NOT — join conditions. AND needs both true; OR needs at least one:

SELECT Name, Dept, Salary FROM EMPLOYEE WHERE Dept='Sales' AND Salary>55000;

+-------------+-------+--------+
| Name        | Dept  | Salary |
+-------------+-------+--------+
| Rohan Mehta | Sales |  61000 |
| Arjun Patel | Sales |  58000 |
+-------------+-------+--------+

Precedence trap. AND is evaluated before OR, exactly as * is evaluated before +. This query looks like it asks for Delhi employees who are in Sales or HR. It does not:

SELECT Name, City, Dept FROM EMPLOYEE WHERE City='Delhi' AND Dept='Sales' OR Dept='HR';

+--------------+---------+-------+
| Name         | City    | Dept  |
+--------------+---------+-------+
| Aarav Sharma | Delhi   | Sales |
| Diya Nair    | Kochi   | HR    |
| Meera Iyer   | Chennai | HR    |
| Sanya Gupta  | Delhi   | Sales |
+--------------+---------+-------+

Diya Nair is in Kochi and Meera Iyer is in Chennai, yet both appear. MySQL read it as (City='Delhi' AND Dept='Sales') OR Dept='HR'. If you meant the other grouping, use brackets: City='Delhi' AND (Dept='Sales' OR Dept='HR'). When you mix AND with OR, always bracket.

DISTINCT removes duplicate rows. This is the point students most often get wrong, so compare three queries on the same table. Plain SELECT City returns all 12 values including Delhi three times. Now:

SELECT DISTINCT City FROM EMPLOYEE;

+-----------+
| City      |
+-----------+
| Delhi     |
| Kochi     |
| Mumbai    |
| Bengaluru |
| Chennai   |
| Ahmedabad |
| Kolkata   |
| Pune      |
| Hyderabad |
| Jaipur    |
+-----------+

Ten rows. Now add a second column:

SELECT DISTINCT City, Dept FROM EMPLOYEE;

+-----------+----------+
| City      | Dept     |
+-----------+----------+
| Delhi     | Sales    |
| Kochi     | HR       |
| Mumbai    | Sales    |
| Bengaluru | IT       |
| Delhi     | IT       |
| Chennai   | HR       |
| Ahmedabad | Sales    |
| Kolkata   | IT       |
| Pune      | Accounts |
| Hyderabad | IT       |
| Jaipur    | Accounts |
+-----------+----------+

Eleven rows, and Delhi now appears twice. That is the whole lesson: DISTINCT applies to the entire selected row, not to the column written next to it. Delhi/Sales occurred twice in the data and collapsed into one; Delhi/IT is a different row, so it survives separately. You cannot write SELECT DISTINCT City, Dept hoping to de-duplicate only the cities.

IN — shorthand for a chain of ORs.

SELECT Name, City FROM EMPLOYEE WHERE City IN ('Delhi','Mumbai','Pune');

+--------------+--------+
| Name         | City   |
+--------------+--------+
| Aarav Sharma | Delhi  |
| Rohan Mehta  | Mumbai |
| Kabir Singh  | Delhi  |
| Sanya Gupta  | Delhi  |
| Neha Joshi   | Pune   |
+--------------+--------+

NOT IN reverses it and returns the other 7 rows.

BETWEEN — a range, and it includes both ends. That inclusivity is checked constantly in exams:

SELECT Name, Salary FROM EMPLOYEE WHERE Salary BETWEEN 50000 AND 70000;

+--------------+--------+
| Name         | Salary |
+--------------+--------+
| Aarav Sharma |  52000 |
| Rohan Mehta  |  61000 |
| Kabir Singh  |  69500 |
| Arjun Patel  |  58000 |
| Sanya Gupta  |  52000 |
+--------------+--------+

A direct check confirms it: 45000 BETWEEN 45000 AND 60000 returned 1, and 60000 BETWEEN 45000 AND 60000 also returned 1. BETWEEN works on dates too, and is the natural way to ask for a period:

SELECT Name, DOJ FROM EMPLOYEE WHERE DOJ BETWEEN '2019-01-01' AND '2021-12-31';

+--------------+------------+
| Name         | DOJ        |
+--------------+------------+
| Aarav Sharma | 2019-06-15 |
| Diya Nair    | 2020-01-10 |
| Ishita Rao   | 2021-07-19 |
| Kabir Singh  | 2020-11-23 |
+--------------+------------+

ORDER BY — sorting. ASC (ascending) is the default and may be omitted; DESC reverses it. Sort on two columns and the second breaks ties in the first:

SELECT Name, Dept, Salary FROM EMPLOYEE ORDER BY Dept ASC, Salary DESC;

+--------------+----------+--------+
| Name         | Dept     | Salary |
+--------------+----------+--------+
| Neha Joshi   | Accounts |  49000 |
| Tara Menon   | Accounts |   NULL |
| Diya Nair    | HR       |  47500 |
| Meera Iyer   | HR       |  45000 |
| Vikram Bose  | IT       |  84000 |
| Ishita Rao   | IT       |  78000 |
| Kabir Singh  | IT       |  69500 |
| Rehan Ali    | IT       |   NULL |
| Rohan Mehta  | Sales    |  61000 |
| Arjun Patel  | Sales    |  58000 |
| Aarav Sharma | Sales    |  52000 |
| Sanya Gupta  | Sales    |  52000 |
+--------------+----------+--------+

Departments are alphabetical, and within each department salary runs highest first.

LIKE — pattern matching for text. It uses two wildcards, and mixing them up is a classic lost mark:

• % stands for any number of characters, including none.
• _ stands for exactly one character.

SELECT Name FROM EMPLOYEE WHERE Name LIKE 'A%';

+--------------+
| Name         |
+--------------+
| Aarav Sharma |
| Arjun Patel  |
+--------------+

Now the underscore. '_a%' means: one character of any kind, then a literal a, then anything at all — in other words, names whose second letter is a:

SELECT Name FROM EMPLOYEE WHERE Name LIKE '_a%';

+--------------+
| Name         |
+--------------+
| Aarav Sharma |
| Kabir Singh  |
| Sanya Gupta  |
| Tara Menon   |
+--------------+

Four names — Aarav, Kabir, Sanya, Tara. Had you written '%a%' instead, you would have got every name containing an a anywhere.

Underscores can also fix a length. '_____' — five underscores — means exactly five characters:

SELECT Name, City FROM EMPLOYEE WHERE City LIKE '_____';

+--------------+-------+
| Name         | City  |
+--------------+-------+
| Aarav Sharma | Delhi |
| Diya Nair    | Kochi |
| Kabir Singh  | Delhi |
| Sanya Gupta  | Delhi |
+--------------+-------+

This also confirms the char behaviour from Section 2. City is char(12), so 'Delhi' is padded out to twelve characters in storage — yet it matches a five-character pattern, because the trailing spaces are stripped when the value is read back.

One honest warning about LIKE in MySQL. Under the default collation on this server (utf8mb4_0900_ai_ci, where ci means case-insensitive), text comparison ignores case. A direct test returned 1 for both 'sharma' LIKE '%SH%' and 'Sharma' = 'SHARMA'. So LIKE 'a%' also matches Arjun. Write your patterns in the natural case and do not rely on case to filter.

NULL means the value is not known. It is not zero. It is not an empty string. It is not a space. It is the database saying "nothing was recorded here". In our table, Rehan Ali and Tara Menon joined recently and their salary has not been fixed yet, so Salary is NULL for both.

That distinction is not a technicality — it changes every comparison. A salary of 0 means the person genuinely earns nothing. A salary of NULL means we do not know what they earn. SQL refuses to guess.

The single most common SQL mistake. Every student instinctively writes this:

SELECT Name, Salary FROM EMPLOYEE WHERE Salary = NULL;

Result: 0 rows returned. Not an error message — just nothing. And the reverse fails identically:

SELECT Name, Salary FROM EMPLOYEE WHERE Salary <> NULL;

Also 0 rows returned. Both queries come back empty, even though two rows are sitting right there with NULL salaries. This is why the mistake is so dangerous: you get a silent wrong answer, not a crash.

Why it happens. Comparing anything with an unknown produces an unknown, not true or false. SQL uses three-valued logic. Here are the actual values MySQL returns:

SELECT NULL = NULL AS eq, NULL <> NULL AS ne, NULL IS NULL AS isnull,
       5000 + NULL AS plus, NULL = 0 AS eq_zero, NULL = '' AS eq_empty;

+------+------+--------+------+---------+----------+
| eq   | ne   | isnull | plus | eq_zero | eq_empty |
+------+------+--------+------+---------+----------+
| NULL | NULL |      1 | NULL |    NULL |     NULL |
+------+------+--------+------+---------+----------+

Read that row carefully, because it answers the whole topic. NULL = NULL is not 1 and not 0 — it is NULL. A WHERE clause keeps a row only when the condition comes out TRUE. NULL is not TRUE, so the row is dropped. That is why both queries returned nothing.

The same table proves the two claims that carry marks: NULL = 0 gives NULL, so NULL is not zero, and NULL = '' gives NULL, so NULL is not an empty string. Confirming it against the real data, both of these returned 0 rows as well:

SELECT Name, Salary FROM EMPLOYEE WHERE Salary = 0;
SELECT Name, Salary FROM EMPLOYEE WHERE Salary = '';

The fix: IS NULL and IS NOT NULL. These are the only operators that can test for NULL:

SELECT Name, Salary FROM EMPLOYEE WHERE Salary IS NULL;

+------------+--------+
| Name       | Salary |
+------------+--------+
| Rehan Ali  |   NULL |
| Tara Menon |   NULL |
+------------+--------+

SELECT Name, Salary FROM EMPLOYEE WHERE Salary IS NOT NULL;

+--------------+--------+
| Name         | Salary |
+--------------+--------+
| Aarav Sharma |  52000 |
| Diya Nair    |  47500 |
| Rohan Mehta  |  61000 |
| Ishita Rao   |  78000 |
| Kabir Singh  |  69500 |
| Meera Iyer   |  45000 |
| Arjun Patel  |  58000 |
| Sanya Gupta  |  52000 |
| Vikram Bose  |  84000 |
| Neha Joshi   |  49000 |
+--------------+--------+

Two consequences you will be tested on. First, NULL spreads through arithmetic — 5000 + NULL is NULL, which is why Rehan Ali's annual salary came out NULL in Section 4. Second, a NULL row is quietly excluded from ordinary conditions. WHERE Salary <> 52000 returned only 8 rows: the ten non-NULL salaries minus the two equal to 52000. The NULL rows did not appear, even though "unknown" is arguably not 52000. Similarly NULL IN ('Delhi','Pune') evaluates to NULL, so a NULL never matches an IN list.

Third, in ORDER BY, MySQL treats NULL as the lowest value, so ascending sorts put NULLs first:

SELECT Name, Salary FROM EMPLOYEE ORDER BY Salary ASC;

+--------------+--------+
| Name         | Salary |
+--------------+--------+
| Rehan Ali    |   NULL |
| Tara Menon   |   NULL |
| Meera Iyer   |  45000 |
| Diya Nair    |  47500 |
| Neha Joshi   |  49000 |
| Aarav Sharma |  52000 |
| Sanya Gupta  |  52000 |
| Arjun Patel  |  58000 |
| Rohan Mehta  |  61000 |
| Kabir Singh  |  69500 |
| Ishita Rao   |  78000 |
| Vikram Bose  |  84000 |
+--------------+--------+

UPDATE — changing values in rows that already exist. SET says what to change, WHERE says in which rows. Here are four updates that were actually run, with the number of rows each one changed:

UPDATE EMPLOYEE SET Bonus = Salary * 0.10 WHERE Dept = 'Sales';
   -> rows affected: 4
UPDATE EMPLOYEE SET Salary = Salary + 3000 WHERE City = 'Delhi';
   -> rows affected: 3
UPDATE EMPLOYEE SET Salary = 40000, Bonus = 2000 WHERE EmpID = 111;
   -> rows affected: 1
UPDATE EMPLOYEE SET Bonus = 0 WHERE Bonus IS NULL;
   -> rows affected: 7

The resulting table:

+-------+--------------+-----------+----------+--------+-------+
| EmpID | Name         | City      | Dept     | Salary | Bonus |
+-------+--------------+-----------+----------+--------+-------+
|   101 | Aarav Sharma | Delhi     | Sales    |  55000 |  5200 |
|   102 | Diya Nair    | Kochi     | HR       |  47500 |     0 |
|   103 | Rohan Mehta  | Mumbai    | Sales    |  61000 |  6100 |
|   104 | Ishita Rao   | Bengaluru | IT       |  78000 |     0 |
|   105 | Kabir Singh  | Delhi     | IT       |  72500 |     0 |
|   106 | Meera Iyer   | Chennai   | HR       |  45000 |     0 |
|   107 | Arjun Patel  | Ahmedabad | Sales    |  58000 |  5800 |
|   108 | Sanya Gupta  | Delhi     | Sales    |  55000 |  5200 |
|   109 | Vikram Bose  | Kolkata   | IT       |  84000 |     0 |
|   110 | Neha Joshi   | Pune      | Accounts |  49000 |     0 |
|   111 | Rehan Ali    | Hyderabad | IT       |  40000 |  2000 |
|   112 | Tara Menon   | Jaipur    | Accounts |   NULL |     0 |
+-------+--------------+-----------+----------+--------+-------+

Four points are visible in that table. One: several columns can be set in a single statement, separated by commas — see the third update. Two: the right-hand side of SET uses the row's current value, so Salary = Salary + 3000 is a raise, not a replacement. Three, and worth pausing on: Aarav Sharma's bonus is 5200 while his salary now reads 55000. The bonus was calculated as 10% of his old salary of 52000, because the bonus update ran before the raise. SQL statements execute in the order you write them, and re-running them in a different order gives different data. Four: Tara Menon's salary is still NULL, because Salary + 3000 was never applied to her and NULL cannot be nudged by arithmetic.

UPDATE with no WHERE changes every row. There is no undo and no confirmation. Demonstrated on the small trainee table:

UPDATE TRAINEE SET Stipend = 15000;
   -> rows affected: 3

+-----+-------------+--------+---------+------------+
| TID | TName       | Stream | Stipend | JoinDate   |
+-----+-------------+--------+---------+------------+
|   1 | Ananya Das  | CS     |   15000 | 2024-01-08 |
|   2 | Farhan Khan | ECE    |   15000 | 2024-02-19 |
|   3 | Pooja Bhatt | CS     |   15000 | 2024-03-04 |
+-----+-------------+--------+---------+------------+

All three stipends overwritten. Always write the WHERE first, then go back and add the SET.

DELETE — removing whole rows. DELETE removes entire rows and nothing less. You cannot delete a single column's value with it; to blank one field you use UPDATE ... SET col = NULL. Three deletes, with real counts:

DELETE FROM EMPLOYEE WHERE Salary IS NULL;
   -> rows affected: 1
DELETE FROM EMPLOYEE WHERE Dept = 'Marketing';
   -> rows affected: 0
DELETE FROM EMPLOYEE WHERE City = 'Kochi';
   -> rows affected: 1

The middle one matched nothing and reported 0 rows affected — that is not an error. A DELETE that finds no matching row simply does nothing.

DELETE without WHERE empties the table but keeps it. This was tested properly. A scratch table was created and filled with ten rows:

CREATE TABLE TEMPSTAFF (
  EmpID INT NOT NULL,
  Name  VARCHAR(25) NOT NULL,
  Dept  VARCHAR(15)
);

Then every row was removed with a single statement, and the table list was checked:

DELETE FROM TEMPSTAFF;
   -> rows affected: 10
SELECT * FROM TEMPSTAFF;   -- 0 rows
SHOW TABLES;               -- employee, tempstaff, trainee

DESCRIBE TEMPSTAFF;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| EmpID | int         | NO   |     | NULL    |       |
| Name  | varchar(25) | NO   |     | NULL    |       |
| Dept  | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Every row is gone, but tempstaff is still listed by SHOW TABLES and DESCRIBE still returns its full structure. That is the distinction the board asks for, in one experiment.