Question

Subject: Database

Notes: Please give me detailed answers so I could learn from this. Thank you.

PS: This is a homework that I already submitted but did not know whether my answer is right or wrong.

2. Weight:15%. Please explain the difference among the following terms: (a) WHERE clause vs HAVING clause in SQL (b) alternate-key vs super-key (c) OUTER JOIN clause ya INNER JOIN

Answer 1

(a) WHERE clause vs HAVING clause in SQL:

In simple terms, WHERE clause is used for individual tuples while HAVING clause is used for group of tuples and not individual tuples.

Detailed Explanation:

WHERE

1. Implemented in row operations and is applied to a single row.

2. Based on conditions, it fetches records from the row.

3. SELECT, UPDATE & DELETE statement can be used.

4. Aggregate functions can't appear here.

HAVING

1. Implemented in columns operations and is applied to sum of rows or groups.

2. It first fetches the data and then separate it according to the conditions.

3. SELECT statement can't be used.

4. Aggregate function can appear here.

(b) alternate key vs super key:

Alternate key is the key that is attached to one or more columns which are unique for every tuple but they are not primary key. It can also be a foreign key. Each alternate key can generate a unique index.

Super key is the key is a set of attributes of a relation variable that for which that it holds in all relations assigned to that variable.

(c) OUTER JOIN clause vs INNER JOIN

An INNER JOIN gives rows which match on the values in columns which are common of two or more tables using an operator such as "=" or equal while a LEFT JOIN gives all the rows from left table with matched rows from both the tables.