How To Create Two Tables In Sql

In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n rows then the resulting table after selecting two tables i.e cross join of two tables contains m*n rows.

Let us see how to select multiple tables using the MSSQL server:

Attention reader! Don't stop learning now. Learn SQL for interviews usingSQL Course by GeeksforGeeks.

Creating a database :

Creating a database GeeksForGeeks by using the following SQL query as follows.

          CREATE DATABASE          GeeksForGeeks;

Using the database :

Using the database student using the following SQL query as follows.

          USE          GeeksForGeeks;

Creating Tables:

Creating three tables student, branch_details, credit_detailswith SQL query as follows:

          CREATE TABLE          student (   stu_id          varchar(10), stu_name          varchar(20), branch          varchar(20) );

          CREATE TABLE          branch_details (   branch_name            varchar(10), subjects          INT          );

          CREATE TABLE          credit_details (   branch          varchar(20),  max_credits          INT,          min_credits_required          INT          );

Verifying the database :

To view the description of the three tables in the database GeeksForGeeks using the following SQL query as follows.

          EXEC          sp_columns student;          EXEC          sp_columns branch_details;          EXEC          sp_columns credit_details;

Inserting data into the three tables:

Inserting rows into tables using the following SQL query as follows:

          INSERT INTO          student          VALUES          ('1901401','DEVA','C.S'), ('1901402','HARSH','C.S'), ('1901403','DAVID','E.C'), ('1901404','GAURAV','E.C');          INSERT INTO          branch_details          VALUES          ('C.S',8), ('E.C',7), ('M.E',7), ('I.C.E',9), ('E.E.E',8);          INSERT INTO          credit_details            VALUES          ('C.S',24, 12), ('E.C',21, 11), ('M.E',21, 11), ('I.C.E',27,14), ('E.E.E',24,12);

Verifying the inserted data :

Viewing the three tables after inserting rows by using the following SQL query as follows.

          SELECT * FROM          student;          SELECT * FROM          branch_details;          SELECT * FROM          credit_details;

Using SELECT statement for n tables:

          SYNTAX:          SELECT          columns          FROM          table_1, table_2,...table_n WHERE condition;

Using SELECT statements for 2 tables student, branch_details:

          SELECT * FROM          student, branch_details;

This table contains 4*5 = 20 rows.

Using SELECT statements for 3 tables student, branch_details, credit_details:

          SELECT * FROM          student, branch_details, credit_details;

The resulting table contains 5*4*5 = 100 rows.

We can obtain other types of join by selecting multiple tables and mentioning appropriate condition in the WHERE clause but instead of using the SELECT with multiple tables and adding conditions using the keywords of joins is more optimal.

Example:

Query to display students who have enrolled in a course and their particulars.

          SELECT          student.stu_id, student.stu_name, student.branch, subjects, max_credits          FROM          student, branch_details, credit_details          WHERE          student.branch = branch_details.branch_name            AND                    branch_details.branch_name = credit_details.branch;

Note: When there are columns with the same name in different tables it is good to dot operator to point to the particular tables data.