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 STATEMENT |
UPDATE Statement:
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 |
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 |
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;
 |
| 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 HEIGHT |
 |
| 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 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 |
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;
 |
| DISTINCT STATEMENT-DUPLICATE VALUES |
 |
| 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 |
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;
 |
| 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';
 |
| 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;
 |
| 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;
 |
| 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;
 |
| 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;
 |
| ALIASES STATEMENT |
0 thoughts:
Post a Comment