Download SQL short notes handwritten and more Study notes Software Development in PDF only on Docsity!
SQL
" whot is sQL 2
SQL (Structured Queny Language) is a programming
language desiged for managing dota in relati onal
database. SqL has a Variety oo 4anctions that
allow ds useng to read, manipulate, and chang e
data. Though sqL is commonly used by engineers
in seft ware devolopment, its also popula uilh
data analysts for afeaw reason :
"H% semantiolly eagy to understand md let.
Becauge it^ Can be (^) used to (^) access
lange
amounts of data dineetly uhene tts slored,
aralysts don't have to copy data into olhen
aplicat'ons. Compared to speread shet tools, ata
analysis done in SQL is easy to audit ond
Peplicate. or omaly etg,this means
looking for the aell euith he typo in the Brmula.
" SELECT* Example.
meanS no more
Ihe lolouing^ sqL^ statement^ seleete^ all^ !he Column.s from^ the^ alestable:
EXample - SELECT * FROM Sales;
"select (^) columhs (^) uise
Example - (^) SELECI year, month
Example
Rename Qolumns
FROM Sales
SELECT cuest (^) AS (^) "west (^) Region" FRoM Sales
" LIMIT Clause
west.
Ihe LIMIT elause is used to spect'y the number o (^) records to (^) petun
Example- SELECT*
FROM Sales
LIMIT 100
WHERE Clause
The wHERE^ clause^ is^ used^ to^ ken^ reconds.
If is uged to extraet only those reconds lhat
fulf:ll a specifted eonattion.
Example- SELECT * FROM Sales
WHERE Country " Canoda;
Jou can. perlorm anithmetie in salusing the Some
operators you.^ cuould in (^) Exeel: + (^) -)*, (^) ouever in (^) saL you (^) Can (^) ony (^) perform (^) anithmetie (^) acogg Columons on^ values in (^) agiven (^) row. To
clanify you
Con (^) only (^) add (^) values in (^) multiple columns (^) Arorn (^) the Same row (^) together asing (^) +-iyou want to (^) odad
Values acrogs multiple rocus, you'll need to use
9Pegate Panetins.
Exemple -^ SELECT (^) yeor
Example. SELECT (^) Jear, months twest s South,
FROM Sales ;
FROM Sales; Example -^ SELECT (^) year, month
months
west +south- 4year AS new.column
west Southy
west South,
FROM Sales j
wegt +south As (^) South.hsc
(west +sout)/2 AS Soulh uest-avg
CREATE TABLE
The cREATE TABLE statement is uged to cneate
anew toble in a.databage.
Example- (^) CREATE (^) TABLE person.l Person ID ints LastName Vanchar (255), FristNome varchar (255),
Aldness varchan (255)
city varchor (255)
" INSERT INTO
The INSERT INTO SBatement is used to insert neau peconds^ in^ atable.
- Speciky both the colum names and the values to be insented :
INSERT INTO (^) table-name (Colunnd, (^) colam2, (^) eolumns,..) VALUES (value 1, value2, Value3, ...);
- Ifyo ane adding volues kr all the colams oP e tetl you do not need to Spectty the eoumn names in he 3aL query"
INSERT INTO table_hame VALUES (value1, \alue2,value3,...);
" UPDATE Statement
The UPDATE stotement is used to madik he existing peconds in atobel.
Example
UPDATE Sales
UPDATE Maltiple Records
Example
SET ContactNane =" Alan", city=" Goa" WAERE Customer ID= 1;
Hig the WHERE clausge that detepmines how many
pecopds enill be updated.
UPDATE Sales
Notesi
SET Postal Code = 00000 WHERE Cotry =India" ;
Be Carelall eken. upateing recopds. T you omit
the WHERE clause, ALL Peconds cuill be updated!
DELETE State^ me^ nt
Te DELETE statemernt is used to delete exisling pecords in a toble.
Example pEETE ROM Sales WHERE CustomerNarme =" Bob':
Delete Al Recopdg I is (^) possible to (^) delete all (^) roue in (^) atable uthout
deleteing the^ table. (^) This (^) meang that (^) the (^) table StnacBure ,^ attribates , (^) and (^) inderes adl (^) be (^) itact:
Exmgle -
" Altases
DELETE FROM toble_pame
Aliases are used togve atabe, on a colun in atable, atempoary Aliases are ol en ased to make coumn. Tames more peadable.
name.
An (^) alias (^) ony exists Kor (^) the (^) duration (^) olat queny. An aliag is created tuth the hs keyuOPd.
Al as Calumn Fxampe SELECT Column_name AS alias _hame
FROM table name ;
" Alias Table Fxample. SELECT column.name (s) FROM table_name AS alias-name ;
"IN Operatop SELECT *
FROM Songs WHERE (^) artist IN (^) (Taylor (^) Suif.'Usher)
" BETWEEN Operator SELECT* FROM Songs WHERE year_Pank BETWEEN 5 AND 10
" AND Operator SELECT *
FROM Songs WHERE year = 2012 AND yean Pank <= 10;
OR OPeratop
SELECT *
FROM Sorngs
WHERE yeaR_Pank =5oR artist ="Soru" ;
" NOT OPerator
SELECT*
FROM Sales
WHERE NOT Country ="Jopan'
" Combining AND,OR and NOT
SELECT FROM Saleg
WHERE Counny-'Tapan' AND (CiBy- Go'oR cdy='ruri)
" QRDER By
SELECT
FROM Sales
ORDER By Country, Customer Name j
SELECT* FROM Sales
ORDERBy coun:try Ase, Customen Name DESC
Usigq Comments CHow to use Commente)
"SELECT -- This is select command FROM Sales
WHERE year =2020;
"/"^ Here's^ a^ Comment^ So^ long^ and^ deseniptive that it could^ only^ ft^ on^ mulliple^ lines.^ fortanately it, too, will not aleet how thig code runs. */ SELECT *
FROM Sales
WHERE year^ =^2015 ;
Example - SELECT MIN CColumn -name)
FROM toble_name WHERE Condition;
Example -^ S£LECT MAx Ccolunnnome)
FROM toble.name WHERE Condition
Example :- SELECT Ava Ccolumn-hame) FROM able.name WHERE Con.dition ;
The SQL GiROUP BY clause GROUP 8S ollows you to Separate aata into groaps.,
chich can be aggregated independently of one anodher.
SELECT year, COUNT (*)^ AS^ Count FROM Sales GAOUP BS year 3
Multile colamn SELECT year, month, COUNT()^ As^ Count FROM Saleg
GROUP 8y^ year,^ mondh^ ;
GROUP BY^ Column (^) numbens
SELECT year, morth, COUNTO AS count FROM Sales
GiROUP By 1,
Using GhROUP BS wth ORDER BY
SELECT yeap, month, COUNT)AS Count FROM &ales GiROUP BN yean, month
ORDER BY month, Jear i
Using GROUP BY audth IMTT
SELECT Column hames FROM table.name WHERE Condetien GhROUP By column hame
LIMIT umber ;
HAVING Clause
The HAVING clause was added to SQL becuse the WHERE keyuord cannot be used aith aggregate
funetions.
Syntax CASE
END ;
WHEN Cohdition1 THEN pesult 1 WHEN Condition 2 THENpesult WHEN Condition N THEN pesultN ELSE resul!
Example (^) SELECT (^) OrderID, (^) Quondty CASE
WHEN Quantity > 30 THEN'he quontity is greato ba
WAEN quantity= 30 THEN he quartityie 30"
ELSE "The quantity is under 20
END AS Quantity Text
FROM 8oes;
SQL DISTINCT
Jou'll occasionally^ want^ to^ look^ at^ nly^ the^ tunigue values ina^ particaar^ colunn.^ You^ Can^ do^ this^ using SELECT DISTINCT yntax.
Exomple:- SELECT^ DIS^ TINCT^ month FROM Sales ;
"SELECT DISTINCT year, month
FROM Sales ;
Using (^) DISTINCT in (^) agqreqations. SELECT COUNT (DISTINCT month) AS Ungue-months
MySQL JOINS
FROM Sales;
A (^) TOIN (^) clause ts (^) used to (^) combine rows (^) rom two oron more tables, based ona nelated column between them.
Fxampe: SELECT * FROM benn.calege- lotbal Playens playens JOIN benm. college. Poolball teams team ON teamg. 3ehool_namne =plasyers .sehol
Suppocted Iypes o ToTNS in My SQL "INNER JOIN : Retunns records that have matching Values in. both tables.
INNER
"LEFT JOIN: Retuns all necopds hom the lefl }oble.
ond the matched recopds rom he night table.
"RIGHT JOIN : Returns all neconds rom the ight
table,and the matched peopde from he le4 table.
" CROSS^ JoIN:^ Returns^ all^ records^ Brom^ bolh^ tables.
Tges
RIGHT JOIN
JOIN (ables table^ table
LEFT JOTN
CROSS 51Etno.e J0IN
RIGHT JOIN Ihe RIGHT JOIN keyuond petunns all peconds from
the night iable (Aables), and !he nalehig necras ti? any)rom the lelf table Cloble 1).
Example:
RIGHT JOIN
(tablei
SELECT Column_name (s) FROM Aable 1 RTGHT JOIN table 2
CROSS JOIN
ON table1. Colunnname =toble 2.columnane ;
The CROSs JOTN kejuord betarns all necovds rom
both tales Ctable 1 ond table 2).
CROSS JOIN
SELECT colum_name (9) FROM table 1
CROSS JOTIN toble 2;
SELF JOTN
Asel! Join is a reaular join, bat the table is Joined uith dsel.
Exomple :
SELECTaol umn-name (s) FROM table1 T1 , table1.T
WHERE Cendition
UNTON Operator sQL joins allow yo¡ to cornoine tu catageta Side-by. side, but UNTON allQwS you to staek one dataset on top oPthe (^) other. (^) Put (^) differenlly, UNION (^) allowsyou to
aurite two, Separate sELECT State ments , and to have
the nesults o ene statemerd digplay in the Same table
as fhe results hrom the other staBement.
Example i C SELECT Qolumn-name (s) FROM table 1 UNION
SELECTCdeonn.name ($) FROM table 2;
" SELECT olumm_name (s) FROM Lable 1 UNTON ALL SELECT coumn_nane Cs) FROM table 2;
IN Operator The INopenaBor allous youto SpeciBy meltiple Values in a wHERE ause.
The TNopenaAor^ is^ a^ shorthond^ 4or^ mulliple^ oA Condetions.
