DBMS-Unit2Slides(20 files merged).pdf

Database Management Systems Relational Query language, Algebra & Unary Operations 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarsha...

Database Management Systems Relational Query language, Algebra & Unary Operations 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. 2 Database Management Systems Unit 2 overview: 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries 3 Database Management Systems Relational Algebra A procedural language that consists of a set of operations, where each operation takes one or two relations as input and produces a new relation as the result. The result of an operation is a new relation, which is formed from one or more input relations. This property makes the algebra "closed" (all objects in relational algebra are relations) These relations can be further manipulated using operations of the same algebra. A sequence of relational algebra operations forms a relational algebra expression. The result of a relational algebra expression is also a relation that represents the result of a database query. 4 Database Management Systems Unit 2: Relational Algebra Operators: Unary Operators: Select: σ Project: ∏ Rename: ρ Binary Operators: Union: U Intersection :∩ Set difference: – Cartesian product: X Join: ⋈ 5 Database Management Systems Unit 2: Relational Algebra Use this table for query examples: University Database Employee Database: 6 Database Management Systems Unary Operator: Select (σ) The SELECT operation is used to choose a subset of the tuples from a relation that satisfies a selection condition. We can consider the SELECT operation to be a filter that keeps only those tuples that satisfy a qualifying condition. Notation: σ p (r) p is called the selection predicate The SELECT operation can also be visualized as a horizontal partition of the relation into two sets of tuples—those tuples that satisfy the condition and are selected, and those tuples that do not satisfy the condition and are filtered out. 7 Database Management Systems Unary Operator: Select (σ) Example 1: Select those tuples of the instructor relation where the instructor is in the “Physics” department. Query: σ(Dept_Name = “Physics”)(Instructor) Example 2: Select the tuples for all employees who either work in department 4 and make over $25,000 per year, or work in department 5 and make over $30,000. Query: σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE) 8 Database Management Systems Unary Operator: Select (σ) SELECT Operation Properties The SELECT operation σ ( ) produces a relation S that has the same schema (same attributes) as R SELECT is commutative: Because of commutative property, A cascade (sequence) of SELECT operations may be applied in any order: or 9 Database Management Systems Unit 2: Relational Model and Database Design We allow comparisons to be used in the selection predicate. =, ≠, >, ≥. 90,000 (instructor) The select predicate may include comparisons between two attributes. Example: Fetch all the details of the ‘Research’ department. σ dept_name=“Research” (department) NOTE: The number of tuples in the result of a SELECT is less than (or equal to) the number of tuples in the input relation R. 10 Database Management Systems Unary Operator: PROJECT(∏) PROJECT(∏) A unary operation that returns its argument relation, with certain attributes left out. Notation: ∏ A1,A2,A3 ….Ak (r) where A1, A2, …, Ak are attribute names and r is a relation name. The PROJECT operation, selects certain columns from the table and discards the other columns. Duplicate rows removed from result, since relations are sets This operation keeps certain columns (attributes) from a relation and discards other columns. The PROJECT operation can be visualized as a vertical partition of the relation The list of specified columns (attributes) is kept in each tuple. The other attributes in each tuple are discarded. 11 Database Management Systems Unary Operator: PROJECT(∏) Example 1: Eliminate the dept_name attribute of instructor Query: ∏ID, name, salary (instructor) Result: 12 Database Management Systems Unary Operator: PROJECT(∏) Example 2: Fetch the Lname, Fname, Salary information about the Employees. Query: ∏Lname, Fname, Salary (EMPLOYEE) Example 3: Fetch the Sex and Salary information about the Employees. Query: ∏Sex, Salary (EMPLOYEE) output for example 2 output for example 3 13 Database Management Systems Unary Operator: PROJECT(∏) Properties of PROJECT operator: The project operation removes any duplicate tuples. This is because the result of the project operation must be a set of tuples. Mathematical sets do not allow duplicate elements. The number of tuples in the result of projection ∏(R) is always less or equal to the number of tuples in R. If the list of attributes includes a key of R, then the number of tuples in the result of PROJECT is equal to the number of tuples in R. 14 Database Management Systems Composition of Relational Operations The result of a relational-algebra operation is a relation and therefore of relational-algebra operations can be composed together into a relational- algebra expression. Example: Find the names of all instructors in the Physics department. ∏name(σ dept_name =“Physics” (instructor) ) Instead of giving the name of a relation as the argument of the PROJECT operation, we give an expression that evaluates to a relation. We may want to apply several relational algebra operations one after the other. Either we can write the operations as a single relational algebra expression by nesting the operations, or we can apply one operation at a time and create intermediate result relations. 15 Database Management Systems Relational Algebra Expressions To retrieve the first name, last name and salary of all employees who work in the department 5, we must apply a select and a project operation. We can write a single relational algebra expression as follows: ∏Fname,Lname,Salary(σ DNo = 5​(Employee)) Or we can explicitly show the sequence of operations, giving a name to each immediate relation: DEP5_EMPS ← σ DNO=5 (Employee) RESULT ← ∏ Fname,Lname,Salary (DEP5_EMPS) 16  Database Management Systems Unary Operator: RENAME (ρ) The results of relational-algebra expressions do not have a name that we can use to refer to them. The rename operator, ρ , is provided for that purpose. The RENAME operator is denoted by ρ(rho) The expression: ρx (E) returns the result of expression E under the name x Another form of the rename operation: ρx(A1,A2,.. An) (E) In some cases, we may want to rename the attributes of a relation or the relation's name or both. Useful when a query requires multiple operations 17 Database Management Systems Unary Operator: RENAME (ρ) This is a unary operator which changes attribute names for a relation without changing any values. Renaming removes the limitations associated with set operators. Notation: ρOldName->NewName (r) For Example, ρFather->Parent (Paternity). For the given operation, the attribute “Father” will be renamed as “Parent” for the relation Paternity. If there are two or more attributes involved in renaming operation, then ordering is more meaningful. ρBrach,Salary->Location,Pay (Employee) 18  Database Management Systems The Assignment Operator (←) It is convenient at times to write a relational-algebra expression by assigning parts of it to temporary relation variables. The assignment operation is denoted by ← and works like assignment in a programming language. Example: Fetch the FirstName, LastName and Salary of all employees who work in department 5. With the assignment operation, a query can be written as a sequential program consisting of a series of assignments followed by an expression whose value is displayed as the result of the query. 19 Database Management Systems The Assignment Operator (←) Example: 20 Database Management Systems Questions Scenario: You have a table Employees with columns EmployeeID, Name, Department, Salary, and JoiningYear (eg:2016). Question: How would you create a query to find the names and departments of employees who joined before 2020, but without showing the EmployeeID and Salary columns? Scenario: A database contains a Customers table with fields CustomerID, FirstName, LastName, City, and PurchaseAmount. Question: If you need to rename the City column to Location, write the relational query for this scenario. Scenario: You are working with a table Products with columns ProductID, ProductName, Category, Price, and StockQuantity. Question: How would you select the ProductName and Price of all products that are in the Electronics category and have more than 10 items in stock? 21 Database Management Systems Questions Scenario: You have a table Employees with columns EmployeeID, Name, Department, Salary, and JoiningYear (eg:2016). Question: How would you create a query to find the names and departments of employees who joined before 2020, but without showing the EmployeeID and Salary columns? Answer: ∏Name,Department​(σ JoiningYearLocation (Customers) 22 Database Management Systems Questions Scenario: You are working with a table Products with columns ProductID, ProductName, Category, Price, and StockQuantity. Question: How would you select the ProductName and Price of all products that are in the Electronics category and have more than 10 items in stock? Answer: ∏ProductName,Price​(σ Category=’Electronics’∧ StockQuantity>10​(Products)) 23 THANK YOU [email protected] 24 Database Management Systems Binary Operations 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. 2 Database Management Systems Unit 2: overview 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries 3 Database Management Systems Binary Operator : The Cartesian-Product Operation The Cartesian-product (Cross Product) operation (denoted by X) allows us to combine information from any two relations. Example: the Cartesian product of the relations instructor and teaches is written as: instructor X teaches We construct a tuple of the result out of each possible pair of tuples: one from the instructor relation and one from the teaches relation (see next slide). Since the instructor ID appears in both relations we distinguish between these attributes by attaching to the attribute the name of the relation from which the attribute originally came. instructor.ID teaches.ID 4 Database Management Systems Binary Operator : The Cartesian-Product Operation Instructor Teaches 5 Database Management Systems Binary Operator : The Cartesian-Product Operation Instructor X Teaches 6 Database Management Systems Binary Operator : The Join Operation Consider the table below : 7 Database Management Systems Binary Operator : The Cartesian-Product Operation 8 Database Management Systems Binary Operator : The Cartesian-Product Operation 9 Database Management Systems Binary Operator : The Cartesian-Product Operation Query split-up: 10 Database Management Systems Binary Operator : The Cartesian-Product Operation Query split-up: 11 Database Management Systems Binary Operator : The Cartesian-Product Operation Query split-up: 12 Database Management Systems Binary Operator : The Join Operation The Cartesian-Product instructor X teaches associates every tuple of instructor with every tuple of teaches. Most of the resulting rows have information about instructors who did NOT teach a particular course. To get only those tuples of “instructor X teaches “ that pertain to instructors and the courses that they taught, we write: σ instructor.id = teaches.id (instructor x teaches ) We get only those tuples of “instructor X teaches” that pertain to instructors and the courses that they taught. The result of this expression is shown in the next slide 13  Database Management Systems Binary Operator : The Join Operation The table corresponding to: σ instructor.id = teaches.id (instructor x teaches) 14 Database Management Systems Binary Operator : The Join Operation 15  Database Management Systems Binary Operator : The Join Operation Example: Write an algebraic query to retrieve the details of employees along with their department id and name. 16  Database Management Systems Binary Operator : The Join Operation Q: Query: You Need to write Query write a query, get a job 😜 17 THANK YOU [email protected] 18 Database Management Systems Set Operations, Equivalent queries 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. 2  Database Management Systems Unit 2: Relational Model and Database Design 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries 3 Database Management Systems Set Operations : Set Operations What are set operations ? Union Operation U Set-Intersection Operation ∩ Set Difference Operation ─ 4 Database Management Systems Set Operations : UNION Operation The union operation allows us to combine two relations. Notation: r ∪ s For r ∪ s to be valid. 1. r, s must have the same arity (same number of attributes). 2. The attribute domains must be compatible (example: 2nd column of r deals with the same type of values as does the 2nd column of s). Schema: Example: Find all courses taught in the Fall 2017 semester, or in the Spring 2018 semester, or in both 5  Database Management Systems Set Operations : UNION Operation Query: ∏course_id (σ semester=“Fall” Λ year=2017 (section)) ∪ ∏course_id (σ semester=“Spring” Λ year=2018 (section)) Table: Output: 6  Database Management Systems University Database Instructor Table: Course Table: Prerequisites Table: Teaches Table: Department Table: 7 Database Management Systems Employee Database 8  Database Management Systems Set Operations : UNION Operation The result of R U S, is a relation that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated. The two-operand relations R and S must be “type compatible” (or UNION compatible). R and S must have same number of attributes. Each pair of corresponding attributes must be type compatible (have same or compatible domains). 9  Database Management Systems Set Operations : UNION Operation What will be the first Column Name after Union of Paternity and Maternity Relation? Is It Meaningful? Table: Output: 10  Database Management Systems Set Operations : UNION Operation To retrieve the social security numbers of all employees who either work in department 5 (RESULT1 below) or directly supervise an employee who works in department 5 (RESULT2 below). We can use the UNION operation as follows: The union operation produces the tuples that are in either RESULT1 or RESULT2 or both. 11  Database Management Systems Set Operations : UNION Operation NOTE: Type Compatibility of operands is required for the binary set operation UNION U, (also for INTERSECTION ∩, and SET DIFFERENCE –). 12  Database Management Systems Set Operations : Intersection Operation The set-intersection operation allows us to find tuples that are in both the input relations. Notation: r ∩ s Assume: r, s have the same arity attributes of r and s are compatible Example: Find the set of all courses taught in both the Fall 2017 and the Spring 2018 semesters. Query: ∏course_id (σ semester=“Fall” Λ year=2017 (section)) ∩ ∏course_id (σ semester=“Spring” Λ year=2018 (section)) Output: Note: the number of attributes of a relation is referred to as its arity. 13 Database Management Systems Set Operations : Intersection Operation Example : Graduates ∩ Managers Output: 14  Database Management Systems Set Operations : SET DIFFERENCE The set-difference operation allows us to find tuples that are in one relation but are not in another. Notation: r – s Set differences must be taken between compatible relations. r and s must have the same arity. attribute domains of r and s must be compatible. Example: to find all courses taught in the Fall 2017 semester, but not in the Spring 2018 semester. Query: ∏course_id (σ semester=“Fall” Λ year=2017 (section)) − ∏course_id (σ semester=“Spring” Λ year=2018 (section)) Output: 15 Database Management Systems Set Operations : Intersection Operation Example : Graduates - Managers Output: 16 Database Management Systems Set Operations :The set operations UNION, INTERSECTION, and MINUS Example : STUDENT ∪ INSTRUCTOR Output: 17 Database Management Systems Set Operations :The set operations UNION, INTERSECTION, and MINUS Example : STUDENT ∩ INSTRUCTOR. Output: 18 Database Management Systems Set Operations :The set operations UNION, INTERSECTION, and MINUS Example : STUDENT – INSTRUCTOR. Output: 19 Database Management Systems Set Operations :The set operations UNION, INTERSECTION, and MINUS Example : INSTRUCTOR – STUDENT. Output: 20  Database Management Systems Equivalent Queries There is more than one way to write a query in relational algebra. Example: Find information about courses taught by instructors in the Physics department with salary greater than 90,000. Query 1: σ dept_name=“Physics” ∧ salary > 90,000 (instructor) Query 2 : σ dept_name=“Physics” (σ salary > 90.000 (instructor)) The two queries are not identical; they are, however, equivalent -- they give the same result on any database. 21  Database Management Systems Equivalent Queries Schema: Instructor {ID, name, dept name, salary} teaches (ID, course id, sec id, semester, year) 22 Database Management Systems Some properties of UNION, INTERSECT and DIFFERENCE Notice that both union and intersection are commutative operations; that is: R ∪ S = S ∪ R and R ∩ S = S ∩ R Both union and intersection can be treated as n-ary operations applicable to any number of relations as both are associative operations; that is: R ∪ (S ∪ T) = (R ∪ S) ∪ T (R ∩ S) ∩ T = R ∩ (S ∩ T) The minus operation is not commutative; that is, in general: R−S ≠ S−R 23 Database Management Systems Questions and Answers Fill in the Blanks: 1) The union operation allows us to combine two relations (r and s). The notation used for union is ______. Ans: r ∪ s 2) For the union operation r ∪ s to be valid, r and s must have the same ______. Ans: arity (same number of attributes) 3) In the union operation, duplicate tuples are ______. Ans: eliminated 4) The set-intersection operation allows us to find tuples that are in both input relations. The notation used for intersection is ______. Ans: r ∩ s 24  Database Management Systems Questions and Answers Fill in the Blanks: 5) The set-difference operation allows us to find tuples that are in one relation but not in another. The notation used for set-difference is ______. Ans: r−s 6) The number of attributes in a relation is referred to as its ______. Ans: arity 7) The two queries that give the same result on any database but are written differently in relational algebra are called ______queries. Ans: equivalent 8) In the UNION operation, the two operand relations must be "type ______“. Ans: compatible 25  Database Management Systems Questions and Answers Can you write the intersection operation using union and set difference operations ? INTERSECTION can be expressed in terms of union and set difference as follows: R ∩ S = ((R ∪ S) − (R − S)) − (S − R) 26  Database Management Systems Questions and Answers Consider the below table: Write relational algebra queries for the following questions: 27  Database Management Systems Questions and Answers 1) Find the names of employees who work in either department 101 or 103 but were not hired after 2020. Relational Algebra Query: (∏emp_name(σ dept_id = 101(Employee)) ∪ ∏emp_name​(σ dept_id = 103 ​(Employee))) − ∏emp_name(σ hire_date > ‘2020-12-31’(Employee)) Output: 28  Database Management Systems Questions and Answers 2) Find the employee IDs of those who earn more than 55000 and work in department 102 or 104. Relational Algebra Query: ∏emp_id(σ salary > 55000(Employee)) ∩ ( ∏emp_id(σ dept_id = 102(Employee)) ∪ ∏emp_id​(σ dept_id = 104(Employee))) Output: 29  Database Management Systems Questions and Answers 3) Find the names of employees who work in department 101 or 104 but not in department 103. Relational Algebra Query: (∏emp_name(σ dept_id = 101(Employee)) ∪ ∏emp_name(σ dept_id = 104(Employee))) − ∏emp_name(σ dept_id = 103(Employee)) Output: 30  Database Management Systems Questions and Answers 4) Find the employee IDs of those who were hired before 2020 or earn less than 60000, but exclude those who work in department 101. Relational Algebra Query: (∏emp_id(σ hire_date < ‘2020-01-01’​(Employee)) ∪ ∏emp_id(σ salary < 60000(Employee))) − ∏emp_id(σ dept_id = 101(Employee)) Output: 31  Database Management Systems Questions and Answers 5) Find the names of employees who were hired in 2018 or 2020, but exclude those who work in department 104. Relational Algebra Query: ∏emp_name​(σ hire_date = ‘2018-11-30’(Employee) ∪ σ hire_date = ‘2020-01-15’​(Employee)) − ∏emp_name(σ dept_id = 104 (Employee)) Output: 32  Database Management Systems Questions and Answers As an additional task, can you write the equivalent queries for the previous questions? 33 THANK YOU [email protected] 34 Database Management Systems Aggregate Functions and Grouping 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. 2 Database Management Systems Unit 2: Relational Model and Database Design 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries 3 Database Management Systems Aggregate Functions and Grouping Aggregate Functions: Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a single value. There are five types of aggregate functions which are 1. AVERAGE 2. MINIMUM 3. MAXIMUM 4. SUM 5. COUNT 4 Database Management Systems Types of aggregate functions 5  Database Management Systems Aggregate Functions and Grouping A type of request that cannot be expressed in basic relational algebra is to specify mathematical aggregate functions on collections of values from the database. Examples of such functions include retrieving the average or total salary of all employees or the total number of employee tuples. These functions are used in simple statistical queries that summarize information from the database tuples. Common functions applied to collections of numeric values include SUM, AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples or values. Note: count just counts the number of rows, without removing duplicates 6  Database Management Systems Aggregate Functions and Grouping Use of the Aggregate Functional operation ℱ ℱMAX Salary (EMPLOYEE) retrieves the maximum salary value from the EMPLOYEE relation. ℱMIN Salary (EMPLOYEE) retrieves the minimum salary value from the EMPLOYEE relation. ℱSUM Salary (EMPLOYEE) retrieves the sum of salary values from the EMPLOYEE relation. ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE) computes the count (number) of employees & their average salary. The examples all summarized one or more attributes for a set of tuples(Maximum Salary or Count (number of) Ssn ) 7 Database Management Systems Aggregate Functions and Grouping Grouping can be combined with Aggregate Functions Example: For each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY A variation of aggregate operation ℱ allows this: Grouping attribute placed to left of symbol Aggregate functions to right of symbol DNO ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE) Above operation groups employees by DNO (department number) and computes the count of employees and average salary per department 8 Database Management Systems Count Function Count: Count function is used to count the number of tuples in a relation. It can work on both numeric and non-numeric data types. Ex: Output: g count(ID) (E)=4 4 is the number of tuples in the above table 9 Database Management Systems Sum Function Sum- Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. Ex: 10 Database Management Systems Average Function Average- The function is used to calculate the average value of the numeric type. average function returns the average of all non-Null values. Ex: Output: g average(C) (R)=27/4=6.75 11 Database Management Systems Minimum Function and Maximum Function Minimum- MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column. Ex- g min(IC) (R)=3 Maximum- MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column. Ex- g max(C) (R)=10 12 Database Management Systems Aggregate Functions and Grouping Consider the table: 13 Database Management Systems Aggregate Functions and Grouping Example: 1) Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. Query: ρR(Total_Sal,Highest_Sal, Lowest_sal,Average_Sal)ℑ (SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)) (EMPLOYEE) Output: 14 Database Management Systems Aggregate Functions and Grouping Exercise: Write Relational Algebra Statements to: a) Retrieve each department number, the number of employees in the department, and their average salary, while renaming the resulting attributes. b) Show the department number, the total number of employees, and the average salary of employees in that department, for each department. c) FInd the total number of employees and the average salary across the entire company. 15 Database Management Systems Aggregate Functions and Grouping Answers: a. ρR(Dno, No_of_employees, Average_sal)(Dno ℑ COUNT Ssn, AVERAGE Salary (EMPLOYEE)). b. Dno ℑ Count ssn,Average_salary(EMPLOYEE). c. ℑ COUNT ssn, AVERAGE Salary(EMPLOYEE). Output: 16  Database Management Systems Grouping In many cases aggregate functions can be applied to subgroups of data. Example: Calculate average salary for each department. In these cases data is divided into non-overlapping groups based on specified attributes. Each group (partition) will consist of the tuples that have the same value of some attribute(s), called the grouping attribute(s). We can then apply the function to each such group independently to produce summary information about each group. SQL has a GROUP BY clause for this purpose. The GROUP BY clause specifies the grouping attributes, which should also appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s). 17 Database Management Systems Aggregate Functions and Grouping Example: find the average salary of employees in each department or the number of employees who work on each project. Query: ρR(Dno,count(*), Avg(Salary))( Dno ℑ (Dno,COUNT(*), AVG(Salary)) (EMPLOYEE) Output: 18 THANK YOU [email protected] 19 Database Management Systems SQL overview, Data definition, Structure of SQL queries Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017.  Database Management Systems Unit 2: overview: 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries Database Management Systems Introduction to SQL Commands SQL language Considered one of the major reasons for the commercial success of relational databases. SQL SQL actually comes from the word “SEQUEL” which was the original term used in the paper: “SEQUEL TO SQUARE” by Chamberlin and Boyce. IBM could not copyright that term, so they abbreviated to SQL and copyrighted the term SQL. Now popularly known as “Structured Query language”. SQL is a practical rendering of the relational data model with syntax. The language has features for Data definition, Data Manipulation, Transaction control , Indexing ,Security specification (Grant and Revoke), Active databases, Multi-media , Distributed databases etc. Database Management Systems Types of database languages Data Definition Language (DDL) statements are used to define the database structure or schema. Data Manipulation Language (DML) statements are used for managing data within schema objects DCL is the abstract of Data Control Language. Data Control Language includes commands such as GRANT, and is concerned with rights, permissions, and other controls of the database system. Transaction Control Language (TCL) is used to run the changes made by the DML statement.  Database Management Systems Schema Concepts in SQL SQL schema Identified by a schema name Includes an authorization identifier and descriptors for each element Schema elements include Tables, constraints, views, domains, and other constructs Each statement in SQL ends with a semicolon CREATE SCHEMA statement CREATE SCHEMA Lib AUTHORIZATION ‘Jsmith’; Catalog Named collection of schemas in an SQL environment SQL also has the concept of a cluster of catalogs  Database Management Systems Attribute Data Types in SQL Basic data types: Numeric data types Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION Character-string data types Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)  Database Management Systems Attribute Data Types in SQL Bit-string data types Fixed length: BIT(n) Varying length: BIT VARYING(n) Boolean data type Values of TRUE or FALSE or NULL DATE data type Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD Multiple mapping functions available in RDBMSs to change date formats Note: Learn more about MYSQL inbuilt Date Functions here  Database Management Systems Attribute Data Types in SQL Additional data types Timestamp data type Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds Optional WITH TIME ZONE qualifier INTERVAL data type Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison. Database Management Systems Attribute Data Types in SQL Explained The SQL standard supports a variety of built-in types, including: char(n): A fixed-length character string with user-specified length n. The full form, character, can be used instead. varchar(n): A variable-length character string with user-specified maximum length n. The full form, character varying, is equivalent. int: An integer (a finite subset of the integers that is machine dependent). The full form, integer, is equivalent. smallint: A small integer (a machine-dependent subset of the integer type).  Database Management Systems Attribute Data Types in SQL Explained Numeric(p, d): A fixed-point number with user-specified precision. The number consists of p digits (plus a sign), and d of the p digits are to the right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but neither 444.5 nor 0.32 can be stored exactly in a field of this type. Real, double precision: Floating-point and double-precision floating-point numbers with machine- dependent precision. Float(n): A floating-point number with a precision of at least n digits. Null: Each type may include a special value called the null value. A null value indicates an absent value that may exist but be unknown or that may not exist at all. ENUM: Data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. ENUM ('value_1','value_2','value_3')  Database Management Systems Single Example covers all data types CREATE TABLE example ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, char_column CHAR(50) NOT NULL, varchar_column VARCHAR(255) NOT NULL, tinytext_column TINYTEXT NOT NULL, text_column TEXT NOT NULL, mediumtext_column MEDIUMTEXT NOT NULL, longtext_column LONGTEXT NOT NULL, binary_column BINARY(20) NOT NULL, varbinary_column VARBINARY(20) NOT NULL, tinyblob_column TINYBLOB NOT NULL, blob_column BLOB NOT NULL, mediumblob_column MEDIUMBLOB NOT NULL,  Database Management Systems Single Example covers all data types longblob_column LONGBLOB NOT NULL, enum_column ENUM('value1', 'value2', 'value3') NOT NULL, set_column SET('value1', 'value2', 'value3') NOT NULL, bool_column BOOLEAN NOT NULL DEFAULT 0, date_column DATE NOT NULL, datetime_column DATETIME NOT NULL, timestamp_column TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, time_column TIME NOT NULL, year_column YEAR(4) NOT NULL, float_column FLOAT(10,6) NOT NULL DEFAULT '0.000000', double_column DOUBLE(10,6) NOT NULL DEFAULT '0.000000' ); Database Management Systems Char vs Varchar  Database Management Systems Char vs Varchar The char data type stores fixed-length strings. Consider, for example, an attribute A of type char(10). If we stored a string “Avi” in this attribute, seven spaces are appended to the string to make it 10 characters long. In contrast, if attribute B were of type varchar(10), and we stored “Avi” in attribute B, no spaces would be added.  Database Management Systems Advanced Data Types like CLOB, BLOB Large-Object Types Many current-generation database applications need to store attributes that can be large (of the order of many kilobytes) These data types include as a photograph, or very large (of the order of many megabytes or even gigabytes), such as a high-resolution medical image or video clips. SQL therefore provides large-object data types for character data (clob) and binary data (blob). The letters “lob” in these data types stand for “Large OBject.”  Database Management Systems Advanced Data Types like CLOB, BLOB Large-Object Types For example, we may declare attributes book review clob(10KB) image blob(10MB) movie blob(2GB) For result tuples containing large objects (multiple megabytes to gigabytes) , it is inefficient or impractical to retrieve an entire large object into memory. LOBs in the database are stored in a way that optimizes the space and provides efficient access within the database tablespaces. Internal LOBs (BLOBs, CLOBs) also provide transactional support (Commit, Rollback, and so on) of the database server.  Database Management Systems Advanced Data Types like CLOB, BLOB BLOBs (Binary LOBs) used to store unstructured binary (also called “raw”) data, such as video clips. CLOBs (Character LOBs) used to store large blocks of character data from the database character set. Blob and Clob together are known as LOB(Large Object Type). The following are the major differences between Blob and Clob data types. Binary Large Object Stores any kind of data in binary format such as images, (BLOB) audio, and video. Stores string data in the database having character set Character Large format. Used for large set of characters/strings or Object (CLOB) documents that use the database character. Database Management Systems Advanced Data Types like CLOB, BLOB Blob Clob The full form of Blob is a Binary Large The full form of Clob is Character Large Object. Object. This is used to store large binary data. This is used to store large textual data. This stores values in the form of binary This stores values in the form of character streams. streams. Using this you can stores files like videos, Using this you can store files like text files, images, gifs, and audio files. PDF documents, word documents etc. MySQL supports this with the following MySQL supports this with the following datatypes:TINYBLOB datatypes:TINYTEXT BLOB TEXT MEDIUMBLOB MEDIUMTEXT LONGBLOB LONGTEXT  Database Management Systems Domains in SQL Domain Name used with the attribute specification Makes it easier to change the data type for a domain that is used by numerous attributes Improves schema readability Example: CREATE DOMAIN SSN_TYPE AS CHAR(9); TYPE User Defined Types (UDTs) are supported for object-oriented applications. Uses the command: CREATE TYPE  Database Management Systems SQL Data Definition, Data Types, Standards Terminology: Table, row, and column used for relational model terms relation, tuple, and attribute respectively. CREATE statement Main SQL command for data definition The CREATE TABLE command is used to specify a new relation by giving it a name and specifying its attributes and initial constraints.  Database Management Systems Data Definition Language(DDL) The SQL DDL provides commands for defining table schemas, deleting tables, and modifying table schemas. DDL includes commands such as CREATE, ALTER, and DROP statements. DDL is used to CREATE, ALTER, OR DROP the database objects (Table, Views, Users). CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object Database Management Systems Types of Database Languages: DDL CREATE TABLE Other: Syntax: Create table from existing table: CREATE TABLE table_name( CREATE TABLE new_table_name AS SELECT * Col_name1 datatype(), FROM existing_table; Col_name2 datatype(),… CREATE TABLE new_table_name LIKE Col_namen datatype(), ); existing_table; Database Management Systems Types of Database Languages: DDL ALTER TABLE 3) RENAME 1) ADD Syntax: Syntax: ALTER TABLE table_name ALTER TABLE table_name RENAME COLUMN (Old_fieldname TO ADD Col_name datatype()...; New_fieldname...); 2) MODIFY 4) DROP Syntax: Syntax: ALTER TABLE table_name ALTER TABLE table_name DROP COLUMN MODIFY (fieldname datatype()...); column_name; Learn more examples Database Management Systems Types of Database Languages: DDL E) RENAME C) DESCRIBE TABLE Rename a table Syntax: Syntax: DESCRIBE table_name; SHOW CREATE TABLE: RENAME TABLE table_name to new Syntax:SHOW CREATE TABLE table_name; table_name //It lists complete information of table including F) TRUNCATE constraints,index etc.. Syntax: TRUNCATE TABLE table_name; // delete D) DROP TABLE complete data from an existing table. Table Syntax: DROP TABLE table_name; Structure remains // Complete table structure will be dropped Database Management Systems COMPANY relational database schema Database Management Systems SQL CREATE TABLE data definition statements for defining the COMPANY schema f CREATE TABLE `department` ( `Dname` varchar(15) NOT NULL, `Dnumber` int(11) NOT NULL, `Mgr_ssn` char(9) NOT NULL, `Mgr_start_date` date DEFAULT NULL); CREATE TABLE `dependent` ( `Essn` char(9) NOT NULL, `Dependent_name` varchar(15) NOT NULL, `Gender` char(1) DEFAULT NULL, `Bdate` date DEFAULT NULL, `Relationship` varchar(8) DEFAULT NULL); CREATE TABLE `dept_locations` ( `Dnumber` int(11) NOT NULL, `Dlocation` varchar(15) NOT NULL); Database Management Systems SQL CREATE TABLE data definition statements for defining the COMPANY schema CREATE TABLE `employee` ( `Fname` varchar(15) NOT NULL, `Minit` char(1) DEFAULT NULL, `Lname` varchar(15) NOT NULL, `Ssn` char(9) NOT NULL, `Bdate` date DEFAULT NULL, `Address` varchar(30) DEFAULT NULL, `Gender` char(1) DEFAULT NULL, `Salary` decimal(10,2) DEFAULT NULL, `Super_ssn` char(9) DEFAULT NULL, `Dno` int(11) NOT NULL); CREATE TABLE `project` ( `Pname` varchar(15) NOT NULL, `Pnumber` int(11) NOT NULL, `Plocation` varchar(15) DEFAULT NULL, `Dnum` int(11) NOT NULL); CREATE TABLE `works_on` ( `Essn` char(9) NOT NULL, `Pno` int(11) NOT NULL, `Hours` decimal(3,1) DEFAULT NULL); Database Management Systems One possible database state for the COMPANY relational database schema Database Management Systems One possible database state for the COMPANY relational database schema THANK YOU [email protected] Database Management Systems SQL overview, Data definition, Structure of SQL queries Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017.  Database Management Systems Specifying Constraints in SQL Constraints in SQL refer to the conditions and restrictions that are applied on the database This further means that before inserting data into the database, we are checking for some conditions. If the condition we have applied to the database holds true for the data that is to be inserted, then only the data will be inserted into the database tables. Constraints can be specified when the table is created with the CREATE TABLEstatement, or after the table is created with the ALTER TABLE statement  Database Management Systems Unit 2: overview: 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures, Cursors 14. Triggers and Recursive Queries Database Management Systems Specifying Constraints in SQL Basic constraints: Relational Model has 3 basic constraint types that are supported in SQL: Key constraint: A primary key value cannot be duplicated Entity Integrity Constraint: A primary key value cannot be null Referential integrity constraints : The “foreign key “ must have a value that is already present as a primary key, or may be null Database Management Systems Specifying Constraints in SQL Other Restrictions on attribute domains: Default value of an attribute DEFAULT NULL is not permitted for a particular attribute (NOT NULL) CHECK clause Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);  Database Management Systems Specifying Constraints in SQL NOT NULL Because SQL allows NULLs as attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute. This is always implicitly specified for the attributes that are part of the primary key of each relation, but it can be specified for any other attributes whose values are required not to be NULL Example:  Database Management Systems Specifying Constraints in SQL DEFAULT It is also possible to define a default value for an attribute by appending the clause DEFAULT to an attribute definition. The default value is included in any new tuple if an explicit value is not provided for that attribute If no default clause is specified, the default default value is NULL for attributes that do not have the NOT NULL constraint. Example: Database Management Systems Specifying Constraints in SQL PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY; UNIQUE clause Specifies alternate (secondary) keys (called CANDIDATE keys in the relational model). Dname VARCHAR(15) UNIQUE; Database Management Systems Specifying Constraints in SQL FOREIGN KEY clause Default operation: reject update on violation Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for “relationship” relations  Database Management Systems Specifying Constraints in SQL Using the Keyword CONSTRAINT CREATE TABLE EMPLOYEE ( … , Name a constraint Dno INT NOT NULL DEFAULT 1, Useful for later altering CONSTRAINT EMPPK PRIMARY KEY (Ssn), CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) REFERENCES The general structure of the SQL EMPLOYEE(Ssn) CONSTRAINT is defined as: ON DELETE SET NULL ON UPDATE CASCADE, The CONSTRAINT keyword is followed CONSTRAINT EMPDEPTFK by a constraint name followed by a FOREIGN KEY(Dno) REFERENCES column or a list of columns. DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE); Database Management Systems Example for Specifying Constraints in SQL Database Management Systems Specifying Constraints in SQL CREATE TABLE DEPT_LOCATIONS ( Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL DEFAULT ‘Banglore’, PRIMARY KEY (Dnumber, Dlocation), Constraint “fk_dno ” FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ); Referential actions FOREIGN KEY (foreign_key_columns) REFERENCES parent_table(parent_key_columns) ON UPDATE action ON DELETE action;  Database Management Systems Referential actions Delete actions of rows in the parent table If you delete one or more rows in the parent table, you can set one of the following actions: ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table. ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table. ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable. ON DELETE SET DEFAULT SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified. By default, SQL Server applies ON DELETE NO ACTION if you don’t explicitly specify any action. Database Management Systems Referential actions Update action of rows in the parent table If you update one or more rows in the parent table, you can set one of the following actions: ON UPDATE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table. ON UPDATE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated. ON UPDATE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute. ON UPDATE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated. Database Management Systems Specifying Constraints on Tuples Using CHECK Additional Constraints on individual tuples within a relation are also possible using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK (Dept_create_date 2; Output:  Database Management Systems Order By clause ORDER BY clause is a simple keyword that can be used with SQL aggregate functions, HAVING clause, or GROUP BY clause. The main function of the ORDER BY clause is to sort the result in either ascending or descending order. It means, sorting the result requires an ORDER BY clause. This can be done using either of the two keywords. ASC – For sorting results in ascending order. DESC – For sorting results in descending order. If we do not place either “ASC or DESC” at the end of the query, the data is sorted in ascending order by default.  Database Management Systems Order By clause Example1: List the employees based on descending order of their salary. Query: select fname, lname, salary from EMPLOYEE order by salary desc; Example2: List the employees based on ascending order of their salary Query: select fname, lname, salary from EMPLOYEE order by salary asc; Output1: Output2:  Database Management Systems Rename SQL allows renaming relations and attributes using the as clause: old-name as new-name Example: Find the annual salary of employees. Query: select fname,lname, salary*12 as Annual_Income from EMPLOYEE; Output: NOTE: Keyword as is optional and may be omitted instructor as T ≡ instructor T  Database Management Systems String comparison SQL includes a string-matching operator for comparisons on character strings. The operator LIKE uses patterns that are described using two special characters: percent ( % ): The % character matches any substring. underscore ( _ ): The _ character matches any character. Example: Retrieve all employees whose address is in Houston, Texas. Query: SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE '%Houston,TX%'; Output: Note: Match the string “100%” like '100 \%' escape '\’ in that above we use backslash (\) as an escape character  Database Management Systems String comparison Examples: Find all employees who were born during the 1960s. Query: SELECT Fname, Lname, bdate FROM EMPLOYEE WHERE Bdate LIKE '_ _6_ _ _ _ _ _ _'; Output: THANK YOU [email protected] Database Management Systems Set Operations and Null Values Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. Database Management Systems Unit 2 overview: 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Additional Basic Operations 7. Set Operations and Null Values 8. Aggregate Functions 9. Nested Subqueries 10. Database Modification 11. Join expressions 12. Integrity Constraints, Data Types, and Schemas 13. Index definitions, Authorization 14. Functions, Procedures, Cursors 15. Triggers and Recursive Queries Database Management Systems SET Operators in SQL SQL Set operations are used to combine two or more SQL SELECT statements. Types of Set Operations: 1.Union 2.Union All 3.Intersect 4.Intersect All 5.Except 6.Except All Database Management Systems Union 1. Union The SQL UNION operation is used to combine the results of two or more SQL SELECT queries. In the UNION operation, the number of columns and datatypes must be the same in both the tables on which the UNION operation is being applied. It eliminates duplicate rows from its result set. Syntax: SELECT column_name FROM table1 UNION SELECT column_name FROM table2; Database Management Systems Example Consider the relation: student_course(name, course, grade) consisting of records of grades obtained by students in various courses. The first image displays students and the grades obtained by them in the course ‘Physics’ The second image displays students and the grades obtained by them in ‘Mathematics’ Further on, these tables will be used to demonstrate the usage of set operations Database Management Systems Union Query : Output : SELECT grade FROM student_course WHERE course = 'Physics' UNION SELECT grade FROM student_course WHERE course = 'Mathematics'; Database Management Systems Union All 2. Union All The UNION ALL operation is similar to the UNION operation. It returns the set without removing duplicates and sorting the data. If a particular value is present c1 times in the first set(or relation) and c2 times in the second set(or relation), the number of duplicates or copies present in the result would be c1 + c2. Syntax: SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2; Database Management Systems Union All Query: Output : SELECT grade FROM student_course WHERE course = 'Physics' UNION ALL SELECT grade FROM student_course WHERE course = 'Mathematics'; Database Management Systems Union All Query: Output : SELECT distinct grade FROM student_course WHERE course = 'Physics' UNION ALL SELECT distinct grade FROM student_course WHERE course = 'Mathematics'; Notice the difference between the results of the two UNION ALL operations. This is due to the inclusion of the DISTINCT keyword. DISTINCT eliminates any duplicates and hence, the output of each SELECT statement has no duplicates. However, due to the usage of UNION ALL, duplicates are not eliminated in the result of the UNION ALL operation. Database Management Systems Intersect 3.. Intersect It is used to combine two SELECT statements. The INTERSECT operation returns common rows from both the SELECT statements. In the Intersect operation, the number of columns and corresponding data types must be the same. It has no duplicates. Syntax: SELECT column_name FROM table1 INTERSECT SELECT column_name FROM table2; Database Management Systems Intersect Query : Output : SELECT grade FROM student_course WHERE course = 'Physics' INTERSECT SELECT grade FROM student_course WHERE course = 'Mathematics'; Database Management Systems Intersect All 4.. Intersect All INTERSECT ALL is similar to the INTERSECT operation. It helps retain duplicates. If a particular value is present c1 times in the first set(or relation) and c2 times in the second set(or relation), the number of duplicates or copies present in the result would be min(c1,c2). Syntax: SELECT column_name FROM table1 INTERSECT ALL SELECT column_name FROM table2; Database Management Systems Intersect All Query: Output : SELECT grade FROM student_course WHERE course = 'Physics' INTERSECT ALL SELECT grade FROM student_course WHERE course = 'Mathematics'; Database Management Systems Except 5.. Except It combines the result of two SELECT statements. EXCEPT operator is used to display the rows that are present in the first query but absent in the second query. It has no duplicates Syntax: SELECT column_name FROM table1 EXCEPT SELECT column_name FROM table2; Database Management Systems Except Query : Output : SELECT grade FROM student_course WHERE course = 'Physics' EXCEPT SELECT grade FROM student_course WHERE course = 'Mathematics'; SELECT grade FROM student_course WHERE course = 'Mathematics' EXCEPT SELECT grade FROM student_course (Empty Set) WHERE course = 'Physics'; Database Management Systems Except All 6.. Except All EXCEPT ALL is similar to the EXCEPT operation. It helps retain duplicates. If a particular value is present c1 times in the first set(or relation) and c2 times in the second set(or relation), the number of duplicates or copies present in the result would be max(c1-c2,0) i.e. c1 – c2 if the difference is positive else 0 (the value itself will not be present) Syntax: SELECT column_name FROM table1 EXCEPT ALL SELECT column_name FROM table2; Database Management Systems Except All Query : Output : SELECT grade FROM student_course WHERE course = 'Physics' EXCEPT ALL SELECT grade FROM student_course WHERE course = 'Mathematics'; SELECT grade FROM student_course WHERE course = 'Mathematics' EXCEPT ALL SELECT grade FROM student_course WHERE course = 'Physics'; Database Management Systems Null values - Introduction SQL has various rules for dealing with NULL values. Generally, NULL has one of the three representations: 1. Unknown value: value exists but is not known, or it is not known whether or not the value exists. For example, when a person’s date of birth is not known, it is represented as NULL in the database. 2. Unavailable or withheld value: value exists but is purposely withheld. For example, a person might not want to disclose his/her date of birth. 3. Not applicable attributes: the attribute does not apply to this tuple or is undefined for this tuple. For example, an attribute LastCollegeDegree would be NULL for a person who has no college degrees. Database Management Systems Null values - Introduction It is often not possible to determine the intended meaning of NULL. Hence, SQL does not distinguish among the different meanings of NULL. In general, each individual NULL value is considered to be different from every other NULL value in the various database records. Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations. Result of an arithmetic operation ( +,-,* or /) is NULL if any of the input values are NULL. Database Management Systems Null values in Arithmetic operations Example: SELECT Hours FROM WORKS_ON; SELECT Hours+10 from WORKS_ON; The first image shows a part of the Hours column of the WORKS_ON relation. The second image shows the result of adding 10 to each value in the Hours column. Notice how the result of adding 10 to NULL results in NULL. Database Management Systems Null values in comparison operations Comparisons involving NULL values pose a greater difficulty. To handle comparisons involving NULL values in SQL, a third logical value UNKNOWN, in addition to TRUE and FALSE, is used. For example, the expression (1 < NULL) would be evaluated as UNKNOWN. Similarly, NOT(1< NULL) would be evaluated as UNKNOWN as well. In other words, any comparison operation involving NULL values would result in an UNKNOWN. Database Management Systems Three-valued logic truth table Truth table for NOT, AND, OR operators: Database Management Systems Is null and is not null If the WHERE predicate evaluates to FALSE or UNKNOWN for a tuple, the tuple is not added to the result. When a JOIN condition is specified, tuples with NULL values for the join attributes are not included in the result (unless it is an OUTER JOIN) SQL uses comparison operators IS and IS NOT to check if an attribute value is NULL or not, respectively Example: Retrieve the names of all employees who do not have supervisors. Query: SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL; Output: Database Management Systems Is unknown and is not unknown SQL allows us to test whether the result of a comparison is UNKNOWN , rather than TRUE or FALSE, by using the clauses IS UNKNOWN and IS NOT UNKNOWN. Example: Retrieve the ssn of employees and their corresponding project numbers which satisfy the condition “Hours > 10 is unknown”. Query: SELECT Essn, Pno FROM WORKS_ON WHERE Hours > 10 IS UNKNOWN; Output: Only those tuples whose Hours attribute value is NULL will return UNKNOWN on performing the comparison.  Database Management Systems Null values in distinct clause When a query uses the SELECT DISTINCT clause, duplicate tuples are eliminated. For this purpose, when comparing values of corresponding attributes from two tuples, the values are treated as identical if either both are non NULL and equal in value, or both are NULL. For example, the tuples (‘A’, NULL) and (‘A’, NULL) are treated as identical. Hence, DISTINCT retains only one copy of such distinct tuples. NOTE: Treatment of NULL above is different from the way NULL values are treated in predicates, where a comparison “NULL=NULL” would return UNKNOWN, rather than TRUE. The approach of treating tuples as identical if they have the same values for all attributes, even if some of the values are NULL, is also used for the set operations UNION, INTERSECTION, and EXCEPT. THANK YOU [email protected] Database Management Systems Aggregate Functions Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. Database Management Systems Unit 2 overview 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Additional Basic Operations 7. Set Operations and Null Values 8. Aggregate Functions 9. Nested Subqueries 10. Database Modification 11. Join expressions 12. Integrity Constraints, Data Types, and Schemas 13. Index definitions, Authorization 14. Functions, Procedures, Cursors 15. Triggers and Recursive Queries Database Management Systems Aggregate functions - Introduction Aggregate functions are used to summarize information from multiple tuples into a single- tuple summary. Grouping is used to create subgroups of tuples before summarization. Built-in aggregate functions: COUNT - returns the number of tuples or values specified in a query SUM – returns the sum of a set (or multiset) of numeric values MAX – returns the maximum value from a set(or multiset) of numeric values MIN - returns the minimum value from a set(or multiset) of numeric values AVG – returns the average of a set(or multiset) of numeric values NOTE: Multiset is a collection similar to a set wherein the multiplicity of every value is greater than or equal to 1. In other words, it is a set that can contain duplicate values. Database Management Systems Aggregate functions - Example The functions MAX and MIN can also be used with attributes that have non-numeric domains if the domain values have a total ordering among one another. DATE, TIME, TIMESTAMP, and alphanumeric strings are examples of such non-numeric domains. Total order - For any two values in the domain, it can be determined that one appears before the other in the defined order. Example: Find the sum of salaries of all employees, the maximum salary, the minimum salary, and the average salary Query: SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM EMPLOYEE; Output: Database Management Systems As clause The AS clause can be used to rename column names to more meaningful ones. Query: SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal FROM EMPLOYEE; Output:  Database Management Systems Aggregate functions - Example Example: Find the sum of salaries of all employees from the ‘Research’ department, as well as the maximum salary, the minimum salary, and the average salary in this department. Query: SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber) WHERE Dname = ‘Research’; Output: Database Management Systems Count - Example Example: Retrieve the total number of employees in the company. Query: SELECT COUNT(*) FROM EMPLOYEE; Output: Example: Find the number of employees in the ‘Research’ department. Query: SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNO = DNUMBER AND DNAME = 'Research'; Output: In general, COUNT(*) can be used to find the number of rows present in the result of the query.  Database Management Systems Use of distinct The COUNT function can be used to count the number of values in a column as well Example: Count the number of distinct salary values in the database. Query: SELECT COUNT(DISTINCT Salary) FROM EMPLOYEE; Output: Note the usage of the DISTINCT in the previous query. To understand its significance, we shall run the same query without using DISTINCT. Query: SELECT COUNT(Salary) FROM EMPLOYEE; Output: Database Management Systems Use of distinct SQL, by default, does not eliminate duplicates in the result of a query. Duplicate retention can be specified using the ALL keyword If duplicate elimination is required, it must be explicitly specified by using the DISTINCT keyword. Notice how there are three copies of the same value in the Salary column. The total number of values in the Salary column equals 8, and hence COUNT (Salary) returns 8 (no duplicate elimination). By using the DISTINCT keyword, we are specifying duplicate elimination. Hence, out of the three copies, two are discarded and one is retained. Therefore, COUNT(DISTINCT Salary) returns 6 ( 8 – 2 (discarded copies) )  Database Management Systems Aggregate functions – Handling of Null values In general, when an aggregate function is applied to a collection of values, NULL values are discarded before the calculation. If the collection becomes empty because all the values are NULL, COUNT returns zero and other aggregate functions return NULL. Example: Find the average number of hours that employees work on each project. Query: SELECT Pno, AVG(Hours) as avg_hours FROM WORKS_ON GROUP BY Pno; Output: Database Management Systems Aggregate functions – Handling of Null values Let’s have a look at WORKS_ON tuples that correspond to Pno = 20: Notice how one of the entries in the Hours column is NULL. When AVG is applied to the Hours column for Pno = 20, it discards the NULL value and considers the other two values. Therefore, the average computation for Pno = 20 is as follows: (10 + 15)/ 2 = 12.5 Database Management Systems Aggregate functions in nested queries Aggregate functions can also be used in selection conditions involving nested queries. They can be used in correlated nested queries, which are then placed in the WHERE clause of an outer query. Example: Retrieve the names of all employees who have two or more dependents. Query: SELECT Lname, Fname FROM EMPLOYEE WHERE ( SELECT COUNT(*) FROM DEPENDENT WHERE Ssn = Essn ) >= 2; Output: Database Management Systems Aggregate functions in nested queries The correlated query returns the count of dependents for every employee, which is then checked if it is greater than or equal to 2. All tuples from the EMPLOYEE table that satisfy the said condition are then selected. SQL has two functions that are applied to Boolean values – SOME and ALL. The SOME function returns True if at least one element in the collection is True. The ALL function returns True if all the elements in the collection are True.  Database Management Systems Grouping In many cases, we would want to apply the aggregate functions to subgroups of tuples in a relation where the subgroups are based on some attribute values. For example, we may want to find the average salary of employees in each department or the number of employees who work on each project. In such cases, we need to partition the relation into nonoverlapping subsets(or groups)of tuples. The attributes used to partition a relation into groups are called grouping attributes. All tuples that have the same values for all grouping attributes are placed in the same group. If NULLs exist in the grouping attribute, then a separate group is created for all tuples with a NULL value in the grouping attribute. Aggregate functions can be applied independently to each group to produce summary information about each group. Database Management Systems Group By clause Grouping can be done in SQL using the GROUP BY clause. The GROUP BY clause specifies the grouping attributes. They should be present in the SELECT clause as well so that the value of the grouping attributes will appear in the result relation along with the aggregated values. Example: For each department, retrieve the department number, the number of employees in the department, and their average salary. Query: SELECT Dno, COUNT(*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno; Output: Database Management Systems Group By clause - Example Query: SELECT Dno, COUNT(*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno; Output: In the above query, Dno is the grouping attribute. The EMPLOYEE relation is partitioned into groups in such a way that each group has tuples with the same value of Dno. Hence, each group consists of employees who work for the same department. The COUNT and AVG functions are applied to each group independently and are displayed in the result relation. The SELECT clause must contain only the grouping attributes and aggregate functions applied on each group of tuples. Database Management Systems Group By clause – How it works An illustration of how grouping works is provided for the previous example Database Management Systems Group By clause along with Join Example: For each project, retrieve the project number, the project name, and the number of employees who work on that project. Query: SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON WHERE Pnumber = Pno GROUP BY Pnumber, Pname; Output: The above query is an example of using GROUP BY along with JOIN. In such statements, relations are first joined using the condition specified in the WHERE clause. Grouping and aggregation are then performed on the result of this joining Database Management Systems Having clause There are some scenarios wherein we would want to retrieve the values of aggregate functions for groups that satisfy certain conditions. SQL provides the HAVING clause, which can appear in conjunction with a GROUP BY clause, for this purpose. The HAVING clause is used to specify conditions for groups of tuples, whereas the WHERE clause is used to specify conditions for individual tuples. HAVING provides a condition on the summary information regarding the group of tuples associated with each value of the grouping attributes. Only the groups that satisfy the condition are retrieved in the result of the query. Database Management Systems Having clause - Example Example: For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project. Query: SELECT Pnumber, Pname, COUNT(*) FROM PROJECT, WORKS_ON WHERE Pnumber = Pno GROUP BY Pnumber, Pname HAVING COUNT(*) > 2; Output: Database Management Systems Having clause – How it works An illustration of how the HAVING clause works is provided for the previous example Database Management Systems Group By clause - Example Example: For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. Query: SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pnumber = Pno AND Ssn = Essn AND Dno = 5 GROUP BY Pnumber, Pname; Output: Database Management Systems Where predicate and Having predicate We must be extra careful when two different conditions apply – one specified in the WHERE clause and the other specified in the HAVING clause. The rule is that the condition specified in the WHERE clause is executed first, to select the individual tuples, and then the HAVING clause is executed, to select groups of tuples. This means that the HAVING clause groups only those tuples that are selected by the WHERE clause. Consider the following example: Retrieve the count of number of employees whose salary exceeds $40,000 in each department, but only for those departments where more than two employees work. Database Management Systems Where predicate and Having predicate Query: SELECT Dno, COUNT(*) FROM EMPLOYEE WHERE Salary>40000 GROUP BY Dno HAVING COUNT(*) > 2; At the outlook, this might seem like the correct query for the given question. However, closer inspection reveals that this might not be the case. As stated earlier, the WHERE clause is executed first and its result is what the HAVING clause works on. In other words, the WHERE clause first returns only those employees whose salary exceeds $40,000. This is followed by the grouping of the filtered relation and the selection of groups by the HAVING clause condition. Hence, only departments with more than two employees who each earn more than $40,000 are selected, which is not the expected result. Database Management Systems Where predicate and Having predicate The correct query is given below Query: SELECT Dno, COUNT(*) FROM EMPLOYEE WHERE Salary>40000 AND Dno IN ( SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT(*) > 2) GROUP BY Dno; Output: This shows that there is only one employee with a salary greater than $40,000 belonging to a department(here department 4) with more than two employees. All other departments either have two or fewer employees or don’t have employees whose salary exceed $40,000. Database Management Systems Where predicate and Having predicate Let’s try executing the incorrect query Query: SELECT Dno, COUNT(*) FROM EMPLOYEE WHERE Salary>40000 GROUP BY Dno HAVING COUNT(*) > 2; Output: The output of the query is an empty relation. This is because the query returns only those departments with more than two employees whose salaries exceed $40,000. From the output of the corrent query, we can see that there are no departments that satisfy this condition. Hence, an empty relation is returned. THANK YOU [email protected] Database Management Systems Nested Queries - 1 Database Management Systems Unit 2: Relational Model and Database Design Slides adapted from Author Slides of “Database System Concepts”, Silberschatz, H Korth and S Sudarshan, McGrawHill, 7th Edition, 2019. And Author Slides of Fundamentals of Database Systems”, Ramez Elamsri, Shamkant B Navathe, Pearson, 7th Edition, 2017. Database Management Systems Unit 2 overview 1. Relational Query language, Algebra & Unary Operations 2. Binary Operations 3. Set Operations, Equivalent Queries 4. Aggregate Functions and Grouping 5. SQL overview, Data definition, Structure of SQL queries 6. Database Modification 7. Additional Basic Operations 8. Set Operations and Null Values 9. Aggregate Functions 10. Nested Subqueries 11. Join expressions 12. Index definitions, Authorization 13. Functions, Procedures 14. Triggers and Recursive Queries Database Management Systems Nested queries - Introduction A nested query can be defined as a complete SELECT-FROM-WHERE block within another SQL query. The other query is called the outer query. Nested queries are used to fetch existing values in the database that can be used by the outer query for comparison. They can appear in the WHERE clause, FROM clause, SELECT clause, and other SQL clauses as needed. SQL provides a comparison operator IN, which checks if a value is present in a set( or multiset) of values. It returns True if the value is present in the set and False otherwise. It essentially checks for Set membership. The NOT IN operator returns True if the value is not present in the set and False otherwise. Database Management Systems Nested queries - Example Example: Retrieve the list of project numbers of projects that have an employee with the last name ‘Smith’ involved either as a manager or as a worker. Query: SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN ( SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith’ ) OR Pnumber IN ( SELECT Pno FROM WORKS_ON, EMPLOYEE WHERE Essn = Ssn AND Lname = 'Smith’ ); Output: Database Management Systems Nested queries – How it works Let’s understand how this query works. Consider the first sub-query: ( SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith’ ) This query selects the project number of all projects handled by departments whose manager’s last name is ‘Smith’. On executing this subquery, we obtain an empty set. The output is shown below: (Empty Set) This is because there is no manager whose last name is ‘Smith’. Database Management Systems Nested queries - How it works Consider the second subquery: ( SELECT Pno FROM WORKS_ON, EMPLOYEE WHERE Essn = Ssn AND Lname = 'Smith’ ); This subquery selects the project number of all projects that employees with the last name ‘Smith’ work on. The output of this subquery is shown below: Database Management Systems Nested queries – How it works ( SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE () WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith’ ) ( SELECT Pno FROM WORKS_ON, EMPLOYEE (1, 2) WHERE Essn = Ssn AND Lname = 'Smith’ ); By plugging in the results of the nested queries in the outer query, we obtain the equivalent query: SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN () OR Pnumber IN (1, 2) Database Management Systems Nested queries In general, a nested query returns a table(relation) which is a set(or multiset) of tuples. However, there are certain scenarios wherein a nested query returns a table with a single attribute and a single tuple. In such cases, we can treat the result as a scalar i.e. a single value, and apply operations applicable to singular values. For example, the equal operator (=) is not applicable when one of the operands is a set of values( which is usually what a nested query returns). However, when a nested query returns a single value, we can use the = operator instead of the IN operator. SQL allows the use of tuples of values in comparisons by placing them within parentheses. Database Management Systems Nested queries - Example Example: Retrieve the ssn of all employees who work on the same (project, hours) combination on some project that employee ‘John Smith’ (ssn = ‘123456789’) works on. Query: SELECT DISTINCT Essn FROM WORKS_ON WHERE (Pno, Hours) IN ( SELECT Pno, Hours FROM WORKS_ON WHERE Essn = '123456789’ ); Output: The IN operator compares the subtuple of values in parentheses (Pno, Hours) within each tuple in WORKS_ON with the set of type-compatible tuples produced by the nested query. Database Management Systems Additional set comparison operators In addition to the IN and NOT IN operators, a number of other comparison operators can be used to compare a single value to a set or multiset of values. These operators are of the form – comp_op [ANY | ALL | SOME] comp_op denotes comparison operators such as =, (not equals), >, >=, ALL (30000.00, 40000.00, 25000.00, 38000.00) For every tuple in the EMPLOYEE relation, the value in the salary attribute is compared with the output of the nested query using > ALL. The WHERE clause evaluates to True if the value in the salary column for that particular tuple is greater than all the values in the nested query output. A table with the last name, first name, and salary of employees is shown to verify the result. The same can be achieved using the MAX function.  Database Management Systems Additional set comp. operators - Example Example: Retrieve the last and first names of all employees whose salary is greater than the salary of at least one employee in department 5 Query: SELECT Lname, Fname FROM EMPLOYEE WHERE Salary > SOME ( SELECT Salary FROM EMPLOYEE WHERE Dno = 5 ); Output: Notice how there are more employees who satisfy the given condition as compared to the previous example. This is because > SOME( and > ANY) evaluates as True if the employee salary in the outer query is greater than even one value in the nested query output.  Database Management Systems Additional set comp. operators The previous example could be solved using the MIN function as well. = SOME or = ANY is equivalent to the IN operator. ALL is equivalent to the NOT IN operator. Database Management Systems Levels of nesting queries In general, we can have several levels of nested queries. Ambiguity among attribute names arises if attributes belonging to the relation in the FROM clause of the outer query and the relation in the FROM clause of the nested query have the same name. The rule is that a reference to an unqualified attribute refers to the relation declared in the innermost query For example, consider the query: The relation PROJECT has been referenced both in the outer query and the inner query. The attributes Pnumber and Dnum in the nested query correspond to the PROJECT relation in the nested query and not the one in the outer query. Database Management Systems Levels of nesting queries To refer to an attribute of a relation specified in the outer query, we specify and refer to an alias for that relation. Consider the following query Here E is the alias for the relation EMPLOYEE. There are two attributes named Sex – one in the EMPLOYEE table and another in the DEPENDENT table. We must qualify E.Sex because it refers to the Sex attribute of the EMPLOYEE relation from the outer query, and not to the Sex attribute of the DEPENDENT relation. Database Management Systems Levels of nesting queries If there were any unqualified references to Sex in the nested query, they would refer to the Sex attribute of the DEPENDENT relation. However, we would not have to qualify attributes such as Ssn (of the EMPLOYEE relation) if they appeared in the nested query because the DEPENDENT relation does not have an attribute called Ssn, so there is no ambiguity. It is generally advisable to create tuple variables (aliases) for all the tables referenced in an SQL query to avoid potential errors and ambiguities Database Management Systems Correlated Nested queries A correlated nested query can be defined as a nested query in which the WHERE clause references attributes of a relation declared in the outer query. A nested query is evaluated once for each tuple (or combination of tuples) of the relation in the outer query. Example: Retrieve the name of each employee who has a dependent who is of the same sex as the employee. Query: SELECT E.Fname, E.Lname FROM EMPLOYEE AS E WHERE E.Ssn IN ( SELECT D.Essn FROM DEPENDENT AS D WHERE E.Gender = D.Gender ); Output: Database Management Systems Correlated Nested queries – How it works? For each EMPLOYEE tuple, the nested query is evaluated, and the Essn values of all DEPENDENT tuples with the same Gender as that of the EMPLOYEE tuple are retrieved. If the Ssn value of the EMPLOYEE tuple is in the result of the nested query, the EMPLOYEE tuple is selected In general, a query written with nested SELECT-FROM-WHERE blocks and using the = or IN comparison operators can always be expressed as a single block query. For the current example, the equivalent query without a nested query is given below: SELECT E.Fname, E.Lname FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.Ssn = D.Essn AND E.Gender = D.Gender; Database Management Systems Exists and Unique EXISTS and UNIQUE are Boolean functions that can be used in the WHERE clause The EXISTS function in SQL is used to check whether the result of a nested query is empty (contains no tuples) or not. The result of EXISTS is True if the nested query result contains at least one tuple, and False if the nested query result contains no tuples. On the other hand, NOT EXISTS returns True if there are no tuples in the result of the nested query, and False if the nested query result contains at least one tuple. EXISTS and NOT EXISTS are typically used in conjunction with a correlated nested query. Database Management Systems Exists - Example Example: Retrieve the name of each employee who has a dependent who is of the same sex as the employee. Query: SELECT E.Fname, E.Lname FROM EMPLOYEE AS E WHERE EXISTS ( SELECT * FROM DEPENDENT AS D WHERE E.Ssn = D.Essn AND E.Gender = D.Gender); Output: For each EMPLOYEE tuple, the nested query is evaluated and it returns all DEPENDENT tuples with the same Essn and Gender as the EMPLOYEE tuple. If at least one tuple exists in the result of the nested query, then the EMPLOYEE tuple under consideration is selecte