tutorials, it was a week before I finally got a working stored procedure I'll stop rambling Normally, you would call a database with a query like: It is also somewhat a standard in the business world to use it, but SQL server does not require it
SQL can create stored procedures in a database The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data
This tutorial uses Transact-SQL, the Microsoft implementation of the create views and stored procedures and configure a user to the database and the data
but if we will use the stored procedure then we need to write all operation and fetch the record whereas normal SQL query fired on database server get parse
SQL i About the Tutorial SQL is a database computer language designed for the retrieval and management of data in a relational database SQL stands for Structured Query Language 46 10 SQL ─ SELECT Database, USE Statement Allows users to create view, stored procedure, functions in a database
Using SQL Queries Writing More Powerful Stored Procedures The data files used for the tutorial are freely available from public web sites; links are VoltDB supports all of the standard SQL query statements, such as INSERT, UPDATE
Rather than use a trademark symbol with every occurrence of a trademarked Query Your Heart Out peers, for being my PL/SQL tutorial guinea pigs Jonathan This is a book about writing stored procedures for an Oracle database
Reduced network traffic: When we use stored procedures instead of writing T- SQL queries at the application level, only the procedure name is passed over the
Create Trigger EmpDate Before Insert On Employee Typically used inside triggers, stored procedures, or stored functions Cursor IS ;
In MySQL Stored Procedure Programming , they put statements, SQL in stored programs, and error handling how to write high-performance SQL as well Chapter 2, MySQL Stored Programming Tutorial, is a tutorial that is designed to
[PDF] tutorialspoint angular 4
[PDF] tutorialspoint angular 6
[PDF] tutorialspoint c bitwise operators
[PDF] tutorialspoint c sharp compiler
[PDF] tutorialspoint java pdf full
[PDF] tutorialspoint java tutorial pdf free download
[PDF] tv5 apprendre le francais b1
[PDF] tv5 apprendre le francais b2
[PDF] tv5 apprendre le français niveau a1
[PDF] tv5 monde apprendre francais b2
[PDF] tv5 monde apprendre le francais baguette
[PDF] tv5 monde enseigner le francais b2
[PDF] tv5monde apprendre b2
[PDF] tv5monde apprendre le français
[PDF] tv5monde apprendre le français a2
61 IntroductiontoSQLSQL(StructuredQueryLanguage)isadatabasecomputerlanguagedesignedformanagingdatainrelationaldatabasemanagementsystems(RDBMS).SQL,isastandardizedcomputerlanguagethatwasoriginallydevelopedbyIBMforquerying,alteringanddefiningrelationaldatabases,usingdeclarativestatements.SQLispronounced/ˌɛskjuːˈɛl/ (letterbyletter) or/ˈsiːkwəl/ (asaword).WhatcanSQLdo?• SQLcanexecutequeriesagainstadatabase• SQLcanretrievedatafromadatabase• SQLcaninsertrecordsinadatabase• SQLcanupdaterecordsinadatabase• SQLcandeleterecordsfromadatabase
7IntroductiontoSQLStructuredQueryLanguage(SQL)• SQLcancreatenewdatabases• SQLcancreatenewtablesinadatabase• SQLcancreatestoredproceduresinadatabase• SQLcancreateviewsinadatabase• SQLcansetpermissionsontables,procedures,andviewsEvenifSQLisastandard,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• ...lotsofothersystemsInthisTutorial,wewillfocusonMicrosoftSQLServer.SQLServerusesT-SQL(Transact-SQL).T-SQLisMicrosoft'sproprietaryextensiontoSQL.T-SQLisverysimilartostandardSQL,butinadditionitsupportssomeextrafunctionality,built-infunctions,etc.
8IntroductiontoSQLStructuredQueryLanguage(SQL)OtherusefulTutorialsaboutdatabases:• IntroductiontoDatabaseSystems• DatabaseCommunicationinLabVIEWTheseTutorialsarelocatedat:https://www.halvorsen.blog1.1 DataDefinitionLanguage(DDL)TheDataDefinitionLanguage(DDL)managestableandindexstructure.ThemostbasicitemsofDDLaretheCREATE,ALTER,RENAMEandDROPstatements:• CREATEcreatesanobject(atable,forexample)inthedatabase.• DROPdeletesanobjectinthedatabase,usuallyirretrievably.• ALTERmodifiesthestructureanexistingobjectinvariousways - forexample,addingacolumntoanexistingtable.1.2 DataManipulationLanguage(DML)TheDataManipulationLanguage(DML)isthesubsetofSQLusedtoadd,updateanddeletedata.TheacronymCRUDreferstoallofthemajorfunctionsthatneedtobeimplementedinarelationaldatabaseapplicationtoconsideritcomplete.EachletterintheacronymcanbemappedtoastandardSQLstatement:OperationSQLDescriptionCreateINSERTINTOinsertsnewdataintoadatabaseRead(Retrieve)SELECTextractsdatafromadatabaseUpdateUPDATEupdatesdatainadatabaseDelete(Destroy)DELETEdeletesdatafromadatabase
92 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).
10IntroductiontoSQLServerStructuredQueryLanguage(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).
11IntroductiontoSQLServerStructuredQueryLanguage(SQL)2.1.1 CreateanewDatabaseItisquitesimpletocreateanewdatabaseinMicrosoftSQLServer.Justright-clickonthe"Databases"nodeandselect"NewDatabase..."Therearelotsofsettingsyoumaysetregardingyourdatabase,buttheonlyinformationyoumustfillinisthenameofyourdatabase:
12IntroductiontoSQLServerStructuredQueryLanguage(SQL)YoumayalsousetheSQLlanguagetocreateanewdatabase,butsometimesitiseasiertojustusethebuilt-infeaturesintheManagementStudio.2.1.2 QueriesInordertomakeanewSQLquery,selectthe"NewQuery"buttonfromtheToolbar.HerewecanwriteanykindofqueriesthatissupportedbytheSQLlanguage.
133 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.
14CREATETABLEStructuredQueryLanguage(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.
15CREATETABLEStructuredQueryLanguage(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.
17CREATETABLEStructuredQueryLanguage(SQL)3.2 CreateTablesusingtheDesignerToolsEvenifyoucando"everything"usingtheSQLlanguage,itissometimeseasiertodoitinthedesignertoolsintheManagementStudioinSQLServer.Insteadofcreatingascriptyoumayaswelleasilyusethedesignerforcreatingtables.Step1:Select"NewTable...":Step2:Next,thetabledesignerpopsupwhereyoucanaddcolumns,datatypes,etc.InthisdesignerwemayalsospecifyColumnNames,DataTypes,etc.Step3:SavethetablebyclickingtheSavebutton.3.3 SQLConstraintsConstraintsareusedtolimitthetypeofdatathatcangointoatable.
18CREATETABLEStructuredQueryLanguage(SQL)Constraintscanbespecifiedwhenatableiscreated(withtheCREATETABLEstatement)orafterthetableiscreated(withtheALTERTABLEstatement).Herearethemostimportantconstraints:• PRIMARYKEY• NOTNULL• UNIQUE• FOREIGNKEY• CHECK• DEFAULT• IDENTITYInthesectionsbelowwewillexplainsomeoftheseindetail.3.3.1 PRIMARYKEYThePRIMARYKEYconstraintuniquelyidentifieseachrecordinadatabasetable.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.
19CREATETABLEStructuredQueryLanguage(SQL)SettingPrimaryKeysintheDesignerTools:IfyouusetheDesignertoolsinSQLServer,youcaneasilysettheprimaryKeyinatablejustbyright-clickandselect"SetprimaryKey".TheprimaryKeycolumnwillthenhaveasmallkeyinfronttoillustratethatthiscolumnisaPrimaryKey.3.3.2 FOREIGNKEYAFOREIGNKEYinonetablepointstoaPRIMARYKEYinanothertable.Example:WewillcreateaCREATETABLEscriptforthesetables:
20CREATETABLEStructuredQueryLanguage(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...":
22CREATETABLEStructuredQueryLanguage(SQL)HereyouspecifytheprimaryKeyColumninthePrimaryKeytableandtheForeignKeyColumnintheForeignKeytable.3.3.3 NOTNULL/RequiredColumnsTheNOTNULLconstraintenforcesacolumntoNOTacceptNULLvalues.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
23CREATETABLEStructuredQueryLanguage(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 UNIQUETheUNIQUEconstraintuniquelyidentifieseachrecordinadatabasetable.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,
24CREATETABLEStructuredQueryLanguage(SQL) Phone varchar(50) NULL, ) GO Weseethatthe"CustomerNumber"issettoUNIQUE,meaningeachcustomermusthaveauniqueCustomerNumber.Example:SettingUNIQUEintheDesignerTools:Ifyouwanttousethedesigner,right-clickonthecolumnthatyouwanttobeUNIQUEandselect"Indexes/Keys...":Thenclick"Add"andthensetthe"IsUnique"propertyto"Yes":
25CREATETABLEStructuredQueryLanguage(SQL)3.3.5 CHECKTheCHECKconstraintisusedtolimitthevaluerangethatcanbeplacedinacolumn.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...":
27CREATETABLEStructuredQueryLanguage(SQL)3.3.6 DEFAULTTheDEFAULTconstraintisusedtoinsertadefaultvalueintoacolumn.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":
28CREATETABLEStructuredQueryLanguage(SQL)3.3.7 AUTOINCREMENTorIDENTITYVeryoftenwewouldlikethevalueoftheprimarykeyfieldtobecreatedautomaticallyeverytimeanewrecordisinserted.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:
29CREATETABLEStructuredQueryLanguage(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
30CREATETABLEStructuredQueryLanguage(SQL)Else ALTER TABLE CUSTOMER ADD CustomerNumber int GO ...
314 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:
335 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!
356 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":
36DELETEStructuredQueryLanguage(SQL)
377 SELECTTheSELECTstatementisprobablythemostusedSQLcommand.TheSELECTstatementisusedforretrievingrowsfromthedatabaseandenablestheselectionofoneormanyrowsorcolumnsfromoneormanytablesinthedatabase.WewillusetheCUSTOMERtableasanexample.TheCUSTOMERtablehasthefollowingcolumns:TheCUSTOMERtablecontainsthefollowingdata:Example:select * from CUSTOMER ThissimpleexamplegetsallthedatainthetableCUSTOMER.Thesymbol"*"isusedwhenyouwanttogetallthecolumnsinthetable.
38SELECTStructuredQueryLanguage(SQL)Ifyouonlywantafewcolumns,youmayspecifythenamesofthecolumnsyouwanttoretrieve,example:select CustomerId, LastName, FirstName from CUSTOMER SointhesimplestformwecanusetheSELECTstatementasfollows:select from 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:
39SELECTStructuredQueryLanguage(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.
40SELECTStructuredQueryLanguage(SQL)select * from CUSTOMER order by LastName desc 7.2 SELECTDISTINCTInatable,someofthecolumnsmaycontainduplicatevalues.Thisisnotaproblem,however,sometimesyouwillwanttolistonlythedifferent(distinct)valuesinatable.TheDISTINCTkeywordcanbeusedtoreturnonlydistinct(different)values.Thesyntaxisasfollows:select distinct from Example:select distinct FirstName from CUSTOMER 7.3 TheWHEREClauseTheWHEREclauseisusedtoextractonlythoserecordsthatfulfillaspecifiedcriterion.Thesyntaxisasfollows:select from where operator value Example:select * from CUSTOMER where CustomerNumber='1001'
41SELECTStructuredQueryLanguage(SQL)Note!SQLusessinglequotesaroundtextvalues,asshownintheexampleabove.7.3.1 OperatorsWiththeWHEREclause,thefollowingoperatorscanbeused:OperatorDescription=Equal<>Notequal>Greaterthan=Greaterthanorequal<=LessthanorequalBETWEENBetweenaninclusiverangeLIKESearchforapatternINIfyouknowtheexactvalueyouwanttoreturnforatleastoneofthecolumnsExamples:select * from CUSTOMER where AreaCode>30 7.3.2 LIKEOperatorTheLIKEoperatorisusedtosearchforaspecifiedpatterninacolumn.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.
42SELECTStructuredQueryLanguage(SQL)select * from CUSTOMER where LastName like '%a%' YoumayalsocombinewiththeNOTkeyword,example:select * from CUSTOMER where LastName not like '%a%' 7.3.3 INOperatorTheINoperatorallowsyoutospecifymultiplevaluesinaWHEREclause.Syntax:SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) 7.3.4 BETWEENOperatorTheBETWEENoperatorselectsarangeofdatabetweentwovalues.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:
43SELECTStructuredQueryLanguage(SQL)WildcardDescription%Asubstituteforzeroormorecharacters_Asubstituteforexactlyonecharacter[charlist]Anysinglecharacterincharlist[^charlist]or[!charlist]AnysinglecharacternotincharlistExamples: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'
44SELECTStructuredQueryLanguage(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
45SELECTStructuredQueryLanguage(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.
46SELECTStructuredQueryLanguage(SQL)7.8.1 DifferentSQLJOINsBeforewecontinuewithexamples,wewilllistthetypesofJOINyoucanuse,andthedifferencesbetweenthem.• JOIN:Returnrowswhenthereisatleastonematchinbothtables• LEFTJOIN:Returnallrowsfromthelefttable,eveniftherearenomatchesintherighttable• RIGHTJOIN:Returnallrowsfromtherighttable,eveniftherearenomatchesinthelefttable• FULLJOIN:ReturnrowswhenthereisamatchinoneofthetablesExample:Given2tables:• SCHOOL• CLASS
47SELECTStructuredQueryLanguage(SQL)Thediagramisshownbelow:Wewanttogetthefollowinginformationusingaquery:SchoolNameClassName............InordertogetinformationfrommorethanonetableweneedtousetheJOIN.TheJOINisusedtojointheprimarykeyinonetablewiththeforeignkeyinanothertable.select SCHOOL.SchoolName, CLASS.ClassName from SCHOOL INNER JOIN CLASS ON SCHOOL.SchoolId = CLASS.SchoolId
488 SQLScriptsASQLscriptisacollectionofSQLstatementsthatyoucanexecuteinoneoperation.YoucanuseanykindofSQLcommands,suchasinsert,select,delete,update,etc.Inadditionyoucandefineandusevariables,andyoumayalsouseprogramflowlikeIf-Else,etc.Youmayalsoaddcommentstomakethescripteasiertoreadandunderstand.8.1 UsingCommentsUsingcommentsinyouSQLscriptisimportanttomakethescripteasiertoreadandunderstand.InSQLwecanuse2differentkindsofcomments:• Single-linecomment• Multiple-linecomment8.1.1 Single-linecommentWecancommentonelineatthetimeusing"--"beforethetextyouwanttocommentout.Syntax:-- text_of_comment 8.1.2 Multiple-linecommentWecancommentseverallineusing"/*"inthestartofthecommentand"*/"intheendofthecomment.Syntax:/* text_of_comment text_of_comment */
49SQLScriptsStructuredQueryLanguage(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.
50SQLScriptsStructuredQueryLanguage(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 @@IDENTITYAfteranINSERT,SELECTINTO,orbulkcopystatementiscompleted,@@IDENTITYcontainsthelastidentityvaluethatisgeneratedbythestatement.Ifthestatementdidnotaffectanytableswithidentitycolumns,@@IDENTITYreturnsNULL.Ifmultiplerowsareinserted,generatingmultipleidentityvalues,@@IDENTITYreturnsthelastidentityvaluegenerated.Example:Giventotables;SCHOOLandCOURSE:
51SQLScriptsStructuredQueryLanguage(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-ELSETheIF-ELSEisveryuseful.Belowweseeanexample:declare @customerNumber int
52SQLScriptsStructuredQueryLanguage(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 WHILEWecanalsouseWHILE,whichisknownfromotherprogramminglanguages.Example:WeareusingtheCUSTOMERtable:andthefollowingquery:while (select AreaCode from CUSTOMER where CustomerId=1) < 20 begin update CUSTOMER set AreaCode = AreaCode + 1
53SQLScriptsStructuredQueryLanguage(SQL)end select * from CUSTOMER AsyoucanseethecodeinsidetheWHILEloopisexecutedaslongas"AreaCode"forCustomerId=1islessthan20.Foreachiterationisthe"AreaCode"forthatcustomerincrementedwith1.8.4.3 CASETheCASEstatementevaluatesalistofconditionsandreturnsoneofmultiplepossibleresultexpressions.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
54SQLScriptsStructuredQueryLanguage(SQL)GRADE 8.4.4 CURSORInadvancesscripts,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
55SQLScriptsStructuredQueryLanguage(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.• UsetheFETCHINTOstatementtofetchindividualrowsandhavethedataforeachcolumnmovedintoaspecifiedvariable.OtherSQLstatementscanthenreferencethosevariablestoaccessthefetcheddatavalues.• Whenyouarefinishedwiththecursor,usetheCLOSEstatement.Closingacursorfreessomeresources,suchasthecursor'sresultsetanditslocksonthecurrentrow.TheDEALLOCATEstatementcompletelyfreesallresourcesallocatedtothecursor,includingthecursorname.
569 ViewsViewsarevirtualtableforeasieraccesstodatastoredinmultipletables.SyntaxforcreatingaView:CREATE VIEW AS ... ...butitmightbeeasiertodoitinthegraphicalviewdesignerthatarebuiltintoSQLManagementStudio.SyntaxforusingaView:select * from where ... Asshownabove,weuseaVIEWjustlikeweuseanordinarytable.
57ViewsStructuredQueryLanguage(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:
60ViewsStructuredQueryLanguage(SQL)Step5:UsetheVIEWinaquery:select * from SchoolView
6110 StoredProceduresAStoredProcedureisaprecompiledcollectionofSQLstatements.Inastoredprocedureyoucanuseifsentence,declarevariables,etc.SyntaxforcreatingaStoredProcedure:CREATE PROCEDURE @ ... declare @myVariable ... Create your Code here Note!Youneedtousethesymbol"@"beforevariablenames.SyntaxforusingaStoredProcedure:EXECUTE Example:
62StoredProceduresStructuredQueryLanguage(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:
63StoredProceduresStructuredQueryLanguage(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:
64StoredProceduresStructuredQueryLanguage(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.
65StoredProceduresStructuredQueryLanguage(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.
6611 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 StringFunctionsHerearesomeusefulfunctionsusedtomanipulatewithstringsinSQLServer:
67FunctionsStructuredQueryLanguage(SQL)• CHAR• CHARINDEX• REPLACE• SUBSTRING• LEN• REVERSE• LEFT• RIGHT• LOWER• UPPER• LTRIM• RTRIMReadmoreaboutthesefunctionsintheSQLServerHelp.11.1.2 DateandTimeFunctionsHerearesomeusefulDateandTimefunctionsinSQLServer:• DATEPART• GETDATE• DATEADD• DATEDIFF• DAY• MONTH• YEAR• ISDATEReadmoreaboutthesefunctionsintheSQLServerHelp.11.1.3 MathematicsandStatisticsFunctionsHerearesomeusefulfunctionsformathematicsandstatisticsinSQLServer:• COUNT• MIN,MAX• COS,SIN,TAN• SQRT• STDEV• MEAN• AVG
68FunctionsStructuredQueryLanguage(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
69FunctionsStructuredQueryLanguage(SQL)WeusetheCUSTOMERtableasanexample:select COUNT(*) as NumbersofCustomers from CUSTOMER 11.1.6 TheGROUPBYStatementAggregatefunctionsoftenneedanaddedGROUPBYstatement.TheGROUPBYstatementisusedinconjunctionwiththeaggregatefunctionstogrouptheresult-setbyoneormorecolumns.SyntaxSELECT 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
70FunctionsStructuredQueryLanguage(SQL)11.1.7 TheHAVINGClauseTheHAVINGclausewasaddedtoSQLbecausetheWHEREkeywordcouldnotbeusedwithaggregatefunctions.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
71FunctionsStructuredQueryLanguage(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'tdoanydatamanipulationandalsoshouldnothaveanyOUTorINOUTparameters.• Functionsmustreturnavalue,butforstoredproceduresthisisnotcompulsory.• AfunctioncanhaveonlyINparameters,whilestoredproceduresmayhaveOUTorINOUTparameters.• Afunctionisasubprogramwrittentoperformcertaincomputationsandreturnasinglevalue.• Astoredprocedureisasubprogramwrittentoperformasetofactions,andcanreturnmultiplevaluesusingtheOUTparameterorreturnnovalueatall.User-definedfunctionsinSQLaredeclaredusingtheCREATEFUNCTIONstatement.Whenwehavecreatedthefunction,wecanusethefunctionthesamewayweusebuilt-infunctions.
7212 TriggersAdatabasetriggeriscodethatisautomaticallyexecutedinresponsetocertaineventsonaparticulartableinadatabase.SyntaxforcreatingaTrigger:CREATE TRIGGER on FOR INSERT, UPDATE, DELETE AS ... Create your Code here GO TheTriggerwillautomaticallybeexecutedwhendataisinserted,updatedordeletedinthetableasspecifiedintheTriggerheader.INSERTEDandDELETED:Insidetriggerswecanusetwospecialtables:theDELETEDtableandtheINSERTEDtables.SQLServerautomaticallycreatesandmanagesthesetables.Youcanusethesetemporary,
73TriggersStructuredQueryLanguage(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
74TriggersStructuredQueryLanguage(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:
7513 CommunicationfromotherApplicationsADatabaseisastructuredwaytostorelotsofinformation.Theinformationisstoredindifferenttables."Everything"todayisstoredindatabases.Examples:• Bank/Accountsystems• InformationinWebpagessuchasFacebook,Wikipedia,YouTube• ...lotsofotherexamplesThismeansweneedtobeabletocommunicatewiththedatabasefromotherapplicationsandprogramminglanguagesinordertoinsert,updateorretrievedatafromthedatabase.13.1 ODBCODBC(OpenDatabaseConnectivity)isastandardizedinterface(API)foraccessingthedatabasefromaclient.Youcanusethisstandardtocommunicatewithdatabasesfromdifferentvendors,suchasOracle,SQLServer,etc.ThedesignersofODBCaimedtomakeitindependentofprogramminglanguages,databasesystems,andoperatingsystems.WewillusetheODBCDataSourceAdministrator:
76CommunicationfromotherApplicationsStructuredQueryLanguage(SQL)13.2 MicrosoftExcelMicrosoftExcelhastheabilitytoretrievedatafromdifferentdatasources,includingdifferentdatabasesystems.ItisverysimpletoretrievedatafromSQLServerintoExcelsinceExcelandSQLServerhasthesamevendor(Microsoft).
7814 ReferencesMyBlog:https://www.halvorsen.blogMicrosoftofficialSQLServerWebsite-http://www.microsoft.com/sqlserverSQLServerBooksOnline-http://msdn.microsoft.com/en-us/library/ms166020.aspxSQLServerHelpw3shools.com-http://www.w3schools.com/sqlWikipedia-MicrosoftSQLServer-http://en.wikipedia.org/wiki/Microsoft_SQL_ServerWikipedia-SQL-http://en.wikipedia.org/wiki/SQLWikipedia-TransactSQL-http://en.wikipedia.org/wiki/T-SQL
quotesdbs_dbs9.pdfusesText_15
[PDF] Writing a Stored Procedure