Introduction to Structured Query Language (SQL)

Overview

Learning outcomes

• Students will be able to state the advantages of SQL and classify commands as DDL, DML or DQL.
• Students will be able to choose suitable data types (CHAR, VARCHAR, INT, FLOAT, DECIMAL, DATE) and explain the difference between CHAR and VARCHAR.
• Students will be able to apply NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY and FOREIGN KEY constraints while creating tables.
• Students will be able to create, open, describe, alter and drop databases and tables in MySQL.
• Students will be able to insert rows with all columns, selected columns and NULL values.
• Students will be able to write SELECT queries using aliases, expressions, DISTINCT, WHERE, relational and logical operators, BETWEEN, IN, LIKE, IS NULL and ORDER BY.
• Students will be able to modify and remove data using UPDATE and DELETE, and distinguish DELETE from DROP.
• Students will be able to predict the output of a given query on a given table.

Lesson plan

Notes

mysql> SELECT Name, Marks
    -> FROM STUDENT
    -> WHERE Marks > 80;

CREATE DATABASE SCHOOL;
SHOW DATABASES LIKE 'SCHOOL';
USE SCHOOL;
SHOW TABLES;

Query OK, 1 row affected (0.00 sec)

+-------------------+
| Database (SCHOOL) |
+-------------------+
| SCHOOL            |
+-------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

CREATE TABLE STUDENT (
    RollNo  INT PRIMARY KEY,
    Name    VARCHAR(30) NOT NULL,
    Class   INT,
    Section CHAR(1) DEFAULT 'A',
    Gender  CHAR(1),
    DOB     DATE,
    Marks   INT
);

Query OK, 0 rows affected (0.00 sec)

CREATE TABLE FEE (
    ReceiptNo INT PRIMARY KEY,
    RollNo    INT NOT NULL,
    Amount    DECIMAL(8,2),
    PayDate   DATE,
    FOREIGN KEY (RollNo) REFERENCES STUDENT(RollNo)
);
INSERT INTO FEE VALUES (5001, 2, 4500.00, '2025-04-05');
INSERT INTO FEE VALUES (5002, 15, 4500.00, '2025-04-09');

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`fee`, CONSTRAINT `fee_ibfk_1` FOREIGN KEY (`RollNo`) REFERENCES `student` (`RollNo`))

DESC STUDENT;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| RollNo  | int         | NO   | PRI | NULL    |       |
| Name    | varchar(30) | NO   |     | NULL    |       |
| Class   | int         | YES  |     | NULL    |       |
| Section | char(1)     | YES  |     | A       |       |
| Gender  | char(1)     | YES  |     | NULL    |       |
| DOB     | date        | YES  |     | NULL    |       |
| Marks   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

ALTER TABLE STUDENT ADD Phone CHAR(10);
ALTER TABLE STUDENT MODIFY Name VARCHAR(40) NOT NULL;
ALTER TABLE STUDENT DROP Phone;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE SPORTS (
    RollNo INT,
    Game   VARCHAR(20)
);
ALTER TABLE SPORTS ADD PRIMARY KEY (RollNo);
DESC SPORTS;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | int         | NO   | PRI | NULL    |       |
| Game   | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

DROP TABLE SPORTS;
SHOW TABLES;

Query OK, 0 rows affected (0.00 sec)

+------------------+
| Tables_in_school |
+------------------+
| STUDENT          |
+------------------+
1 row in set (0.00 sec)

INSERT INTO STUDENT VALUES (9, 'Ishaan Verma', 12, 'C', 'M', '2008-02-14', 55);
INSERT INTO STUDENT (RollNo, Name, Class, Marks) VALUES (10, 'Pooja Reddy', 11, 49);
SELECT * FROM STUDENT WHERE RollNo >= 9;

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+--------+--------------+-------+---------+--------+------------+-------+
| RollNo | Name         | Class | Section | Gender | DOB        | Marks |
+--------+--------------+-------+---------+--------+------------+-------+
|      9 | Ishaan Verma |    12 | C       | M      | 2008-02-14 |    55 |
|     10 | Pooja Reddy  |    11 | A       | NULL   | NULL       |    49 |
+--------+--------------+-------+---------+--------+------------+-------+
2 rows in set (0.00 sec)

INSERT INTO STUDENT VALUES (3, 'Tara Joshi', 11, 'B', 'F', '2009-02-11', 66);
INSERT INTO STUDENT VALUES (11, NULL, 11, 'B', 'M', '2009-08-19', 59);
SELECT COUNT(*) FROM STUDENT;

ERROR 1062 (23000): Duplicate entry '3' for key 'student.PRIMARY'

ERROR 1048 (23000): Column 'Name' cannot be null

+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

SELECT * FROM STUDENT;

+--------+--------------+-------+---------+--------+------------+-------+
| RollNo | Name         | Class | Section | Gender | DOB        | Marks |
+--------+--------------+-------+---------+--------+------------+-------+
|      1 | Aarav Sharma |    11 | A       | M      | 2009-03-14 |    78 |
|      2 | Diya Menon   |    12 | B       | F      | 2008-07-22 |    91 |
|      3 | Kabir Singh  |    11 | A       | M      | 2008-11-05 |    45 |
|      4 | Ananya Iyer  |    12 | C       | F      | 2008-01-30 |  NULL |
|      5 | Rohan Das    |    12 | B       | M      | 2009-09-18 |    62 |
|      6 | Sneha Patil  |    12 | A       | F      | 2007-12-25 |    84 |
|      7 | Vihaan Gupta |    11 | C       | M      | NULL       |    38 |
|      8 | Meera Nair   |    11 | B       | F      | 2008-05-10 |    70 |
+--------+--------------+-------+---------+--------+------------+-------+
8 rows in set (0.00 sec)

SELECT Name, Marks, Marks + 5 AS "After Grace"
FROM STUDENT
WHERE Class = 12;

+-------------+-------+-------------+
| Name        | Marks | After Grace |
+-------------+-------+-------------+
| Diya Menon  |    91 |          96 |
| Ananya Iyer |  NULL |        NULL |
| Rohan Das   |    62 |          67 |
| Sneha Patil |    84 |          89 |
+-------------+-------+-------------+
4 rows in set (0.00 sec)

SELECT DISTINCT Section FROM STUDENT;
SELECT DISTINCT Class, Gender FROM STUDENT;

+---------+
| Section |
+---------+
| A       |
| B       |
| C       |
+---------+
3 rows in set (0.00 sec)

+-------+--------+
| Class | Gender |
+-------+--------+
|    11 | M      |
|    12 | F      |
|    12 | M      |
|    11 | F      |
+-------+--------+
4 rows in set (0.00 sec)

SELECT RollNo, Name, Marks FROM STUDENT WHERE Marks >= 70;
SELECT Name, DOB FROM STUDENT WHERE DOB < '2008-06-01';
SELECT Name, Section FROM STUDENT WHERE Section <> 'B';

+--------+--------------+-------+
| RollNo | Name         | Marks |
+--------+--------------+-------+
|      1 | Aarav Sharma |    78 |
|      2 | Diya Menon   |    91 |
|      6 | Sneha Patil  |    84 |
|      8 | Meera Nair   |    70 |
+--------+--------------+-------+
4 rows in set (0.00 sec)

+-------------+------------+
| Name        | DOB        |
+-------------+------------+
| Ananya Iyer | 2008-01-30 |
| Sneha Patil | 2007-12-25 |
| Meera Nair  | 2008-05-10 |
+-------------+------------+
3 rows in set (0.00 sec)

+--------------+---------+
| Name         | Section |
+--------------+---------+
| Aarav Sharma | A       |
| Kabir Singh  | A       |
| Ananya Iyer  | C       |
| Sneha Patil  | A       |
| Vihaan Gupta | C       |
+--------------+---------+
5 rows in set (0.00 sec)

SELECT Name, Class, Gender FROM STUDENT WHERE Class = 11 AND Gender = 'M';
SELECT Name, Marks FROM STUDENT WHERE Marks > 85 OR Marks < 40;
SELECT Name, Class, Section FROM STUDENT WHERE Class = 12 AND (Section = 'A' OR Section = 'C');

+--------------+-------+--------+
| Name         | Class | Gender |
+--------------+-------+--------+
| Aarav Sharma |    11 | M      |
| Kabir Singh  |    11 | M      |
| Vihaan Gupta |    11 | M      |
+--------------+-------+--------+
3 rows in set (0.00 sec)

+--------------+-------+
| Name         | Marks |
+--------------+-------+
| Diya Menon   |    91 |
| Vihaan Gupta |    38 |
+--------------+-------+
2 rows in set (0.00 sec)

+-------------+-------+---------+
| Name        | Class | Section |
+-------------+-------+---------+
| Ananya Iyer |    12 | C       |
| Sneha Patil |    12 | A       |
+-------------+-------+---------+
2 rows in set (0.00 sec)

SELECT Name, Marks FROM STUDENT WHERE Marks BETWEEN 45 AND 70;
SELECT Name, DOB FROM STUDENT WHERE DOB BETWEEN '2008-01-01' AND '2008-12-31';
SELECT RollNo, Name, Section FROM STUDENT WHERE Section IN ('A', 'C');

+-------------+-------+
| Name        | Marks |
+-------------+-------+
| Kabir Singh |    45 |
| Rohan Das   |    62 |
| Meera Nair  |    70 |
+-------------+-------+
3 rows in set (0.00 sec)

+-------------+------------+
| Name        | DOB        |
+-------------+------------+
| Diya Menon  | 2008-07-22 |
| Kabir Singh | 2008-11-05 |
| Ananya Iyer | 2008-01-30 |
| Meera Nair  | 2008-05-10 |
+-------------+------------+
4 rows in set (0.00 sec)

+--------+--------------+---------+
| RollNo | Name         | Section |
+--------+--------------+---------+
|      1 | Aarav Sharma | A       |
|      3 | Kabir Singh  | A       |
|      4 | Ananya Iyer  | C       |
|      6 | Sneha Patil  | A       |
|      7 | Vihaan Gupta | C       |
+--------+--------------+---------+
5 rows in set (0.00 sec)

SELECT Name FROM STUDENT WHERE Name LIKE '%an%';
SELECT Name FROM STUDENT WHERE Name LIKE '_i%';
SELECT Name FROM STUDENT WHERE Name NOT LIKE '%e%';

+--------------+
| Name         |
+--------------+
| Ananya Iyer  |
| Rohan Das    |
| Vihaan Gupta |
+--------------+
3 rows in set (0.00 sec)

+--------------+
| Name         |
+--------------+
| Diya Menon   |
| Vihaan Gupta |
+--------------+
2 rows in set (0.00 sec)

+--------------+
| Name         |
+--------------+
| Aarav Sharma |
| Kabir Singh  |
| Rohan Das    |
| Vihaan Gupta |
+--------------+
4 rows in set (0.00 sec)

SELECT RollNo, Name FROM STUDENT WHERE Marks = NULL;
SELECT RollNo, Name FROM STUDENT WHERE Marks IS NULL;
SELECT Name, DOB FROM STUDENT WHERE DOB IS NOT NULL AND Gender = 'M';

Empty set (0.00 sec)

+--------+-------------+
| RollNo | Name        |
+--------+-------------+
|      4 | Ananya Iyer |
+--------+-------------+
1 row in set (0.00 sec)

+--------------+------------+
| Name         | DOB        |
+--------------+------------+
| Aarav Sharma | 2009-03-14 |
| Kabir Singh  | 2008-11-05 |
| Rohan Das    | 2009-09-18 |
+--------------+------------+
3 rows in set (0.00 sec)

SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC;
SELECT Class, Section, Name FROM STUDENT WHERE Gender = 'F' ORDER BY Class, Name;

+--------------+-------+
| Name         | Marks |
+--------------+-------+
| Diya Menon   |    91 |
| Sneha Patil  |    84 |
| Aarav Sharma |    78 |
| Meera Nair   |    70 |
| Rohan Das    |    62 |
| Kabir Singh  |    45 |
| Vihaan Gupta |    38 |
| Ananya Iyer  |  NULL |
+--------------+-------+
8 rows in set (0.00 sec)

+-------+---------+-------------+
| Class | Section | Name        |
+-------+---------+-------------+
|    11 | B       | Meera Nair  |
|    12 | C       | Ananya Iyer |
|    12 | B       | Diya Menon  |
|    12 | A       | Sneha Patil |
+-------+---------+-------------+
4 rows in set (0.00 sec)

UPDATE STUDENT SET Marks = 88 WHERE RollNo = 4;
UPDATE STUDENT SET Marks = Marks + 5 WHERE Class = 11 AND Marks < 50;
SELECT RollNo, Name, Marks FROM STUDENT WHERE RollNo IN (3, 4, 7);

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

+--------+--------------+-------+
| RollNo | Name         | Marks |
+--------+--------------+-------+
|      3 | Kabir Singh  |    50 |
|      4 | Ananya Iyer  |    88 |
|      7 | Vihaan Gupta |    43 |
+--------+--------------+-------+
3 rows in set (0.00 sec)

DELETE FROM STUDENT WHERE RollNo = 7;
SELECT RollNo, Name FROM STUDENT WHERE Class = 11;

Query OK, 1 row affected (0.00 sec)

+--------+--------------+
| RollNo | Name         |
+--------+--------------+
|      1 | Aarav Sharma |
|      3 | Kabir Singh  |
|      8 | Meera Nair   |
+--------+--------------+
3 rows in set (0.00 sec)

DELETE FROM STUDENT;
SELECT * FROM STUDENT;
DROP TABLE STUDENT;
SELECT * FROM STUDENT;

Query OK, 8 rows affected (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1146 (42S02): Table 'school.student' doesn't exist

SELECT RollNo, Name, Marks
FROM STUDENT
WHERE Gender = 'F' AND Marks IS NOT NULL
ORDER BY Marks DESC;

+--------+-------------+-------+
| RollNo | Name        | Marks |
+--------+-------------+-------+
|      2 | Diya Menon  |    91 |
|      6 | Sneha Patil |    84 |
|      8 | Meera Nair  |    70 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

Key terms

SQL

Structured Query Language; the standard language used to create, change and query relational databases.

MySQL

A free, open-source relational database management system that uses SQL.

DDL

Data Definition Language; commands that define or change structure, e.g. CREATE, ALTER, DROP.

DML

Data Manipulation Language; commands that change the data in tables: INSERT, UPDATE, DELETE.

DQL

Data Query Language; the SELECT command, used to read data without changing it.

Data type

Property of a column that decides the kind of values it can store, e.g. INT, VARCHAR, DATE.

CHAR(n)

Fixed-length text type; always uses n characters of space.

VARCHAR(n)

Variable-length text type; stores only the characters actually entered, up to n.

DECIMAL(p,s)

Exact number type with p total digits and s digits after the decimal point.

Constraint

A rule on a column or table that the RDBMS checks before accepting data.

Primary key

Column(s) that uniquely identify each row; cannot be NULL or repeated.

Foreign key

Column in a child table whose values must match the primary key of a parent table (or be NULL).

Referential integrity

The guarantee, enforced by foreign keys, that every reference points to a row that really exists.

NULL

A marker for a missing or unknown value; different from 0 or an empty string.

Schema

The structure of a table or database: its columns, data types and constraints.

Degree

Number of columns (attributes) in a table.

Cardinality

Number of rows (tuples) in a table.

Column alias

A temporary heading given to a column or expression in a query result using AS.

Wildcard

A special character used with LIKE: % for any number of characters, _ for exactly one.

Clause

A part of an SQL statement that starts with a keyword, such as WHERE or ORDER BY.

MCQs

Tap an option to check it, or use Show answers above to reveal them all.

1. SQL is called a non-procedural language because:

   1. it can be used only by one user at a time
   2. the user states what data is needed, not the steps to get it
   3. it has no keywords
   4. it works only on numbers
   Answer

   (b) the user states what data is needed, not the steps to get it

   In SQL we describe the required result; the RDBMS decides how to fetch it.

2. Which of the following is a DDL command?

   1. INSERT
   2. UPDATE
   3. ALTER
   4. SELECT
   Answer

   (c) ALTER

   ALTER changes the structure of a table, so it is DDL. INSERT and UPDATE are DML; SELECT is DQL.

3. According to the CBSE syllabus, the SELECT command belongs to:

   1. DQL
   2. DDL
   3. DML
   4. DCL
   Answer

   (a) DQL

   SELECT only reads data, so it is classified as Data Query Language.

4. Which data type is most suitable for storing a 6-digit PIN code such as 110001?

   1. INT
   2. DATE
   3. VARCHAR(100)
   4. CHAR(6)
   Answer

   (d) CHAR(6)

   A PIN code always has 6 characters and is never used in arithmetic, so fixed-length CHAR(6) fits best.

5. A column is declared as VARCHAR(20). How many characters of space are used to store the value 'Ravi'?

   1. 20
   2. 4 (plus a small length prefix)
   3. 24
   4. 0
   Answer

   (b) 4 (plus a small length prefix)

   VARCHAR is variable length, so only the 4 characters entered are stored, along with a length byte.

6. In MySQL, a DATE value is written in the format:

   1. 'DD-MM-YYYY'
   2. 'MM/DD/YYYY'
   3. 'YYYY-MM-DD'
   4. 'YYYY/DD/MM'
   Answer

   (c) 'YYYY-MM-DD'

   MySQL stores and accepts dates as 'YYYY-MM-DD', for example '2008-07-22'.

7. Which constraint ensures that no two rows have the same value in a column but still allows NULL?

   1. NOT NULL
   2. DEFAULT
   3. PRIMARY KEY
   4. UNIQUE
   Answer

   (d) UNIQUE

   UNIQUE stops duplicates but accepts NULL. PRIMARY KEY stops both duplicates and NULL.

8. Which statement about a PRIMARY KEY is correct?

   1. Its values must be unique and cannot be NULL
   2. A table can have any number of primary keys
   3. It can contain duplicate values
   4. It must always be a text column
   Answer

   (a) Its values must be unique and cannot be NULL

   A primary key behaves like UNIQUE + NOT NULL, and a table has only one primary key.

9. In table FEE, RollNo is a foreign key referring to STUDENT(RollNo). Which RollNo can be inserted in FEE?

   1. Any integer
   2. Only a RollNo that exists in STUDENT (or NULL, if the column allows it)
   3. Only a RollNo that does not exist in STUDENT
   4. Only the value 0
   Answer

   (b) Only a RollNo that exists in STUDENT (or NULL, if the column allows it)

   A foreign key value must match an existing primary key value in the parent table, or be NULL.

10. Which command shows the column names, data types and keys of the table STUDENT?

    1. SHOW STUDENT;
    2. SELECT STUDENT;
    3. DESCRIBE STUDENT;
    4. USE STUDENT;
    Answer

    (c) DESCRIBE STUDENT;

    DESCRIBE (or DESC) displays the structure of a table.

11. Which command makes SCHOOL the current database?

    1. OPEN SCHOOL;
    2. USE SCHOOL;
    3. SELECT SCHOOL;
    4. SHOW SCHOOL;
    Answer

    (b) USE SCHOOL;

    USE database_name; selects the database for later statements.

12. Which statement adds a column Phone to the table STUDENT?

    1. UPDATE STUDENT ADD Phone CHAR(10);
    2. INSERT INTO STUDENT Phone CHAR(10);
    3. ALTER STUDENT ADD Phone;
    4. ALTER TABLE STUDENT ADD Phone CHAR(10);
    Answer

    (d) ALTER TABLE STUDENT ADD Phone CHAR(10);

    A structure change needs ALTER TABLE, followed by ADD, the column name and its data type.

13. Which command removes both the structure and the data of a table permanently?

    1. DROP TABLE
    2. DELETE FROM
    3. ALTER TABLE ... DROP
    4. UPDATE
    Answer

    (a) DROP TABLE

    DROP TABLE removes the whole table. DELETE removes rows only; ALTER ... DROP removes one column.

14. Using the sample STUDENT table, how many rows does SELECT DISTINCT Section FROM STUDENT; return?

    1. 8
    2. 2
    3. 3
    4. 1
    Answer

    (c) 3

    The sections present are A, B and C; DISTINCT shows each once.

15. Using the sample STUDENT table, how many rows are returned by SELECT Name FROM STUDENT WHERE Marks BETWEEN 45 AND 70;?

    1. 2
    2. 3
    3. 4
    4. 5
    Answer

    (b) 3

    Kabir (45), Rohan (62) and Meera (70). BETWEEN includes both end values.

16. Which names from the sample table match Name LIKE '_i%'?

    1. Diya Menon and Vihaan Gupta
    2. Kabir Singh only
    3. Aarav Sharma and Ananya Iyer
    4. No name
    Answer

    (a) Diya Menon and Vihaan Gupta

    The pattern needs 'i' as the second character: D-i-ya and V-i-haan.

17. Which condition correctly finds students whose marks have not been entered?

    1. WHERE Marks = NULL
    2. WHERE Marks = 0
    3. WHERE Marks = 'NULL'
    4. WHERE Marks IS NULL
    Answer

    (d) WHERE Marks IS NULL

    NULL can only be tested with IS NULL or IS NOT NULL.

18. If no ASC or DESC is written after ORDER BY, the rows are sorted in:

    1. descending order
    2. ascending order
    3. the order they were inserted
    4. random order
    Answer

    (b) ascending order

    ASC is the default sort order.

19. Which condition is equivalent to Section = 'A' OR Section = 'C'?

    1. Section BETWEEN 'A' AND 'C'
    2. Section LIKE 'A%C'
    3. Section IN ('A', 'C')
    4. Section = ('A', 'C')
    Answer

    (c) Section IN ('A', 'C')

    IN matches any value in the list. BETWEEN 'A' AND 'C' would also include 'B'.

20. What is displayed for Ananya by SELECT Marks + 10 FROM STUDENT WHERE RollNo = 4; (her Marks is NULL)?

    1. NULL
    2. 10
    3. 0
    4. An error
    Answer

    (a) NULL

    Any arithmetic with NULL gives NULL.

21. What happens when UPDATE STUDENT SET Marks = 50; is executed?

    1. Only the first row changes
    2. An error occurs because WHERE is missing
    3. Marks of every row becomes 50
    4. A new row with Marks 50 is added
    Answer

    (c) Marks of every row becomes 50

    Without a WHERE clause, UPDATE changes all rows.

22. After DELETE FROM STUDENT;, which statement is true?

    1. The table STUDENT no longer exists
    2. The database is deleted
    3. Only the first row is deleted
    4. The table exists but has no rows
    Answer

    (d) The table exists but has no rows

    DELETE without WHERE removes all rows but keeps the structure.

23. In WHERE Class = 12 AND Section = 'A' OR Section = 'C', which operator is evaluated first?

    1. OR
    2. AND
    3. Both together
    4. Whichever is written last
    Answer

    (b) AND

    The precedence is NOT, then AND, then OR. Use brackets to change it.

24. Which keyword is used to give a temporary heading to a column in the output?

    1. AS
    2. IS
    3. IN
    4. TO
    Answer

    (a) AS

    Example: SELECT Marks + 5 AS NewMarks FROM STUDENT;

25. A table has degree 7 and cardinality 8. After ALTER TABLE STUDENT ADD Phone CHAR(10); and one INSERT, the degree and cardinality are:

    1. 7 and 9
    2. 8 and 8
    3. 8 and 9
    4. 7 and 8
    Answer

    (c) 8 and 9

    ADD increases the number of columns to 8; one INSERT increases the number of rows to 9.

Assertion–Reason

1. Assertion (A): CHAR is a better choice than VARCHAR for a Gender column that stores 'M' or 'F'.

   Reason (R): CHAR is a fixed-length type, which suits values that always have the same length.

   Answer

   (A)

   Every gender value has length 1, so fixed-length CHAR(1) wastes no space and R correctly explains A.

2. Assertion (A): The query SELECT * FROM STUDENT WHERE Marks = NULL; returns no rows even though some marks are NULL.

   Reason (R): NULL represents an unknown value, so a comparison with = is never true; IS NULL must be used.

   Answer

   (A)

   Both are true, and R explains why the = comparison fails.

3. Assertion (A): A table can have two primary keys.

   Reason (R): A primary key may consist of more than one column.

   Answer

   (D)

   A table has only one primary key, so A is false. R is true: a composite primary key has several columns.

4. Assertion (A): DELETE FROM STUDENT; removes the table STUDENT from the database.

   Reason (R): DELETE is a DML command that removes rows from a table.

   Answer

   (D)

   DELETE keeps the table structure, so A is false. R is a correct statement.

5. Assertion (A): SQL is a non-procedural language.

   Reason (R): SQL keywords are not case-sensitive.

   Answer

   (B)

   Both statements are true, but case-insensitivity has nothing to do with being non-procedural.

6. Assertion (A): The condition Marks BETWEEN 50 AND 60 selects a student who scored exactly 60.

   Reason (R): BETWEEN includes both the lower and the upper boundary values.

   Answer

   (A)

   Because both ends are included, 60 is selected; R explains A.

7. Assertion (A): A column with the UNIQUE constraint can contain NULL in more than one row in MySQL.

   Reason (R): A PRIMARY KEY column can contain NULL values.

   Answer

   (C)

   UNIQUE allows multiple NULLs in MySQL, so A is true. A primary key never allows NULL, so R is false.

Short answer

1. 2 marks

   Write any four advantages of using SQL.

   Answer

   1. Easy to learn: uses simple English keywords.
   2. Non-procedural: we state what is needed, not how to get it.
   3. Portable/standard: the same statements work in most RDBMS such as MySQL and Oracle.
   4. One language for all tasks: defining tables, changing data and querying are all done in SQL; large data is retrieved quickly.
2. 2 marks

   Differentiate between DDL and DML. Give two commands of each.

   Answer

   DDL (Data Definition Language) defines or changes the structure of database objects, e.g. CREATE TABLE, ALTER TABLE, DROP.

   DML (Data Manipulation Language) works on the data stored in tables, e.g. INSERT, UPDATE, DELETE.

3. 2 marks

   Differentiate between CHAR and VARCHAR data types with an example.

   Answer

   CHAR(n) is fixed length: it always uses n characters, padding with spaces. VARCHAR(n) is variable length: it uses only as many characters as entered (plus a length byte).

   Example: storing 'Ravi' in CHAR(10) uses 10 characters; in VARCHAR(10) it uses 4. CHAR suits fixed-size data like Gender CHAR(1); VARCHAR suits names.

4. 2 marks

   Differentiate between a primary key and a foreign key with an example.

   Answer

   A primary key uniquely identifies each row of its own table; it cannot be NULL or repeated, e.g. RollNo in STUDENT.

   A foreign key is a column in another table that refers to that primary key, linking the two tables; it may repeat, e.g. RollNo in FEE refers to STUDENT(RollNo), so one student can have many fee receipts.

5. 2 marks

   How is the UNIQUE constraint different from the PRIMARY KEY constraint?

   Answer

   • UNIQUE allows NULL values; PRIMARY KEY does not.
   • A table can have many UNIQUE columns but only one PRIMARY KEY.

   Both prevent duplicate values. Example: Email UNIQUE, RollNo PRIMARY KEY.

6. 2 marks

   Differentiate between DELETE and DROP commands.

   Answer

   • DELETE is DML; DROP is DDL.
   • DELETE removes rows (all, or those matching a WHERE condition) and keeps the table structure; DROP removes the entire table (structure and data).

   Example: DELETE FROM STUDENT WHERE RollNo = 7; versus DROP TABLE STUDENT;

7. 2 marks

   Differentiate between ALTER and UPDATE commands with one example each.

   Answer

   ALTER (DDL) changes the structure of a table, e.g. ALTER TABLE STUDENT ADD Phone CHAR(10);

   UPDATE (DML) changes values in existing rows, e.g. UPDATE STUDENT SET Marks = 80 WHERE RollNo = 3;

8. 2 marks

   What is NULL? Why can we not use = to search for NULL values?

   Answer

   NULL means a value is missing or unknown; it is not 0 or a blank. Since the value is unknown, comparing it with anything using = (even with NULL) is never true, so no rows are returned. We must use IS NULL or IS NOT NULL, e.g. SELECT * FROM STUDENT WHERE Marks IS NULL;

9. 2 marks

   Explain the wildcard characters used with LIKE, with one example each.

   Answer

   • % matches zero or more characters: Name LIKE 'S%' finds names starting with S.
   • _ matches exactly one character: Name LIKE '____' finds names of exactly 4 characters; Name LIKE '_e%' finds names whose second letter is e.
10. 2 marks

    Write SQL statements to (i) create a database LIBRARY, (ii) open it, and (iii) list the tables in it.

    Answer

    CREATE DATABASE LIBRARY;
    USE LIBRARY;
    SHOW TABLES;

11. 2 marks

    Rewrite the following query after removing all errors. Underline each correction.

    SELECT Name Marks FROM STUDENT WHERE Marks = NULL ORDER Name DESC

    Answer

    Corrections: comma between Name and Marks; IS NULL instead of = NULL; ORDER BY instead of ORDER; semicolon at the end.

    SELECT Name, Marks FROM STUDENT WHERE Marks IS NULL ORDER BY Name DESC;

12. 2 marks

    The table STUDENT has 7 columns and 8 rows. What will be its degree and cardinality after: (a) ALTER TABLE STUDENT ADD Phone CHAR(10); (b) two new rows are inserted (c) DELETE FROM STUDENT WHERE RollNo = 7;

    Answer

    Degree = 7 + 1 = 8 (only ALTER changes columns).

    Cardinality = 8 + 2 − 1 = 9 (INSERT and DELETE change rows).

13. 2 marks

    Rewrite the following conditions using IN and BETWEEN respectively:

    (i) Section = 'A' OR Section = 'B'
    (ii) Marks >= 40 AND Marks <= 60

    Answer

    (i) Section IN ('A', 'B')

    (ii) Marks BETWEEN 40 AND 60

14. 2 marks

    What is the use of the DISTINCT keyword? Write a query to display the different classes in STUDENT.

    Answer

    DISTINCT removes duplicate values from the result so each value is shown only once.

    SELECT DISTINCT Class FROM STUDENT;

    Output

    +-------+
    | Class |
    +-------+
    |    11 |
    |    12 |
    +-------+
    2 rows in set (0.00 sec)

15. 2 marks

    What is the DEFAULT constraint? Write a column definition so that City gets 'Jaipur' when no value is given.

    Answer

    DEFAULT supplies a value automatically for a column when an INSERT does not give one.

    City VARCHAR(20) DEFAULT 'Jaipur'

Long answer

1. 5 marks

   Consider the table STUDENT given in the notes (RollNo, Name, Class, Section, Gender, DOB, Marks). Write SQL queries for the following:

   (i) Display the names and marks of all girls, highest marks first.
   (ii) Display the details of students of Class 11 whose marks are between 40 and 80.
   (iii) Display the roll number and name of students whose name ends with 'r' or whose section is 'C'.
   (iv) Display the names of students whose date of birth has not been recorded.
   (v) Display the name and marks of each student along with marks increased by 10%, using the heading "Revised", for students who have marks.

   Answer

   Queries with results on the sample data:

   -- (i)
   SELECT Name, Marks FROM STUDENT WHERE Gender = 'F' ORDER BY Marks DESC;
   -- (ii)
   SELECT * FROM STUDENT WHERE Class = 11 AND Marks BETWEEN 40 AND 80;
   -- (iii)
   SELECT RollNo, Name FROM STUDENT WHERE Name LIKE '%r' OR Section = 'C';
   -- (iv)
   SELECT Name FROM STUDENT WHERE DOB IS NULL;
   -- (v)
   SELECT Name, Marks, Marks * 1.1 AS Revised FROM STUDENT WHERE Marks IS NOT NULL;

   Output

   +-------------+-------+
   | Name        | Marks |
   +-------------+-------+
   | Diya Menon  |    91 |
   | Sneha Patil |    84 |
   | Meera Nair  |    70 |
   | Ananya Iyer |  NULL |
   +-------------+-------+
   4 rows in set (0.00 sec)
   
   +--------+--------------+-------+---------+--------+------------+-------+
   | RollNo | Name         | Class | Section | Gender | DOB        | Marks |
   +--------+--------------+-------+---------+--------+------------+-------+
   |      1 | Aarav Sharma |    11 | A       | M      | 2009-03-14 |    78 |
   |      3 | Kabir Singh  |    11 | A       | M      | 2008-11-05 |    45 |
   |      8 | Meera Nair   |    11 | B       | F      | 2008-05-10 |    70 |
   +--------+--------------+-------+---------+--------+------------+-------+
   3 rows in set (0.00 sec)
   
   +--------+--------------+
   | RollNo | Name         |
   +--------+--------------+
   |      4 | Ananya Iyer  |
   |      7 | Vihaan Gupta |
   |      8 | Meera Nair   |
   +--------+--------------+
   3 rows in set (0.00 sec)
   
   +--------------+
   | Name         |
   +--------------+
   | Vihaan Gupta |
   +--------------+
   1 row in set (0.00 sec)
   
   +--------------+-------+---------+
   | Name         | Marks | Revised |
   +--------------+-------+---------+
   | Aarav Sharma |    78 |    85.8 |
   | Diya Menon   |    91 |   100.1 |
   | Kabir Singh  |    45 |    49.5 |
   | Rohan Das    |    62 |    68.2 |
   | Sneha Patil  |    84 |    92.4 |
   | Vihaan Gupta |    38 |    41.8 |
   | Meera Nair   |    70 |    77.0 |
   +--------------+-------+---------+
   7 rows in set (0.00 sec)

2. 5 marks

   A stationery shop stores its stock in the table ITEM:

   Columns: ICode, IName, Category, Price, Qty, LastSold

   • I101 | Gel Pen | Pen | 15.00 | 120 | 2025-07-12
   • I102 | Fountain Pen | Pen | 180.00 | 25 | 2025-06-30
   • I103 | Notebook A4 | Paper | 60.00 | 200 | 2025-07-15
   • I104 | Geometry Box | Kit | 245.50 | 18 | NULL
   • I105 | Drawing Sheet | Paper | 8.00 | 500 | 2025-07-10
   • I106 | Pencil Box | Kit | 99.00 | NULL | 2025-05-21
   • I107 | Marker Pen | Pen | 35.00 | 60 | 2025-07-01

   Write SQL queries to:

   (i) Display the name and price of items in the categories 'Pen' and 'Kit'.
   (ii) Display the name and stock value (Price × Qty) of each item with the heading StockValue.
   (iii) Display the details of items sold between 1 July 2025 and 12 July 2025.
   (iv) Display the names of items whose name contains the word 'Pen', in alphabetical order.
   (v) Increase the price of all 'Paper' items by 2.

   Answer

   Note two traps in the results: Pencil Box shows NULL in (ii) because its Qty is NULL, and Pencil Box also appears in (iv) because 'Pen' occurs inside 'Pencil'.

   -- (i)
   SELECT IName, Price FROM ITEM WHERE Category IN ('Pen', 'Kit');
   -- (ii)
   SELECT IName, Price * Qty AS StockValue FROM ITEM;
   -- (iii)
   SELECT * FROM ITEM WHERE LastSold BETWEEN '2025-07-01' AND '2025-07-12';
   -- (iv)
   SELECT IName FROM ITEM WHERE IName LIKE '%Pen%' ORDER BY IName;
   -- (v)
   UPDATE ITEM SET Price = Price + 2 WHERE Category = 'Paper';

   Output

   +--------------+--------+
   | IName        | Price  |
   +--------------+--------+
   | Gel Pen      |  15.00 |
   | Fountain Pen | 180.00 |
   | Geometry Box | 245.50 |
   | Pencil Box   |  99.00 |
   | Marker Pen   |  35.00 |
   +--------------+--------+
   5 rows in set (0.00 sec)
   
   +---------------+------------+
   | IName         | StockValue |
   +---------------+------------+
   | Gel Pen       |    1800.00 |
   | Fountain Pen  |    4500.00 |
   | Notebook A4   |   12000.00 |
   | Geometry Box  |    4419.00 |
   | Drawing Sheet |    4000.00 |
   | Pencil Box    |       NULL |
   | Marker Pen    |    2100.00 |
   +---------------+------------+
   7 rows in set (0.00 sec)
   
   +-------+---------------+----------+-------+-----+------------+
   | ICode | IName         | Category | Price | Qty | LastSold   |
   +-------+---------------+----------+-------+-----+------------+
   | I101  | Gel Pen       | Pen      | 15.00 | 120 | 2025-07-12 |
   | I105  | Drawing Sheet | Paper    |  8.00 | 500 | 2025-07-10 |
   | I107  | Marker Pen    | Pen      | 35.00 |  60 | 2025-07-01 |
   +-------+---------------+----------+-------+-----+------------+
   3 rows in set (0.00 sec)
   
   +--------------+
   | IName        |
   +--------------+
   | Fountain Pen |
   | Gel Pen      |
   | Marker Pen   |
   | Pencil Box   |
   +--------------+
   4 rows in set (0.00 sec)
   
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0

3. 3 marks

   Write the output of the following queries based on the sample STUDENT table:

   (i) SELECT Name, Marks FROM STUDENT WHERE Section = 'A' AND Marks > 50;
   (ii) SELECT DISTINCT Gender FROM STUDENT WHERE Class = 11;
   (iii) SELECT Name, DOB FROM STUDENT WHERE Name LIKE '%a' OR DOB < '2008-01-01';

   Answer

   SELECT Name, Marks FROM STUDENT WHERE Section = 'A' AND Marks > 50;
   SELECT DISTINCT Gender FROM STUDENT WHERE Class = 11;
   SELECT Name, DOB FROM STUDENT WHERE Name LIKE '%a' OR DOB < '2008-01-01';

   Output

   +--------------+-------+
   | Name         | Marks |
   +--------------+-------+
   | Aarav Sharma |    78 |
   | Sneha Patil  |    84 |
   +--------------+-------+
   2 rows in set (0.00 sec)
   
   +--------+
   | Gender |
   +--------+
   | M      |
   | F      |
   +--------+
   2 rows in set (0.00 sec)
   
   +--------------+------------+
   | Name         | DOB        |
   +--------------+------------+
   | Aarav Sharma | 2009-03-14 |
   | Sneha Patil  | 2007-12-25 |
   | Vihaan Gupta | NULL       |
   +--------------+------------+
   3 rows in set (0.00 sec)

4. 3 marks

   Consider the table PLAYER of a cricket league:

   Columns: PID, PName, Team, Role, Runs, Wickets, Debut

   • 1 | Rahul Yadav | Mumbai Stars | Batter | 1450 | 2 | 2019-04-10
   • 2 | Arjun Mehta | Delhi Kings | Bowler | 210 | 58 | 2020-03-22
   • 3 | Farhan Ali | Mumbai Stars | All-rounder | 890 | 31 | 2018-11-02
   • 4 | Karan Bhatia | Chennai Lions | Batter | 1720 | NULL | 2017-09-15
   • 5 | Nikhil Rao | Delhi Kings | Wicketkeeper | 640 | NULL | 2021-01-08
   • 6 | Sameer Khan | Chennai Lions | Bowler | 95 | 72 | 2019-12-01

   Write the output of:

   (i) SELECT PName, Runs FROM PLAYER WHERE Team = 'Delhi Kings' OR Runs > 1500;
   (ii) SELECT PName, Wickets FROM PLAYER WHERE Wickets IS NULL;
   (iii) SELECT PName, Role FROM PLAYER WHERE Debut BETWEEN '2019-01-01' AND '2020-12-31' ORDER BY PName DESC;

   Answer

   SELECT PName, Runs FROM PLAYER WHERE Team = 'Delhi Kings' OR Runs > 1500;
   SELECT PName, Wickets FROM PLAYER WHERE Wickets IS NULL;
   SELECT PName, Role FROM PLAYER WHERE Debut BETWEEN '2019-01-01' AND '2020-12-31' ORDER BY PName DESC;

   Output

   +--------------+------+
   | PName        | Runs |
   +--------------+------+
   | Arjun Mehta  |  210 |
   | Karan Bhatia | 1720 |
   | Nikhil Rao   |  640 |
   +--------------+------+
   3 rows in set (0.00 sec)
   
   +--------------+---------+
   | PName        | Wickets |
   +--------------+---------+
   | Karan Bhatia |    NULL |
   | Nikhil Rao   |    NULL |
   +--------------+---------+
   2 rows in set (0.00 sec)
   
   +-------------+--------+
   | PName       | Role   |
   +-------------+--------+
   | Sameer Khan | Bowler |
   | Rahul Yadav | Batter |
   | Arjun Mehta | Bowler |
   +-------------+--------+
   3 rows in set (0.00 sec)

5. 5 marks

   Based on the table PLAYER given in the previous question, write SQL statements to:

   (i) Display the name, team and runs of all batters, in descending order of runs.
   (ii) Display the names of players whose name starts with 'S' or 'K'.
   (iii) Set the wickets of all players whose wickets are not recorded to 0.
   (iv) Add a column Fees of type DECIMAL(10,2) to the table.
   (v) Remove the record of the player with PID 6.

   Answer

   -- (i)
   SELECT PName, Team, Runs FROM PLAYER WHERE Role = 'Batter' ORDER BY Runs DESC;
   -- (ii)
   SELECT PName FROM PLAYER WHERE PName LIKE 'S%' OR PName LIKE 'K%';
   -- (iii)
   UPDATE PLAYER SET Wickets = 0 WHERE Wickets IS NULL;
   -- (iv)
   ALTER TABLE PLAYER ADD Fees DECIMAL(10,2);
   -- (v)
   DELETE FROM PLAYER WHERE PID = 6;

   Output

   +--------------+---------------+------+
   | PName        | Team          | Runs |
   +--------------+---------------+------+
   | Karan Bhatia | Chennai Lions | 1720 |
   | Rahul Yadav  | Mumbai Stars  | 1450 |
   +--------------+---------------+------+
   2 rows in set (0.00 sec)
   
   +--------------+
   | PName        |
   +--------------+
   | Karan Bhatia |
   | Sameer Khan  |
   +--------------+
   2 rows in set (0.00 sec)
   
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   
   Query OK, 0 rows affected (0.00 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   Query OK, 1 row affected (0.00 sec)

6. 3 marks

   Write an SQL statement to create a table BOOK with the following structure, then insert one record into it.

   • BookID: integer, primary key
   • Title: variable-length text up to 40 characters, must not be empty
   • Author: variable-length text up to 30 characters
   • Price: exact number with 2 decimal places, up to 9999.99
   • Language: 10 characters, default 'English'
   • PubDate: date of publication
   Answer

   Price uses DECIMAL(6,2) (6 digits in all, 2 after the point). Language is not listed in the INSERT, so it takes its default value.

   CREATE TABLE BOOK (
       BookID   INT PRIMARY KEY,
       Title    VARCHAR(40) NOT NULL,
       Author   VARCHAR(30),
       Price    DECIMAL(6,2),
       Language CHAR(10) DEFAULT 'English',
       PubDate  DATE
   );
   INSERT INTO BOOK (BookID, Title, Author, Price, PubDate)
   VALUES (101, 'Wings of Fire', 'A. P. J. Abdul Kalam', 299.00, '1999-01-01');
   SELECT * FROM BOOK;

   Output

   Query OK, 0 rows affected (0.00 sec)
   
   Query OK, 1 row affected (0.00 sec)
   
   +--------+---------------+----------------------+--------+----------+------------+
   | BookID | Title         | Author               | Price  | Language | PubDate    |
   +--------+---------------+----------------------+--------+----------+------------+
   |    101 | Wings of Fire | A. P. J. Abdul Kalam | 299.00 | English  | 1999-01-01 |
   +--------+---------------+----------------------+--------+----------+------------+
   1 row in set (0.00 sec)

7. 5 marks

   What are constraints? Explain NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY and FOREIGN KEY with the help of a CREATE TABLE statement for two related tables CLASSTEACHER and STUDENT.

   Answer

   A constraint is a rule applied to a column or table that the RDBMS checks whenever data is inserted or changed; rows that break the rule are rejected, keeping the data valid.

   • NOT NULL: a value must be given (TName).
   • UNIQUE: no two rows may repeat the value, though NULL is allowed (Phone).
   • DEFAULT: value used when none is given (Section defaults to 'A').
   • PRIMARY KEY: unique, non-NULL identifier; one per table (TID, RollNo).
   • FOREIGN KEY: value must exist in the referenced primary key; links STUDENT to CLASSTEACHER (TID). The parent table CLASSTEACHER is created first.

   CREATE TABLE CLASSTEACHER (
       TID    INT PRIMARY KEY,
       TName  VARCHAR(30) NOT NULL,
       Phone  CHAR(10) UNIQUE
   );
   
   CREATE TABLE STUDENT (
       RollNo  INT PRIMARY KEY,
       Name    VARCHAR(30) NOT NULL,
       Section CHAR(1) DEFAULT 'A',
       TID     INT,
       FOREIGN KEY (TID) REFERENCES CLASSTEACHER(TID)
   );

8. 3 marks

   A table MEMBER was created without a primary key: CREATE TABLE MEMBER (MID INT, MName VARCHAR(20), City VARCHAR(15)); Write SQL statements to:

   (i) make MID the primary key,
   (ii) change the size of MName to 30 characters and make it compulsory,
   (iii) remove the column City.

   Answer

   ALTER TABLE MEMBER ADD PRIMARY KEY (MID);
   ALTER TABLE MEMBER MODIFY MName VARCHAR(30) NOT NULL;
   ALTER TABLE MEMBER DROP City;

   Output

   Query OK, 0 rows affected (0.00 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   Query OK, 0 rows affected (0.00 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   Query OK, 0 rows affected (0.00 sec)
   Records: 0  Duplicates: 0  Warnings: 0

Case-based

Practicals

CREATE DATABASE SCHOOL;
SHOW DATABASES LIKE 'SCHOOL';
USE SCHOOL;
SHOW TABLES;

Query OK, 1 row affected (0.00 sec)

+-------------------+
| Database (SCHOOL) |
+-------------------+
| SCHOOL            |
+-------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

CREATE TABLE STUDENT (
    RollNo  INT PRIMARY KEY,
    Name    VARCHAR(30) NOT NULL,
    Class   INT,
    Section CHAR(1) DEFAULT 'A',
    Gender  CHAR(1),
    DOB     DATE,
    Marks   INT
);
DESC STUDENT;

Query OK, 0 rows affected (0.00 sec)

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| RollNo  | int         | NO   | PRI | NULL    |       |
| Name    | varchar(30) | NO   |     | NULL    |       |
| Class   | int         | YES  |     | NULL    |       |
| Section | char(1)     | YES  |     | A       |       |
| Gender  | char(1)     | YES  |     | NULL    |       |
| DOB     | date        | YES  |     | NULL    |       |
| Marks   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

INSERT INTO STUDENT VALUES
(1,  'Aarav Sharma', 11, 'A', 'M', '2009-03-14', 78),
(2,  'Diya Menon',   12, 'B', 'F', '2008-07-22', 91),
(3,  'Kabir Singh',  11, 'A', 'M', '2008-11-05', 45),
(4,  'Ananya Iyer',  12, 'C', 'F', '2008-01-30', NULL),
(5,  'Rohan Das',    12, 'B', 'M', '2009-09-18', 62),
(6,  'Sneha Patil',  12, 'A', 'F', '2007-12-25', 84),
(7,  'Vihaan Gupta', 11, 'C', 'M', NULL,         38),
(8,  'Meera Nair',   11, 'B', 'F', '2008-05-10', 70),
(9,  'Ishaan Verma', 12, 'C', 'M', '2008-02-14', 55),
(10, 'Pooja Reddy',  11, 'A', 'F', '2009-06-03', 49);

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

SELECT * FROM STUDENT;

+--------+--------------+-------+---------+--------+------------+-------+
| RollNo | Name         | Class | Section | Gender | DOB        | Marks |
+--------+--------------+-------+---------+--------+------------+-------+
|      1 | Aarav Sharma |    11 | A       | M      | 2009-03-14 |    78 |
|      2 | Diya Menon   |    12 | B       | F      | 2008-07-22 |    91 |
|      3 | Kabir Singh  |    11 | A       | M      | 2008-11-05 |    45 |
|      4 | Ananya Iyer  |    12 | C       | F      | 2008-01-30 |  NULL |
|      5 | Rohan Das    |    12 | B       | M      | 2009-09-18 |    62 |
|      6 | Sneha Patil  |    12 | A       | F      | 2007-12-25 |    84 |
|      7 | Vihaan Gupta |    11 | C       | M      | NULL       |    38 |
|      8 | Meera Nair   |    11 | B       | F      | 2008-05-10 |    70 |
|      9 | Ishaan Verma |    12 | C       | M      | 2008-02-14 |    55 |
|     10 | Pooja Reddy  |    11 | A       | F      | 2009-06-03 |    49 |
+--------+--------------+-------+---------+--------+------------+-------+
10 rows in set (0.00 sec)

SELECT RollNo, Name, Marks FROM STUDENT WHERE Marks > 50;

+--------+--------------+-------+
| RollNo | Name         | Marks |
+--------+--------------+-------+
|      1 | Aarav Sharma |    78 |
|      2 | Diya Menon   |    91 |
|      5 | Rohan Das    |    62 |
|      6 | Sneha Patil  |    84 |
|      8 | Meera Nair   |    70 |
|      9 | Ishaan Verma |    55 |
+--------+--------------+-------+
6 rows in set (0.00 sec)

SELECT RollNo, Name, DOB FROM STUDENT WHERE DOB BETWEEN '2008-01-01' AND '2008-12-31';

+--------+--------------+------------+
| RollNo | Name         | DOB        |
+--------+--------------+------------+
|      2 | Diya Menon   | 2008-07-22 |
|      3 | Kabir Singh  | 2008-11-05 |
|      4 | Ananya Iyer  | 2008-01-30 |
|      8 | Meera Nair   | 2008-05-10 |
|      9 | Ishaan Verma | 2008-02-14 |
+--------+--------------+------------+
5 rows in set (0.00 sec)

SELECT Name, Class, Section, Marks FROM STUDENT
WHERE Gender = 'F' AND Section IN ('A', 'C');
SELECT RollNo, Name, Marks FROM STUDENT WHERE Class = 12 ORDER BY Marks DESC;

+-------------+-------+---------+-------+
| Name        | Class | Section | Marks |
+-------------+-------+---------+-------+
| Ananya Iyer |    12 | C       |  NULL |
| Sneha Patil |    12 | A       |    84 |
| Pooja Reddy |    11 | A       |    49 |
+-------------+-------+---------+-------+
3 rows in set (0.00 sec)

+--------+--------------+-------+
| RollNo | Name         | Marks |
+--------+--------------+-------+
|      2 | Diya Menon   |    91 |
|      6 | Sneha Patil  |    84 |
|      5 | Rohan Das    |    62 |
|      9 | Ishaan Verma |    55 |
|      4 | Ananya Iyer  |  NULL |
+--------+--------------+-------+
5 rows in set (0.00 sec)

SELECT Name FROM STUDENT WHERE Name LIKE 'A%' OR Name LIKE 'S%';
SELECT Name FROM STUDENT WHERE Name LIKE '_a%';
SELECT RollNo, Name FROM STUDENT WHERE Marks IS NULL OR DOB IS NULL;

+--------------+
| Name         |
+--------------+
| Aarav Sharma |
| Ananya Iyer  |
| Sneha Patil  |
+--------------+
3 rows in set (0.00 sec)

+--------------+
| Name         |
+--------------+
| Aarav Sharma |
| Kabir Singh  |
+--------------+
2 rows in set (0.00 sec)

+--------+--------------+
| RollNo | Name         |
+--------+--------------+
|      4 | Ananya Iyer  |
|      7 | Vihaan Gupta |
+--------+--------------+
2 rows in set (0.00 sec)

UPDATE STUDENT SET Marks = 88 WHERE RollNo = 4;
UPDATE STUDENT SET Marks = Marks + 5 WHERE Marks < 50;
DELETE FROM STUDENT WHERE RollNo = 7;
SELECT RollNo, Name, Marks FROM STUDENT;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 1 row affected (0.00 sec)

+--------+--------------+-------+
| RollNo | Name         | Marks |
+--------+--------------+-------+
|      1 | Aarav Sharma |    78 |
|      2 | Diya Menon   |    91 |
|      3 | Kabir Singh  |    50 |
|      4 | Ananya Iyer  |    88 |
|      5 | Rohan Das    |    62 |
|      6 | Sneha Patil  |    84 |
|      8 | Meera Nair   |    70 |
|      9 | Ishaan Verma |    55 |
|     10 | Pooja Reddy  |    54 |
+--------+--------------+-------+
9 rows in set (0.00 sec)

ALTER TABLE STUDENT ADD Phone CHAR(10);
UPDATE STUDENT SET Phone = '9876543210' WHERE RollNo = 2;
SELECT RollNo, Name, Phone FROM STUDENT WHERE RollNo <= 3;
ALTER TABLE STUDENT MODIFY Name VARCHAR(40) NOT NULL;
ALTER TABLE STUDENT DROP Phone;
CREATE TABLE TEMP_LIST (Item VARCHAR(20));
DROP TABLE TEMP_LIST;
SHOW TABLES;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+--------+--------------+------------+
| RollNo | Name         | Phone      |
+--------+--------------+------------+
|      1 | Aarav Sharma | NULL       |
|      2 | Diya Menon   | 9876543210 |
|      3 | Kabir Singh  | NULL       |
+--------+--------------+------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------------------+
| Tables_in_school |
+------------------+
| STUDENT          |
+------------------+
1 row in set (0.00 sec)

Common mistakes

• Writing WHERE Marks = NULL or WHERE Marks = 'NULL'. Both return no rows. The correct test is WHERE Marks IS NULL.

• Forgetting quotes around text and dates (WHERE Name = Diya Menon, DOB > 2008-01-01). Text and dates must be in single quotes: 'Diya Menon', '2008-01-01'. Numbers need no quotes.

• Writing dates as '22-07-2008' or '22/07/2008'. MySQL expects 'YYYY-MM-DD', i.e. '2008-07-22'.

• Using = with wildcards (WHERE Name = 'A%'). Wildcards work only with LIKE: WHERE Name LIKE 'A%'.

• Writing WHERE Section = 'A' OR 'C'. Each condition needs its own column: WHERE Section = 'A' OR Section = 'C', or better WHERE Section IN ('A', 'C').

• Running UPDATE or DELETE without a WHERE clause and changing every row. Always check the rows with a SELECT using the same WHERE first.

• Confusing DELETE with DROP. DELETE FROM STUDENT; empties the table but keeps it; DROP TABLE STUDENT; removes the table itself.

• Confusing ALTER with UPDATE. ALTER changes the structure (columns); UPDATE changes values in rows.

• Putting ORDER BY before WHERE, or writing ORDER Marks. The order is SELECT, FROM, WHERE, ORDER BY, and the keyword is two words: ORDER BY.

• Missing the semicolon in the MySQL client and thinking the computer has hung at the -> prompt.