10 Types of Programming Languages Every Coder should Know
Learn about different types of programming languages and how they are implemented in the real world.
8 mins read
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
- 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.
- 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.
- 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.
- 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.
POSTGRADUATE PROGRAM IN
Multi Cloud Architecture & DevOps
Master cloud architecture, DevOps practices, and automation to build scalable, resilient systems.
What are CRUD Operations?
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.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Major VARCHAR(100)
);
- Courses table: This table contains the details of all courses in the university database.
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Department VARCHAR(100),
Credits INT
);
- 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.
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
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:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
- 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.
INSERT INTO Students (StudentID, FirstName, LastName, Age, Major)
VALUES (001, 'Roy', 'Smith', 20, 'Information Technology);
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.
INSERT INTO Students (StudentID, FirstName, LastName, Age, Major)
VALUES
(002, 'Bob', 'Jameson', 18, 'Electrical Engineering'),
(003, 'John', 'Brown', 20, 'Mathematics');
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.
INSERT INTO Courses (CourseID, CourseName, Department, Credits)
VALUES (305 'Introduction to Programming, 'Computer Science', 4);
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.
SELECT * FROM Students;
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.
SELECT FirstName, LastName, Major
FROM Students;
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.
SELECT FirstName, LastName, Age
FROM Students
WHERE Major = 'Information Technology';
Explanation:
The WHERE clause filters students based on their major. In this example, we are only interested in students majoring in Information Technology.
82.9%
of professionals don't believe their degree can help them get ahead at work.
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.
UPDATE Students
SET Major = 'Software Engineering'
WHERE StudentID = 001;
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.
UPDATE Students
SET Major = 'Undeclared'
WHERE Age < 18;
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.
UPDATE Courses
SET Credits = 5
WHERE CourseID = 301;
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.
DELETE FROM Students
WHERE StudentID = 003;
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.
DELETE FROM Enrollments
WHERE EnrollmentDate < '2020-02-23';
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.
DELETE FROM Students
WHERE Major = 'Undeclared';
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.
FAQs
What are CRUD operations in SQL?
CRUD operations in SQL represent the four fundamental functions for managing and interacting with data in a relational database: Create, Read, Update, and Delete.
How can I use SQL to generate a new record?
The INSERT INTO statement adds new rows to a selected database with the necessary data values so that you can create a new record in SQL.
What is the difference between UPDATE and DELETE operations in SQL?
UPDATE modifies existing data within a table, while DELETE removes entire records from a table. Both operations affect data that's already stored in the database.
How can I join tables when reading data in SQL?
You can join tables using various types of SQL JOIN clauses (e.g., INNER JOIN, LEFT JOIN) to combine rows from two or more tables based on related columns.
What are some best practices when performing CRUD operations?
Using transactions to ensure data consistency, backing up data before making significant changes or deletions, utilising indexes to enhance read efficiency, and consistently utilising WHERE clauses to prevent inadvertent updates or deletions are a few best practices.
Updated on October 1, 2024
Link
