SQL Basics (Zero to Hero)- Part 01

SQL - Structured Query Language

A table is a collection of related data entries and it consists of columns and rows.

Eg: SELECT * FROM Customers;

--> It will retrieve all records data from customer table.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Most Important SQL Commands

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

SQL SELECT Statement
The SELECT statement is used to select data from a database.

--> SELECT column1, column2,...  FROM table_name;
--> SELECT * FROM table_name;
--> SELECT DISTINCT column1 FROM table_name        #Unique values
--> SELECT COUNT(DISTINCT Country) FROM Customers;  #Gives count

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT * FROM Customers
WHERE Country='India' OR Country='USA';

SQL ORDER BY Keyword
ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

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

Example:
SELECT * FROM Customers
ORDER BY Country DESC;

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

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Suresh', 'Bengaluru', 'India');

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Here, WHERE clause that determines how many records will be updated.

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

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

SQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MIN(column_name) FROM table_name WHERE condition;

SELECT MAX(column_name) FROM table_nameWHERE condition;

SQL COUNT(), AVG() and SUM() Functions
The COUNT() function returns the number of rows that matches a specified criterion.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.

SELECT COUNT(column_name) FROM table_name WHERE condition;
SELECT AVG(column_name)   FROM table_name WHERE condition;
SELECT SUM(column_name)   FROM table_name WHERE condition;

SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

The percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character

SELECT column1, column2 FROM table_name
WHERE columnN LIKE pattern;

Example:
WHERE CustomerName LIKE 'a%'-->Finds any values that start with "a"

SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

**Comments**
Single line comments start with --.
Multi-line comments start with /* and end with */.