Getting Started with SQL Server 2000

1
Getting Started with SQL Server 2000

• ICS 321
• Fall 2004
• Sang Sup Kim

2
Creating a Database

• Normally only a system administrator has the
  authorization to execute a CREATE DATABASE
  command.
• GRANT ALL statement_list TO account_list
• GRANT CREATE DATABASE TO ltuser_accountgt
• CREATE DATABASE ltdatabase_namegt
• If you are running your SQL Server using a system
  administrators account, you are a system
  administrator of the Server because we use
  windows authentication for the Server.

3
(No Transcript)
4
After pressing F5 to refresh
5
USE

• The USE command selects a database to use for
  future processing.

6
(No Transcript)
7
CREATE TABLE

• CREATE TABLE table_name (    
• ltColumn Definition1gt ltData Type1gt
• ltColumn Definition2gt ltData Type2gt
• )

8
TABLE CREATION I

• One of the first steps in creating a database is
  to create the tables that will store
  organizations data.
• In order to create a table, four pieces of
  information must be determined
• The table name
• The column (field) names
• Column data types and
• Column sizes

9
NAMING TABLES AND COLUMNS

• Table and Column names should be meaningful and
  reflect the nature of the data that is to be
  stored.
• If the data stored is about the products that a
  firm sells, then the table should probably be
  named product!
• If products are identified by a string of eight
  characters, then the column that stores the
  product information data should be named
  product_number, or product_code.

10
CREATING A TABLE

• Create a simple table that stores five items of
  information about employees for an organization.
• The table is named employee and stores
  information about each employees social security
  number, last name, first name, date hired, and
  annual salary.

11
TABLE CREATION II

• CREATE TABLE employee (
• emp_ssn CHAR(9),
• emp_last_name VARCHAR(25),
• emp_first_name VARCHAR(25),
• emp_date_of_birth DATETIME,
• emp_salary MONEY
• )
• The table named employee, is specified along
  with five data columns.
• Each column has a name that is unique within the
  table and is specified to store a specific type
  of data.

12
(No Transcript)
13
Picking a Data type

• The data type chosen for a column determines the
  nature of the data that can be stored in the
  column.
• This is termed the Domain of valid column values.

14
Data type

• Transact-SQL provides numerous data types
  categorized as follows
• Numeric data types.
• String or Character data types this includes
  binary data (bit strings).
• Date and/or time data types.
• Derived data types.
• Special, new data.

15
Integer Numeric Types

• BIGINT Integer values requiring 8 bytes of
  storage.
• INTEGER or INT Integer values requiring 4 bytes
  of storage. INT is short for INTEGER.
• SMALLINT Integer values requiring 2 bytes of
  storage.
• TINYINT Integer values (non-negative only) that
  can be stored in 1 byte.

16
Real Numeric Data Types

• DECIMAL(p,s) or DEC Fixed point values with
  precision p that specifies the total number of
  digits, with assumed decimal point of scale s to
  the right of the decimal. Memory requirements
  vary from 2 to 17 bytes depending on the value of
  p. The specification of a value for s is
  optional.
• NUMERIC(p,s) Same as DECIMAL.
• REAL Floating point values. Positive values
  stored range from 2.23E-308 to 1.79E308.
  Negative values stored range from -2.23E-308 to
  -1.79E308.
• FLOAT(p) Floating point values like REAL where
  the parameter p specifies the precision. When p
  lt 25, the number is stored using 4 bytes and is
  single-precision. When p gt 25, the number is
  stored using 8 bytes and is double-precision.

17
Monetary Numeric Data Types

• MONEY Monetary values requiring 8 bytes of
  storage. This corresponds to 8 byte DECIMAL
  values rounded to four digits to the right of the
  decimal point.
• SMALLMONEY Monetary values requiring 4 bytes of
  storage.

18
Character (String) Data Types

• CHAR(n) or CHARACTER(n) Fixed-length string
  values where n is the number of characters in the
  string. The parameter n cannot be greater than
  8,000. The default length when n is omitted is 1
  character.
• NCHAR(n) Fixed-length string values storing
  Unicode character data. Each NCHAR character
  requires 2 bytes of storage whereas CHAR data
  requires 1 byte of storage per character. The
  parameter n cannot be greater than 4,000.
• VARCHAR(n) Variable-length string values
  where n is the maximum number of characters in
  the string. The parameter n cannot be greater
  than 8,000. Also termed CHAR VARYING and
  CHARACTER VARYING.

19
Character (String) Data Types Contd.

• NVARCHAR(n) Variable-length string values
  storing Unicode characters. This data type has
  the same characteristics of storage as NCHAR.
• TEXT(n) Fixed-length string up to
  2,147,483,647 characters.
• NTEXT(n) Stores large string values of
  varying lengths with a maximum length of
  1073,741,823 characters. NTEXT data characters
  are stored using the Unicode scheme of 2 bytes
  per character while TEXT data requires 1 byte of
  storage per character.

20
Binary (and Bit) String Data Types

• BINARY(n) Fixed-length bit string of exactly
  n bytes where the maximum value of the parameter
  n is 8000.
• VARBINARY(n) Variable-length bit string where
  n is the maximum number of bytes stored. The
  maximum value of the parameter n is 8000.
• IMAGE(n) Fixed-length bit strings of nearly
  unlimited values.
• BIT Stores Boolean data. Each value requires
  only a single bit of storage. The values stored
  are limited to TRUE, FALSE, or NULL.

21
Date and Time Data Types

• DATETIME Date and time data requiring 4 bytes
  of storage for the date and 4 bytes of storage
  for the time.
• SMALLDATETIME Date and time data requiring 2
  bytes of storage for the date and 2 bytes of
  storage for the time.

22
Derived Data Types

• SYSNAME Columns defined as NVARCHAR(128) that
  store the names of database objects in the system
  catalog.
• TIMESTAMP Columns defined as VARBINARY(8) or
  BINARY(8) that stores the current value for each
  database that is used to timestamp rows that are
  inserted or updated that contain a TIMESTAMP
  column.

23
TABLE CREATION II (REVISITED)

• CREATE TABLE employee (
• emp_ssn CHAR(9),
• emp_last_name VARCHAR(25),
• emp_first_name VARCHAR(25),
• emp_date_of_birth DATETIME,
• emp_salary MONEY
• )

24
INSERT

• INSERT INTO ltcol1, col2, gt values ltval1, val2, gt

25
(No Transcript)
26
(No Transcript)
27

• NOT NULL Constraint
• A NOT NULL constraint means that a data row must
  have a value for the column specified as NOT
  NULL.
• A fairly standard practice is to assign each
  constraint a unique constraint name.
• It is common to use either a prefix or suffix to
  denote the type of constraint. In this text, we
  use prefixes. The prefix for a NOT NULL
  constraint is nn.
• Example
• emp_last_name VARCHAR(25)
• CONSTRAINT nn_emp_last_name NOT NULL

28

• PRIMARY KEY Constraint
• Each table must normally contain a column or set
  of columns that uniquely identifies rows of data
  that are stored in the table. This column or set
  of columns is referred to as the primary key.
• Example
• emp_ssn CHAR(9)
• CONSTRAINT pk_employee PRIMARY KEY,
• .
• .

29

• Composite PRIMARY KEY Constraint
• If a table requires two or more columns in order
  to identify each row, the primary key is termed a
  composite primary key.
• Example
• CREATE TABLE assignment (
• work_emp_ssn CHAR(9),
• work_pro_number SMALLINT,
• work_hours DECIMAL (5,1),
• CONSTRAINT pk_assignment
• PRIMARY KEY ( work_emp_ssn, work_pro_number
  ),
• .
• .

30

• CHECK Constraint
• Sometimes the data values stored in a specific
  column must fall within some acceptable range of
  values.
• A CHECK constraint is used to enforce this data
  limit.
• emp_salary MONEY
• CONSTRAINT ck_emp_salary
• CHECK (emp_salary lt 85000),
•  

31

• UNIQUE Constraint
• Sometimes it is necessary to enforce uniqueness
  for a column value that is not a primary key
  column.
• The UNIQUE constraint can be used to enforce this
  rule.
• Assume that each parking space for the
  organization is numbered and that no two
  employees can be assigned the same parking space.
• emp_parking_space INT
• CONSTRAINT un_emp_parking_space
  UNIQUE,

32
(No Transcript)
33

• Altering a column for an existing Table
• Modifying existing tables to either add new
  columns or alter existing columns can be
  accomplished with the ALTER TABLE statement.
• The current data type of the emp_parking_space
  column is INT (INTEGER). Due to growth in the
  availability of employee parking spaces, you have
  determined that it is necessary to modify the
  definition of this column to specify a BIGINT
  data type.

34

• ALTER TABLE Command
• The ALTER TABLE command can be used to modify the
  emp_parking_space column to specify the BIGINT
  data type.
• ALTER TABLE employee DROP CONSTRAINT
  un_emp_parking_space
• ALTER TABLE employee ALTER COLUMN
  emp_parking_space BIGINT
• ALTER TABLE employee
• ADD CONSTRAINT un_emp_parking_space
• UNIQUE (emp_parking_space)

35

• Adding New Columns
• The ALTER TABLE command can be used to add a new
  column to the employee table.
• Suppose that an organization recognizes the need
  to track the gender of employees in order to meet
  a governmental reporting requirement, an
  emp_gender column can be added to the employee
  table.
• ALTER TABLE employee ADD
• emp_gender CHAR(1)

36

• Relating Tables Identifying Foreign Keys
• Normally, in a relational database, data rows in
  one table are related to data rows in other
  tables.
• The dependent table will store information about
  dependent within the organization.
• Each dependent is identified by a unique
  composite (primary key).

37

• Relating Tables- Identifying Foreign Keys Contd.
• CREATE TABLE DEPENDENT (
• DEP_EMP_SSN CHAR(9),
• DEP_NAME VARCHAR(50),
• DEP_GENDER CHAR(1),
• DEP_DATE_OF_BIRTH DATETIME NULL,
• DEP_RELATIONSHIP VARCHAR(10) NULL,
• CONSTRAINT PK_DEPENDENT PRIMARY KEY
  (DEP_EMP_SSN, DEP_NAME),
• CONSTRAINT FK_DEP_EMP_SSN
• FOREIGN KEY (DEP_EMP_SSN) REFERENCES EMPLOYEE
• ON DELETE CASCADE
• )

38
IDEF1X

39
Execute SQL script

• Make a file which has .sql extention.
• e.g. mySql.sql
• From the Query Analyzer, FilegtOpen
• Choose the sql file you want to execute.
• Run you script.
• This is how your TA is going to grade your
  homework from the sql script files submitted by
  you. So for the homework, you are going to
  submitting a zip file containing all your sql
  script files.

40
(No Transcript)
41
(No Transcript)