Unit 5: Introduction to SQL - Subjective Questions

SQL (Structured Query Language) is a standard language used to create, access, manipulate, and control data in a relational database management system (RDBMS).

Main features of SQL:

• It uses simple, declarative statements to interact with databases.
• It supports the creation and modification of database structures.
• It can insert, update, delete, and retrieve records.
• It provides commands for access control and transaction management.
• It supports filtering, sorting, grouping, joins, and aggregate calculations.

SQL acts as an interface between users or applications and an RDBMS. Instead of describing every processing step, a user specifies the required result, and the DBMS determines how to produce it.

Data Definition Language (DDL) commands define or modify the structure of database objects.

• CREATE: Creates a new object. Example: CREATE TABLE Student (ID INT, Name VARCHAR(50));
• ALTER: Changes an existing object's structure. Example: ALTER TABLE Student ADD Email VARCHAR(100);
• TRUNCATE: Removes all rows while retaining the table structure. Example: TRUNCATE TABLE Student;
• DROP: Removes an object, including its structure and data. Example: DROP TABLE Student;
• RENAME: Changes an object's name. Example: ALTER TABLE Student RENAME TO Learner;

DDL operations affect the database schema. Depending on the DBMS, many DDL commands perform an implicit commit and therefore cannot be rolled back in the same way as ordinary DML operations.

Data Manipulation Language (DML) commands retrieve or modify the records stored in database tables.

• INSERT: Adds new rows: INSERT INTO Student (ID, Name) VALUES (1, 'Asha');
• UPDATE: Modifies existing rows: UPDATE Student SET Name = 'Anita' WHERE ID = 1;
• DELETE: Removes selected rows: DELETE FROM Student WHERE ID = 1;
• SELECT: Retrieves data: SELECT ID, Name FROM Student;

INSERT, UPDATE, and DELETE change table data, whereas SELECT reads it. A WHERE condition is especially important with UPDATE and DELETE; without it, every row may be affected. In some classifications, SELECT is placed in a separate category called Data Query Language (DQL).

Data Control Language (DCL) controls users' permissions on database objects.

• GRANT: Gives one or more privileges to a user or role. Example: GRANT SELECT, INSERT ON Student TO teacher_role;
• REVOKE: Removes privileges that were previously granted. Example: REVOKE INSERT ON Student FROM teacher_role;

Common privileges include SELECT, INSERT, UPDATE, DELETE, and EXECUTE. DCL supports the principle of least privilege, under which users receive only the permissions necessary for their work. Roles can be used to manage the same set of privileges for multiple users efficiently.

Transaction Control Language (TCL) manages a transaction, which is a logical group of database operations treated as one unit.

• COMMIT: Permanently saves changes made by the transaction.
• ROLLBACK: Cancels uncommitted changes.
• SAVEPOINT: Establishes an intermediate point to which a transaction can be rolled back.

Example sequence:

1. UPDATE Account SET Balance = Balance - 500 WHERE AccountID = 1;
2. SAVEPOINT debit_done;
3. UPDATE Account SET Balance = Balance + 500 WHERE AccountID = 2;
4. If both operations are valid, execute COMMIT;.
5. If the second operation fails, execute ROLLBACK TO debit_done; and then handle or reverse the remaining work as required.

TCL helps preserve consistency by ensuring that related modifications are either saved appropriately or undone before they leave the database in an invalid state.

The four SQL command categories differ as follows:

• DDL: Defines database structures. It affects schema objects such as tables and views. Examples: CREATE, ALTER, TRUNCATE, and DROP.
• DML: Retrieves or modifies table records. Examples: SELECT, INSERT, UPDATE, and DELETE.
• DCL: Controls authorization and privileges. Examples: GRANT and REVOKE.
• TCL: Controls transaction boundaries and uncommitted changes. Examples: COMMIT, ROLLBACK, and SAVEPOINT.

For example, creating an Employee table is DDL, adding an employee is DML, allowing a user to read that table is DCL, and permanently saving an update is TCL. Thus, the categories respectively manage structure, data, security, and transactions.

A key is an attribute or a set of attributes used to identify records, enforce uniqueness, or establish relationships.

• Super key: Any attribute set that uniquely identifies a row, possibly containing unnecessary attributes.
• Candidate key: A minimal super key; none of its attributes can be removed without losing uniqueness.
• Primary key: The candidate key selected as the main row identifier. It must be unique and non-null.
• Alternate key: A candidate key not selected as the primary key.
• Foreign key: An attribute set that references a candidate or primary key in another table, enforcing referential integrity.
• Composite key: A key formed from two or more attributes.
• Unique key: A constraint that prevents duplicate values; treatment of NULL values depends on the DBMS.

Keys maintain entity integrity, prevent unwanted duplication, and connect related tables.

Suitable keys may be identified as follows:

• In Student, StudentID should be the primary key because it uniquely and consistently identifies each student.
• If every email address is distinct, Email is a candidate key. When StudentID is selected as the primary key, Email becomes an alternate key and can have a UNIQUE constraint.
• In Enrollment, (StudentID, CourseID, Semester) can form a composite primary key because the combination uniquely identifies a student's enrollment in a course during a particular semester.
• Enrollment.StudentID is a foreign key referencing Student.StudentID.
• CourseID would normally be a foreign key referencing the primary key of a Course table.

These constraints prevent duplicate enrollments and stop enrollment rows from referring to nonexistent students or courses.

Aggregate functions calculate one result from a collection of rows.

• MIN(column): Returns the smallest value: SELECT MIN(Salary) FROM Employee;
• MAX(column): Returns the largest value: SELECT MAX(Salary) FROM Employee;
• SUM(column): Returns the total of numeric values: SELECT SUM(Salary) FROM Employee;
• AVG(column): Returns the arithmetic mean: SELECT AVG(Salary) FROM Employee;
• COUNT(column): Counts non-null values: SELECT COUNT(Email) FROM Employee;
• COUNT(*): Counts rows, regardless of null values in individual columns: SELECT COUNT(*) FROM Employee;

These functions can be applied to all selected rows or separately to groups created with GROUP BY.

Most aggregate functions ignore NULL values.

• MIN(Salary), MAX(Salary), SUM(Salary), and AVG(Salary) operate only on non-null salaries.
• COUNT(*) counts every selected row, even if one or more columns contain NULL.
• COUNT(column) counts only rows in which that column is not NULL.
• COUNT(DISTINCT column) counts distinct non-null values in the column.

For salaries 1000, 2000, 2000, and NULL:

• COUNT(*) is .
• COUNT(Salary) is .
• COUNT(DISTINCT Salary) is .
• AVG(Salary) is , not .

COALESCE may be used when a null value must be replaced explicitly, but doing so can change the meaning of the calculation.

The GROUP BY clause divides selected rows into groups that share the same value in one or more columns. Aggregate functions then produce one result per group.

Example:

SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AverageSalary FROM Employee GROUP BY DepartmentID;

Important rules:

• Every selected expression that is not aggregated should normally appear in GROUP BY.
• Rows with the same grouping value contribute to the same result group.
• Multiple columns can be used to create more specific groups.
• WHERE filters individual rows before grouping.
• HAVING filters complete groups after aggregation.

The example returns one row for each department, together with its employee count and average salary.

WHERE filters individual rows before grouping and aggregation, whereas HAVING filters groups after GROUP BY has calculated aggregate results.

Required query:

SELECT DepartmentID, COUNT(*) AS EmployeeCount FROM Employee WHERE Salary >= 30000 GROUP BY DepartmentID HAVING COUNT(*) > 5;

Processing occurs conceptually as follows:

1. WHERE Salary >= 30000 removes employees below the salary threshold.
2. GROUP BY DepartmentID groups the remaining employees.
3. COUNT(*) calculates each group's size.
4. HAVING COUNT(*) > 5 retains only groups with more than five qualifying employees.

Aggregate conditions usually belong in HAVING, so WHERE COUNT(*) > 5 would be invalid.

The ORDER BY clause arranges the final result set in ascending or descending order.

• ASC specifies ascending order and is the default.
• DESC specifies descending order.
• Multiple sort columns are evaluated from left to right.

Example of sorting employees by department and then by decreasing salary:

SELECT Name, DepartmentID, Salary FROM Employee ORDER BY DepartmentID ASC, Salary DESC;

Example of sorting by an aggregate alias:

SELECT DepartmentID, AVG(Salary) AS AverageSalary FROM Employee GROUP BY DepartmentID ORDER BY AverageSalary DESC;

The second query displays departments from the highest to the lowest average salary. The exact placement of NULL values may vary by DBMS unless explicitly controlled.

A self join joins a table to itself. It is useful when rows in one table refer to other rows in the same table, such as employees and their managers.

Assume Employee(EmployeeID, Name, ManagerID), where ManagerID references EmployeeID.

SELECT e.Name AS EmployeeName, m.Name AS ManagerName FROM Employee AS e LEFT JOIN Employee AS m ON e.ManagerID = m.EmployeeID;

Here:

• e represents an employee row.
• m represents the matching manager row from the same table.
• Aliases are necessary to distinguish the two logical copies.
• LEFT JOIN retains top-level employees whose ManagerID is NULL; their manager name appears as NULL.

A self join is a usage pattern rather than a separate SQL keyword.

An equi join combines rows by using the equality operator (=) in the join condition.

Assume that both tables contain DepartmentID:

SELECT e.EmployeeID, e.Name, d.DepartmentName FROM Employee AS e JOIN Department AS d ON e.DepartmentID = d.DepartmentID;

This returns employees whose department identifier matches a department row. An older form is:

SELECT e.EmployeeID, e.Name, d.DepartmentName FROM Employee AS e, Department AS d WHERE e.DepartmentID = d.DepartmentID;

If SELECT * is used with an ON-based join, both copies of DepartmentID may appear in the result. Listing required columns explicitly avoids unnecessary duplication. An equi join describes the comparison used; it is often implemented as an inner join, though equality can also appear in an outer join condition.

An inner join returns only row combinations that satisfy the specified join condition.

Example:

SELECT e.Name, d.DepartmentName FROM Employee AS e INNER JOIN Department AS d ON e.DepartmentID = d.DepartmentID;

If an employee has no matching department, that employee is omitted. Similarly, a department with no matching employee does not appear.

An inner join differs from a Cartesian product because it uses a condition to retain meaningful matches. A Cartesian product combines every row from the first table with every row from the second. If the tables contain and rows, the product contains rows. Omitting the join condition accidentally can therefore create a very large and usually incorrect result.

An outer join returns matching rows and also preserves certain unmatched rows.

• Left outer join: Returns every row from the left table and matching rows from the right table. Right-side columns are NULL when no match exists.
• Right outer join: Returns every row from the right table and matching rows from the left table. Left-side columns are NULL when no match exists.
• Full outer join: Returns all matched rows and all unmatched rows from both tables. Missing values from either side are represented by NULL.

Examples include Employee LEFT JOIN Department, Employee RIGHT JOIN Department, and Employee FULL OUTER JOIN Department, each with an appropriate ON condition. Outer joins are useful for locating missing relationships, such as employees without valid departments or departments without employees. Some DBMS products do not directly support FULL OUTER JOIN, so an equivalent union-based query may be required.

A cross join returns the Cartesian product of two tables: every row of the first table is paired with every row of the second.

Syntax:

SELECT * FROM Colour CROSS JOIN Size;

If Colour contains rows and Size contains rows, each of the colour rows is paired with all size rows. Therefore, the number of result rows is:

For example, colours and sizes produce combinations.

Practical uses:

• Generating all possible product variants, such as every colour-size combination.
• Producing schedules or test data from all combinations of two sets.

A cross join should be used carefully because large input tables can produce an extremely large result.

The joins describe different aspects of a query:

• Self join: Describes the tables involved—the same table is referenced more than once.
• Equi join: Describes the condition—the comparison uses equality.
• Inner join: Describes row preservation—only matching combinations are returned.
• Outer join: Preserves unmatched rows from the left table, right table, or both.
• Cross join: Produces every possible row combination without a matching condition.

These terms are not fully exclusive. For example, an employee-manager query can be a self left outer join because the table joins to itself while preserving employees without managers. An ordinary department query can be both an equi join and an inner join because it uses equality and returns only matches.

Thus, some names describe the join's participants, others its comparison operator, and others its treatment of unmatched rows.

A suitable query is:

SELECT d.DepartmentID, d.DepartmentName, MIN(e.Salary) AS MinimumSalary, MAX(e.Salary) AS MaximumSalary, SUM(e.Salary) AS TotalSalary, AVG(e.Salary) AS AverageSalary, COUNT(*) AS EmployeeCount FROM Department AS d INNER JOIN Employee AS e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentID, d.DepartmentName HAVING COUNT(*) >= 3 ORDER BY TotalSalary DESC;

Explanation:

• INNER JOIN keeps departments having matching employee records.
• MIN and MAX find the salary range in each department.
• SUM calculates the departmental salary total.
• AVG calculates the mean salary.
• COUNT(*) counts employees in each group.
• GROUP BY creates one group for each department.
• HAVING COUNT(*) >= 3 removes departments with fewer than three employees.
• ORDER BY TotalSalary DESC displays the highest salary total first.

Grouping by both the identifier and name clearly associates every aggregate result with its department.