Structured Query language (SQL)

 

1. Create database	create database sample2
2. Use the database	use sample2
3. Create table	create table customer ( customerid int identity(1,1) primary key, customernumber int not null unique check (customernumber>0), lastname varchar(30) not null, firstname varchar(30) not null, areacode int default 71000, address varchar(50), country varchar(50) default 'Malaysia' )
4. Insert values into table	insert into customer values (100,'Fang Ying','Sham','418999','sdadasfdfd',default), (200,'Mei Mei','Tan',default,'adssdsadsd','Thailand'), (300,'Albert','John',default,'dfdsfsdf',default)
5. Display record from table	-- display all records select * from customer -- display particular columns select customerid, customernumber, lastname, firstname from customer
6. Add new column to table	alter table customer add phonenumber varchar(20)
7. Add values to newly added column/ Update table	update customer set phonenumber='1234545346' where customerid=1 update customer set phonenumber='45554654' where customerid=2
8. Delete a column	alter table customer drop column phonenumber
9. Delete record from table --if not put ‘where’, will delete all record	delete from customer where country='Thailand'
10. Delete table	drop table customer
11. Change data type	alter table customer alter column phonenumber varchar(10)
12. Check what table inside	select * from information_schema.tables
13. View specific row	--top: show only the first two select top 2 * from customer --top 40 percent: also means show the first two select top 40 percent * from customer
14. View specific column	--sort result (by default is ascending) select customerfirstname, customerlastname from customer order by customerlastname desc select customerfirstname, customerlastname from customer order by 4, 2, 3 desc -- Order By Based on column no. without typing column name --distinct: only show unique value select distinct customerlastname from customer order by customerlastname
15. 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)
16. 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%'
17. In (search something)	-- search multiple items select * from customer where customerlastname in ('Brown', ‘Michael’, ’Jim’)
18. > (search something)	select * from customer where customerlastname > 'Brown' or customerlastname>'Cross'
19. <> (Not Equal)	select * from customer where customerlastname <> 'Brown'
20. IS NULL	-- check null values select * from customer where customerlastname IS NULL
21. IS NOT NULL	select * from customer where customerlastname IS NOT NULL
22. between	select * from sale where saleunitprice between 5 and 10 --not include 5 & 10
23. count	-- returns the number of rows in a table -- AS means aliasing, temporary giving name to a column/ table select count(*) as [Number of Records] from customer where customerfirstname like 'B%'
24. sum	select sale.employeeid ,EmployeeFirstName, EmployeeLastName , count(*) as [Number of order] , sum(salequantity) as [Total Quantity] from sale,employee where sale.employeeid = employee.employeeid group by sale.employeeid ,EmployeeFirstName, EmployeeLastName
25. count month	select month(saledate) as [Month], count ( * ) as [Number of sale], sum(salequantity*saleunitprice) as [Total Amount] from sale group by month(saledate)
26. Max/Min/Average	SELECT MAX(Salary) FROM EmployeeSalary
27. having	SELECT JobTitle, COUNT(JobTitle) FROM EmployeeDemographics ED JOIN EmployeeSalary ES  ON ED.EmployeeID = ES.EmployeeID GROUP BY JobTitle HAVING COUNT(JobTitle) > 1 SELECT JobTitle, AVG(Salary) FROM EmployeeDemographics ED JOIN EmployeeSalary ES  ON ED.EmployeeID = ES.EmployeeID GROUP BY JobTitle HAVING AVG(Salary) > 45000 ORDER BY AVG(Salary)
28. Change data type temporary for use	-- CAST(expression AS datatype(length)) SELECT CAST('2017-08-25 00:00:00.000' AS date) -- CONVERT(data_type(length), expression, style) SELECT CONVERT(date,'2017-08-25 00:00:00.000')
29. CASE Statement	SELECT FirstName, LastName, Age, CASE  WHEN Age > 30 THEN 'Old'  WHEN Age BETWEEN 27 AND 30 THEN 'Young'  ELSE 'Baby' END FROM EmployeeDemographics ED WHERE Age IS NOT NULL ORDER BY Age -- 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
30. Partition By --returns a single value for each row	SELECT FirstName, LastName, Gender, Salary, COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID
31. String Functions	-- Remove space Select EmployeeID, TRIM(EmployeeID) AS IDTRIM FROM EmployeeErrors Select EmployeeID, RTRIM(EmployeeID) as IDRTRIM FROM EmployeeErrors Select EmployeeID, LTRIM(EmployeeID) as IDLTRIM FROM EmployeeErrors -- 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) -- UPPER and LOWER CASE Select firstname, LOWER(firstname) from EmployeeErrors Select Firstname, UPPER(FirstName) from EmployeeErrors"
32. Stored Procedure	CREATE PROCEDURE Temp_Employee @JobTitle nvarchar(100) AS DROP TABLE IF EXISTS #temp_employee Create table #temp_employee ( JobTitle varchar(100), EmployeesPerJob int , AvgAge int, AvgSalary int ) Insert into #temp_employee SELECT JobTitle, Count(JobTitle), Avg(Age), AVG(salary) FROM EmployeeDemographics emp JOIN EmployeeSalary sal  ON emp.EmployeeID = sal.EmployeeID where JobTitle = @JobTitle --- make sure to change this in this script from original above group by JobTitle Select * From #temp_employee GO; By Fang Ying, Sham 6 --- only need to run this on next time EXEC Temp_Employee @JobTitle = 'Salesman'
33. Subquery	-- Subquery in Select SELECT EmployeeID, Salary, (SELECT AVG(Salary) FROM EmployeeSalary) AS AllAvgSalary FROM EmployeeSalary -- 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) SELECT EmployeeID, JobTitle, Salary FROM EmployeeSalary WHERE Salary in (SELECT Max(Salary) FROM EmployeeSalary)
1. getting data from multiple tables (explicit join - without using join command)	select * from inventory,sale where sale.inventoryid=inventory.inventoryid select inventoryname,saledate,saleunitprice,salequantity,salequantity*saleunitprice as [Total amount] from sale,inventory where sale.inventoryid=inventory.inventoryid group by sale.inventoryid,inventoryname,saledate,salequantity,saleunitprice order by inventoryname
2. getting data from multiple tables (implicit join - using join command)	--inner join select * from inventory inner join sale on sale.inventoryid=inventory.inventoryid select inventoryname,saledate,saleunitprice,salequantity,saleunitprice*salequantity as [Total Amount] from inventory inner join sale on sale.inventoryid=inventory.inventoryid order by inventoryname
	--full outer join (shows everything) select sale.inventoryid,inventoryname from inventory full outer join sale on sale.inventoryid=inventory.inventoryid where sale.inventoryid is NULL
	--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
	--left join select inventory.inventoryid,inventoryname from inventory left join sale on sale.inventoryid=inventory.inventoryid where sale.inventoryid is NULL   -- without join: use subquery select inventoryid,inventoryname from inventory where inventoryid not in (select inventoryid from sale)
	--right join select sale.inventoryid,inventoryname from inventory right join sale on sale.inventoryid=inventory.inventoryid
3. Self Join --commonly used in processing hierarchy	--inner join Staff Table employeeID employeefirstname employeelastname managerID 1001 Tan Mei Ling NULL 1002 Kelvin Koh 1001 1003 Amin Wong 1002   select E.employeeID, E.employeefirstname+' '+E.employeelastname as [Full Name], E.managerID, , M.employeefirstname+' '+M.employeelastname as [Manager Name] from staff E inner join staff M on E.managerID = M.employeeID     --left outer join (list all the employees) select E.employeeID, E.employeefirstname+' '+E.employeelastname as [F Name], E.managerID, , M.employeefirstname+' '+M.employeelastname as [Manager Name] from staff E left outer join staff M on E.managerID = M.employeeID
4. Cross Join --generate all combination of records (all possibility	select * from inventory1 cross join inventory2
1. Union --allow you to combine two tables together (but the no. of columns & each column’s data types for 2 tables must be match) --don't need common key, only need common attributes --merge, not showing duplicate record	select cust_lname,cust_fname from customer union select cust_lname,cust_fname from customer_2
2. Union all --merge, but show you everything, even the duplicate record	select cust_lname,cust_fname from customer union all select cust_lname,cust_fname from customer_2
3. Intersect --keep only the rows in common to both query --not showing duplicate record	select cust_lname,cust_fname from customer intersect select cust_lname,cust_fname from customer_2
	select c.cust_lname,c.cust_fname from customer c,customer_2 c2 where c.cust_lname=c2.cust_lname and c.cust_fname=c2.cust_fname
4. Except --generate only the records that are unique to the CUSTOMER table	select cust_lname,cust_fname from customer except select cust_lname,cust_fname from 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)
Table & View
1. view table (view will be updated when update base) --view is a result set of SQL statements, exists only for a single query	create view CustomerView as select customerfirstname+' '+customerlastname as [Customer Name] , customerphonenumber, inventoryname,saledate,salequantity,saleunitprice,salequantity*saleunitprice as [Total Amount] from customer inner join sale on customer.customerid=sale.customerid inner join inventory on sale.inventoryid=inventory.inventoryid
2. Temp table (temp will NOT be updated when update base) --a single hashtag (#) sign must be added in front of their names --used to store data temporarily, physically created in the Tempdb database --can perform CRUD, join, and some other operations like the persistent database tables	DROP TABLE IF EXISTS #temp_Employee Create table #temp_Employee ( JobTitle varchar(100), EmployeesPerJob int, AvgAge int, AvgSalary int ) Insert INTO #temp_Employee SELECT JobTitle, Count(JobTitle), Avg(Age), AVG(salary) FROM EmployeeDemographics emp JOIN EmployeeSalary sal  ON emp.EmployeeID = sal.EmployeeID group by JobTitle SELECT * FROM #temp_Employee
3. CTE (Common Table Expression) --create temporary result set which is used to manipulate the complex sub-queries data --created in memory rather than Tempdb database, so cannot create any index on CTE.	WITH CTE_Employee AS ( SELECT FirstName, LastName, Gender, Salary, COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID WHERE Salary > '45000' ) SELECT FirstName, LastName, Gender, TotalGender FROM CTE_Employee WHERE TotalGender = (SELECT MIN(TotalGender) FROM CTE_Employee)
4. Duplicate Table	select customerfirstname+' '+customerlastname as [Customer Name] , customerphonenumber, inventoryname,saledate,salequantity,saleunitprice,salequantity*saleunitprice as [Total Amount] into customerRec from customer inner join sale on customer.customerid=sale.customerid inner join inventory on sale.inventoryid=inventory.inventoryid order by customerfirstname +' '+ customerlastname,inventoryname
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
2. RANK()	--specify rank for each row in the result set --use PARTITION BY to performs calculation on each group --each subset get rank as per Salary in descending order USING PARTITION BY SELECT *,  RANK() OVER(PARTITION BY JobTitle ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary ORDER BY JobTitle, SalaryRank   NOT USING PARTITION BY -- get SAME ranks for the row having similar values SELECT *,  RANK() OVER(ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary ORDER BY SalaryRank
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
3. Split by delimiter ❖ SUBSTRING(string, start, length) ❖ CHARINDEX(substring, string, start) ❖ LEN(string)	SELECT PropertyAddress, SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1 ) as Address , SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress)) as City From NashvilleHousing   ALTER TABLE NashvilleHousing Add PropertySplitAddress Nvarchar(255); ALTER TABLE NashvilleHousing Add PropertySplitCity Nvarchar(255);   Update NashvilleHousing SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1 ) Update NashvilleHousing SET PropertySplitCity = SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress))
PARSENAME('object_name' , object_piece) --numbering works from right to left ❖ REPLACE(string, old_string, new_string)	Select OwnerAddress, PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3) ,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2) ,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1) From NashvilleHousing   ALTER TABLE NashvilleHousing Add OwnerSplitAddress Nvarchar(255); ALTER TABLE NashvilleHousing Add OwnerSplitCity Nvarchar(255); ALTER TABLE NashvilleHousing Add OwnerSplitState Nvarchar(255); Update NashvilleHousing SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3) Update NashvilleHousing SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2) Update NashvilleHousing SET OwnerSplitState = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1)
5. Remove duplicate records	WITH RowNumCTE AS( Select *, ROW_NUMBER() OVER ( PARTITION BY ParcelID, PropertyAddress, SalePrice, SaleDate, LegalReference ORDER BY UniqueID) as row_num From NashvilleHousing order by ParcelID ) --DELETE Select * From RowNumCTE Where row_num > 1 Order by PropertyAddress