module 3 of ooc is covered, Study notes of Object Oriented Programming

Download module 3 of ooc is covered and more Study notes Object Oriented Programming in PDF only on Docsity!

Module 3

Chapter 1: SQL- Advances Queries

Comparisons Involving NULL and Three-Valued Logic

 SQL has various rules for dealing with NULL values. NULL is used to represent a

missing value, but that it usually has one of three different interpretations value Example

 1. Unknown value : A Person’s date of birth is not known, so it is represented by Null in

the database.

 2. Unavailable or withheld value : A person has a home phone but does not want it to be

listed, so it is withheld and represented as NULL in the database.

 3. Not applicable attribute : An attribute College Degree would be NULL for a person

who has no college degrees because it does not apply to that person.

Nested Queries, Tuples, and Set/Multiset Comparisons

 Some queries require that existing values in the database be fetched and then used in a

comparison condition. Such queries can be conveniently formulated by using nested

queries, which are complete select-from-where blocks within the WHERE clause of

another query.

Nested Queries::Comparison Operators

 Other comparison operators can be used to compare a single value v to a set or multiset

V. The = ANY (or = SOME) operator returns TRUE if the value v is equal to some

value in the set V and is hence equivalent to IN.

 The two keywords ANY and SOME have the same effect. The keyword ALL can also

be combined with each of these operators. For example, the comparison condition (v >

ALL V) returns TRUE if the value v is greater than all the values in the set (or multiset)

V.

SELECT Lname, Fname

FROM EMPLOYEE

WHERE Salary > ALL ( SELECT Salary

FROM EMPLOYEE WHERE Dno=5 );

The EXISTS and UNIQUE Functions in SQL

EXISTS Functions  (^) The EXISTS function in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not. The result of EXISTS is a Boolean value TRUE if the nested query result contains at least one tuple, or FALSE if the nested query result contains no tuples.  (^) For example, the query to retrieve the name of each employee who has a dependent with the same first name and is the same gender as the employee can be written using EXISTS functions as follows: 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 AND E.Fname=D.Dependent_name )

UNIQUE Functions

 UNIQUE(Q) returns TRUE if there are no duplicate tuples in the result of query Q;

otherwise, it returns FALSE. This can be used to test whether the result of a nested

query is a set or a multiset.

Explicit Sets and Renaming of Attributes in SQL

 IN SQL it is possible to use an explicit set of values in the WHERE clause, rather

than a nested query. Such a set is enclosed in parentheses.

 Example: Retrieve the Social Security numbers of all employees who work on

project numbers 1, 2,or 3.

SELECT DISTINCT Essn

FROM WORKS_ON

WHERE Pno IN (1, 2, 3);

The EXISTS and UNIQUE Functions in SQL

Joined Tables in SQL and Outer Joins  (^) A JOIN is a means for combining fields from two tables by using values common to each. SQL specifies four types of JOIN.

1. INNER
   2. OUTER
2. EQUIJOIN and
3. NATURAL JOIN

INNER JOIN

 (^) An inner join is the most common join operation used in applications and can be regarded as the default join type.  (^) Inner join creates a new result table by combining column values of two tables (A and B) based upon the join- predicate (the condition).  (^) The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B) then return all records which satisfy the join predicate.  (^) Example: SELECT * FROM employee INNER JOIN department ON employee.dno = department.dnumber; Joined Tables in SQL and Outer Joins

 (^) CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. OUTER JOIN  (^) An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record-even if no other matching record exists. Outer joins subdivide further into  (^) Left outer joins  (^) Right outer joins  (^) Full outer joins Joined Tables in SQL and Outer Joins

Joined Tables in SQL and Outer Joins

Joined Tables in SQL and Outer Joins

MULTIWAY JOIN

 (^) It is also possible to nest join specifications; that is, one of the tables in a join may itself be a joined table. This allows the specification of the join of three or more tables as a single joined table, which is called a multiway join. Joined Tables in SQL and Outer Joins

Aggregate Functions in SQL

Grouping: The GROUP BY and HAVING Clauses  (^) Grouping is used to create subgroups of tuples before summarization. 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.  (^) 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).