SQL: A Comprehensive Beginner's Tutorial for Learning SQL Programming Sequentially

By Ryan roffe

 

Are you on the hunt for a dynamic and effective programming language?

Have you experimented with various options but found none quite up to par? Perhaps it's time to consider SQL.

In today's market, there's a plethora of programming languages, ranging from the straightforward to the exceedingly intricate. As a novice, you likely seek something user-friendly and comprehensible, and SQL, or Structured Query Language, might just fit the bill.

Within the pages of " SQL: A Comprehensive Beginner's Tutorial for Learning SQL Programming Sequentially
" you'll discover a

comprehensive manual to kickstart your journey, covering essential topics such as:

Data definition language

SQL joins and unions

Maintaining data integrity

Database creation

Database administration

Table modification and control

And much more...

When on the quest for a programming language that suits you best, SQL stands out for its ease of use and adaptability for beginners. With this book tailored for novices, you could soon find yourself proficiently writing your own programs, steadily advancing through each enlightening chapter.

 

• Language: English
• Publisher: Ryan roffe
• Release date: Jan 27, 2024
• ISBN: 9798224723683

Book preview

INTRODUCTIONB B

NeverB BwriteB BwhenB ByouB BcanB Btalk.B BNeverB BtalkB BwhenB ByouB BcanB Bnod.B BAndB BneverB BputB BanythingB BinB BanB Be-mail.B B–B BEliotB BSpitzer

OnB BaB BhardB Bdisk,B BdataB BcanB BbeB BstoredB BinB BdifferentB BfileB Bformats.B BItB BcanB BbeB BstoredB BinB BtheB BformB BofB BtextB Bfiles,B BwordB Bfiles,B Bmp4B Bfiles,B Betc.B BHowever,B BaB BuniformB BinterfaceB BthatB BcanB BprovideB BaccessB BtoB BdifferentB BtypesB BofB BdataB BunderB BoneB BumbrellaB BinB BaB BrobustB BandB BefficientB BmannerB BisB Brequired.B BHere,B BtheB BroleB BofB BdatabasesB Bemerges.

TheB BdefinitionB BofB BaB BdatabaseB BisB BaB BcollectionB BofB BinformationB BstoredB BinB BcomputerB BinB BaB BwayB BthatB BitB BcanB BeasilyB BbeB Baccessed,B BmanagedB BandB Bmanipulated.

DatabasesB BstoreB BdataB BinB BtheB BformB BofB BaB BcollectionB BofB BtablesB BwhereB BeachB BtableB BstoresB BdataB BaboutB BaB BparticularB Bentity.B BTheB BinformationB BthatB BweB BwantB BtoB BstoreB BaboutB BstudentsB BwillB BbeB BrepresentedB BinB BtheB BcolumnsB BofB BtheB Btable.B BEachB BrowB BofB BtheB BtableB BwillB BcontainB BtheB BrecordB BofB BaB BparticularB Bstudent.B BEachB BrecordB BwillB BbeB BdistinguishedB BbyB BaB BparticularB Bcolumn,B BwhichB BwillB BcontainB BaB BuniqueB BvalueB BforB BeachB Brow.

SupposeB ByouB BwantB BtoB BstoreB BtheB BID,B Bname,B Bage,B Bgender,B BandB BdepartmentB BofB BaB Bstudent.B BTheB BtableB BinB BtheB BdatabaseB BthatB BwillB BcontainB BdataB BforB BthisB BstudentB BwillB BlookB BlikeB Bthis:

SID

Tom

14

Male

Mike

12

Male

Sandy

13

Female

Jack

10

Male

StudentB BTable

Here,B BtheB BletterB BSB BhasB BbeenB BprefixedB BwithB BtheB BnameB BofB BeachB Bcolumn.B BThisB BisB BjustB BoneB BofB BtheB BconventionsB BusedB BtoB BdenoteB BcolumnB Bnames.B BYouB BcanB BgiveB BanyB BnameB BtoB BtheB Bcolumns.B B(WeB BwillB BlookB BatB BhowB BtoB BcreateB BtablesB BandB BcolumnsB BwithinB BitB BinB BtheB BcomingB Bchapters.)B BItB BisB BmuchB BeasierB BtoB Baccess,B Bmanipulate,B BandB BmanageB BdataB BstoredB BinB BthisB Bform.B BSQLB BqueriesB BcanB BbeB BexecutedB BonB BtheB BdataB BstoredB BinB BtheB BformB BofB BtablesB BthatB BhaveB BrelationshipsB BwithB BotherB Btables.

AB BdatabaseB Bdoesn’tB BcontainB BaB BsingleB Btable.B BRather,B BitB BcontainsB BmultipleB BrelatedB Btables.B BRelationshipsB BmaintainB BdatabaseB BintegrityB BandB BpreventB BdataB Bredundancy.B BForB Binstance,B BifB BtheB BschoolB BdecidesB BtoB BrenameB BtheB BComputerB BdepartmentB BfromB BComputerB BtoB BCompB B&B BSoft,B ByouB BwillB BhaveB BtoB BupdateB BtheB BrecordsB BofB BallB BstudentsB BinB BtheB BComputerB Bdepartment.B BYouB BwillB BhaveB BtoB BupdateB BtheB B1st,B B4th,B BandB B5thB BrecordsB BofB BtheB BstudentB Btable.

ItB BisB BeasyB BtoB BupdateB BthreeB Brecords;B Bhowever,B BinB BrealB BlifeB Bscenarios,B BthereB BareB BthousandsB BofB BstudentsB BandB BitB BisB BanB BuphillB BtaskB BtoB BupdateB BtheB BrecordsB BofB BallB BofB Bthem.B BInB BsuchB Bscenarios,B BrelationshipsB BbetweenB BdataB BtablesB BbecomeB Bimportant.B BForB Binstance,B BtoB BsolveB BtheB BaforementionedB BredundancyB Bproblem,B BweB BcanB BcreateB BanotherB BtableB BnamedB BDepartmentB BandB BstoreB BtheB BrecordsB BofB BallB BtheB BdepartmentsB BinB BthatB Btable.B BTheB BtableB BwillB BlookB BlikeB Bthis:

DID

DName

DCapacity

101

Electrical

800

102

Computer

500

103

Mechanical

500

DepartmentB BTable

Now,B BinB BtheB BstudentB Btable,B BinsteadB BofB BstoringB BtheB BdepartmentB Bname,B BtheB BdepartmentB BIDB BwillB BbeB Bstored.B BTheB BstudentB BtableB BwillB BbeB BupdatedB BlikeB Bthis:

SID

Tom

14

Male

Mike

12

Male

Sandy

13

Female

Jack

10

Male

TableB BStudent

YouB BcanB BseeB BthatB BtheB BdepartmentB BnameB BcolumnB BhasB BbeenB BreplacedB BbyB BtheB BdepartmentB BidB Bcolumn,B BrepresentedB BbyB BDID.B BTheB B1st,B B4th,B BandB B5thB BrowsB BthatB BwereB BpreviouslyB BassignedB BtheB BComputerB BdepartmentB BnowB BcontainB BtheB BidB BofB BtheB Bdepartment,B BwhichB BisB B102.B BNow,B BifB BtheB BnameB BofB BtheB BdepartmentB BisB BchangedB BfromB BComputerB BtoB BCompB B&B BSoft,B BthisB BchangeB BhasB BtoB BbeB BmadeB BonlyB BinB BoneB BrecordB BofB BtheB BdepartmentB BtableB BandB BallB BtheB BassociatedB BstudentsB BwillB BbeB BautomaticallyB BreferredB BtoB BtheB BupdatedB BdepartmentB Bname.

AdvantagesB BofB BDatabases

TheB BfollowingB BareB BsomeB BofB BtheB BmajorB BadvantagesB BofB Bdatabases:

DatabasesB BmaintainB BdataB Bintegrity.B BThisB BmeansB BthatB BdataB BchangesB BareB BcarriedB BoutB BatB BaB BsingleB BplaceB BandB BallB BtheB BentitiesB BaccessingB BtheB BdataB BgetB BtheB BlatestB BversionB BofB BtheB Bdata.

ThroughB BcomplexB Bqueries,B BdatabasesB BcanB BbeB BefficientlyB Baccessed,B Bmodified,B BandB Bmanipulated.B BSQLB BisB BdesignedB BforB BthisB Bpurpose.

DatabasesB BavoidB BdataB Bredundancy.B BThroughB BtablesB BandB Brelationships,B BdatabasesB BavoidB BdataB BredundancyB BandB BdataB BbelongingB BtoB BparticularB BentitiesB BresidesB BinB BaB BsingleB BplaceB BinB BaB Bdatabase.

DatabasesB BofferB BbetterB BandB BmoreB BcontrolledB Bsecurity.B BForB Bexample,B BusernamesB BandB BpasswordsB BcanB BbeB BstoredB BinB BtablesB BwithB BexcessiveB BsecurityB Blevels.

B BTypesB BofB BSQLB BQueries

OnB BtheB BbasisB BofB Bfunctionality,B BSQLB BqueriesB BcanB BbeB BbroadlyB BclassifiedB BintoB BaB BcoupleB BofB BmajorB BcategoriesB BasB Bfollows:

DataB BDefinitionB BLanguageB B(DDL)

DataB BDefinitionB BLanguageB B(DDL)B BqueriesB BareB BusedB BtoB BcreateB BandB BdefineB BschemasB BofB Bdatabases.B BTheB BfollowingB BareB BsomeB BofB BtheB BqueriesB BthatB BfallB BinB BthisB Bcategory:

CREATEB B–B BtoB BcreateB BtablesB BandB BotherB BobjectsB BinB BaB Bdatabase

ALTERB B–B BtoB BalterB BdatabaseB Bstructures,B BmainlyB Btables.

DROPB B-B BdeleteB Bobjects,B BmainlyB BtablesB BfromB BtheB Bdatabase

DataB BManipulationB BLanguage

DataB BManipulationB BLanguageB B(DML)B BqueriesB BareB BusedB BtoB BmanipulateB BdataB BwithinB Bdatabases.B BTheB BfollowingB BareB BsomeB BexamplesB BofB BDMLB Bqueries.B B

SELECTB B–B BselectB BdataB BfromB BtablesB BofB BaB Bdatabase

UPDATEB B-B BupdatesB BtheB BexistingB BdataB BwithinB BaB Btable

DELETEB B-B BdeletesB BallB BrowsB BfromB BaB Btable,B BbutB BtheB BspaceB BforB BtheB BrecordB Bremains

CHAPTERB B1:B BTheB BDataB BDefinitionB BLanguageB B(DDL)

EveryB BmanB BhasB BaB BrightB BtoB BhisB Bopinion,B BbutB BnoB BmanB BhasB BaB BrightB BtoB BbeB BwrongB BinB BhisB Bfacts.B B–B BBernardB BMannesB BBaruch

SQLB BdataB BdefinitionB BlanguageB BisB BusedB BtoB BdefineB BnewB Bdatabases,B BdataB Btables,B BdeleteB Bdatabases,B BdeleteB BdataB Btables,B BandB BalterB BdataB BtableB BstructuresB BwithB BtheB BfollowingB BkeyB Bwords;B Bcreate,B BalterB BandB Bdrop.B BInB BthisB Bchapter,B BweB BwillB BhaveB BaB BdetailedB BdiscussionB BaboutB BtheB BSQLB BDataB BDefinitionB BLanguageB BinB BaB BpracticalB Bstyle.

DDLB BforB BDatabaseB BandB BTableB BCreation

TheB BdatabaseB BcreationB BlanguageB BisB BusedB BtoB BcreateB BdatabasesB BinB BaB BdatabaseB BmanagementB Bsystem.B BTheB BlanguageB BsyntaxB BisB BasB BdescribedB Bbelow:

CREATEB BDATABASEB Bmy_Database

ForB Bexample,B BtoB BcreateB BaB Bcustomer_detailsB BdatabaseB BinB ByourB BdatabaseB BmanagementB Bsystem,B BuseB BtheB BfollowingB BSQLB BDDLB Bstatement:

CREATEB BDATABASEB Bcustomer_details

PleaseB BrememberB BthatB BSQLB BstatementB BisB BcaseB Binsensitive.B BNext,B BweB BneedB BtoB BcreateB BtheB BvariousB BcustomerB BtablesB BthatB BwillB BholdB BtheB BrelatedB BcustomersB Brecords,B BinB BtheB BearlierB BcreatedB B‘customer_details’B Bdatabase.B BThisB BisB BwhyB BtheB BsystemB BisB BcalledB BaB BrelationalB BdatabaseB BmanagementB Bsystem,B BasB BallB BtablesB BareB BrelatedB BforB BeasyB BrecordB BretrievalB BandB BinformationB Bprocessing.B BToB BcreateB BtheB BdifferentB BbutB BrelatedB BcustomerB BtablesB BinB BtheB Bcustomer_detailsB Bdatabase,B BweB BapplyB BtheB BfollowingB BDDLB Bsyntax:

CREATEB BTABLEB Bmy_table

(

table_column-1B Bdata_type,

table_column-2B Bdata_type,

table_column-3B Bdata_type,

table_column-nB Bdata_type

)

CREATEB BTABLEB Bcustomer_accounts

(

acct_noB BINTEGER,B BPRIMARYB BKEY,

acct_balB BDOUBLE,

acct_typeB BINTEGER,

acct_opening_dateB BDATE

)

¹

TheB BattributeB BPRIMARYB BKEYB BensuresB BtheB BcolumnB BnamedB B‘acct_no’B BhasB BuniqueB BvaluesB Bthroughout,B BwithB BnoB BnullB Bvalues.B BEveryB BtableB BshouldB BhaveB BaB BprimaryB BkeyB BcolumnB BtoB BuniquelyB BidentifyB BeachB BrecordB BofB BtheB Btable.B BOtherB BcolumnB BattributesB BareB B‘NOTB BNULL’B BwhichB BensuresB BthatB BaB BnullB BvalueB BisB BnotB BacceptedB BintoB BtheB Bcolumn,B BandB B‘FOREIGNB BKEY’B BwhichB BensuresB BthatB BaB BrelatedB BrecordB BinB BanotherB BtableB BisB BnotB BmistakenlyB BorB BunintentionallyB Bdeleted.B BAB BcolumnB BwithB BaB B‘FOREIGNB BKEY’B BattributeB BisB BaB BcopyB BofB BaB BprimaryB BkeyB BcolumnB BinB BanotherB BrelatedB Btable.B BForB Bexample,B BweB BcanB BcreateB BanotherB BtableB B‘customer_personal_info’B BinB BourB B‘customer_details’B BdatabaseB BlikeB Bbelow:

CREATEB BTABLEB Bcustomer_personal_info

(

cust_idB BINTEGERB BPRIMARYB BKEY,

first_nameB BVARCHAR(100)B BNOTB BNULL,B B

second_nameB BVARCHAR(100),

lastnameB BVARCHAR(100)B BNOTB BNULL,

sexB BVARCHAR(5),

date_of_birthB BDATE,

addressB BVARCHAR(200)

)

²

TheB BnewlyB BcreatedB B‘customer_personal_info’B BtableB BhasB BaB BprimaryB BkeyB BcolumnB BnamedB B‘cust_id’.B BTheB B‘customer_accounts’B BtableB BneedsB BtoB BincludeB BaB BcolumnB BnamedB B‘cust_id’B BinB BitsB BfieldB BtoB BlinkB BtoB BtheB B‘customer_personal_info’B BtableB BinB BorderB BtoB BaccessB BtheB BtableB BforB BmoreB BinformationB BaboutB BtheB BcustomerB BwithB BaB BgivenB BaccountB Bnumber.B B

Therefore,B BtheB BbestB BwayB BtoB BensureB BdataB BintegrityB BbetweenB BtheB BtwoB Btables,B BsoB BthatB BanB BactiveB BrecordB BinB BaB BtableB BisB BneverB BdeletedB BisB BtoB BinsertB BaB BkeyB BnamedB B‘cust_id’B BinB BtheB B‘customer_accounts’B BtableB BasB BaB BforeignB Bkey.B BThisB BensuresB BthatB BaB BrelatedB BrecordB BinB B‘customer_personal_info’B BtoB BanotherB BinB B‘customer_accounts’B BtableB BisB BneverB BaccidentallyB Bdeleted.B BWeB BwillB BdiscussB BhowB BtoB BgoB BaboutB BthisB BinB BtheB BnextB Bsection.

AlterB BDDLB BforB BForeignB BKeyB BAddition

SinceB B‘customer_accounts’B BtableB BisB BalreadyB Bcreated,B BweB BneedB BtoB BalterB BtheB BtableB BtoB BaccommodateB BtheB BnewB BforeignB Bkey.B BToB BachieveB Bthis,B BweB BuseB BtheB BSQLB BDataB BDefinitionB BLanguageB BsyntaxB BdescribedB Bbelow:

ALTERB BTABLEB Bmytable

ADDB BFOREIGNB BKEYB B(targeted_column)

REFERENCESB Brelated_table(related_column)

Now,B BtoB BaddB BtheB BforeignB BkeyB BtoB BtheB BtheB B‘customer_accounts’B BtableB BandB BmakeB BitB BreferenceB BtheB BkeyB BcolumnB B‘cust_id’B BofB BtableB B‘customer_personal_info’,B BweB BuseB BtheB BfollowingB BSQLB Bstatements:

ALTERB BTABLEB Bcustomer_accounts

ADDB BFOREIGNB BKEYB B(cust_id)

REFERENCESB Bcustomer_personal_info(cust_id)

ForeignB BKeyB BDDLB BinB BTables

InB BsituationsB BwhereB BweB BneedB BtoB BcreateB BforeignB BkeysB BasB BweB BcreateB BnewB Btables,B BweB BmakeB BuseB BofB BtheB BfollowingB BDDLB Bsyntax:

CREATEB BTABLEB Bmy_table

(

Column-1B Bdata_typeB BFOREIGNB BKEY,B BREFERENCESB B(relatedB Bcolumn)

)

UniqueB BConstraintB BDDLB BinB BTables

TheB BuniqueB BconstraintB BcanB BbeB BplacedB BonB BaB BtableB BcolumnB BtoB BensureB BthatB BtheB BvaluesB BstoredB BinB BtheB BcolumnB BareB Bunique,B BjustB BlikeB BtheB BprimaryB BkeyB Bcolumn.B BTheB BonlyB BdifferenceB BisB BthatB ByouB BcanB BonlyB BhaveB BoneB BprimaryB BkeyB BcolumnB BinB BaB BtableB BbutB BasB BmanyB BuniqueB BcolumnsB BasB ByouB Blike.B BTheB BDDLB BsyntaxB BforB BtheB BcreationB BofB BaB BuniqueB BtableB BcolumnB BorB BfieldB BisB BasB BdescribedB Bbelow:

CREATEB BTABLEB Bmy_table

(

Column-1B Bdata_typeB BUNIQUE

)

DeleteB BandB BDropB BDDLB BinB BTables

DROPB BTABLEB Bmy_table

ItB BmustB BbeB BnotedB BthatB BthisB BactionB BisB BirreversibleB BandB BplentyB BofB BcareB BmustB BbeB BtakenB BbeforeB BdoingB Bthis.B BAlso,B BaB BdatabaseB BcanB BbeB BdeletedB BwithB BtheB BfollowingB BDDLB Bsyntax:

DROPB BDATABASEB Bmy_database

ThisB BactionB BisB BalsoB Birreversible.B BAtB BtheB BatomicB Blevel,B ByouB BmayB BdecideB BtoB BdeleteB BaB BcolumnB BfromB BaB BdataB Btable.B BToB BachieveB