_edited_edited.png)
SQL
• SQL full form is Structured Query Language.
• SQL is used to access, analyze, and manipulate databases.
• It can be used to execute queries, to retrieve and delete records from a database, and also to insert and update records in a database.
Types of SQL Commands
There are five different types of SQL Commands
• DDL or Data Definition Language : It is a collection of SQL commands that are used to build, change, and delete database structures.
• DQL or Data Query Language : It is used for performing queries on the data within schema objects.
• DML or Data Manipulation Language : It deals with manipulation of data present in the database.
• DCL or Data Control Language : It is mainly used to deal with rights, permissions, and other controls of the database system.
• TCL or Transaction Control Language : It is used to group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group are successfully completed.
SQL Commands in detail :
Important SQL Commands
Some of the most frequently used SQL commands are :
1. SELECT : It is used to retrieve data from a database.
2. INSERT : It is used to add new data to a database.
3. UPDATE : It is used to modify existing data in a database.
4. DELETE : It is used to delete or remove data from a database.
5. CREATE TABLE : It is used to create a new table in a database.
6. ALTER TABLE : It is used to modify the structure of an existing table.
7. DROP TABLE : It is used to delete an entire table from a database.
8. WHERE : It is used to filter rows based on a specified condition.
9. ORDER BY : It is used to sort the result set in ascending or descending order.
10. JOIN : It is used to combine rows from two or more tables based on a related column between them.
1. SELECT Statement
• The SELECT statement selects columns from the table and displays its values in the output.
• We can display only selected columns from the table OR all the columns.
Syntax :
For selected columns :
SELECT column1, column2, … FROM table_name;
Example :
SELECT first_name, last_name FROM customer;
Syntax :
For all columns :
SELECT * FROM table_name;
Example :
SELECT * FROM customer;
2. INSERT Statement
The INSERT statement is used to add new data into a database.
Syntax :
INSERT INTO table_name (column1, clumn2, column3, …)
VALUES (value1, value2, value3, …);
Example:
INSERT INTO customer (first_name, last_name, email )
VALUES(‘Dan’, ‘Joe’, ‘dan.doe@example.com’);
3. UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
Syntax :
UPDATE table_name SET column = <new value> WHERE condition;
Example :
UPDATE customer SET store_id=2 WHERE store_id=1;
4. DELETE Statement
The DELETE statement is used to delete the existing data in a table.
Syntax :
DELETE * FROM table_name WHERE condition;
Example :
DELETE * FROM customer WHERE store_id=1;
5. CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.
Syntax :
CREATE TABLE table_name(column1 datatype,
column2 datatype,
column3 datatype, …);
Example :
CREATE TABLE Movies(
movie_id int,
movie_name varchar(45),
release_year varchar(10),
director_name varchar(45));
6. ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
It is also used to add and drop various constraints on an existing table.
Syntax :
ALTER TABLE table_name
ADD column_name datatype;
Example:
ALTER TABLE customer ADD zip_code varchar(50);
7. DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.
Syntax:
DROP TABLE table_name;
Example :
DROP TABLE language;
8. WHERE Clause
WHERE clause is used to filter records.
It extracts only those records that fulfill a specific condition.
WHERE clause can be used in any statement where we want to add a condition such as SELECT, UPDATE, DELETE, etc.
Syntax and Example :
For SELECT statement :
SELECT column1, column2, ... FROM table_name WHERE condition;
SELECT first_name, last_name, FROM customer WHERE store_id=1;
9. ORDER BY keyword
The ORDER BY keyword is sorts the result-set in ascending or descending order.
The default order is ascending.
To sort the records in the descending order, DESC keyword is used.
For Ascending Order :
Syntax :
SELECT column1, column2, …
FROM table_name
ORDER By column1, column2, …;
Example :
SELECT first_name, last_name, …
FROM customer
ORDER BY first_name, last_name;
For Descending Order :
Syntax :
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2 DESC, …;
Example :
SELECT first_name, last_name, …
FROM customer
ORDER BY first_name DESC, last_name;
10. JOIN Clause
The SQL JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Example :
SELECT e.first_name, e.last_name, d.department_name
FROM employees e JOIN departments d ON
e.department_id = d.department_id
Conclusion
SQL commands are the basic foundation of an effective database management system. SQL provides all the necessary tools to manipulate, access, update, manage and to analyze data. In this blog, we learned about mainly used SQL commands while doing Data analyzation. I hope this blog brings a deep understanding of basics of SQL commands and syntax with examples.