SQL Queries

In previous article we have discussed about SQL and some queries. In this part we will continue on SQL Queries:

Customer Id	Name	Address	Contact Marks
12ST09	John	USA	456788 54
12ST10	Royce	Argentina	457912 34
12ST11	Mickel	London	56276 45
12ST12	Tom	New york	2898938 43
12ST13	Hugh	Luanda	2974789 37
12ST14	Nolan	Paris	232688 56
12ST15	Jennifer	USA	43762762 67

BETWEEN

BETWEEN operator is used to select values within a range. These values can be numbers, text, or dates.

Syntax

SELECT column_name

FROM name_of_table

WHERE column_name BETWEEN value1 AND value2;

SELECT column_name

FROM name_of_table

WHERE column_name BETWEEN value1 AND value2;

SELECT column_name

FROM name_of_table

WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT * FROM student_info

WHERE marks BETWEEN 30 AND 40;

Output:

12ST10	Royce		Argentina			457912 34
12ST13		Hugh		Luanda	237644 37

Aliases

Aliases are used to give temporary name to database table or a column in a table. Basically SQL aliases are created to make database table more readable. Following is the syntax of aliases:

Syntax:

For column

SELECT column_name AS name_of_alias

FROM name_of_table;

Syntax:

For table

SELECT column_name

FROM name_of_table AS name_of_alias;

Example:

SELECT name AS Firstname,

FROM student_info;

Output:

Firstname

John

Royce

Mickel

Tom

Hugh

Nolan

Jennifer

JOIN

JOIN clause is used to combine rows from two or more rows, based on common column between them. The most common type is INNER JOIN which returns all rows from tables where the join condition is met.

Here are two tables:

Student table

studentId	Name	Address
12ST09	John	USA
12ST10	Royce	Argentina
12ST11	Mickel	London
12ST12	Tom	New york
12ST13	Hugh	Luanda
12ST14	Nolan	Paris
12ST15	Jennifer	USA

Marks table

studentId	Contact Marks
12ST09	456788 54
12ST10	457912 34
12ST11	56276 45
12ST12	2898938 43
12ST13	2974789 37
12ST14	232688 56
12ST15	43762762 67

SELECT student.name, student.contact,marks.marks

FROM student

INNER JOIN marks

ON student.studentId=marks.studentId;

Output:

Name	Contact Marks
John	456788 54
Royce	457912 34
Mickel	56276 45
Tom	2898938 43
Hugh	2974789 37
Nolan	232688 56
Jennifer	43762762 67

LEFT JOIN

The LEFT JOIN keyword returns all the rows from first table, with the matching rows in the second table. NULL result will be shown if there is no match

Syntax:

SELECT column_name

FROM tablefirst

LEFT JOIN tablesecond

ON tablefirst.column_name=tablesecond.column_name;

Example:

SELECT student.name, marks.studentId

FROM student

LEFT JOIN marks

ON student.studentId=marks.studentId;

Output:

Name	studentId
John	12ST09
Royce	12ST10
Mickel	12ST11
Tom	12ST12
Hugh	12ST13
Nolan	12ST14
Jennifer	12ST15

RIGHT JOIN

RIGHT JOIN keyword return all rows from second table, with the matching rows in first table. Null is return if there is no match found.

Syntax:

SELECT column_name

FROM tablefirst

RIGHT JOIN tablesecond

ON tablefirst.column_name=tablesecond.column_name;

Example:

SELECT marks.studentId, student.name

FROM marks

RIGHT JOIN student

ON marks.studentId=student.studentId;

Output:

studentId	Name
12ST09	John
12ST10	Royce
12ST11	Mickel
12ST12	Tom
12ST13	Hugh
12ST14	Nolan
12ST15	Jennifer

FULL OUTER JOIN

The FULL OUTER JOIN will return all rows from first table and all rows from second table. The FULL OUTER JOIN is the result of both LEFT and RIGHT joins.

Syntax:

SELECT column_name

FROM tablefirst

FULL OUTER JOIN tablesecond

ON tablefirst.column_name=tablesecond.column_name;

Example:

SELECT student.studentname, marks.studentId

FROM student

FULL OUTER JOIN marks

ON student.studentId=marks.studentId;

Output:

Name	studentId
John	12ST09
Royce	12ST10
Mickel	12ST11
Tom	12ST12
Hugh	12ST13
Nolan	12ST14
Jennifer	12ST15

UNION

The UNION operator is used to combine the result of two or more SELECT statements. Please note SELECT statement with in the union must have same number of column.

Syntax:

SELECT column_name FROM tablefirst

UNION

SELECT column_name FROM tablesecond;

Example:

SELECT name FROM student

UNION

SELECT contact,marks FROM marks;

Output:

Name	Contact Marks
John	456788 54
Royce	457912 34
Mickel	56276 45
Tom	2898938 43
Hugh	2974789 37
Nolan	232688 56
Jennifer	43762762 67

SELECT INTO

The SELECT INTO statement select record from one table and insert into another table. Following is the syntax of SELECT INTO:

SELECT *

INTO new table

FROM tablefirst;

Or we can insert one column

SELECT column_name

INTO new table [IN externaldb]

FROM tablefirst;

INSERT INTO SELECT

The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Following is the example of INSERT INTO SELECT:

Syntax:

INSERT INTO tablesecond

SELECT * FROM tablefirst;

Example:

INSERT INTO student

SELECT * FROM marks;

Output:

Customer Id	Name	Address	Contact Marks
12ST09	John	USA	456788 54
12ST10	Royce	Argentina	457912 34
12ST11	Mickel	London	56276 45
12ST12	Tom	New york	2898938 43
12ST13	Hugh	Luanda	2974789 37
12ST14	Nolan	Paris	232688 56
12ST15	Jennifer	USA	43762762 67

blog

Example:

Example:

Example:

Example:

Output:

INSERT INTO SELECT

Example:

Samuel Fernandes

Leave Comment

1 Comments

Comments

Liked By