Relational Algebra & SQL: Unary/Binary Operators, Single Table Queries & Subqueries, Slides of Database Management Systems (DBMS)

Download Relational Algebra & SQL: Unary/Binary Operators, Single Table Queries & Subqueries and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Chapter 11.1 and 11.

Data Manipulation: Relational Algebra and SQL

Agenda

• 11.1 – Relational Algebra
  • Unary Operators
  • Binary Operators
• 11.2 – Structured Query Language (SQL)
  • SQL Queries on a Single Table
  • SQL Queries on Binary Operators
  • Subqueries

Relational Algebra

• Binary Operators

• Cartesian Product Operator
• Set Theoretic Operators
  • Union
  • Intersection
  • Difference
• Join Operators
  • Natural Join Operator
  • Equijoin Operator
  • Theta Join Operator
  • Outer Join Operator
• The Divide Operator

Relational Algebra

• Binary Operators

• Cartesian Product Operator is used to combine tuples from any two relations in a combinatorial fashion
• Relational Algebra Syntax: RELATION_R X RELATION_S

Relational Algebra

• Join Operators

• Join Operators specify how to relate tables in the query, it is used to combine related tuples from two relations into single tuples
• In order to join the two relations R and S, they must be join compatible, which means that the join condition must involve attributes from R and S which share the same domain

Relational Algebra

• Join Operators

• Natural Join Operator produces all the different combinations of the tuples from the two relations R and S that satisfy a join condition R * (^) S

Relational Algebra

• Join Operators

• Theta Join Operator produces the combinations of tuples from R and S that satisfy a join condition which does not have to involve equality comparisons (if it did then they are called an Equijoin) R [X] S
• Let R be the CAR relation and S be the BOAT relation if a customer wants to buy both a car and boat but doesn’t want to spend more money on a boat then a car we would use a theta join operator to give a new relation with all the possible options CarPrice ≥ BoatPrice

Relational Algebra

• Join Operators

• Outer Join Operators are Inner Join operations that only look at matching tuples from one relation to the other, attributes of tuples with no matching tuples are set to NULL
• Left Outer Join is denoted by ]X| , and keeps every tuple in the left or first relation (R)
• Right Outer Join is denoted by |X[ , and keeps every tuple in the right or second relation (S)
• Full Outer Join is denoted by ]X[ , and keeps all the tuples in both the left and right relations when no matching tuples are found

Relational Algebra

• Divide Operator

• Divide Operator returns every tuple from R that match all tuples in S; R and S must be division compatible R ÷ S
• Let R be COMPLETED and S be DBPROJECT we can get the result as

Structured Query Language (SQL)

• Overview

• SQL Queries based on a Single Table

• Sample SQL Queries
• Handling null values

• SQL Queries based on Binary Operators

• Subqueries

Structured Query Language (SQL)

• Overview

• Other useful clauses for a SELECT statement include: GROUP BY group_by_expression - Forms groups of rows with the same value HAVING group_condition - Filters the groups subject to some condition ORDER BY column name(s) - Specifies the order of the output
• After your SELECT statement has been written a semi-colon ‘;’ is needed at the end to close up the statement

Structured Query Language (SQL)

• SQL Queries Based on a Single Table

• We can use an asterisk (*) to denote that all columns from a table is to be selected
• We can also use the logical operators AND OR and NOT with the WHERE clause to form more precise clauses
• When writing a query the hierarchy order in any expression follows

1. The comparison operators (> ≥ = ≠ ≤ <) = is equals <> is not equals

   = is greater than or equal to <= is less than or equal to Is greater than < is less than

2. NOT operator
3. AND operator
4. OR operator

Structured Query Language (SQL)

• SQL Queries Based on a Single Table

Example 2 Suppose we want to know all professors who make more than $6000 a month and the professors who work in a certain department (known as code 3) SELECT * FROM PROFESSOR WHERE PROFESSOR.SALARY/12 > 6000 AND PROFESSOR.PR_DPT_DCODE = 3;

Structured Query Language (SQL)

• SQL Queries Based on a Single Table

• We can also simulate a projection operation by using SELECT
• We can get all the colleges of a university from the co_college column with this command SELECT COURSE.CO_COLLEGE FROM COURSE;