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:
Example:
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),
....
);
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:
Example:
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;
Example:
UPDATE student_info
SET FirstName='Jennifer’ WHERE FirstName='John’;
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;
SELECT * FROM name_of_table;
Example:
|
Output:
|
SELECT * FROM student_info;
Output:
|
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;
SELECT DISTINCT address FROM student_info;
|
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;
SELECT * FROM student_info
WHERE address= ‘USA’;
|
|||||||||||||||||||||
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';
|
Example:
SELECT * FROM student_info
WHERE name='John'
OR address='Argentina';
|
||||||||||||||||||||||||||||||||||
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 ;
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;
|
|||||||||||||||||||||||||||
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;
SELECT * FROM student_info
WHERE address LIKE 'a%';
|
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:
|
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,...);
SELECT * FROM student_info
WHERE address IN ('Paris','London');
|
|||||||||||||||||||||||
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.
Manish Kumar
16-Mar-2017Thanks Samuel for providing informative post about sql.