SQL Basics Explained: Commands, Queries, and Examples

SQL (Structured Query Language) is the standard query language for databases. It is not just for querying data, but a complete language used to control, manage, and maintain a relational database system.

Why only RDBMS (Relational Database Management Systems)?

Because NoSQL systems such as MongoDB, CouchDB, and others use different storage structures like JSON, BSON, or document-like formats. They do not follow the relational model, so SQL is not used in the same way.

This post will explain basic SQL commands and queries with examples. It will help you get started with your SQL journey.

Types of SQL Commands

The different types of SQL commands are categorized based on their functions within the database system. These commands can define database structures such as tables and indexes, insert or update data, delete values from tables, retrieve information through queries, control user access, and manage transactions.

A list of SQL command categories is given below:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

Let’s discuss each of them in detail.

Data Definition Language (DDL commands)

Data Definition Language (DDL) consists of SQL commands used to create, modify, and delete database structures such as databases, tables, views, and indexes. All DDL commands are auto-committed, meaning the changes take effect immediately and permanently, and cannot be rolled back.

Common DDL commands are listed below.

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

CREATE Command

The CREATE command is used to create database objects like databases, tables, indexes, and views.

Examples of CREATE

1. To create a database.

CREATE DATABASE library;

2. Command to create a table.

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(150),
    publisher VARCHAR(150),
    published_year INT,
    price DECIMAL(10,2)
);

Note: If you are running SQL commands on MySQL , then make sure run to USE <database name>;

before any SQL command. Otherwise , it won’t work.

3. Command to create views.

A view is a virtual table based on SELECT query.

CREATE VIEW cheap_books AS
SELECT title, author, price
FROM books
WHERE price < 300;

4. Command to create a unique index.

A database index helps in faster searches.

CREATE UNIQUE INDEX idx_isbn
ON books(isbn);

ALTER Command

The ALTER command can modify the structure of an existing database object. It allows you to change, add and delete columns, constraints, and any other properties without deleting the table or its data.

The ALTER command is mostly used for:

• Add a new column
• Modify the data type of an existing column
• Rename a column
• Drop (remove) a column
• Add or remove constraints
• Rename the table

Examples of ALTER Commands

1. Add a New Column to Existing Table.

If you are using My SQL, make sure to run USE <database name>; before any other SQL commands, otherwise, it won’t work.

ALTER TABLE books 
ADD isbn VARCHAR(30);

2. Modify the data type of an existing column

ALTER TABLE books
MODIFY price DECIMAL(12,2);

The initial data type of the price attribute was DECIMAL (10, 2);. The new data type is DECIMAL(12, 2);.

3. Rename a Column

ALTER TABLE books RENAME COLUMN title TO book_title;

The RENAME takes two parameters:

• Old column name
• New column name

4. Drop a Column

ALTER TABLE books
DROP COLUMN published_year;

This will remove the column published_year from the table. Any data for the column is permanently deleted, including constraints, indexes, etc.

How to know if the column is deleted ? Use DESCRIBE books; or SHOW COLUMNS FROM books;

You should see all columns except published_year.

5. Add or Remove a Constraint

ALTER TABLE books
ADD CONSTRAINT unique_title_author UNIQUE (title, author);

The command above will add a UNIQUE constraint on the title and author columns. All combination of title and author are unique.

How do we know if the constraint was added?

The command SHOW INDEX FROM book; will return non_unique = 0 which means that all entries are unique.

6. Rename the table

ALTER TABLE books RENAME TO library_books;

DROP Command

The DROP command helps to delete database objects. You can delete databases itself using the DROP command. This command is auto-committed meaning the action cannot be undone, and deletion is permanent.

You can delete following using this command.

1. Database
2. Table
3. Views
4. Index
5. Columns via ALTER TABLE command
6. Constraints via ALTER TABLE command.

Examples of DROP Command

We can see example of first 4 objects from the above list. For examples of drop column and constraints, refer to the ALTER command examples.

Before running any command in My SQL , run following command, USE <database name>;.

1. Remove a Database

DROP DATABASE members;

2. Remove a Table

DROP DATABASE memberships;

3. Remove Views

DROP VIEW cs_books;

4. Remove index

DROP INDEX idx_isbn ON Library;

TRUNCATE Command

This command deletes all the rows from a table but keep the schema and structure of intact. It is more efficient than DROP command which delete the entire object.

The TRUNCATE command cannot be used for deleting specific roles.

Example of TRUNCATE command

If the books table is empty, insert the following records, otherwise, skip this step.

INSERT INTO books (book_id, title, author, price) VALUES (12, 'Ashtavakra Geeta', 'Ashtavakra', 120.43);
INSERT INTO books (book_id, title, author, price) VALUES (13, 'Sri Ramayanam', 'Maharshi Valmiki', 550.50);
INSERT INTO books (book_id, title, author, price) VALUES (14, 'Sri Mahabharatam', 'Maharshi Vedvyas', 1000.35);

Now run the truncate command.

TRUNCATE books;

To know the effect of TRUNCATE command, run the following SQL command. This command will try to retrieve all the records from book table.

SELECT * FROM books;

If you only see a structure of table in the results, but no records. The TRUNCATE has been successful. The TRUNCATE command will remove all records.

RENAME Command

The RENAME command will help us rename the TABLE. There are two ways to achieve this goal.

1. Rename the Table directly.
2. Use the ALTER TtBLE command.

Examples of RENAME Command

1. Direct renaming.

RENAME TABLE books TO library_books;

2. Using ALTER TABLE Command.

ALTER TABLE books RENAME TO library_books;

DML Commands

Data Manipulation Language (DML) commands are those commands that work with actual data records stored inside database tables. They are used to insert, modify, retrieve and delete the records.

Why are DML commands used:

1. Add a new record
2. Modify an existing record.
3. Retrieve and display existing records.
4. Delete records from one or more tables.

List of DML commands

There are many DML commands, the most common ones are listed below.

1. INSERT
2. UPDATE
3. SELECT
4. DELETE

Let’s discuss each of them briefly.

INSERT Command

This command simply inserts a new record into the table with the values you provide. The table name and the data values must be correct for insert to work.

Before you try this command in MySQL, do the following.

CREATE DATABASE university;
USE university;

To insert values into the University database, create a table for Students and insert values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    course VARCHAR(100)
);

Now, insert values using the INSERT command.

INSERT INTO students (student_id, first_name,last_name, age, email, course)
VALUES (1, 'Rakesh', 'Kumar', 20, '[email protected]', 'Physics');

INSERT INTO students (student_id, first_name,last_name, age, email, course)
VALUES (2, 'Karuna', 'Nair', 20, '[email protected]', 'Mathematics');

UPDATE Command

This command will update records in a table.

We have entered the wrong age for student ‘Karuna’, which we now want to change.

UPDATE students
SET age = 25
WHERE student_id = 2;

SELECT Command

This command helps query the database, retrieve results and displaying them. You can query the database based on conditions.

For example, to view the updated information of student “Karuna”, run the following command.

SELECT * FROM students WHERE student_id = 2;

The following command will retrieve records from entire table.

The asterisk (*) selects all columns from the table. The WHERE clause is used to filter rows based on conditions.

SELECT * FROM books;

DELETE Command

This command helps delete the records from a table in a database.

DELETE FROM students
WHERE id = 1;

Data Query Language (DQL)

The Data Query Language (DQL) is used to retrieve data from the database based on specific conditions. It does not modify or change table values. — It only reads data.

SELECT is the only command to run query. But DQL can do lot of things such as:

1. Retrieve data from one or more tables.
2. Filter and extract data based on conditions.
3. Sort data in ascending or descending order.
4. Group data.
5. Join multiple tables.

Examples of DQL Commands

Here are some examples of different tasks carried out using SELECT command.

1. Get all the records from a table.

SELECT * FROM students;

2. Filter and get results based on conditions.

SELECT first_name, age FROM students;

This time the query return only the first name and age column from the students table.

3. Sort the results.

You can sort the query results by using ‘ORDER BY’ clause.

Ascending order

SELECT * FROM students
ORDER BY age ASC;

Result:

Descending order

SELECT * FROM students
ORDER BY age DESC;

Result:

4. Group the results

GROUP BY is used to organize rows that share identical values in one or more columns. It is commonly used together with aggregate functions such as COUNT(), SUM(), or AVG() to summarize data.

Aggregate functions are take multiple of rows data and return a single summarized value. Use these functions to calculate total, averages, or maximum /minimum values.

1. SUM()
2. COUNT ()
3. AVG()
4. MAX()
5. MIN()

Grouping students on age and counting students for each group

SELECT age, COUNT(*) AS total_students
FROM student
GROUP BY age;

The SQL command does two things.

1. Group all students in groups where age is same.
2. Count the total number of students for each group.

Result:

5. JOIN two or more tables

The JOIN command joins two or more tables based on one or more shared shared columns. All types of joins use a shared column except

1. CROSS JOIN
2. SELF JOIN

What are different types of JOINs?

Here is a list of common joins.

1. INNER JOIN – When all records from both table are matching.
2. LEFT JOIN – All records of left table and matching records from right table.
3. RIGHT JOIN – All records of right table and matching records from left table.
4. FULL JOIN – All records from both tables.
5. CROSS JOIN – Return all combination of both tables ( A Cartesian Product).
6. SELF JOIN – A Join on itself ( A reflexive relationship).

JOIN Command Examples

We shall see examples of JOIN now.

INNER JOIN

SELECT 
    s.student_id,
    s.name,
    c.course_name
FROM students s
INNER JOIN courses c
    ON s.student_id = c.student_id;

Result:

Return records of all students who have joined a course.

LEFT JOIN

SELECT 
    s.student_id,
    s.name,
    c.course_name
FROM student s
LEFT JOIN course c
    ON s.student_id = c.student_id;

Result:

The LEFT JOIN returns all entries from left table (student) and those entries from course table where student.student_id matches course.student_id.

All non-matching entries will have a Null value.

RIGHT JOIN

SELECT 
    s.student_id,
    s.name,
    c.course_name
FROM student s
RIGHT JOIN course c
    ON s.student_id = c.student_id;

Result:

The RIGHT JOIN returns all entries from right-hand table(course) and matching records from student table. Non-matching entries will have a Null value.

Note that there is no Null in the output, because there are no mismatched records. Every course has a student.

FULL JOIN

SELECT 
    s.student_id,
    s.name,
    c.course_name
FROM 
    student AS s
LEFT JOIN 
    course AS c
ON s.student_id = c.student_id

UNION

SELECT 
    s.student_id,
    s.name,
    c.course_name
FROM 
    student AS s
RIGHT JOIN 
    course AS c
ON s.student_id = c.student_id;

MySQL do not support FULL JOIN, therefore, you can use UNION command to include results from LEFT JOIN and a RIGHT JOIN.

The FULL JOIN returns all rows from both tables – student and course. If records match, it shows the matching data, if not, the missing values are filled with Null.

Result:

The result shows all matching records from student and course table. It also includes all records that do not match and fill the empty column with Null.

The student ‘Kelly’ did not join any course and the course ‘ENGLISH’ has no students.

CROSS JOIN (Cartesian Product)

SELECT 
    student.student_name,
    course.course_name
FROM 
    student
CROSS JOIN course;

Result:

The CROSS JOIN produces a Cartesian product of two tables. Every row of first table is paired with every row of second table.

Data Control Language (DCL)

The Data Control Language (DCL) in SQL consists of commands that manage access control, permissions, and data security. It decides who can do what.

It has three main tasks:

1. Grant permission to users.
2. Revoke the permission given to users.
3. Secure the data and make sure authorized access.

DCL Commands

There are only two commands. Different version of SQL uses slightly different syntex, but they mean the same thing.

1. GRANT
2. REVOKE

For example, MySQL doesn’t use DENY, but SQL Server does. MySQL uses REVOKE statements.

Examples of DCL Commands

Grant permission to SELECT, INSERT to the user ‘Kiran’ using GRANT command.

GRANT SELECT, INSERT ON students TO 'kiran'@'localhost';

Revoke all permissions for user ‘Kiran’ using REVOKE command.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'kiran'@'localhost';

Transaction Control Language (TCL)

A transaction is a group of SQL commands executed as one unit of work. Either all of the operations succeed or fall fails.

Transaction Control Language (TCL) controls these operations which are nothing but several DML statements such as INSERT, UPDATE, and DELETE.

TCL Commands

The Main TCL Commands are:

1. COMMIT
2. ROLLBACK
3. SAVEPOINT
4. SET TRANSACTION

Examples of TCL Commands

COMMIT

When transaction is successful. The Commit command save the changes to the database permanently.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

In the example above, both succeed or both fails.

Learn more about Database Transaction States.

Want to get in-depth knowledge on Transaction Processing?

Visit Transaction Processing in DBMS: Concept, Properties and Examples.

ROLLBACK

When the transaction fails, database can become inconsistent and all changes are ROLLBACK to bring the database into a consistent state.

START TRANSACTION;
DELETE FROM student WHERE student_id = 4;
ROLLBACK;  -- Undo the delete

SAVEPOINT

The SAVEPOINT creates a checkpoint, so that we can partially rollback to it, if necessary.

START TRANSACTION;
UPDATE products SET price = price + 10;
SAVEPOINT p1;
UPDATE products SET price = price + 5;
ROLLBACK TO p1;   -- Undo only the second update
COMMIT;

You can understand the example, by knowing what each step does.

1. Transaction starts.
2. product price is set to price + 10;
3. We created a SAVEPOINT, if anything goes wrong, go back to SAVEPOINT.
4. The product price is incremented by 5. This is an unwanted activity.
5. Rollback to the nearest correct price and state.
6. Commit the changes if all operations are completed successfully.

SET TRANSACTION

The SET TRANSACTION define the properties of a transaction. A set transaction changes following properties:

1. Isolation Level – Isolation is one of the ACID properties that controls how a transaction must behave during concurrent access. Therefore, isolation level decides how much the current transaction must “see” from other transactions, running simultaneously.
2. Read/Write Mode – It defines whether transaction can (read and write) or (read only).
3. Scope – This property decides whether settings are applied to Next transaction or Current Transaction only.

SET TRANSACTION READ ONLY;

START TRANSACTION;

SELECT * FROM products;  -- Allowed
UPDATE products SET price = 100;  -- Not allowed

COMMIT;

Summary of SQL Basic