CodeWithAbby

 ASSIGNMENT 08 ---------------------------- Interview Questions Based On IDENTITY in SQL Server  What is the purpose of the IDENTITY property in SQL Server? How do you define an IDENTITY column in SQL Server? Can you insert values manually into an IDENTITY column? If yes, how? How would you reset the identity seed of a table after deleting all rows from it? What is the difference between an IDENTITY column and a SEQUENCE in SQL Server? What is SCOPE_IDENTITY() and how does it differ from @@IDENTITY and IDENT_CURRENT()? How can you retrieve the last identity value generated in the current session or scope? Explain how DBCC CHECKIDENT works. Can an IDENTITY column have a negative seed or increment? What happens if you try to insert a record after the identity value reaches its maximum limit? How would you handle gaps in an IDENTITY column caused by transaction rollbacks or deletions? If you delete a row from a table with an IDENTITY column, will SQL Server reuse that identity va...

 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. oneTim...

                                                           Assignment - 06 ------------------------- Write SQL Queries for the following (based on the above data). You can use SQL Server Management Studio to run your queries: How many flats are there whose areaSqft is less than the 1200. How may floors exists in flatMaster table Can you figure out how many 2 BHK flats are there in the apartment? How may 3 BHK flats are there in block B? Display the smallest size of 3 BHK flat  Find , how many residents are there who have non gmail email account? How many maids are there who actually know cooking How many male maids are there who can also do cooking ? How many maids are there who have got "Good Behaviour" and comments by residents? Who is/are the highest ratings maids Can you figure out the each maid's years of...

  Assignment -2 ------------------ Below are huge data sets for all the tables. Please use these data to fill all the tables. These will be used in all our future assignments/queries. Table Name : flatMaster flatNo floorNo block areasqft bhk comments A001 0 A 1250 3 NA A002 0 A 1170 2 NA A003 0 A 1400 3 NA A004 0 A 1150 2 NA A005 0 A 1420 3 NA A006 0 A 1070 2 NA A007 0 A 1200 2 NA A008 0 A 1200 2 NA A009 0 A 1350 3 NA...