DBMS-2

DBMS-Concepts

(Session 2)

By

V.Veda Sri

Asst.Professor

Dept. of Comp.Science

RBVRR Women's College

SQL JOIN

• In SQL, JOIN clause is used to combine the records from two or more tables in a database.

• Types of SQL JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN ,FULL JOIN.

1. INNER JOIN

• In SQL, INNER JOIN selects records that have matching values in both tables as long as the condition is satisfied. It returns the combination of all rows from both the tables where the condition satisfies.

• Syntax

  SELECT table1.column1, table1.column2, table2.column1,....   FROM table1    INNER JOIN table2   ON table1.matching_column = table2.matching_column;  

Query

_{SELECT EMPLOYEE.EMP_NAME,} 

_{PROJECT.DEPARTMENT   FROM EMPLOYEE}  

_{INNER JOIN PROJECT}  

_{ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;}  

2. LEFT JOIN

• The SQL left join returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL.

• Syntax

  SELECT table1.column1, table1.column2, table2.column1,....   FROM table1    LEFT JOIN table2   ON table1.matching_column = table2.matching_column;  

Query

_{SELECT EMPLOYEE.EMP_NAME,} 

_{PROJECT.DEPARTMENT}   

_{FROM EMPLOYEE}  

_{LEFT JOIN PROJECT}  

_{ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;}  

3. RIGHT JOIN

• In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and the matched values from the left table. If there is no matching in both tables, it will return NULL.

• Syntax

  SELECT table1.column1, table1.column2, table2.column1,....   FROM table1    RIGHT JOIN table2   ON table1.matching_column = table2.matching_column; 

Query

_{SELECT EMPLOYEE.EMP_NAME,} 

_{PROJECT.DEPARTMENT}   

_{FROM EMPLOYEE}  

_{RIGHT JOIN PROJECT   ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;}  

4. FULL JOIN

• In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables have all the records from both tables. It puts NULL on the place of matches not found.

• Syntax

  SELECT table1.column1, table1.column2, table2.column1,....   FROM table1    FULL JOIN table2   ON table1.matching_column = table2.matching_column;  

Query

_{SELECT EMPLOYEE.EMP_NAME,} 

_{PROJECT.DEPARTMENT}   

_{FROM EMPLOYEE}  

_{FULL JOIN PROJECT    ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;}  

Multiple Choice

_{Insert the missing parts in the JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables.}

_{SELECT *}

_{FROM Orders}

_{LEFT JOIN Customers}

_{_______=________}

• ON Orders.CustomerID=

  Customers.CustomerID;

• Customers.CustomerID=

  orders.customerID

• Orders.CustomerID=

  Customers.CustomerID;

• ON Customers.CustomerID=

  Orders.CustomerID

Multiple Choice

Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.

_{SELECT *}

_{FROM Orders}

_{------------------------}

_{ON Orders.CustomerID=Customers.CustomerID;}

• JOIN Customers

• RIGHT JOIN Customers

• RIGHT JOIN Orders

• OUTER JOIN Customers

Multiple Choice

The LEFT JOIN keyword returns all records from the left table (Customers), even if there are ______ in the right table (Orders).

• matches

• no matches

Multiple Choice

The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table _______.

• only matches

• only not matches

• matches or not

• None

SQL Constraints

• SQL constraints are used to specify rules for the data in a table.

• This ensures the accuracy and reliability of the data in the table.

• Constraints can be column level or table level.

The following constraints are commonly used in SQL

• _{NOT NULL - Ensures that a column cannot have a NULL value}

• _{UNIQUE - Ensures that all values in a column are different}

• _{PRIMARY KEY - A combination of a NOT NULL and UNIQUE.} 

• _{FOREIGN KEY - Uniquely identifies a row/record in another table}

• _{CHECK - Ensures that all values in a column satisfies a specific condition}

• _{DEFAULT - Sets a default value for a column when no value is specified}

• _{INDEX - Used to create and retrieve data from the database very quickly}

Example

CREATE TABLE Persons (

   ID int NOT NULL UNIQUE/PRIMARY KEY,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255) NOT NULL,

   Age int CHECK (Age>=18),

City varchar(255) DEFAULT 'Sandnes'

);

1. Domain constraints

• Domain constraints can be defined as the definition of a valid set of values for an attribute.

• The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain.

Example

2. Entity integrity constraints

• The entity integrity constraint states that primary key value can't be null.

• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.

• A table can contain a null value other than the primary key field.

Example

3. Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.

• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

Example

4. Key constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.

• An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table.

Example