Basics of SQL: Important Commands, Codes and Examples

1. What Is a Database, a Table and a Record?

• Database: an organised collection of related data.
• Table: data arranged in rows and columns.
• Row / record: one complete entry, such as one student's details.
• Column / field: one type of information, such as a student's name or marks.
• Query: an instruction written in SQL to work with data.

2. SQL Command Categories

These categories are common teaching conventions. Exact command support and permission syntax depend on the database system.

3. Create a Database and Select It

Use CREATE DATABASE to create a database. In MySQL, you can then select it with USE. PostgreSQL and SQLite handle database selection differently, so the second line is not universal.

CREATE DATABASE school_db;
USE school_db;

4. CREATE TABLE: Build a Table

Let us create a Students table. An integer ID identifies each student, the name stores text, class stores a class number, and marks stores a numeric score.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    class INT,
    marks DECIMAL(5,2)
);

PRIMARY KEY uniquely identifies each row. NOT NULL means a value must be supplied for that column. DECIMAL(5,2) can store up to five digits in total, including two digits after the decimal point.

5. INSERT: Add Records

Use INSERT INTO to add data. Listing the column names makes the statement clearer and safer if the table structure changes.

INSERT INTO Students (student_id, name, class, marks)
VALUES
    (1, 'Aarav', 10, 88.00),
    (2, 'Meera', 10, 94.00),
    (3, 'Kabir', 9, 76.50),
    (4, 'Sana', 10, 81.00),
    (5, 'Ishita', 9, 89.50);

Text values are usually enclosed in single quotes. Numeric values do not need quotes. These sample records are fictional.

6. SELECT: Retrieve Data

SELECT * FROM Students;

SELECT name, marks
FROM Students;

SELECT DISTINCT class
FROM Students;

7. WHERE: Filter Rows

The WHERE clause selects only rows that meet a condition.

-- Students scoring more than 85
SELECT name, marks
FROM Students
WHERE marks > 85;

-- Students in Class 10
SELECT name, class
FROM Students
WHERE class = 10;

-- Marks between 80 and 90, inclusive
SELECT name, marks
FROM Students
WHERE marks BETWEEN 80 AND 90;

-- Names matching either of two choices
SELECT name
FROM Students
WHERE name IN ('Aarav', 'Sana');

In a LIKE pattern, % represents zero or more characters and _ represents exactly one character. Use IS NULL, not = NULL, to test for a missing value.

8. ORDER BY and LIMIT: Sort and Restrict Results

-- Highest marks first
SELECT name, marks
FROM Students
ORDER BY marks DESC;

-- Alphabetical order
SELECT name
FROM Students
ORDER BY name ASC;

-- Top three records by marks (MySQL, PostgreSQL, SQLite)
SELECT name, marks
FROM Students
ORDER BY marks DESC
LIMIT 3;

ASC means ascending order and is the default. DESC means descending order. LIMIT is supported by MySQL, PostgreSQL and SQLite; SQL Server and some other systems use different syntax, such as TOP or FETCH FIRST.

9. Aggregate Functions: Calculate Summary Values

SELECT
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks,
    MAX(marks) AS highest_marks,
    MIN(marks) AS lowest_marks,
    SUM(marks) AS total_marks
FROM Students;

AS gives a column or expression a readable alias. Aggregate functions generally ignore NULL values, except COUNT(*), which counts rows.

10. GROUP BY and HAVING

GROUP BY makes groups of rows with the same value. HAVING filters the groups after aggregate calculations.

-- Average marks for each class
SELECT class, AVG(marks) AS average_marks
FROM Students
GROUP BY class;

-- Classes whose average marks are at least 85
SELECT class, AVG(marks) AS average_marks
FROM Students
GROUP BY class
HAVING AVG(marks) >= 85;

Use WHERE to filter individual rows before grouping and HAVING to filter groups after aggregation.

11. UPDATE: Change Existing Data

UPDATE Students
SET marks = 90
WHERE student_id = 1;

This changes Aarav's marks to 90 because the row with ID 1 is selected. Always check the WHERE condition before running an update.

12. DELETE: Remove Records

DELETE FROM Students
WHERE student_id = 5;

This removes the row whose ID is 5. Deleting a row is different from deleting a table structure.

13. ALTER, TRUNCATE and DROP

ALTER TABLE Students
ADD email VARCHAR(100);

TRUNCATE TABLE Students;

DROP TABLE Students;

14. Constraints: Rules for Reliable Data

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE Enrolments (
    student_id INT,
    course_id INT,
    enrolment_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

This illustrates a composite primary key for enrolments and a foreign key to courses. The exact handling of default dates and constraint enforcement may vary by database.

15. JOIN: Combine Data from Tables

Suppose we have a second table named Courses. A join lets us display related information from both tables.

CREATE TABLE StudentCourses (
    student_id INT,
    course_name VARCHAR(60)
);

INSERT INTO StudentCourses (student_id, course_name)
VALUES
    (1, 'Mathematics'),
    (2, 'Computer Science'),
    (3, 'Science');

SELECT s.name, sc.course_name
FROM Students AS s
INNER JOIN StudentCourses AS sc
    ON s.student_id = sc.student_id;

SELECT s.name, sc.course_name
FROM Students AS s
LEFT JOIN StudentCourses AS sc
    ON s.student_id = sc.student_id;

Other join types: RIGHT JOIN returns all rows from the right table and matches from the left where supported. FULL OUTER JOIN returns matched rows plus unmatched rows from both sides, but it is not supported directly by every database (including MySQL).

16. Subqueries: A Query Inside Another Query

A subquery is a query used inside another SQL statement. For example, to find students who scored above the overall average:

SELECT name, marks
FROM Students
WHERE marks > (
    SELECT AVG(marks)
    FROM Students
);

The inner query calculates the average; the outer query selects students whose marks are greater than that value.

17. CASE: Add Conditional Logic

SELECT
    name,
    marks,
    CASE
        WHEN marks >= 90 THEN 'Excellent'
        WHEN marks >= 75 THEN 'Good'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Needs improvement'
    END AS result_category
FROM Students;

CASE evaluates conditions in order and returns the result for the first matching condition. The grade labels here are only an example, not official grading criteria.

18. SQL Functions and Useful Expressions

-- Combine text (syntax varies by database)
SELECT UPPER(name) AS uppercase_name
FROM Students;

-- Round a calculation
SELECT ROUND(AVG(marks), 2) AS average_marks
FROM Students;

-- Work with a date (current date syntax varies)
SELECT CURRENT_DATE;

-- Check for missing marks
SELECT name
FROM Students
WHERE marks IS NULL;

-- Replace a missing value in query output
SELECT name, COALESCE(marks, 0) AS displayed_marks
FROM Students;

Common functions include UPPER, LOWER, LENGTH or CHAR_LENGTH, ROUND, ABS, COALESCE, date functions and string concatenation. Function names and syntax can differ across database systems.

19. Transactions: COMMIT and ROLLBACK

A transaction groups database operations so that they can be committed together or rolled back when supported by the database and storage engine.

BEGIN;

UPDATE Students
SET marks = 91
WHERE student_id = 1;

-- Keep the change:
COMMIT;

-- If you have not committed and need to cancel:
-- ROLLBACK;

Run either COMMIT or ROLLBACK as appropriate before the transaction ends. Transaction start syntax and behaviour differ between systems; some commands such as certain DDL statements may commit automatically.

20. SQL Comments and Formatting

-- This is a single-line comment

/*
This is a
multi-line comment
*/

SELECT name, marks
FROM Students
WHERE marks >= 80
ORDER BY marks DESC;

Use comments to explain why a query is written. Indenting clauses and writing keywords in uppercase can make SQL easier to read, although SQL keywords are generally case-insensitive.

21. SQL Injection and Safe Querying

Applications should not build SQL by directly joining user input into a query string. That can allow SQL injection, where untrusted input changes the meaning of a query.

Prefer parameterised queries provided by your programming language's database library. The placeholder syntax depends on the library.

22. Complete Mini Practice: Find Top Students

Using the Students table created earlier, this query lists students in descending order of marks and displays the top three:

SELECT student_id, name, class, marks
FROM Students
WHERE marks IS NOT NULL
ORDER BY marks DESC
LIMIT 3;

To show the average marks for each class and sort the class with the highest average first:

SELECT class, ROUND(AVG(marks), 2) AS average_marks
FROM Students
GROUP BY class
ORDER BY average_marks DESC;

23. Quick SQL Cheat Sheet

Practice Questions

1. Write a query to display the names of all students in Class 10.
2. Write a query to list students whose marks are at least 85, from highest to lowest.
3. Find the average marks of all students.
4. Count the number of students in each class.
5. Insert a new student record with ID 6, name 'Rohan', Class 10 and marks 84.
6. Update the marks of the student with ID 3 to 80.
7. Explain the difference between WHERE and HAVING.
8. Explain the difference between DELETE, TRUNCATE and DROP.
9. Write a query using an INNER JOIN to display student names and their courses.
10. Why should a database application use parameterised queries?

Frequently Asked Questions

What is SQL used for?

SQL is used to define relational database structures and insert, retrieve, update, delete and summarise data. It is used in websites, business software, reporting systems and many other applications.

Is SQL a programming language?

SQL is a specialised declarative language for working with relational data. You describe the data you want or the operation you need, and the database engine determines how to carry it out.

Are SQL and MySQL the same?

No. SQL is the language; MySQL is a relational database management system that uses SQL. PostgreSQL, SQL Server and SQLite are other database systems with their own syntax differences.

Which SQL commands should beginners learn first?

Start with SELECT, FROM, WHERE, ORDER BY, INSERT, UPDATE, DELETE, CREATE TABLE, aggregate functions, GROUP BY and JOIN.

Can these examples run in every database?

The core ideas are widely used, but exact syntax varies. In particular, database selection, limiting results, date functions, altering columns, transactions and some join types may need adjustment for your database.