Question

QUESTION 1
a. What are referential integrity constraints? Please explain with an example.

b. Explain with an example the GROUP BY/HAVING clause

Answer 1

a.

Referential integrity refers to the accuracy and consistency of data within a relationship.Relationships are those where data is linked together to two or more tables. Foreign key defined during table creation or alteration are used to acheive this relationship by referencing the primary key value of the parent table.Hence it is important to maintain the data in bothsides without any updates or alteration.If done,same is to done on the other side too.

Referential integrity requires a foreign key value which must be referenced to a valid, existing primary key in the parent table.

Example

Let us consider a table with primary key value 1,2,13.If we delete record number 13 in a primary table, we need to make sure that there’s no foreign key in any related table with the value of 13. We should only be able to delete a primary key if there are no associated records or else we would end up with an orphaned record.As a result some unwanted data may be entered to the table which will inversely effect the data integrity.

b.

GROUP BY

Group by close is used to select rows one or more tables having the same values with or without help of some functions.

Syntax:

SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 ORDER BY column1, column2;

HAVING

WHERE clause is used to place conditions on columns whereas HAVING is used if we want to place conditions on groups.We can use HAVING clause to place conditions to decide which group will be the part of final result-set. Unlike WHERE clause ,which cannot be used with the aggregate functions like SUM(), COUNT() etc.,HAVING clause,can be used along with these aggragate functions.

Syntax:

SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2;