Basic SQL Commands

What Is SQL?

SQL (Structured Query Language) is the standard language used to communicate with relational databases. It allows you to create databases and tables, insert data, retrieve data, update existing records, and delete data.

In simple terms, SQL helps you store, manage, and interact with data in a database.

What Can You Do with SQL?

Using SQL, you can:

Advertisement
  • Create databases and tables.
  • Insert new records.
  • Retrieve data.
  • Update existing data.
  • Delete records.
  • Manage database permissions.
  • Control database transactions.

Why SQL Matters in Software Testing

SQL is an essential skill for software testers because it helps validate backend data.

For example, when testing an e-commerce application like Amazon, you can use SQL to verify that:

  • User details are stored correctly after registration.
  • Orders are saved successfully.
  • Product prices are calculated correctly.
  • Payment information is recorded accurately.

Backend validation helps identify data inconsistencies that may not be visible in the application's user interface.

Example

Suppose you have a Students table with the following columns:

  • ID
  • Name
  • MathsMarks

To retrieve students who scored more than 80 marks:

SELECT Name
FROM Students
WHERE MathsMarks > 80;

This query returns the names of students whose MathsMarks are greater than 80.

SQL Interview Answer

SQL stands for Structured Query Language. It is the standard language used to interact with relational databases. SQL allows us to create tables, insert data, retrieve records, update existing data, and delete records. As a software tester, I use SQL to validate backend data. For example, after submitting a registration form, I can verify that the user's information has been stored correctly using queries like SELECT * FROM Users WHERE email = 'testuser@gmail.com';. Similarly, I can validate order information using queries such as SELECT * FROM Orders WHERE user_id = 101;. This helps detect backend data mismatches early in the testing process.

Types of SQL Commands

SQL commands are divided into four major categories:

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

DDL (Data Definition Language)

DDL commands define or modify the structure of database objects such as tables.

Common DDL Commands

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE

Example

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(50)
);

DML (Data Manipulation Language)

DML commands manipulate the data stored inside tables.

Common DML Commands

  • INSERT
  • UPDATE
  • DELETE

Example

INSERT INTO Students (ID, Name)
VALUES (1, 'Rahul');

DCL (Data Control Language)

DCL commands manage database permissions.

Common DCL Commands

  • GRANT
  • REVOKE

Example

GRANT SELECT
ON Students
TO user1;

TCL (Transaction Control Language)

TCL commands manage database transactions.

Common TCL Commands

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

These commands determine whether database changes are saved or undone.

DDL vs DML vs DCL vs TCL

Command Type Purpose Common Commands Real-World Example
DDL Defines database structure CREATE, ALTER, DROP, TRUNCATE Building or modifying a shelf
DML Manipulates data INSERT, UPDATE, DELETE Adding or changing items on the shelf
DCL Controls permissions GRANT, REVOKE Giving someone access keys
TCL Manages transactions COMMIT, ROLLBACK, SAVEPOINT Saving or undoing your work

DELETE vs TRUNCATE vs DROP

Although all three remove data, they behave differently.

Feature DELETE TRUNCATE DROP
Removes Specific rows All rows Entire table
WHERE clause Yes No No
Can be rolled back Yes (within a transaction) Usually No No
Table structure remains Yes Yes No
Command Type DML DDL DDL

DELETE

Removes selected rows.

DELETE FROM Students
WHERE ID = 1;

TRUNCATE

Removes all rows while keeping the table structure.

TRUNCATE TABLE Students;

DROP

Removes the entire table along with its structure.

DROP TABLE Students;

Testing Tip

  • Use DELETE to remove specific test data because it is safer and can be rolled back (within a transaction).
  • Avoid using TRUNCATE or DROP in production unless absolutely necessary.

Primary Key

A Primary Key uniquely identifies every row in a table.

A Primary Key:

  • Cannot contain NULL values.
  • Cannot contain duplicate values.
  • Can consist of one or more columns.

Example

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(10)
);

Here, StudentID uniquely identifies each student.

Primary Key Characteristics

Feature Primary Key
Uniquely identifies rows Yes
NULL values allowed No
Duplicate values allowed No
Composite key supported Yes
Keys per table One

Testing Tip

As a QA engineer, you can verify a Primary Key constraint by attempting to insert duplicate values and confirming that the database rejects them.

Foreign Key

A Foreign Key is a column (or set of columns) that references the Primary Key of another table.

It establishes relationships between tables and maintains referential integrity.

A Foreign Key prevents inserting values that do not exist in the referenced Primary Key.

Composite Key

A Composite Key is a Primary Key made up of two or more columns.

It is used when a single column cannot uniquely identify a row.

Example:

  • StudentID
  • CourseID

Together, these columns uniquely identify a student's enrollment.

Unique Key

A Unique Key ensures that all values in a column are unique.

Unlike a Primary Key:

  • It allows NULL values (typically one NULL in most databases).
  • A table can have multiple Unique Keys.

Example

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(15) UNIQUE
);
 

Primary Key vs Unique Key

Feature Primary Key Unique Key
Enforces uniqueness Yes Yes
NULL values allowed No Yes (typically one)
Keys per table One Multiple
Default index Clustered Non-clustered

CHAR vs VARCHAR

Both are character data types, but they store data differently.

CHAR

  • Fixed-length data type.
  • Always stores exactly the specified number of characters.
  • Pads unused space with blanks.

Example:

CHAR(10)
Value: Ram
Stored as:
Ram_______

 

VARCHAR

  • Variable-length data type.
  • Stores only the required number of characters.
  • Saves storage space.

Example:

VARCHAR(10)
Value:
Ram

Only three characters are stored.

CHAR vs VARCHAR Comparison

Feature CHAR VARCHAR
Length Fixed Variable
Storage Always uses full length Uses only required space
Performance Slightly faster for fixed-length data Better for variable-length data
Best Use Country codes, status codes Names, addresses, descriptions

Constraints in SQL

Constraints are rules applied to table columns to maintain data integrity.

They prevent invalid data from being inserted into the database.

Common SQL Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK
  • DEFAULT

These constraints ensure data remains accurate, consistent, and reliable.

Default Sorting Order in SQL

When using the ORDER BY clause without specifying a sorting direction, SQL sorts data in ascending order (ASC) by default.

Example

SELECT *
FROM Employees
ORDER BY Salary;
 

This is equivalent to:

SELECT *
FROM Employees
ORDER BY Salary ASC;
 

To sort in descending order:

SELECT *
FROM Employees
ORDER BY Salary DESC;
 

FAQs

1. What Is SQL?

SQL (Structured Query Language) is the standard language used to communicate with relational databases. It is used to create, retrieve, update, and delete data stored in databases.

2. What Are DDL, DML, DCL, and TCL?

  • DDL (Data Definition Language): Defines database structures using commands such as CREATE, ALTER, DROP, and TRUNCATE.
  • DML (Data Manipulation Language): Manipulates data using commands such as INSERT, UPDATE, and DELETE.
  • DCL (Data Control Language): Manages user permissions using GRANT and REVOKE.
  • TCL (Transaction Control Language): Controls transactions using COMMIT, ROLLBACK, and SAVEPOINT.

3. What Is the Difference Between DELETE, TRUNCATE, and DROP?

  • DELETE removes selected rows and can be rolled back (within a transaction).
  • TRUNCATE removes all rows quickly while preserving the table structure.
  • DROP removes the entire table, including both its structure and data.

4. What Is the Difference Between a Primary Key and a Unique Key?

Both enforce uniqueness.

  • A Primary Key does not allow NULL values, and a table can have only one Primary Key.
  • A Unique Key allows one NULL value (in most databases), and a table can have multiple Unique Keys.

5. What Is a Foreign Key?

A Foreign Key is a column that references the Primary Key of another table. It creates relationships between tables and ensures referential integrity.

6. What Is the Difference Between CHAR and VARCHAR?

  • CHAR is a fixed-length data type that always uses the specified storage size.
  • VARCHAR is a variable-length data type that stores only the required number of characters, making it more storage-efficient.

7. What Is the Default Sorting Order in SQL?

The default sorting order is Ascending (ASC) when the ORDER BY clause is used without explicitly specifying a sorting direction.