Unit2 - Subjective Questions

Data Definition Language (DDL) consists of SQL commands used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Primary DDL Commands:

1. CREATE: Used to create the database or its objects (like tables, views, indexes).
   • Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
2. ALTER: Used to modify the structure of the database.
   • Syntax: ALTER TABLE table_name ADD column_name datatype;
3. DROP: Used to delete objects from the database.
   • Syntax: DROP TABLE table_name;
4. TRUNCATE: Used to remove all records from a table, including all spaces allocated for the records are removed.
   • Syntax: TRUNCATE TABLE table_name;
5. RENAME: Used to rename an object existing in the database.
   • Syntax: RENAME TABLE old_name TO new_name;

The differences between DELETE, TRUNCATE, and DROP are primarily based on what they remove and their transaction behavior:

Data Manipulation Language (DML) is a subset of SQL used for selecting, inserting, deleting, and updating data in a database. It allows users to manipulate data without affecting the schema.

Difference between Procedural and Non-Procedural DML:

1. Procedural DML (Low-level):

   • Requires a user to specify what data is needed and how to get it.
   • Used typically in programming languages (e.g., PL/SQL, Java).
   • It operates on one record at a time.

2. Non-Procedural DML (High-level / Declarative):

   • Requires a user to specify what data is needed without specifying how to get it.
   • Standard SQL is primarily non-procedural.
   • It operates on sets of records.
   • Easier for users as the database engine optimizes the access path.

Transaction Control Language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made by DML statements. To use TCL, the database must ensure the ACID properties.

Main TCL Commands:

1. COMMIT:

   • Saves all the changes made during the current transaction permanently.
   • Example: After inserting data, running COMMIT; ensures data is written to the disk.

2. ROLLBACK:

   • Undoes the transactions that have not yet been saved to the database. It restores the database to the last committed state.
   • Example: If an error occurs during an update, ROLLBACK; reverts the changes.

3. SAVEPOINT:

   • Sets a named point within a transaction to which you can later roll back. It acts as a checkpoint.
   • Example: SAVEPOINT SP1; ... (operations) ... ROLLBACK TO SP1; (undoes operations only back to SP1).

Data Control Language (DCL) includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.

1. GRANT Command:

   • Gives user access privileges to a database.
   • Syntax: GRANT privilege_name ON object_name TO {user_name | PUBLIC | role_name} [WITH GRANT OPTION];
   • Example: GRANT SELECT, UPDATE ON Employees TO User1; allows User1 to read and update the Employees table.

2. REVOKE Command:

   • Withdraws user access privileges given by the GRANT command.
   • Syntax: REVOKE privilege_name ON object_name FROM {user_name | PUBLIC | role_name};
   • Example: REVOKE UPDATE ON Employees FROM User1; removes the ability for User1 to update the table.

A Key is an attribute or a set of attributes that helps identify a row (tuple) in a relation (table) uniquely.

1. Super Key:

   • A set of one or more attributes whose values uniquely identify each tuple in a relation.
   • If is a super key of relation , then for distinct tuples , $t_1[K]
     eq t_2[K]$.
   • Example: In Student(ID, Name, Email), {ID}, {ID, Name}, and {Email, Name} are Super Keys.

2. Candidate Key:

   • A minimal Super Key. A super key for which no proper subset is a super key.
   • If is a candidate key, then any subset is not a super key.
   • Example: {ID} and {Email} are Candidate Keys (removing any attribute makes them non-unique).

3. Primary Key:

   • A candidate key chosen by the database designer to act as the unique identifier for the table.
   • It cannot contain NULL values.
   • Selection: From the set of Candidate Keys, one is selected as the Primary Key.

Foreign Key:
A Foreign Key is a field (or collection of fields) in one table that refers to the Primary Key in another table. It establishes a link between two tables.

Referential Integrity:
This is a database concept that ensures relationships between tables remain consistent. It guarantees that if a foreign key in Table B refers to a Primary Key in Table A, then the value in Table B must either be NULL or exist in Table A.

Example:

• Table A (Department): Columns DeptID (PK), DeptName.
• Table B (Employee): Columns EmpID (PK), Name, DeptID (FK).

Here, Employee.DeptID is a foreign key referencing Department.DeptID.

• Constraint: You cannot insert an employee with DeptID = 105 if 105 does not exist in the Department table.
• Prevention: You cannot delete a department from Table A if employees in Table B are still assigned to that department (unless cascading is enabled).

Both Primary Keys and Unique Keys are used to enforce uniqueness in a column, but they have distinct differences:

Integrity constraints ensure the accuracy and consistency of data in a relational database. They are categorized as follows:

1. Domain Integrity Constraints:

   • Ensures that values in a column fall within a defined scope.
   • Examples: Data types (integer, varchar), NOT NULL, CHECK (e.g., Age > 0).

2. Entity Integrity Constraints:

   • Ensures that each row in a table is uniquely identifiable.
   • Rule: The Primary Key cannot be NULL and must be unique.

3. Referential Integrity Constraints:

   • Maintains the relationship between tables.
   • Rule: A Foreign Key value must match a Primary Key in the parent table or be NULL.

4. Key Constraints:

   • Ensures that unique keys are unique across the relation.
   • Example: UNIQUE constraint on an email column.

1. NOT NULL Constraint:

• Definition: Restricts a column from having a NULL value. It ensures that a field must always contain data.
• Usage: Useful for mandatory fields like Names, IDs, etc.
• Syntax:
  sql
  CREATE TABLE Students (
  ID int NOT NULL,
  Name varchar(255) NOT NULL
  );

2. CHECK Constraint:

• Definition: Used to limit the value range that can be placed in a column. If the condition evaluates to false, the operation fails.
• Usage: Validating age, ensuring salary is positive, etc.
• Syntax:
  sql
  CREATE TABLE Employees (
  ID int,
  Age int,
  CONSTRAINT CHK_Age CHECK (Age >= 18)
  );

Definition:
A Composite Key is a candidate key that consists of two or more attributes (columns) that, when taken together, uniquely identify a tuple in a relation.

Usage Scenario:
It is used when a single attribute is not sufficient to uniquely identify a record.

Example:
Consider a table Student_Course_Grades:

• Columns: StudentID, CourseID, Grade.
• StudentID acts as a key for the Student, but a student takes many courses.
• CourseID acts as a key for the Course, but a course has many students.
• Composite Key: {StudentID, CourseID}. The combination ensures that a student cannot have two different grades for the exact same course ID in the same table context.

Structure of SELECT Statement:
sql
SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY column_name;

Logical Order of Execution:
SQL queries are not executed in the order they are written (top to bottom), but rather in the following logical order:

1. FROM / JOIN: The database first determines the data source (tables) and performs joins.
2. WHERE: Filters rows based on specified conditions. (Cannot use aggregate aliases here).
3. GROUP BY: Groups the filtered rows based on distinct values in specified columns.
4. HAVING: Filters the groups created in step 3 (often using aggregate functions).
5. SELECT: Selects the specific columns to display.
6. DISTINCT: Removes duplicate rows if specified.
7. ORDER BY: Sorts the final result set.

SQL Set operations combine the results of two or more SELECT queries.

1. UNION ():

   • Returns all distinct rows selected by either query.
   • It removes duplicates automatically (unless UNION ALL is used).
   • Math:

2. INTERSECT ():

   • Returns only rows that are common to both queries.
   • Math:

3. EXCEPT / MINUS ():

   • Returns distinct rows from the first query that are not present in the second query.
   • Math: $R_1 - R_2 = {t \mid t \in R_1 \land t
     otin R_2}$

Requirements: Both queries must have the same number of columns, and corresponding columns must have compatible data types.

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause.

Common Aggregate Functions:

1. COUNT(): Returns the number of rows.
   • SELECT COUNT(*) FROM Students;
2. SUM(): Returns the total sum of a numeric column.
   • SELECT SUM(Salary) FROM Employees;
3. AVG(): Returns the average value of a numeric column.
   • SELECT AVG(Marks) FROM Results;
4. MIN(): Returns the smallest value of the selected column.
   • SELECT MIN(Price) FROM Products;
5. MAX(): Returns the largest value of the selected column.
   • SELECT MAX(Price) FROM Products;

Note: Aggregate functions ignore NULL values (except `COUNT()`).*

Both clauses are used to filter records, but they operate at different stages of query processing.

Joins are used to combine rows from two or more tables based on a related column between them.

1. INNER JOIN:

   • Returns records that have matching values in both tables.
   • If a row in Table A has no match in Table B, it is excluded.

2. LEFT (OUTER) JOIN:

   • Returns all records from the left table (Table A), and the matched records from the right table (Table B).
   • The result is NULL from the right side if there is no match.

3. RIGHT (OUTER) JOIN:

   • Returns all records from the right table (Table B), and the matched records from the left table (Table A).
   • The result is NULL from the left side if there is no match.

4. FULL (OUTER) JOIN:

   • Returns all records when there is a match in either left or right table.
   • Ideally, it combines the result of both Left and Right joins. It contains all rows from both tables, with NULLs in places where no match was found.

Pattern Matching allows searching for specific patterns within a string column rather than exact matches.

LIKE Operator:
It is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters:

1. Percent sign (%): Represents zero, one, or multiple characters.
   • 'a%': Starts with 'a'.
   • '%a': Ends with 'a'.
   • '%or%': Contains 'or' anywhere.
2. Underscore (_): Represents a single character.
   • '_r%': Second character is 'r'.
   • 'a__%': Starts with 'a' and is at least 3 characters long.

Example: SELECT * FROM Customers WHERE Name LIKE 'J_n%'; (Matches Jan, Jon, Jenny, etc.)

The ORDER BY clause is used to sort the result-set in ascending or descending order.

Default Behavior:
By default, it sorts records in ascending order (ASC). To sort in descending order, the DESC keyword is used.

1. Single Column Sorting:

• Syntax: SELECT * FROM Table ORDER BY Column1 ASC;

2. Multiple Column Sorting:

• SQL sorts by the first column specified. If there are duplicate values in that column, it then uses the second column to sort those specific rows.
• Example: SELECT * FROM Employees ORDER BY Department ASC, Salary DESC;
  • This sorts employees alphabetically by Department first.
  • Within the same Department, employees are sorted by Salary from highest to lowest.

The ALTER TABLE command is a DDL operation used to add, delete, or modify columns in an existing table.

1. ADD Column:

   • Adds a new column to the table.
   • Syntax: ALTER TABLE table_name ADD column_name datatype;

2. DROP Column:

   • Deletes a column from the table.
   • Syntax: ALTER TABLE table_name DROP COLUMN column_name;

3. MODIFY / ALTER Column:

   • Changes the data type or constraint of an existing column.
   • Syntax (SQL Server): ALTER TABLE table_name ALTER COLUMN column_name new_datatype;
   • Syntax (Oracle/MySQL): ALTER TABLE table_name MODIFY column_name new_datatype;

Alternate Key Concept:

1. Relation to Candidate Keys:

   • A table may have multiple Candidate Keys (minimal sets of attributes that uniquely identify a row).
   • The database designer selects one of these Candidate Keys to be the Primary Key.

2. Definition of Alternate Key:

   • All other Candidate Keys that were not chosen as the Primary Key are called Alternate Keys (or Secondary Keys).

Example:

• Table: Employee(EmpID, Email, SSN, Name)
• Candidate Keys: {EmpID}, {Email}, {SSN} (assuming all are unique).
• If {EmpID} is selected as the Primary Key.
• Then {Email} and {SSN} become the Alternate Keys.