SQL Basic Commands: Complete Beginner Guide (DDL & DML Explained)
Master fundamental Structured Query Language (SQL) statements with real-world examples, dual-language explanations (English + Devanagari Hindi), side-by-side comparative tables, and practical exercises.
1. Overview of SQL Commands
SQL (Structured Query Language) relies on standardized commands to create, manipulate, and query relational database management systems (RDBMS) such as MySQL, PostgreSQL, SQL Server, and Oracle. These commands are split into distinct functional groups:
- DDL (Data Definition Language): Alters database structure and schemas (CREATE, DROP, TRUNCATE).
- DML (Data Manipulation Language): Modifies actual data records within tables (INSERT, UPDATE, DELETE).
- DQL (Data Query Language): Retrieves stored records (SELECT).
| Category | Command | Primary Purpose | Auto Commit |
|---|---|---|---|
| DDL | CREATE | Builds new database containers or table structures. | Yes |
| DDL | DROP | Deletes an entire table or database permanently. | Yes |
| DDL | TRUNCATE | Removes all records instantly, keeping structure. | Yes |
| DML | INSERT | Adds new rows of data into a table. | No |
| DML | UPDATE | Modifies existing row data based on conditions. | No |
| DML | DELETE | Removes specific rows using criteria. | No |
| DQL | SELECT | Fetches data records for viewing. | No |
2. SQL CREATE DATABASE Command
Definition: The CREATE DATABASE command initializes a brand-new, empty storage container inside your Database Management System (DBMS).
Purpose: Before creating tables, storing records, or performing queries, you must instantiate a distinct logical space to organize your tables.
Syntax
CREATE DATABASE database_name;Code Example
-- Create a database named school_db
CREATE DATABASE school_db;Line-by-Line Explanation:
Line 1: SQL comment denoting intent.
Line 2: CREATE DATABASE instructs the RDBMS engine to build a new database named school_db. The semicolon indicates statement completion.
CREATE DATABASE school_db; เคฒिเคเคจे เคธे 'school_db' เคจाเคฎ เคा เคเค เคจเคฏा เคกेเคाเคฌेเคธ เคธ्เคชेเคธ เคคैเคฏाเคฐ เคนो เคाเคคा เคนै।
Database names must be unique within an RDBMS instance. Always avoid reserved keywords like TABLE, USER, or SYSTEM for database naming.
Attempting to create tables without selecting the newly created database context using the USE database_name; query first.
3. SQL CREATE TABLE Command
Definition: The CREATE TABLE command defines a new structured table inside an active database, specifying column names, data types, and structural constraints.
Purpose: Tables act as organized grids (rows and columns) that hold your domain data.
Syntax
CREATE TABLE table_name (
column1 data_type constraint,
column2 data_type constraint,
...
);Code Example
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
age INT,
admission_date DATE
);Line-by-Line Explanation:
CREATE TABLE students: Commands the system to create a table labeled "students".student_id INT PRIMARY KEY: Defines an integer column that uniquely identifies every student row.first_name VARCHAR(50) NOT NULL: Allocates a text column (up to 50 characters) that cannot be left blank.age INT: Holds numerical values for student ages.admission_date DATE: Captures the date the student joined.
Always define a PRIMARY KEY on every table to guarantee record uniqueness and boost query speeds.
4. SQL INSERT Command
Definition: The INSERT INTO command inserts one or more new data records (rows) into an existing table.
Purpose: Populates structured tables with operational data.
Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);Code Example
-- Insert single row
INSERT INTO students (student_id, first_name, age, admission_date)
VALUES (101, 'Aarav', 15, '2026-01-15');
-- Insert multiple rows
INSERT INTO students (student_id, first_name, age, admission_date)
VALUES
(102, 'Ananya', 16, '2026-01-16'),
(103, 'Rohan', 15, '2026-01-17');Line-by-Line Explanation:
INSERT INTO students... maps data to specific column targets.
VALUES (...) assigns explicit literals matching data types (strings and dates must be enclosed in single quotes).
Mismatched column-to-value positions. Providing 4 column names but supplying 3 values causes an explicit SQL syntax runtime exception.
5. SQL SELECT Command
Definition: The SELECT command fetches and returns datasets from database tables.
Purpose: Serves as the backbone of data querying, reporting, and analysis.
Syntax & Code Examples
-- 1. Fetch all columns and rows
SELECT * FROM students;
-- 2. Fetch specific columns
SELECT first_name, age FROM students;
-- 3. Using Column Aliases
SELECT first_name AS "Student Name", age AS "Current Age" FROM students;Line-by-Line Explanation:
SELECT *: The asterisk wildcard selects every column defined in the target table.first_name, age: Explicitly selects designated columns, lowering memory overhead.AS "Student Name": Assigns a temporary display label (Alias) for presentation output without altering underlying schema names.
Avoid SELECT * in production environments. Explicitly requesting named columns optimizes query bandwidth and application execution speeds.
6. SQL UPDATE Command
Definition: The UPDATE command modifies existing field records within an established table.
Purpose: Keeps stored records accurate when real-world states change (e.g., updating user address, phone numbers, or test scores).
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;Code Examples
-- Single column update
UPDATE students
SET age = 16
WHERE student_id = 101;
-- Multiple column update
UPDATE students
SET age = 17, first_name = 'Aarav Sharma'
WHERE student_id = 101;Line-by-Line Explanation:
UPDATE students specifies the operational target table.
SET age = 16 replaces the old age value with 16.
WHERE student_id = 101 restricts update operations purely to matching rows.
Omitting the WHERE clause in an UPDATE statement applies updates to **EVERY ROW** across the target table permanently.
7. SQL DELETE Command
Definition: The DELETE statement removes specific row records from a table using defined logical evaluation criteria.
Syntax
DELETE FROM table_name WHERE condition;Code Example
-- Delete specific row
DELETE FROM students
WHERE student_id = 103;
-- Delete all rows conditionally
DELETE FROM students
WHERE age < 10;Line-by-Line Explanation:
DELETE FROM students flags table for record purging.
WHERE student_id = 103 isolates precise record boundaries to prevent collateral data loss.
8. SQL DROP Command
Definition: The DROP statement removes an entire structural entity (database or table) along with all underlying metadata, rows, index links, and integrity constraints permanently.
Syntax
DROP TABLE table_name;
DROP DATABASE database_name;Code Example
-- Completely eradicate table and schema
DROP TABLE students;Executing DROP TABLE cannot be rolled back in standard autocommit modes. Structural and row records are irrecoverable.
9. SQL TRUNCATE Command
Definition: The TRUNCATE TABLE command removes all rows from an existing table instantly while preserving the master structural schema (columns, indexes, and constraints).
Syntax
TRUNCATE TABLE table_name;Code Example
TRUNCATE TABLE students;10. Comparative Analysis Tables
Understanding structural vs content actions is essential for database administration and development.
DELETE vs TRUNCATE vs DROP
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Command Type | DML | DDL | DDL |
| What it removes | Specific or all rows | All rows instantly | Entire table & structure |
| WHERE Clause Support | Yes | No | No |
| Execution Speed | Slower (Logs row actions) | Fast (Deallocates pages) | Instantaneous |
| Rollback Capability | Possible (with transaction) | DBMS Dependent / Harder | No |
| Table Structure | Preserved | Preserved | Destroyed |
11. Real-Life Project: School Management System
Below is a complete SQL execution script demonstrating real-world query order: creation, population, querying, modification, and teardown.
-- Step 1: Initialize Database
CREATE DATABASE SchoolManagement;
USE SchoolManagement;
-- Step 2: Create Master Tables
CREATE TABLE Teachers (
teacher_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
subject VARCHAR(30)
);
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
grade INT,
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);
CREATE TABLE Marks (
mark_id INT PRIMARY KEY,
student_id INT,
subject VARCHAR(30),
score INT,
FOREIGN KEY (student_id) REFERENCES Students(student_id)
);
-- Step 3: Insert Records
INSERT INTO Teachers (teacher_id, name, subject) VALUES
(1, 'Dr. Sharma', 'Mathematics'),
(2, 'Mrs. Verma', 'Science');
INSERT INTO Students (student_id, name, grade, teacher_id) VALUES
(101, 'Amit Kumar', 10, 1),
(102, 'Priya Singh', 10, 2);
INSERT INTO Marks (mark_id, student_id, subject, score) VALUES
(1, 101, 'Mathematics', 95),
(2, 102, 'Science', 88);
-- Step 4: Query Data
SELECT name, grade FROM Students;
-- Step 5: Update Data
UPDATE Marks SET score = 98 WHERE mark_id = 1;
-- Step 6: Cleanup via Deletion
DELETE FROM Marks WHERE score < 40;
TRUNCATE TABLE Marks;
DROP TABLE Marks;12. Key Takeaways
- CREATE DATABASE / TABLE: Sets up your data structure container (DDL).
- INSERT INTO: Adds fresh records into a table (DML).
- SELECT: Queries and reads records from tables without modifying them (DQL).
- UPDATE: Modifies field data inside target rows using conditional checks.
- DELETE: Safely removes targeted data rows using a
WHEREcriteria. - TRUNCATE: Instantly clears table contents while leaving column definitions intact.
- DROP: Permanently removes a table, its records, and structural layout from storage.
13. Beginner SQL Interview Questions
Q1: What does DDL and DML stand for?
Answer: DDL stands for Data Definition Language (structure commands). DML stands for Data Manipulation Language (data modification commands).
Q2: Is SQL case-sensitive?
Answer: Standard SQL keywords are case-insensitive, but data inside rows and certain platform configurations can be case-sensitive.
Q3: What happens if you run UPDATE without a WHERE clause?
Answer: Every single row within the target table will be updated with the specified new value.
Q4: Why is PRIMARY KEY used in tables?
Answer: It ensures every row contains a unique identifier and prohibits NULL values.
Q5: Can TRUNCATE be used with a WHERE clause?
Answer: No, TRUNCATE operates on the entire table structure at once and does not accept conditional clauses.
Q6: What is the main syntax difference between DELETE and TRUNCATE?
Answer: DELETE FROM table_name WHERE condition; vs TRUNCATE TABLE table_name;.
Q7: Which statement returns records from a database?
Answer: The SELECT query statement.
Q8: What does the asterisk symbol (*) do in a SELECT query?
Answer: It selects and returns all available columns from the target table.
Q9: How do you change a column display header in output results?
Answer: By applying a column Alias using the AS keyword.
Q10: What is the default date format in SQL standard?
Answer: YYYY-MM-DD format (e.g., '2026-08-06').
Q11: Which command destroys a database completely?
Answer: The DROP DATABASE database_name; command.
Q12: Can you recover data deleted via DROP?
Answer: No, DROP commands bypass transaction rollback buffers in standard setups.
Q13: How do you insert multiple values in one SQL command?
Answer: Comma-separate value blocks: VALUES (val1), (val2), (val3);.
Q14: What constraint prevents blank column submissions?
Answer: The NOT NULL constraint.
Q15: What is RDBMS?
Answer: Relational Database Management System - database software built around relational table associations.
Q16: Which SQL clause filters records during queries?
Answer: The WHERE clause filter.
Q17: Is TRUNCATE faster than DELETE?
Answer: Yes, because TRUNCATE deallocates data pages instead of logging every individual row delete action.
Q18: What data type stores variable character text?
Answer: The VARCHAR data type.
Q19: Can a table have multiple PRIMARY KEYS?
Answer: No, a table can only have one primary key constraint, though it may consist of multiple combined columns (Composite Key).
Q20: What keyword sorts query results?
Answer: The ORDER BY clause (covered in upcoming lessons).
14. Practice Exercises
Exercise 1: Write an SQL statement to create a table named Books with columns: book_id (INT), title (VARCHAR), and price (INT).
Exercise 2: Write a query to insert a new book with ID 101, title 'SQL Mastery', and price 500.
Exercise 3: Formulate a query updating the price of 'SQL Mastery' to 450.
Exercise 4: Write a statement to delete all books priced lower than 200.
Exercise 5: Write a query to fetch only book titles from the Books table.
15. Frequently Asked Questions (FAQs)
Q1: What are the fundamental basic commands in SQL?
The basic SQL commands are CREATE, INSERT, SELECT, UPDATE, DELETE, TRUNCATE, and DROP.
Q2: What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes specific conditional rows; TRUNCATE clears all rows keeping schema structures; DROP deletes the table structure and contents completely.
Q3: How do I select specific columns from a table?
Use SELECT column1, column2 FROM table_name; instead of using the wildcard asterisk.
Q4: Why is the WHERE clause critical in UPDATE queries?
Without a WHERE clause, an UPDATE command overwrites every row across the database table.
Q5: Can I rollback a TRUNCATE command?
In most systems (like MySQL), TRUNCATE performs an implicit commit, making rollback impossible.
Q6: Which SQL command is used to add new rows?
The INSERT INTO command adds new data rows.
Q7: What is a Primary Key?
A field constraint ensuring every record row maintains a unique non-null identifier value.
Q8: What does VARCHAR stand for?
Variable Character - a dynamic length string storage data type.
Q9: How do I remove an entire database?
Execute the DROP DATABASE database_name; command.
Q10: How do I rename column headers in query results?
Use SQL column Aliases with the AS keyword: SELECT column AS "New Label".
Q11: What is DDL in SQL?
Data Definition Language - subset handling data structural elements like CREATE, ALTER, DROP, and TRUNCATE.
Q12: What is DML in SQL?
Data Manipulation Language - subset handling internal row values like INSERT, UPDATE, and DELETE.
Q13: Is SELECT a DML or DQL command?
While categorized under DML historically, SELECT is technically classified as Data Query Language (DQL).
Q14: How are string values formatted in SQL?
String values must be enclosed inside single quote marks: 'Sample Text'.
Q15: What is the best standard command for clearing large tables?
TRUNCATE is preferred over DELETE because it executes faster and releases storage pages efficiently.
No comments:
Post a Comment