Study resource

Read at your pace, then save it for later.

Unit-10: SQL (DCL/TCL) and Inbuilt Functions

SQL (DCL/TCL)

SQL is categorized into several sub-languages based on the type of commands. Data Control Language (DCL) and Transaction Control Language (TCL) are two important categories that manage permissions and transactions respectively.

Data Control Language (DCL)

Definition: DCL commands are used to manage permissions, rights, and other controls of the database system. They primarily deal with the authorization aspect of data.

GRANT

The GRANT command is used to give specific privileges to a user or role on database objects like tables, views, or stored procedures. These privileges can include actions such as selecting, inserting, updating, or deleting data.

Syntax:

GRANT privilege_name ON object_name TO user_or_role_name;

GRANT ALL PRIVILEGES ON object_name TO user_or_role_name;

Example:

GRANT SELECT, INSERT ON Employees TO 'john_doe'@'localhost';
GRANT UPDATE (Salary) ON Employees TO 'hr_manager';

Exam Note: GRANT is crucial for database security, allowing administrators to control who can access and manipulate data.

REVOKE

The REVOKE command is used to remove access privileges from a user or role. It is the opposite of the GRANT command and helps in maintaining database security by taking away permissions that are no longer needed or were mistakenly granted.

Syntax:

REVOKE privilege_name ON object_name FROM user_or_role_name;

REVOKE ALL PRIVILEGES ON object_name FROM user_or_role_name;

Example:

REVOKE INSERT ON Employees FROM 'john_doe'@'localhost';
REVOKE UPDATE ON Employees FROM 'hr_manager';

Common Mistake: Forgetting to specify the exact privileges or using FROM instead of TO in GRANT or vice-versa in REVOKE.

Transaction Control Language (TCL)

Definition: TCL commands are used to manage transactions in the database. Transactions are a sequence of SQL statements that are executed as a single logical unit of work. TCL commands ensure data integrity and consistency.

COMMIT

The COMMIT command is used to permanently save any changes made during the current transaction to the database. Once committed, the changes become permanent and visible to other users.

Example:

START TRANSACTION;
INSERT INTO Accounts (AccountNumber, Balance) VALUES ('12345', 1000);
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountNumber = '67890';
COMMIT;

Important Observation: Without COMMIT, changes are temporary and can be undone.

ROLLBACK

The ROLLBACK command is used to undo changes made during the current transaction. It reverts the database to the state it was in before the transaction began. This is useful when an error occurs or when changes are not intended to be permanent.

Example:

START TRANSACTION;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 101;
DELETE FROM Orders WHERE OrderID = 200;
-- An error occurs or decision to cancel changes
ROLLBACK;
-- The database will revert to its state before START TRANSACTION

Exam Note: ROLLBACK is vital for error recovery and ensuring atomicity in transactions.

SAVEPOINT

The SAVEPOINT command allows you to set a point within a transaction to which you can later roll back. This means you don't have to roll back the entire transaction but can revert to an intermediate point. This is useful for complex transactions where only a part of the work might need to be undone.

Syntax:

SAVEPOINT savepoint_name;

ROLLBACK TO savepoint_name;

Example:

START TRANSACTION;
INSERT INTO Log (Message) VALUES ('Step 1 completed');
SAVEPOINT S1;
INSERT INTO Orders (CustomerID, Amount) VALUES (1, 150);
SAVEPOINT S2;
UPDATE Customers SET TotalOrders = TotalOrders + 1 WHERE CustomerID = 1;
-- Suppose there's an issue with the customer update, but the order is fine.
ROLLBACK TO S2; -- Rolls back only the customer update, not the order insert.
-- Now, re-attempt customer update or proceed without it.
COMMIT;

Important Observation: SAVEPOINTs are cleared once a COMMIT or full ROLLBACK occurs. They are local to the current transaction.

Implementation of Aggregate Functions

Definition: Aggregate functions perform a calculation on a set of rows and return a single summary value. They are often used with the GROUP BY clause to apply the function to subsets of rows.

  • COUNT()

    Counts the number of rows in a specified column or the number of non-NULL values in a column.

    Syntax: COUNT(column_name | *)

    Example:

    SELECT COUNT(*) FROM Employees; -- Total number of employees
SELECT COUNT(DISTINCT DepartmentID) FROM Employees; -- Number of unique departments
  • SUM()

    Calculates the sum of all values in a numeric column.

    Syntax: SUM(numeric_column)

    Example:

    SELECT SUM(Salary) FROM Employees;
SELECT DepartmentID, SUM(Salary) FROM Employees GROUP BY DepartmentID;
  • AVG()

    Calculates the average of all values in a numeric column.

    Syntax: AVG(numeric_column)

    Example:

    SELECT AVG(Salary) FROM Employees;
SELECT JobTitle, AVG(Salary) FROM Employees GROUP BY JobTitle;
  • MIN()

    Retrieves the minimum value from a specified column.

    Syntax: MIN(column_name)

    Example:

    SELECT MIN(Salary) FROM Employees;
SELECT DepartmentID, MIN(HireDate) FROM Employees GROUP BY DepartmentID;
  • MAX()

    Retrieves the maximum value from a specified column.

    Syntax: MAX(column_name)

    Example:

    SELECT MAX(Salary) FROM Employees;
SELECT DepartmentID, MAX(HireDate) FROM Employees GROUP BY DepartmentID;

Exam Note: Aggregate functions ignore NULL values by default, except for COUNT(*) which counts all rows including those with NULLs in other columns.

Implementation of Inbuilt Character Functions

Definition: Character (or String) functions are used to manipulate and extract information from text strings.

  • UPPER(string)

    Converts all characters in a string to uppercase.

    Example: SELECT UPPER('hello world'); -- Returns 'HELLO WORLD'

  • LOWER(string)

    Converts all characters in a string to lowercase.

    Example: SELECT LOWER('HELLO WORLD'); -- Returns 'hello world'

  • LENGTH(string) / LEN(string)

    Returns the length of a string (number of characters).

    Example: SELECT LENGTH('SQL'); -- Returns 3 (Syntax varies: LEN() in SQL Server, LENGTH() in MySQL, PostgreSQL, Oracle)

  • SUBSTR(string, start, length) / SUBSTRING(string, start, length)

    Extracts a substring from a string, starting at a specified position and for a specified length.

    Example: SELECT SUBSTR('Database', 1, 4); -- Returns 'Data' (Syntax varies: SUBSTRING() in SQL Server, MySQL; SUBSTR() in Oracle, PostgreSQL)

  • CONCAT(string1, string2, ...)

    Concatenates (joins) two or more strings into a single string.

    Example: SELECT CONCAT('First', ' ', 'Name'); -- Returns 'First Name' (Also common: string1 || string2 in Oracle/PostgreSQL, string1 + string2 in SQL Server)

  • TRIM(string), LTRIM(string), RTRIM(string)

    Removes leading and/or trailing spaces (or specified characters) from a string.

    • TRIM(): Removes leading and trailing spaces.
    • LTRIM(): Removes leading spaces.
    • RTRIM(): Removes trailing spaces.

    Example:

    SELECT TRIM('   Hello   '); -- Returns 'Hello'
SELECT LTRIM('   World'); -- Returns 'World'
SELECT RTRIM('Hello   '); -- Returns 'Hello'
  • REPLACE(string, old_substring, new_substring)

    Replaces all occurrences of a specified substring within a string with another substring.

    Example: SELECT REPLACE('Data Base', ' ', '-'); -- Returns 'Data-Base'

Implementation of Inbuilt Numeric Functions

Definition: Numeric functions perform mathematical operations on numeric values.

  • ABS(number)

    Returns the absolute (positive) value of a number.

    Example: SELECT ABS(-10); -- Returns 10

  • ROUND(number, decimal_places)

    Rounds a number to a specified number of decimal places.

    Example: SELECT ROUND(123.456, 2); -- Returns 123.46

    Important: If decimal_places is omitted, it rounds to the nearest integer.

  • TRUNC(number, decimal_places) / TRUNCATE(number, decimal_places)

    Truncates a number to a specified number of decimal places, essentially cutting off the remaining digits without rounding.

    Example: SELECT TRUNC(123.987, 2); -- Returns 123.98 (Syntax varies: TRUNCATE() in MySQL, TRUNC() in Oracle, PostgreSQL)

  • MOD(number, divisor)

    Returns the remainder of a division operation (modulo operator).

    Example: SELECT MOD(10, 3); -- Returns 1

  • POWER(base, exponent) / POW(base, exponent)

    Raises a base number to a specified exponent.

    Example: SELECT POWER(2, 3); -- Returns 8 (2*2*2)

  • SQRT(number)

    Calculates the square root of a non-negative number.

    Example: SELECT SQRT(25); -- Returns 5

  • CEIL(number) / CEILING(number)

    Returns the smallest integer greater than or equal to the given number (rounds up).

    Example: SELECT CEIL(10.1); -- Returns 11 (CEILING() in SQL Server)

  • FLOOR(number)

    Returns the largest integer less than or equal to the given number (rounds down).

    Example: SELECT FLOOR(10.9); -- Returns 10

Implementation of Inbuilt Date & Time Functions

Definition: Date and Time functions are used to manipulate and extract information from date and time values.

  • CURRENT_TIMESTAMP / NOW() / GETDATE() / SYSDATE()

    Returns the current date and time of the database server.

    Example: SELECT CURRENT_TIMESTAMP; -- Returns current datetime (e.g., '2023-10-27 10:30:00')

  • YEAR(date), MONTH(date), DAY(date)

    Extracts the year, month, or day part from a date value.

    Example:

    SELECT YEAR('2023-10-27'); -- Returns 2023
SELECT MONTH('2023-10-27'); -- Returns 10
SELECT DAY('2023-10-27'); -- Returns 27
  • DATE_ADD(date, INTERVAL value unit) / DATEADD(unit, value, date)

    Adds a specified time interval to a date.

    Example (MySQL/PostgreSQL syntax):

    SELECT DATE_ADD('2023-01-01', INTERVAL 1 MONTH); -- Returns '2023-02-01'
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY); -- Returns current date + 7 days

    Example (SQL Server syntax):

    SELECT DATEADD(month, 1, '2023-01-01'); -- Returns '2023-02-01'
  • DATE_SUB(date, INTERVAL value unit) / DATEDIFF(unit, date, number)

    Subtracts a specified time interval from a date. (Often similar syntax to DATE_ADD or specific functions like DATEDIFF for subtracting dates to get difference in units).

    Example (MySQL/PostgreSQL syntax):

    SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY); -- Returns '2022-12-31'
  • DATEDIFF(unit, date1, date2)

    Calculates the difference between two dates in a specified unit.

    Example (SQL Server syntax - DATEDIFF(unit, start_date, end_date)):

    SELECT DATEDIFF(day, '2023-01-01', '2023-01-31'); -- Returns 30

    Example (MySQL syntax - DATEDIFF(end_date, start_date)):

    SELECT DATEDIFF('2023-01-31', '2023-01-01'); -- Returns 30
  • DATE_FORMAT(date, format_string) / TO_CHAR(date, format_string)

    Formats a date/time value into a string according to a specified format.

    Example (MySQL syntax):

    SELECT DATE_FORMAT('2023-10-27', '%Y-%m-%d'); -- Returns '2023-10-27'
SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%W, %M %D, %Y'); -- Returns 'Friday, October 27th, 2023'

    Example (Oracle/PostgreSQL TO_CHAR syntax):

    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD'); -- Returns '2023-10-27'

    Exam Note: Format strings vary significantly between database systems.

xxx

Did this help you understand better?

Your feedback improves the quality of this resource for everyone.