SQL Queries - Assignment-7

 SQL Assignment -  07 

---------------------------------

Creat the following tables. These tables are to be created at the top of 5 earlier tables.

Table Name : amenitiesMaster

About this table:

The table amenitiesMaster holds the data related to all the amenities in the apartment society. The amenities are those facilities which are shared/utilized/enjoyed by all the residents of the society. 

Field Name	Field Type	Constraints (if any)	Description
amenityID	smallint	PRIMARY KEY	This is a unique and not null field. This holds all the unique amenity ID.
amenityName	varchar(40)	NOT NULL	This contains the amenity names. For example : Club House, Party Hall etc.
oneTimeBookingCharge	float	NA	This contains one time booking charge of the amenity. For example :
dtCreated	DateTime	DEFAULT (GETDATE())	Date of creation of amenity. This will contain the details when the amenity item was created
isActive	char(1)	Check if he isActive in 'Y' or 'N'	isActive field show that where the amenity item is active any more or not. There is a possibilty that the item is not in use after months/years.
Comments	varchar(200)	NA	Holds any additional comments.

Table Name : amenitiesBookings

About this table:

The table amenitiesBooking holds the details related to bookings done for amenities. Residents may need bookings for the amenities like - Clubhouse, Party Hall, Open Ground etc. This table will store all these details. 

Field Name	Field Type	Constraints (if any)	Description
amenityID	smallint	NOT NULL	Contains the amenityID.
residentID	smallint	NOT NULL	Contains residentID. The ID of the resident who is trying to book the amenity.
bookingStartDate	DateTime	NOT NULL	Start date of amenity booking
bookingEndDate	DateTime	NOT NULL	End Date of amenity
bookingDate	DateTime	NA	Date of which the booking being done.
totalAmount	Float	NA	Contains the total amount calculated for the amenitiy booked
bookedBy	Varchar(50)	NA	Name of the person doing booking.

Once the above table created, Let's fill the above table by downloading the below excel files from google drive.

The process of "how to bulk insert has been explained in the previous video". 

Link of the Data Set Excel File :

https://docs.google.com/spreadsheets/d/1oL5EBdKdWuxN_gglnWlDysgSME30l47_/edit?usp=sharing&ouid=112715479848878601933&rtpof=true&sd=true

Video for how to do bulk insert from Excel: