Home > DeveloperSection > Blogs > Introduction to SQL

Introduction to SQL


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

Introduction to SQL 


SQL stands for Structured Query Language. The structured query language is used to manipulate and access the databases. SQL is an ANSI (American National Standard Institute) standard. Use of SQL:

·         SQL is used to create database.

·         SQL is used to create table.

·         SQL is used to insert value in table.

·         SQL is used to modify value in table.

·         SQL is used to delete value in table.

·         SQL is used to drop the table.

·         SQL is used to drop database.


In this section we will discuss some queries used in databases.

NOTE: SQL is case insensitive means select is same as SELECT.

Create database

To create database CREATE DATABASE statement is used. Below is the syntax for creating database:

Syntax:

CREATE DATABASE name_of_database;

 

Example:

CREATE DATABASE student;

student database will be created.

Create Table

To create table CREATE TABLE statement is used. Below is the syntax for creating table:

Syntax

CREATE TABLE  name_of_table

(

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

....

);

 

Example:

 

CREATE TABLE student

(

studentID int,

First Name varchar(55),

Last Name varchar(55),

Address varchar(55),

City varchar(55)

);

Student_info table will be created.

INSERT INTO

To insert values in the table INSERT INTO statement is used. This statement is used in two ways:

Syntax:

1)  INSERT INTO name_of_table VALUES (value1, value2, value3,...);

 

2)  INSERT INTO name_of_table (column1, column2, column3,...)

VALUES (value1, value2, value3,...);

 

Example:

1)  INSERT INTO student_info  VALUES (1234,'John’,’Smith’,52,’London’);

 

2)  INSERT INTO student_info (studentid, Firstname, Lastname, City)VALUES (1234,'John’,’Smith’,’London’);

UPDATE

To update record UPDATE statement is used. Below is the syntax for updating record:

Syntax:

UPDATE name_of_table

SET column1=value1,column2=value2,...

WHERE column=value;

 

NOTE: WHERE clause is used to specify which record is to be updated.

Example:

UPDATE student_info

SET FirstName='Jennifer’ WHERE FirstName='John’;

 

DELETE

To delete record DELETE statement is used. Below is the syntax for deleting record:

Syntax:

DELETE FROM name_of_table

WHERE column=value;

NOTE: WHERE clause is used to specify which record is to be deleted.

Example:

DELETE FROM student_info

WHERE firstName='Jennifer' ;

 

Output: Jennifer record will be deleted.

SELECT

To select data from database SELECT statement is used. The result which we get will be stored in result set. Following is the syntax of SELECT statement:

Syntax:

SELECT column_name1, column_name2

FROM name_of_table;

 

Above syntax is given where particular data is to be selected.

 

SELECT * FROM name_of_table;

 

Above syntax is used to fetch whole record in database.

Example:

StudentId

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      34

12ST14

John

Paris

232688        56

12ST15

Jennifer

USA

43762762    67

 

SELECT  Name, address from student_info;

 Output:

Name

Address

John

USA

Royce

Argentina

Mickel

London

Tom

New york

Hugh

Luanda

John

Paris

Jennifer

USA

 

SELECT * FROM student_info;

Output:

StudentId

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      34

12ST14

John

Paris

232688        56

12ST15

Jennifer

USA

43762762    67

 

DISTINCT

In a table, a field may have duplicate values; and you only want distinct values. So, DISTINCT keyword is used which will return only different (distinct) values. Following is the syntax of DISTINCT statement:

Syntax:

SELECT DISTINCT column_name1, column_name2

FROM name_of_table;

 

Example:

SELECT DISTINCT address  FROM student_info;

 

output

USA

Argentina

London

New york

Luanda

Paris

WHERE

 WHERE clause filter only those data that fulfill a specified criterion. Following is the syntax of DISTINCT statement:

Syntax:

SELECT column_name1,column_name2

FROM name_of_table

WHERE column_name operator value;

 

Example:

SELECT * FROM student_info

WHERE address= ‘USA’;

 

Output:

12ST09

       John

                 USA

  456788           54

12ST15

Jennifer             USA

  43762762 

  67

 

 

 

AND & OR

Like WHERE clause AND & OR operator is also used for filter.

The AND operator is used when first condition and second condition both are true.

The OR operator is used when either the first condition or second condition is true.

Example:

SELECT * FROM student_info

WHERE address='Paris'

AND marks='56';

 

Output:

12ST14

 John

 Paris

   232688        56

 

Example:

SELECT * FROM student_info

WHERE name='John'

OR address='Argentina';

 

Output:

12ST09

    John

USA

456788     54

12ST14

    John

        Paris

   232688       

                        

12ST10

   Royce

    Argentina

457912        34

 

 

 

ORDER BY

ORDER BY is used to sort the column. By default the records is sorted in ascending order. For descending order you can use DESC keyword.

Syntax:

SELECT column_name1, column_name

FROM name_of_table

ORDER BY column_name ;

 

Example

SELECT * FROM student_info

ORDER BY name;

Output:

Hugh

Jennifer

John

John

Mickel

Royce

Tom

 

 

SELECT TOP

The SELECT TOP is used to return number of data. The SELECT TOP can be very useful for thousands of record. Following is the syntax of SELECT TOP:

Syntax:

SELECT column_name

FROM name_of_table

LIMIT number;

 

Example:

SELECT *

FROM student_info

LIMIT 2;

 

Output:

12ST14

    John

        Paris

                       232688       45

                        

12ST10

   Royce

Argentina

457912        34

 

LIKE

The LIKE is used with WHERE clause to search for a specific pattern. Following is the syntax:

SELECT column_name1

FROM name_of_table

WHERE column_name LIKE pattern;

 

Example:

SELECT * FROM student_info

WHERE address LIKE  'a%';

 

Output:

12ST10

  Royce

   Argentina

  457912        34

12ST13

 Hugh

Luanda

  2974789      34

Wildcards

Wildcard are used with LIKE operator. These wildcard are used to search data within a table. Following is the example of wildcard:

Example:

SELECT * FROM student_info

WHERE name LIKE  'e%';

Output:

12ST10

  Royce

   Argentina

  457912        34

 

IN

The IN operator enable you to specify multiple values. Following is the example of wildcard:

Syntax

SELECT column_name

FROM name_of_table

WHERE column_name IN (value1,value2,...);

 

Example:

SELECT * FROM student_info

WHERE address IN ('Paris','London');

 

Output

12ST14

    John

        Paris

                   232688       45

12ST11

Mickel

    London

                56276          45

 

 

 

NOTE: BETWEEN, Aliases, Joins, Inner Join, Left Join, Right Join, Full Join, Union, Select Into, Insert Into Select, etc  queries are discussed in next blog.


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

Follow MindStick