📌 Snapshot
- SQL is the standard query language for Relational Database Management Systems (RDBMS); MySQL is the working environment.
- The full DDL–DML–DQL cycle covers: creating databases and tables (DDL), inserting, updating and deleting records (DML), and querying with SELECT (DQL).
- SQL syntax, data types, constraints, and SELECT clause operators (WHERE, BETWEEN, IN, LIKE, ORDER BY, DISTINCT, IS NULL) are direct, objective-answer topics, so CUET tests them heavily.
- A running example — the
StudentAttendancedatabase with relations STUDENT, GUARDIAN and ATTENDANCE — illustrates every concept, making it ideal for case-based MCQs. - Data updation (UPDATE) and deletion (DELETE) complete every core RDBMS operation a Class XI student needs to know for CUET.
📖 Detailed Notes
2.1 Core concepts
- What is SQL? SQL (Structured Query Language) is the most popular query language used by major RDBMS such as MySQL, ORACLE, and SQL Server. Unlike file-system programs, SQL lets a user specify what to retrieve rather than how to retrieve it. SQL covers data definition, data manipulation, constraints, and data retrieval. (NCERT §8.2, p. 144)
- MySQL basics: MySQL is open-source RDBMS software. After installation and starting the MySQL service, the
mysql>prompt appears. SQL statements are case-insensitive; every statement ends with a semicolon (;). Multi-line statements use->as the continuation prompt until;is typed. (NCERT §8.2.1, p. 144–145) - Data types — CHAR(n): Fixed-length character data; length 0–255. If actual data is shorter, MySQL pads remaining spaces on the right. Suitable when stored strings always have the same length (e.g., a 12-digit Aadhaar number as CHAR(12)). (NCERT §8.3.1, Table 8.1, p. 145)
- Data types — VARCHAR(n): Variable-length character data; length 0–65535. Only the actual bytes needed are stored, so VARCHAR(30) storing 'city' uses 4-character space. More space-efficient than CHAR for variable-length data like names. (NCERT §8.3.1, Table 8.1, p. 145)
- Data types — INT: Integer value occupying 4 bytes; range −2147483648 to 2147483647. Use BIGINT (8 bytes) for larger values. Suitable for roll numbers, IDs. (NCERT §8.3.1, Table 8.1, p. 146)
- Data types — FLOAT: Numbers with decimal points; 4 bytes of storage. (NCERT §8.3.1, Table 8.1, p. 146)
- Data types — DATE: Stores dates in
'YYYY-MM-DD'format. Supported range: '1000-01-01' to '9999-12-31'. (NCERT §8.3.1, Table 8.1, p. 146) - Constraints: Constraints restrict the data values an attribute can hold, ensuring accuracy and reliability. It is not mandatory to define a constraint for every attribute. Five constraints are covered: NOT NULL (column cannot be empty), UNIQUE (all values in column are distinct), DEFAULT (a pre-set value used when no value is supplied), PRIMARY KEY (uniquely identifies each row; combines NOT NULL + UNIQUE implicitly), and FOREIGN KEY (references the primary key of another table). (NCERT §8.3.2, Table 8.2, p. 146)
- DDL — CREATE DATABASE:
CREATE DATABASE databasename;creates a new database. TheUSE databasename;statement selects it as the active database.SHOW TABLES;lists all tables in the active database. (NCERT §8.4.1, p. 146–147) - DDL — CREATE TABLE: Defines a relation with attribute names, data types, and optional constraints. Syntax:
CREATE TABLE tablename (attr1 datatype constraint, attr2 datatype constraint, ...);. N attributes means the relation has degree N. By default, each attribute can take NULL except primary key attributes. (NCERT §8.4.2, p. 147–148) - Relation schemas used in chapter: STUDENT (RollNumber INT PK, SName VARCHAR(20) NOT NULL, SDateofBirth DATE NOT NULL, GUID CHAR(12) FK); GUARDIAN (GUID CHAR(12) PK, GName VARCHAR(20) NOT NULL, GPhone CHAR(10) NULL UNIQUE, GAddress VARCHAR(30) NOT NULL); ATTENDANCE (AttendanceDate DATE PK*, RollNumber INT PK* + FK, AttendanceStatus CHAR(1) NOT NULL). The asterisk (*) denotes composite primary key. (NCERT §8.3, Tables 8.3–8.5, p. 148)
- DDL — DESCRIBE/DESC:
DESCRIBE tablename;orDESC tablename;displays the structure of an existing table (field names, types, null, key, default, extra). (NCERT §8.4.3, p. 149) - DDL — ALTER TABLE: Modifies the structure of an existing table. Operations include: ADD PRIMARY KEY, ADD FOREIGN KEY ... REFERENCES, ADD UNIQUE, ADD attribute, MODIFY attribute datatype, MODIFY attribute datatype NOT NULL (to change constraint), MODIFY attribute datatype DEFAULT value, DROP attribute, DROP PRIMARY KEY. (NCERT §8.4.4, p. 149–152)
- DDL — DROP:
DROP TABLE tablename;permanently removes a table (and all its data).DROP DATABASE databasename;removes the entire database including all tables. This action cannot be undone. (NCERT §8.4.5, p. 153) - DML — INSERT INTO:
INSERT INTO tablename VALUES (v1, v2, ...);inserts a record. If inserting values for only some attributes, the syntaxINSERT INTO tablename (col1, col2) VALUES (v1, v2);must be used. Text and date values must be enclosed in single quotes. When a foreign key table is being populated, the referenced table must already have the corresponding record. (NCERT §8.5.1, p. 153–156) - DQL — SELECT statement:
SELECT attr1, attr2 FROM tablename WHERE condition;retrieves data.SELECT *retrieves all columns. The WHERE clause is optional and filters rows. The AS keyword renames a column in the output (alias). Arithmetic expressions likeSalary*12can appear in SELECT. Aliases with spaces must be enclosed in single quotes. (NCERT §8.6, §8.6.1, p. 156–159) - DQL — DISTINCT:
SELECT DISTINCT col FROM table;removes duplicate values from output. (NCERT §8.6.2(C), p. 160) - DQL — WHERE clause operators: Relational operators
=, <, <=, >, >=, !=; logical operators AND, OR, NOT; range operatorBETWEEN val1 AND val2(inclusive of both boundary values); membership operatorIN (v1, v2, ...)(checks if value belongs to a set);NOT INexcludes specified values. (NCERT §8.6.2(D)(E), p. 160–163) - DQL — ORDER BY:
ORDER BY columnsorts output in ascending order by default.ORDER BY column DESCsorts in descending order. Multiple columns can be specified. (NCERT §8.6.2(F), p. 163) - DQL — NULL handling: NULL represents a missing or unknown value; it is different from 0. Any arithmetic on NULL yields NULL (e.g., 5 + NULL = NULL). To filter NULL values:
WHERE column IS NULLorWHERE column IS NOT NULL. The= NULLoperator does NOT work for NULL checks. (NCERT §8.6.2(G), p. 164) - DQL — LIKE operator: Used with WHERE for substring/pattern matching. Two wildcards:
%(percent) — represents zero, one or multiple characters;_(underscore) — represents exactly one character. Example:LIKE 'K%'finds names starting with K;LIKE '%a'finds names ending with 'a';LIKE '_ANYA'finds 5-character names where last 4 are 'ANYA'. (NCERT §8.6.2(H), p. 165–166) - DML — UPDATE:
UPDATE tablename SET attr1=val1, attr2=val2 WHERE condition;modifies existing records. Omitting WHERE updates all rows — a common error to avoid. (NCERT §8.7.1, p. 166–167) - DML — DELETE:
DELETE FROM tablename WHERE condition;deletes matching records. Without WHERE, all records in the table are deleted. Unlike DROP TABLE, the table structure itself is preserved. (NCERT §8.7.2, p. 167–168)
2.2 Definitions to memorise
| Term | Definition | Page |
|---|---|---|
| SQL | Structured Query Language; standard query language for RDBMS; case-insensitive | 144 |
| DDL | Data Definition Language; SQL commands for creating, modifying and deleting relation schemas (CREATE, ALTER, DROP) | 146 |
| DML | Data Manipulation Language; SQL commands for inserting, updating and deleting records (INSERT, UPDATE, DELETE) | 153 |
| CHAR(n) | Fixed-length string type of length n (0–255); unused positions padded with spaces | 145 |
| VARCHAR(n) | Variable-length string type; maximum n characters (0–65535); stores only actual characters | 145 |
| INT | Integer data type; 4 bytes; range −2,147,483,648 to 2,147,483,647 | 146 |
| FLOAT | Numeric type holding decimal values; 4 bytes | 146 |
| DATE | Date type stored as 'YYYY-MM-DD'; range 1000-01-01 to 9999-12-31 | 146 |
| NOT NULL | Constraint ensuring a column cannot have NULL (missing/unknown) values | 146 |
| UNIQUE | Constraint ensuring all values in a column are distinct | 146 |
| DEFAULT | Constraint specifying a pre-set value used when no value is provided for the column | 146 |
| PRIMARY KEY | Constraint uniquely identifying each row in a table; implies NOT NULL + UNIQUE | 146 |
| FOREIGN KEY | Constraint referencing the primary key of another (referenced) table | 146 |
| Composite Primary Key | Primary key made up of two or more attributes together | 148 |
| DESCRIBE / DESC | SQL statement that displays the structure of a table | 149 |
| NULL | Special SQL value representing missing or unknown data; distinct from 0 or empty string | 164 |
| BETWEEN | Operator checking if a value falls within an inclusive range | 162 |
| IN | Membership operator checking if a value matches any value in a given list | 162 |
| LIKE | Operator used with WHERE for pattern matching; uses % and _ wildcards | 165 |
| ORDER BY | Clause that sorts query results by one or more columns; default is ascending | 163 |
| DISTINCT | Clause that eliminates duplicate rows from SELECT output | 160 |
| Alias (AS) | Temporary column name assigned in the SELECT output using AS keyword | 158 |
CREATE DATABASE |
DDL command that creates a new database | 146 |
USE |
Selects the active database for subsequent commands | 147 |
SHOW TABLES |
Lists all tables in the active database | 147 |
CREATE TABLE |
DDL command that defines a new relation with attributes and constraints | 147 |
ALTER TABLE |
DDL command that modifies the structure of an existing table | 149 |
DROP TABLE |
DDL command that permanently removes a table and its data | 153 |
DROP DATABASE |
DDL command removing an entire database including all tables | 153 |
INSERT INTO |
DML command that inserts records into a table | 153 |
UPDATE |
DML command that modifies existing records based on a WHERE clause | 166 |
DELETE |
DML command that removes matching records but preserves table structure | 167 |
Wildcard % |
LIKE wildcard matching zero or more characters | 165 |
Wildcard _ |
LIKE wildcard matching exactly one character | 165 |
IS NULL |
Predicate used to test if a column value is NULL | 164 |
BIGINT |
8-byte integer data type used when INT range is insufficient | 146 |
2.3 Diagrams / processes to remember
- Figure 8.1 — MySQL Shell: Screenshot of the MySQL 5.7 command-line client showing the
mysql>prompt. Students should recognise what the prompt signifies (MySQL is ready for SQL input). (p. 145) - Table 8.1 — Commonly used data types in MySQL: The five data types (CHAR, VARCHAR, INT, FLOAT, DATE) with storage size and range. This table is frequently referenced in MCQs asking which data type is appropriate for a given attribute. (p. 145–146)
- Table 8.2 — Commonly used SQL Constraints: Five constraints with descriptions. The "which two constraints together produce a Primary Key effect" question (NOT NULL + UNIQUE) is a classic CUET trap. (p. 146)
- Tables 8.3–8.5 — Data types and constraints for STUDENT, GUARDIAN, ATTENDANCE: Real schema examples showing how data type and constraint decisions are made attribute by attribute; referencing table and referenced table concepts for foreign key. (p. 148)
- INSERT order dependency: When inserting data into tables with foreign key relationships, the referenced table must be populated first (GUARDIAN before STUDENT, STUDENT before ATTENDANCE). (p. 153–154)
2.4 Common confusions / NTA trap points
- CHAR vs VARCHAR for fixed-length data: A phone number or Aadhaar number is stored as CHAR, not INT — because no arithmetic is performed on it and its length is fixed. NTA may present an attribute and ask the correct data type; choosing INT for a phone number is a common wrong answer.
- NULL is not zero: Any arithmetic involving NULL returns NULL. To filter NULL values,
IS NULLmust be used — writing= NULLis incorrect and returns no results. NTA sometimes presentsWHERE Bonus = NULLas a plausible option. - PRIMARY KEY vs NOT NULL + UNIQUE: PRIMARY KEY implicitly enforces both NOT NULL and UNIQUE, but NOT NULL + UNIQUE alone does not automatically create a PRIMARY KEY designation. Students often confuse these in "which combination equals primary key" assertion–reason questions.
- DROP vs DELETE vs ALTER ... DROP: DROP TABLE removes the entire table structure and data permanently. DELETE removes records but preserves the table structure. ALTER TABLE ... DROP removes a single attribute from the table structure. NTA presents these as distractors in "what does the statement do" questions.
- ORDER BY default order (NCERT §8.6.2(F), p. 163). ORDER BY sorts in ascending order by default. DESC must be explicitly written for descending order.
- BETWEEN is inclusive (NCERT §8.6.2(D), p. 162).
WHERE Marks BETWEEN 50 AND 60includes both 50 and 60. NTA distractor: claims exclusion at boundaries. - SQL is case-insensitive in keywords but case-sensitive in data (NCERT §8.2.1, p. 144).
SELECTandselectare the same; but'akansha'and'Akansha'may match differently depending on collation. - DELETE preserves the table (NCERT §8.7.2, p. 167-168). DROP removes the table itself.
- Foreign key insertion order (NCERT §8.5.1, p. 153-154). Insert the referenced table first; otherwise the FK constraint fails.
- Date format is 'YYYY-MM-DD' (NCERT §8.3.1, p. 146). NTA may suggest 'DD-MM-YYYY' as a plausible option — it is wrong.
SELECT *retrieves all columns (NCERT §8.6, p. 157). A star always means "all attributes" in SQL.
🎯 Practice MCQs
First 3 questions free · create a free account to unlock the rest — answers & explanations included, no payment needed
Q1. Which of the following statements is correct about CHAR(10) and VARCHAR(10) data types in MySQL?
▸ Show answer & explanation
Answer: B
CHAR is fixed-length and always reserves the declared size (padding with spaces if needed), whereas VARCHAR is variable-length and stores only the actual data. Option D is wrong because VARCHAR(n) supports up to 65535 characters, not 255. ---
Q2. Consider the following SQL statement: ``` ALTER TABLE GUARDIAN ADD UNIQUE (GPhone); ``` What is the effect of executing this statement?
▸ Show answer & explanation
Answer: C
ADD UNIQUE adds the UNIQUE constraint to an existing attribute, preventing duplicate values. It does not add, remove, or change the attribute itself. ---
Q3. Match the following SQL commands with their correct functions: | Command | Function | |---|---| | P. INSERT INTO | 1. Permanently removes a table from the database | | Q. DROP TABLE | 2. Adds new records to a table | | R. UPDATE | 3. Displays the structure of a table | | S. DESC | 4. Modifies existing data in a table |
▸ Show answer & explanation
Answer: A
INSERT INTO adds records (§8.5.1), DROP TABLE permanently removes a table (§8.4.5), UPDATE modifies existing data (§8.7.1), and DESC shows a table's structure (§8.4.3). ---
🔒 12 more practice MCQs
Create a free account to unlock every MCQ in this chapter — answers and explanations included. No payment needed.
Already registered? Just log in and they'll all appear here.
Q4. A student writes the following query to find all employees who have not received a bonus: ``` SELECT * FROM EMPLOYEE WHERE Bonus = NULL; ``` Which of the following statements about this query is correct?
▸ Show answer & explanation
Answer: C
NULL represents an unknown value; comparing with `= NULL` is syntactically accepted by MySQL but logically incorrect and returns no rows. The correct syntax is `WHERE Bonus IS NULL`, as demonstrated in Example 8.11. ---
Q5. Assertion (A): In a MySQL INSERT INTO statement, if the number of values provided exactly equals the total number of attributes in the table, it is not necessary to specify the attribute names. Reason (R): When inserting values for only a subset of attributes, both the attribute names and corresponding values must be specified explicitly.
▸ Show answer & explanation
Answer: B
Both statements are independently true facts about INSERT INTO syntax, but R describes a separate rule (partial insert), not the reason why omitting column names is allowed when all values are supplied. Hence R does not explain A. ---
Q6. Which of the following SELECT queries correctly retrieves all employees from the EMPLOYEE table whose name contains exactly 5 characters and has 'ANYA' as the last four characters?
▸ Show answer & explanation
Answer: C
The underscore `_` wildcard matches exactly one character, so `_ANYA` matches any 5-character name ending in 'ANYA'. Option A (`%ANYA`) would also match names longer than 5 characters ending in 'ANYA'. Option D uses two underscores, giving 6-character matches. --- ---
Q7. The SQL clause that removes duplicate values from results is:
▸ Show answer & explanation
Answer: C
Q8. Which date format does MySQL DATE use?
▸ Show answer & explanation
Answer: C
Q9. The SQL query `SELECT * FROM STUDENT WHERE SName LIKE 'A%';` returns:
▸ Show answer & explanation
Answer: B
Q10. Which clause is used with the WHERE to test for a value inside a list?
▸ Show answer & explanation
Answer: B
Q11. Which combination of constraints together produces the effect of a PRIMARY KEY?
▸ Show answer & explanation
Answer: C
Q12. Which statement deletes ALL rows in a table but keeps its structure?
▸ Show answer & explanation
Answer: B
Q13. Output type for the query `SELECT 5 + NULL;`:
▸ Show answer & explanation
Answer: B
Any arithmetic with NULL yields NULL. ---
Q14. Assertion (A): `SELECT * FROM STUDENT ORDER BY SName;` sorts the results in ascending order of name. Reason (R): The default order for `ORDER BY` is ascending.
▸ Show answer & explanation
Answer: A
Q15. The wildcard `_` in the LIKE operator matches:
▸ Show answer & explanation
Answer: B
📊 Previous-Year Questions
Practise with real CUET Computer Science previous-year papers — every question solved, with the correct answer and a step-by-step explanation.
View solved CUET PYQ papers →Ready to drill Computer Science?
Unlock all MCQs, chapter tests, mocks & PYQs for ₹199/year.
Get UniDrill Pro