Create Query in SQL - Javagyansite

In this article, we will delve deeply into “Create Query in SQL”. Let’s get started.

Table of Contents

What is use of Create Query in SQL ?

The use of the CREATE query in SQL is to create or define various database objects and elements within a relational database management system (RDBMS). These objects and elements can include tables, indexes, views, stored procedures, functions, databases, schemas, triggers, users, and roles, among others.

Let’s now look how we can create these Database Objects and elements using Create query in SQL.

Create Table Query in SQL

The CREATE TABLE query in SQL is used to define and create tables within a database.

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    salary DECIMAL(10, 2),
    hire_date DATE
);

This query creates a table named “employees” with columns for employee ID, name, salary, and hire date.

Create an Index in SQL

Indexes are used to improve the speed of data retrieval. Here’s how you can create an index:

CREATE INDEX idx_employee_name ON employees (employee_name);

This query creates an index named “idx_employee_name” on the “employee_name” column of the “employees” table.

Create a View in SQL

Views are virtual tables that provide a simplified way to access data. To create a view, you can use the CREATE VIEW statement:

CREATE VIEW employee_names_salaries AS
SELECT employee_name, salary
FROM employees;

This query creates a view named “employee_names_salaries” that displays only employee names and salaries from the “employees” table.

Create a Stored Procedure in SQL

Stored procedures are sets of precompiled SQL statements that can be executed. Here’s an example of creating a stored procedure:

CREATE PROCEDURE sp_GetEmployeeByID (IN empId INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = empId;
END;

Create a User-Defined Function

User-defined functions allow you to encapsulate custom logic. Here’s an example of creating a scalar function:

CREATE FUNCTION CalculateSquare(num INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = num * num;
    RETURN result;
END;

This query creates a scalar function named “CalculateSquare” that calculates the square of the input number.

Create a Database

To create a new database in SQL, you can use the CREATE DATABASE statement:

CREATE DATABASE MyDatabase;

This query creates a new database named “MyDatabase.”

Create a Schema

Schemas are used to organize database objects within a database. You can create a schema using the CREATE SCHEMA statement:

CREATE SCHEMA MySchema;

This query creates a new schema named “MySchema.”

Create a Trigger

Triggers are special types of stored procedures that are automatically executed when specific events occur in the database. Here’s an example of creating a trigger:

CREATE TRIGGER AuditEmployeeChanges
AFTER UPDATE
ON employees FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, old_employee_name, new_employee_name, old_salary, new_salary, change_date)
    VALUES (OLD.employee_id, OLD.employee_name, NEW.employee_name, OLD.salary, NEW.salary, NOW());
END;

This query creates a trigger named “AuditEmployeeChanges” that fires after an update operation on the “employees” table.It captures changes made to the employees table and inserts the relevant information into the employee_audit table.

Create a User

To create a new user account in SQL, you can use the CREATE USER statement:

CREATE USER 'MyUser'@'localhost' IDENTIFIED BY 'MyPassword';

This query creates a new user named “MyUser” with the specified password.

Create a Role

Roles are used to group users and manage permissions. You can create a role using the CREATE ROLE statement:

CREATE ROLE MyRole;

This query creates a new role named “MyRole.”

Conclusion: Create Query in SQL

This article has provided a comprehensive overview of the “CREATE Query in SQL” and its versatile applications in defining and creating various database objects and elements within a relational database management system (RDBMS). We explored the creation of tables, indexes, views, stored procedures, user-defined functions, databases, schemas, triggers, users, and roles, showcasing their individual syntax and usage.

Related Articles: