Question

1. What is an Inner joins vs. outer joins?
2. What is a Natural Join vs. Cross join?
3. What is a Subquery?

4, What is a Transaction? Show an example.

5. What is a purpose of Lock Manager when a RDBMS processes transactions?

Answer 1

As per rules, i can answer 4 sub-parts. If this answer is helpful plz upvote.

Inner join vs outer join -

Inner join - it creates new table by joining column values of two tables. Basically, inner join is the intersection of two tables. It links two tables based on a common value. It shows the data that exists in both tables.

How to join :

Use a keyword called JOIN

After the first table in the FROM clause

Add second table, then specify the columns

SELECT... FROM tableA

JOIN tableB ON tableA.col=tableB.col

Use INNER JOIN or JOIN

INNER is optional

Same values-

Columns need to refer to the same concept

E.g, department ID in both tables

Dont join the employee ID to the department ID:different concepts

After joining :

Then select columns from both tables.

Syntax:

SELECT table 1.column1,table2.column2...

FROM table1

INNER JOIN Table2

On table 1.common_field=table2.common_field;

Outer join - An outer joins returns all rows from one of the tables, along with matching information from another table.

Left outer join -it keeps every tuple in the first or left relation.

Syntax: SELECT columns FROM table1 LEFT[outer] JOIN table2 ON table1.column=table2

Right outer join - it keeps every tuple in the second or right relation.

Syntax: SELECT column-names. FROM table-name1 RIGHT OUTER JOIN table-name2.ONcolumn-name1=column-name2 WHERE condition

Full join - it keeps tuples from both left and right relation.

syntax is:

SELECT table1. Column1, table2. Column2..

FROM table 1

FULL JOIN table2

ON table1. Common_field =table2. Common_field ;

Natural vs cross join-

Natural join -

Natural join joins two tables based on same attribute name and datatypes. It selects rows from the two tables that have equal values in all matched columns.

Syntax:

SELECT *FROM

Tablename1 NATURAL JOIN tablename2;

Cross join-

Cross join is used to combine each row of the first table with each row of the second table.it is also called Cartesian product.

Syntax:

SELECT [colmn names]

FROM [table 1]

CROSS JOIN [table2]

Subquery- A subquery is a query within another SQL query. (inner query or a nested query)

The result returned by the inner query will be used as an input to outer query. We can use comparative operators in queries.

Types of subquery :

Single row subquery

Multiple row subquery

Multiple column subquery

Corelated subqueries

Nested subqueries

The sub query can be nested inside a INSERT statement, DELETE statement, SELECT statement, UPDATE statement.

4.Transaction - It is a program unit whose execution may change the content of database.it is a exchange of information in database. It can manipulate methods itself. Transaction is the major part of our lifestyle as banking system, financial system depends on transaction system.

Example-

company takes out a loan.

Sales of goods, finance and services for cash or credit.

Purchasing of goods through credit cards.