SQL Basics in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Learn SQL basics in DBMS with beginner-friendly explanation of commands, tables, SELECT, WHERE, INSERT, UPDATE, DELETE, constraints, and result examples.
SQL stands for Structured Query Language. It is the language used to communicate with a relational database. When we want to create a table, insert data, read data, update records, or delete records, we commonly write SQL commands. A DBMS manages the database internally, but SQL gives the user a clear way to ask for work. For example, a student record may be stored inside a table, and SQL can be used to show all students, find one student by roll number, or update marks after an exam.
SQL is important for students because it connects theory with real database usage. Concepts such as tables, keys, constraints, and relationships become much clearer when we write SQL commands for them.
Why SQL Is Needed
A database can contain a large amount of data. Without a query language, it would be difficult to search, filter, sort, and modify that data in a controlled way. SQL gives a standard style for database operations. Different database systems such as MySQL, PostgreSQL, Oracle, SQL Server, and SQLite may have small syntax differences, but the basic SQL ideas remain almost the same.
In placement interviews, SQL is commonly asked because it checks whether a student can work with real data. Even if a student is learning backend development, data analytics, or testing, SQL basics are useful.
Table Used in Examples
We will use a simple Students table to understand SQL commands.
```text
Students
+---------+--------+------------+-------+
| roll_no | name | department | marks |
+---------+--------+------------+-------+
| 101 | Rahul | CSE | 85 |
| 102 | Priya | IT | 92 |
| 103 | Aman | CSE | 76 |
+---------+--------+------------+-------+
```
Here, each row represents one student record. Each column stores one type of information such as roll number, name, department, or marks.
Types of SQL Commands
SQL commands are commonly divided into categories. Numbering these categories makes them easier to remember.
1. DDL: Data Definition Language commands define or change database structure.
2. DML: Data Manipulation Language commands insert, update, or delete data.
3. DQL: Data Query Language commands read data from tables.
4. DCL: Data Control Language commands control permissions.
5. TCL: Transaction Control Language commands manage transactions.
Each category has a different purpose. A beginner should first become comfortable with DDL, DML, and DQL because these are used most often in basic database work.
SQL Command Categories
This table gives a quick overview of the main SQL command groups.
| Category | Full Form | Common Commands | Purpose |
| --- | --- | --- | --- |
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE | Defines database structure |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE | Changes data inside tables |
| DQL | Data Query Language | SELECT | Reads data from tables |
| DCL | Data Control Language | GRANT, REVOKE | Controls access permissions |
| TCL | Transaction Control Language | COMMIT, ROLLBACK, SAVEPOINT | Manages transaction changes |
Do not try to memorize only the full forms. Understand the action of each category. DDL works on structure, DML works on data, DQL reads data, DCL manages access, and TCL manages saved changes.
Creating a Table
The `CREATE TABLE` command is used to create a new table. A table definition includes column names, data types, and constraints.
```sql
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(20),
marks INT
);
```
This command creates a table named Students. The `roll_no` column stores integer values and is selected as the primary key. The `name` and `department` columns store text, and `marks` stores an integer value. A primary key is useful here because every student should be identified uniquely. If two rows had the same roll number, it would become difficult to know which student record is correct.
Inserting Data
The `INSERT` command adds new rows to a table. The column names tell the DBMS where each value should go.
```sql
INSERT INTO Students (roll_no, name, department, marks)
VALUES (101, 'Rahul', 'CSE', 85);
```
After this command, one new student record is stored in the Students table. Text values are written inside single quotes, while numeric values are usually written without quotes. When inserting data, the value order should match the column order. Writing column names clearly is a good habit because it reduces mistakes.
Reading Data Using SELECT
The `SELECT` command is used to read data from a table. It is one of the most frequently used SQL commands.
```sql
SELECT * FROM Students;
```
The star `*` means all columns. This command shows all rows and all columns from the Students table. The result will look like this for our sample data.
```text
+---------+--------+------------+-------+
| roll_no | name | department | marks |
+---------+--------+------------+-------+
| 101 | Rahul | CSE | 85 |
| 102 | Priya | IT | 92 |
| 103 | Aman | CSE | 76 |
+---------+--------+------------+-------+
```
If only selected columns are needed, write those column names instead of `*`.
```sql
SELECT name, marks FROM Students;
```
This query displays only the name and marks columns. Selecting only required columns makes output cleaner and can improve performance on large tables.
Filtering Data Using WHERE
The `WHERE` clause is used to filter rows based on a condition. It helps us read only the records that match a requirement.
```sql
SELECT * FROM Students
WHERE department = 'CSE';
```
This query shows only students whose department is CSE.
```text
+---------+--------+------------+-------+
| roll_no | name | department | marks |
+---------+--------+------------+-------+
| 101 | Rahul | CSE | 85 |
| 103 | Aman | CSE | 76 |
+---------+--------+------------+-------+
```
The `WHERE` clause is important because most real queries do not need every row. We usually search for a specific user, a selected category, a date range, or records that satisfy a condition.
Comparison Operators
SQL conditions can use comparison operators. These operators are similar to programming language operators.
1. `=` checks equality.
2. `<>` or `!=` checks not equal.
3. `>` checks greater than.
4. `<` checks less than.
5. `>=` checks greater than or equal to.
6. `<=` checks less than or equal to.
For example, this query shows students who scored at least 80 marks.
```sql
SELECT name, marks FROM Students
WHERE marks >= 80;
```
The result contains Rahul and Priya because both have marks greater than or equal to 80.
Logical Operators
Logical operators combine multiple conditions. They are useful when one condition is not enough.
1. `AND` returns rows where all conditions are true.
2. `OR` returns rows where at least one condition is true.
3. `NOT` reverses a condition.
```sql
SELECT * FROM Students
WHERE department = 'CSE' AND marks >= 80;
```
This query returns students who are in CSE and also have marks at least 80. In our sample data, Rahul matches both conditions.
Sorting Data Using ORDER BY
The `ORDER BY` clause arranges query results in ascending or descending order.
```sql
SELECT name, marks FROM Students
ORDER BY marks DESC;
```
`DESC` means descending order. This query shows students from highest marks to lowest marks. If we use `ASC`, or write nothing, SQL sorts in ascending order by default. Sorting is useful in reports, ranking systems, result lists, leaderboards, and admin dashboards where users expect data in a meaningful order.
Updating Data
The `UPDATE` command changes existing records. It is usually used with `WHERE` so that only the required row is updated.
```sql
UPDATE Students
SET marks = 88
WHERE roll_no = 101;
```
This command changes Rahul's marks from 85 to 88 because Rahul has roll number 101. The `WHERE` clause is very important in update queries. Without `WHERE`, the DBMS may update every row in the table, which can damage the data.
Deleting Data
The `DELETE` command removes rows from a table. Like `UPDATE`, it should usually be written with a `WHERE` condition.
```sql
DELETE FROM Students
WHERE roll_no = 103;
```
This command removes the record of the student whose roll number is 103. A beginner should be careful with delete queries. If `WHERE` is missing, all rows in the table may be deleted.
Constraints in SQL
Constraints are rules applied to table columns. They protect data quality and prevent invalid records.
1. `PRIMARY KEY` uniquely identifies each row.
2. `FOREIGN KEY` connects one table to another table.
3. `NOT NULL` prevents empty values in a column.
4. `UNIQUE` prevents duplicate values.
5. `CHECK` allows only values that satisfy a condition.
6. `DEFAULT` provides a value when no value is given.
For example, marks should not be negative. A `CHECK` constraint can help prevent such invalid data.
```sql
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
marks INT CHECK (marks >= 0)
);
```
This table definition says that roll number must be unique, name cannot be empty, and marks cannot be negative.
Aggregate Functions
Aggregate functions calculate one result from many rows. They are useful for reports and summaries.
1. `COUNT()` counts rows.
2. `SUM()` adds values.
3. `AVG()` calculates average.
4. `MAX()` finds the highest value.
5. `MIN()` finds the lowest value.
```sql
SELECT AVG(marks) AS average_marks
FROM Students;
```
This query calculates the average marks of all students. The alias `average_marks` gives a readable name to the output column.
GROUP BY
`GROUP BY` groups rows that have the same value in a column. It is often used with aggregate functions.
```sql
SELECT department, COUNT(*) AS total_students
FROM Students
GROUP BY department;
```
This query counts how many students are present in each department.
```text
+------------+----------------+
| department | total_students |
+------------+----------------+
| CSE | 2 |
| IT | 1 |
+------------+----------------+
```
Grouping is useful when we need summaries such as total orders per customer, average marks per class, or number of employees per department.
HAVING Clause
`HAVING` is used to filter grouped results. `WHERE` filters rows before grouping, while `HAVING` filters groups after grouping.
```sql
SELECT department, COUNT(*) AS total_students
FROM Students
GROUP BY department
HAVING COUNT(*) > 1;
```
This query shows only departments having more than one student. In the sample data, CSE is shown because it has two students.
Important Points to Remember
SQL is used to create, read, update, and delete database records. The most common beginner commands are `CREATE TABLE`, `INSERT`, `SELECT`, `UPDATE`, and `DELETE`. Always use `WHERE` carefully with `UPDATE` and `DELETE`. A missing condition can change or remove many records by mistake.
For interviews, students should understand command categories, constraints, filtering, sorting, aggregate functions, grouping, and the difference between `WHERE` and `HAVING`.