SQL CRUD: CREATE, READ, UPDATE, DELETE, DROP, and ALTER in SQL

For database management, you've gotta understand the fundamental operations in SQL that allow you to create new tables, insert or update new records, tweak information in columns, and drop tables where necessary.

These types of actions are known as CRUD operations and use the Data Manipulation Language (DML) commands , , , and :

• : Creating new table or adding new records to a database using the statement.
• : Retrieving records from a database using the statement.
• : Modifying existing records in a database using the statement.
• /: Removing records from a database using statement or removing a whole table via

To understand each CRUD command, we'll use the table from the Amazon database in this Amazon SQL Interview Question to test out the queries. While we'll provide sample queries and expected results, you won't be able to run these queries as it's not a live database. Our SQL interview platform can only run queries!

table which stores Amazon product reviews:

CREATE SQL Command Example: Adding a New Table "customers"

Let's say, Amazon wants to maintain information of their customers. This allows the store to track customer interactions, personalize marketing efforts, and provide better customer service.

The following query creates a new table named with customer information such as customer ID, first name, last name, email, and phone number.

Query Example:

Expected Result:

A new table named is created with the column serving as the primary key for uniquely identifying each customer.

Inserting Records into Table "customers"

After creating the table, we insert three new records of customers.

Query Example:

Expected Results:

These three customers' records are inserted into the table, each with a unique customer ID and corresponding first name, last name, email and phone number values.

CREATE Example: Inserting a New Review

A customer has left a new product review on the Amazon website. Let's add it to the table.

Query Example:

Expected Result:

A new review is inserted into the table:

SQL READ Example: Retrieving Reviews for a Product

Now, let's say you want to retrieve all the reviews for a particular product item with the ID 12580.

Query Example:

Expected Result:

All reviews for the product with ID 12580 is retrieved from the table:

SQL UPDATE Example: Modifying an Existing Review

Imagine you need to adjust the star rating of a specific review (identified by review ID 1301) from 4 stars to 3 stars.

Query Example:

Expected Result:

Before the operation:

After the operation, the rating of the review ID 1301 is updated to 3 stars:

DELETE Example: Removing an Existing Review

Suppose you need to delete a particular review identified by review ID 4517 from the table.

Query Example:

Expected Result:

Before the operation:

After the operation, review ID 4517 is removed from the table:

SQL DROP Operation

The operation is used to remove database objects such as tables, indexes, views, or even entire databases. It is a Data Definition Language (DDL) command.

Using the DROP operation requires caution, as it ‼️ permanently deletes database objects and their associated data ‼️.

DROP Example: Removing a Table

Consider a scenario where you must permanently remove the table along with all its contents from the database.

Query Example:

Expected Result:

The table and all its contents is now permanently deleted from the database. Since the table is dropped, there won't be any data to display.

SQL ALTER Operation

The operation is used to modify the structure of existing database objects. It allows adding, modifying, or dropping columns, constraints, or indexes from a table. It is also a Data Definition Language (DDL) command.

ALTER Example: Adding a New Column for Product Reviews

Imagine you want to enhance the structure of the table by introducing a new column called which will store product reviews.

Query Example:

Expected Result:

The table's structure is modified to include a new column named of type . However, since the column is just added and not populated, there won't be any data to display initially.

SQL ALTER Example: Altering Column Data Type

Suppose you want to update the data type of the column in the table. Currently, it stores integer values like 3, 4, 5, but you need it to store floating-point numbers like 3.5, 4.4, 5.0.

Query Example:

Expected Result:

The data type of the column in the table is changed from integer to float.

SQL ALTER Example: Altering Column Name

Imagine you need to rename the column in the table. You might want to change it to for better clarity and consistency.

Query Example:

Expected Result:

The column in the table is renamed to .

SQL ALTER Example: Altering Column Length

Let's say, you need to increase the maximum length of the column in the table. Currently, it can only store limited text. You want to expand it to accommodate longer reviews, allowing up to 1000 characters.

Query Example:

Expected Result:

The maximum length of the column in the table is increased to 1000 characters.

DML vs. DQL

DML is concerned with modifying or manipulating data in the database, while DQL (Data Query Language) is focused on retrieving or querying data from the database. While DML was covered briefly in this article, we created a whole free SQL tutorial to practice your query skills:

]