SQL Query

The practice of normalization improves data integrity in the table by removing redundant data and duplication. Additionally, normalization aids in database data organization. Setting the data into tabular form and removing duplicate data from relational tables involves several steps. n order to make sure that restrictions on database integrity effectively carry out their obligations, normalization arranges the columns and tables of a database. It is a methodical method of breaking down tables to eliminate redundant data and undesired traits like Insertion, Update, and Deletion anomalies. The different normal forms are: 

• 1st Normal Form (1NF) 
• 2nd Normal Form (2NF) 
• 3rd Normal Form (3NF) 
• Boyce CoddNormal Form (BCNF) 

When assessing relational databases and application architectures, database professionals typically check for the ACID (Atomicity Consistency Isolation Durability) principle. All four of these qualities must be present for a database to be regarded as robust. 

• Atomicity in SQL ensures that while running a block of SQL code, either all logical statements (CREATE, INSERT, ALTER, DELETE, etc.) are executed successfully or they are rolled back. 
• Consistency ensures that whatever operation is being performed in a database, it will not leave the database in an incomplete state. 
• Isolation applies to individual transactions. It ensures that one transaction does not access the results of the other transactions unless its execution is not completed successfully. 
• Durability ensures that the database will maintain track of pending changes so that committed transactions won't be lost and the server can recover from an abnormal termination. 

A trigger is a SQL process that starts doing an action if an object experiences an event such as an INSERT, DELETE, or UPDATE. The DBMS stores and maintains triggers. DBMS automatically fires a trigger as a result of a data alteration to the related table; a trigger cannot be called or executed directly. Triggers are primarily used to ensure improved performance for defining rules in a database and easier maintenance, as the triggers can be reused by each of the applications that use the database. 

The syntax for trigger in MySQL is – 

CREATE [ OR ALTER ] TRIGGER $trigger_name 
[ BEFORE | AFTER ] { INSERT | UPDATE | DELETE } 
ON $table_name FOR EACH ROW 
{ $trigger_body } 

For example, let us consider a ‘Sales’ table where we have the following columns: 

• Sale_ID 
• Timestamp 
• Product_ID 
• Quantity 
• Unit_Cost 
• Total_Cost 

For every entry that is made, we will add a SQL Trigger which will calculate the value for the ‘Total_Cost’ column using the ‘Quantity’ and ‘Unit_Cost’ columns and then update in the ‘Sales’ table. The SQL trigger is given by – 

CREATE TRIGGER trg_product_cost 
BEFORE INSERT 
ON Sales FOR EACH ROW 
SET Sales.Total_Cost = Sales.Quantity * Sales.Unit_Cost; 

Now every time an INSERT SQL statement is being run on the server, it will calculate the total cost for the respective entry and fill the value in the table along with other fields. 

SQL Injection is a technique that exposes your database to vulnerabilities by hackers. It is a way in which hackers try to run a SQL statement like ALTER, DELETE, TRUNCATE, etc that might cause your database to be compromised. The most common way of doing it is by placing a malicious SQL statement instead of a user input variable that the original SQL considers. There are different ways of protecting SQL injection within your code by the following standard practices to avoid such attacks. SQLAlchemy in Python is known to handle such malicious SQL injection attacks using SQL parameters.

1. One-to-One Relationship
   The relationship between two tables where each record form one table is associated with a single record from the other table.
   For example, an `employee_id` from the employee table will be associated to a single salary record in the salary table. 
2. One-to-Many Relationship
   The relationship between two tables where each record from one table is associated with more than one record from the other table.
   For example, an `employee_id` from the employee table can be associated to more than one `project_id` from the project table. 
3. Many-to-One Relationship
   The relationship between two tables where multiple records from one table is associated with a single record from the other table.
   For example, more than one `project_id` from the project table can be associated to a single `employee_id` from the employee table. 
4. Many-to-Many Relationship
   The relationship between two tables where multiple records from one table is associated with multiple records from the other table.
   For example, an `employee_id` from the employee table can register for multiple `event_id` from the events table and similarly one `event_id` can be registered by more than one `employee_id` from the employee table. 
5. Self-Referencing Relationship
   The relationship of a table with itself.
   For example, an employee table that contains information about the managers of each employee.

GRANT and REVOKE commands in SQL are used to enforce security in the database where multiple users are involved. The commands are a part of the Data Control Language and can provide only the required set of permissions to a user for running a query. 

For example, 

GRANT SELECT ON employee TO temp_user; 

The above command grants `temp_user` the permission to run SELECT queries on the employee table. 

REVOKE SELECT ON employee TO temp_user; 

The above command revokes the granted permission to `temp_user` to run SELECT queries on the employee table. 

GRANT ALL PRIVILEGES ON organization.* TO temp_user; 

The above command provides `temp_user` with the permission to run any query, modify or alter the database, or even add tables and records for all the tables present in the organization database. 

The GROUP BY clause is used in the SELECT statement. The aggregate functions that generate summary values for each of those sets are most frequently used with this clause. GROUP BY clause-containing queries are referred to as grouped queries because they only return a single result for each grouped item. One or more columns may be used to organize the groups. For instance, the GROUP BY query will be used to calculate the total salary for each department or to count the number of employees in each department. In the SELECT query, you must use aggregate functions like MAX(), MIN(), COUNT(), SUM(), AVG(), etc. For example, to get the department-wise average salary of employees where the average salary is more than 5 lakh, we can use –

SELECT 
Dept_ID, AVG(Salary) 
FROM employees 
GROUP BY Dept_ID 
HAVING AVG(Salary) > 500000; 

DELETE FROM $table_name 
WHERE row_id NOT IN ( 
SELECT MAX(row_id) AS max_row_id 
FROM $table_name 
GROUP BY $column_1, $column_2, … $column_n 
); 

The query will keep only the latest records in the table which represents the unique record based on the columns provided in the GROUP BY clause. 

We can achieve this using window partition functions. In MySQL, we can write the following query:

SELECT *
FROM (
    SELECT
        *,
        RANK() OVER (PARTITION BY Subject, ORDER BY Score DESC) rn
        FROM Student
    ) t
WHERE rn = 1;

While applying a filter to a string column, wildcards can be useful. They are used along with the LIKE operator. Wildcards help to substitute one or more characters in a string. We can define a string format using patterns, for example, email ID. They can be considered similar to regular expressions used in other programming languages. Following are some of the common wildcards used – 

In SQL, we can prepare code that may be saved and reused repeatedly, known as a stored procedure. So, if you frequently develop SQL queries, save them as stored procedures and just call them to run them. Additionally, you can send parameters to a stored procedure, allowing it to act based on the value(s) of the passed parameters. The syntax to create a SQL procedure is – 

CREATE PROCEDURE $procedure_name 
AS 
{ sql statements } 
GO; 

To execute or run the procedure – 

EXEC $procedure_name; 

For example, the following SQL stored procedure, ShowDepartmentWiseEmployees, takes in to account a department and displays the employee details belonging to only the respective department. 

CREATE PROCEDURE 
ShowDepartmentWiseEmployees @Department VARCHAR(128) 
AS 
SELECT * FROM Employees WHERE Department = @Department 
GO; 

After creating this procedure, we can use the following query to get the desired output – 

EXEC ShowDepartmentWiseEmployees @Department = ‘Human Resource’; 

The SELECT INTO statement is used to copy data from one table to another. The syntax is given by – 

SELECT * 
INTO $schema_name.$table_name 
FROM $schema_name.$table_name 
WHERE condition(s); 

The purpose of DDL language is to define the data structure of the database which includes – 

1. Create database, schema or table. 
2. Alter the tables and table fields. 
3. Drop the database, schema, table, and table fields. 
4. Truncate a table. 

The purpose of DML language is to perform any manipulation in the database which includes – 

1. Select the data from the database. 
2. Insert new records in the table. 
3. Update any existing record in the table. 
4. Delete the table records. 

The purpose of DCL language is to control user permissions in a database like – 

1. Grant permission to a user to access the database or revoke permission of an already existing user. 
2. Grant user-restricted permission like read-only, read and write by allowing the user to run only specific SQL commands on the database. 
3. Revoke any existing set of rules or permission granted to a user. 

The purpose of TCL language is to manage transactions in a database where COMMIT is used to permanently save changes made by the current transaction and ROLLBACK to undo the changes made by the current transaction. For example, the usage of TCL commands can be noticed in digital payments where the payee can reverse a transaction.

DELETE and TRUNCATE commands are used to delete records from a table without altering the table schema. 

• DELETE command can be used with the WHERE clause to mention a condition or filter while deleting these records. With TRUNCATE, all the records in the respective table get deleted. Filters cannot be provided with the TRUNCATE command as it does not allow the use of the WHERE clause.  
• Due to this behaviour, the DELETE command is a part of the Data Manipulation Language (DML) and the TRUNCATE command is categorized as Data Definition Language (DDL). 
• DELETE command maintains an entry for each record in the transaction log due to which it can be rolled back and is slower. In contrast, the TRUNCATE command maintains only the page deallocations in the transaction log which makes it a faster operation as compared to deleting and not possible to rollback. 
• For a database user to use the DELETE command requires to DELETE permission on the table whereas to use the TRUNCATE command requires ALTER permission on the table. 

Constraints in SQL are used to enforce some set of rules for the table fields and records while creating a table. This set of rules helps in automated data validation and avoids irrelevant data from being stored in these tables. These constraints can be categorised into column-level constraints and table-level constraints. The column-level constraints enforce the set of rules on a single column whereas the table-level constraints apply to the entire table. 

Some of the most used constraints are PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, AUTO_INCREMENT, etc. 

A NOT NULL constraint would prevent null entries in a certain column. For example, the birth date field in birth records cannot be null. A column can only contain values that fall under the scope of a CHECK constraint. A student percentage field, for instance, may only accept values in a specified range. If a column's value appears in another table row, a UNIQUE constraint will throw an error. This ensures that none of the values in a column are repeated. For example, we might not want to see any repeated value for the mobile number field in a table. 

CREATE TABLE knowledgehut_blogs ( 
blog_id TEXT PRIMARY KEY 
blog_title TEXT NOT NULL 
page_id INT UNIQUE 
); 

In the above example, the “knowledgehut_blogs” table consists of a PRIMARY KEY constraint to uniquely identify a record, a NOT NULL constraint to avoid blank entries for blog titles, and a UNIQUE constraint to avoid duplication of webpage IDs. 

Both WHERE and HAVING clauses can contain search conditions to further filter the rows selected. The major difference between both of them is that HAVING can be used with aggregate functions like AVG, SUM, MIN, MAX, etc. whereas WHERE does not support them. Also, the HAVING clause requires the GROUP BY clause to be present (due to aggregation) to work. WHERE condition is used to filter individual records based on one or more conditions.

String manipulation functions are useful to alter values in the fields containing string datatypes. Some of the common string manipulation functions in SQL and their examples are – 

1. CONCAT(string1, string2, … stringN) 

Used to concatenate two or more string values. 

SELECT CONCAT("Welcome ", "to ", "KnowledgeHut"); 

Output: Welcome to KnowledgeHut 

2. SUBSTR(string, start, length) 

Used to select a substring starting at the nth position in string up to a length x. 

SELECT SUBSTR('Welcome to KnowledgeHut', 12, 12); 

Output: KnowledgeHut 

3. LENGTH(string) 

Calculates the length of the string. 

SELECT LENGTH('KnowledgeHut'); 

Output: 12 

4. INSTR(search string, search term) 

Returns the index position of the specified character or word. 

SELECT INSTR('Welcome to KnowledgeHut', 'KnowledgeHut'); 

Output: 12 

5. LPAD(string, length, character) 

Adds padding to the left side of the string to fill up the required blank spaces up to the specified length of the string. 

SELECT LPAD('98345', 10, 'X'); 

Output: XXXXX98345 

6. RPAD(string, length, character) 

Adds padding to the right side of the string to fill up the required blank spaces to the specified length of the string. 

SELECT RPAD('98345', 10, 'X'); 

Output: 98345XXXXX 

7. REPLACE(string, replace, replace_with) 

Replaces the occurrence of a string of characters with a specified value. 

SELECT REPLACE('Welcome to KnowledgeHut', 'KnowledgeHut', 'upGrad KnowledgeHut'); 

Output: Welcome to upGrad KnowledgeHut 

CASE statements in SQL are similar to using conditional statements in other programming languages. It considers more than one condition and defines the action that needs to be performed when a condition is met. The syntax for the SQL case statements is –

CASE
    WHEN [condition_1] THEN [result_1]
    WHEN [condition_2] THEN [result_2]
    ...
    WHEN [condition_N] THEN [result_N]
    ELSE [result_default]
END;

Consider you are working with a student results table and grade the students based on their percentage, we can follow the below CASE statement –

SELECT
    Student_ID,
    Student_Name,
    Percentage,
CASE
    WHEN Percentage > 90 THEN ‘A’
    WHEN Percentage > 75 THEN ‘B’
    WHEN Percentage > 50 THEN ‘C’
    WHEN Percentage > 35 THEN ‘D’
    ELSE ‘Fail’
END AS Grade
FROM Results;