SQL command for daily use ddl dml dcl dql

SQL

1. SQLFOREVERYONE
THEDEFINITIVEGUIDE
TableofContents
1.IntroductiontoSQL
2.BasicSQLSyntax
3.QueryingData
4.FilteringandSortingData
5.JoiningTables
6.AggregationFunctions
7.SubqueriesandNestedQueries
8.ModifyingDatabaseInformation
9.AdvancedSQLTechniques
10.OptimizationandPerformanceTuning
1.IntroductiontoSQL
SQLisastandardlanguagedesignedformanagingdatainrelational
databases.It'scommonlyusedtoquery,insert,update,andmodify
data.MostRDBMS(RelationalDatabaseManagementSystem)likeMySQL,
SQLite,Oracle,andPostgreSQLuseSQL.
Asadataanalyst,you'lloftenworkwithlargevolumesofdatastored
inthesedatabases.SQLbecomesanessentialtooltoretrieve,
manipulate,andanalyzethisdata.
1.1RDBMSandTables
InSQL,dataisstoredintables,justlikeanExcelspreadsheet.A
tableismadeupofrows(records)andcolumns(fields).Here'san
exampleofatable,Employees:
By:WaleedMousa

2. EmployeeID FirstName LastName Position
1 John Doe Analyst
2 Jane Doe Engineer
3 Mary Johnson Manager
2.BasicSQLSyntax
Let'slookatthefundamentalSQLcommands:SELECT,FROM,WHERE,GROUP
BY,HAVING,andORDERBY.
2.1SELECTandFROM
TheSELECTstatementisusedtoselectdatafromadatabase,andthe
FROMstatementspecifieswhichtabletogetthedatafrom.
SELECTFirstName,LastName
FROMEmployees;
ThisqueryretrievesallfirstandlastnamesfromtheEmployeestable.
Ifyouwanttoselectallcolumns,usethe*symbol:
SELECT*FROMEmployees;
2.2WHERE
TheWHEREclauseisusedtofilterrecords:
SELECT*
FROMEmployees
WHEREPosition='Analyst';
Thisqueryretrievesalldataforemployeeswhoareanalysts.
By:WaleedMousa

3. 2.3GROUPBYandHAVING
GROUPBYgroupsrowsthathavethesamevaluesinspecifiedcolumnsinto
aggregateddata.HAVINGisusedinsteadofWHEREwithaggregateddata.
SELECTPosition,COUNT(*)
FROMEmployees
GROUPBYPosition
HAVINGCOUNT(*)>1;
Thisqueryshowspositionsheldbymorethanoneemployee.
2.4ORDERBY
ORDERBYisusedtosortthedatainascendingordescendingorder:
SELECT*
FROMEmployees
ORDERBYLastNameASC;
Thisquerysortsemployeesbytheirlastnameinascendingorder.
3.QueryingData
TheSELECTstatementisnotjustforselectingsimplerows.Wecanuse
ittoperformcalculations,concatenations,andmore.
SELECTFirstName||''||LastNameasFullName,Position
FROMEmployees;
Thisqueryconcatenatesthefirstandlastnames,separatedbyaspace,
anddisplaysitasFullName.
4.FilteringandSortingData
ApartfromWHEREandORDERBY,SQLoffersBETWEEN,LIKE,andINto
filterdata.
By:WaleedMousa

4. 4.1BETWEEN
BETWEENisusedtofilterbyarange:
SELECT*
FROMOrders
WHEREOrderDateBETWEEN'2023-01-01'AND'2023-12-31';
Thisqueryselectsallordersplacedintheyear2023.
4.2LIKEandILIKE
LIKEisusedinaWHEREclausetosearchforaspecifiedpatternina
column.The"%"signisusedtodefinewildcards(missingletters)both
beforeandafterthepattern.Also,notethatLIKEiscasesensitive.
ILIKEcanbeusedforcase-insensitivesearch.
SELECT*
FROMEmployees
WHEREFirstNameLIKE'J%';
Thisqueryselectsallemployeeswithafirstnamestartingwith'J'.
4.3IN
INallowsyoutospecifymultiplevaluesinaWHEREclause:
SELECT*
FROMEmployees
WHEREPositionIN('Analyst','Engineer');
Thisqueryselectsallanalystsandengineers.
5.JoiningTables
JOINstatementsareusedtocombinerowsfromtwoormoretablesbased
onarelatedcolumn.ThedifferenttypesofjoinsincludeINNERJOIN,
LEFT(OUTER)JOIN,RIGHT(OUTER)JOIN,andFULL(OUTER)JOIN.
By:WaleedMousa

5. Considerthisadditionaltable,Departments:
DepartmentID DepartmentName
1 IT
2 Sales
3 HR
AndsupposeweaddaDepartmentIDfieldtotheEmployeestable.Here's
howwecanusedifferenttypesofjoins:
5.1INNERJOIN
SELECTEmployees.LastName,Employees.FirstName,Departments.DepartmentName
FROMEmployees
INNERJOINDepartmentsONEmployees.DepartmentID=Departments.DepartmentID;
Thisqueryretrievesthelistofemployeesalongwiththeirrespective
departmentnames.
5.2LEFT(OUTER)JOIN
SELECTEmployees.LastName,Employees.FirstName,Departments.DepartmentName
FROMEmployees
LEFTJOINDepartmentsONEmployees.DepartmentID=Departments.DepartmentID;
Thisqueryretrievesallemployeesandtheirdepartments,including
employeeswithnodepartment(theDepartmentNameforthemwillbe
NULL).
5.3RIGHT(OUTER)JOIN
SELECTEmployees.LastName,Employees.FirstName,Departments.DepartmentName
FROMEmployees
RIGHTJOINDepartmentsONEmployees.DepartmentID=Departments.DepartmentID;
Thisqueryretrievesalldepartmentsandtheiremployees,including
departmentswithnoemployees.
By:WaleedMousa

6. 5.4FULL(OUTER)JOIN
SELECTEmployees.LastName,Employees.FirstName,Departments.DepartmentName
FROMEmployees
FULLJOINDepartmentsONEmployees.DepartmentID=Departments.DepartmentID;
Thisqueryretrievesallcombinationsofemployeesanddepartments,
includingemployeeswithnodepartmentanddepartmentswithno
employees.
6.AggregationFunctions
SQLprovidesseveralfunctionstoperformcalculationsondata,suchas
COUNT(),SUM(),AVG(),MIN(),MAX(),andGROUP_CONCAT().
SELECTCOUNT(*)
FROMOrders
WHEREOrderDateBETWEEN'2023-01-01'AND'2023-12-31';
Thisqueryreturnsthetotalnumberofordersplacedintheyear2023.
7.SubqueriesandNestedQueries
AsubqueryisaSQLquerynestedinsidealargerquery.Asubquerymay
occurin:
●ASELECTclause
●AFROMclause
●AWHEREclause
ThesubquerycanbenestedinsideaSELECT,INSERT,UPDATE,orDELETE
statementorinsideanothersubquery.
SELECTEmployeeID,FirstName,Position
FROMEmployees
WHEREEmployeeIDIN(SELECTEmployeeIDFROMOrdersWHEREOrderTotal>1000);
Thisqueryselectsallemployeeswhohavemadeorderstotalingmore
than1000.
By:WaleedMousa

7. 8.ModifyingDatabaseInformation
SQLallowsyoutoinsert,update,anddeletedatawithINSERT,UPDATE,
andDELETEcommandsrespectively.Becarefulwhenusingthesecommands
asyoucanchangeyourdatapermanently.
8.1INSERT
INSERTINTOEmployees(EmployeeID,FirstName,LastName,Position)
VALUES(4,'Mark','Anderson','Analyst');
ThisqueryaddsanewrowtotheEmployeestable.
8.2UPDATE
UPDATEEmployees
SETPosition='SeniorAnalyst'
WHEREEmployeeID=4;
ThisquerychangesMarkAnderson'spositiontoSeniorAnalyst.
8.3DELETE
DELETEFROMEmployeesWHEREEmployeeID=4;
ThisquerydeletesMarkAnderson'srecordfromtheEmployeestable.
9.AdvancedSQLTechniques
Let'sdelveintomorecomplextechniqueswiththehelpofexamples.
9.1HandlingNULLvalues
NULLvalueinSQLmeansnoorzerovalue.Here'showyoucanuseIS
NULLandISNOTNULL:
SELECT*
FROMEmployees
WHEREDepartmentIDISNULL;
Thisqueryselectsallemployeeswhodon'tbelongtoanydepartment.
By:WaleedMousa

8. SELECT*
FROMEmployees
WHEREDepartmentIDISNOTNULL;
Thisqueryselectsallemployeeswhobelongtoadepartment.
9.2StringFunctions
SQLoffersseveralfunctionstomanipulatestrings.Someexamples
include:
●CONCAT():Concatenatestwoormorestrings.
●TRIM():Removesleadingandtrailingspacesofastring.
●LENGTH():Returnsthelengthofastring.
SELECTCONCAT(FirstName,'',LastName)asFullName,TRIM(Position),
LENGTH(FirstName)asNameLength
FROMEmployees;
Thisqueryretrievesafullnamebycombiningfirstandlastnames,the
positionafterremovingleadingandtrailingspaces,andthelengthof
thefirstname.
9.3DateandTimeFunctions
SQLprovidesmanyfunctionstoworkwithdateandtime.Someexamples
include:
●NOW():Returnsthecurrentdateandtime.
●CURDATE():Returnsthecurrentdate.
●CURTIME():Returnsthecurrenttime.
SELECTOrderID,OrderTotal,NOW()asQueryTime
FROMOrders
WHEREOrderDate=CURDATE();
Thisqueryretrievestoday'sordersalongwiththequeryexecution
time.
By:WaleedMousa

9. 9.4CaseStatements
CasestatementshelpinimplementingconditionallogicinSQL:
SELECTFirstName,Position,
CASE
WHENPosition='Analyst'THEN'JuniorLevel'
WHENPosition='Engineer'THEN'MidLevel'
ELSE'SeniorLevel'
ENDasJobLevel
FROMEmployees;
Thisquerycategorizesemployeesintojoblevelsbasedontheir
positions.
9.5WindowFunctions
Windowfunctionsperformcalculationsacrossasetoftablerowsthat
arerelatedtothecurrentrow:
SELECTFirstName,Position,Salary,
RANK()OVER(PARTITIONBYPositionORDERBYSalaryDESC)asRank
FROMEmployees;
Thisqueryranksemployeeswithintheirrespectivepositionsbasedon
theirsalaries.
10.OptimizationandPerformanceTuning
HerearesomeexamplesdemonstratingSQLoptimizationtechniques:
10.1EXPLAIN
MostSQLdatabasessupporttheEXPLAINcommand,whichshowsthe
executionplanofanSQLstatement.Thiscanhelpyouunderstandhow
yourSQLquerywillbeexecutedandwhereyoucanoptimizeit.
EXPLAINSELECT*FROMEmployees;
By:WaleedMousa

10. 10.2AvoidSELECT*
RatherthanusingSELECT*,specifythecolumnsyouneed.Thisreduces
theamountofdatathatneedstobereadfromthedisk.
SELECTFirstName,LastNameFROMEmployees;
10.3UseLIMIT
Ifyouonlyneedaspecificnumberofrows,useLIMITtopreventreading
unnecessarydata.
SELECT*FROMEmployeesORDERBYSalaryDESCLIMIT10;
Thisquerygetsthetop10employeeswiththehighestsalaries.
10.4Indexyourdata
Indexingyourdatacansignificantlyspeedupdataretrievaltimes.
Here'showyoucanaddanindex:
CREATEINDEXidx_employees_positionONEmployees(Position);
By:WaleedMousa

11. --What'sNext?
1.Practice,practice,practice:ThebestwaytoreinforceyourSQL
skillsisbypracticing.WebsiteslikeLeetCode,HackerRank,andSQLZoo
providehundredsofSQLproblemsthatyoucanpracticewith.
2.LearnDatabaseDesign:Understandinghowdatabasesarestructured
anddesignedwillhelpyouwritebetterandmoreefficientSQLqueries.
Lookintotopicssuchasnormalization,entity-relationshipmodels,and
dataintegrity.
3.ExploreAdvancedSQLTopics:Thistutorialcoveredthebasics,but
there'sstillalottolearn.Delveintomoreadvancedtopicslike
storedprocedures,triggers,views,andtransactioncontrol.
4.LearnAboutDatabaseAdministration:Althoughnottypicallypartof
aDataAnalyst'srole,understandinghowadatabaseisadministeredcan
provideusefulcontext.Thiscanalsoopenupnewopportunitiesinthe
realmofdatabasemanagement.
5.UnderstandSQLinthecontextofaprogramminglanguage:Ifyouare
familiarwithaprogramminglanguagelikePythonorR,trytouseSQL
commandswithintheselanguages.Thisoftengivesyoumoreflexibility
andallowsyoutoperformmorecomplexoperationswithyourdata.
6.LearnAboutDifferentSQLDatabases:TherearemanydifferentSQL
databases,suchasMySQL,SQLite,PostgreSQL,andOracle.Eachhasits
ownuniquefeaturesandsyntaxnuances.Familiarizeyourselfwiththe
onethat'smostrelevanttoyourworkorinterests.
9.ApplySQLinYourProjects:Theultimatetestofyourskillswillbe
applyingSQLinyourprojects.Whetherit'sforcleaningdata,data
wrangling,oranalysis,thepracticalapplicationofSQLwillsolidify
yourlearningandgiveyouvaluableexperience.
Remember,becomingproficientinSQLisajourney,notadestination.
Enjoytheprocessoflearningandexperimenting.Happyquerying!
By:WaleedMousa