CRUD Operations in SQL – Explained with Code Examples

Structured Query Language (SQL) is the industry standard language for maintaining and modifying databases. CRUD operations are a fundamental part of SQL and database administration. Create, Read, Update, and Delete (CRUD), are the four fundamental actions that make up dealing with a database. The core base of any computer programming language or technology is CRUD operations. The four fundamental processes for generating and maintaining permanent data elements – primarily in relational and NoSQL databases – are denoted by CRUD operations.

In this article, we will examine each CRUD operation in SQL with thorough explanations, examples, and use cases.

What is SQL?

SQL (Structured Query Language) is a standardised computer language used in performing activities in Relational Database Management Systems. This gives users a very convenient way of retrieving and manipulating the database as they can search, edit, and select certain data among other things. It is efficient in processing relational data only. Databases such as MYSQL, PostgreSQL, Oracle database, SQL Server, SQLite, etc., all require SQL for their different operations in database management systems.

Characteristics of SQL

1. Data Querying: Data can be retrieved from a database by the user through the use of SQL SELECT which can also use filter criteria, then sort and combine various data elements.
2. Data Manipulation: SQL also enables the task of inserting records into tables using the INSERT statement. It enables the changing of records using the UPDATE statement that are already present, and it also enables the elimination of records/tables using the DELETE statement.
3. Data Definition: It also offers commands to create new objects CREATE statements, change objects ALTER statements, and delete objects that are deleted DROP statements upon completion such as tables, schemas, or indexes.
4. Data Control: DBA or data administrator may grant access to the database objects or take them away through the GRANT and REVOKE statements.

Types of SQL Commands

SQL commands are typically categorised into the following types:

1. DDL (Data Definition Language): Commands for defining and modifying the structure of the database. Examples include:

• CREATE: Creates a new table, database, or other objects.
• ALTER: Modifies existing database objects.
• DROP: Deletes tables or databases.

2. DML (Data Manipulation Language): Commands used to manipulate data stored in tables. Examples include:

• SELECT: Retrieves data from one or more tables.
• INSERT: Adds new rows to a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes rows from a table.

3. DCL (Data Control Language): Commands for managing access to the database. Examples include:

• GRANT: Provides specific privileges to users.
• REVOKE: Removes user privileges.

4. TCL (Transaction Control Language): Commands that control transactions, ensuring that operations are completed successfully.

Examples include:

• BEGIN TRANSACTION: Starts a new transaction.
• COMMIT: Saves the changes made in a transaction.
• ROLLBACK: Reverts the changes made in a transaction.

CRUD refers to Create, Read, Update, and Delete which are the pillars in relational database systems. CRUD operations are very important in the management of relational databases because it is these that underlie how records are retrieved, created, viewed, changed, or deleted. As a developer or a database engineer, while handling relational data or querying and processing big data, CRUD operations will facilitate the management of your data. CRUD stands for the following:

• CREATE: This means to create the data into the database.
• READ: This means to retrieve the data from the database.
• UPDATE: This means to update the data in the database.
• DELETE: This means to delete the data from the database.

Database applications make considerable use of CRUD operations. Relational database management systems (RDBMS) such as PostgreSQL, MySQL, and Oracle fall under this category. NoSQL databases like MongoDB, AWS DynamoDB, Cassandra, etc., are also included in these operations. CRUD serves as the foundation for other operations, including access and permission management, security control, transaction control, and performance optimization in the databases.

Let’s now understand the CRUD operations in detail one by one, with their syntaxes, code examples, and detailed explanations. We’ll use a university database, which has relational tables, to better demonstrate CRUD operations. We will define three tables for our examples: Enrollments, Courses, and Students.

Table Definitions

• Students table: This table contains the details of all students in the university database.

• Courses table: This table contains the details of all courses in the university database.

• Enrollments table: This table links the students with the classes they are taking. The many-to-many (M:M) relationship between Students and Courses is established by this junction table.

Now that we have the table structures, let’s explore each CRUD operation with detailed examples.

Create Operation – INSERT

To add new records (rows) to a table, use the Create operation. To accomplish this in SQL, use the INSERT INTO statement. In an RDBMS, a database table row is called a record. Its columns are called fields or attributes. A table can have one or more new records with unique field values added by performing the CREATE action.

Syntax:

• table_name: The name of the table where the record is inserted.
• column1, column2, etc.: The specific columns where values will be inserted.
• value1, value2, etc.: The actual values to be inserted in the respective columns.

Example: Query to insert a new student record in the table.

Explanation:

Here, we are adding a new student, Roy Smith, with a StudentID of 001, a major in information technology, and an age of 20. The data types and column orders that are supplied must coincide with the values in the VALUES clause.

Example: Query to insert multiple Students record in the table.

Explanation:

Here, we are inserting two more records of Bob and John. We can insert multiple records at once by separating each set of values with commas.

Example: Query to insert a new course record in the table.

Explanation:

In this example, we created a query that adds a course named “Introduction to Programming” offered by the Computer Science department with 4 credits.

Read Operation – SELECT

The Read operation is used to fetch data from the database. In SQL, this can be done using the SELECT statement. This operation is significant, particularly when the database needs to be searched for specific information to return it, along with other parameters such as sorting and filtering. The READ operation is capable of retrieving any or all of the records from a given table along with any of the fields or all of the fields.

Syntax:

SELECT column1, column2, …

FROM table_name;

• column1, column2, etc.: The columns you want to retrieve.
• table_name: The table from which you are retrieving the data.

Example: Query to fetch or retrieve all students from the database.

Explanation:

The * wildcard retrieves all columns from the Students table. In this example, the query will return all rows (students) in the table.

Example: Query to fetch or retrieve specific columns from the student table of the database.

Explanation:

In this example, the query retrieves only the FirstName, LastName, and Major columns from the Students table, which can be useful for displaying selective data.

Example: Query to filter results using the WHERE clause in the database.

Explanation:

The WHERE clause filters students based on their major. In this example, we are only interested in students majoring in Information Technology.

Update Operation – UPDATE

The Update operation is used to modify existing records in a table. You can modify the values of one or more columns for particular rows by using the UPDATE command. A record’s one or more fields can be modified and kept updated with a single UPDATE operation. The database system makes sure that if several fields need to be updated, they are updated either completely or not at all.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2, …

WHERE condition;

• table_name: The name of the table where the records will be updated.
• column1, column2, etc.: The columns to update with new values.
• condition: A condition to ensure only the desired records are updated.

Example: Query to update the major of the student with StudentID having 001 in the student’s table.

Explanation:

In this example, we update the Major field for the student with StudentID = 001. This changes Roy Smith’s major from Computer Science to Software Engineering.

Example: Query to update the major of students having an age less than 18 in the student’s table.

Explanation:

In this example, the query updates the Major field to Undeclared for all students younger than 18.

Example: Query to update the course credits in the courses table.

Explanation:

In this example, the query changes the credits for the course with CourseID = 305 (Introduction to Programming) from 4 to 5.

Delete Operation – DELETE

The Delete operation is used to remove existing records from a table. The DELETE statement is the SQL command used for this purpose.

Syntax:

DELETE FROM table_name

WHERE condition;

• table_name: The name of the table where records will be deleted.
• condition: A condition to specify which rows should be deleted.

Example: Query to delete the student having StudentID = 003 from the Students table.

Explanation:

In this example, the query deletes the student with StudentID = 003 (John) from the Students table.

Example: Query to delete the student having EnrollmentDate before February 23, 2020, from the Enrollments table.

Explanation:

In this example, the query removes all enrollments that took place before Feb 23, 2020. This is useful for cleaning up old or obsolete data.

Example: Query to delete the record of all students having undeclared departments.

Explanation:

In this example, the query deletes all students who have not declared a major (i.e., those with Major = ‘Undeclared’).

Why are CRUD Operations So Important?

CRUD operations form an integral part of database management because these are the critical aspects that allow the performance of the four fundamental actions to be undertaken over any data in a relational database management system. Here is why CRUD operations are important in SQL:

1. Integral to Information Management

CRUD Operations, Create, Read, Update, and Delete – simply form the basis for data handling. Look at any data-related application, be it a web mobile, or enterprise application, it uses CRUD to control the data. Without Crud functions, users would neither be able to use the tool to enhance their data nor manage it properly.

2. Universal in Software Development

The word CRUD has become the most familiar in the Software Development industry. It does not matter if you are working with SQL server, NoSQL, REST API, or File-based storage, they all utilise some sort of standard counsel for such operations as inserting, fetching, updating, and deleting data. All web applications such as social networks or e-commerce systems, inventory management systems and almost all systems are functioning through these four operations.

3. Efficient Data Manipulation

CRUD operations offer focused and organised data handling. Users are permitted to input data through CREATE operations, fetch through READ, modify existing data through UPDATE, and delete the data through DELETE.

4. Application Stability

A well-designed CRUD operation enhances an application’s scalability:

• Databases can handle big datasets and deliver quick query responses when they perform optimal read operations (like indexing).
• By ensuring that the data is current and relevant, update and delete procedures free the system’s capacity to expand without becoming slowed down by out-of-date or irrelevant data.

Best Practices for CRUD Operations

When performing CRUD operations in SQL, keep the following best practices in mind:

1. Use Transactions for Critical Operations: In the case of several updates occurring, try to use transactions to guarantee that those updates are complete.

Example:

BEGIN TRANSACTION;
— series of update or delete queries
COMMIT;

2. Data Backup: Whenever there is a need for a large DELETE or UPDATE operation, have a second order to back up your data even if the operation is intended.

3. Using Indexes: Indexing optimises SELECT operations by reducing the time that is spent looking for rows in large tables. They work even more efficiently on such columns which are frequently used in SELECT queries like StudentID, CourseID, etc.

4. Restrict the Use of * in SELECT Statements: Avoid bringing out all the columns, use only the relevant columns to increase the efficiency of the queries being prepared.

5. Filter Queries: Forgetting to include the WHERE clause, especially in UPDATE and DELETE statements will lead to improper update and deletion of intended target records. Therefore, use SQL’s WHERE clause whenever required.

Conclusion

CRUD (Create, Read Update, and Delete) operations in SQL are referred to as the foundation stones in database administration. This article has taken the reader through the steps involved in each of the CRUD operations as exemplified by utilising a university database. We’ve covered basic examples such as adding students, reading course information, updating enrollment records, and deleting students, as well as more complex queries using joins and filtering.

Anyone using relational databases must become proficient in CRUD operations in SQL. You can efficiently manipulate data using these procedures while still preserving the integrity of your database. Your CRUD operations can be made secure and effective by adhering to best practices, which include using transactions and doing frequent backups.