🐬 Introduction to SQL

In the last chapter, you learnt what a database and a table are. But how do we actually talk to a database — to create a table, add a student, or fix a wrong entry? 🗣️ We use a special language called SQL! This chapter answers the BIG questions: What is SQL and MySQL? What are DDL and DML commands? Which data types can a column have? How do constraints keep wrong data out? And how do we create a database, create a table, change its structure, and insert records? Let's write our first queries! 💻

9.1 🏁 Introduction — What is SQL?

SQL (Structured Query Language) is the standard language used to create, manage and ask questions (queries) of a relational database. It is pronounced "S-Q-L" or "sequel".

MySQL is a popular, free and open-source RDBMS (Relational DBMS) software that understands SQL.

Analogy: If the database is a library 📚 and MySQL is the librarian, then SQL is the language you speak to the librarian — "Please add this book", "Show me all books by Premchand", "Remove this old book". 🗣️

9.2 🧩 Types of SQL Commands

graph TD
    SQL["🗣️ SQL COMMANDS"]
    DDL["🏗️ DDL\nData Definition Language\n(works on the STRUCTURE)"]
    DML["📝 DML\nData Manipulation Language\n(works on the DATA)"]
    D1["CREATE\nALTER\nDROP"]
    D2["INSERT\nUPDATE\nDELETE\nSELECT"]

    SQL --> DDL --> D1
    SQL --> DML --> D2

    style DDL fill:#2196F3,color:#fff
    style DML fill:#4CAF50,color:#fff

Analogy: Building a cupboard 🗄️ vs using it:

• DDL = the carpenter — makes the cupboard, adds a shelf, or breaks it down (structure).
• DML = you — put clothes in, take them out, rearrange them (data).

9.3 🔢 MySQL Data Types

Every column must have a data type that tells MySQL what kind of values it can hold.

9.3.1 CHAR vs VARCHAR ⚖️

Suppose a column is CHAR(10) and another is VARCHAR(10), and we store 'Riya' (4 characters) in both:

CHAR(10)     │ R │ i │ y │ a │   │   │   │   │   │   │   ← always uses 10 (padded with spaces)
VARCHAR(10)  │ R │ i │ y │ a │                           ← uses only 4 (+ a tiny length marker)

Analogy: CHAR is like a fixed train seat 💺 — you get the whole seat even if you're small. VARCHAR is like a bench 🪑 — each person takes only as much space as they need!

9.4 🛡️ Constraints

A constraint is a rule applied to a column so that only valid data can be stored in it. If someone tries to enter data that breaks the rule, MySQL rejects it with an error.

Analogy: Constraints are like the security guard at your school gate 💂 — "No ID card? You can't come in!" (NOT NULL). "Two students with the same roll number? Not allowed!" (UNIQUE / PRIMARY KEY).

9.5 🗂️ Working with Databases

A MySQL server can hold many databases. First, we create one and select it.

CREATE DATABASE School;     -- make a new database
SHOW DATABASES;             -- list all databases
USE School;                 -- select it for work

9.6 🏗️ Creating a Table — CREATE TABLE

CREATE TABLE table_name (
    column1  datatype  [constraint],
    column2  datatype  [constraint],
    ...
);

Let's create our STUDENT table:

CREATE TABLE Student (
    RollNo   INT PRIMARY KEY,
    Name     VARCHAR(30) NOT NULL,
    Gender   CHAR(1),
    Class    INT,
    Marks    FLOAT,
    DOB      DATE
);

   CREATE TABLE Student (
       RollNo   INT          PRIMARY KEY,
       ──────   ───          ───────────
         │       │               └─ constraint (unique + not null)
         │       └─ data type (whole number)
         └─ column name
       Name     VARCHAR(30)  NOT NULL,   ← text up to 30 characters, must be filled
       ...
   );                                    ← closing bracket + semicolon

9.6.1 Viewing Tables and Their Structure 🔍

SHOW TABLES;

+------------------+
| Tables_in_school |
+------------------+
| Student          |
+------------------+

DESCRIBE (or DESC) shows the structure of a table — its columns, types and constraints:

DESCRIBE Student;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | int         | NO   | PRI | NULL    |       |
| Name   | varchar(30) | NO   |     | NULL    |       |
| Gender | char(1)     | YES  |     | NULL    |       |
| Class  | int         | YES  |     | NULL    |       |
| Marks  | float       | YES  |     | NULL    |       |
| DOB    | date        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

CREATE TABLE Item (
    ItemCode  CHAR(4) PRIMARY KEY,
    ItemName  VARCHAR(25) NOT NULL,
    Price     FLOAT,
    Qty       INT
);

9.7 🔧 Changing a Table — ALTER TABLE

ALTER TABLE changes the structure of an existing table — without losing its data.

graph TD
    AT["🔧 ALTER TABLE"]
    A1["➕ ADD\na new column"]
    A2["✏️ MODIFY\na column's type or size"]
    A3["➖ DROP\na column"]
    A4["🔑 ADD / DROP\nPRIMARY KEY"]

    AT --> A1
    AT --> A2
    AT --> A3
    AT --> A4

    style A1 fill:#4CAF50,color:#fff
    style A2 fill:#FF9800,color:#fff
    style A3 fill:#F44336,color:#fff
    style A4 fill:#9C27B0,color:#fff

1. Add a new column:

ALTER TABLE Student ADD City VARCHAR(20);

2. Change the data type or size of a column:

ALTER TABLE Student MODIFY Name VARCHAR(40) NOT NULL;

3. Delete a column:

ALTER TABLE Student DROP City;

4. Remove and add a primary key:

ALTER TABLE Student DROP PRIMARY KEY;
ALTER TABLE Student ADD PRIMARY KEY (RollNo);

9.8 🗑️ Deleting a Table — DROP TABLE

DROP TABLE Student;

This deletes the table completely — its structure AND all its data. It cannot be undone! ⚠️

9.9 ➕ Adding Records — INSERT INTO

INSERT INTO adds new rows (records) to a table. (Let's assume our Student table from 9.6 exists again.)

9.9.1 Inserting Values for All Columns

Values must be given in the same order as the columns in the table:

INSERT INTO Student VALUES (101, 'Riya', 'F', 11, 92.5, '2009-05-21');
INSERT INTO Student VALUES (102, 'Aman', 'M', 11, 85, '2009-08-14');

9.9.2 Inserting Values for Selected Columns

Name the columns you want to fill. Columns you leave out get NULL (or their DEFAULT value):

INSERT INTO Student (RollNo, Name, Class) VALUES (103, 'Zoya', 12);

9.9.3 Inserting Many Rows at Once

INSERT INTO Student VALUES
    (104, 'Kabir', 'M', 12, 78, '2008-11-02'),
    (105, 'Meera', 'F', 11, NULL, '2009-01-30');

Let's see all the records with SELECT * FROM Student; (you'll learn SELECT fully in the next chapter):

SELECT * FROM Student;

+--------+-------+--------+-------+-------+------------+
| RollNo | Name  | Gender | Class | Marks | DOB        |
+--------+-------+--------+-------+-------+------------+
|    101 | Riya  | F      |    11 |  92.5 | 2009-05-21 |
|    102 | Aman  | M      |    11 |    85 | 2009-08-14 |
|    103 | Zoya  | NULL   |    12 |  NULL | NULL       |
|    104 | Kabir | M      |    12 |    78 | 2008-11-02 |
|    105 | Meera | F      |    11 |  NULL | 2009-01-30 |
+--------+-------+--------+-------+-------+------------+

INSERT INTO Student VALUES (101, 'Tina', 'F', 11, 70, '2009-02-02');
-- ERROR 1062: Duplicate entry '101' — RollNo is the PRIMARY KEY

INSERT INTO Student (RollNo, Class) VALUES (106, 11);
-- ERROR 1364: Field 'Name' doesn't have a default value — Name is NOT NULL

INSERT INTO Item VALUES ('I005', 'Pencil Box', 120, 50);

🧾 Commands at a Glance

⚠️ Common Errors and Misconceptions

🔑 Quick Revision — Exam Ready!

• SQL → Structured Query Language, used to work with relational databases; MySQL → free, open-source RDBMS
• SQL is not case sensitive (for keywords); statements end with ;
• DDL → structure: CREATE, ALTER, DROP
• DML → data: INSERT, UPDATE, DELETE, SELECT
• Data types → CHAR(n) fixed, VARCHAR(n) variable, INT, FLOAT, DECIMAL(p, s), DATE ('YYYY-MM-DD')
• Constraints → NOT NULL, UNIQUE, PRIMARY KEY (unique + not null, one per table), DEFAULT
• Database → CREATE DATABASE, SHOW DATABASES, USE, DROP DATABASE
• Table → CREATE TABLE, SHOW TABLES, DESCRIBE, DROP TABLE
• ALTER TABLE → ADD col, MODIFY col, DROP col, ADD PRIMARY KEY (col), DROP PRIMARY KEY
• INSERT INTO → all columns in order, or chosen columns (rest become NULL); many rows with commas
• Text and dates in single quotes; numbers and NULL without quotes

🎯 Sample Exam Questions

Q1: Very Short Answer [1 mark each]

a) What is the full form of SQL? Answer: Structured Query Language

b) Which command shows the structure of a table? Answer: DESCRIBE (or DESC)

c) Which constraint allows NULL values but no duplicates? Answer: UNIQUE

d) Is ALTER a DDL or a DML command? Answer: DDL

e) In which format is a date written in MySQL? Answer: 'YYYY-MM-DD'

f) Which command selects a database for use? Answer: USE

Q2: Short Answer [2 marks]

Write SQL commands to (a) create a database named Library and (b) open it for use.

Answer:

CREATE DATABASE Library;
USE Library;

Q3: Program [3 marks]

Write a command to create the table BOOK with the following structure:

Answer:

CREATE TABLE Book (
    BookID   CHAR(5) PRIMARY KEY,
    Title    VARCHAR(40) NOT NULL,
    Author   VARCHAR(30),
    Price    FLOAT,
    PubDate  DATE
);

Q4: Short Answer [3 marks]

For the table BOOK above, write commands to: (a) add a column Qty of integer type (b) increase the size of Author to 50 characters (c) insert the record: B0001, Wings of Fire, A P J Abdul Kalam, 350, 1999-01-01, 10

Answer:

ALTER TABLE Book ADD Qty INT;
ALTER TABLE Book MODIFY Author VARCHAR(50);
INSERT INTO Book VALUES ('B0001', 'Wings of Fire', 'A P J Abdul Kalam', 350, '1999-01-01', 10);

Q5: Error Finding [2 marks]

Find the errors in the following commands:

CREATE TABLE Emp (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(20),
);
INSERT INTO Emp VALUES (1, Ravi);

Answer: (1) There must be no comma after the last column Name VARCHAR(20). (2) The text value must be in quotes: 'Ravi'.

CREATE TABLE Emp (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(20)
);
INSERT INTO Emp VALUES (1, 'Ravi');

✏️ Practice Problems

1. What is SQL? Why is MySQL called an RDBMS?
2. Classify as DDL or DML: INSERT, DROP, SELECT, ALTER, UPDATE, CREATE, DELETE.
3. Which data type would you use for: (a) PIN code (b) name of a city (c) date of admission (d) percentage (e) number of siblings?
4. A column Grade is CHAR(2) and stores 'A'. How many characters of space does it use? What if it were VARCHAR(2)?
5. Write a command to create a table TEACHER(TID, TName, Subject, Salary, DOJ) with TID as the primary key and TName not null.
6. Write commands to add a column Phone VARCHAR(15) to TEACHER, and then remove it.
7. Write commands to insert any three records into TEACHER.
8. Write an INSERT command that fills only TID and TName of TEACHER. What will the other columns contain?
9. What happens if you insert two rows with the same TID? Why?
10. What is the difference between DROP TABLE Teacher; and DROP DATABASE School;?
11. Write the command to see the structure of TEACHER. What does "PRI" in the Key column mean?
12. Can a table have two UNIQUE columns? Two PRIMARY KEYs? Explain.