Structured Query language (SQL) SQL Commands cL (dea witness pL (detnedatabase | [ome maninuate data] [rigs and date conta], TEL(éeae withthe | | Da rettove daa schemainOBWS} | |” preentinthe08) "| onthe data presenein| "™"s*Cvershegpenie| trom the DU wig the db) = CREATE INSERT ‘RANT cownarr seuect ono? uPpare REVOKE frowsace ATER oeLeTe ODL : Data Definition Language DML. Dota Manipulation Language aan (ct: oto contro! Longuage TL: Trorsocton Control Lanquoge (Dota Query canavage 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 ull 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 ron customer 6. Add new column to table 7. Add values to newly added column/ Update table alter table customer add_phonenunber varchar (20) _ update customer set phonenumber~' 1234545346" where customer id=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 fron customer where country='Thailand’ 10. Delete table drop table customer 11. Change data type alter table customer alter column phonenumber_varchar(1@)a | 1. Create database create database SaleOrder 2._ Use the database use SaleOrder 3. Create tables create table [Link] { CustomeriO int NOT null primary key, CustomerFirstName varchar(50) NOT null, CustomerLastName varciar(50) NOT nu! CustomerAddress varchar(50) NOT null, CustomerSuburb varchar(50) cull, CustomerCity varcharS0) NOT null CustomerPostCode char(4) null CustomerPhoneNumber char(12) nul ) create table dbo inventory ( InventoryID tinyint NOT null primary key, InventoryName varchar(50) NOT null, InventoryDescription varchar(255) nul, 7 create table dbo employee | EmployeeID tinyint NOT nul primary key, EmployeeFirstName varchar|50) NOT nul, EmployeeLastName varchar(50) NOT null EmployeeExtension char) nul } create table dbo sale ( SalelD tinyint not null primary key, CustomeriD int not null references customer(Customer!D), InventoryID tinyint “ot null references Inventory(InventorylD), EmployeeID tinyint not null references Employee|EmployeelD), SaleDate date not nul, SaleQuantity int not nu!l, SaleUnitPrice smallmoney not null ) 4, Check what table inside select * from information [Link] 5. 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 & View specifie 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 dese -- Order By Based on column no. without typing column name ~distinct: only show unique value select distinct customerlastname from customer order by customerlastname‘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) 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 ' 1%! In (search something) — search multiple items select * from customer where customerlastname in ‘Brown’, ‘Michae'’ im’) 10. > (search something) select © from customer where customerlastname > ‘Brown’ or customerlastname>'Cross’ rT <> (Not Equal) select * from customer where customerlastname <> 'Brown! 12 IS NULL = check null values select * from customer where customerlastname IS NULL FET IS NOT NULL select * from customer where customerlastname IS NOT NULL 14. between select © from sale where saleunitprice between 5 and 10 not include 5 & 10 15, 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%! 16. ‘sum select sale employeeid EmployeeFirstName, EmployeelastName , counl(") as [Number of order] sum(salequantity) as [Total Quantity] from saleemployee where sale employeeid - employee employeeid group by [Link] EmployeeFirstName, EmployeeLastName v7. count month select month(saledate) as [Month], count ( * ) as [Number of sale] sum(salequantity"saleunitorice as [Total Amount] from sale group by month(saledate) 18. max SELECT MAX(Salary) FROM EmployeeSalary 18, min SELECT MIN(Salary) FROM EmployeeSalary 20. average SELECT AVG (Salary) FROM EmployeeSalary21. having SELECT JobTitie, COUNT( JobTitle) FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON [Link] - [Link] GROUP BY JobTitle HAVING COUNT(JobTitle) > 1 SELECT JobTitle, AVG(Salary) FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON [Link] = [Link] GROUP BY JobTitle HAVING AVG(Salary) > 45000 ORDER BY AVG(Salary) 22. Change data type temporary for use — CASTlexpression AS datatype(length)) SELECT CAST(*2@17-08-25 @[Link].008' AS date) ~- CONVERT(data_type(length), expression, style) SELECT CONVERT (date, ‘2017-08-25 00:€0:00.000' ) 23. CASE Statement SELECT FirstName, LastName, Age, CASE WHEN Age > 30 THEN ‘Old* WHEN Age BETWEEN 27 AND 3@ 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 *.@3) END AS SalaryAfterRaise FROM EmployeeDemographics ED JOIN EmployeeSalary &S ON ED. EmployeeID - [Link] 24, 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 &S ON ED. EmployeeID - [Link] Fete Ne Oendet Sly Tele a es oes 2 Medtn Pain Fomale 600 Stay Hutson Milo $8000 5 Dwgtt —ScewleNale £9000 a ee)~-- only need to run this on next time EXEC Temp_Employee 27. 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 Enpoyeel0 Soy atavSday 1 [too «sa00 a7ano 3 10) esKO 47000 5 105 sonco 47500 = Subquery in From SELECT [Link], AllAvgSalary FROM (SELECT EmployeeID, Salary, AVG(Salary) OVER () AS AllavgSalary FROM EmployeeSalary) a ORDER BY [Link] > AaSiny 7908 7003 om = 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)SQL JOINS Inner Join Outer Join Self Join Cross Join [ Left Outer Join | [ rer outer ti | Full Outer Join getting data from multiple tables {explicit join - without using join command) select © from inventory sale where sale [Link] select inventoryname, saledate seleunitprice 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 getting data from multiple tables (implicit join - using join command) inner join select * from inventory inner join sale on [Link] select inventoryname,saledate seleunitprice salequantity saleunitprice’ salequantity as [Total Amount] from inventory inner join sale on sale inventoryid-inventory inventoryid order by inventoryname ~full cuter join (shows everything) select sale inventoryid inventoryname from inventory full outer join sale on sale inventoryid-inventory inventoryid where sale inventoryid is NUL25. String Functions = wane apace Select EnployeeID, TRIN(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 ([Link],1,3), Substring([Link],1,3), Substring (dem. LastName, 1,3) FROM Employeerrors err JOIN EmployeeDemographics dem ‘on Substring([Link],1,3) = Substring ([Link], 1,3) and Substring([Link],1,3) = Substring (dem. LastName, 1,3) =- UPPER and LOWER CASE Select firstname, LOWER(firstname) from EmployeeErrors Select Firstname, UPPER (FirstName) rom EmployeeErrors” 26. Stored Procedure ‘CREATE PROCEDURE aa Employee AS DROP TABLE IF EXISTS #temp_employee Create table #temp_employee ( JobTitle varchar(100), EmployeesPeriob 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.£mployeeID vhere EESEGMNMMMNEEN --- nake sure to change this An this script from original above group by JobTitle Select * From #temp_employee Go;~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 oe aaa wentoryid ~ without join: use subquery select inventoryid inventoryname from inventory where inventoryid not in (select inventoryid from sale) right join select [Link],inventoryname from inventory right join sale on [Link]- inventory inventoryid 3. Selfoin ~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, [Link]»" '[Link] as [Full Name], [Link], , [Link]:' '+[Link] as [Manager Name] from staff E inner join staff M ‘on ExmanageriD = [Link]!DOutput: employeelD FullName — managerID_ managerName 1002 Kelvin Koh 1001 Tan Mei Ling 1003 Amin Wong. 1002 Kelvin Koh ~left outer join (list all the employees) select E,employee!D, [Link]’ '-[Link] as [F Name], [Link], , [Link]' '+[Link] as [Manager Name] from staff E left outer join staff M ‘on [Link] = [Link] Output: employeelD FullName — manageriD_ managerName 1001 Tan Mei Ling 1002 Kelvin Koh 1001 Tan Mei Ling 1003 Amin Wong 002 Kelvin Koh 4. Gross Join -generate all combination of records (all possibility) (Cartesian Product) ‘select * from inventory cross join inventory2 Preset TEFTyOIN Tabs 8 ON Akey Be SQL JOINS ‘@ ici JOIN roses ON Axey= BK ‘SiR ay 1s NU FULL OUP JOIN Taam ficterJor8 Tas TULLOUTEROR Tien 9 ON Abs hey Duns esenSQL UNIONS 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_Iname,cust_fname from customer union select cust_Iname,cust_fname from customer_2 2. Union all ~merge, but show you everything, even the duplicate record select cust_Iname,cust_fname from customer union all select cust_Iname,cust_fname from customer_2 Intersect ceep only the rows in common to both query not showing duplicate record select cust_iname,cust_fname from customer intersect select cust_Iname,cust_fname from customer_2 ‘select ¢.cust_Iname,c.cust_fname from customer c,customer_2 2 where ¢ cust_Iname=c2.cust_iname and c.cust_fname=c2.cust_fname 4. Except ~generate only the records that are unique to the CUSTOMER table ‘select cust_iname,cust_fname from customer except select cust_Iname,cust_fname from customer_2 ~use subquery select cust_iname,cust_fhame from customer where(cust_iname) not in (select cust_Iname from customer_2) and (cust_fname) riot in (select cust_fname from customer_2) 10Table & View View table (view will be updated when update base) ~view is a result set of SQL statements, exists only for a single query select customerfirstname customerphonenumber, inventoryname, saledate salequantity saleunitprice,salequan as [Total Amount] from customer inner join sale on [Link] inne join inventory on sale inventoryid-inventory inventoryid »customerlastname as [Customer Name] *saleunitprice Temp table (temp will NOT be updated when update base) ~a single hashtag (H) 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 =xISTS #teap_Enployee Create table #EGRpLEWPLOVEE ( JobTitle varcnar 100), EnployeesPerdob int, avenge int AvgSalary int Insert INTO #temp_Enployee SELECT JobTitle, Count (JobTitle), Avg(Age), AVG(salary) FROM EmployeeDemographics emp 301 Employeesalary sal ‘ON emp. EnployeeID ~ [Link] group by JobTitle SELECT * FROM temp Employee 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 cre i SELECT FirstName, LastName, Gender, Salary, COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender FROM EmployeeDemographics ED JOIN Employeesalary ES ON ED. EnmployeeID = ES. EmployeeD WHERE Salary > '45000" SELECT FirstName, LastName, Gender, TotalGender FROM CTE_Employee WHERE TotalGender = (SELECT MIN(TotalGender) FROM CTE_Employee) Duplicate Table select customerfirstname~’ -customerlastname as [Customer Name] , customerphonenumber, inventoryname saledate,salequantity saleunitprice salequantity ‘ saleunitprice as Total Amount iteistomerRe from customer inner join sale on customer customerid=sale customerid inner join inventory on sale [Link] order by customerfirstname ~' + customerlastname inventoryname| SQL RANKS 1, ROW_NUMBER() get a unique sequential number for each row ~get fifferend|ranks for the row having similar values SELECT *, ROW_NUNBER() OVER(ORDER BY Salary DESC) SalaryRank FROM EnployeeSelary EnpiyeciD_obTio Sot Satnankc 1 [rons 7] Ragan anager 08000 1 2 Tm Seman 89000 2 4 1008 Salesman 40000 5100 Acsrutant 6 100 NULL 7 1001 Seeman 50007 8 MULL Salesman 2000 8 100 emumant 2000 101007 Sigler Raters $1000 10 11100 Fcopicrist 3600011 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 (PARTETHONIBYIJOBTHELE ORDER BY Salary DESC) ‘SalaryRank FROM Employeesalary ORDER BY JobTitle, SalaryRank _EmpyeoiO Joi ‘Say Sayan 1 [roto we 470 + 2 lope Aeenuntant 470001 3 ton _Accuintant__—_—42000_ 2 a 300007 5 loo Recoptonst = 3000 61008 Rego Manager 650001 7100s Sakeman 30007 8 toe = Saksman 4000 2 8 1001 Salesman 4500 1 Nyu__saesan 43000 4 111007 Supe Relators 21000 = get SAME] ranks for the row having similar values SELECT *, RANK() OVER(ORDER BY Salary DESC) SalaryRank FROM Employeesalary ORDER BY SalaryRank FropelD bite ‘Say Salayfonk 1 [10057 | Regenaltanager 65000 1 21003 Sama amo 2 210s oH 0000 3 41008 Seman ‘2000 5 100% Acsuniant 8 wom 8 NULL Saesman 43000 8 8 1000 estat 2000 9 10 1007 Supper Fatons 41000 10 11 1002 Roceptenst 3600011wrneieon 3. DENSE_RANKQ ~ 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 8Y Salary DESC) SalaryRank FROM Enployeeselary ORDER BY SalaryRank Empoye0iD Joorite Salary Salanftank 1 [1006] Regina Manager 1 2 60d Satesmen 3105 HR 4 1008 ——_—Satosman 51008 Aeccunant 6 wo NULL 71001 Satesman 8 NULL Satesman 8 1009 Accountant 10 1007 Supple Retains 11 1002 Recaptonst RANK() DENSE_RANK() SELECT *, SELECT *, RANK() OVER(PARTITION BY JobTitle ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary ORDER BY Jobritle, SalaryRank DENSE_RANK() OVER(PARTITION BY JobTitle ORDER BY Salary DESC) SalaryRank FROM EnployeeSalary ORDER BY JobTitle, Salarynank EenpkyeIO _JooTte Say Sainte me aa 5 eee 1 470001 1 fio NULL 47000 1 2 “100i Accoutani 47000 1 3 joan aa 3 1000 Accouiant «42000 2 3 pecouniant 2 4 105 HR 0000 1 ‘ HR 1 5 1002 Rocoptonist 300001 5 Rocoptenst 3 6 1006 __Fogonalanagor_65000_1 6 FegonalNangger 1 71003 Safesman 6300 7 Saesman 8 1001 Saesman 49004 8 1001 Salesman 9 1008 Salesman a0 9 1008 Salesman to_NULL___Salosman___ 42000 10_MUL__Soesman 111007 Supper Rabons 41000 “T T1100 —— Sin Rao = skip a rank if have similar values -~ nalittsins-the rank and does pot ive any: sep for the values 3B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; 7 2 Saewran 650001 3 10s HR so000 1 oe ‘4 10o1__ssesman__ 45000 1 $100 Saran 45000"? © 1004 Accomm’ $7000 2 === Group2 7 0 NL tn 2 NUL seman 20022 [> tas —~"heorrant tan 3] 102 Besoin aD 9 cnes 10 1007 Super eltons $1002. 3 11102 ___—Receptonst 96000 3 USING PARTITION BY SELECT =, NTILE(3) OVER(PARTITION BY JobTitle ORDER BY Salary DESC) Salarykank FROM EnployeeSalary ORDER BY JobTitle, SalaryRank Emel Jobe Sszy_Sotyark 1 foo 70001 2108 Aeoutant 0001 31000 ___Acscuntnt_a200_2 41005 He S000 7 5 1002 Reoptonst e000. 1 x Group 1 8 ' Group 2 : Group 3 7 141. Write the query to show the invoice number, the customer number, the customer name, the invoice date, and the invoice amount for all customers with a customer balance of $1,000 or more. invoice_num.c.cust_num.¢ cust_Iname.c.cust_fname.inv_date.inv_amount from customer ¢; invoice where ccust_num=invoice cust_num and cust_balance>=1000 select invoice_numc cust_num,cust_Inames" “scust_fname as [Name] inv_date,iny_amount from customer ¢ join invoice i fon c.cust_num=i cust_num where cust_balance>=1000 2. ISNULL(expression, value) ~expression: to test whether is NULL, value: to return if expression is NULL ~ParceliD is same, but UniquelD is different; can assume that if the ParcellD is same, the Property Address will be same Select [Link], [Link], [Link], [Link], ISNULL (a. PropertyAddress ,[Link] ) From NashvilleHousing a JOIN NashvilleHousing b on [Link] - [Link] AND a. [UniqueID] <> b.[UniqueID] Where [Link] is null ~ Update record Update a SET PropertyAddress ~ ISNULL (a. PropertyAddress,[Link] ) From NashvilleHousing a JOIN NashvilleHousing b on [Link] = [Link] AND a. (UniqueID] <> b.[UniqueID] Where [Link] is null 3. Split by delimiter 4 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 aaress oy if T] aoe FoxcHASEDR —GOOOLETISVILLE 2 1K2 FOKCHASE DOR, GOODLETISVILLE 1822 FOXCHASEOR —GOOOLETISVILLE 3 WEEFOXCHASE DR. GOODLETISVILLE T84FOXCHASE DR GODDLETISVILLE 41653 FOXCHASEOR, GOODLETTSVILLE 1859 FOXCHASEOR _GOOOLETISVILLE 8 1820 FOXCHASEOR,GOODLETTSVILLE 1820 FOXCHASEOR © GOOOLETISVILE ALTER TABLE NashvilleHousing Add PropertySplitAddress Nvarchar(255); ALTER TABLE NashvilleHousing Add PropertySplitcity Nvarchar (255) ;4 PARSENAME(‘object_name! object_piece) “numbering works from right to left * REPLACE(string, old_string, new_string) '8y Fang ing Sham e 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 (REPLACE (OwnerAddress, ',", '.') , 2) »PARSENAME (REPLACE (OwnerAddress, ',, '.') , 1) From NashvilleHousing Mocournrans) __ocokmnae)_flocourn rane) TEVILETW | os FoxcwsEDR —GOODLETTSVLLE TN Wek FOXCHASEDR GOOOLETTSVALE TA 186 FOXCHASEDR _GOODLETISVLLE 7H 1WSt FOXOAASEDR GOOOLETTSVLLE TN 1853 FOXGHASEDR —GOODLETISVLLE 7W 1uz9 FOXCHASE DR, COOOLETTSVALLE TM 1220 FOXCHASEDR _GOODLETISVLLE TN 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 "at tet) 3) Update NashvilleHousing SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',", "s*) > 2) Update NashvilleHousing SET OwnerSplitState = PARSENAME (REPLACI "Ay, 4) wnerAddress, ',* 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 > 2 Order by PropertyAddress