CRUD in SQL- Create Database, Create Table, Insert, Select, Update,Alter table, Delete

by | Apr 15, 2024 | SQL | 0 comments

CRUD stands for Create, Read, Update, and Delete. It’s a set of basic operations that are essential for managing data in a database or any persistent storage system. It refers to the four basic functions that any persistent storage application needs to perform. Persistent storage refers to data storage that retains information even after the device is powered off, unlike RAM which loses data when power is lost. Examples of persistent storage include hard drives and solid-state drives.

Here’s a breakdown of each CRUD operation:

  • Create: This operation allows you to add new data to the storage system.
  • Read: This operation allows you to retrieve existing data from the storage system.
  • Update: This operation allows you to modify existing data in the storage system.
  • Delete: This operation allows you to remove data from the storage system.

CRUD is fundamental for various applications, including:

  • Databases: CRUD operations are the foundation of working with data in relational and NoSQL databases.
  • APIs: When you build APIs, CRUD represents the essential functionalities for managing resources.
  • User Interfaces: Many interfaces use CRUD functionalities behind the scenes to allow users to view, edit, and delete information through forms and reports.

Overall, CRUD provides a simple and effective way to understand the core data manipulation operations in computer programming.with appropriate privileges to create or delete databases, especially in production environments. Make sure you have the necessary permissions before attempting these operations.


Create Table

The CREATE TABLE statement is used to define a new table in a database. It follows this general syntax:


CREATE TABLE table_name (
  column1 data_type [constraint1, constraint2, ...],
  column2 data_type [constraint1, constraint2, ...],

Explanation of Parts:

  • CREATE TABLE: This keyword initiates the table creation process.
  • table_name: This is a chosen name that identifies your table.
  • column1column2, etc.: These represent the individual columns within the table, each holding a specific type of data.
  • data_type: This specifies the kind of data each column can store. Examples include int for integers, varchar for variable-length text, and date for storing dates.
  • constraint1constraint2, etc.: These are optional clauses that define rules for the data within a column. Common constraints include NOT NULL to ensure a value must be present, and PRIMARY KEY to uniquely identify each row.

The data types of columns or fields may vary from one database system to another. For example, NUMBER is supported in Oracle database for integer values whereas INT is supported in MySQL.  

the CREATE TABLE syntax is available in the Data Definition Language (DDL) subset

Example 1: Creating a Customers Table

Let’s create a table named Customers to store customer information:


CREATE TABLE Customers (
  customer_id int NOT NULL PRIMARY KEY,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) NOT NULL,
  email varchar(100) UNIQUE,
  phone_number varchar(20)

Explanation of the Example:

  • This code creates a table named Customers.
  • It has five columns:
    • customer_id: Stores a unique integer identifier for each customer (primary key).
    • first_name and last_name: Stores customer’s first and last name (not null).
    • email: Stores the customer’s email address (unique).
    • phone_number: Stores the customer’s phone number (optional).

Example 2: Creating a Products Table

Here’s another example for a Products table:


  name varchar(255) NOT NULL,
  price decimal(10,2) NOT NULL,
  description text,
  stock int DEFAULT 0

Explanation of the Example:

  • This code creates a table named Products.
  • It has five columns:
    • product_id: Stores a unique integer identifier for each product (primary key with auto-increment).
    • name: Stores the product name (not null).
    • price: Stores the product’s price (decimal with two decimal places, not null).
    • description: Stores a textual description of the product (optional).
    • stock: Stores the current stock level (default value 0).

These are just a couple of examples. You can create tables to store any kind of data following this structure and define constraints based on your specific needs.

Creating data in a database using CRUD operations

When it comes to creating data in a database using CRUD operations (Create, Read, Update, Delete), there are several ways to perform the “Create” operation, depending on the specific requirements of the application and the capabilities of the database management system (DBMS) being used. Here are some common types of create operations:

Single Record Insertion:

This is the simplest form of the create operation, where a single record is inserted into a table.

Example INSERT INTO users (name, email) VALUES ('John Doe', '');

Bulk Insertion:

Bulk insertion involves inserting multiple records into a table in a single operation.

It is often more efficient than inserting records one by one, especially when dealing with large datasets.

Example INSERT INTO users (name, email) VALUES ('Alice', ''), ('Bob', ''), ('Charlie', '');

Insertion from Another Table:

Data can be inserted into a table by selecting records from another table and inserting them into the target table.

This is useful for copying data or transforming data from one table to another.

Example INSERT INTO new_users (name, email) SELECT name, email FROM old_users WHERE age > 18;

Insertion with Default Values:

If certain columns have default values defined, those values are automatically used during insertion if no explicit value is provided.

Example INSERT INTO users (name) VALUES ('Jane');

Assuming ’email’ column has a default value defined, it will be used during insertion.

Insertion with Generated Values:

Some databases support generated values for columns, such as auto-incrementing primary keys or UUIDs.

These values are automatically generated by the database system during insertion.

Example (using SQL with auto-incrementing primary key): INSERT INTO users (name) VALUES ('Jane');

Insertion Using ORM (Object-Relational Mapping):

In object-oriented programming, ORM frameworks abstract away the database operations and allow developers to create and manipulate objects instead of writing SQL queries directly.

Example (using an ORM like SQLAlchemy in Python): user = User(name='John', email='') db.session.add(user) db.session.commit()


For the “Read” operation, it involves retrieving data from the database. Here are common types of read operations:

Selecting All Records:

Retrieve all records from a table.

Example SELECT * FROM users;

Selecting Specific Columns:

Retrieve specific columns from a table.

Example SELECT name, email FROM users;

Filtering with WHERE Clause:

Retrieve records that meet specific conditions using the WHERE clause.

Example SELECT * FROM users WHERE age > 18;

Sorting with ORDER BY Clause:

Retrieve records sorted in ascending or descending order based on one or more columns.

Example SELECT * FROM users ORDER BY name ASC;

Limiting Results with LIMIT Clause:

Retrieve a limited number of records from the result set.

Example SELECT * FROM users LIMIT 10;

Joining Tables:

Retrieve data from multiple tables by joining them based on related columns.

Example SELECT, orders.order_date FROM users INNER JOIN orders ON = orders.user_id;

Aggregation Functions:

Retrieve aggregated data using functions like COUNT, SUM, AVG, MIN, MAX, etc.

Example SELECT COUNT(*) FROM users; SELECT AVG(age) FROM users WHERE country = 'USA';

Grouping Results with GROUP BY Clause:

Group rows that have the same values into summary rows.

Example SELECT country, COUNT(*) FROM users GROUP BY country;


Retrieve data from a subquery and use it in the main query.

Example SELECT name, email FROM users WHERE age > (SELECT AVG(age) FROM users);

These are some common types of read operations used to retrieve data from a database using CRUD operations. The appropriate method to use depends on the specific requirements and constraints of the application.

***Create & Delete Database:-

Creating and deleting databases can vary slightly depending on the specific database management system (DBMS) you are using. Let us see some examples for MySQL and PostgreSQL, two popular relational database management systems.

1. MySQL Example:

Creating a Database:

CREATE DATABASE my_database;

Deleting a Database:

DROP DATABASE my_database;

2. PostgreSQL Example:

Creating a Database:

CREATE DATABASE my_database;

Deleting a Database:

DROP DATABASE my_database;

In both examples:

  • my_database is the name of the database you want to create or delete.
  • CREATE DATABASE is the SQL command used to create a new database.
  • DROP DATABASE is the SQL command used to delete an existing database.

It’s important to exercise caution when using DROP DATABASE because it permanently deletes the database and all of its contents. Always double-check before executing this command to avoid accidental data loss.

Additionally, in practice, you may need to authenticate



Written by HintsToday Team

Related Posts

Database Structures, Types of Keys

Learn about tables, fields (or attributes), records, keys and table relationships. What is database structure? A database structure is the blueprint that defines how data is arranged ,organized, stored, accessed, and managed within a database. It's the underlying...

read more

SQL Data Types(Numeric, String & Date)- Default Values

SQL (Structured Query Language) supports various data types to represent different kinds of data. These data types define the format and constraints of the data stored in each column of a table. Here are some common SQL data types: Numeric Types: INT: Integer type,...

read more

Get the latest news

Subscribe to our Newsletter


Submit a Comment

Your email address will not be published. Required fields are marked *