Upgrade to remove ads
Only $35.99/year

Day 16 -19

Terms in this set (57)

Two unary operations
Selection (horiztonal tuples)
Projection (vertical tuples)
Six binary operators
Union
Intersection
Difference
Join
Cartesian product
Division
Select operator
Selects a horizontal subset of tuples that satisfy a selection condition from the relation
<selection condition>
is a boolean expression specified on the attributes of relation R
Relational algebra operations can be:
Nested
Project operator
Selects a vertical subset of attributes from a relation.
If the attribute list in a projection is not a superkey (is not unique)...
duplicate tuples will NOT be displayed. Relations don't allow duplicates.
Cartesian product aka "Cross product" or "product"
All combinations of tuples from two relations
When cartesian product is combined with the select operator it is....
equivalent to a join operation which is more useful.
Set operations
Union - "OR"
Intersection "AND"
Difference "NOT"
Two relations (R and S) are "Union compatible" if they:
*Have the same degree (number of attributes)
*Pairs of attributes from R and S have the same domain.
Union compatibility
is a requirement for all set operations (union, intersection and difference)
Join operation
used to combine related tuples from two relations into a single tuple when the join condition is satisfied. Must be join compatible. (Must involve attributes from R and S which share the same domain)
Difference between Join and Cartesian
With join only combinations of tuples satisfying the join condition will appear in the result, whereas in the cartesian product all combinations are in the result.
Variations on join
Equijoin (most common)
Natural Join
Theta join (uncommon)
Outer join
- left outer join
- right outer join
- full outer join
Equijoin operation
Involves join conditions with equality comparisons only. The result of a equijoin includes all attributes from both relations participating in the join operation. This implies duplication of the joining attributes in the result.
IN SQL (INNER JOIN)
Natural join
created to omit the second (and superfluous) attribute in an equijoin condition. It gets rid of the repeated attribute.
Weakness of the natural join:
the attributes used for the join must have the same name in both relations
Theta join operation
Occurs infrequently in practical applications, theta join don't involve equality but inequality conditions for the join condition involving attributes that share the same domain
Outer join operation
can be used when we want to keep all the tuples in R, or those in S, or those in both relations in the result of the join, whether or not they have matching tuples in the other relation
Left outer join
Keeps every tuple in first or left relation in R
Right outer join
keeps every tuple in the second or right relation S in the result
Full outer join
keeps all tuples in both the left and right relations when no matching tuples are found
The divide operation
Useful when there is a need to identify tuples in one relation that match all tuples in another relation
General form of SELECT FROM WHERE
SELECT <column list>
FROM <table list>
WHERE <condition>
<column list>
is a list of column names (attributes) whose values are to be projected
<table list>
is a list of the table names (relations) required to process the query
<condition>
is a conditional (boolean) expression that identifies the rows to be retrieved by the query
Optional add-ons to select clause:
GROUP BY expression
HAVING condition
ORDER BY column name(s)
Group by expression
group of rows with the same value
typically used with aggregate functions (count, min,max,max,avg, etc..)
ex: SELECT college, avg(hrs) FROM course GROUP BY college;
Having condition
-filters the group subject to some condition
- similar to WHERE
- used on the result of aggregate functions

ex: SELECT college avg(hrs) FROM course GROUP BY college HAVING AVG(hrs) > 3;
ORDER BY column name(s)
changes the order in which tuples are returned

ex: SELECT college, AVG(hrs) FROM course GROUP BY college HAVING AVG(hrs) > 3 ORDER BY AVG(hrs);
Algebra aggregate function
COUNT()
AVG()
MIN()
MAX()
SUM()
-ignore null values
ex: SELECT college, AVG(hrs) FROM course GROUP BY college;
Creating aliases
can be used for any attribute, but is particularly used for aggregate attributes
IN/NOT IN and BETWEEN
can be used as comparison operators

ex: SELECT * FROM course
WHERE credit = 'G'
AND college IN ('business', 'engineering');
Two wildcard characters
% any number of characters
_ exactly one character


ex: SELECT * FROM textbook WHERE title LIKE 'S%';
IS NULL and IS NOT NULL
the absence of data, so nothing is EVER "equal" to NULL - therefore we have to use the "IS" operator

ex: SELECT * FROM section WHERE room IS NULL
Simple comparison operators
>,<,>=, <=,<>, =

ex: SELECT * FROM employees WHERE salary > 50000;
"INNER JOIN" and "JOIN"
are equivalent terms. Using Inner makes it a little more clear what you are doing
Inner Join
The result of an equijoin results in paris of attributes with identical values in all the tuples
Natural Join (3 techniques)
Requires the joining attributes have the same name

ex:
- SELECT * FROM C NATURAL JOIN D;
- SELECT * FROM C JOIN D USING dcode;
- SELECT C.*, D.Name, D.College FROM C JOIN D ON C.dcode = D.dcode
Inner Join
Used for recursive (unary) relationships. It is necessary to use table aliases if using the same table twice (or more) in one query

SELECT attributes
FROM table1 alias1
JOIN table1 alias2
ON alias1.attr = alias2.attr

OR ---

SELECT * FROM table1, table2
WHERE table1.attribute = table2.attribute
Inner join - more than two tables
you can join any number of tables together by nesting the join operations
Left outer join Syntax
SELECT * FROM D LEFT OUTER
JOIN C ON D.dcode = C.dcode;
Right outer join syntax
SELECT * FROM D RIGHT OUTER
JOIN C ON D.dcode = C.dcode;
Full outer join syntax
• SELECT * FROM D FULL OUTER
JOIN C ON D.dcode = C.dcode;
INSERT
command inserts new data into a table

ex: INSERT INTO course (name, course#, credit, college, hrs) values ('db', '83838', 'u')
UPDATE
command updates existing data in table

ex: UPDATE course SET hrs=3 WHERE course#='454534'
DELETE
command deletes data from a table

ex: DELETE FROM course WHERE college='Education'
Delete must not.....
violate referential integrity constraints
Show all attributes for employees with a salary greater than $20,000 order the result by last name.
SELECT *
FROM employees
WHERE salary > 20000
ORDER BY lname;
List each department name and the department's average salary for departments
that have an average salary of $50,000 or above. Do not include the accounting or
economics departments. Order the results alphabetically by department
SELECT department, AVG(salary)
FROM employees
WHERE department NOT IN ('Accounting', 'Economics')
GROUP BY department
HAVING AVG(salary) >= 50000
ORDER BY department;
List each department and the number of faculty in each department that have a
salary over $100,000 when the average salary in the department is less than
$80,000. Order the results so the department with the largest number of faculty
meeting this criteria is first
SELECT department, COUNT(empid) as Num_faculty
FROM employees
WHERE salary > 100000
GROUP BY department
HAVING AVG(salary) < 80000
ORDER BY COUNT(empid) DESC;
Cartesian product with conditional statement is
equivalent to an equijoin
Uncorrelated subquery
Executed once and results used by outer query. Passes one or more values.
Three operators: IN, ANY, ALL
May be negated with NOT
correlated subquery
Executed once for each row in the outer query. Can be used if necessary to check if a nested subquery returns no rows, using the EXISTS operator which returns the value of true if a set is non-empty.
Use of all and any with subqueries
The ALL and ANY operators can be combined with other comparison
operators to treat the results of a subquery as a set of values, rather than as
individual values.
Other sets by this creator