Introduction

A water distribution company is a community water provider. It uses and installs water meters at customers residences such as houses and appartments, etc.

This is SQL code for a simple water distribution company. This code is created and executed in Microsoft SQL Server. We create tables for water meters, customers, and water bills. To make it slightly interesting, we will create stored procedures to select records.

Practical Learning: Preparing a Database

1. Start SQL Server Management Studio. In the Server Explorer, right-click the name of the server and click the option to create a new query (if you are using Microsoft Visual Studio, first create a simple Windows Forms application. Then, to create a new database, on the main menu of Microsoft Visual Studio, click View and click Server Explorer; in the Server Explorer, right-click Data Connections and click Create New SQL Server Database...; in the Server Name combo box, select your server or type (local); set the database name as StellarWaterPoint; click OK; in the Server Explorer, right-click the StellarWaterPoint connection and click New Query)
2. Type the following code to create the tables and some stored procedures:

   CREATE TABLE WaterMeters
   (
   	WaterMeterId int identity(1, 1),
   	MeterNumber  nvarchar(10) not null,
   	Make         nvarchar(25) null,
   	Model        nvarchar(15) not null,
   	MeterSize    nvarchar(15),
   	CONSTRAINT   PK_WaterMeters PRIMARY KEY(WaterMeterId)
   );
   GO
   
   CREATE TABLE Customers
   (
   	CustomerId    int identity(1, 1),
   	AccountNumber nvarchar(15) not null,
   	MeterNumber   nvarchar(10),
   	FirstName     nvarchar(18),
   	LastName      nvarchar(18) not null,
   	[Address]     nvarchar(150),
   	City          nvarchar(25),
   	County        nvarchar(35),
   	[State]       nvarchar(35) not null,
   	ZIPCode       nvarchar(12),
   	CONSTRAINT    PK_Customers PRIMARY KEY(CustomerId)
   );
   GO
   
   CREATE TABLE WaterBills
   (
   	WaterBillId           int identity(1, 1),
   	AccountNumber         nvarchar(15) not null,
   	InvoiceNumber         int          not null,
   	MeterReadingStartDate nvarchar(50) not null,
   	MeterReadingEndDate   nvarchar(50) not null,
   	BillingDays           int          not null,
   	CounterReadingStart   nvarchar(15),
   	CounterReadingEnd     nvarchar(15),
   	Consumption           nvarchar(15),
   	TotalGallons          nvarchar(15),
   	FirstTierConsumption  nvarchar(15),
   	SecondTierConsumption nvarchar(15),
   	LastTierConsumption   nvarchar(15),
   	WaterCharges          nvarchar(15),
   	SewerCharges          nvarchar(15),
   	EnvironmentCharges    nvarchar(15),
   	TotalCharges          nvarchar(15),
   	LocalTaxes            nvarchar(15),
   	StateTaxes            nvarchar(15),
   	PaymentDueDate        nvarchar(50),
   	AmountDue             nvarchar(15),
   	LatePaymentDueDate    nvarchar(50),
   	LateAmountDue         nvarchar(15),
   	CONSTRAINT            PK_WaterBills PRIMARY KEY(WaterBillId)
   );
   GO
   -----------------------------------------------------
   CREATE PROCEDURE LocateBill @InvNbr nvarchar(15)
   AS
       BEGIN
           SELECT InvoiceNumber,
                  AccountNumber,
                  MeterReadingStartDate,
                  MeterReadingEndDate,
                  BillingDays,
                  CounterReadingStart,
                  CounterReadingEnd,
                  Consumption,
                  TotalGallons,
                  FirstTierConsumption,
                  SecondTierConsumption,
                  LastTierConsumption,
                  WaterCharges,
                  SewerCharges,
                  EnvironmentCharges,
                  TotalCharges,
                  LocalTaxes,
                  StateTaxes,
                  PaymentDueDate,
                  AmountDue,
                  LatePaymentDueDate,
                  LateAmountDue
           FROM   WaterBills
           WHERE  InvoiceNumber = @InvNbr
       END;
   GO
   
   CREATE PROCEDURE GetWaterMonitor @MtrNbr nvarchar(15)
   AS
       BEGIN
           SELECT MeterNumber,
                  Make,
                  Model,
                  MeterSize
           FROM   WaterMeters
           WHERE  MeterNumber = @MtrNbr
       END;
   GO
   
   CREATE PROCEDURE IdentifyClient @AcntNbr nvarchar(15)
   AS
       BEGIN
           SELECT AccountNumber,
                  MeterNumber,
                  FirstName,
                  LastName,
                  [Address],
                  City,
                  County,
                  [State],
                  ZIPCode
           FROM Customers
           WHERE AccountNumber = @AcntNbr
       END;
   GO
   -------------------------------------------------------------------------------
   INSERT INTO WaterMeters(MeterNumber, Make, Model, MeterSize)
   VALUES(N'392-44-572', N'Constance Technologies', N'TG-4822', N'5/8 Inches'),
   	  (N'938-75-869', N'Stanford Trend',         N'266G',    N'1 1/2 Inches'),
   	  (N'799-28-461', N'Constance Technologies', N'BD-7000', N'3/4 Inches'),
   	  (N'207-94-835', N'Constance Technologies', N'TG-6220', N'5/8 Inches'),
   	  (N'592-84-957', N'Standard Trend',         N'428T',    N'3/4 Inches'),
   	  (N'374-06-284', N'Raynes Energica',        N'i2022',   N'3/4 Inches');
   GO
   
   INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, County, [State], ZIPCode)
   VALUES(N'9279-570-8394', N'799-28-461', N'Thomas', N'Stones',  N'10252 Broward Ave #D4', N'Frederick', N'Frederick', N'MD', N'21703');
   GO
   
   INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, [State], ZIPCode)
   VALUES(N'4820-375-2842', N'392-44-572', N'Akhil', N'Koumari', N'748 Red Hills Rd', N'Roanoke', N'VA', N'24012');
   GO
   
   INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, County, [State], ZIPCode)
   VALUES(N'7518-302-6895', N'207-94-835', N'Grace', N'Brenner', N'4299 Peachtree Court', N'Rockville', N'Montgomery', N'MD', N'20853');
   GO
   
   INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, [State], ZIPCode)
   VALUES(N'2038-413-9680', N'938-75-869', N'Amidou', N'Gomah', N'2075 Rose Hills Ave', N'Washington', N'DC', N'20004');
   GO
   
   INSERT INTO Customers(AccountNumber, MeterNumber, FirstName, LastName, [Address], City, County, [State], ZIPCode)
   VALUES(N'5938-074-5293', N'592-84-957', N'Marie', N'Rath', N'582G Dunhill Ave', N'Lanham', N'Prince Georges', N'MD', N'20706');
   GO

3. Right-click inside the document and click Execute
4. Close the Query window

Home	Copyright © 2003-2023, FunctionX	Saturday 24 March 2023