Creating Demonstration Tables

3.4 Creating Demonstration Tables

The case studies are based on the GTW_EMP, GTW_DEPT, and GTW_SALGRADE tables and the stored procedures InsertDept and GetDept. If the demonstration tables and stored procedures have not been created in the SQL Server database, use the bldmsql.sql script to create them.

Enter the following:

> isql -USCOTT -PTIGER2 -ibldmsql.sql

The script creates the demonstration tables and stored procedures in the SQL Server database accordingly:

CREATE TABLE GTW_EMP (
EMPNO      SMALLINT NOT NULL 
ENAME      VARCHAR(10),
JOB        VARCHAR(9),
MGR        SMALLINT,
HIREDATE   DATETIME,
SAL        NUMERIC(7,2),
COMM       NUMERIC(7,2),
DEPTNO     SMALLINT)
go

CREATE TABLE GTW_DEPT (
DEPTNO     SMALLINT NOT NULL,
DNAME      VARCHAR(14),
LOC        VARCHAR(13)) 
go

CREATE TABLE GTW_SALGRADE (
GRADE      MONEY,
LOSAL      NUMERIC(9,4),
HISAL      NUMERIC(9,4))
go

DROP PROCEDURE InsertDept
go

CREATE PROCEDURE InsertDept (@dno INTEGER,
   @dname VARCHAR(14), @loc VARCHAR(13))
AS INSERT INTO GTW_DEPT VALUES (@dno, @dname, @loc)
go

DROP PROCEDURE GetDept
go

CREATE PROCEDURE GetDept (@dno INTEGER, @dname VARCHAR(14) OUTPUT)
AS SELECT @dname=DNAME FROM GTW_DEPT WHERE DEPTNO=@dno
go

Demonstration Table Definitions
Demonstration table definitions.
Demonstration Table Contents
Demonstration table contents.

Parent topic: Case Studies

3.4.1 Demonstration Table Definitions

Demonstration table definitions.

The following table definitions use information retrieved by the SQL*PLUS DESCRIBE command:

GTW_EMP

Name                            Null?    Type
------------------------------- -------- ----
EMPNO                           NOT NULL NUMBER(5)
ENAME                                    VARCHAR2(10)
JOB                                      VARCHAR2(9)
MGR                                      NUMBER(5)
HIREDATE                                 DATE
SAL                                      NUMBER(7,2)
COMM                                     NUMBER(7,2)
DEPTNO                                   NUMBER(5)

GTW_DEPT

Name                            Null?    Type
------------------------------- -------- ----
DEPTNO                          NOT NULL NUMBER(5)
DNAME                                    VARCHAR2(14)
LOC                                      VARCHAR2(13)

GTW_SALGRADE

Name                            Null?    Type
------------------------------- -------- ----
GRADE                                    NUMBER(19,4)
LOSAL                                    NUMBER(9,4)
HISAL                                    NUMBER(9,4)

Parent topic: Creating Demonstration Tables

3.4.2 Demonstration Table Contents

Demonstration table contents.

The contents of the SQL Server tables are:

GTW_EMP

EMPNO ENAME   JOB         MGR   HIREDATE   SAL   COMM   DEPTNO
----- -----   ---         ---   --------   ---   ----   ------
7369  SMITH   CLERK       7902  17-DEC-80   800             20
7499  ALLEN   SALESMAN    7698  20-FEB-81  1600   300       30
7521  WARD    SALESMAN    7698  22-FEB-81  1250   500       30
7566  JONES   MANAGER     7839  02-APR-81  2975             20
7654  MARTIN  SALESMAN    7698  28-SEP-81  1250  1400       30
7698  BLAKE   MANAGER     7839  01-MAY-81  2850             30
7782  CLARK   MANAGER     7839  09-JUN-81  2450             10
7788  SCOTT   ANALYST     7566  09-DEC-82  3000             20
7839  KING    PRESIDENT         17-NOV-81  5000             10
7844  TURNER  SALESMAN    7698  08-SEP-81  1500     0       30
7876  ADAMS   CLERK       7788  12-JAN-83  1100             20
7900  JAMES   CLERK       7698  03-DEC-81   950             30
7902  FORD    ANALYST     7566  03-DEC-81  3000             20
7934  MILLER  CLERK       7782  23-JAN-82  1300             10

GTW_DEPT

DEPTNO DNAME          LOC 
----- -------------- --------
   10 ACCOUNTING     NEW YORK
   20 RESEARCH       DALLAS
   30 SALES          CHICAGO
   40 OPERATIONS     BOSTON

GTW_SALGRADE

GRADE      LOSAL      HISAL
------     ------     -----
    1        700      1200
    2       1201      1400
    3       1401      2000
    4       2001      3000
    5       3001      9999

Parent topic: Creating Demonstration Tables