Basic concepts anyone starting in SQL should know about

• SQL stands for Structured Query Language.
• SQL is a database management language for relational databases.
• SQL lets you access and manipulate databases.
• SQL queries are not case sensitive. (SELECT = select)

Division of  SQL:

• Data Manipulation Language (DML) is used to add, update or delete data . Examples: INSERT, DELETE and UPDATE.
• Data Definition Language (DDL) is used for managing tables and index structures. Examples: CREATE, ALTER, TRUNCATE and DROP.
• Data Control Language (DCL) is used to assign and revoke database rights and permissions. Its main statements are GRANT and REVOKE.

Important SQL Commands:

• SELECT – extracts data from a table or view.
• UPDATE – updates data in a table.
• DELETE – deletes data from a table.
• INSERT  – inserts new data into a table.
• CREATE – creates a new table, database, schema etc.
• ALTER – modifies a table.
• DROP  – deletes a table, view etc.

SQL CREATE Statement:

The CREATE TABLE statement is used to create a table in a database.

Syntax: CREATE TABLE table_name(
column_name1  data_type(size),
column_name2  data_type(size),
column_name3  data_type(size),
....
);

Example: CREATE TABLE People(
ID int,
FirstName varchar(255),
LastName varchar(255)
);

SQL INSERT Statement:

The INSERT INTO statement is used to insert new records in a table.

Syntax: INSERT INTO table_name
(column1, column2,..)
VALUES ( Value1, Value2,…)

Example: INSERT INTO Customers(ID, FirstName, LastName)
VALUES (1,’Tom’,’Ryan’)              

SQL UPDATE Statement:

The UPDATE statement is used to update existing records in a table.

Syntax: UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

Example: UPDATE  Customers
SET  FirstName =‘Jack'
WHERE ID=1;

SQL DELETE Statement:

The DELETE statement is used to delete records in a table.

Syntax: DELETE FROM table_name
WHERE some_column=some_value;

Example: DELETE FROM Customers
WHERE  FirstName =‘Ron'

SQL ALTER Statement

The ALTER statement is used to add, delete, or modify columns in an existing table.

Syntax: ALTER TABLE table_name
ADD column_name datatype

Syntax: ALTER TABLE table_name 
DROP COLUMN column_name

Syntax: ALTER TABLE table_name 
ALTER COLUMN column_name datatype

Example: ALTER TABLE Customer
ADD Address varchar(100)

Example: ALTER TABLE Customer
DROP COLUMN Address

Example: ALTER TABLE Customer
ALTER COLUMN ID varchar(10)

SQL DROP Statement:

The DROP statement is used to delete an existing table.

Syntax: DROP TABLE Table_Name;

Example: DROP TABLE Customers;

SQL SELECT Statement:

The SELECT statement is used to view data in an existing table or view.

Syntax: SELECT * FROM table_name;

Syntax: SELECT column_name,column_name
FROM table_name;

Example: SELECT * FROM  Customers;

Example: SELECT FirstName, LastName FROM Customers;

SQL SELECT DISTINCT Statement:

The SELECT DISTINCT statement is used to return only distinct (different) values.

Syntax: SELECT  DISTINCT column_name,column_name
FROM table_name;

Example: SELECT  DISTINCT FirstName  FROM  Customers;

SQL WHERE Clause:

The WHERE clause is used to filter records.

Syntax: SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

Example: SELECT * FROM People
WHERE ID=5;

SQL AND Operator:

The AND operator displays a record if both the first condition AND the second condition are true.

Syntax: SELECT * FROM table_name
WHERE Firstcondition= value 
AND SecondCondition=value;

Example: SELECT * FROM Customers
WHERE FirstName='Jack'
AND LastName='Hammer';

SQL OR Operator:

The OR operator displays a record if either the first condition OR the second condition is true.

Syntax: SELECT * FROM table_name 
WHERE Firstcondition= value 
OR SecondCondition=value;

Example: SELECT * FROM Customers 
WHERE FirstName=‘Tom' 
OR LastName=‘sweeny';

SQL GROUP BY:

GROUP BY clause is used to group or aggregate the result based on the Column named  in the GROUP BY clause.

Syntax: SELECT  Col_list  FROM  Table_Name 
GROUP BY col_list; 

Example: SELECT FirstName,SUM(Amount) AS payment FROM Customer  
GROUP BY Firstname; 

   

SQL ORDER BY:

ORDER BY clause is used to organize the result in ascending or descending order based on the Column named in the ORDER BY clause. Ascending order segregation is by default, to organize the result in descending order we have to use DESC clause.

Syntax: SELECT  Col_list  FROM Table_Name   
GROUP BY col_list            
ORDER BY col_name DESC;

Example: SELECT  FirstName,SUM(Amount) AS Payment  FROM Customer 
GROUP BY Firstname 
ORDER BY SUM(Amount) DESC;

SQL JOIN:

JOIN clause is used to get results from two or more table which have one or more common columns.

Syntax: SELECT a.col_name,b.col_name  FROM Table1_Name a 
Type_of_JOIN Table2_Name b 	 
ON  a.col_Name=b.col_Name; 

Example: SELECT a.FirstName,b.Salary  FROM Employee a 
INNER JOIN SalaryInfo b  
ON  a.EmployeeID=b.EmployeeID;

Types of JOIN

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

Ready to elevate your skills? Click here for my Python book and here for my Machine Learning book on Kindle.