I understand that A primary key constrain is a column or group of columns that uniquely identifies every row in the table of the relational database management system. It cannot be a duplicate, meaning the same value should not appear more than once in the table.

Foreign key is a column that creates a relationship between two tables. The purpose of the Foreign key is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table. Every relationship in the database should be supported by a foreign key.

so I want help with a table that describes a primary key and another table that explains foreign key.

secondly, what is joining table in RDBMS?

 

The following are examples of two tables:

• Student table
• Department table

Primary key- This constraint uniquely identifies any record in a table. It should contain UNIQUE values or data, and it cannot contain any NULL values.

Foreign key- It's the key used to connect two tables. It is a field or field collection in 1 table that corresponds to the PRIMARY KEY in some other table.

Create a Student table- In the Student table, Std_Id is a primary key.

CREATE TABLE Student(
    Std_Id int NOT NULL,
    Std_FirstName varchar(255),
    Std_LastName varchar(255),
    Age int,
    Std_Address varchar(255),
    PRIMARY KEY (Std_Id)
);

The following is the query of insert values in the Student table:

INSERT INTO Student (Std_Id, Std_FirstName, Std_LastName, Age, Std_Address) VALUES (1, 'Kari', 'Pettersen', 21, 'Australia'); INSERT INTO Student (Std_Id, Std_FirstName, Std_LastName, Age, Std_Address) VALUES (2, 'John', 'Svendson', 19, 'Brazil'); INSERT INTO Student (Std_Id, Std_FirstName, Std_LastName, Age, Std_Address) VALUES (3, 'July', 'Hansen', 22, 'Nepal'); INSERT INTO Student (Std_Id, Std_FirstName, Std_LastName, Age, Std_Address) VALUES (4, 'Sumeet', 'Kumar', 24, 'Bangalore'); INSERT INTO Student (Std_Id, Std_FirstName, Std_LastName, Age, Std_Address) VALUES (5, 'Manish', 'Singh', 20, 'Delhi');

Create department table- In the Department table, Dept_Id is a primary key and Std_Id is a Foreign key. Std_Id is a primary key of the Student table.

CREATE TABLE Department(
    Dept_Id int NOT NULL,
    Dept_Name varchar(255),
    Dept_Location varchar(255),
    Std_Id int,
    PRIMARY KEY ( Dept_Id),
    FOREIGN KEY (Std_Id) REFERENCES Student(Std_Id)
);

The following is the query of insert value in Department table:

INSERT INTO Department (Dept_Id, Dept_Name,  Dept_Location, Std_Id  ) VALUES (1, 'Mathematics', 'Delhi', 2); INSERT INTO Department (Dept_Id, Dept_Name,  Dept_Location, Std_Id  ) VALUES (2, 'Accounting', 'Pune', 4); INSERT INTO Department (Dept_Id, Dept_Name,  Dept_Location, Std_Id  ) VALUES (3, 'Research', 'Chennai', 1); INSERT INTO Department (Dept_Id, Dept_Name,  Dept_Location, Std_Id  ) VALUES (4, 'Sale', 'Mumbai', 2); INSERT INTO Department (Dept_Id, Dept_Name,  Dept_Location, Std_Id  ) VALUES (5, 'Marketing', 'Goa', 4);

Here is the query of joining table-

SELECT Student.Std_FirstName, Student.Std_Address, Department.Dept_Name, Department.Dept_Location
FROM Student /* this is a right_side table*/  
INNER JOIN Department /* this is a left_side table*/  
ON  Student.Std_Id=Department.Std_Id ;  

In the joining table, the query will display the Student Name, Student address, department name, and department location with the matched values of Student Id and department Student Id.

Joining Table- A joining table is used to retrieve information from two or more tables that are joined together to appear as a single data set in the RDBMS. It is used to combine or merge columns from two or more tables utilizing values common to both tables.

Step-by-step explanation

The following are the primary key and foreign key-

The foreign key is a type of column or a collection of columns in only one table that belong to the primary columns in some other table. The primary key is described as a column or column set where each value or record is unique and a single row of a table is identified.

The description of the Student table-

"Insert into" is utilized to insert new data or record in a table. In this table, the "insert into" statement will insert the Id, name, address, and age of the student in the database.

The description of the Department table-

In the Department table, the "insert into" statement will insert the Id, name, and location of the department in the database.

Snip of Student table-

Snip of the Department table-

Snip of the Joining table-

Joining table-The JOIN concept is used to merge rows between multiple tables, depending on the corresponding column between both. JOIN Keyword is being used in SQL queries for connecting two or more tables. It's done if the users need to get records from multiple tables. Below are the forms of JOIN that can be used in SQL:

• Inner join.
• Right join.
• Outer join
• Left join