Unit2 - Subjective Questions

Data Definition Language (DDL) is a subset of SQL used to define, modify, and manage the structural components of a database, such as tables, schemas, and views. DDL commands auto-commit, meaning their changes are permanently saved in the database immediately.

Primary DDL Commands:

• CREATE: Used to build a new database or table.
  Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype);
• ALTER: Used to modify the structure of an existing table (e.g., adding, dropping, or modifying columns).
  Syntax: ALTER TABLE table_name ADD column_name datatype;
• DROP: Completely removes an existing database or table from the system, including its structure and data.
  Syntax: DROP TABLE table_name;
• TRUNCATE: Removes all records from a table, but leaves the table's structure (schema) intact for future data insertion.
  Syntax: TRUNCATE TABLE table_name;

Both DROP and TRUNCATE are DDL commands, but they serve different purposes:

• Purpose: TRUNCATE deletes all rows from a table but keeps the table structure intact. DROP removes the table entirely, including its structure, data, privileges, and indexes.
• Speed: TRUNCATE is faster than DROP (and DELETE) because it deallocates the data pages instead of logging individual row deletions.
• Space Recovery: TRUNCATE resets the table's identity column and reclaims the storage space. DROP frees up the space entirely back to the database.
• Reversibility: Neither command can be rolled back in most standard SQL databases once executed, as DDL operations auto-commit.
• Syntax:
  • TRUNCATE TABLE table_name;
  • DROP TABLE table_name;

The ALTER TABLE command is a DDL command used to change the structure of an existing table without dropping and recreating it.

1. Adding a Column:
You can add a new column to an existing table using the ADD clause.
Syntax: ALTER TABLE table_name ADD column_name datatype;

2. Modifying a Column:
You can change the data type or size of an existing column using the MODIFY (or ALTER COLUMN in some SQL dialects) clause.
Syntax: ALTER TABLE table_name MODIFY column_name new_datatype;

3. Dropping a Column:
You can remove an existing column from a table using the DROP COLUMN clause.
Syntax: ALTER TABLE table_name DROP COLUMN column_name;

Data Manipulation Language (DML) is a subset of SQL used for managing and manipulating data within existing schema objects. DML allows users to access, insert, modify, or delete data.

Types of DML:

1. Procedural DML:

• The user must specify exactly what data is needed and how to get it.
• It involves writing a sequence of operations (code) to retrieve the data.
• Typically used in traditional programming languages accessing databases through APIs.

2. Non-Procedural (Declarative) DML:

• The user only needs to specify what data is needed without specifying how to retrieve it.
• The Database Management System (DBMS) query optimizer figures out the most efficient execution plan.
• SQL is a prime example of a non-procedural DML (e.g., using SELECT statements).

The core DML commands are used to manipulate data within tables:

1. INSERT:
Adds one or more new rows to a table.

• Syntax: INSERT INTO table_name (col1, col2) VALUES (val1, val2);
• Example: INSERT INTO Employees (ID, Name) VALUES (101, 'Alice');

2. UPDATE:
Modifies existing data in a table. A WHERE clause is crucial to avoid updating all rows.

• Syntax: UPDATE table_name SET col1 = val1 WHERE condition;
• Example: UPDATE Employees SET Name = 'Alice Smith' WHERE ID = 101;

3. DELETE:
Removes existing rows from a table. Like UPDATE, it should usually include a WHERE clause.

• Syntax: DELETE FROM table_name WHERE condition;
• Example: DELETE FROM Employees WHERE ID = 101;

While both DELETE and TRUNCATE remove data from a table, they operate very differently:

Data Control Language (DCL) is used to control access to data within the database. It allows database administrators to implement security by granting or revoking privileges to users or roles.

Main DCL Commands:

1. GRANT:
Provides specific privileges to a user or role, allowing them to perform specified operations (like SELECT, INSERT, EXECUTE).

• Syntax: GRANT privilege_name ON object_name TO user_name;
• Example: GRANT SELECT, INSERT ON Employees TO user_john;

2. REVOKE:
Removes previously granted privileges from a user or role, restricting their access.

• Syntax: REVOKE privilege_name ON object_name FROM user_name;
• Example: REVOKE INSERT ON Employees FROM user_john;

Transaction Control Language (TCL) commands are used to manage transactions in the database. A transaction is a logical unit of work comprising one or more DML operations. TCL ensures the ACID properties (Atomicity, Consistency, Isolation, Durability) are maintained.

Primary TCL Commands:

1. COMMIT:
Permanently saves all changes made during the current transaction. Once committed, changes cannot be rolled back.

• Syntax: COMMIT;

2. ROLLBACK:
Undoes all changes made since the beginning of the transaction or since a specific savepoint, restoring the database to its previous consistent state.

• Syntax: ROLLBACK;

3. SAVEPOINT:
Creates a specific marker within a transaction. It allows a partial rollback. Instead of rolling back the entire transaction, you can roll back to a specific savepoint.

• Syntax: SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;

Database Integrity refers to the accuracy, consistency, and reliability of data stored in the database over its entire lifecycle. Integrity constraints are rules enforced on data columns to prevent invalid data entry.

Three Main Types of Integrity Constraints:

1. Domain Integrity:
Ensures that all values in a column fall within a defined set of valid values (domain). This is implemented using Data Types, CHECK constraints, and DEFAULT constraints.

2. Entity Integrity:
Ensures that each row in a table is uniquely identifiable. It dictates that a primary key must exist and that the primary key column(s) cannot contain NULL values. Let be a relation and be its primary key; then .

3. Referential Integrity:
Ensures consistency between two associated tables. It requires that a foreign key value must either match an existing primary key value in the referenced table or be NULL.

Referential Integrity is a database concept that ensures the relationships between tables remain consistent. It dictates that a foreign key in a child table must reference a valid, existing primary key (or unique key) in the parent table.

Enforcement in SQL:
Referential integrity is enforced using the FOREIGN KEY constraint.

• When a record is inserted into the child table, the DBMS checks if the foreign key value exists in the parent table.
• If a user tries to delete or update a record in the parent table that is referenced by the child table, the DBMS prevents it unless specific actions are defined.

Cascading Actions:
To handle updates or deletions in the parent table, SQL provides cascading rules:

• ON DELETE CASCADE: If a parent record is deleted, all referencing child records are also deleted.
• ON DELETE SET NULL: If a parent record is deleted, the referencing child records' foreign key is set to NULL.

1. CHECK Constraint:
The CHECK constraint is used to limit the value range that can be placed in a column. It ensures that all values in a column satisfy a specific condition or boolean expression.

• Syntax: CREATE TABLE Persons (Age INT CHECK (Age >= 18));
• Explanation: This ensures that no person under the age of 18 can be added to the Persons table.

2. DEFAULT Constraint:
The DEFAULT constraint provides a default value for a column when no value is specified during an INSERT operation. It ensures the column does not accidentally become NULL if a standard fallback value exists.

• Syntax: CREATE TABLE Orders (OrderDate DATE DEFAULT CURRENT_DATE);
• Explanation: If an order is inserted without specifying the OrderDate, the database automatically assigns the current date.

NOT NULL Constraint:

• Ensures that a column cannot have a NULL value.
• It forces a field to always contain a value, meaning you cannot insert a new record or update a record without adding a value to this field.

UNIQUE Constraint:

• Ensures that all values in a column (or a set of columns) are distinct from one another.
• No two rows can have the same value for that specific column.
• Standard SQL allows a UNIQUE column to contain NULL values (and usually multiple NULLs are allowed since NULL != NULL).

Using them together:
Yes, they can be used together. A column defined as UNIQUE NOT NULL requires that every row has a value and that every value is different. A PRIMARY KEY inherently applies both UNIQUE and NOT NULL constraints.

Let be a relational schema. Keys are sets of attributes used to uniquely identify tuples in .

1. Super Key:
A Super Key is a set of one or more attributes that can uniquely identify a row in a table. It may contain extraneous attributes. Mathematically, if is a super key, no two distinct tuples can have .

2. Candidate Key:
A Candidate Key is a minimal Super Key. It is a Super Key with no redundant attributes. If any attribute is removed from a Candidate Key, it loses its unique identification property. A table can have multiple candidate keys.

3. Primary Key:
A Primary Key is a specific Candidate Key chosen by the database designer to identify tuples in a relation uniquely. There can be only one Primary Key per table. It must satisfy Entity Integrity (cannot be NULL).

Hierarchy: Every Primary Key is a Candidate Key, and every Candidate Key is a Super Key. The reverse is not true.

Foreign Key:
A Foreign Key is a column or a set of columns in one table that refers to the Primary Key (or Unique Key) in another table. The table containing the foreign key is the child table, and the table containing the referenced key is the parent table.

Significance:

• Enforces Referential Integrity: It prevents actions that would destroy links between tables (e.g., deleting a parent record when child records still reference it).
• Establishes Relationships: It is the fundamental mechanism for linking tables together in a relational database (e.g., one-to-many relationships).

Example:
Consider two tables: Departments(DeptID, DeptName) and Employees(EmpID, EmpName, DeptID).

• DeptID is the Primary Key in Departments.
• DeptID in the Employees table is a Foreign Key referencing Departments(DeptID). This ensures every employee belongs to a valid department.

Both PRIMARY KEY and UNIQUE constraints guarantee uniqueness for a column or set of columns, but they have key differences:

1. Alternate Key:
When a table has multiple Candidate Keys, the database administrator selects one to be the Primary Key. The remaining candidate keys, which were not selected as the primary key, are known as Alternate Keys (or Secondary Keys). They still uniquely identify rows but are not the primary means of doing so.
Example: If a table has EmployeeID, Email, and SSN as candidate keys. If EmployeeID is chosen as the Primary Key, then Email and SSN become Alternate Keys.

2. Composite Key:
A Composite Key (or Compound Key) is a primary key that consists of two or more attributes (columns) that uniquely identify an entity occurrence. Individually, the attributes might not be unique, but combined, they guarantee uniqueness.
Example: In an Enrollment table, StudentID alone is not unique, and CourseID alone is not unique. However, the combination of (StudentID, CourseID) uniquely identifies a specific student's enrollment in a specific course.

Basic SQL Query Structure:
The most common SQL statement is the SELECT statement, which retrieves data from one or more tables.

• SELECT: Specifies the columns to be returned.
• FROM: Specifies the table(s) from which to retrieve the data.
• WHERE: Specifies the conditions that the records must meet to be included in the result set.

Syntax: SELECT column1, column2 FROM table_name WHERE condition;

Logical Order of Execution:
While the query is written starting with SELECT, the database engine processes it in a different order:

1. FROM: The database first identifies the working dataset (the table or joined tables).
2. WHERE: It then filters the rows based on the conditions specified. Rows evaluating to FALSE or UNKNOWN are discarded.
3. SELECT: Finally, it evaluates the SELECT list to determine which columns (and expressions) to output for the filtered rows.

Aggregate functions perform a calculation on a set of values and return a single scalar value. They are frequently used with the GROUP BY clause. The five basic SQL aggregate functions are:

1. COUNT(): Returns the number of rows that match a specified criterion. COUNT(*) counts all rows, while COUNT(column_name) counts non-NULL values in that column.
2. SUM(): Returns the total sum of a numeric column.
3. AVG(): Returns the average value of a numeric column. It ignores NULL values in the calculation.
4. MIN(): Returns the smallest (minimum) value in the selected column.
5. MAX(): Returns the largest (maximum) value in the selected column.

Example: SELECT COUNT(EmpID), AVG(Salary) FROM Employees;

GROUP BY Clause:
The GROUP BY statement groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

HAVING Clause:
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. HAVING filters the results of the GROUP BY clause.

Practical Example:
Suppose we have a table Sales(Region, Amount).
We want to find regions where the total sales amount exceeds 10,000.

sql
SELECT Region, SUM(Amount) as TotalSales
FROM Sales
GROUP BY Region
HAVING SUM(Amount) > 10000;

Explanation: The query first groups the sales by Region, calculates the SUM(Amount) for each region, and finally, the HAVING clause filters out the regions where the TotalSales is 10,000 or less.

SQL Aliases are temporary names assigned to tables or columns for the duration of a specific query. They make column names more readable or abbreviate long table names, especially in complex joins.

1. Column Alias:
Used to rename a column in the query output for better readability.

• Syntax: SELECT column_name AS alias_name FROM table_name;
• Example: SELECT Emp_First_Name AS "First Name" FROM Employees; (Quotes are used if the alias contains spaces).

2. Table Alias:
Used to give a table a short nickname, which makes queries involving joins or self-referencing much easier to write and read.

• Syntax: SELECT t.column_name FROM table_name AS t;
• Example: SELECT e.Name, d.DeptName FROM Employees e JOIN Departments d ON e.DeptID = d.DeptID; (Here, 'e' and 'd' are table aliases).