Home

Georgetown Cleaning Services

 

Introduction

In this exercise, we will create a database application that can be used to process customers orders for a dry cleaning business. We will create the database in Microsoft SQL Server. This database will have only one table (for now).

Practical Learning Practical Learning: Creating the Database 

  1. Start the SQL Query Analyzer
  2. On the main menu, click Query -> Change Database...
    If you see a database named gcs, click OK and continue in the next section.
    If you don't see a database named gcs1, type the following and execute it (F5) to create a new database named gcs and a table named CleaningOrders
     
    -- =============================================
    -- Database: gcs
    -- Name:     Georgetown Cleaning Services
    -- =============================================
    IF EXISTS (SELECT * 
    	   FROM   master..sysdatabases 
    	   WHERE  name = N'gcs')
    	DROP DATABASE gcs
    GO
    
    CREATE DATABASE gcs
    GO
    
    -- =============================================
    -- Database: Georgetown Cleaning Services
    -- Table:    CleaningOrders
    -- =============================================
    USE gcs
    GO
    
    IF EXISTS(SELECT name 
    	  FROM 	 sysobjects 
    	  WHERE  name = N'CleaningOrders' 
    	  AND 	 type = 'U')
        DROP TABLE CleaningOrders
    GO
    
    CREATE TABLE CleaningOrders (
    	CleaningOrderID int IDENTITY (1001, 1) NOT NULL,
    	CustomerName varchar(80) NOT NULL,
    	CustomerPhone varchar(40) NULL,
    	DateLeft SmallDateTime NOT NULL,
    	TimeLeft SmallDateTime NOT NULL,
    	DateExpected SmallDateTime NOT NULL,
    	TimeExpected SmallDateTime NOT NULL,
    	ShirtsUnitPrice Decimal(6,2) NULL DEFAULT (0.95),
    	ShirtsQuantity SmallInt NULL DEFAULT (0),
    	ShirtsSubTotal Decimal(6,2) NULL DEFAULT (0.00),
    	PantsUnitPrice Decimal(6,2) NULL DEFAULT (2.75),
    	PantsQuantity SmallInt NULL DEFAULT (0),
    	PantsSubTotal Decimal(6,2) NULL DEFAULT (0.00),
    	Item1Name varchar (50) NULL DEFAULT ('None'),
    	Item1UnitPrice Decimal(6,2) NULL DEFAULT (0.00),
    	Item1Quantity SmallInt NULL DEFAULT (0),
    	Item1SubTotal Decimal(6,2) NULL DEFAULT (0.00),
    	Item2Name varchar (50) NULL DEFAULT ('None'),
    	Item2UnitPrice Decimal(6,2) NULL DEFAULT (0.00),
    	Item2Quantity SmallInt NULL DEFAULT (0),
    	Item2SubTotal Decimal(6,2) NULL DEFAULT (0.00),
    	Item3Name varchar (50) NULL DEFAULT ('None'),
    	Item3UnitPrice Decimal(6,2) NULL DEFAULT (0.00),
    	Item3Quantity SmallInt NULL DEFAULT (0),
    	Item3SubTotal Decimal(6,2) NULL DEFAULT (0.00),
    	Item4Name varchar (50) NULL DEFAULT ('None'),
    	Item4UnitPrice Decimal(6,2) NULL DEFAULT (0.00),
    	Item4Quantity SmallInt NULL DEFAULT (0),
    	Item4SubTotal Decimal(6,2) NULL DEFAULT (0.00),
    	CleaningTotal Decimal(6,2) NULL DEFAULT (0.00),
    	TaxRate Decimal(6,2) NULL DEFAULT (5.75),
    	TaxAmount Decimal(6,2) NULL DEFAULT (0.00),
    	OrderTotal Decimal(6,2) NULL DEFAULT (0.00),
    	CONSTRAINT PK_CleaningOrders PRIMARY KEY  CLUSTERED 
    	(
    		CleaningOrderID
    	)) 
    GO
  3. Close the SQL Query Analyzer

Home Copyright © 2005-2012 FunctionX Next