In SQL (Structured Query Language), a DDL (Data Definition Language) command is used to define, manage, and manipulate the structure of database objects such as tables, indexes, and views. DDL commands do not manipulate the data within the database but rather the schema or structure of the database itself. Here are some common DDL commands in SQL:
CREATE TABLE:
CREATE TABLE TableName ( Column1 DataType, Column2 DataType, ... );
EXAMPLE:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), DepartmentID INT );
ALTER TABLE
ALTER TABLE TableName ADD NewColumn DataType; ALTER TABLE TableName MODIFY ColumnName NewDataType; ALTER TABLE TableName DROP COLUMN ColumnName;
EXAMPLE
ALTER TABLE Employees ADD Email VARCHAR(100); ALTER TABLE Employees MODIFY DepartmentID INT NOT NULL; ALTER TABLE Employees DROP COLUMN Email;
DROP TABLE:
DROP TABLE TableName;
Example
DROP TABLE Employees;
CREATE INDEX:
CREATE INDEX IndexName ON TableName (Column1, Column2, ...);EXAMPLE
CREATE INDEX IX_DepartmentID ON Employees (DepartmentID);
DROP INDEX:
Example
DROP INDEX IX_DepartmentID;
CREATE VIEW
In SQL, a view is a virtual table that is based on the result of a SELECT query. Views do not store data themselves; instead, they provide a way to present data from one or more tables in a structured and simplified manner. Views are often used to:
- Simplify Complex Queries: Views can encapsulate complex SQL logic and join operations, making it easier for users to query and retrieve data without needing to understand the underlying data model intricacies.
- Enhance Data Security: Views can restrict access to specific columns or rows of a table. This can help enforce security policies by limiting what data users can see or modify.
- Provide Data Abstraction: Views allow you to present a simplified and user-friendly interface to the database, hiding details about the actual schema and making it easier to work with the data.
- Centralize Business Logic: Views can encapsulate business rules and calculations, ensuring that they are applied consistently whenever the view is queried.
A view is a virtual table based on the result of a SELECT query. This command creates a view.
Syntex
CREATE VIEW ViewName AS SELECT Column1, Column2, ... FROM TableName WHERE Condition;
Example
CREATE VIEW EmployeeNames AS SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1;
DROP VIEW:
DROP VIEW ViewName;
Example
DROP VIEW EmployeeNames;
CREATE DATABASE
CREATE DATABASE DatabaseName;
DROP DATABASE
DROP DATABASE DatabaseName;
Related Articles
The post DDL Commands in SQL: A Complete Guide appeared first on Data Science institute and Data Analytics Training institute.
This post first appeared on Coaching Tally Accounts & Finance ,taxation,bankin, please read the originial post: here