SQL Components part-1

SQL Components:

• SQL (Structured Query Language) consists of several key components that facilitate the management and manipulation of relational databases.
• Here’s an overview of the primary components of SQL:

•  DQL:

• It Stands for DATA QUERY LANGUAGE.
• Data Query Language (DQL) is a component of SQL (Structured Query Language) specifically focused on retrieving data from a database. 
• The primary command associated with DQL is the SELECT statement, which allows users to specify the data they wish to access.

• Key Features of DQL:
• Data Retrieval: 
• DQL enables users to extract data from one or more tables, making it essential for data analysis and reporting.
• Filtering and Conditions: 
• With the WHERE clause, DQL allows users to apply conditions to refine the results, retrieving only the records that meet specific criteria.
• Sorting Results:
• The ORDER BY clause can be used to sort the retrieved data based on one or more columns, either in ascending or descending order.
• Joining Tables: 
• DQL supports various types of joins (like INNER JOIN, LEFT JOIN) to combine data from multiple tables, which helps in gathering related information.
• Aggregate Functions: 
• DQL can perform calculations on data sets using aggregate functions like COUNT, SUM, and AVG to provide summarized information.

• Basic Syntax:

• A simple DQL query follows this structure:
• SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column_name;
• Example:
• SELECT Name, Position FROM Employees WHERE Department = 'Sales' ORDER BY Name ASC;
• In this example, the query retrieves the names and positions of employees in the "Sales" department and organizes the results in alphabetical order by name.

• DDL:
• It stands for Data Definition Language.
• Data Definition Language (DDL) is a subset of SQL (Structured Query Language) that focuses on defining and managing the structure of database objects. 
• DDL commands are used to create, alter, and delete database schemas, tables, indexes, and other objects. 
• Unlike DML (Data Manipulation Language), which deals with the data itself, DDL is concerned with the structure that holds the data.

• Key Features of DDL:
• Structure Management: 
• DDL allows users to create and modify the layout of database objects, establishing how data is organized and stored.
• Schema Definition: 
• DDL commands define the database schema, which serves as a blueprint for how data is structured.
• Data Types: 
• DDL allows for the specification of data types for each column in a table, ensuring that data is stored correctly.
• Integrity Constraints: 
• DDL supports defining constraints (like primary keys, foreign keys, unique constraints) to enforce data integrity and relationships.

• Common DDL Commands:

• CREATE: Used to create new tables or other database objects.
• ALTER: Used to modify the structure of an existing database object.
• DROP: Used to delete tables or other database objects.

• CREATE Command: 

• The CREATE command in SQL is a part of the Data Definition Language (DDL) and is used to define and initialize new database objects. 
• These objects can include tables, views, indexes, and databases themselves. 
• By using the CREATE command, users set up the structure of how data will be stored.
• Purpose:

• To create new objects in a database, such as tables, views, and schemas.
• Define the structure of a table, including its columns and data types.
• Syntax:

CREATE TABLE table_name ( 

 column1 datatype constraint, 

 column2 datatype constraint, ... );

• Example:

CREATE TABLE Employees ( 

 EmployeeID INT PRIMARY KEY,

 Name VARCHAR(100), 

 Department VARCHAR(50), 

 Salary DECIMAL(10, 2) );

• In this example, the CREATE command is used to create a table called Employees with columns for EmployeeID, Name, Department, and Salary. 
• Each column is assigned a specific data type (e.g., INT, VARCHAR, DECIMAL), and constraints like PRIMARY KEY are applied to ensure data integrity.

• ALTER Command:

• The ALTER command in SQL is part of the Data Definition Language (DDL) and is used to modify the structure of existing database objects like tables, columns, and indexes. 
• With ALTER, you can add, delete, or modify columns, change data types, and apply or remove constraints without losing the data in the table.
• Purpose:
• Modify the structure of existing tables or other database objects.
• Add, modify, or delete columns, constraints, and data types.
• Syntax:
• Add a column:

ALTER TABLE table_name 

ADD column_name datatype;

• Modify a column:

ALTER TABLE table_name 

MODIFY column_name datatype;

• Drop a column:

ALTER TABLE table_name 

DROP COLUMN column_name;

• Example:
• Add a new column:

ALTER TABLE Employees 

ADD DateOfJoining DATE;

• In this example, a new column DateOfJoining is added to the Employees table.
• Modify a column's data type:

ALTER TABLE Employees 

MODIFY Salary DECIMAL(12, 2);

• This changes the Salary column’s data type to have a precision of 12 digits and 2 decimal places.
• Drop a column:

ALTER TABLE Employees 

DROP COLUMN Department;

• This command removes the Department column from the Employees table.

• DROP Command:

• The DROP command in SQL is a part of the Data Definition Language (DDL) and is used to permanently delete database objects such as tables, views, indexes, or entire databases. 
• Once executed, this action is irreversible, and all the data along with the object’s structure is removed.
• Purpose:
• To permanently remove a database object (like a table, view, or index).
• Deletes both the structure and all the data within the object.
• Syntax:

DROP TABLE table_name;

• Example:

DROP TABLE Employees;

• In this example, the Employees table is permanently deleted from the database, along with all the records it contains.
• Important Notes:

• Irreversible: 
• The DROP command cannot be undone. Once an object is dropped, it is permanently deleted along with all its data.
• Data Loss: 
• Ensure that you no longer need the data or have backed it up before using DROP.