structured query language...structured query language (sql) 3.2 create tables using the designer...
TRANSCRIPT
![Page 1: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/1.jpg)
https://www.halvorsen.blog
AvailableOnline:https://www.halvorsen.blog
StructuredQueryLanguageHans-PetterHalvorsen
![Page 2: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/2.jpg)
StructuredQueryLanguage
Hans-PetterHalvorsen
Copyright©2017
https://www.halvorsen.blog
![Page 3: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/3.jpg)
3
TableofContents1 IntroductiontoSQL...........................................................................................................6
1.1 DataDefinitionLanguage(DDL)..................................................................................8
1.2 DataManipulationLanguage(DML)...........................................................................8
2 IntroductiontoSQLServer................................................................................................9
2.1 SQLServerManagementStudio...............................................................................10
2.1.1 CreateanewDatabase......................................................................................11
2.1.2 Queries..............................................................................................................12
3 CREATETABLE.................................................................................................................13
3.1 DatabaseModelling..................................................................................................15
3.2 CreateTablesusingtheDesignerTools....................................................................17
3.3 SQLConstraints.........................................................................................................17
3.3.1 PRIMARYKEY.....................................................................................................18
3.3.2 FOREIGNKEY.....................................................................................................19
3.3.3 NOTNULL/RequiredColumns.........................................................................22
3.3.4 UNIQUE.............................................................................................................23
3.3.5 CHECK................................................................................................................25
3.3.6 DEFAULT............................................................................................................27
3.3.7 AUTOINCREMENTorIDENTITY.........................................................................28
3.4 ALTERTABLE.............................................................................................................29
4 INSERTINTO....................................................................................................................31
5 UPDATE...........................................................................................................................33
![Page 4: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/4.jpg)
4 TableofContents
StructuredQueryLanguage(SQL)
6 DELETE.............................................................................................................................35
7 SELECT.............................................................................................................................37
7.1 TheORDERBYKeyword............................................................................................39
7.2 SELECTDISTINCT.......................................................................................................40
7.3 TheWHEREClause....................................................................................................40
7.3.1 Operators..........................................................................................................41
7.3.2 LIKEOperator....................................................................................................41
7.3.3 INOperator........................................................................................................42
7.3.4 BETWEENOperator...........................................................................................42
7.4 Wildcards..................................................................................................................42
7.5 AND&OROperators................................................................................................43
7.6 SELECTTOPClause....................................................................................................44
7.7 Alias..........................................................................................................................45
7.8 Joins..........................................................................................................................45
7.8.1 DifferentSQLJOINs...........................................................................................46
8 SQLScripts.......................................................................................................................48
8.1 UsingComments.......................................................................................................48
8.1.1 Single-linecomment..........................................................................................48
8.1.2 Multiple-linecomment......................................................................................48
8.2 Variables...................................................................................................................49
8.3 Built-inGlobalVariables...........................................................................................50
8.3.1 @@IDENTITY.....................................................................................................50
8.4 FlowControl.............................................................................................................51
8.4.1 IF–ELSE.............................................................................................................51
8.4.2 WHILE................................................................................................................52
8.4.3 CASE...................................................................................................................53
![Page 5: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/5.jpg)
5 TableofContents
StructuredQueryLanguage(SQL)
8.4.4 CURSOR.............................................................................................................54
9 Views...............................................................................................................................56
9.1 UsingtheGraphicalDesigner...................................................................................57
10 StoredProcedures........................................................................................................61
10.1 NOCOUNTON/NOCOUNTOFF..............................................................................64
11 Functions......................................................................................................................66
11.1 Built-inFunctions..................................................................................................66
11.1.1 StringFunctions.............................................................................................66
11.1.2 DateandTimeFunctions...............................................................................67
11.1.3 MathematicsandStatisticsFunctions...........................................................67
11.1.4 AVG()..............................................................................................................68
11.1.5 COUNT().........................................................................................................68
11.1.6 TheGROUPBYStatement..............................................................................69
11.1.7 TheHAVINGClause........................................................................................70
11.2 User-definedFunctions.........................................................................................71
12 Triggers.........................................................................................................................72
13 CommunicationfromotherApplications.....................................................................75
13.1 ODBC.....................................................................................................................75
13.2 MicrosoftExcel......................................................................................................76
14 References....................................................................................................................78
![Page 6: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/6.jpg)
6
1 IntroductiontoSQLSQL(StructuredQueryLanguage)isadatabasecomputerlanguagedesignedformanagingdatainrelationaldatabasemanagementsystems(RDBMS).
SQL,isastandardizedcomputerlanguagethatwasoriginallydevelopedbyIBMforquerying,alteringanddefiningrelationaldatabases,usingdeclarativestatements.
SQLispronounced/ˌɛskjuːˈɛl/ (letterbyletter) or/ˈsiːkwəl/ (asaword).
WhatcanSQLdo?
• SQLcanexecutequeriesagainstadatabase• SQLcanretrievedatafromadatabase• SQLcaninsertrecordsinadatabase• SQLcanupdaterecordsinadatabase• SQLcandeleterecordsfromadatabase
![Page 7: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/7.jpg)
7 IntroductiontoSQL
StructuredQueryLanguage(SQL)
• SQLcancreatenewdatabases• SQLcancreatenewtablesinadatabase• SQLcancreatestoredproceduresinadatabase• SQLcancreateviewsinadatabase• SQLcansetpermissionsontables,procedures,andviews
EvenifSQLisastandard,manyofthedatabasesystemsthatexisttodayimplementtheirownversionoftheSQLlanguage.Inthisdocument,wewillusetheMicrosoftSQLServerasanexample.
Therearelotsofdifferentdatabasesystems,orDBMS–DatabaseManagementSystems,suchas:
• MicrosoftSQLServero Enterprise,Developerversions,etc.o Expressversionisfreeofcharge
• Oracle• MySQL(Oracle,previouslySunMicrosystems)-MySQLcanbeusedfreeofcharge
(opensourcelicense),WebsitesthatuseMySQL:YouTube,Wikipedia,Facebook• MicrosoftAccess• IBMDB2• Sybase• …lotsofothersystems
InthisTutorial,wewillfocusonMicrosoftSQLServer.SQLServerusesT-SQL(Transact-SQL).T-SQLisMicrosoft'sproprietaryextensiontoSQL.T-SQLisverysimilartostandardSQL,butinadditionitsupportssomeextrafunctionality,built-infunctions,etc.
![Page 8: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/8.jpg)
8 IntroductiontoSQL
StructuredQueryLanguage(SQL)
OtherusefulTutorialsaboutdatabases:
• IntroductiontoDatabaseSystems• DatabaseCommunicationinLabVIEW
TheseTutorialsarelocatedat:https://www.halvorsen.blog
1.1 DataDefinitionLanguage(DDL)TheDataDefinitionLanguage(DDL)managestableandindexstructure.ThemostbasicitemsofDDLaretheCREATE,ALTER,RENAMEandDROPstatements:
• CREATEcreatesanobject(atable,forexample)inthedatabase. • DROPdeletesanobjectinthedatabase,usuallyirretrievably. • ALTERmodifiesthestructureanexistingobjectinvariousways—forexample,adding
acolumntoanexistingtable.
1.2 DataManipulationLanguage(DML)TheDataManipulationLanguage(DML)isthesubsetofSQLusedtoadd,updateanddeletedata.
TheacronymCRUDreferstoallofthemajorfunctionsthatneedtobeimplementedinarelationaldatabaseapplicationtoconsideritcomplete.EachletterintheacronymcanbemappedtoastandardSQLstatement:
Operation SQL DescriptionCreate INSERTINTO insertsnewdataintoa
databaseRead(Retrieve) SELECT extractsdatafromadatabaseUpdate UPDATE updatesdatainadatabaseDelete(Destroy) DELETE deletesdatafromadatabase
![Page 9: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/9.jpg)
9
2 IntroductiontoSQLServerMicrosoftisthevendorofSQLServer.
WehavedifferenteditionsofSQLServer,whereSQLServerExpressisfreetodownloadanduse.
SQLServerusesT-SQL(Transact-SQL).T-SQLisMicrosoft'sproprietaryextensiontoSQL.T-SQLisverysimilartostandardSQL,butinadditionitsupportssomeextrafunctionality,built-infunctions,etc.T-SQLexpandsontheSQLstandardtoincludeproceduralprogramming,localvariables,varioussupportfunctionsforstringprocessing,dateprocessing,mathematics,etc.
SQLServerconsistsofaDatabaseEngineandaManagementStudio(andlotsofotherstuffwhichwewillnotmentionhere).TheDatabaseenginehasnographicalinterface-itisjustaservicerunninginthebackgroundofyourcomputer(preferableontheserver).TheManagementStudioisgraphicaltoolforconfiguringandviewingtheinformationinthedatabase.Itcanbeinstalledontheserverorontheclient(orboth).
![Page 10: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/10.jpg)
10 IntroductiontoSQLServer
StructuredQueryLanguage(SQL)
2.1 SQLServerManagementStudioSQLServerManagementStudioisaGUItoolincludedwithSQLServerforconfiguring,managing,andadministeringallcomponentswithinMicrosoftSQLServer.Thetoolincludesbothscripteditorsandgraphicaltoolsthatworkwithobjectsandfeaturesoftheserver.Asmentionedearlier,versionofSQLServerManagementStudioisalsoavailableforSQLServerExpressEdition,forwhichitisknownasSQLServerManagementStudioExpress.
AcentralfeatureofSQLServerManagementStudioistheObjectExplorer,whichallowstheusertobrowse,select,andactuponanyoftheobjectswithintheserver.Itcanbeusedtovisuallyobserveandanalyzequeryplansandoptimizethedatabaseperformance,amongothers.SQLServerManagementStudiocanalsobeusedtocreateanewdatabase,alteranyexistingdatabaseschemabyaddingormodifyingtablesandindexes,oranalyzeperformance.ItincludesthequerywindowswhichprovideaGUIbasedinterfacetowriteandexecutequeries.
WhencreatingSQLcommandsandqueries,the“QueryEditor”(select“NewQuery”fromtheToolbar)isused(showninthefigureabove).
WithSQLandthe“QueryEditor”wecandoalmosteverythingwithcode,butsometimesitisalsoagoodideatousethedifferentDesignertoolsinSQLtohelpusdotheworkwithoutcoding(somuch).
![Page 11: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/11.jpg)
11 IntroductiontoSQLServer
StructuredQueryLanguage(SQL)
2.1.1 CreateanewDatabase
ItisquitesimpletocreateanewdatabaseinMicrosoftSQLServer.Justright-clickonthe“Databases”nodeandselect“NewDatabase…”
Therearelotsofsettingsyoumaysetregardingyourdatabase,buttheonlyinformationyoumustfillinisthenameofyourdatabase:
![Page 12: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/12.jpg)
12 IntroductiontoSQLServer
StructuredQueryLanguage(SQL)
YoumayalsousetheSQLlanguagetocreateanewdatabase,butsometimesitiseasiertojustusethebuilt-infeaturesintheManagementStudio.
2.1.2 Queries
InordertomakeanewSQLquery,selectthe“NewQuery”buttonfromtheToolbar.
HerewecanwriteanykindofqueriesthatissupportedbytheSQLlanguage.
![Page 13: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/13.jpg)
13
3 CREATETABLEBeforeyoustartimplementingyourtablesinthedatabase,youshouldalwaysspendsometimedesignyourtablesproperlyusingadesigntoollike,e.g.,ERwin,ToadDataModeler,PowerDesigner,Visio,etc.ThisiscalledDatabaseModeling.
TheCREATETABLEstatementisusedtocreateatableinadatabase.
Syntax:
CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... )
Thedatatypespecifieswhattypeofdatathecolumncanhold.
![Page 14: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/14.jpg)
14 CREATETABLE
StructuredQueryLanguage(SQL)
Youhavespecialdatatypesfornumbers,textdates,etc.
Examples:
• Numbers:int,float• Text/Stings:varchar(X)–whereXisthelengthofthestring• Dates:datetime• etc.
Example:
Wewanttocreateatablecalled“CUSTOMER”whichhasthefollowingcolumnsanddatatypes:
CREATE TABLE CUSTOMER ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO
Bestpractice:
Whencreatingtablesyoushouldconsiderfollowingtheseguidelines:
• Tables:Useuppercaseandsingularformintablenames–notplural,e.g.,“STUDENT”(notstudents)
• Columns:UsePascalnotation,e.g.,“StudentId”• PrimaryKey:
o Ifthetablenameis“COURSE”,namethePrimaryKeycolumn“CourseId”,etc.
![Page 15: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/15.jpg)
15 CREATETABLE
StructuredQueryLanguage(SQL)
o “Always”useIntegerandIdentity(1,1)forPrimaryKeys.UseUNIQUEconstraintforothercolumnsthatneedstobeunique,e.g.RoomNumber
• SpecifyRequiredColumns(NOTNULL)–i.e.,whichcolumnsthatneedtohavedataornot
• Standardizeonfew/theseDataTypes:int,float,varchar(x),datetime,bit• UseEnglishfortableandcolumnnames• Avoidabbreviations!(UseRoomNumber–notRoomNo,RoomNr,...)
3.1 DatabaseModellingAsmentioninthebeginningofthechapter,youshouldalwaysstartwithdatabasemodellingbeforeyoustartimplementingthetablesinadatabasesystem.
BelowweseeadatabasemodelincreatedwithERwin.
Withthistoolwecantransferthedatabasemodelastablesintodifferentdatabasesystems,suchase.g.,SQLServer.CAERwinDataModelerCommunityEditionisfreewitha25objectslimit.IthassupportforOracle,SQLServer,MySQL,ODBCandSybase.
![Page 16: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/16.jpg)
16 CREATETABLE
StructuredQueryLanguage(SQL)
BelowweseethesametablesinsidethedesigntoolinSQLServer.
![Page 17: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/17.jpg)
17 CREATETABLE
StructuredQueryLanguage(SQL)
3.2 CreateTablesusingtheDesignerToolsEvenifyoucando“everything”usingtheSQLlanguage,itissometimeseasiertodoitinthedesignertoolsintheManagementStudioinSQLServer.
Insteadofcreatingascriptyoumayaswelleasilyusethedesignerforcreatingtables.
Step1:Select“NewTable…”:
Step2:Next,thetabledesignerpopsupwhereyoucanaddcolumns,datatypes,etc.
InthisdesignerwemayalsospecifyColumnNames,DataTypes,etc.
Step3:SavethetablebyclickingtheSavebutton.
3.3 SQLConstraintsConstraintsareusedtolimitthetypeofdatathatcangointoatable.
![Page 18: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/18.jpg)
18 CREATETABLE
StructuredQueryLanguage(SQL)
Constraintscanbespecifiedwhenatableiscreated(withtheCREATETABLEstatement)orafterthetableiscreated(withtheALTERTABLEstatement).
Herearethemostimportantconstraints:
• PRIMARYKEY• NOTNULL• UNIQUE• FOREIGNKEY• CHECK• DEFAULT• IDENTITY
Inthesectionsbelowwewillexplainsomeoftheseindetail.
3.3.1 PRIMARYKEY
ThePRIMARYKEYconstraintuniquelyidentifieseachrecordinadatabasetable.
Primarykeysmustcontainuniquevalues.Itisnormaltojustuserunningnumbers,like1,2,3,4,5,…asvaluesinPrimaryKeycolumn.ItisagoodideatoletthesystemhandlethisforyoubyspecifyingthatthePrimaryKeyshouldbesettoidentity(1,1).IDENTITY(1,1)meansthefirstvaluewillbe1andthenitwillincrementby1.
Eachtableshouldhaveaprimarykey,andeachtablecanhaveonlyONEprimarykey.
IfwetakeacloserlookattheCUSTOMERtablecreatedearlier:
CREATE TABLE [CUSTOMER] ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO
Asyouseeweusethe“PrimaryKey”keywordtospecifythatacolumnshouldbethePrimaryKey.
![Page 19: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/19.jpg)
19 CREATETABLE
StructuredQueryLanguage(SQL)
SettingPrimaryKeysintheDesignerTools:
IfyouusetheDesignertoolsinSQLServer,youcaneasilysettheprimaryKeyinatablejustbyright-clickandselect“SetprimaryKey”.
TheprimaryKeycolumnwillthenhaveasmallkey infronttoillustratethatthiscolumnisaPrimaryKey.
3.3.2 FOREIGNKEY
AFOREIGNKEYinonetablepointstoaPRIMARYKEYinanothertable.
Example:
WewillcreateaCREATETABLEscriptforthesetables:
![Page 20: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/20.jpg)
20 CREATETABLE
StructuredQueryLanguage(SQL)
SCHOOL:
CREATE TABLE SCHOOL ( SchoolId int IDENTITY(1,1) PRIMARY KEY, SchoolName varchar(50) NOT NULL UNIQUE, Description varchar(1000) NULL, Address varchar(50) NULL, Phone varchar(50) NULL, PostCode varchar(50) NULL, PostAddress varchar(50) NULL, ) GO
CLASS:
CREATE TABLE CLASS ( ClassId int IDENTITY(1,1) PRIMARY KEY, SchoolId int NOT NULL FOREIGN KEY REFERENCES SCHOOL (SchoolId), ClassName varchar(50) NOT NULL UNIQUE, Description varchar(1000) NULL, ) GO
TheFOREIGNKEYconstraintisusedtopreventactionsthatwoulddestroylinksbetweentables.
TheFOREIGNKEYconstraintalsopreventsthatinvaliddatafrombeinginsertedintotheforeignkeycolumn,becauseithastobeoneofthevaluescontainedinthetableitpointsto.
SettingForeignKeysintheDesignerTools:
Ifyouwanttousethedesigner,right-clickonthecolumnthatyouwanttobetheForeignKeyandselect“Relationships…”:
![Page 21: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/21.jpg)
21 CREATETABLE
StructuredQueryLanguage(SQL)
Thefollowingwindowpopsup(ForeignKeyRelationships):
Clickonthe“Add”buttonandthenclickonthesmall“…”button.Thenthefollowingwindowpopsup(TablesandColumns):
![Page 22: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/22.jpg)
22 CREATETABLE
StructuredQueryLanguage(SQL)
HereyouspecifytheprimaryKeyColumninthePrimaryKeytableandtheForeignKeyColumnintheForeignKeytable.
3.3.3 NOTNULL/RequiredColumns
TheNOTNULLconstraintenforcesacolumntoNOTacceptNULLvalues.
TheNOTNULLconstraintenforcesafieldtoalwayscontainavalue.Thismeansthatyoucannotinsertanewrecord,orupdatearecordwithoutaddingavaluetothisfield.
IfwetakeacloserlookattheCUSTOMERtablecreatedearlier:
CREATE TABLE [CUSTOMER] ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO
![Page 23: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/23.jpg)
23 CREATETABLE
StructuredQueryLanguage(SQL)
Weseethat“CustomerNumber”,“LastName”and“FirstName”issetto“NOTNULL”,thismeansthesecolumnsneedstocontaindata.While“AreaCode”,“Address”and“Phone”maybeleftempty,i.e,theydon’tneedtobefilledout.
Note!AprimarykeycolumncannotcontainNULLvalues.
SettingNULL/NOTNULLintheDesignerTools:
IntheTableDesigneryoucaneasilysetwhichcolumnsthatshouldallowNULLornot:
3.3.4 UNIQUE
TheUNIQUEconstraintuniquelyidentifieseachrecordinadatabasetable.TheUNIQUEandPRIMARYKEYconstraintsbothprovideaguaranteeforuniquenessforacolumnorsetofcolumns.
APRIMARYKEYconstraintautomaticallyhasaUNIQUEconstraintdefinedonit.
Note!YoucanhavemanyUNIQUEconstraintspertable,butonlyonePRIMARYKEYconstraintpertable.
IfwetakeacloserlookattheCUSTOMERtablecreatedearlier:
CREATE TABLE [CUSTOMER] ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL,
![Page 24: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/24.jpg)
24 CREATETABLE
StructuredQueryLanguage(SQL)
Phone varchar(50) NULL, ) GO
Weseethatthe“CustomerNumber”issettoUNIQUE,meaningeachcustomermusthaveauniqueCustomerNumber.Example:
SettingUNIQUEintheDesignerTools:
Ifyouwanttousethedesigner,right-clickonthecolumnthatyouwanttobeUNIQUEandselect“Indexes/Keys…”:
Thenclick“Add”andthensetthe“IsUnique”propertyto“Yes”:
![Page 25: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/25.jpg)
25 CREATETABLE
StructuredQueryLanguage(SQL)
3.3.5 CHECK
TheCHECKconstraintisusedtolimitthevaluerangethatcanbeplacedinacolumn.
IfyoudefineaCHECKconstraintonasinglecolumnitallowsonlycertainvaluesforthiscolumn.
IfyoudefineaCHECKconstraintonatableitcanlimitthevaluesincertaincolumnsbasedonvaluesinothercolumnsintherow.
Example:
CREATE TABLE [CUSTOMER] ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE CHECK(CustomerNumber>0), LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO
Inthiscase,whenwetrytoinsertaCustomerNumberlessthanzerowewillgetanerrormessage.
SettingCHECKconstraintsintheDesignerTools:
Ifyouwanttousethedesigner,right-clickonthecolumnwhereyouwanttosettheconstraintsandselect“CheckConstraints…”:
![Page 26: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/26.jpg)
26 CREATETABLE
StructuredQueryLanguage(SQL)
Thenclick“Add”andthenclick“…”inordertoopentheExpressionwindow:
IntheExpressionwindowyoucantypeintheexpressionyouwanttouse:
![Page 27: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/27.jpg)
27 CREATETABLE
StructuredQueryLanguage(SQL)
3.3.6 DEFAULT
TheDEFAULTconstraintisusedtoinsertadefaultvalueintoacolumn.
Thedefaultvaluewillbeaddedtoallnewrecords,ifnoothervalueisspecified.
Example:
CREATE TABLE [CUSTOMER] ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, Country varchar(20) DEFAULT 'Norway',
AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO
SettingDEFAULTvaluesintheDesignerTools:
Selectthecolumnandgointothe“ColumnProperties”:
![Page 28: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/28.jpg)
28 CREATETABLE
StructuredQueryLanguage(SQL)
3.3.7 AUTOINCREMENTorIDENTITY
Veryoftenwewouldlikethevalueoftheprimarykeyfieldtobecreatedautomaticallyeverytimeanewrecordisinserted.
Example:
CREATE TABLE CUSTOMER ( CustomerId int IDENTITY(1,1) PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO
Asshownbelow,weusetheIDENTITY()forthis.IDENTITY(1,1)meansthefirstvaluewillbe1andthenitwillincrementby1.
Settingidentity(1,1)intheDesignerTools:
WecanusethedesignertoolstospecifythataPrimaryKeyshouldbeanidentitycolumnthatisautomaticallygeneratedbythesystemwhenweinsertdataintothetable.
ClickonthecolumninthedesignerandgointotheColumnPropertieswindow:
![Page 29: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/29.jpg)
29 CREATETABLE
StructuredQueryLanguage(SQL)
3.4 ALTERTABLETheALTERTABLEstatementisusedtoadd,delete,ormodifycolumnsinanexistingtable.
Toaddacolumninatable,usethefollowingsyntax:
ALTER TABLE table_name ADD column_name datatype
Todeleteacolumninatable,usethefollowingsyntax(noticethatsomedatabasesystemsdon'tallowdeletingacolumn):
ALTER TABLE table_name DROP COLUMN column_name
Tochangethedatatypeofacolumninatable,usethefollowingsyntax:
ALTER TABLE table_name ALTER COLUMN column_name datatype
IfweuseCREATETABLEandthetablealreadyexistsinthetablewewillgetanerrormessage,soifwecombineCREATETABLEandALTERTABLEwecancreaterobustdatabasescriptsthatgivesnoerrors,astheexampleshownbelow:
if not exists (select * from dbo.sysobjects where id = object_id(N'[CUSTOMER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE CUSTOMER ( CustomerId int PRIMARY KEY, CustomerNumber int NOT NULL UNIQUE, LastName varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, AreaCode int NULL, Address varchar(50) NULL, Phone varchar(50) NULL, ) GO if exists(select * from dbo.syscolumns where id = object_id(N'[CUSTOMER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and name = 'CustomerId') ALTER TABLE CUSTOMER ALTER COLUMN CustomerId int Else ALTER TABLE CUSTOMER ADD CustomerId int GO if exists(select * from dbo.syscolumns where id = object_id(N'[CUSTOMER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and name = 'CustomerNumber') ALTER TABLE CUSTOMER ALTER COLUMN CustomerNumber int
![Page 30: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/30.jpg)
30 CREATETABLE
StructuredQueryLanguage(SQL)
Else ALTER TABLE CUSTOMER ADD CustomerNumber int GO ...
![Page 31: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/31.jpg)
31
4 INSERTINTOTheINSERTINTOstatementisusedtoinsertanewrowinatable.
ItispossibletowritetheINSERTINTOstatementintwoforms.
Thefirstformdoesn'tspecifythecolumnnameswherethedatawillbeinserted,onlytheirvalues:
INSERT INTO table_name VALUES (value1, value2, value3,...)
Example:
INSERT INTO CUSTOMER VALUES ('1000', 'Smith', 'John', 12, 'California', '11111111')
Thesecondformspecifiesboththecolumnnamesandthevaluestobeinserted:
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
Thisformisrecommended!
Example:
INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName, AreaCode, Address, Phone) VALUES ('1000', 'Smith', 'John', 12, 'California', '11111111')
InsertDataOnlyinSpecifiedColumns:
Itisalsopossibletoonlyadddatainspecificcolumns.
Example:
INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName) VALUES ('1000', 'Smith', 'John')
Note!YouneedatleasttoincludeallcolumnsthatcannotbeNULL.
WerememberthetabledefinitionfortheCUSTOMERtable:
![Page 32: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/32.jpg)
32 INSERTINTO
StructuredQueryLanguage(SQL)
i.e.,weneedtoincludeatleast“CustomerNumber”,“LastName”and“FirstName”.“CustomerId”issetto“identity(1,1)”andthereforevaluesforthiscolumnaregeneratedbythesystem.
InsertDataintheDesignerTools:
Whenyouhavecreatedthetables,youcaneasilyinsertdataintothemusingthedesignertools.Right-clickonthespecifictableandselect“EditTop200Rows”:
Thenyoucanenterdatainatableformat,similarto,e.g.,MSExcel:
![Page 33: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/33.jpg)
33
5 UPDATETheUPDATEstatementisusedtoupdateexistingrecordsinatable.
Thesyntaxisasfollows:
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
Note!NoticetheWHEREclauseintheUPDATEsyntax.TheWHEREclausespecifieswhichrecordorrecordsthatshouldbeupdated.IfyouomittheWHEREclause,allrecordswillbeupdated!
Example:
update CUSTOMER set AreaCode=46 where CustomerId=2
Beforeupdate:
Afterupdate:
Ifyoudon’tincludetheWHEREclausetheresultbecomes:
→SomakesuretoincludetheWHEREclausewhenusingtheUPDATEcommand!
![Page 34: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/34.jpg)
34 UPDATE
StructuredQueryLanguage(SQL)
UpdateDataintheDesignerTools:
Thesamewayyouinsertdatayoucanalsoupdatethedata.Right-clickonthespecifictableandselect“EditTop200Rows”:
Thenyoucanchangeyourdata:
![Page 35: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/35.jpg)
35
6 DELETETheDELETEstatementisusedtodeleterowsinatable.
Syntax:
DELETE FROM table_name WHERE some_column=some_value
Note!NoticetheWHEREclauseintheDELETEsyntax.TheWHEREclausespecifieswhichrecordorrecordsthatshouldbedeleted.IfyouomittheWHEREclause,allrecordswillbedeleted!
Example:
delete from CUSTOMER where CustomerId=2
Beforedelete:
Afterdelete:
DeleteAllRows:
Itispossibletodeleteallrowsinatablewithoutdeletingthetable.Thismeansthatthetablestructure,attributes,andindexeswillbeintact:
DELETE FROM table_name
Note!Makesuretodothisonlywhenyoureallymeanit!YoucannotUNDOthisstatement!
DeleteDataintheDesignerTools:
Youdeletedatainthedesignerbyright-clickontherowandselect“Delete”:
![Page 36: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/36.jpg)
36 DELETE
StructuredQueryLanguage(SQL)
![Page 37: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/37.jpg)
37
7 SELECTTheSELECTstatementisprobablythemostusedSQLcommand.TheSELECTstatementisusedforretrievingrowsfromthedatabaseandenablestheselectionofoneormanyrowsorcolumnsfromoneormanytablesinthedatabase.
WewillusetheCUSTOMERtableasanexample.
TheCUSTOMERtablehasthefollowingcolumns:
TheCUSTOMERtablecontainsthefollowingdata:
Example:
select * from CUSTOMER
ThissimpleexamplegetsallthedatainthetableCUSTOMER.Thesymbol“*”isusedwhenyouwanttogetallthecolumnsinthetable.
![Page 38: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/38.jpg)
38 SELECT
StructuredQueryLanguage(SQL)
Ifyouonlywantafewcolumns,youmayspecifythenamesofthecolumnsyouwanttoretrieve,example:
select CustomerId, LastName, FirstName from CUSTOMER
SointhesimplestformwecanusetheSELECTstatementasfollows:
select <column_names> from <table_names>
Ifwewantallcolumns,weusethesymbol“*”
Note!SQLisnotcasesensitive.SELECTisthesameasselect.
ThefullsyntaxoftheSELECTstatementiscomplex,butthemainclausescanbesummarizedas:
SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]
Itseemscomplex,butwewilltakethedifferentpartsstepbystepinthenextsections.
SelectDataintheDesignerTools:
Right-clickonatableandselect“SelectTop1000Rows”:
Thefollowingwillappear:
![Page 39: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/39.jpg)
39 SELECT
StructuredQueryLanguage(SQL)
ASelectqueryisautomaticallycreatedforyouwhichyoucaneditifyouwantto.
7.1 TheORDERBYKeywordIfyouwantthedatatoappearinaspecificorderyouneedtousethe“orderby”keyword.
Example:
select * from CUSTOMER order by LastName
Youmayalsosortbyseveralcolumns,e.g.likethis:
select * from CUSTOMER order by Address, LastName
Ifyouusethe“orderby”keyword,thedefaultorderisascending(“asc”).Ifyouwanttheordertobeopposite,i.e.,descending,thenyouneedtousethe“desc”keyword.
![Page 40: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/40.jpg)
40 SELECT
StructuredQueryLanguage(SQL)
select * from CUSTOMER order by LastName desc
7.2 SELECTDISTINCTInatable,someofthecolumnsmaycontainduplicatevalues.Thisisnotaproblem,however,sometimesyouwillwanttolistonlythedifferent(distinct)valuesinatable.
TheDISTINCTkeywordcanbeusedtoreturnonlydistinct(different)values.
Thesyntaxisasfollows:
select distinct <column_names> from <table_names>
Example:
select distinct FirstName from CUSTOMER
7.3 TheWHEREClauseTheWHEREclauseisusedtoextractonlythoserecordsthatfulfillaspecifiedcriterion.
Thesyntaxisasfollows:
select <column_names> from <table_name> where <column_name> operator value
Example:
select * from CUSTOMER where CustomerNumber='1001'
![Page 41: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/41.jpg)
41 SELECT
StructuredQueryLanguage(SQL)
Note!SQLusessinglequotesaroundtextvalues,asshownintheexampleabove.
7.3.1 Operators
WiththeWHEREclause,thefollowingoperatorscanbeused:
Operator Description= Equal<> Notequal > Greaterthan < Lessthan >= Greaterthanorequal <= Lessthanorequal BETWEEN Betweenaninclusiverange LIKE Searchforapattern IN Ifyouknowtheexactvalueyouwanttoreturnforatleastoneofthe
columns
Examples:
select * from CUSTOMER where AreaCode>30
7.3.2 LIKEOperator
TheLIKEoperatorisusedtosearchforaspecifiedpatterninacolumn.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
Example:
select * from CUSTOMER where LastName like 'J%'
Note!The"%"signcanbeusedtodefinewildcards(missinglettersinthepattern)bothbeforeandafterthepattern.
![Page 42: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/42.jpg)
42 SELECT
StructuredQueryLanguage(SQL)
select * from CUSTOMER where LastName like '%a%'
YoumayalsocombinewiththeNOTkeyword,example:
select * from CUSTOMER where LastName not like '%a%'
7.3.3 INOperator
TheINoperatorallowsyoutospecifymultiplevaluesinaWHEREclause.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
7.3.4 BETWEENOperator
TheBETWEENoperatorselectsarangeofdatabetweentwovalues.Thevaluescanbenumbers,text,ordates.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
7.4 WildcardsSQLwildcardscansubstituteforoneormorecharacterswhensearchingfordatainadatabase.
Note!SQLwildcardsmustbeusedwiththeSQLLIKEoperator.
WithSQL,thefollowingwildcardscanbeused:
![Page 43: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/43.jpg)
43 SELECT
StructuredQueryLanguage(SQL)
Wildcard Description% Asubstituteforzeroormorecharacters_ Asubstituteforexactlyonecharacter[charlist] Anysinglecharacterincharlist[^charlist]or[!charlist]
Anysinglecharacternotincharlist
Examples:
SELECT * FROM CUSTOMER WHERE LastName LIKE 'J_cks_n'
SELECT * FROM CUSTOMER WHERE CustomerNumber LIKE '[10]%'
7.5 AND&OROperatorsTheANDoperatordisplaysarecordifboththefirstconditionandthesecondconditionistrue.
TheORoperatordisplaysarecordifeitherthefirstconditionorthesecondconditionistrue.
Examples:
select * from CUSTOMER where LastName='Smith' and FirstName='John'
select * from CUSTOMER where LastName='Smith' or FirstName='John'
![Page 44: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/44.jpg)
44 SELECT
StructuredQueryLanguage(SQL)
CombiningAND&OR:
YoucanalsocombineANDandOR(useparenthesistoformcomplexexpressions).
Example:
select * from CUSTOMER where LastName='Smith' and (FirstName='John' or FirstName='Smith')
7.6 SELECTTOPClauseTheTOPclauseisusedtospecifythenumberofrecordstoreturn.
TheTOPclausecanbeveryusefulonlargetableswiththousandsofrecords.Returningalargenumberofrecordscanimpactonperformance.
Syntax:
SELECT TOP number|percent column_name(s) FROM table_name
Examples:
select TOP 1 * from CUSTOMER
Youcanalsospecifyinpercent:
select TOP 60 percent * from CUSTOMER
Thisisveryusefulforlargetableswiththousandsofrecords
![Page 45: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/45.jpg)
45 SELECT
StructuredQueryLanguage(SQL)
7.7 AliasYoucangiveatableoracolumnanothernamebyusinganalias.Thiscanbeagoodthingtodoifyouhaveverylongorcomplextablenamesorcolumnnames.
Analiasnamecouldbeanything,butusuallyitisshort.
SQLAliasSyntaxforTables:
SELECT column_name(s) FROM table_name AS alias_name
SQLAliasSyntaxforColumns:
SELECT column_name AS alias_name FROM table_name
7.8 JoinsSQLjoinsareusedtoquerydatafromtwoormoretables,basedonarelationshipbetweencertaincolumnsinthesetables.
![Page 46: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/46.jpg)
46 SELECT
StructuredQueryLanguage(SQL)
7.8.1 DifferentSQLJOINs
Beforewecontinuewithexamples,wewilllistthetypesofJOINyoucanuse,andthedifferencesbetweenthem.
• JOIN:Returnrowswhenthereisatleastonematchinbothtables• LEFTJOIN:Returnallrowsfromthelefttable,eveniftherearenomatchesinthe
righttable• RIGHTJOIN:Returnallrowsfromtherighttable,eveniftherearenomatchesinthe
lefttable• FULLJOIN:Returnrowswhenthereisamatchinoneofthetables
Example:
Given2tables:
• SCHOOL• CLASS
![Page 47: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/47.jpg)
47 SELECT
StructuredQueryLanguage(SQL)
Thediagramisshownbelow:
Wewanttogetthefollowinginformationusingaquery:
SchoolName ClassName
… …
… …
InordertogetinformationfrommorethanonetableweneedtousetheJOIN.TheJOINisusedtojointheprimarykeyinonetablewiththeforeignkeyinanothertable.
select SCHOOL.SchoolName, CLASS.ClassName from SCHOOL INNER JOIN CLASS ON SCHOOL.SchoolId = CLASS.SchoolId
![Page 48: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/48.jpg)
48
8 SQLScriptsASQLscriptisacollectionofSQLstatementsthatyoucanexecuteinoneoperation.YoucanuseanykindofSQLcommands,suchasinsert,select,delete,update,etc.Inadditionyoucandefineandusevariables,andyoumayalsouseprogramflowlikeIf-Else,etc.Youmayalsoaddcommentstomakethescripteasiertoreadandunderstand.
8.1 UsingCommentsUsingcommentsinyouSQLscriptisimportanttomakethescripteasiertoreadandunderstand.
InSQLwecanuse2differentkindsofcomments:
• Single-linecomment• Multiple-linecomment
8.1.1 Single-linecomment
Wecancommentonelineatthetimeusing“--”beforethetextyouwanttocommentout.
Syntax:
-- text_of_comment
8.1.2 Multiple-linecomment
Wecancommentseverallineusing“/*”inthestartofthecommentand“*/”intheendofthecomment.
Syntax:
/* text_of_comment text_of_comment */
![Page 49: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/49.jpg)
49 SQLScripts
StructuredQueryLanguage(SQL)
8.2 VariablesTheabilitytousingvariablesinSQLisapowerfulfeature.YouneedtousethekeywordDECLAREwhenyouwanttodefinethevariables.Localvariablesmusthavethethesymbol“@”asaprefix.Youalsoneedtospecifyadatatypeforyourvariable(int,varchar(x),etc.).
Syntaxfordeclaringvariables:
declare @local_variable data_type
Ifyouhavemorethanonevariableyouwanttodeclare:
declare @myvariable1 data_type, @myvariable2 data_type, …
Whenyouwanttoassignvaluestothevariable,youmustuseeitheraSEToraSELECTstatement.
Example:
declare @myvariable int set @myvariable=4
Ifyouwanttoseethevalueforavariable,youcane.g.,usethePRINTcommandlikethis:
declare @myvariable int set @myvariable=4 print @myvariable
ThefollowingwillbeshowninSQLServer:
AssigningvariableswithavaluefromaSELECTstatementisveryuseful.
![Page 50: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/50.jpg)
50 SQLScripts
StructuredQueryLanguage(SQL)
WeusetheCUSTOMERtableasanexample:
Youcanassignavaluetothevariablefromaselectstatementlikethis:
declare @mylastname varchar(50) select @mylastname=LastName from CUSTOMER where CustomerId=2 print @mylastname
YoucanalsouseavariableintheWHEREclauseLIKE,e.g.,this:
declare @find varchar(30) set @find = 'J%' select * from CUSTOMER where LastName LIKE @find
8.3 Built-inGlobalVariablesSQLhavelotsofbuilt-invariablesthatareveryusefultouseinqueriesandscripts.
8.3.1 @@IDENTITY
AfteranINSERT,SELECTINTO,orbulkcopystatementiscompleted,@@IDENTITYcontainsthelastidentityvaluethatisgeneratedbythestatement.Ifthestatementdidnotaffectanytableswithidentitycolumns,@@IDENTITYreturnsNULL.Ifmultiplerowsareinserted,generatingmultipleidentityvalues,@@IDENTITYreturnsthelastidentityvaluegenerated.
Example:
Giventotables;SCHOOLandCOURSE:
![Page 51: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/51.jpg)
51 SQLScripts
StructuredQueryLanguage(SQL)
SCHOOLtable: COURSEtable:
WewanttoinsertanewSchoolintotheSCHOOLtableandwewanttoinsert2newCoursesintheCOURSEtablethatbelongtotheSchoolweinsert.Tofindthe“SchoolId”wecanusethe@@IDENTITYvariable:
declare @SchoolId int -- Insert Data into SCHOOL table insert into SCHOOL(SchoolName) values ('MIT') select @SchoolId = @@IDENTITY -- Insert Courses for the specific School above in the COURSE table insert into COURSE(SchoolId,CourseName) values (@SchoolId, 'MIT-101') insert into COURSE(SchoolId,CourseName) values (@SchoolId, 'MIT-201')
Theresultbecomes:
SCHOOLtable: COURSEtable:
8.4 FlowControlAswithotherprogramminglanguagesyoucanusedifferentkindofflowcontrol,suchasIF-ELSE,WHILE,etc,whichisveryuseful.
8.4.1 IF–ELSE
TheIF-ELSEisveryuseful.Belowweseeanexample:
declare @customerNumber int
![Page 52: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/52.jpg)
52 SQLScripts
StructuredQueryLanguage(SQL)
select @customerNumber=CustomerNumber from CUSTOMER where CustomerId=2 if @customerNumber > 1000 print 'The Customer Number is larger than 1000' else print 'The Customer Number is not larger than 1000'
BEGIN…END:
IfmorethanonelineofcodeistobeexecutedwithinanIFsentenceyouneedtouseBEGIN…END.
Example:
select @customerNumber=CustomerNumber from CUSTOMER where CustomerId=2 if @customerNumber > 1000 begin print 'The Customer Number is larger than 1000' update CUSTOMER set AreaCode=46 where CustomerId=2 end else print 'The Customer Number is not larger than 1000'
8.4.2 WHILE
WecanalsouseWHILE,whichisknownfromotherprogramminglanguages.
Example:
WeareusingtheCUSTOMERtable:
andthefollowingquery:
while (select AreaCode from CUSTOMER where CustomerId=1) < 20 begin update CUSTOMER set AreaCode = AreaCode + 1
![Page 53: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/53.jpg)
53 SQLScripts
StructuredQueryLanguage(SQL)
end select * from CUSTOMER
AsyoucanseethecodeinsidetheWHILEloopisexecutedaslongas“AreaCode”forCustomerId=1islessthan20.Foreachiterationisthe“AreaCode”forthatcustomerincrementedwith1.
8.4.3 CASE
TheCASEstatementevaluatesalistofconditionsandreturnsoneofmultiplepossibleresultexpressions.
Example:
Wehavea“GRADE”tablethatcontainsthegradesforeachstudentindifferentcourses:
select GradeId, StudentId, CourseId, Grade from GRADE
Inthe“GRADE”tableisthegradesstoredasnumbers,butsincethestudentsgetgradeswiththelettersA..F(A=5,B=4,C=3,D=2,E=1,F=0),wewanttoconvertthevaluesinthetableintolettersusingaCASEstatement:
select GradeId, StudentId, CourseId, case Grade when 5 then 'A' when 4 then 'B' when 3 then 'C' when 2 then 'D' when 1 then 'E' when 0 then 'F'
else '-' end as Grade from
![Page 54: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/54.jpg)
54 SQLScripts
StructuredQueryLanguage(SQL)
GRADE
8.4.4 CURSOR
Inadvancesscripts,CURSORsmaybeveryuseful.ACURSORworkslikeanadvancedWHILEloopwhichweusetoiteratethroughtherecordsinoneormoretables.
CURSORSareusedmainlyinstoredprocedures,triggers,andSQLscripts.
Example:
WeusetheCUSTOMERtableasanexample:
WewillcreateaCURSORthatiteratethroughalltherecordsintheCUSTOMERtableandcheckifthePhonenumberconsistsof8digits,ifnotthescriptwillreplacetheinvalidPhonenumberwiththetext“Phonenumberisnotvalid”.
HereistheSQLScriptusingaCURSOR:
DECLARE @CustomerId int, @phone varchar(50) DECLARE db_cursor CURSOR FOR SELECT CustomerId from CUSTOMER OPEN db_cursor FETCH NEXT FROM db_cursor INTO @CustomerId WHILE @@FETCH_STATUS = 0 BEGIN select @phone=Phone from CUSTOMER where CustomerId=@CustomerId if LEN(@phone) < 8
![Page 55: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/55.jpg)
55 SQLScripts
StructuredQueryLanguage(SQL)
update CUSTOMER set Phone='Phone number is not valid' where CustomerId=@CustomerId FETCH NEXT FROM db_cursor INTO @CustomerId END CLOSE db_cursor DEALLOCATE db_cursor
TheCUSTOMERtablebecomes:
CreatingandusingaCURSORincludesthesesteps:
• DeclareSQLvariablestocontainthedatareturnedbythecursor.Declareonevariableforeachresultsetcolumn.
• AssociateaSQLcursorwithaSELECTstatementusingtheDECLARECURSORstatement.TheDECLARECURSORstatementalsodefinesthecharacteristicsofthecursor,suchasthecursornameandwhetherthecursorisread-onlyorforward-only.
• UsetheOPENstatementtoexecutetheSELECTstatementandpopulatethecursor.• UsetheFETCHINTOstatementtofetchindividualrowsandhavethedataforeach
columnmovedintoaspecifiedvariable.OtherSQLstatementscanthenreferencethosevariablestoaccessthefetcheddatavalues.
• Whenyouarefinishedwiththecursor,usetheCLOSEstatement.Closingacursorfreessomeresources,suchasthecursor'sresultsetanditslocksonthecurrentrow.TheDEALLOCATEstatementcompletelyfreesallresourcesallocatedtothecursor,includingthecursorname.
![Page 56: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/56.jpg)
56
9 ViewsViewsarevirtualtableforeasieraccesstodatastoredinmultipletables.
SyntaxforcreatingaView:
CREATE VIEW <ViewName> AS …
...butitmightbeeasiertodoitinthegraphicalviewdesignerthatarebuiltintoSQLManagementStudio.
SyntaxforusingaView:
select * from <MyView> where …
Asshownabove,weuseaVIEWjustlikeweuseanordinarytable.
![Page 57: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/57.jpg)
57 Views
StructuredQueryLanguage(SQL)
Example:
WeusetheSCHOOLandCLASStablesasanexampleforourView.WewanttocreateaViewthatlistsalltheexistingschoolsandthebelongingclasses.
WecreatetheVIEWusingtheCREATEVIEWcommand:
CREATE VIEW SchoolView AS SELECT SCHOOL.SchoolName, CLASS.ClassName FROM SCHOOL INNER JOIN CLASS ON SCHOOL.SchoolId = CLASS.SchoolId
Note!Inordertogetinformationfrommorethanonetable,weneedtolinkthetablestogetherusingaJOIN.
9.1 UsingtheGraphicalDesignerWecreatethesameViewusingthegraphicaldesignerinSQLServerManagementStudio:
![Page 58: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/58.jpg)
58 Views
StructuredQueryLanguage(SQL)
Step1:Right-clickontheViewnodeandselect“NewView…”:
Step2:Addnecessarytables:
![Page 59: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/59.jpg)
59 Views
StructuredQueryLanguage(SQL)
Step3:AddColumns,etc.
Step4:SavetheVIEW:
![Page 60: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/60.jpg)
60 Views
StructuredQueryLanguage(SQL)
Step5:UsetheVIEWinaquery:
select * from SchoolView
![Page 61: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/61.jpg)
61
10 StoredProceduresAStoredProcedureisaprecompiledcollectionofSQLstatements.Inastoredprocedureyoucanuseifsentence,declarevariables,etc.
SyntaxforcreatingaStoredProcedure:
CREATE PROCEDURE <ProcedureName> @<Parameter1> <datatype> … declare @myVariable <datatype> … Create your Code here
Note!Youneedtousethesymbol“@”beforevariablenames.
SyntaxforusingaStoredProcedure:
EXECUTE <ProcedureName(…)>
Example:
![Page 62: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/62.jpg)
62 StoredProcedures
StructuredQueryLanguage(SQL)
WeusetheSCHOOLandCLASStablesasanexampleforourStoredProcedure.WewanttocreateaStoredProcedurethatlistsalltheexistingschoolsandthebelongingclasses.
WecreatetheStoredProcedureasfollows:
CREATE PROCEDURE GetAllSchoolClasses AS select SCHOOL.SchoolName, CLASS.ClassName from SCHOOL inner join CLASS on SCHOOL.SchoolId = CLASS.SchoolId order by SchoolName, ClassName
WhenwehavecreatedtheStoredProcedurewecanrun(orexecute)theStoredprocedureusingtheexecutecommandlikethis:
execute GetAllSchoolClasses
WecanalsocreateaStoreProcedurewithinputparameters.
Example:
![Page 63: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/63.jpg)
63 StoredProcedures
StructuredQueryLanguage(SQL)
Weusethesametablesinthisexample(SCHOOLandCLASS)butnowwewanttolistallclassesforaspecificschool.
TheStoredProcedurebecomes:
CREATE PROCEDURE GetSpecificSchoolClasses @SchoolName varchar(50) AS select SCHOOL.SchoolName, CLASS.ClassName from SCHOOL inner join CLASS on SCHOOL.SchoolId = CLASS.SchoolId where SchoolName=@SchoolName order by ClassName
Werun(orexecute)theStoredProcedure:
execute GetSpecificSchoolClasses 'TUC'
or:
execute GetSpecificSchoolClasses 'NTNU'
WhenwetrytocreateaStoredProcedurethatalreadyexistswegetthefollowingerrormessage:
There is already an object named 'GetSpecificSchoolClasses' in the database.
Thenwefirstneedtodelete(orDROP)theoldStoredProcedurebeforewecanrecreateitagain.
WecandothismanuallyintheManagementStudioinSQLlikethis:
![Page 64: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/64.jpg)
64 StoredProcedures
StructuredQueryLanguage(SQL)
Abettersolutionistoaddcodeforthisinourscript,likethis:
IF EXISTS (SELECT name FROM sysobjects WHERE name = GetSpecificSchoolClasses ' AND type = 'P') DROP PROCEDURE GetSpecificSchoolClasses GO CREATE PROCEDURE GetSpecificSchoolClasses @SchoolName varchar(50) AS select SCHOOL.SchoolName, CLASS.ClassName from SCHOOL inner join CLASS on SCHOOL.SchoolId = CLASS.SchoolId where SchoolName=@SchoolName order by ClassName
SoweuseCREATEPROCEDUREtocreateaStoredProcedureandweuseDROPPROCEDUREtodeleteaStoredProcedure.
10.1 NOCOUNTON/NOCOUNTOFFInadvancedStoredProceduresandScript,performanceisveryimportant.UsingSETNOCOUNTONandSETNOCOUNTOFFmakestheStoredProcedurerunfaster.
SETNOCOUNTONstopsthemessagethatshowsthecountofthenumberofrowsaffectedbyaTransact-SQLstatementorstoredprocedurefrombeingreturnedaspartoftheresultset.
![Page 65: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/65.jpg)
65 StoredProcedures
StructuredQueryLanguage(SQL)
SETNOCOUNTONpreventsthesendingofDONE_IN_PROCmessagestotheclientforeachstatementinastoredprocedure.Forstoredproceduresthatcontainseveralstatementsthatdonotreturnmuchactualdata,orforproceduresthatcontainTransact-SQLloops,settingSETNOCOUNTtoONcanprovideasignificantperformanceboost,becausenetworktrafficisgreatlyreduced.
Example:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_LIMS_IMPORT_REAGENT' AND type = 'P') DROP PROCEDURE sp_LIMS_IMPORT_REAGENT GO CREATE PROCEDURE sp_LIMS_IMPORT_REAGENT @Name varchar(100), @LotNumber varchar(100), @ProductNumber varchar(100), @Manufacturer varchar(100) AS SET NOCOUNT ON if not exists (SELECT ReagentId FROM LIMS_REAGENTS WHERE [Name]=@Name) INSERT INTO LIMS_REAGENTS ([Name], ProductNumber, Manufacturer) VALUES (@Name, @ProductNumber, @Manufacturer) else UPDATE LIMS_REAGENTS SET [Name] = @Name, ProductNumber = @ProductNumber, Manufacturer = @Manufacturer, WHERE [Name] = @Name SET NOCOUNT OFF GO
ThisStoredProcedureupdatesatableinthedatabaseandinthiscaseyoudon’tnormallyneedfeedback,spsettingSETNOCOUNTONatthetopinthestoredprocedureisagoodidea.itisalsogoodpracticetoSETNOCOUNTOFFatthebottomofthestoredprocedure.
![Page 66: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/66.jpg)
66
11 FunctionsWithSQLandSQLServeryoucanuselotsofbuilt-infunctionsoryoumaycreateyourownfunctions.Herewewilllearntousesomeofthemostusedbuilt-infunctionsandinadditionwewillcreateourownfunction.
11.1 Built-inFunctionsSQLhasmanybuilt-infunctionsforperformingcalculationsondata.
Wehave2categoriesoffunctions,namelyaggregatefunctionsandscalarfunctions.Aggregatefunctionsreturnasinglevalue,calculatedfromvaluesinacolumn,whilescalarfunctionsreturnasinglevalue,basedontheinputvalue.
Aggregatefunctions-examples:
• AVG()-Returnstheaveragevalue• STDEV()-Returnsthestandarddeviationvalue• COUNT()-Returnsthenumberofrows• MAX()-Returnsthelargestvalue• MIN()-Returnsthesmallestvalue• SUM()-Returnsthesum• etc.
Scalarfunctions-examples:
• UPPER()-Convertsafieldtouppercase• LOWER()-Convertsafieldtolowercase• LEN()-Returnsthelengthofatextfield• ROUND()-Roundsanumericfieldtothenumberofdecimalsspecified• GETDATE()-Returnsthecurrentsystemdateandtime• etc.
11.1.1 StringFunctions
HerearesomeusefulfunctionsusedtomanipulatewithstringsinSQLServer:
![Page 67: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/67.jpg)
67 Functions
StructuredQueryLanguage(SQL)
• CHAR • CHARINDEX • REPLACE • SUBSTRING • LEN • REVERSE • LEFT• RIGHT • LOWER • UPPER • LTRIM • RTRIM
ReadmoreaboutthesefunctionsintheSQLServerHelp.
11.1.2 DateandTimeFunctions
HerearesomeusefulDateandTimefunctionsinSQLServer:
• DATEPART• GETDATE• DATEADD• DATEDIFF• DAY• MONTH• YEAR• ISDATE
ReadmoreaboutthesefunctionsintheSQLServerHelp.
11.1.3 MathematicsandStatisticsFunctions
HerearesomeusefulfunctionsformathematicsandstatisticsinSQLServer:
• COUNT• MIN,MAX• COS,SIN,TAN• SQRT• STDEV• MEAN• AVG
![Page 68: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/68.jpg)
68 Functions
StructuredQueryLanguage(SQL)
ReadmoreaboutthesefunctionsintheSQLServerHelp.
11.1.4 AVG()
TheAVG()functionreturnstheaveragevalueofanumericcolumn.
Syntax:
SELECT AVG(column_name) FROM table_name
Example:
GivenaGRADEtable:
Wewanttofindtheaveragegradeforaspecificstudent:
select AVG(Grade) as AvgGrade from GRADE where StudentId=1
11.1.5 COUNT()
TheCOUNT()functionreturnsthenumberofrowsthatmatchesaspecifiedcriteria.
TheCOUNT(column_name)functionreturnsthenumberofvalues(NULLvalueswillnotbecounted)ofthespecifiedcolumn:
SELECT COUNT(column_name) FROM table_name
TheCOUNT(*)functionreturnsthenumberofrecordsinatable:
SELECT COUNT(*) FROM table_name
![Page 69: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/69.jpg)
69 Functions
StructuredQueryLanguage(SQL)
WeusetheCUSTOMERtableasanexample:
select COUNT(*) as NumbersofCustomers from CUSTOMER
11.1.6 TheGROUPBYStatement
AggregatefunctionsoftenneedanaddedGROUPBYstatement.
TheGROUPBYstatementisusedinconjunctionwiththeaggregatefunctionstogrouptheresult-setbyoneormorecolumns.
Syntax
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
Example:
WeusetheCUSTOMERtableasanexample:
Ifwetrythefollowing:
select FirstName, MAX(AreaCode) from CUSTOMER
Wegetthefollowingerrormessage:
Column 'CUSTOMER.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ThesolutionistousetheGROUPBY:
select FirstName, MAX(AreaCode) from CUSTOMER group by FirstName
![Page 70: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/70.jpg)
70 Functions
StructuredQueryLanguage(SQL)
11.1.7 TheHAVINGClause
TheHAVINGclausewasaddedtoSQLbecausetheWHEREkeywordcouldnotbeusedwithaggregatefunctions.
Syntax:
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
WeusetheGRADEtableasanexample:
select * from GRADE
FirstweusetheGROUPBYstatement:
select CourseId, AVG(Grade) from GRADE group by CourseId
Whilethefollowingquery:
select CourseId, AVG(Grade) from GRADE group by CourseId having AVG(Grade)>3
![Page 71: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/71.jpg)
71 Functions
StructuredQueryLanguage(SQL)
11.2 User-definedFunctionsINSQL,wemayalsocreateourownfunctions,so-calleduser-definedfunctions.
Auser-definedfunctionisaroutinethatacceptsparameters,performsanaction,suchasacomplexcalculation,andreturnstheresultofthatactionasavalue.Thereturnvaluecaneitherbeascalar(single)valueoratable.Usethisstatementtocreateareusableroutinethatcanbeusedinotherqueries.
InSQLdatabases,auser-definedfunctionprovidesamechanismforextendingthefunctionalityofthedatabaseserverbyaddingafunctionthatcanbeevaluatedinSQLstatements.TheSQLstandarddistinguishesbetweenscalarandtablefunctions.Ascalarfunctionreturnsonlyasinglevalue(orNULL),whereasatablefunctionreturnsa(relational)tablecomprisingzeroormorerows,eachrowwithoneormorecolumns.
StoredProceduresvs.Functions:
• Onlyfunctionscanreturnavalue(usingtheRETURNkeyword). • StoredprocedurescanuseRETURNkeywordbutwithoutanyvaluebeingpassed[1] • FunctionscouldbeusedinSELECTstatements,providedtheydon’tdoanydata
manipulationandalsoshouldnothaveanyOUTorINOUTparameters. • Functionsmustreturnavalue,butforstoredproceduresthisisnotcompulsory. • AfunctioncanhaveonlyINparameters,whilestoredproceduresmayhaveOUTorIN
OUTparameters. • Afunctionisasubprogramwrittentoperformcertaincomputationsandreturna
singlevalue. • Astoredprocedureisasubprogramwrittentoperformasetofactions,andcan
returnmultiplevaluesusingtheOUTparameterorreturnnovalueatall.
User-definedfunctionsinSQLaredeclaredusingtheCREATEFUNCTIONstatement.
Whenwehavecreatedthefunction,wecanusethefunctionthesamewayweusebuilt-infunctions.
![Page 72: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/72.jpg)
72
12 TriggersAdatabasetriggeriscodethatisautomaticallyexecutedinresponsetocertaineventsonaparticulartableinadatabase.
SyntaxforcreatingaTrigger:
CREATE TRIGGER <TriggerName> on <TableName> FOR INSERT, UPDATE, DELETE AS … Create your Code here GO
TheTriggerwillautomaticallybeexecutedwhendataisinserted,updatedordeletedinthetableasspecifiedintheTriggerheader.
INSERTEDandDELETED:
Insidetriggerswecanusetwospecialtables:theDELETEDtableandtheINSERTEDtables.SQLServerautomaticallycreatesandmanagesthesetables.Youcanusethesetemporary,
![Page 73: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/73.jpg)
73 Triggers
StructuredQueryLanguage(SQL)
memory-residenttablestotesttheeffectsofcertaindatamodifications.Youcannotmodifythedatainthesetables.
TheDELETEDtablestorescopiesoftheaffectedrowsduringDELETEandUPDATEstatements.DuringtheexecutionofaDELETEorUPDATEstatement,rowsaredeletedfromthetriggertableandtransferredtotheDELETEDtable.
TheINSERTEDtablestorescopiesoftheaffectedrowsduringINSERTandUPDATEstatements.Duringaninsertorupdatetransaction,newrowsareaddedtoboththeINSERTEDtableandthetriggertable.TherowsintheINSERTEDtablearecopiesofthenewrowsinthetriggertable.
Example:
WewillusetheCUSTOMERtableasanexample:
WewillcreateaTRIGGERthatwillcheckifthePhonenumberisvalidwhenweinsertorupdatedataintheCUSTOMERtable.Thevalidationcheckwillbeverysimple,i.e.,wewillcheckifthePhonenumberislessthan8digits(whichisnormallengthinNorway).IfthePhonenumberislessthan8digits,thefollowingmessage“PhoneNumberisnotvalid”bewritteninplaceofthewrongnumberinthePhonecolumn.
TheTRIGGERbecomessomethinglikethis:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CheckPhoneNumber' AND type = 'TR') DROP TRIGGER CheckPhoneNumber GO CREATE TRIGGER CheckPhoneNumber ON CUSTOMER FOR UPDATE, INSERT AS DECLARE @CustomerId int, @Phone varchar(50), @Message varchar(50) set nocount on select @CustomerId = CustomerId from INSERTED select @Phone = Phone from INSERTED
![Page 74: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/74.jpg)
74 Triggers
StructuredQueryLanguage(SQL)
set @Message = 'Phone Number ' + @Phone + ' is not valid' if len(@Phone) < 8 --Check if Phone Number have less than 8 digits update CUSTOMER set Phone = @Message where CustomerId = @CustomerId set nocount off GO
WetesttheTRIGGERwiththefollowingINSERTINTOstatement:
INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName, AreaCode, Address, Phone) VALUES ('1003', 'Obama', 'Barak', 51, 'Nevada', '4444')
Theresultsbecome:
Asyoucansee,theTRIGGERworksasexpected.
WetrytoupdatethePhonenumbertoavalidnumber:
update CUSTOMER set Phone = '44444444' where CustomerNumber = '1003'
Theresultsbecome:
![Page 75: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/75.jpg)
75
13 CommunicationfromotherApplications
ADatabaseisastructuredwaytostorelotsofinformation.Theinformationisstoredindifferenttables.“Everything”todayisstoredindatabases.
Examples:
• Bank/Accountsystems • InformationinWebpagessuchasFacebook,Wikipedia,YouTube• …lotsofotherexamples
Thismeansweneedtobeabletocommunicatewiththedatabasefromotherapplicationsandprogramminglanguagesinordertoinsert,updateorretrievedatafromthedatabase.
13.1 ODBCODBC(OpenDatabaseConnectivity)isastandardizedinterface(API)foraccessingthedatabasefromaclient.Youcanusethisstandardtocommunicatewithdatabasesfromdifferentvendors,suchasOracle,SQLServer,etc.ThedesignersofODBCaimedtomakeitindependentofprogramminglanguages,databasesystems,andoperatingsystems.
WewillusetheODBCDataSourceAdministrator:
![Page 76: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/76.jpg)
76 CommunicationfromotherApplications
StructuredQueryLanguage(SQL)
13.2 MicrosoftExcelMicrosoftExcelhastheabilitytoretrievedatafromdifferentdatasources,includingdifferentdatabasesystems.ItisverysimpletoretrievedatafromSQLServerintoExcelsinceExcelandSQLServerhasthesamevendor(Microsoft).
![Page 77: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/77.jpg)
77 CommunicationfromotherApplications
StructuredQueryLanguage(SQL)
![Page 78: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/78.jpg)
78
14 ReferencesMyBlog:https://www.halvorsen.blog
MicrosoftofficialSQLServerWebsite-http://www.microsoft.com/sqlserver
SQLServerBooksOnline-http://msdn.microsoft.com/en-us/library/ms166020.aspx
SQLServerHelp
w3shools.com-http://www.w3schools.com/sql
Wikipedia–MicrosoftSQLServer-http://en.wikipedia.org/wiki/Microsoft_SQL_Server
Wikipedia-SQL-http://en.wikipedia.org/wiki/SQL
Wikipedia–TransactSQL-http://en.wikipedia.org/wiki/T-SQL
![Page 79: Structured Query Language...Structured Query Language (SQL) 3.2 Create Tables using the Designer Tools Even if you can do “everything” using the SQL language, it is sometimes easier](https://reader034.vdocuments.us/reader034/viewer/2022042712/5f989bb7d7e8fc0fc04814fb/html5/thumbnails/79.jpg)
StructuredQueryLanguage
Hans-PetterHalvorsen
Copyright©2017
https://www.halvorsen.blog