Computer Science Test - 5

Concept

Cartesian Product

It is also an operator that works on two sets. It is sometimes called the CROSS PRODUCT or CROSS JOIN. It combines the tuples of one relation with all the tuples of the other relation.

Syntax 

table_name1 × table_name2

Example

Table 1

Table 2

Cartesian Product = Table1 × Table2

Important Points

Natural Join

It is also known as equijoin. Such joins result in two attributes in the resulting relation having exactly the same value. A `natural join' will remove the duplicate attribute(s). the natural join will require that the attributes have the same name to identify the attribute(s) to be used in the join. 

Outer Joins

An outer join retains the information that would have been lost from the tables, replacing missing data with nulls.

There are three forms of the outer join, depending on which data is to be kept.

• LEFT OUTER JOIN - keep data from the left-hand table
• RIGHT OUTER JOIN - keep data from the right-hand table
• FULL OUTER JOIN - keep data from both tables

The correct answer is option 3.

Concept:

Projection(π):

The projection eliminates all attributes of the input relation but those mentioned in the projection list. The projection method defines a relation that contains a vertical subset of Relation.

This helps to extract the values of specified attributes to eliminate duplicate values. (pi) symbol is used to choose attributes from a relation. This operator helps you to keep specific columns from a relation and discards the other columns.

Hence the correct answer is Project.

Additional InformationSELECT (σ):

The SELECT operation is used for selecting a subset of the tuples according to a given selection condition. Sigma(σ)Symbol denotes it. It is used as an expression to choose tuples that meet the selection condition. The select operator selects tuples that satisfy a given predicate.

Union operation (υ):

UNION is symbolized by ∪ the symbol. It includes all tuples that are in tables A or in B. It also eliminates duplicate tuples.

Join Operation(⋈):

Join operation is essentially a cartesian product followed by a selection criterion. Join operation denoted by ⋈.

The correct answer is option 2.

Concept:

Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output.

It uses operators to perform queries. An operator can be either unary or binary. They accept relations as their input and yield relations as their output. Relational algebra is performed recursively on relation and intermediate results are also considered relations.

The fundamental operations of relational algebra are as follows:

• Select (σ)
• Project (∏)
• Union (∪)
• Set different (−)
• Cartesian product (Χ)
• Rename (ρ)

Hence the correct answer is procedural.

Key Points

Option 1:σMARKS>80 (STUDENT)

True, Find the number of students having marks above 80. A selection operator is used to select tuples from a relation based on some condition.

Option 2: ΠMarks<80 (STUDENT)

The projection operator is used to project particular columns from a relation but here condition is used so wrong statement. 

Option 3: σMARKS<80 (STUDENT)

Find the number of students having marks less than 80. A selection operator is used to select tuples from a relation based on some condition.

Option 4: ΠMarks>80 (STUDENT)

The projection operator is used to project particular columns from a relation but here condition is used so wrong statement. 

Hence the correct answer is σMARKS>80 (STUDENT).

Key Points

• Selection operator (σ): Selection operator is used to selecting tuples from a relation based on some condition.
• Projection Operator (∏): Projection operator is used to project particular columns from a relation.
• Rename(ρ): Rename operator is used to giving another name to a relation.
• Cross Product(X): Cross product is used to join two relations. 
• Union (U): Union operator when applied on two relations R1 and R2 will give a relation with tuples which are either in R1 or in R2. 
• Minus (-): Minus operator when applied on two relations as R1-R2 will give a relation with tuples which are in R1 but not in R2.

Hence the correct answer is Projection, Cartesian product, select.

The correct answer is option 1

EXPLANATION:

Self-Join:

A self-join is a join in which the table is joined with itself to get the appropriate results. In this case, it is necessary to ensure that the join statement defines an ALIAS name for both copies of the tables to avoid column ambiguity. Example:

Query:

SELECT A. Coursename AS Course, B. Coursename AS Prerequisite_Course

FROM Course A, Course B

WHERE, A.Precourse = B.CourseID;

OUTPUT

Additional Information

Join:

Join operation is used to combine related tuples from two relations into single tuples. Join operation can be stated in terms of the Cartesian product followed by SELECT operation.

Join condition form: ANDAND……AND

Outer Join:

Let R and S be two relations on which join operation is performed. Consider R # S where # is an operator which could be left outer join, right outer join or full outer join. R is the left relation and S is the right relation. There are various variations of join operations such as equi-join, natural join, theta join, inner join, outer join.

Three forms of outer join as follows:

1. The left outer join preserves tuples only in the relation R named before the left outer join operation.
2. The right outer join preserves tuples only in the relation S named after the right outer join operation.
3. The full outer join preserves tuples in R and S relations.

Equi Join:

Join operation with equality condition is known as equi-join.

Concept:

⟕ is a symbol of Left outer join

• Left outer join ⟕ : If we joining two tables for example Table 1 and Table 2 the It gives all tuples of table 1 in the result set.
• Right Outer join ⟖ : If we joining two tables for example Table 1 and Table 2 the It gives all tuples of table 2 in the result set. 
• Full join ⟗ : If we joining two tables for example Table 1 and Table 2 the It gives  all tuples of Table 1 and all tuples of Table 2 in the result set

Important Point :

Below is the Syntax of Joins :

• JOIN

SELECT column-names FROM table1 JOIN table2 ON column.table1 = column.table2 WHERE condition

• INNER JOIN

SELECT column-names FROM table1 INNER JOIN table2  ON column.table1 = column.table2 WHERE condition

• LEFT JOIN  

SELECT column-names FROM table1 LEFT JOIN table2  ON column.table1 = column.table2 WHERE condition

• LEFT OUTER JOIN  

SELECT column-names FROM table1 LEFT OUTER JOIN table2  ON column.table1 = column.table2 WHERE condition

• RIGHT JOIN

SELECT column-names FROM table1 RIGHT JOIN table2  ON column.table1 = column.table2 WHERE condition

• RIGHT OUTER JOIN

SELECT column-names FROM table1 RIGHT OUTER JOIN table2  ON column.table1 = column.table2 WHERE condition

• FULL JOIN

SELECT column-names FROM table1 FULL JOIN table2  ON column.table1 = column.table2

WHERE condition

• FULL OUTER JOIN

SELECT column-names FROM table1 FULL OUTER JOIN table2  ON column.table1 = column.table2 WHERE condition

• Self JOIN

SELECT column-names FROM table-name T1 JOIN table-name T2 WHERE condition

T1 and T2 are different table Aliases for the same table

SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. It is used for combining column from two or more tables by using values common to both tables.

INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the SQL query.

Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

LEFT Outer Join

The left outer join returns a resultset table with the matched data from the two tables and then the remaining rows of the left table and null from the right table's columns.

RIGHT Outer Join

The right outer join returns a resultset table with the matched data from the two tables being joined, then the remaining rows of the right table and null for the remaining left table's columns.

Full Outer Join

The full outer join returns a resultset table with the matched data of two table then remaining rows of both left table and then the right table.

π customer_name (σ  balance > 1000 (Deposit)) will be the correct answer

Important Points

Project Operation (π)

It projects column(s) that satisfy a given predicate.

Notation − πA1, A2, An (r)

Where A1, A2 , An are attribute names of relation r.

Duplicate rows are automatically eliminated, as the relation is a set.

It selects tuples that satisfy the given predicate from a relation

Output: