Home > DeveloperSection > Blogs > SQL Queries

SQL Queries


MYSQL Database  SQL 
Ratings:
0 Comment(s)
 101  View(s)
Rate this:

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;

 

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


Don't want to miss updates? Please click the below button!

Follow MindStick