/* * NEU Silicon Valley * CS 5200 Database Management Systems * http://dbis-uibk.github.io/relax/calc.htm * Wikipedia - Relational algebra data set * * Examples of how the various SQL statements work. * Uncomment each one to execute the expression and see * what it does with the Employee and Dept relations. * For help see relational algebra reference: * http://dbis-uibk.github.io/relax/help.htm#relalg-reference */ -- ----------------- -- Basic operations -- ----------------- /* The Employee relation */ -- select * from Employee /* The Dept relation */ -- select * from Dept -- --------- -- Selection -- --------- /* Select tuples from Employee with DeptName = 'Finance'. * Result is relation of same type and only selected tuples. */ -- select * from Employee where DeptName = 'Finance' /* Select tuples from Employee with DeptName ≠ 'Finance' * Result is relation of same type and only selected tuples. */ -- select * from Employee where DeptName != 'Finance' /* Select tuples from Employee with DeptName = 'Sales' or * DeptName ='Executive' * Result is relation of same type and only selected tuples. */ -- select * from Employee where DeptName = 'Sales' or DeptName = 'Executive' -- ------- -- Project -- ------- /* Project the Name and DeptName attributes of Employee. * Result is relation of new type with only Name and * DeptName attributes. */ -- select Name, DeptName from Employee /* Project the DeptName, Name, EmpId attributes of Employee. * Result is relation of new type with same attributes * reordered. */ -- select DeptName, Name, EmpId from Employee /* Project the Name and DeptName attributes of the * selection of Employee tuples with DeptName ≠ 'Finance' * Result is relation of new type with only Employee Name * and DeptName attributes. */ -- select Name, DeptName from Employee where DeptName != 'Finance' -- ----- -- Group -- ----- /* Group to count total number of employee tuples. * Result is relation with Count attribute and one tuple * with number of employees in the relation. */ -- select count(*) as Count from Employee /* Group to count number of employee tuples per department. * Result is relation with DeptName and Count attributes * showing the number of employees per department. */ -- select DeptName, count(*) as Count from Employee group by DeptName /* Group to count average number of employees per department. * Result is relation with Average attribute and one tuple * with the average number of employees per department. */ -- select avg(Count) as Average from (select count(*) as Count from Employee group by DeptName) as CountPerDept -- ---- -- Sort -- ---- /* Employee relation sorted ascending (default) by Name * attribute */ -- select * from Employee order by Name /** Employee relation sorted descending by Name attribute */ -- select * from Employee order by Name desc /** Employee relation sorted ascending by EmpId attribute */ -- select * from Employee order by EmpId asc /* Sort in descending order by Name * the projection of the Name and DeptName attributes of * the selection of Employee tuples with DeptName ≠ 'Finance' * Result is relation of new type with only Name and DeptName * attributes. */ -- select Name, DeptName from Employee where DeptName != 'Finance' order by Name desc -- ----------- -- Rename (ρ) -- ----------- /* Rename EmpId attribute to EmployeeID */ -- select Name, EmpId as EmployeeId, DeptName from Employee /* Rename Employee relation to Worker * */ -- select * from Employee as Worker /* Rename Employee relation to Worker and the * EmpId relation to WorkerId */ -- select Name, EmpId as WorkerId, DeptName from Employee as Worker -- ----- -- Union -- ----- /* Take the union the Name and Dept name fields from * Employee and the Manager and DeptName fields from Dept. */ -- (select Name, DeptName from Employee) union (select Manager, DeptName from Dept) -- --------- -- Intersect -- --------- /* Take the intersection the Name and Dept name fields from * Employee and the Manager and DeptName fields from Dept. */ -- (select Name, DeptName from Employee) intersect (select Manager, DeptName from Dept) /* Take the difference the Name and Dept name fields from * Employee and the Manager and DeptName fields from Dept. */ -- (select Name, DeptName from Employee) except (select Manager, DeptName from Dept) -- -------------- -- Cross Join (⨯) -- -------------- /* Cross join the Employee and Dept relations. * Result is a relation of new type with attributes of both relations * with all possible combinations of tuples from each relation. * Note: Attributes are qualified by relation name, so DeptName * attribute from each relation is distinct */ -- select * from Employee, Dept /* Select tuples with matching DeptName attributes from * Cross join of Employee and Dept relations. * Result is new type with tuples that have matching DeptName * attributes. */ -- select * from Employee, Dept where Employee.DeptName = Dept.DeptName -- ---------------- -- Natural Join (⨝) -- ---------------- /* Natural join of the Employee and Dept relations with * matching DeptName attributes. * Result is new type with tuples that have matching DeptName * attributes (same as previous select of cross join) */ -- select * from Employee join Dept on Employee.DeptName = Dept.DeptName -- ------------------- -- Left Outer Join (⟕) -- ------------------- /* Left Outer Join of the Employee and Dept relations on * matching DeptName attributes. * Result is new type with tuples from Employee relation, * combined with matching tupples from Dept relation or * NULL attributes if no Dept tuple with matching DeptName * attribute. */ -- Employee ⟕ Employee.DeptName = Dept.DeptName Dept -- select * from Employee left join Dept on Employee.DeptName = Dept.DeptName -- -------------------- -- Right Outer Join (⟖) -- -------------------- /* Right Outer Join of the Employee and Dept relations on * matching DeptName attributes. * Result is new type with tuples from Dept relation, * combined with matching tupples from Employee relation or * NULL attributes if no Employee tuple with matching * DeptName attribute. */ -- select * from Employee right join Dept on Employee.DeptName = Dept.DeptName -- -------------------- -- Full Outer Join (⟗) -- -------------------- /* Full Outer Join of the Employee and Dept relations on * matching DeptName attributes. * Result is new type whose tuples have attributes from * Dept relation, combined with matching tupples from * Employee relation, or NULL attributes if no Employee * tuple with matching DeptName value or no Dept tupple * with matching DeptName value. */ -- select * from Employee full join Dept on Employee.DeptName = Dept.DeptName -- -------------------- -- Left Semijoin (⋉) -- -------------------- /* SQL has no Left Semijoin, which result in tuples from the * Employee table that have matching attributes in the Dept table. * It is often implemented using the EXISTS as: * select * from Employee where exists (select * from Dept where Employee.DeptName = Dept.DeptName) * If EXISTS is not available, it can also be implemented using * JOIN as shown here. */ -- select Employee.Name, Employee.EmpId, Employee.DeptName from Employee join Dept on Employee.DeptName = Dept.DeptName -- -------------------- -- Right Semijoin (⋊) -- -------------------- /* SQL has no Right Semijoin, which results in tuples from the * Dept table that have matching attributes in the Employee table * It is often implemented using EXISTS as: * select * from Dept where exists (select * from Employee where Employee.DeptName = Dept.DeptName) * If EXISTS is not available, it can also be implemented using * JOIN as shown here. */ -- select Dept.DeptName, Dept.Manager from Dept join Employee on Employee.DeptName = Dept.DeptName -- ------------- -- Antijoin (▷) -- ------------- /* SQL has no Antijoin, which results is the tuples from the * Employee table that have no matching attributes in the * Dept table. * It is often implemented using NOT and EXISTS as: * select * from Employee where not exists (select * from Dept where Employee.DeptName = Dept.DeptName) * If EXISTS is not available, it can also be implemented using * EXCEPT to subtract from Employee the left semi-join of * Employee and Dept. */ -- (select * from Employee) except (select Employee.Name, Employee.EmpId, Employee.DeptName from Employee join Dept on Employee.DeptName = Dept.DeptName)