SQL Component Part-2

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:

• DML:

• It stands for Data Manipulation Language.
• Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to manage and manipulate the data within database tables. 
• Unlike DDL, which deals with the structure of the database, DML focuses on the actual data and allows users to perform operations such as inserting, updating, deleting, and retrieving data.

• Key Features of DML:

• Modify Data: DML commands allow users to insert, update, or delete data from tables.
• Data Retrieval: DML also includes SELECT statements (often considered part of DQL) to query and fetch data from tables.
• Transactional Control: Changes made by DML commands can be rolled back or committed, ensuring data integrity.

• Common DML Commands:
• INSERT: Adds new data to a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes existing data from a table.

• INSERT Command:

• The INSERT command in SQL is part of the Data Manipulation Language (DML) and is used to add new records into a table. 
• This command allows you to specify values for each column, and it can be used to insert one or multiple rows at once.

• Purpose:

• Add new rows of data to a table.
• Populate specific or all columns in a row with new values.

• Syntax:

• For inserting data into specific columns:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• For inserting data into all columns:

INSERT INTO table_name VALUES (value1, value2, ...);

• Example 1: Insert data into specific columns

INSERT INTO Employees (Name, Department, Salary) VALUES ('Alice', 'HR', 60000);

• In this example, a new employee record is added with the name "Alice," department "HR," and a salary of 60,000.
• Example 2: Insert data into all columns

INSERT INTO Employees VALUES (1, 'John', 'Finance', 75000);

• In this example, a complete row is inserted with values for all columns: EmployeeID, Name, Department, and Salary.
• Important Notes:

• The order of the values must match the order of the columns listed.
• You can omit columns that are allowed to accept NULL or have default values.
• Multiple rows can be inserted at once by providing multiple VALUES sets separated by commas.
• Example 3: Insert multiple rows

INSERT INTO Employees (Name, Department, Salary) VALUES ('Bob', 'Marketing', 50000), ('Carol', 'IT', 70000);

• This example inserts two rows into the Employees table in a single INSERT statement.

• UPDATE Command:

• The UPDATE command in SQL is part of the Data Manipulation Language (DML) and is used to modify existing records in a table. 
• It allows you to change the values in one or more columns for rows that meet a specific condition.

• Purpose:

• To modify existing data in a table.
• Update one or multiple columns for specific records.

• Syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

• The WHERE clause specifies which rows should be updated. If omitted, all rows in the table will be updated.
• Example:
• Update a specific row:

UPDATE Employees SET Salary = 80000 WHERE EmployeeID = 1;

• In this example, the Salary of the employee with EmployeeID 1 is updated to 80,000.
• Update multiple columns:

UPDATE Employees SET Salary = 85000, Department = 'Finance' WHERE Name = 'Alice';

• Here, both the Salary and Department are updated for the employee named "Alice."
• Update all rows (without a WHERE clause):

UPDATE Employees SET Department = 'Sales';

• This updates the Department column to "Sales" for all employees in the table.
• Important Notes:

• Always include a WHERE clause to avoid updating all rows unintentionally.
• You can update multiple columns in a single UPDATE statement.
• Changes can be rolled back if they are part of a transaction.

• DELETE Command:

• The DELETE command in SQL is part of the Data Manipulation Language (DML) and is used to remove existing records from a table. 
• This command can delete one or multiple rows based on a specified condition.

• Purpose:

• To remove specific rows from a table.

• Clean up data by deleting records that are no longer needed.

• Syntax:

DELETE FROM table_name WHERE condition;

• The WHERE clause specifies which rows should be deleted. If omitted, all rows in the table will be deleted.
• Example:
• Delete a specific row:

DELETE FROM Employees WHERE EmployeeID = 3;

• In this example, the record of the employee with EmployeeID 3 is deleted from the Employees table.
• Delete multiple rows based on a condition:

DELETE FROM Employees WHERE Department = 'HR';

• This command removes all employees who belong to the "HR" department.
• Delete all rows (without a WHERE clause):

DELETE FROM Employees;

• This command deletes all records from the Employees table, but the table structure remains intact.
• Important Notes:
• Irreversible Action: Once executed, the deletion cannot be undone unless you use transactions (with ROLLBACK).
• Use the WHERE Clause: Always include a WHERE clause to prevent deleting all rows unintentionally.
• Performance Considerations: Deleting large numbers of rows can affect performance; consider using batch deletes if needed.

• DCL:

• It stands for Data Control Language.
• Data Control Language (DCL) is a subset of SQL used to control access to data within a database. 
• DCL commands primarily deal with the permissions and access rights of users and roles, ensuring that only authorized individuals can perform certain operations on the database.

• Key Features of DCL:

• Manages user permissions for database objects.
• Enhances database security by restricting access.
• Allows administrators to grant or revoke access rights.

• Common DCL Commands:
• GRANT: Provides specific privileges to users or roles, allowing them to perform actions on database objects.

• REVOKE: Removes specific privileges from users or roles, revoking their access to certain database operations.

• Important Notes:
• Privileges: Privileges can include actions like SELECT, INSERT, UPDATE, DELETE, and more.
• Roles: Permissions can also be assigned to roles, which can then be granted to multiple users, simplifying permission management.
• Security: DCL commands are crucial for maintaining the security and integrity of a database by controlling who can access and manipulate data.

• GRANT Command:

• The GRANT command in SQL is part of the Data Control Language (DCL) and is used to provide specific privileges to users or roles in a database. 
• This command enables database administrators to control access to various database objects, ensuring that only authorized users can perform certain actions.
• Purpose:

• To assign permissions to users or roles for performing actions on database objects (like tables, views, procedures, etc.).
• Enhance database security by controlling who can access and manipulate data.
• Syntax:

GRANT privilege_type ON object_name TO user_name;

• privilege_type: The specific permission being granted (e.g., SELECT, INSERT, UPDATE, DELETE).
• object_name: The name of the database object (e.g., a table or view) on which the privilege is granted.
• user_name: The user or role that will receive the privileges.
• Example:
• Grant SELECT and INSERT permissions:

GRANT SELECT, INSERT ON Employees TO user1;

• In this example, the user user1 is granted permission to both select and insert data in the Employees table.
• Grant all privileges:

GRANT ALL PRIVILEGES ON Employees TO user2;

• Here, user2 is granted all possible privileges on the Employees table, allowing them to perform any action.
• Grant permissions to a role:

GRANT SELECT ON Employees TO role_manager;

• This command grants the SELECT permission on the Employees table to a role named role_manager, allowing all users assigned to that role to select data from the table.
• Important Notes:

• Cascading Grants: If a user has been granted permissions, they can further grant those permissions to other users if they also have the appropriate privileges.
• Security Considerations: Carefully consider which permissions to grant to users to maintain data integrity and security.
• Revoking Privileges: If needed, privileges granted can be removed using the REVOKE command.

• REVOKE Command:

• The REVOKE command in SQL is part of the Data Control Language (DCL) and is used to remove specific privileges from users or roles in a database. 
• This command allows database administrators to control access to various database objects by revoking permissions previously granted.
• Purpose:

• To withdraw access rights from users or roles for performing actions on database objects.
• Enhance security by ensuring that users no longer have unnecessary or unauthorized access.
• Syntax:

REVOKE privilege_type ON object_name FROM user_name;

• privilege_type: The specific permission being revoked (e.g., SELECT, INSERT, UPDATE, DELETE).
• object_name: The name of the database object (e.g., a table or view) from which the privilege is being revoked.
• user_name: The user or role from whom the privileges are being removed.
• Example:
• Revoke a specific permission:

REVOKE INSERT ON Employees FROM user1;

• In this example, the INSERT permission for the Employees table is revoked from user1, meaning they can no longer add new records.
• Revoke multiple privileges:

REVOKE SELECT, UPDATE ON Employees FROM user2;

• Here, both SELECT and UPDATE permissions are removed from user2 for the Employees table.
• Revoke permissions from a role:

REVOKE DELETE ON Employees FROM role_manager;

• This command revokes the DELETE permission on the Employees table from the role named role_manager, affecting all users assigned to that role.
• Important Notes:

• Cascading Revokes: If a user had previously granted permissions to others, revoking those permissions from the original user may also affect the permissions granted to others.
• Security Considerations: Regularly review and manage permissions to ensure that users only have the access necessary for their roles.
• Granularity: You can revoke specific permissions without affecting other granted permissions.