SQL basic syntaxes with examples

CREATE DATABASE

It is used to create a new SQL database.

CREATE DATABASE database_name;

CREATE DATABASE test;

SHOW DATABASES

It displays all the existing databases.

SHOW DATABASES;

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+

DROP DATABASE

It deletes an existing database.

DROP DATABASE database_name;

DROP DATABASE test;

USE

It selects an existing database and allow users to perform any kind of operation like creating a table etc. It is something like selecting a folder in your computer before you do any kind of further operations inside that folder. So it is one of the important statements in SQL.

USE database_name;

USE test;

CREATE TABLE

It creates a table inside an existing database. See the syntax below, data type is compulsory and constraint is optional. To see all the common data types available in SQL, read my previous post.  For constraints, check out this post.

Note: you have to always select an existing database using USE statement and then do further operation like create table etc inside that database.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

CREATE TABLE student( 
   Id int, 
   Branch varchar(255) 
);

DESCRIBE

It displays all the attributes ( Column names) of an existing table with respective properties.

DESCRIBE table_name;

DESCRIBE student;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| Id     | int(11)      | YES  |     | NULL    |       |
| Branch | varchar(255) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

DROP TABLE

It deletes an existing table.

DROP TABLE table_name;

DROP TABLE student;

INSERT INTO

It adds a new row with values to an existing table. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

INSERT INTO table_name (column1, column2,.....)
VALUES (value1, value2,.....);

INSERT INTO student(Id, Branch)
VALUES (1, 'cs');

ALTER TABLE

This is used to add a new column or modify (delete, update, rename etc) any existing column in a table. However you have to always include the alter specific options like ALTER TABLE table_name alter_specific_option. Given below some common SQL Alter specific options.

• ADD – to add a new column
• DROP  – to delete an existing column
• CHANGE – to rename a column name.
• MODIFY – to change the data type of an existing column

ALTER TABLE table_name
ADD new_column_name data_type;

ALTER TABLE table_name
DROP existing_column_name;

ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type_of_new_column;

ALTER TABLE table_name
MODIFY existing_column_name new_data_type;

SELECT

It helps selecting row from an existing table. You can also use wildcard character (*) to select all the columns without explicitly define column names (Example-2). Note: SELECT can be used without FROM also (Example-3). 

SELECT (column1, column2, column3,...) FROM table_name;

SELECT Id, Branch FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
| 1    | cs     |
+------+--------

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
+------+--------+

SELECT 'HELLO WORLD';
+-------------+
| HELLO WORLD |
+-------------+
| HELLO WORLD |
+-------------+

SELECT DISTINCT

This statement is used to return only distinct (unique) values. Inside a table, a column often contains many duplicate values; and sometimes you want to remove those duplicate rows and list the unique values only.

SELECT (column1, column2, column3,...) FROM table_name;

SELECT Id, Branch FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
|    2 | mech   |
|    1 | cs     |
+------+--------+
SELECT DISTINCT Id, Branch FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
|    2 | mech   |
+------+--------+

UPDATE

The statement is used to modify the existing records in a table. Without where condition it will update all the records. So be careful.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
|    2 | mech   |
+------+--------+

UPDATE student
SET Id = 9, Branch = 'civil'
WHERE Id = 1;

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    9 | civil  |
|    2 | mech   |
+------+--------+

UPDATE student
SET Id = 5, Branch = 'eee'

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    5 | eee    |
|    5 | eee    |
+------+--------+

DELETE

The statement is used to delete the existing records from a table. Without where condition it will delete all the records. So be careful.

DELETE FROM table_name
WHERE condition;

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
|    2 | mech   |
+------+--------+

DELETE FROM student WHERE Id = 2;

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
+------+--------+

ORDER BY

This is used to sort the data in ascending or descending order, based on one or more columns. If you don’t include an option i.e ASC/ DESC then by default it will sort the query results in an ascending order. If there are multiple columns included then it will order based on first column initially and if some rows in first column have the same values then again order those results based on second column. It continues like that in case of several columns. See the examples below.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT * FROM student;
+------+--------+
| Id   | Branch |
+------+--------+
|    3 | civil  |
|    2 | mech   |
|    6 | cs     |
|    4 | eee    |
|    1 | cs     |
+------+--------+

SELECT * FROM student ORDER BY Id;
+------+--------+
| Id   | Branch |
+------+--------+
|    1 | cs     |
|    2 | mech   |
|    3 | civil  |
|    4 | eee    |
|    6 | cs     |
+------+--------+

SELECT * FROM student ORDER BY Branch;
+------+--------+
| Id   | Branch |
+------+--------+
|    3 | civil  |
|    6 | cs     |
|    1 | cs     |
|    4 | eee    |
|    2 | mech   |
+------+--------+

SELECT * FROM student ORDER BY Branch,Id;
+------+--------+
| Id   | Branch |
+------+--------+
|    3 | civil  |
|    1 | cs     |
|    6 | cs     |
|    4 | eee    |
|    2 | mech   |
+------+--------+

GROUP BY

It groups rows that have the same values into summary rows. It simply means duplicate rows are filtered out. You might be getting confused as it does the same job as SELECT DISTINCT . Yes it does. But both are syntactically different and is meant to be used in different case/sense.  GROUP BY is often used with aggregate functions. Let us take an example.

Minutely notice the example below. DISTINCT is used to filter out the duplicate set of values. Here (6, cs, 9.1) and (1, cs, 5.5) are two different sets though they have one common value i.e cs branch. So DISTINCT is going to display both the rows while GROUP BY Branch is going to display only one.

SELECT column_name(s)
FROM table_name
GROUP BY column_name(s)

 SELECT * FROM student; 
+------+--------+------+
| Id   | Branch | CGPA |
+------+--------+------+
|    3 | civil  |  7.2 |
|    2 | mech   |  6.3 |
|    6 | cs     |  9.1 |
|    4 | eee    |  8.2 |
|    1 | cs     |  5.5 |
+------+--------+------+


SELECT DISTINCT * FROM student; 
+------+--------+------+
| Id   | Branch | CGPA |
+------+--------+------+
|    3 | civil  |  7.2 |
|    2 | mech   |  6.3 |
|    6 | cs     |  9.1 |
|    4 | eee    |  8.2 |
|    1 | cs     |  5.5 |
+------+--------+------+


SELECT * FROM student GROUP BY Branch;
+------+--------+------+
| Id   | Branch | CGPA |
+------+--------+------+
|    3 | civil  |  7.2 |
|    6 | cs     |  9.1 |
|    4 | eee    |  8.2 |
|    2 | mech   |  6.3 |
+------+--------+------+

Sometimes the results that can be achieved by GROUP BY clause is not possible to achieved by DISTINCT without using some extra clause or conditions and vice versa. E.g in above case.

To get the same result as DISTINCT you have to pass all the column names in GROUP BY clause like below. So you must have knowledge about all the column names to use GROUP BY clause in that case.  Note the syntactical difference here.

SELECT * FROM student GROUP BY Id, Branch, CGPA;
+------+--------+------+
| Id   | Branch | CGPA |
+------+--------+------+
|    1 | cs     |  5.5 |
|    2 | mech   |  6.3 |
|    3 | civil  |  7.2 |
|    4 | eee    |  8.2 |
|    6 | cs     |  9.1 |
+------+--------+------+

Also I have noticed GROUP BY displays the results in ascending order by default which DISTINCT does not. But I am not sure about this. It may differ vendor wise.