Class 11 SQL Foundations: Complete Concepts and Practice

Big picture

SQL expresses what data a relational database should define, change or retrieve. A school inventory can be stored in a table and queried without writing a procedural search loop. The same language family supports structure, record changes and read-only questions, but those operations have very different effects.

This chapter uses a small original inventory dataset. Core examples are checked using an in-memory SQLite database; classroom MySQL-specific commands are identified separately. An exam answer should follow the SQL dialect named in the question, especially for database selection and table alteration.

Learning goals

• Distinguish schema changes, record changes and retrieval.

• Define a table with suitable types and constraints.

• Insert records and calculate SELECT expressions.

• Filter with comparisons, patterns and NULL predicates.

• Predict ordered query results and controlled modifications.

Core ideas

1. SQL families and safe statement reading

Structured Query Language, or SQL, is used with relational database systems. DDL defines structure, DML changes stored records, and SELECT retrieves data. Textbooks may group retrieval under DML or discuss DQL separately; recognise the operation rather than arguing only about the label.

SQL keywords are conventionally uppercase for clarity, while string literals use single quotes. A semicolon terminates a statement in common interactive tools. Read an entire UPDATE or DELETE, including its WHERE clause, before deciding what it changes. Omitting that condition can affect every row, not merely the one currently visible on screen.

2. Database and table definitions

In a MySQL classroom, CREATE DATABASE SchoolStore creates a database and USE SchoolStore selects it. SHOW DATABASES, SHOW TABLES and DESCRIBE Item inspect the environment. These commands are dialect-specific and are not the syntax used by SQLite to manage database files.

The portable teaching example defines a table:

CREATE TABLE Item (
    ItemID INTEGER PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Price DECIMAL(7,2) NOT NULL,
    Stock INTEGER CHECK (Stock >= 0)
);

INTEGER represents whole-number data, VARCHAR holds variable-length text, and DECIMAL describes fixed-precision decimal values in systems such as MySQL. SQLite's typing and enforcement differ, so an in-memory syntax check does not prove identical enforcement of every MySQL type constraint. Explain the intended domain as well as writing the type name.

3. Constraints and inserting records

A primary key identifies each row uniquely and cannot be null. NOT NULL requires a value, UNIQUE prevents unwanted repeated values under the database's rules, CHECK states a condition, and DEFAULT supplies an omitted value where defined. Constraints address specific rules rather than proving the whole record is factually correct.

INSERT INTO Item (ItemID, Name, Price, Stock)
VALUES
(11, 'Notebook', 45, 12),
(12, 'Folder', 30, 8),
(13, 'Marker', 25, 0),
(14, 'Sketchbook', 80, 5);

Naming columns makes the mapping explicit and protects readability when table definitions evolve. Quotes identify text; NULL is written without quotes when a missing value is intended. The quoted text 'NULL' is a string, not the missing-value marker. A duplicate primary-key insertion must not silently become a second identical identifier.

4. Projection, expressions and DISTINCT

SELECT chooses the expressions or columns to return, while FROM identifies the source table. SELECT retrieves all columns, but explicitly named columns communicate intent. A calculated expression can have an alias with AS. DISTINCT* removes duplicate result rows from the selected combination, not independently from each column.

SELECT Name, Price * Stock AS Value
FROM Item
ORDER BY ItemID;

The rows are Notebook with 540, Folder with 240, Marker with 0, and Sketchbook with 400. These values follow 45 × 12 = 540, 30 × 8 = 240, 25 × 0 = 0, and 80 × 5 = 400. The query calculates values without storing a new column. An alias labels the result; it does not rename the underlying table attribute permanently.

5. Filtering, comparisons and Boolean conditions

WHERE retains rows whose condition is true. Use = for SQL equality and <> for inequality; comparisons include <, <=, > and >=. AND requires both conditions, OR accepts either, and NOT negates. Parentheses make grouping explicit.

SELECT Name
FROM Item
WHERE Price >= 30 AND Stock > 0
ORDER BY ItemID;

The result is Notebook, Folder and Sketchbook, in that order. Marker fails both the minimum price and positive-stock requirements. A condition Price >= 30 OR Stock > 0 is broader because either fact is enough. Write the rule in words before choosing the operator. SELECT does not alter the matching rows; filtering a result is different from deleting records from the table.

6. BETWEEN, IN, LIKE and NULL

BETWEEN includes both endpoints. IN tests membership in a supplied set. LIKE performs pattern matching: % stands for any sequence of characters, while _ stands for one character. Exact case behaviour can depend on collation and the DBMS, so do not assume all installations compare case identically.

Use IS NULL or IS NOT NULL for missing values. Ordinary equality with NULL does not produce a true match because NULL represents an unknown or absent value; SQL uses three-valued logic in such comparisons. WHERE keeps true rows, not unknown ones. A stock of zero is a known quantity, so Stock = 0 and Stock IS NULL ask different questions. For a pattern such as 'N%', the first character is N, unlike '%N%', which allows it anywhere.

7. Ordering and controlled modification

ORDER BY sorts the result, with ASC as the usual default and DESC for descending order. Multiple sort columns resolve ties in sequence. Without ORDER BY, do not promise that insertion order will be the displayed order.

UPDATE Item SET Stock = Stock + 2 WHERE ItemID = 12;
DELETE FROM Item WHERE ItemID = 13;
SELECT ItemID, Stock FROM Item ORDER BY ItemID;

After these statements, the result is (11, 12), (12, 10), (14, 5). Folder's stock rises from eight to ten, and Marker is removed. The update affects only the specified identifier. A useful habit is to reason through an equivalent SELECT condition first. A correct WHERE clause is part of the modification's meaning, not an optional optimisation.

8. ALTER, DROP and a complete checking method

ALTER TABLE changes a table definition, while DROP TABLE removes the table itself and its data. DELETE removes rows while leaving the table structure. Basic ADD COLUMN syntax is common, but renaming, changing types and dropping columns vary by dialect and version. Use the specified classroom dialect rather than mixing unrelated syntaxes.

For a case-based question, identify the required columns, filtering rule, ordering and whether data should change. Check spelling against the schema. Evaluate the sample rows manually and compare expected membership with the query result. For modifications, record before-and-after values and ensure unrelated rows remain correct. SQLite checking establishes the tested examples' behaviour, but a teacher should confirm MySQL-only administrative commands and course-specific alteration forms. Never infer a result order from how a small tool happens to display an unordered query.

A reliable approach to questions

1. Read the command word before choosing an answer. A definition needs the distinguishing feature; a comparison needs matching criteria on both sides; an application needs a reason linked to the situation.

2. Extract the supplied evidence and assumptions. Keep a small working table rather than relying on memory. If information is absent, state the limit instead of inventing a value or claiming certainty.

3. Work through the relevant rule one step at a time. For a program, record the changing state; for a scenario, connect the requirement to the feature that meets it. Explain why the closest alternative fails.

4. Check the result against the original question, including boundary cases, units and wording. Present a concise final answer followed by enough reasoning for another student to reproduce your conclusion.

Connections that matter

• Database concepts supply keys, domains and integrity rules.

• Boolean logic explains compound WHERE predicates.

• Data projects use queries to retrieve reproducible subsets.

Common mistakes

• Using = NULL: missing values require IS NULL.

• Assuming BETWEEN excludes endpoints: both are included.

• Expecting stable row order without ORDER BY: explicitly request it.

• Confusing DELETE and DROP: one removes rows and the other removes the table.

Revision checklist

• I can distinguish schema changes, record changes and retrieval.

• I can define a table with suitable types and constraints.

• I can insert records and calculate SELECT expressions.

• I can filter with comparisons, patterns and NULL predicates.

• I can predict ordered query results and controlled modifications.