Using SQL Statements - Electronic Commerce - Lecture Slides, Slides of Fundamentals of E-Commerce

Download Using SQL Statements - Electronic Commerce - Lecture Slides and more Slides Fundamentals of E-Commerce in PDF only on Docsity!

Using SQL Statements

Docsity.com

Learning Objectives

• Sample university database

• Data types

• SQL statements

• Data Definition Language
• Data Manipulation Language

Creating and Managing Database

Tables

• CREATE TABLE TableName (Column1Name

DataType [(Length)] NULL|NOT NULL

[ConstraintSpecification], Column2Name

DataType [(Length)] NULL|NOT NULL

[ConstraintSpecification],... )

• DataType is SQL Server data types
• ConstraintSpecification specifies constraints on

column values

Creating and Managing Database

Tables (cont’d)

• Naming conventions for tables and columns
  • Must begin with a letter
  • Between one and 128 characters
    • Alphanumeric characters
    • Symbols
    • Special characters like @, #, $, _
  • Place the table or field name in square brackets if
    • The name begins with a character or symbol
    • Name contains a reserved word
  • Good idea : avoid special characters, reserved words and symbols (except for _)

SQL Server Data Type Definitions

• Specifying data types for the columns

• Basic database data categories

• Numbers data type
• Text data type
• Dates/times data type
• Binary values

Encoding Schemes

• MS SQL supports eight-bit and Unicode
• Eight-bit character coding
  • Represents characters as eight-bit binary values
  • Data types encoding occupy one byte of memory space for each character
• Unicode
  • Represents characters as 16-bit binary values
  • Encodes characters from many other alphabets and a large array of special symbols
  • Data types encoding occupy two bytes of memory for each character

Numbers Data Types (cont’d)

• Float and Real
  • Used for columns that require great deal of numeric precision
• Bit
  • Stores values that have either one or zero
• Tinyint, Smallint, Int, and Bigint
  • Stores integer data values of varying sizes
  • Optimize the storage space by selecting right data type
• Smallmoney, Money
  • Stores data values representing monetary units with currency symbols

Numbers Data Types (cont’d)

Text Data Types (cont’d)

• Char and Nchar

• Store fixed-width character data encoded in

the eight-bit (Char) and Unicode (Nchar)

coding schemes

• Store text data with a specific number of

characters

• Example - Two-character state abbreviations
  • What is the risk for a two-character state abbreviation?

Text Data Types (cont’d)

• Varchar, Text
  • Stores variable-width character data encoded in an eight-bit coding scheme
• Nvarchar, Ntext
  • Stores variable-width character data encoded in Unicode
• The maximum number of characters is specified

by enclosing the integer value in parentheses

Date/Time Data Types

• Datetime

• Stores a combined date and time value as two

four-byte integers

• First four bytes is the date portion
• Second four byte is the time portion (milliseconds)
• Example
• 1998-01-01 22:59:59.

Date/Time Data Types (cont’d)

• Smalldatetime
  • Stores a combined date and time value as two two- byte integers. - First two-byte integer is the date portion - Second two-byte integer stores the number of minutes since midnight
  • Example
    • 1998-01-01 23:
• For eCommerce transactions, what data type

would you prefer: datetime or smalldatetime?

Binary Data Types (cont’d)

• Variable-length Binary data

• Varbinary
  • Maximum size is 8,000 bytes
• Image
  • Stores larger quantities of any type of binary data
  • Maximum size is more than 8,000 bytes

Binary Data Types (cont’d)