ITM 500 Chapter Notes -Aggregate Function
DepartmentInformation Technology Management
Course CodeITM 500
This preview shows half of the first page. to view the full 2 pages of the document.
SELECT <list of column expressions>
FROM <list of tables and join operations>
WHERE <list of logical expressions for rows>
GROUP BY <list of grouping columns>
HAVING <list of logical expressions for groups>
ORDER BY <list of sorting specifications>
WHERE vs. HAVING:
- WHERE conditions: cannot have aggregate functions (syntax error)
- HAVING: only use conditions that involve an aggregate function
aggregate function is a function that returns a single value from a collection of input values
such as a set, a bag or a list.
Common aggregate functions include:
Weak entity rule
- At least one identifying relationship
- Cannot be a weak entity without at least one identifying relationship
Identifying relationship rule:
- At least one participating relationship must be weak
- Not an identifying relationship if there is not a weak entity
Identification dependency cardinality rule:
- Minimum and maximum cardinality must (1,1) for a weak entity in all identifying relationships
- (1,1) cardinality should appear near the parent entity type
- Common source of diagram errors
There are three types of outer joins namely:
Left Outer Join---For retreiving all the columns from the first table irrespective of the column match.
Right Outer Join---For retreiving all the columns from the second table irrespective of the column match
Full Outer Join---For retreiving all the columns from both the tables irrespective of column match.
1.Its a join of two or more tables.
2.It means any data matched in ON clause will be matched in both the tables
3.It returns only the rows which are satisfied by join conditions.
You're Reading a Preview
Unlock to view full version