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 TableName ( Column1 DataType, Column2 DataType, ... );
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), DepartmentID INT );
ALTER TABLE TableName ADD NewColumn DataType; ALTER TABLE TableName MODIFY ColumnName NewDataType; ALTER TABLE TableName DROP COLUMN ColumnName;
ALTER TABLE Employees ADD Email VARCHAR(100); ALTER TABLE Employees MODIFY DepartmentID INT NOT NULL; ALTER TABLE Employees DROP COLUMN Email;
DROP TABLE TableName;
DROP TABLE Employees;
CREATE INDEX IndexName ON TableName (Column1, Column2, ...);EXAMPLE
CREATE INDEX IX_DepartmentID ON Employees (DepartmentID);
DROP VIEW ViewName;
DROP INDEX IX_DepartmentID;
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.
CREATE VIEW ViewName AS SELECT Column1, Column2, ... FROM TableName WHERE Condition;
CREATE VIEW EmployeeNames AS SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1;
DROP VIEW ViewName;
DROP VIEW EmployeeNames;
CREATE DATABASE DatabaseName;
DROP DATABASE DatabaseName;