Tuesday, 19 January 2016

How to write SQL query?

We have discussed about the conceptual data model, logical data model, normalization and denormalization techniques and we discussed about installing and running up with MySQL community server.  What will be next? Once we build the data model and decided the database server, the next process will be start developing the physical database. What is needed to develop physical database? We need a common language to communicate with both the developer and database server. Here, comes the structure query language. So in this post we are going to see about the SQL (Structure Query Language). After reading this post you will be able to answer the following questions:



  • What is structure query language (SQL) and why it is important?
  • How to create database using SQL?
  • How to create database table?
  • How to insert values into database table?
  • How to update existing values in a database table?
  • How to delete values in database table?
  • How to select values from database tables?
  • How to select the result in sorted format using ORDER BY?
  • How to select the values in database table using AND & OR condition?
  • How to select distinct result set using DISTINCT?
  • How to select specific number of result set using SELECT TOP?
  • How to select values within a range using BETWEEN?
  • How to select specified pattern from a data field values?
  • How to select values from more than one table using JOIN?
  • How to temporarily rename a table or data field?


Structure Query Language (SQL):


Structure query language is a programming language, which is used for accessing, manipulating and managing the data stored in the database server. SQL can query a database for data and retrieve the data, update the existing data, delete the data and many other functions. SQL consists of data definition language (CREATE, DROP, ALTER, RENAME, etc.), data manipulation language (SELECT…INTO, INSERT…INTO, UPDATE, DELETE), data control language (GRANT, REVOKE). Let us discuss about some of the basic SQL statement here.


CREATE DATABASE Statement:


The create database used to create a new database.
Syntax: 
CREATE DATABASE db_name;
Example: 
Create database school;









CREATE TABLE Statement:


The create table statement used to create a new table in a database.
Syntax: 
CREATE TABLE table_name  
(            
    column_1 data_type(size),            
    column_2 data_type(size),            
. . . . . . . . 
);   
Example: 
Create table student 
(           
              student_id int,           
             first_name varchar(255),            
             last_name varchar(255),            
             weight  decimal(3,2),            
             height  decimal(3,2),            
             primary key (student_id)
       );


INSERT INTO Statement:



The insert into statement used to insert new values into table.
Syntax: 
INSERT INTO table_name VALUES (value1, value2, value3, . . . .);
Example: 
Insert into student values (‘1’, ‘steve’, ‘jobs’, ‘65’, ‘170’);  
Insert into student values ('2',"bill", "gates", '66', '175'); 
Insert into student values ('3',"sachin", "tendulkar", '68', '160'); 
Insert into student values ('4',"bradd", "pitt", '75', '175'); 
Insert into student values ('5',"will", "smith", '70', '178');
insert into table statement
INSERT INTO STATEMENT


UPDATE Statement:


The update statement is used to update data in a table.
Syntax: 
UPDATE table_name 
SET column_1 = value, column_2 = value, . . .   
WHERE some_column = some_value;
Example: 
update student 
set first_name = "virendar", last_name ="shewag" 
where student_id  = '3';
Update statement
UPDATE STATEMENT


DELETE Statement:


The delete statement used to delete data in a table.
Syntax: 
DELETE FROM table_name 
WHERE some_column = some_value
Example: 
Delete from student where student_id=3;
Delete statement
DELETE STATEMENT


SELECT Statement:


The select statement used to select data from table.
Syntax: 
SELECT column_1, column_2
FROM table_name; 
SELECT * FROM table_name;
Example: 
select last_name, first_name from student;select * from student;
SQL select statement
SELECT STATEMENT


ORDER BY Statement:


The order by statement used to sort the result set.
Syntax: 
SELECT column_1, column_2FROM table_nameORDER BY column_1 ASC|DESC, column_2 ASC|DESC;
Example: 
Select first_name, last_namefrom studentorder by height desc;
Order by statement
ORDER BY HEIGHT
Order by statement
ORDER BY STATEMENT


AND Operator:


The and operator used to filter data in result set. AND operator will display data if both the first condition and second conditions are true.
Syntax: 
SELECT * FROM table_nameWHERE column_1 = value AND column_2 = value;
Example: 
select * from studentwhere first_name = "steve" AND height = '165';
AND Operator
AND OPERATORS


OR Operator:


The or operator used to filter data in result set. OR operator will display data if either of the first condition or the second condition is true.
Syntax: 
SELECT * FROM table_nameWHERE column_1 = value OR column_2 = value;
Example: 
Select * from studentwhere first_name = "steve" OR height = '200';
OR Operator
OR OPERATOR


DISTINCT Statement:


The distinct statement used to select distinct result set.
Syntax: 
SELECT DISTINCT column_1, column_2FROM table_name;
Example: 
select distinct height from student;
duplicate values
DISTINCT STATEMENT-DUPLICATE VALUES

distinction statement
DISTINCTION STATEMENT


LIMIT Clause:


The limit statement used to select specific number of records on result set.
Syntax: 
SELECT columnFROM table_nameLIMIT number;
Example: 
select first_name from student limit 3;
limit clause
LIMIT CLAUSE


BETWEEN Operator: 


The between operator used to select values within a range.
Syntax: 
SELECT * FROM table_nameWHERE column BETWEEN value_1 AND value_2; 
Example: 
select * from studentwhere height between 160 and 175;
SQL Between Operator
BETWEEN OPERATOR


LIKE Operator:


The like operator used to search specified pattern from a data field.
Syntax: 
SELECT * FROM table_nameWHERE column_name LIKE pattern;
Example: 
select * from studentwhere first_name like 'will';
SQL Like Operator
LIKE OPERATOR


JOIN Statement:


The sql join clause used to combine data from two or more tables in a relational database. The join clause creates a data set which can be used as a table or used as it is.

Inner Join:

Syntax: 
SELECT column(s)FROM table_1JOIN table_2ON table_1.column_name = table_2.column_name;
Example: 
select mark_id, first_name, last_name, marks, description from markinner join studenton mark.student_id = student.student_id;
SQL inner join
INNER JOIN


Left Join:

Syntax: 
SELECT column(s)FROM table_1LEFT JOIN table_2ON table_1.column_name = table_2.column_name;
Example: 
select mark_id, first_name, last_name, marks, description from markleft join studenton mark.student_id = student.student_id;
SQL Left Join
LEFT JOIN


Right Join:

Syntax: 
SELECT column(s)FROM table_1RIGHT JOIN table_2ON table_1.column_name = table_2.column_name;
Example: 
select mark_id, first_name, last_name, marks, description from markright join studenton mark.student_id = student.student_id;
SQL Right Join
RIGHT JOIN


ALIASES Statement:


The aliases statement used to create temporary name to a table or column for better readability:
Syntax:

SELECT column_name AS aliasFROM table_name;SELECT * FROM table_name AS aliases; 
Example: 
select mark_id as ID, student_id as REGNO, marks as GRAD, description from mark;
SQL aliases statement
ALIASES STATEMENT
I hope this post will help you to understand about basic SQL statements, let us see more detailed discussion on database programming in future posts. Share your thoughts and questions on comment section and share this post with your friends.
Post a Comment