Master SQL in 16 Pages
Master SQL in 16 Pages
Master SQL in 16 Pages
ALTER DELETE
DDL : Data Definition Language DML: Data Manipulation Language
DCL : Data Control Language TCL : Transaction Control Language
TRUNCATE
DQL : Data Query Language
1
Veerendra
2
Veerendra
7. Save table to another table --into file_name: save result in another table (BASE TABLE)
select distinct customerlastname into temp
from customer
order by customerlastname
select * from temp --see the table (data type will remain)
8. Like (search something) -- (underscore sign) _ is only specific for one character only
-- (percent sign) % represents zero, one, or multiple characters
select * from customer
where customerlastname like '_r%'
3
Veerendra
--
SELECT FirstName, LastName, JobTitle, Salary,
CASE
WHEN JobTitle = 'Salesman' THEN Salary + (Salary *.10)
WHEN JobTitle = 'Accountant' THEN Salary + (Salary *.05)
WHEN JobTitle = 'HR' THEN Salary + (Salary *.000001)
ELSE Salary + (Salary *.03)
END AS SalaryAfterRaise
FROM EmployeeDemographics ED
JOIN EmployeeSalary ES
ON ED.EmployeeID = ES.EmployeeID
4
Veerendra
-- Replace
Select LastName, REPLACE(LastName, '- Fired', '') as
LastNameFixed
FROM EmployeeErrors
-- Substring
Select Substring(err.FirstName,1,3),
Substring(dem.FirstName,1,3), Substring(err.LastName,1,3),
Substring(dem.LastName,1,3)
FROM EmployeeErrors err
JOIN EmployeeDemographics dem
on Substring(err.FirstName,1,3) =
Substring(dem.FirstName,1,3)
and Substring(err.LastName,1,3) =
Substring(dem.LastName,1,3)
Select *
From #temp_employee
GO;
5
Veerendra
-- with Partition By
SELECT EmployeeID, Salary, AVG(Salary) OVER () AS
AllAvgSalary
FROM EmployeeSalary
-- Subquery in From
SELECT a.EmployeeID, AllAvgSalary
FROM (SELECT EmployeeID, Salary, AVG(Salary) OVER () AS
AllAvgSalary
FROM EmployeeSalary) a
ORDER BY a.EmployeeID
-- Subquery in Where
SELECT EmployeeID, JobTitle, Salary
FROM EmployeeSalary
WHERE EmployeeID in (SELECT EmployeeID FROM
EmployeeDemographics
WHERE Age > 30)
6
Veerendra
SQL JOINS
select
inventoryname,saledate,saleunitprice,salequantity,saleunitprice*salequantity
as [Total Amount]
from inventory inner join sale
on sale.inventoryid=inventory.inventoryid
order by inventoryname
inventory sales
inventory sales
7
Veerendra
--left join (might have NULL value, since some inventory might not have sales)
select inventory.inventoryid,inventoryname
from inventory left join sale on
sale.inventoryid=inventory.inventoryid
inventory sales
--left join
select inventory.inventoryid,inventoryname
from inventory left join sale on
sale.inventoryid=inventory.inventoryid
where sale.inventoryid is NULL
inventory sales
--right join
select sale.inventoryid,inventoryname
from inventory right join sale on
sale.inventoryid=inventory.inventoryid
inventory sales
8
Veerendra
Output:
employeeID Full Name managerID managerName
1002 Kelvin Koh 1001 Tan Mei Ling
1003 Amin Wong 1002 Kelvin Koh
Output:
employeeID Full Name managerID managerName
1001 Tan Mei Ling
1002 Kelvin Koh 1001 Tan Mei Ling
1003 Amin Wong 1002 Kelvin Koh
9
Veerendra
SQL UNIONS
1. Union
--allow you to combine two tables select cust_lname,cust_fname from customer
together (but the no. of columns & union
each column’s data types for 2 tables select cust_lname,cust_fname from customer_2
must be match)
--don't need common key, only need
common attributes
--merge, not showing duplicate record
customer customer_2
customer customer_2
customer customer_2
--use subquery
select cust_lname,cust_fname from customer
where(cust_lname) not in
(select cust_lname from customer_2) and
(cust_fname) not in
(select cust_fname from customer_2)
10
Veerendra
customer
inventory sales
11
Veerendra
SQL RANKS
1. ROW_NUMBER() --get a unique sequential number for each row
--get different ranks for the row having similar values
SELECT *,
ROW_NUMBER() OVER(ORDER BY Salary DESC) SalaryRank
FROM EmployeeSalary
USING PARTITION BY
SELECT *,
RANK() OVER(PARTITION BY JobTitle ORDER BY Salary DESC)
SalaryRank
FROM EmployeeSalary
ORDER BY JobTitle, SalaryRank
12
Veerendra
3. DENSE_RANK()
-- if have duplicate values, SQL assigns different ranks to those rows.
-- will get the same rank for duplicate or similar values
SELECT *,
DENSE_RANK() OVER(ORDER BY Salary DESC) SalaryRank
FROM EmployeeSalary
ORDER BY SalaryRank
RANK() DENSE_RANK()
SELECT *, SELECT *,
RANK() OVER(PARTITION BY JobTitle ORDER DENSE_RANK() OVER(PARTITION BY JobTitle
BY Salary DESC) SalaryRank ORDER BY Salary DESC) SalaryRank
FROM EmployeeSalary FROM EmployeeSalary
ORDER BY JobTitle, SalaryRank ORDER BY JobTitle, SalaryRank
-- skip a rank if have similar values -- maintains the rank and does not give any gap
for the values
13
Veerendra
4. NTILE() -- can specify required how many group of result, and it will rank accordingly
SELECT *,
NTILE(3) OVER(ORDER BY Salary DESC) SalaryRank
FROM EmployeeSalary
ORDER BY SalaryRank;
Group 1
Group 2
Group 3
USING PARTITION BY
SELECT *,
NTILE(3) OVER(PARTITION BY JobTitle ORDER BY Salary DESC)
SalaryRank
FROM EmployeeSalary
ORDER BY JobTitle, SalaryRank;
Group 1
Group 2
Group 3
14
Veerendra
2. ISNULL(expression, value) --ParcelID is same, but UniqueID is different; can assume that if the ParcelID is
--expression: to test whether is same, the Property Address will be same
NULL, value: to return if Select a.ParcelID, a.PropertyAddress, b.ParcelID,
expression is NULL b.PropertyAddress,
ISNULL(a.PropertyAddress,b.PropertyAddress)
From NashvilleHousing a
JOIN NashvilleHousing b
on a.ParcelID = b.ParcelID
AND a.[UniqueID] <> b.[UniqueID]
Where a.PropertyAddress is null
-- Update record
Update a
SET PropertyAddress =
ISNULL(a.PropertyAddress,b.PropertyAddress)
From NashvilleHousing a
JOIN NashvilleHousing b
on a.ParcelID = b.ParcelID
AND a.[UniqueID] <> b.[UniqueID]
Where a.PropertyAddress is null
LEN(string)
15
Veerendra
Update NashvilleHousing
SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1,
CHARINDEX(',', PropertyAddress) -1 )
Update NashvilleHousing
SET PropertySplitCity = SUBSTRING(PropertyAddress,
CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress))
Select OwnerAddress,
PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3)
PARSENAME('object_name' ,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2)
, object_piece) ,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1)
From NashvilleHousing
--numbering works from
right to left
REPLACE(string, old_string,
new_string)
Update NashvilleHousing
SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress,
',', '.') , 3)
Update NashvilleHousing
SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',',
'.') , 2)
Update NashvilleHousing
SET OwnerSplitState = PARSENAME(REPLACE(OwnerAddress, ',',
'.') , 1)
16