???????2??CREATE_INDEXES.sql


/* ???????? */
PRINT '???????????'
GO
USE TestDB
GO
IF NOT EXISTS ( SELECT  1
                FROM    SYS.INDEXES
                WHERE   NAME = 'IX_EMPLOYEE_LASTNAME' )
    CREATE INDEX IX_EMPLOYEE_LASTNAME ON DBO.EMPLOYEE(LASTNAME?? FIRSTNAME)
GO
IF NOT EXISTS ( SELECT  1
                FROM    SYS.INDEXES
                WHERE   NAME = 'IX_TIMECARD_EMPLOYEEID' )
    CREATE INDEX IX_TIMECARD_EMPLOYEEID ON DBO.TIMECARD(EMPLOYEEID)
GO


???????3??CREATE_PROCEDURES.sql


/* ?????洢???? */
PRINT '????????洢????'
GO
USE TestDB
GO
IF OBJECT_ID('GET_EMPLOYEE_TIMECARDS') IS NOT NULL
    DROP PROCEDURE DBO.GET_EMPLOYEE_TIMECARDS
GO
CREATE PROCEDURE DBO.GET_EMPLOYEE_TIMECARDS @EMPLOYEEID INT
AS
    SET NOCOUNT ON

    SELECT  *
    FROM    DBO.EMPLOYEE E
            JOIN DBO.TIMECARD T ON E.EMPLOYEEID = T.EMPLOYEEID
    WHERE   E.EMPLOYEEID = @EMPLOYEEID
    ORDER BY DATEWORKED

GO
 


???????4??CREATE_TABLES.sql


/* ????????? */
PRINT '???????????? '
GO
USE TestDB
GO
IF OBJECT_ID('EMPLOYEE') IS NOT NULL
    DROP TABLE DBO.EMPLOYEE
GO
CREATE TABLE DBO.EMPLOYEE
    (
      EMPLOYEEID INT IDENTITY(1?? 1)
                     NOT NULL
                     PRIMARY KEY ??
      FIRSTNAME VARCHAR(50) ??
      LASTNAME VARCHAR(50)
    )
GO

IF OBJECT_ID('TIMECARD') IS NOT NULL
    DROP TABLE DBO.TIMECARD
GO
CREATE TABLE DBO.TIMECARD
    (
      TIMECARDID INT IDENTITY(1?? 1)
                     NOT NULL
                     PRIMARY KEY ??
      EMPLOYEEID INT NOT NULL ??
      HOURSWORKED TINYINT NOT NULL ??
      HOURLYRATE MONEY NOT NULL ??
      DATEWORKED DATETIME NOT NULL
    )
GO

DECLARE @TOTAL_TABLES INT
SET @TOTAL_TABLES = 2