SQL

Here is where I will keep logs of what I am learning!

Welcome to "SQL-Learning" For Data Analysis!

In this Git repo, I'll embark on a SQL learning adventure to become a SQL master. Over the next couple of weeks, I'll dive into the world of databases, queires, and data manipulation!
Get ready to explore SQL fundamentals, tackle complex JOINS, unleash the power of subqueries, and discouver the magic of aggregate functions. With each passing day, I'll levelup my SQL skills.
Let's make data dance to our tunes!!

Learning Logs

Skill learned	Topic Content
SQL	SQL, DBMS, SQL Commands, DDL, DML
DDL & DML	UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE
Other SQL Queries	LIKE, Wildcards, DISTINCT, ORDER BY, NULL VALUES, Aliases, In, Between, Select Top, SQL Operators
Aggregrate Function	AVG, MIN, MAX, SUM, COUNT
SQL Joins	Inner Join, Outer Join, Left/Right Join ,Self Join
Intermediate SQL	UNION, GROUP BY, HAVING, EXISTS, PARTITION BY, Window Function. Null Functions
CASE Statement	CASE
Common Table Expression	CTE
Temporary Tables
String Functions	UPPER, LOWER, SUBSTRING, CONCAT, TRIM
Window Function	RANK, DENSE RANK, ROW NUMBER, LEAD/LAG
Advance SQL	PARTITION BY, Store Procedure, View, Temp Table
SQL Project	Project
SQL Tutorial	Learning Logs

SQL Projects
COVID-19 Data Exploration
Nashville Housing Data Cleaning
SQL Murder Mystery Game
Company Layoffs
Adidas Sales Analysis

DAY 1 of 'Learning SQL'

What is SQL?

SQL (Structured Query Language) is a standard language for accessing and manipulating database. SQL Lets you access and manipulate database. Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. However, to be compliant with ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manners.

What can SQL do?

Insert, update, delete record from database
Create new database
Create new tables in a database
Execute queries against a database
Retrieve data from database

Database Management System (DBMS)

DBMS is a collection of program that enables you to enter the data to the database, organize the data in the database and select data from the database.
DBMS manages the process of storing and retrieving data as well as providing users access to the database,

DBMS Software

Oracle, SQ Lite, MicroSoft SQL, IBM DB, MySQL, PostgreSQL, Hadoop HDF

SQL	Description	Commands
Data Definition Language (DDL)	DDL commands are used to define and manage the structure of the database, including tables, views, indexes, and constraints.	CREATE, ALTER, DROP, TRUNCATE, RENAME
Data Manipulation Language (DML)	DML commands are used to manipulate and retrieve data within the database.	SELECT, INSERT, UPDATE, DELETE.

Today I learn about SQL syntax used during data analysis. SQL statement consists of keyworks. Some of the most important SQL commands
- SELECT – Extracts data from database
- UPDATE – update data in a database
- DELETE – delete data from a database
- INSERT INTO – Insert new data into a database
- CREATE DATABASE – creates a database
- ALTER DATABASE – modifies a database
- CREATE TABLE – creates a new table
- ALTER TABLE – modifies a table
- DROP TABLE – delete a table
- CREATE INDEX – creates an index (search key)
- DROP INDEX – deletes an index

Data Definition Language

SQL Commands	Description
CREATE	This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers)
ALTER	This is used to alter the structure of the database.
DROP	This command is used to delete objects from the database.
TRUNCATE	This is used to remove all records from a table, including all spaces allocated for the records are removed.

DAY 2

CREATE Table

It is used to create table in a database. syntax:

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

ALTER Table

It is used to add or remove columns,keys,constraints and modify the data types of columns.
Syntax:

ALTER TABLE table_name
ADD column_name datatype;

DROP Table

It is used to drop existing table from the databases.
Syntax:

DROP TABLE table_name;

TRUNCATE Table

It is used to delete the data inside the table but not the table itself.
Syntax:

TRUNCATE TABLE table_name;

Day 3

🔷 SQL SELECT Statement

The SELECT Statement is used to select data from database
FROM is used to specify location of data

SELECT column1, column2, ...
FROM table_name
;

Example:

SELECT *
FROM Customer

🔷 SQL SELECT DISTINCT Statement The SELECT DISTINCT Statement is used to return only distinct(different) values

SELECT DISTINCT column1, column2, ...
FROM table_name
;

SELECT DISTINCT CustomerName
FROM Customer
// Ignore duplicate name
;

🔷 SQL WHERE Clause The WHERE clause is used to filter records. It is used to extract only those records that fulfill specified condition.

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

Example

SELECT *
FROM Customer
WHERE Country = 'Mexico'
;

Day 4 & 5

Today I learn about the Data Manipulation Languages and it's commands

SQL INSERT

SQL Commands	Description
SELECT	Retrieves data from one or more tables based on specified conditions
INSERT	Inserts new data into a table.
DELETE	Deletes data from a table based on specified conditions.
UPDATE	Modifies existing data in a table.

SQL INSERT

INSERT INTO Statement is used to insert new records in a table Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1.1, value1.2, value1.3, ...),
(value2.1, value2.2, value2.3, ...)
;

INSERT INTO table_name VALUES
VALUES (value1.1, value1.2, value1.3, ...),
(value2.1, value2.2, value2.3, ...)
;

Example:

Insert Into Customers values
(1,'Alfreds Futterkiste','Maria Anders','Berlin','12209','German'),
(2,'Ana Trujillo','Ana Trujillo', 'Tokyo','13235','Japan'),

SQL UPDATE

UPDATE Statement is used to modify the existing records in a table Syntax:

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

Example:

UPDATE Customer
SET Country  = 'Mexico'
WHERE CustomerID = 1;

SQL DELETE

DELETE Statement is used to delete existing records from a table.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Customer
WHERE Country = 'Brazil';

Deleting all records from table

DELETE From Customer;

To delete table completely

DROP TABLE Customer

Day 6 & 7

SQL Commands	Description
LIMIT	The LIMIT clause is used to restrict the number of rows returned by a SELECT statement
ORDER BY	The ORDER BY statement allows us to sort our results using the data in any column
Operators	Operators are used to give result based on condition applied

🔷LIMIT: LIMIT is used for restricting the number of rows retrieved from databases. Example:

Retrieve only 10 rows of data from Customer

SELECT * FROM Customer
LIMIT 10

SQL ORDER BY

ORDER BY Statement is used to sort the result-set in ascending or descending order

syntax:

SELECT Column1, Column2,......
FROM table_name
WHERE Condition
ORDER BY Column1, Column2,...
;

Example:

SELECT *
FROM orders
ORDER BY price DESC 
;
// Result are ordered in descending order

SQL Operators

Operators	Sign
Arithmetric	+,-,/,*
Logical	OR, AND, NOT
Comparision	=, <=,>= <>,!=

SQL AND

AND operator is used to filter records based on more than one condition. AND operator display a record if all condition are TRUE. syntax:

SELECT Column1, Column2,......
FROM table_name
WHERE Condition1 AND Condition2........
;

Example:

SELECT *
FROM Customer
WHERE Country = 'Germany' AND City = 'Berlin'
;

SQL OR

OR operator is used to filter records based on more than one condition. OR operator display a record if any of condition are TRUE. syntax:

SELECT Column1, Column2,......
FROM table_name
WHERE Condition1 OR Condition2........
;

Example:

SELECT *
FROM Customer
WHERE Country = 'Germany' OR City = 'Tokyo'
;

SQL NOT

NOT operator is used in combination with other operators to give opposite result i.e. negative result. syntax:

SELECT Column1, Column2,......
FROM table_name
WHERE NOT Condition
;

Example:

SELECT *
FROM Customer
WHERE NOT Country  = 'Germany' 
;

SELECT *
FROM Customer
WHERE Country NOT IN  ('Germany','Brazil') 
;

SELECT *
FROM Customer
WHERE NOT price > 1500
;

We can use operators like =, <=,>= <>,!= to filter the search. The following operators can be used in WHERE Clause Example:

SELECT *
FROM Customer
WHERE CustomerID Between 10 AND 20  // Between is used to determine certain range of values
;


SELECT *
FROM Customer
WHERE City LIKE 's%'  // City name that started by S
;

Day 8

🔷 COUNT: COUNT() is a built in function that retrieves the number of rows that matches the query crietaria .

SELECT COUNT(column_name)
FROM table_name
WHERE Condition
;

Example:

SELECT COUNT(*)
FROM Customer
;

// Ignore Dupicate
SELECT COUNT(DISTINCT price)
FROM order
;

SQL MIN

MIN() return smallest value of selected column syntax:

SELECT MIN(column_name)
FROM table_name
WHERE Condition
;

Example

SELECT MIN(price)
FROM Order

;

SQL MAX

MAX() return largest value of selected column syntax:

SELECT MAX(column_name)
FROM table_name
WHERE Condition
;

Example

SELECT MAX(price)
FROM Order
;

SQL SUM

SUM() function returns total sum of numeric column syntax:

SELECT SUM(column_name)
FROM table_name
WHERE Condition
;

Example

SELECT SUM(price)
FROM Order
;

SELECT SUM(price* quantity) As total_sum
From Orders
// As is Alias which gives temporary name to column

SQL AVG

SUM() function returns the average value syntax:

SELECT AVG(column_name)
FROM table_name
WHERE Condition;

Example

SELECT AVG(price)
FROM Order;

Day 9

🔷 SQL NULL Values A field with NULL Values is a field with no value. We will have to use IS NULL or IS NOT NULL operator syntax:

SELECT column_name
FROM table_name
WHERE column_name IS NULL
;

🔷 SQL SELECT TOP The SELECT TOP clause is used to specify the number of records to return Example:

// Select only first 3 records of Customer
SELECT TOP 3 *
FROM Customer;

// SQL TOP Percent
Select Top 50 PERCENT
FROM Customer;

Note: Not all database system support the SELECT TOP clause. MYSQL support LIMIT, while ORACLE use FETCH FIRST n Rows only and ROWNUM

🔷 SQL Aliases An Alias is creates with AS keyword. It is used to give temporary name to columns Example

SELECT AVG(price) AS average_price
FROM Order;

When Joining two tables

SELECT o.orderID, o.orderDate, c.CustomerName
FROM Customer c,
order o
WHERE c.customerName ='ABC' AND C.customerID = o.CustomerID;

🔷 SQL IN IN operator allows you to specify multiple values in WHERE clause syntax

SELECT column1, column2,.....
FROM table_name
WHERE column_name IN (value1, value2,.......)

Example

SELECT *
FROM Customers
WHERE Country IN ('France','Brazil');

SQL LIKE & WILDCARDS

LIKE operator is used in a WHERE clause to search for a specified pattern in a column syntax

SELECT column1, column2,.....
FROM table_name
WHERE column LIKE pattern;

The Wildcards _Wildcard represent a single character Example

SELECT *
FROM Customer
WHERE City LIKE 'L_nd_n';
// It display values where city is London

The % Wildcard represent any number of characters, even characters.

SELECT *
FROM Customer
WHERE City LIKE '%L%';
// It display values where city contain letter L. Example: London, Berlin, Marseille

// Select all customers that ends with 'a'
SELECT *
FROM Customer
WHERE Customer_Name LIKE '%a';

// Return all customers that starts with 'a' and are atleast 3 character in length
SELECT *
FROM Customer
WHERE Customer_Name LIKE 'a___%';

// Return all customers starting with either 'b', 's', or 'p'
SELECT *
FROM Customer
WHERE Customer_Name LIKE '[bsp]%';

// Return all customers starting with 'a','b','c','d', 'e', or 'f'
SELECT *
FROM Customer
WHERE Customer_Name LIKE '[a-f]%';

DAY 10

SQL JOINS

JOIN clause is used to combine rows from two or more tables, based on related column between them.

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Here are the differetn types of JOINS in SQL:

INNER JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table
FULL(OUTER) JOIN : Returns all records when there is a match in either left or right table

🔷 INNER JOIN INNER JOIN keyword selects records that have matching values in both tables. Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

Note: The ```INNER JOIN``` keyword returns only rows with a match in both tables. Which means that if you have a product with no CategoryID, or with a CategoryID that is not present in the Categories table, that record would not be returned in the result.

JOIN or INNER JOIN

JOIN and INNER JOIN will return the same result. INNER is default join type for JOIN, so when you write JOIN the parser actually writes INNER JOIN.

JOIN Three Tables

Example:

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

DAY 11

🔷 LEFT JOIN LEFT JOIN keyword selects records all records from the left table (table1) and matching records from the right table (table2). The result is 0 records from the right side, if there is no match. Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Note: The ```LEFT JOIN``` keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

🔷 RIGHT JOIN RIGHT JOIN keyword selects records all records from the right table (table2) and matching records from the left table (table1). The result is 0 records from the left side, if there is no match. Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

Note: The ```RIGHT JOIN``` keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

DAY 12

🔷 SQL FULL OUTER JOIN FULL OUTER JOIN keyword selects records all records all records when there is a match in left (table1) or right(table2) table records.

Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

🔷 SQL Self JOIN Self JOIN is a regular join, but the table is joined with itself.

Syntax:

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Example:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

Day 13 & 14

SQL UNION Operator

The UNION operator is used to combine the result set of two or more SELECT statement.

Every SELECT statement with UNION must have the same number of columns.
The columns must also have similar data types.
The columns in every SELECT statement must also be in same order.

Note: To allow duplicate values, use UNION ALL Syntax:

SELECT column_name(s)
FROM table1 T1
UNION
SELECT column_name(s);

Example:

SELECT City, Country
FROM Customers
Where Country = 'Germany'
UNION
SELECT City, Country
FROM Suppliers
Where Country = 'Germany'
ORDER BY City;

GROUP BY

The GROUP BY statement groups rows that have 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. Syntax:

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

Example:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

GROUP BY With JOIN

Example:

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;;

SQL HAVING Clause

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

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

Example:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Name		Name	Last commit message	Last commit date
Latest commit History 47 Commits
Asset		Asset
SQL Project		SQL Project
Covid Exploration Project.sql		Covid Exploration Project.sql
Nashville Housing Data Cleaning.sql		Nashville Housing Data Cleaning.sql
README.md		README.md
SQL Data Cleaning.sql		SQL Data Cleaning.sql
SQL-Murder-Mystery.sql		SQL-Murder-Mystery.sql
SQL_CTE.sql		SQL_CTE.sql
SQL_CaseStatement.sql		SQL_CaseStatement.sql
SQL_CaseStudy_Sales.sql		SQL_CaseStudy_Sales.sql
SQL_PartitionBY.sql		SQL_PartitionBY.sql
SQL_Procedure.sql		SQL_Procedure.sql
SQL_StringFunciton.sql		SQL_StringFunciton.sql
SQL_Subqueries.sql		SQL_Subqueries.sql
SQL_TEMP_TABLE.sql		SQL_TEMP_TABLE.sql
SQL_Tutorial_learning.sql		SQL_Tutorial_learning.sql

Aayush-Basnet/SQL

Folders and files

Latest commit

History

Repository files navigation

SQL

Welcome to "SQL-Learning" For Data Analysis!

Learning Logs

DAY 1 of 'Learning SQL'

What is SQL?

Database Management System (DBMS)

DBMS Software

Data Definition Language

DAY 2

CREATE Table

ALTER Table

DROP Table

TRUNCATE Table

Day 3

Day 4 & 5

SQL INSERT

SQL INSERT

SQL UPDATE

SQL DELETE

Day 6 & 7

SQL ORDER BY

SQL Operators

SQL AND

SQL OR

SQL NOT

Day 8

SQL MIN

SQL MAX

SQL SUM

SQL AVG

Day 9

SQL LIKE & WILDCARDS

DAY 10

SQL JOINS

JOIN or INNER JOIN

JOIN Three Tables

DAY 11

DAY 12

Day 13 & 14

SQL UNION Operator

GROUP BY

GROUP BY With JOIN

SQL HAVING Clause

About

Topics

Resources

Stars

Watchers

Forks

Languages