Step 1: Open SQL Server Management Studio (SSMS)

1. Launch SQL Server Management Studio.
2. Connect to your SQL Server instance.

Step 2: Select the Database

1. In the Object Explorer, expand the "Databases" node.

2. Select the database where you want to create the DailyExpenditure table. If you don't have a database, you can create one using the following SQL command:

   sql
   1CREATE DATABASE YourDatabaseName;
   2GO
   3USE YourDatabaseName;
   4GO

Step 3: Create the DailyExpenditure Table

1. Open a new query window by clicking on "New Query".

2. Copy and paste the following SQL code into the query window:

   sql
   1CREATE TABLE DailyExpenditure
   2(
   3    DailyExpenditureId Int Identity Constraint Pk_DailyExpenditure_DailyExpendureId Primary Key,
   4    PreviousBalance Numeric(18,2),
   5    OpeningBalance Numeric(18,2),
   6    CounterBalance Numeric(18,2),
   7    CreatedDate DateTime Constraint df_DailyExpenditure_CreatedDate Default(getDate()),
   8    CreatedBy Varchar(20)
   9);

3. Execute the query by clicking on the "Execute" button or pressing F5.

Step 4: Verify Table Creation

To verify that the table has been created successfully, you can run the following query:

sql
1SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DailyExpenditure';

If the table exists, it will return information about the DailyExpenditure table.

Step 5: Insert Data into the Table

You can insert data into the DailyExpenditure table using the INSERT INTO statement. Here’s an example:

sql
1INSERT INTO DailyExpenditure (PreviousBalance, OpeningBalance, CounterBalance, CreatedBy)
2VALUES (1000.00, 1200.00, 800.00, 'JohnDoe');
3
4INSERT INTO DailyExpenditure (PreviousBalance, OpeningBalance, CounterBalance, CreatedBy)
5VALUES (800.00, 900.00, 700.00, 'JaneSmith');

Explanation of the Insert Statement

• You do not need to specify the DailyExpenditureId and CreatedDate columns because:
  • DailyExpenditureId is an IDENTITY column, which means SQL Server will automatically generate a unique value for it.
  • CreatedDate has a default value of the current date and time, so it will automatically be populated.

Step 6: Query Data from the Table

To retrieve data from the DailyExpenditure table, you can use the SELECT statement. Here’s an example:

sql
1SELECT * FROM DailyExpenditure;

This will return all records from the DailyExpenditure table.

Step 7: Update Data in the Table

If you need to update a record, you can use the UPDATE statement. For example, if you want to update the CounterBalance for the record with DailyExpenditureId of 1:

sql
1UPDATE DailyExpenditure
2SET CounterBalance = 750.00
3WHERE DailyExpenditureId = 1;

Step 8: Delete Data from the Table

To delete a record from the DailyExpenditure table, you can use the DELETE statement. For example, to delete the record with DailyExpenditureId of 2:

sql
1DELETE FROM DailyExpenditure
2WHERE DailyExpenditureId = 2;

Step 9: Additional Queries

You can also perform more complex queries. For example, to find all records where the OpeningBalance is greater than 1000:

sql
1SELECT * FROM DailyExpenditure
2WHERE OpeningBalance > 1000;

Step 10: Drop the Table (if needed)

If you ever need to remove the DailyExpenditure table, you can use the DROP TABLE statement:

sql
1DROP TABLE DailyExpenditure;

Table Schema

sql
1CREATE TABLE DailyExpenditure
2(
3    DailyExpenditureId Int Identity Constraint Pk_DailyExpenditure_DailyExpendureId Primary Key,
4    PreviousBalance Numeric(18,2),
5    OpeningBalance Numeric(18,2),
6    CounterBalance Numeric(18,2),
7    CreatedDate DateTime Constraint df_DailyExpenditure_CreatedDate Default(getDate()),
8    CreatedBy Varchar(20)
9);

Explanation of Each Component

1. Table Name: DailyExpenditure

   • This is the name of the table. It indicates that the table is intended to store records related to daily expenditures.

2. Column Definitions:

   • DailyExpenditureId Int Identity

     • Data Type: Int
       • This column will store integer values. It is commonly used for unique identifiers.
     • Identity:
       • This property indicates that the value for this column will be automatically generated by SQL Server. The first value will start at 1, and it will increment by 1 for each new record. This is useful for ensuring that each record has a unique identifier.
     • Constraint: Constraint Pk_DailyExpenditure_DailyExpendureId Primary Key
       • This defines the column as the primary key of the table. A primary key uniquely identifies each record in the table and ensures that no two records can have the same value in this column. It also cannot contain null values.

   • PreviousBalance Numeric(18,2)

     • Data Type: Numeric(18,2)
       • This column can store numeric values with a total of 18 digits, of which 2 digits can be after the decimal point. This is suitable for storing monetary values, such as the previous balance before the current expenditure.
     • Constraints:
       • There are no specific constraints applied to this column in the provided schema, but you could add constraints like NOT NULL if you want to ensure that a value must always be provided.

   • OpeningBalance Numeric(18,2)

     • Data Type: Numeric(18,2)
       • Similar to PreviousBalance, this column will store the opening balance for the day, formatted to handle monetary values.
     • Constraints:
       • Like PreviousBalance, there are no specific constraints applied, but you could add constraints as needed.

   • CounterBalance Numeric(18,2)

     • Data Type: Numeric(18,2)
       • This column is intended to store the counter balance, which could represent the remaining balance after expenditures have been accounted for.
     • Constraints:
       • Again, there are no specific constraints applied, but you could add constraints as needed.

   • CreatedDate DateTime

     • Data Type: DateTime
       • This column will store the date and time when the record was created. The DateTime data type is suitable for storing both date and time information.
     • Constraint: Constraint df_DailyExpenditure_CreatedDate Default(getDate())
       • This sets a default value for the CreatedDate column to the current date and time when a new record is inserted. The getDate() function retrieves the current date and time from the server. This constraint ensures that if no value is provided for CreatedDate during an insert operation, it will automatically be populated with the current date and time.

   • CreatedBy Varchar(20)

     • Data Type: Varchar(20)
       • This column will store variable-length character strings with a maximum length of 20 characters. It is typically used to store names or identifiers of users.
     • Constraints:
       • There are no specific constraints applied to this column in the provided schema, but you could add constraints like NOT NULL if you want to ensure that a value must always be provided.