Action Queries - Electronic Commerce - Lecture Slides, Slides of Fundamentals of E-Commerce

Download Action Queries - Electronic Commerce - Lecture Slides and more Slides Fundamentals of E-Commerce in PDF only on Docsity! Action Queries Docsity.com Learning Objectives • MS SQL Management Studio Express • Create SQL action queries to insert, update, and delete data • Understand action query transactions and record locking and blocking • Understand basic T-SQL programming concepts 4-2 Docsity.com MS SQL Mgt Studio Express • Try to operate the software 4-5 Docsity.com MS SQL Mgt Studio Express • Try to run sample queries • Use you last name as your database – Work with your database only! • Mention your database name every time for every operation!!!!!!!!!!! 4-6 Docsity.com MS SQL Mgt Studio Express CREATE DATABASE serenko USE serenko CREATE TABLE Table1 ( MyID BIGINT IDENTITY CONSTRAINT PK_Table1_MyID PRIMARY KEY CLUSTERED (MyID), MyFirstName VARCHAR(50), MyLastName VARCHAR(50), ) USE serenko INSERT INTO Table1(MyFirstName, MyLastName) VALUES ('Alexander', 'Serenko') USE serenko SELECT * FROM Table1 4-7 Docsity.com Executing Action Queries • Connect to the database instance • Type query into the Query Editor • Execute the USE command to specify the database • Execute the query • Note the confirmation message on the Message tab 4-10 Docsity.com INSERT Action Queries Columns-List Format • Lists column names and values explicitly • Syntax INSERT INTO TableName (Column1, Column2,..) VALUES(Column1Value, Column2Value, ... ) 4-11 Docsity.com Columns-List Format • Column names and their values must appear in the same order in each list • Omitted column names have NULL value • Column value must be specified for columns with NOT NULL constraints 4-12 Docsity.com No-Columns-List Format • Values list must contain a value for every table column • Column values must appear in the order in which the columns were listed when the table was created 4-15 Docsity.com No-Columns-List Format (cont’d) • To insert a NULL value into a column, place the word NULL in the values list in place of the column value • NULL marker should be capital letters • NULL marker does not appear in quotation marks USE serenko INSERT INTO Table1(MyFirstName, MyLastName) VALUES (NULL, 'Serenko2') 4-16 Docsity.com UPDATE Action Queries • Updates data values in existing records • Syntax UPDATE TableName SET Column1 = Column1Value, Column2 = Column2Value, ... WHERE SearchCondition(s) 4-17 Docsity.com UPDATE Action Queries (cont’d) • Data values can contain arithmetic expressions – Number values as numerals – Text values as characters in single quotation marks – Dates as text strings in date formats • While updating a column with a foreign key constraint, the new value must already exist in the parent table • If the search condition is omitted,the UPDATE action query updates all table records 4-20 Docsity.com DELETE Action Queries • Deletes existing table records • Syntax DELETE FROM TableName WHERE SearchCondition(s) • A single DELETE action query can remove data from one table only • Do not delete a record that is the parent record in a foreign key relationship 4-21 Docsity.com DELETE Action Queries FIGURE 4-3 Examples of DELETE action queries DELETE FROM UniversityStudent WHERE StudentID = 7 (I row(s) affected) DELETE FROM UniversityTutorSession WHERE TutorSessionStartTime >= '10/1/2008' (5 row(s) affected) bei Docsity.com Database Transactions - Transaction • SQL server stores database data in a data file • Database changes in a transaction log file • All queries in a transaction must succeed, or none of them should succeed • Process of writing actions to a transaction log file and noting when action queries have been committed to the data file creates database transactions 4-25 Docsity.com Database Transactions - Transaction FIGURE 4-4 Action queries that comprise a transaction INSERT INTO UniversityEnrollment VALUES (6, 14, NULL) UPDATE UniversitySection SET SectionCurrentEnrollment = SectionCurrentEnrollment + 1 WHERE SectionID = 14 csi Docsity.com Database Transactions - Commit • Makes the query changes permanent in the database and visible to other users • Performed after entering all the queries in a transaction • Command - Commit • DBMS notes in the transaction log file • Changes cannot be rolled back 4-27 Docsity.com Database Transactions (cont’d) • BEGIN TRANSACTION – Statement that signals the beginning of a transaction • COMMIT TRANSACTION – Signals the end of the transaction and commits the transaction 4-30 Docsity.com Database Transactions (cont’d) • Three types of transactions – Autocommit – Explicit – Implicit 4-31 Docsity.com Autocommit Transaction • Default transaction mode • Commit each action query as soon as it executes • Does not require any specific transaction control commands 4-32 Docsity.com Explicit Transaction • Each transaction starts with a BEGIN TRANSACTION • All SQL commands follow part of the transaction • Each transaction ends with a COMMIT TRANSACTION 4-35 Docsity.com Explicit Transaction (cont’d) • Include commands to explicitly begin and end each transaction • Syntax 1 SET XACT_ABORT ON BEGIN TRANSACTION [TransactionName] Action Query(ies) [ROLLBACK TRANSACTION [TransactionName]] COMMIT TRANSACTION [TransactionName] 4-36 Docsity.com Explicit Transaction (cont’d) • Syntax 2 BEGIN TRY Program Command(s) END TRY BEGIN CATCH Error Handling Command(s) e.g., ROLLBACK TRANSACTION PRINT ‘Error Message’ END CATCH 4-37 Docsity.com Implicit Transaction (cont’d) • Syntax SET IMPLICIT_TRANSACTIONS ON --begin Transaction 1 Action Query(ies) [ROLLBACK TRANSACTION]|[COMMIT TRANSACTION] --begin Transaction 2 Action Query(ies) [ROLLBACK TRANSACTION]|[COMMIT TRANSACTION] ... 4-40 Docsity.com Implicit Transaction (cont’d) • Begins the Implicit transaction when set to ON • Instructs the DBMS instance to use implicit transactions • Syntax SET IMPLICIT_TRANSACTIONS ON 4-41 Docsity.com Locking and Blocking • Competing transactions – Transactions that attempt to view, update, and delete the same records simultaneously • Problems may occur 4-42 Docsity.com Locking • Other transactions cannot use the data manipulated by our transaction until it is committed • On commit, lock is released • Allows a transaction to run in isolation 4-45 Docsity.com Locking (cont’d) • Isolation levels – Specifies whether a transaction may run safely at the same time as other transactions – Is it the only transaction to be executed? – Can other transactions be executed simultaneously? • Ideally, all transactions should run in isolation to avoid problems 4-46 Docsity.com Blocking • Occurs when a transaction tries to access data locked by another transaction • The transaction cannot access the locked data and therefore cannot continue until the lock is released 4-47 Docsity.com Lock Levels • SQL server DBMS has limits as to how many locks it can manage at one time – Record lock – Page lock – Extent lock – Database lock 4-50 Docsity.com Lock Levels (cont’d) • Record locks – Locks an entire record of data – Lowest level at which the DBMS can lock data – Blocks the fewest transactions 4-51 Docsity.com Lock Levels (cont’d) • Page locks – Locks storage units of 8,060 bytes in a database – Contains a variable number of rows, depending on the volume of data in each row 4-52 Docsity.com Shared Lock • Read lock that multiple transactions can share • DBMS releases shared locks as soon as a SELECT command finishes processing • Lets other locks know that a transaction is currently reading the data but do not block other transactions from reading the data • Used by SQL Server for data that a transaction is currently reading but not changing 4-55 Docsity.com Exclusive Lock • Prevents other transactions from either reading or updating the locked record • Allowed only when all other locks on the record are released 4-56 Docsity.com Update Lock • Combination of a shared lock and exclusive lock • Allows a transaction to read a data value and then update it • Reading portion – Lock behaves as a shared lock, and other transactions can read the data • Update portion – Lock changes to an exclusive lock, and other transactions cannot read or update the record 4-57 Docsity.com Table Hints TABLE 4-| Table hints for transaction locking Hint Description HOLDLOCK Forces a shared lock for the duration of the transaction; same as SERIALIZABLE. NOLOCK Instructs to use no lock for the transaction, which permits dirty reads; same as READUNCOMMITTED. PAGELOCK Forces the use of a page-level lock. READCOMMITTED Releases the lock as soon as the command comple! (default hint) can change prior to completion of the transaction, phantom reads are possible. READPAST Instructs to skip locked rows during a read; can be used only with the READCOMMITTED isolation level hint and works. only with row-level locks. READUNCOMMITTED Instructs to use no-lock for the transaction, which permits. dirty reads; same as NOLOCK. REPEATABLEREAD Instructs to hold the lock until the transaction completes; data can change prior to completion of transaction; so phantom reads are possible. ROWLOCK Forces a row-level lock if possible; if the DBMS’s maximum lock limit has been reached, the lock level still escalates to page, extent, table, and database. SERIALIZABLE Forces a shared lock for the duration of transaction; same as HOLDLOCK. TABLOCK Forces a shared lock on the entire table. TABLOCKX Forces an exclusive lock on the entire table. UPDLOCK Forces an update lock and holds it for the entire transaction. XLOCK Forces an exclusive lock and holds it for the entire transaction. 4-60 Docsity.com Isolation Levels • To control how transactions acquire locks and how they behave when they encounter locks held by other transactions • Improves transaction processing performance 4-61 Docsity.com Isolation Level – Read Uncommitted • Used for generating reports quickly • Used on transactions that rarely modify data • Command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 4-62 Docsity.com T-SQL Programming - Scripts • Scripts are written to automate database-related tasks • Scripts contain SQL commands • Scripts also contains commands to perform common programming tasks – Assigning variables – Manipulating numbers and text – Displaying outputs 4-65 Docsity.com T-SQL Programming – Uses of Scripts • To automate tasks that are performed repeatedly • To perform data processing tasks that are too complicated for a single query 4-66 Docsity.com T-SQL Batches • T-SQL script can be written in the Query Editor • T-SQL Interpreter – Parses the query to confirm that it does not contain syntax errors – Translates the query into machine-readable format and creates an execution plan during parsing • Execution plan – Contains instructions for running the query so that it executes in the fastest way 4-67 Docsity.com T-SQL Batches (cont’d) • GO command – Instructs the Query Editor to send to the T-SQL interpreter all the preceding commands from the beginning of the script or the last GO command • Batches generate two types of errors – Compile errors – Run-time errors 4-70 Docsity.com Compile Error • Same as syntax error • Results when the program statements do not obey the rules of the language • T-SQL interpreter reports the compile error in the Messages pane and does not execute the batch 4-71 Docsity.com Run Time Error • Occurs when the command syntax is correct but an error occurs and is detected by the DBMS • Execution usually stops, and the rest of the script commands do not execute • If error causes the transaction to fail, then the DBMS rolls back the previously executed commands 4-72 Docsity.com Variables – Naming Convention • Variable names must begin with a letter and can contain the special characters of @, $, #, and _ • Cannot contain embedded blank spaces • Avoid using reserved words • Create descriptive variable names made up of two joined words 4-75 Docsity.com Variables – Naming Convention (cont’d) • Use mixed-case letters – Start the first word with a lowercase letter – Start subsequent words with an uppercase letter • Variable name should be same as the table field name – good practice if you work with tables • DataType can be any of the SQL Server data types text, ntext, or image • Use a single DECLARE command to declare multiple variables separated by comma 4-76 Docsity.com Assigning Values to Variables • After the variable is declared, its initial value is NULL • Always assign an initial value to a variable before performing an operation on it • Use either the SET or the SELECT command to assign values to variables 4-77 Docsity.com Arithmetic Operations • Perform arithmetic operations on any of the number data types in the order of precedence – Multiplication – Division – Modulo – Addition and subtraction • Use parentheses to change the order of precedence • Assign result to a variable of appropriate data type 4-80 Docsity.com Arithmetic Operations TABLE 4-2. T-SQL arithmetic operators Operator Description Example Result + Addition 5 + 2 7 - Subtraction 52 3 * Multiplication 5 * 2 10 f Division 5 / 2 2.5 % Modulo (remainder of a division operation) 5 8 2 1 Docsity.com String–Handling Operations • Concatenate or join two separate text strings using plus sign (+) operator • Parse or separate, a single text string into its component parts • T-SQL provides several string-handling functions to support string handling • Example SUBSTRING,CHARINDEX 4-82 Docsity.com Decision Control Structures • Program structures that allow program execution to take different paths depending on the values of specific variables • IF-ELSE Decision Control Structure format 4-85 Docsity.com Decision Control Structures (cont’d) • BooleanExpression – Expression that T-SQL can evaluate as either true or false – Usually compares two values, such as a variable and a constant • If it evaluates as true, the first block of program statements executes • If it evaluates as false, the second block executes 4-86 Docsity.com Decision Control Structures (cont’d) • Run this query in SQL: IF (5=1) BEGIN PRINT 'IT IS TRUE' END ELSE BEGIN PRINT 'IT IS FALSE' END GO 4-87 Docsity.com