SQL Foreign Keys: Referential Integrity and Data Integrity in MySQL, Study notes of Database Management Systems (DBMS)

Download SQL Foreign Keys: Referential Integrity and Data Integrity in MySQL and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

✓ Integrity Constraints

✓ Null Values

✓ Aggregate Functions ✓ Set Operations

• Union
• Union All
• Intersect
• Minus The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

Example: ID NAME 1 Jack 2 Harry 3 Jackson

The First table

ID NAME

3 Jackson 4 Stephan 5 David

The Second table

Union SQL query will be: SELECT * FROM First UNION SELECT * FROM Second;

The resultset table will look like:

ID NAME

1 Jack 2 Harry 3 Jackson 4 Stephan 5 David

2. Union All Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting the data. Syntax: SELECT column_name FROM table UNION ALL SELECT column_name FROM table2;

Union All query will be like:

SELECT * FROM First UNION ALL SELECT * FROM Second;

The resultset table will look like:

ID NAME

1 Jack 2 Harry 3 Jackson 3 Jackson 4 Stephan 5 David

4. Minus

• It combines the result of two SELECT statements. Minus operator is used to display the

rows which are present in the first query but absent in the second query.

• It has no duplicates and data arranged in ascending order by default. Syntax: SELECT column_name FROM table MINUS SELECT column_name FROM table2; Example Minus query will be: SELECT * FROM First MINUS SELECT * FROM Second; The resultset table will look like: ID NAME 1 Jack 2 Harry

Operator Returns UNION All distinct rows selected by either query UNION ALL All rows selected by either query, including all duplicates INTERSECT All distinct rows selected by both queries MINUS All distinct rows selected by the first query but not the second

Difference between the COMMIT and ROLLBACK Comparison Based on Parameters

COMMIT Statement ROLLBACK Statement

Definition/ Basic A COMMIT statement is used to save the changes on the current transaction is permanent. A Rollback statement is used to undo all the changes made on the current transaction. Transaction condition Once the current transaction is completely executed using the COMMIT command, it can't undo its previous state. Whereas in the Rollback statement, once the current transaction is successfully executed, it can reach its previous state using the ROLLBACK command. Syntax of Statement Commit; Rollback; Occurrence The COMMIT statement is applied when the transaction is completed. The Rollback statement occurs when the transaction is either aborted, power failure, or incorrect execution of system failure. Successfully executed the statement. If all the statements are executed successfully without any error, the COMMIT statement will permanently save the state. If any operations fail during the completion of a transaction, it shows all the changes have not been successfully executed, and we can undo them using the ROLLBACK statement. Visible change When we perform the commit command, the current transaction statement becomes permanent and visible to all users. Whereas the rollback command is also visible to all users, even the current transaction may contain the wrong or right information.

What is a NULL Value?

➢ A field with a NULL value is a field with

no value.

➢ If a field in a table is optional, it is

possible to insert a new record or update a

record without adding a value to this field.

Then, the field will be saved with a NULL

value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

The IS NULL Operator

• The IS NULL operator is used to test for empty values (NULL values). The following SQL lists all customers with a NULL value in the "Address" field: Example : SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL; Try The IS NOT NULL Operator
• The IS NOT NULL operator is used to test for non-empty values (NOT NULL values). The following SQL lists all customers with a value in the "Address" field:

Example :

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;

SQL Aggregate Functions

• SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
• It is also used to summarize the data. Types of SQL Aggregation Function

Example for COUNT() with DISTINCT SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST; Example for COUNT() with GROUP BY SELECT COMPANY, COUNT() FROM PRODUCT_MAST GROUP BY COMPANY; Example for COUNT() with HAVING SELECT COMPANY, COUNT() FROM PRODUCT_MAST GROUP BY COMPANY HAVING COUNT(*)>2;

SELECT COUNT(*) FROM PRODUCT_MAST WHERE RATE>=20;

Example for COUNT with WHERE

1. SUM()

SELECT SUM(COST) FROM PRODUCT_MAST;

2. SUM() with WHERE SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3; 3. SUM() with GROUP BY SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3 GROUP BY COMPANY; 4. SUM() with HAVING SELECT COMPANY, SUM(COST) FROM PRODUCT_MAST GROUP BY COMPANY HAVING SUM(COST)>=170; 2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on

numeric fields only.

SUM()

or

SUM( [ALL|DISTINCT] expression )

Syntax : Example:

4.MAX Function MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column. Syntax

MAX()

or MAX( [ALL|DISTINCT] expression ) Example: (^) SELECT MAX(RATE) FROM PRODUCT_MAST;

5. MIN Function MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column. Syntax

MIN()

or MIN( [ALL|DISTINCT] expression ) Example: SELECT MIN(RATE) FROM PRODUCT_MAST;

Integrity Constraints

• Integrity constraints are a set of rules. It is used to maintain the quality of

information.

• Integrity constraints ensure that the data insertion, updating, and other

processes have to be performed in such a way that data integrity is not affected.

• Thus, integrity constraint is used to guard against accidental damage to the

database.

Types of Integrity Constraint