🔍 Databases

SQL: DDL, DML & Queries

SQL की मूल बातें

⏱ 5 hr3 topicsInteractive

🎯 By the end: You can create and alter tables with constraints, insert/update/delete rows, and write SELECT queries with WHERE, ORDER BY, DISTINCT, BETWEEN, IN and LIKE.

SQL (Structured Query Language) is how we talk to a relational database — creating tables, adding data and, above all, asking questions of the data. The best part of this chapter: there's a real SQL database built into the page. You'll write queries and see actual result tables, no setup needed.

1Data types, DDL and constraints

Every column has a data type. The common ones: INT (whole numbers), DECIMAL (numbers with fractions), CHAR(n) (fixed-length text), VARCHAR(n) (variable-length text), and DATE.

DDL (Data Definition Language) commands define the structure:

CREATE TABLE student (
  roll  INT PRIMARY KEY,
  name  VARCHAR(20) NOT NULL,
  class CHAR(3),
  marks INT DEFAULT 0,
  city  VARCHAR(20)
);

ALTER TABLE student ADD email VARCHAR(40);   -- add a column
DROP TABLE student;                           -- delete the whole table

Constraints (rules on the data)

Constraint	Rule
`PRIMARY KEY`	Unique + not NULL — the main identifier
`NOT NULL`	The column must have a value
`UNIQUE`	No two rows may share this value
`DEFAULT`	A fallback value if none is given
`CHECK`	A condition the value must satisfy (e.g. marks between 0 and 100)
`FOREIGN KEY`	Must match a primary key in another table

Key points

Data types: INT, DECIMAL, CHAR(n), VARCHAR(n), DATE.
DDL defines structure: CREATE TABLE, ALTER TABLE (add/modify), DROP TABLE.
Constraints: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, FOREIGN KEY.

2DML — inserting, updating, deleting

DML (Data Manipulation Language) commands change the data in a table:

-- add a row
INSERT INTO student (roll, name, class, marks, city)
VALUES (6, 'Neha', '12A', 91, 'Delhi');

-- change existing rows
UPDATE student SET marks = 95 WHERE roll = 6;

-- remove rows
DELETE FROM student WHERE city = 'Pune';

Always use WHERE with UPDATE and DELETE! DELETE FROM student; with no WHERE clause empties the entire table. The WHERE clause limits the command to the rows you mean.

Key points

DML changes data: INSERT INTO (add rows), UPDATE ... SET (change rows), DELETE FROM (remove rows).
UPDATE and DELETE without a WHERE clause affect EVERY row — always include WHERE.
INSERT can list the columns explicitly before VALUES.

3SELECT — asking questions (try it live!)

The SELECT statement retrieves data — it's the command you'll use most. Its key parts:

Projection — choosing columns: SELECT name, marks (or SELECT * for all).
Selection — choosing rows with WHERE and conditions, using relational operators (=, >, <, >=, <=, <>) and logical operators (AND, OR, NOT).
BETWEEN a AND b — within a range; IN (...) — matches a list; LIKE — pattern match with wildcards % (any characters) and _ (one character).
ORDER BY col ASC|DESC — sort the results; DISTINCT — remove duplicate values.
IS NULL / IS NOT NULL — test for missing values.

The SQL trainer below has a real student table. Edit the query and press Run — try WHERE marks >= 85, ORDER BY marks DESC, or WHERE city = 'Delhi':

SQL Trainer — student table▶ runs real SQL

🗃️ student(roll, name, class, marks, city) — 5 rows

query.sql

SELECT name, class, marks
FROM student
WHERE marks >= 85
ORDER BY marks DESC;

Result

Click Run to execute.

Key points

SELECT does projection (pick columns) and selection (pick rows with WHERE).
WHERE uses =, >, <, >=, <=, <> and AND/OR/NOT; plus BETWEEN, IN, LIKE (% and _), IS NULL.
ORDER BY sorts (ASC/DESC); DISTINCT removes duplicate values.

★ Practical: query the student table

Using the SQL trainer above, write queries that:

Show all students from Delhi (WHERE city = 'Delhi').
List names of students scoring between 80 and 90 (BETWEEN 80 AND 90).
Show the distinct list of cities (SELECT DISTINCT city).
Find students whose name starts with 'A' (LIKE 'A%'), sorted by marks descending.

Ready for the chapter test?

Answer 5 questions. Score 60% or more to mark this chapter complete.

Start the test →

💡 Log in to save your progress and earn the certificate.

← Previous

Database Concepts

SQL Functions, Grouping & Joins