MODULE 2 – SQL DML & QUERY BASICS (WEEK 2)
Premium Student Notes – Same Design Pattern
Topics Covered:
1. INSERT Statement
2. UPDATE Statement
3. DELETE Statement
4. SELECT Statement Fundamentals
5. WHERE Clause and Filtering Data
1) INSERT Statement
The INSERT statement is used to add new records (rows) into a table.
Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Example
CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
course VARCHAR(30)
);
INSERT INTO student (student_id, name, age, course)
VALUES (101, 'Ravi', 20, 'BSc');
Important Points
Values must match the order and datatype of columns.
You can insert one row or multiple rows.
If column names are omitted, all values must be provided in correct order.
2) UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
Example
UPDATE student
SET course = 'BBA'
WHERE student_id = 101;
Important Points
Always use WHERE when updating specific rows.
Without WHERE, all rows will be updated.
You can update one or more columns at a time.
3) DELETE Statement
The DELETE statement is used to remove one or more rows from a table.
Syntax
DELETE FROM table_name
WHERE condition;
Example
DELETE FROM student
WHERE student_id = 101;
DELETE vs TRUNCATE
DELETE TRUNCATE
Deletes selected rows or all rows Deletes all rows only
Can use WHERE clause Cannot use WHERE clause
DML command DDL command
Slower for large tables Faster for removing all rows
4) SELECT Statement Fundamentals
The SELECT statement is used to retrieve data from one or more tables.
Common Syntax
SELECT * FROM student;
SELECT name, course FROM student;
SELECT student_id, name FROM student;
Common SELECT Uses
SELECT * returns all columns.
SELECT column1, column2 returns only required columns.
Can be combined with WHERE, ORDER BY, GROUP BY and aggregate functions.
Simple Query Flow Diagram
Write SELECT Query
Database Reads Table
Filters Requested Columns/Rows
Processes Query
Returns Result Set
5) WHERE Clause and Filtering Data
The WHERE clause is used to filter records based on a condition.
Syntax
SELECT * FROM table_name
WHERE condition;
Examples
SELECT * FROM student
WHERE age > 18;
SELECT name, course FROM student
WHERE course = 'BSc';
Common Comparison Operators
Operator Meaning
= Equal to
!= Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
6) Full Practice Example
CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
course VARCHAR(30)
);
INSERT INTO student VALUES (101, 'Ravi', 20, 'BSc');
INSERT INTO student VALUES (102, 'Asha', 21, 'BBA');
INSERT INTO student VALUES (103, 'Mohan', 19, 'BSc');
UPDATE student
SET course = 'BCom'
WHERE student_id = 102;
DELETE FROM student
WHERE student_id = 103;
SELECT * FROM student;
SELECT name, course FROM student;
SELECT * FROM student WHERE age > 20;
7) Table Snapshot Example
student_id (PK) name age course
101 Ravi 20 BSc
102 Asha 21 BCom
103 Mohan 19 BSc
8) Important Viva / Interview Questions
What is the purpose of INSERT statement?
What is the difference between UPDATE and DELETE?
Why is WHERE clause important in UPDATE and DELETE?
What does SELECT * mean?
What is the purpose of WHERE clause?
9) Student Assignment
Create a table employees(emp_id, emp_name, salary, dept).
Insert 5 employee records.
Update the salary of one employee.
Delete one employee record using emp_id.
Display all employee records using SELECT.
Display only employee name and salary.
Display employees whose salary is greater than 30000.