161. How to implement one-to-one, one-to-many and many-to-many relationshipswhile designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Manyto-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

162. What are the basic functions for master, msdb, model, tempdb and resourcedatabases?

1. The master database holds information for all databases located on the SQL Serverinstance and is theglue that holds the engine together. Because SQL Server cannotstart without a functioning masterdatabase, you must administer this database withcare.

2. The msdb database stores information regarding database backups, SQL Agentinformation, DTS packages, SQL Server jobs, and some replication information suchas for log shipping.

3. The tempdb holds temporary objects such as global and local temporary tables andstored procedures.

4. The model is essentially a template database used in the creation of any new userdatabase created in the instance.

5. The resoure Database is a read-only database that contains all the system objectsthat are included with SQL Server. SQL Server system objects, such as sys.objects,are physically persisted in the Resource database, but they logically appear in thesys schema of every database. The Resource database does not contain user data oruser metadata.

163. What is Service Broker?Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.

164. Where SQL server user names and passwords are stored in SQL server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

165. What is Policy Management?

Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

166. What is Replication and Database Mirroring?

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

167. What are Sparse Columns?

A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

168. What does TOP Operator Do?

The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements.

169. What is CTE?

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

170. What is MERGE Statement?

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

171. What is Filtered Index?

Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

172. Which are new data types introduced in SQL SERVER 2008?

1. The GEOMETRY Type: The GEOMETRY data type is a system .NET commonlanguage runtime (CLR) data type in SQL Server. This type represents data in a twodimensional Euclidean coordinate system.2. The GEOGRAPHY Type: The GEOGRAPHY datatype’s functions are the same as withGEOMETRY. The difference between the two is that when you specify GEOGRAPHY,you are usually specifying points in terms of latitude and longitude.

3. New Date and Time Datatypes: SQL Server 2008 introduces four new datatypesrelated to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.1. DATE: The new DATE type just stores the date itself. It is based on theGregorian calendar and handles years from 1 to 9999.2. TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000through 23:59:59.9999999. The precision is allowed with this type. TIMEsupports seconds down to 100 nanoseconds. The n in TIME (n) defines thislevel of fractional second precision, from 0 to 7 digits of precision.3. The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zoneaware version of a datetime datatype. The name will appear less odd whenyou consider what it really is: a date + a time + a time-zone offset. The offsetis based on how far behind or ahead you are from Coordinated UniversalTime (UTC) time.4. The DATETIME2 Type: It is an extension of the datetime type in earlierversions of SQL Server. This new datatype has a date range covering datesfrom January 1 of year 1 through December 31 of year 9999. This is a definiteimprovement over the 1753 lower boundary of the datetime datatype.DATETIME2 not only includes the larger date range, but also has a timestampand the same fractional precision that TIME type provides173. What are the Advantages of using CTE?1. Using CTE improves the readability and makes maintenance of complex querieseasy.2. The query can be divided into separate, simple, logical building blocks which can bethen used to build more complex CTEs until final result set is generated.3. CTE can be defined in functions, stored procedures, triggers or even views.4. After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT,UPDATE or DELETE Data.

174. How would you handle error in SQL SERVER 2008?SQL Server now supports the use of TRY…CATCH con handling. TRY…CATCH lets us build error handling at the level we need, in the way w to, by setting a region where if any error occurs, it will break out of the region and head to an error handler.The basic structure is as follows:BEGIN TRY stmts..END TRY BEGIN CATCH stmts..END CATCH

SQL INTERVIEW QUESTIONS AND ANSWERS40 | P a g e

175. What is Aggregate Functions?Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.Following functions are aggregate functions.AVG, MIN CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX. VARP

176. What do you mean by Table Sample?TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

177. What is the difference between UNION and UNION ALL?1. UNION The UNION command is used to select related information from two tables,much like the JOIN command. However, when using the UNION command allselected columns need to be of the same data type. With UNION, only distinct valuesare selected.2. UNION ALL The UNION ALL command is equal to the UNION command, except thatUNION ALL selects all values.The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

178. What is B-Tree?The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:1. root node: A root node contains node pointers to branch nodes which can be onlyone.2. branch node: A branch node contains pointers to leaf nodes or other branch nodeswhich can be two or more.3. leaf nodes: A leaf node contains index items and orizantal pointers to other leafnodes which can be many.

179. What is a foreign key, and what is it used for?A foreign key is used to establish relationships among relations in the relational model. Technically, a foreign key is a column (or columns) appearing in one relation that is (are) the primary key of another table. Although there may be exceptions, the values in the foreign key columns usually must correspond to values existing in the set of primary key values. This correspondence requirement is created in a database using a referential integrity constraint on the foreign key.

180. What does it mean when we say that a relation is in Boyce-Codd Normal Form(BCNF)?A relation is in BCNF when every determinant in the relation is a candidate key. This means that any possible primary key can determine all other attributes in the relation. Attributes may not be determined by non-candidate key attributes or part of a composite candidate key. Thus it is said “I swear to construct my tables so that all nonkey columns are dependent on the key, the whole key and nothing but the key, so help me Codd!”

181. You have been given a set of tables with data and asked to create a new database to store them. When you examine the data values in the tables, what are you looking for?(1) Multivalued dependencies, (2) Functional dependencies, (3) Candidate keys, (4)Primary keys and (5) Foreign keys.

182. Explain the difference between attributes and identifiers.Entities have attributes. Attributes are properties that describe the entity’s characteristics. Entity instances have identifiers. Identifiers are attributes that name, or identify, entity instances.

183. Name and describe three types of binary relationships.1:1 — a single entity instance of one type is related to a single-entity instance of another type.1:N — a single entity instance of one type is related to many-entity instances of another type.M:N — many-entity instances of one type relate to many-entity instances of another type.

184. What are stored procedures, and how do they differ from triggers?A stored procedure is a program that is stored within the database and is compiled when used. They can receive input parameters and they can return results. Unlike triggers, their scope is database-wide; they can be used by any process that has permission to use the database stored procedure.

185. What are the advantages of using stored procedures?The advantages of stored procedures are (1) greater security, (2) decreased network traffic, (3) the fact that SQL can be optimized and (4) code sharing which leads to less work, standardized processing, and specialization among developers.

186. What is the relationship of ODBC, OLE DB, and ADO?Developed first, the ODBC standard is for relational databases; while the OLE DB standard provides functionality for both relational and other databases. Finally, ADO was developed to provide easier access to OLE DB data for the non-object-oriented programmer.

187. Explain the differences between structured data and unstructured data.Structured data are facts concerning objects and events. The most important structured data are numeric, character, and dates. Structured data are stored in tabular form. Unstructured data are multimedia data such as documents, photographs, maps, images, sound, and video clips. Unstructured data are most commonly found on Web servers and Web-enabled databases.

188.What are dimension tables and definition of Fact tables?These two questions are most commonly asked database interview questions. Fact tables are mainly central tables that are an integral part of data warehousing and dimension tables are used for describing the attributes of the fact tables. Both of these tables are important and play an important role in maintaining the database management system.189.What is Data Warehouse?A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process.Subject-Oriented: A data warehouse can be used to analyze a particular subject area. For example, “sales” can be a particular subject.Integrated: A data warehouse integrates data from multiple data sources. For example, source A and source B may have different ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse. This contrasts with a transactions system, where often only the most recent data is kept. For example, a transaction system may hold the most recent address of a customer, where a data warehouse can hold all addresses associated with a customer.

SQL INTERVIEW QUESTIONS AND ANSWERS43 | P a g eNon-volatile: Once data is in the data warehouse, it will not change. So, historical data in a data warehouse should never be altered.190.What is Data Mining?Data mining is a term from computer science. Sometimes it is also called knowledge discovery in databases (KDD). Data mining is about finding new information in a lot of data. The information obtained from data mining is hopefully both new and useful

131. What are the difference between clustered and a non-clustered index?

1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

2. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

132. What are the different index configurations a table can have?

A table can have one of the following index configurations:

1. No indexes

2. A clustered index

3. A clustered index and many nonclustered indexes

4. A non clustered index

5. Many non clustered indexes

134. What are different types of Collation Sensitivity?

1. Case sensitivity — A and a, B and b, etc.

2. Accent sensitivity

3. Kana Sensitivity — When Japanese kana characters Hiragana and Katakana aretreated differently, it is called Kana sensitive.

4. Width sensitivity — A single-byte character (half-width) and the same characterrepresented as a double-byte character (full-width) are treated differently than it iswidth sensitive.

135. What is OLTP (Online Transaction Processing)?

In OLTP — online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

136. What’s the difference between a primary key and a unique key?

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non clustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

137. What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.1. TRUNCATE:1. TRUNCATE is faster and uses fewer system and transaction log resourcesthan DELETE.2. TRUNCATE removes the data by deallocating the data pages used to store thetable’s data, and only the page deallocations are recorded in the transactionlog.3. TRUNCATE removes all rows from a table, but the table structure, itscolumns, constraints, indexes and so on, remains. The counter used by anidentity for new rows is reset to the seed for the column.4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEYconstraint. Because TRUNCATE TABLE is not logged, it cannot activate atrigger.5. TRUNCATE cannot be rolled back.6. TRUNCATE is DDL Command.7. TRUNCATE Resets identity of the table 2. DELETE:1. DELETE removes rows one at a time and records an entry in the transactionlog for each deleted row.2. If you want to retain the identity counter, use DELETE instead. If you want toremove table definition and its data, use the DROP TABLE statement.3. DELETE Can be used with or without a WHERE clause

SQL INTERVIEW QUESTIONS AND ANSWERS32 | P a g e4. DELETE Activates Triggers.5. DELETE can be rolled back.6. DELETE is DML Command.7. DELETE does not reset identity of the table.Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

138. When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

139. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

140. What are the properties and different Types of Sub-Queries?

1. Properties of Sub-Query1. A sub-query must be enclosed in the parenthesis.

2. A sub-query must be put in the right hand of the comparison operator, and

3. A sub-query cannot contain an ORDER-BY clause.

4. A query can contain more than one sub-query.

2. Types of Sub-Query

a. Single-row sub-query, where the sub-query returns only one row.

b. Multiple-row sub-query, where the sub-query returns multiple rows,. and

c. Multiple column sub-query, where the sub-query returns multiple columns

141. What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

142. What are the authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode — SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

143. Which command using Query Analyzer will give you the version of SQL serverand operating system?

SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’).

144. What is SQL Server Agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its fullfunction scheduling engine, which allows you to schedule your own jobs and scripts.

145. Can a stored procedure call itself or recursive stored procedure? How muchlevel SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

146. What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

147. Name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1 SELECT COUNT(*) FROM table1SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2148. What does it mean to have QUOTED_IDENTIFIER ON? What are the implicationsof having it OFF?When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all TransactSQL rules for identifiers.

149. What is the difference between a Local and a Global temporary table?

1. A local temporary table exists only for the duration of a connection or, if definedinside a compound statement, for the duration of the compound statement.

2. A global temporary table remains in the database permanently, but the rows existonly within a given connection. When connection is closed, the data in the globaltemporary table disappears. However, the table definition remains with thedatabase for access when database is opened next time.

150. What is the STUFF function and how does it differ from the REPLACE function?

STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

151. What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

152. What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

153. What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

154. What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

155. What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

156. How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

157. What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

158. What are the advantages of using Stored Procedures?

1. Stored procedure can reduced network traffic and latency, boosting applicationperformance.

2. Stored procedure execution plans can be reused, staying cached in SQL Server’smemory, reducing server overhead.

3. Stored procedures help promote code reuse.

4. Stored procedures can encapsulate logic. You can change stored procedure codewithout affecting clients.

5. Stored procedures provide better security to your data.

159. What is a table called, if it has neither Cluster nor Non-cluster Index? What is itused for?

Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.

160. Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

101.Define SQL.

SQL stands for Structured Query Language. It allows access, insert/update/delete records and retrieve data from the database.

102.What is RDBMS?

Explain its features.RDBMS stands for Relational Database Management System. It organizes data into related rows and columns.

103.What is an Entity-Relationship diagram?

It is a graphical representation of tables with the relationship between them. Read answer

104.Define referential integrity.

It is the rules that are applied when the relationships are created. It ensures integrity of data and prevents inconsistent data into the tables.

105.Define Primary key and Foreign key.

A column or combination of columns that identify a row of data in a table is Primary Key.

106.Define alternate key.

There can be a key apart from primary key in a table that can also be a key. This key may or may not be a unique key.

107.Delete vs. Truncate table.

Delete logs the deletion of each row whereas Truncate doesn’t log deleted rows in the transaction log. This makes truncate command is bit faster than Delete command.

108.Define constraints.

Constraints enforce integrity of the database. Constraints can be of following typesNot Null Check Unique Primary key Foreign key

109.Define stored procedure.

Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the program.

110.Define Trigger.

Triggers are similar to stored procedure except it is executed automatically when any operations are occurred on the table.

111. What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

112. What are the properties of the Relational tables?

Relational tables have six properties:1. Values are atomic.2. Column values are of the same kind.3. Each row is unique.4. The sequence of columns is insignificant.5. The sequence of rows is insignificant.6. Each column must have a unique name.

113. What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

114. What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

115. What are different normalization forms?

1. 1NF: Eliminate Repeating Groups Make a separate table for each set of relatedattributes, and give each table a primary key. Each field contains at most one valuefrom its attribute domain.

2. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multivalued key, remove it to a separate table.

3. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute toa description of the key, remove them to a separate table. All attributes must bedirectly dependent on the primary key.

4. BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies betweencandidate key attributes, separate them out into distinct tables.

5. 4NF: Isolate Independent Multiple Relationships No table may contain two ormore 1:n or n:m relationships that are not directly related.

6. 5NF: Isolate Semantically Related Multiple Relationships There may be practicalconstrains on information that justify separating logically related many-to-manyrelationships.

7. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, asexpressed in Object Role Model notation.

8. DKNF: Domain-Key Normal Form A model free from all modification anomalies issaid to be in DKNF.Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

116. What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.e.g. sp_helpdb, sp_renamedb, sp_depends etc.

117. What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

118. What is Nested Trigger?

A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

119. What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

120. What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

121. What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedserverlogin will be used add new Linked Server.

122. What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-byrow basis, instead of the typical SQL commands that operate on all the rows in the set at one time.In order to work with a cursor we need to perform some steps in the following order:

1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. De-allocate cursor

123. What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

124. What is Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another row set. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Row set operations.

125. What is sub-query?

Explain properties of sub-query?Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

126. What are different Types of Join?

1. Cross Join A cross join that does not have a WHERE clause produces the Cartesianproduct of the tables involved in the join. The size of a Cartesian product result set isthe number of rows in the first table multiplied by the number of rows in the secondtable. The common example is when company wants to combine each product witha pricing table to analyze each product at each price.
2. Inner Join A join that displays only the rows that have a match in both joined tablesis known as inner Join. This is the default type of join in the Query and ViewDesigner.
3. Outer Join A join that includes rows even if they do not have related rows in thejoined table is an Outer Join. You can create three different outer join to specify theunmatched rows to be included:

a. Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left”table, which appears leftmost in the JOIN clause are included. Unmatchedrows in the right table do not appear.

b. Right Outer Join: In Right Outer Join all rows in the second-named table i.e.”right” table, which appears rightmost in the JOIN clause are included.Unmatched rows in the left table are not included.

c. Full Outer Join: In Full Outer Join all rows in all joined tables are included,whether they are matched or not.

4. Self Join This is a particular case when one table joins to itself, with one or twoaliases to avoid confusion. A self join can be of any type, as long as the joined tablesare the same. A self join is rather unique in that it involves a relationship with onlyone table. The common example is when company has a hierarchal reportingstructure whereby one member of staff reports to another. Self Join can be OuterJoin or Inner Join.

127. What are primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

128. What is User Defined Functions? What kind of User-Defined Functions can becreated?

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.Different Kinds of User-Defined Functions created are:

1. Scalar User-Defined Function A Scalar user-defined function returns one of thescalar data types. Text, image and timestamp data types are not supported. Theseare the type of user-defined functions that most developers are used to in otherprogramming languages. You pass in 0 to many parameters and you get a returnvalue.
2. Inline Table-Value User-Defined Function An Inline Table-Value user-definedfunction returns a table data type and is an exceptional alternative to a view as theuser-defined function can pass parameters into a T-SQL select command and inessence provide us with a parameterized, non-updateable view of the underlyingtables.
3. Multi-statement Table-Value User-Defined Function A Multi-Statement TableValue user-defined function returns a table and is also an exceptional alternative toa view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

129. What is Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at

1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

130. Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

86. How to determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit

87.What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of the result sets.Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria:Salary between 30000 and 40000–5000 hike Salary between 40000 and 55000–7000 hike Salary between 55000 and 65000–9000 hikeIn this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:UPDATE tbl_emp SET salary =CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 ENDAnother situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row.

88. Write down the general syntax for a SELECT statements covering all the optionsHere’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).

SELECT select_list [INTO new_table_] FROM table_source[WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition][ORDER BY order_expression [ASC | DESC] ]

89. What is a join and explain different types of joinsJoins are used in queries to explain how different tables are related.

Joins also let you select data from a table depending upon data from another table.Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs,OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

90. Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

91. What is an extended stored procedure? Can you instantiate a COM object by usingT-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL,just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OA Create stored procedure.

92. What is the system function to get the current user’s user id?

USER_ID().Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

93. What are triggers? How many triggers you can have on a table? How to invoke atrigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorderTriggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.

94. There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.

95. What is a self join? Explain it with an example

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.CREATE TABLE emp (empid int,mgrid int, empname char(10) )INSERT emp SELECT 1,2,’Vyas’ INSERT emp SELECT 2,3,’Mohan’ INSERT emp SELECT 3,NULL,’Shobha’ INSERT emp SELECT 4,2,’Shridhar’ INSERT emp SELECT 5,2,’Sourabh’SELECT t1.empname [Employee], t2.empname [Manager] FROM emp t1, emp t2WHERE t1.mgrid = t2.empidHere’s an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid

96.Define Fact tables and dimension tables.

Fact tables are central tables in data warehousing. They contain the aggregate values that are used in business process.

97.Explain the ETL process in Data warehousing.

Extraction, Transformation and loading are different stages in data warehousing.

98.What is Data mining?

Data mining is a process of analyzing current data and summarizing the information in more useful manner.

99.What are indexes?

Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record.

100.Explain the types of indexes?

Clustered index Non-clustered.

66. Explain Active/Active and Active/Passive cluster configurations

Ans : Hopefully you have experience setting up cluster servers. But if you don’t, at least be familiar with the way clustering works and the two clustering configurations Active/Active and Active/Passive.

67. Explain the architecture of SQL Server

Ans : This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture.

68. What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.

69.What is the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

70. Explain the storage models of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more information.

71. What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)?

What changed between the previous version of SQL Server and the current version?This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled “What’s New”, which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled “Backward Compatibility” in books online which talks about the changes that have taken place in the new version.

72. What are constraints? Explain different types of constraints

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

73. What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.Indexes are of two types. Clustered indexes and non-clustered indexes. When you createe a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

74. What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board’s homepage.

75. What are the steps you will take to improve performance of a poor performingquery?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

76. What are the steps you will take, if you are tasked with securing an SQL Server?

Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, database and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.

77. What is a deadlock and what is a live lock? How will you go about resolvingdeadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

78. What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

79. Explain CREATE DATABASE syntax

Many of us are used to creating databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two file groups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That’s why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.

80. How to restart SQL Server in single user mode? How to start SQL Server inminimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal configuration mode. Check out SQL Server books online for more parameters and their explanations.

81. As a part of your job, what are the DBCC commands that you commonly use fordatabase maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.

82. What are statistics, under what circumstances they go out of date, how do youupdate them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.Some situations under which you should update statistics:1) If there is significant change in the key values in the index2) If a large amount of data in an indexed column has been added, changed, or removed(that is, if the distribution of key values has changed), or the table has been truncated usingthe TRUNCATE TABLE statement and then repopulated3) Database is upgraded from a previous version

83. What are the different ways of moving data/databases between servers anddatabases in SQL Server?

There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data.

84. Explain different types of BACKUPs available in SQL Server? Given a particularscenario, how would you go about choosing a backup plan?

Types of backups you can create in SQL Server 7.0+ are Full database backup, differential database backup, transaction log backup, file group backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.

85.What is database replication? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:o Snapshot replicationo Transactional replication (with immediate updating subscribers, with queued updatingsubscribers)o Merge replication

31.What are properties of a transaction?

Ans:

Properties of the transaction can be summarized as ACID Properties.

1. Atomicity A transaction consists of many steps. When all the steps in a transaction gets completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.

2. Consistency The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.

3. Isolation Every transaction should operate as if it is the only transaction in the system

4. Durability Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis

32.What is a Database Lock ?

Ans:

Database lock tell a transaction, if the data item in questions is currently being used by other transactions.

33. What are the type of locks ?

Ans:

1. Shared Lock When a shared lock is applied on data item, other transactions can only read the item, but can’t write into it.

2. Exclusive Lock When a exclusive lock is applied on data item, other transactions can’t read or write into the data item.

34.What are the different type of normalization?

Ans:

In database design , we start with one single table, with all possible columns. Lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.

1. First Normal Form (1NF) A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data

2. Second Normal Form (2NF) A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data

3. Third Normal Form (3NF) A relation is said to be in 3NF, if and only if it is in 2NF and every non key attribute is nontransitively dependent on the primary key

35.What is a primary key?

Ans:

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :

1. No two rows can have the same primary key value.

2. Every row must have a primary key value

3. Primary key field cannot be null

4. Values in primary key columns can never be modified or updated

36.What is a Composite Key ?

Ans:

A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table. For example — if “Employee_ID” and “Employee Name” in a table is combined to uniquely identifies a row its called a Composite Key.

37.What is a Composite Primary Key ?

Ans:

A Composite primary key is a set of columns whose values uniquely identify every row in a table. What it means is that, table which contains composite primary key will be indexed based on columns specified in the primary key. This key will be referred in Foreign Key tables. For example — if combined effect of columns, “Employee_ID” and “Employee Name” in a table is required to uniquely identifies a row, its called a Composite Primary Key. In this case, both the columns will be represented as primary key.

38.What is a Foreign Key ?

Ans:

When a “one” table’s primary key field is added to a related “many” table in order to create the common field which relates the two tables, it is called a foreign key in the “many” table. For example, salary of an employee is stored in salary table. Relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in Employee table .

39. What is a Unique Key ?

Ans:

Unique key is same as primary with difference being existence of null. Unique key field allows one value as NULL value.

40.Define SQL Insert Statement ?

Ans:

SQL INSERT statement is used to add rows to a table. For a full row insert , SQL Query should start with “insert into “ statement followed by table name and values command, followed by the values that need to be inserted into the table. Insert can be used in several ways:

1. To insert a single complete row

2. To insert a single partial row

41. Define SQL Update Statement ?

Ans:

SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is ,Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated

42. Define SQL Delete Statement ?

Ans:

SQL Delete is used to delete a row or set of rows specified in the filter condition. The basic format of an SQL DELETE statement is, DELETE FROM command followed by table name followed by filter condition that determines which rows should be updated.

43.What are wild cards used in database for Pattern Matching ?

Ans:

SQL Like operator is user for pattern matching. SQL ‘Like’ command takes more time to process. So before using like operator, consider suggestions given below on when and where to use wild card search.

1. Don’t overuse wild cards. If another search operator will do, use it instead.

2) When you do use wild cards, try not to use them at the beginning of the search pattern, unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.

3) Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended

44. Define Join and explain different type of joins?

Ans:

In order to avoid data duplication, data is stored in related tables . Join keyword is used to fetch data from related table. Join return rows when there is at least one match in both tables . Type of joins are Right Join Return all rows from the right table, even if there are no matches in the left table . Outer Join Left Join Return all rows from the left table, even if there are no matches in the right table . Full Join Return rows when there is a match in one of the tables .

45.What is Self-Join?

Ans:

Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.

46. What is Cross Join?

Ans:

Cross Join will return all records where each row from the first table is combined with each row from the second table.

47. What is a view?

Ans:

Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

48. What is a materialized view?

Ans:

Materialized views is also a view but are disk based. Materialized views get updated on specific duration, base upon the interval specified in the query definition. We can index materialized view.

49. What are the advantages and disadvantages of views in a database?

Ans:

Advantages:

1. Views doesn’t store data in a physical location.

2. View can be use to hide some of the columns from the table

3. Views can provide Access Restriction, since data insertion, update and deletion is not possible on the view.

Disadvantages:

1. When a table is dropped, associated view become irrelevant.

2. Since view are created when a query requesting data from view is triggered, its bit slow

3. When views are created for large tables, it occupy more memory .

50. What is a stored procedure?

Ans:

Stored Procedure is a function which contain collection of SQL Queries. Procedure can take inputs , process them and send back output.

51.What are the advantages a stored procedure?

Ans:

Stored Procedures are pre-complied and stored in database. This enable the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.

52.What is a trigger?

Ans:

Database are set of commands that get executed when an event(Before Insert, After Insert, On Update, On delete of a row) occurs on a table, views.

53. Explain the difference between DELETE , TRUNCATE and DROP commands?

Ans:

Once delete operation is performed, Commit and Rollback can be performed to retrieve data. Once truncate statement is executed, Commit and Rollback statement cant be performed. Where condition can be used along with delete statement but it cant be used with truncate statement. Drop command is used to drop the table or keys like primary,foreign from a table.

54. What is the difference between Cluster and Non cluster Index?

Ans:

A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It make data retrieval faster.

A non clustered index does not alter the way it was stored but creates a complete separate object within the table. As a result insert and update command will be faster.

55. What is Union, minus and Interact commands?

Ans:

MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.

56.What’s the difference between a primary key and a unique key?

Ans:

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non clustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

57. What are user defined datatypes and when you should go for them?

Ans:

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

58.How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

Ans:

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

59.What is bit datatype and what’s the information that can be stored inside a bit column?

Ans:

Bit datatype is used to store boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

60.Define candidate key, alternate key, composite key .

Ans:

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.

If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

61. What are defaults? Is there a column to which a default can’t be bound?

Ans:

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them. See CREATE DEFUALT in books online.

62. What is a transaction and what are ACID properties?

Ans:

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

63.Explain different isolation levels .

Ans:

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable.

64.CREATE INDEX myIndex ON myTable (myColumn) What type of Index will get created after executing the above statement?

Ans:

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

65.What’s the maximum size of a row?

Ans:

8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. Check out SQL Server books online for the page titled: “Maximum Capacity Specifications”

1. What is the difference between “Stored Procedure” and “Function”?

Ans:

1. A procedure can have both input and output parameters, but a function can only have input parameters.
2. Inside a procedure we can use DML (INSERT/UPDATE/DELETE) statements. But inside a function we can’t use DML statements.
3. We can’t utilize a Stored Procedure in a Select statement. But we can use a function in a Select statement.
4. We can use a Try-Catch Block in a Stored Procedure but inside a function we can’t use a Try-Catch block.
5. We can use transaction management in a procedure but we can’t in a function.
6. We can’t join a Stored Procedure but we can join functions.
7. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section. But we can use a function anywhere.

8. A procedure can return 0 or n values (max 1024). But a function can return only 1 value that is mandatory.

9. A procedure can’t be called from a function but we can call a function from a procedure.

2.What is difference between “Clustered Index” and “Non Clustered Index”?

Ans:

1. A Clustered Index physically stores the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined, whereas a non-clustered index creates a separate list of key values (or creates a table of pointers) that points towards the location of the data in the data pages.

2. A Clustered Index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key whereas a non-clustered index requires separate storage than the table storage to store the index information.

3. A table with a Clustered Index is called a Clustered Table. Its rows are stored in a BTree structure sorted whereas a table without any clustered indexes is called a nonclustered table. Its rows are stored in a heap structure unsorted.

4. The default index is created as part of the primary key column as a Clustered Index.

5. In a Clustered Index, the leaf node contains the actual data whereas in a nonclustered index, the leaf node contains the pointer to the data rows of the table.

6. A Clustered Index always has an Index Id of 1 whereas non-clustered indexes have Index Ids > 1.

7. A Table can have only 1 Clustered Index whereas prior to SQL Server 2008 only 249 non-clustered indexes can be created. With SQL Server 2008 and above 999 nonclustered indexes can be created.

8. A Primary Key constraint creates a Clustered Index by default whereas A Unique Key constraint creates a non-clustered index by default

3.What is the difference between the “DELETE” and “TRUNCATE” commands?

Ans:

1. The DELETE command is used to remove rows from a table based on a WHERE condition whereas TRUNCATE removes all rows from a table.

2. So we can use a where clause with DELETE to filter and delete specific records whereas we cannot use a Where clause with TRUNCATE.

3. DELETE is executed using a row lock, each row in the table is locked for deletion whereas TRUNCATE is executed using a table lock and the entire table is locked for removal of all records.

4. DELETE is a DML command whereas TRUNCATE is a DDL command.

5. DELETE retains the identity of the column value whereas in TRUNCATE, the Identify column is reset to its seed value if the table contains any identity column.

6. To use Delete you need DELETE permission on the table whereas to use Truncate on a table you need at least ALTER permission on the table.

7. DELETE uses more transaction space than the TRUNCATE statement whereas Truncate uses less transaction space than DELETE statement.

8. DELETE can be used with indexed views whereas TRUNCATE cannot be used with indexed views.

9. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

10. Delete activates a trigger because the operation is logged individually whereas TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions.

4.What is the difference between the “WHERE” clause and the “HAVING” clause?

Ans:

1. WHERE clause can be used with a Select, Update and Delete Statement Clause but the HAVING clause can be used only with a Select statement.

2. We can’t use an aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause whereas we can use an aggregate function in the HAVING clause. We can use a column name in the HAVING clause but the column must be contained in the group by clause.

3. WHERE is used before the GROUP BY clause whereas a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause in the query.

4. A WHERE clause applies to each and every row whereas a HAVING clause applies to summarized rows (summarized with GROUP BY).

5. In the WHERE clause the data that is fetched from memory depending on a condition whereas in HAVING the completed data is first fetched and then separated depending on the condition.

5.What is the difference between “Primary Key” and “Unique Key”?

Ans:

1. We can have only one Primary Key in a table whereas we can have more than one Unique Key in a table.

2. The Primary Key cannot have a NULL value whereas a Unique Key may have only one null value.

3. By default, a Primary Key is a Clustered Index whereas by default, a Unique Key is a unique non-clustered index.

4. A Primary Key supports an Auto Increment value whereas a Unique Key doesn’t support an Auto Increment value.

6. What is the difference between a “Local Temporary Table” and “Global Temporary Table”?

Ans:

1. A Local Temporary Table is created by giving it a prefix of # whereas a Global Temporary Table is created by giving it a prefix of ##.

2. A Local Temporary Table cannot be shared among multiple users whereas a Global Temporary Table can be shared among multiple users.

3. A Local Temporary Table is only available to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once created. They are cleared when the last connection is closed.

7.What are super, primary, candidate and foreign keys?

Ans:

A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes. A Candidate key is minimal super key, i.e., no proper subset of Candidate key attributes can be a super key. A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table. Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

8.What is the difference between primary key and unique constraints?

Ans:

Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains.

9.What is database normalization?

Ans:

It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties:

1. Minimizing Redundancy

2) Minimizing the Insertion, Deletion, And Update Anomalies Relation schemas that do not meet the properties are decomposed into smaller relation schemas that could meet desirable properties.

10.What is SQL?

Ans:

SQL is Structured Query Language designed for inserting and modifying in a relational database management system.

11.What are the differences between DDL, DML and DCL in SQL?

Ans:

Following are some details of three. DDL stands for Data Definition Language.

SQL queries like CREATE, ALTER, DROP and RENAME come under this. DML stands for Data Manipulation Language.

SQL queries like SELECT, INSERT and UPDATE come under this. DCL stands for Data Control Language.

SQL queries like GRANT and REVOKE come under this.

12.What is the difference between having and where clause?

Ans:

HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions.

13.What is Join?

Ans:

An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.

14.What is Identity?

Ans:

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at

1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

15.What is a view in SQL? How to create one ?

Ans:

A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.

CREATE VIEW view_name AS

SELECT column_name(s) FROM table_name

WHERE condition

16.What are the uses of view?

Ans:

1. Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

2. Views can join and simplify multiple tables into a single virtual table

3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data

4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.

5. Depending on the SQL engine used, views can provide extra security.

17.What is a Trigger?

Ans:

A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.

18.What is a stored procedure?

Ans:

A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

19.What is the difference between Trigger and Stored Procedure?

Ans:

Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries.

20.What is a transaction? What are ACID properties?

Ans:

A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them. An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

21.What are indexes?

Ans:

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired, For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

22.What are clustered and non-clustered Indexes?

Ans:

Clustered indexes is the index according to which data is physically stored on disk. Therefore, only one clustered index can be created on a given database table. Non-clustered indexes don’t define physical ordering of data, but logical ordering. Typically, a tree is created whose leaf point to disk records. B-Tree or B+ tree are used for this purpose.

23.What are Primary Keys and Foreign Keys?

Ans:

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key. Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

24.What is SQL ?

Ans:

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard .

25. What are the different type of SQL or different commands in SQL?

Ans:

1. DDL — Data Definition Language.DDL is used to define the structure that holds the data.

2.DML– Data Manipulation Language DML is used for manipulation of the data itself. Typical operations are Insert, Delete,Update and retrieving the data from the table

3.DCL–DataControlLanguage DCL is used to control the visibility of data like granting database access and set privileges to create table etc.

4.TCL-TransactionControl Language

26. What are the Advantages of SQL?

Ans:

1. SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmer to interact with any database like ORACLE, SQL ,MYSQL etc.

2. SQL is easy to learn. The statements are all made up of descriptive English words, and there aren’t that many of them.

3. SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations

27. what is a field in a database ?

Ans:

A field is an area within a record reserved for a specific piece of data. Examples: Employee Name , Employee ID etc

28.What is a Record in a database ?

Ans:

A record is the collection of values / fields of a specific entity: i.e. a Employee , Salary etc.

29. What is a Table in a database ?

Ans:

A table is a collection of records of a specific type. For example, employee table , salary table etc.

30.What is a database transaction?

Ans:

Database transaction take database from one consistent state to another. At the end of the transaction the system must be in the prior state if transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

Excel Statistical Functions:

In order to have a cell showing the result of a statistical calculation (like the sum or the average of some other cells), we use what is called a “Function”.

We get the function by clicking a button, or by writing a short code composed of few letters.

Here is a description of the functions learned, with the relevant “function code” appearing at the beginning of each paragraph:

1. SUM Function in Excel:

The SUM is a mathematical function that adds up a range of values. One can use the SUM function to quickly find the total of a set of values, such as company expenses, sales figures, or test scores.

The Syntax of the SUM function is:

=SUM(number1, number2,….) The function takes the following arguments : number1: It indicates the first numerical value we want to add. number2: It marks the second numerical value we want to add.

It takes a maximum of 255 arguments, and they can be numbers, cell references, cell ranges, constants, arrays, and the results of other formulas or functions.

2. MAX Function:

MAX is a mathematical function determining the largest value in a given cell- range. You can use the function to find the highest monthly sales figure, test score, or temperature in a given period.

Syntax:

=MAX(number1, number2,…) The MAX function in Excel accepts the following arguments: number1- It represents the first integer we want to compare number2-It represents the second integer we want to compare

In Excel 2007 and newer versions, the MAX function can handle a maximum of 255 arguments. However, for Excel 2003 and older versions, the MAX function can only process up to 30 arguments.

3. MIN Function:

The Excel MIN function provides the smallest value within a range and is particularly useful in data analysis and decision-making. For instance, finance professionals may use it to identify the lowest price point for a product over a given period.

Syntax:

=MIN(number1, number2,…) The MIN function in Excel takes the following arguments: number1- It represents the first integer we want to compare number2-It represents the second integer we want to compare

The MIN function in Excel can support up to 255 arguments in Excel 2007 and later versions. In 2003 and earlier versions of Excel, it can only accept up to 30 arguments.

4. AVERAGE Function in Excel:

In Excel, the AVERAGE function determines the arithmetic mean of a range of values. The function takes a series of numbers and returns the sum divided by the count of those numbers. It helps users find a company’s average monthly, quarterly, or yearly revenue or expenses. Students can use it to calculate their average grade across multiple subjects.

Syntax:

=AVERAGE(number1, number2,…) The AVERAGE function takes the following arguments Number 1 indicates the first number or range of numbers we want to take an average of. Number 2 is an optional argument representing a second number or range of numbers we want to find average.

The function allows up to 255 arguments and ignores text or blank cells.

5. Product Function in Excel:

The Product is a mathematical function that multiply a range of values.

The Syntax of the Product function is:

=PRODUCT(number1, number2,….) The function takes the following arguments : number1: It indicates the first numerical value we want to multiply. number2: It marks the second numerical value we want to multiply.

6. Excel COUNT Function:

COUNT Function Syntax:

=COUNT(value1, [value2], …) Where ‘value’ can be a single cell (you wouldn’t do this though as I hope you can already count to 1, if not my 2 year old can teach you, or for advanced counting my 5 year old says he can count to infinity!) or, more likely you will enter a range of cells in place of each ‘value’.

For example; you can count one range of cells: =COUNT(A1:A500)

Or multiple ranges of non contiguous cells: =COUNT(A1:A500,C1:C500,E1:G500)

There can be up to 30 ‘values’.

COUNT Function Rules:

• It only counts cells containing numbers
• It ignores blank cells
• It ignores cells containing anything but a number Ok, that’s 3 ways to say the same thing but it leads me nicely onto the COUNTA function.

Excel COUNTA Function:

Excel’s COUNTA function counts cells that are not empty.

That means it includes error values, like #VALUE!, numbers and blank spaces. I don’t mean blank cells, I mean cells with empty text like for example if you entered a space in a cell then COUNTA would count that cell.

COUNTA doesn’t count empty or blank cells. You need the COUNTBLANK function for that. More on COUNTBLANK below.

COUNTA Function Syntax:

=COUNTA(value1, [value2], …) Ditto COUNT function formula examples. That is; the ‘value’ in the COUNTA function syntax works the same as they do for the COUNT function.

Excel’s COUNTBLANK Function:

COUNTBLANK Function Syntax:

=COUNTBLANK(range) You’ll notice that the syntax is ‘range’ and there’s only one of them. This is because unlike COUNT and COUNTA, the COUNTBLANK function cannot handle non-contiguous ranges.

Autosum:

Where is the AutoSum button in Excel?

The AutoSum button is available in 2 locations on the Excel ribbon. Home tab > Editing group > AutoSum:

The AutoSum button in Excel:

Formulas tab > Function Library group > AutoSum: Another location of the AutoSum button in Excel

How to AutoSum in Excel:

Whenever you need to sum a single range of cells, whether a column, row or several adjacent columns or rows, you can have Excel AutoSum to automatically make an appropriate SUM formula for you.

To use AutoSum in Excel, just follow these 3 easy steps:

• Select a cell next to the numbers you want to sum:
• To sum a column, select the cell immediately below the last value in the column.
• To sum a row, select the cell to the right of the last number in the row.
• Select a cell next to the numbers you want to sum.
• Click the AutoSum button on either the Home or Formulas tab.

In most cases, Excel selects the correct range to total. In a rare case when a wrong range is selected, you can correct it manually by typing the desired range in the formula or by dragging the cursor through the cells you want to sum.

Tip -

To sum multiple columns or rows at a time, select several cells at the bottom or to the right of your table, respectively, and then click the AutoSum button. For more details, please see How to use AutoSum on more than one cell at a time.

• Press the Enter key to complete the formula.
• Now, you can see the calculated total in the cell, and the SUM formula in the formula bar:
• Press the Enter key to complete the Sum formula.

Shortcut for Sum in Excel:

If you one of those Excel users that prefer working with the keyboard rather than the mouse, you can use the following Excel AutoSum keyboard shortcut to total cells:

• Pressing the Equal Sign key while holding the Alt key inserts a Sum formula in a selected cells(s) exactly like pressing the AutoSum button on the ribbon does, and then you hit the Enter key to complete the formula.

How to use AutoSum with other functions:

Apart from adding cells, you can use Excel’s AutoSum button to insert other functions, such as:

AVERAGE — to return the average (arithmetic mean) of numbers. COUNT — to count cells with numbers. MAX — to get the largest value. MIN — to get the smallest value. All you need to do is select a cell where you want to insert a formula, click the AutoSum drop-down arrow, and choose the desired function from the list.

For example, this is how you can get the largest number in column B: Using Excel AutoSum to automatically insert formulas other than SUM

If you select More Functions from the AutoSum drop-down list, Microsoft Excel will open the Insert Function dialog box, like it does when you click the Insert Function button on the Formulas tab, or the fx button on the Formula bar.

How to AutoSum only visible (filtered) cells in Excel:

You already know how to use AutoSum in Excel to total a column or row. But did you know that you can use it to sum only visible cells in a filtered list?

If your data in organized in an Excel table (which can be easily done by pressing Ctrl + T shortcut), clicking the AutoSum button inserts the SUBTOTAL function that adds only visible cells.

How to use AutoSum on more than one cell at a time:

If you want to sum values in several columns or rows, select all the cells where you want to insert the Sum formula, and then click the AutoSum button on the ribbon or press the Excel Sum shortcut.

For example, you can select cells A10, B10 and C10, click AutoSum, and total 3 columns at once. As shown in the below screenshot, the values in each of the 3 columns are summed individually: Using AutoSum on more than one cell at a time

How to sum selected cells vertically and horizontally:

To total only certain cells in a column, select those cells and click the AutoSum button. This will total the selected cells vertically column-by-column, and place the SUM formula(s) below the selection: Total the selected cells vertically column-by-column.

• If you want to sum cells row-by-row, select the cells you want to total and one empty column to the right. Excel will sum the selected cells horizontally and insert the SUM formulas to the empty column included in the selection: Total the selected cells horizontally row-by-row
• To sum cells column-by-column and row-by-row, select the cells you want to add, plus one empty row below and one empty column to the right, and Excel will total the selected cells vertically and horizontally: Sum the selected cells vertically and horizontally.

How to copy an AutoSum formula to other cells:

Once AutoSum has added a SUM (or other) function in the selected cell, the inserted formula behaves like a normal Excel formula. Consequently, you can copy that formula to other cells in the usual way, for example by dragging the fill handle. For more information, please see How to copy a formula in Excel.

Just keep in mind that Excel’s AutoSum uses relative cell references (without $) that adjust to the new formula location based on the relative position of rows and columns.

For example, you can have AutoSum to insert the following formula in cell A10 to total the values in column A: =SUM(A1:A9). And when you copy that formula to cell B10, it will turn into =SUM(B1:B9) and total the numbers in column B.

In most cases, it is exactly what you need. But if you want to copy the formula to another cell without changing the cell references, you’d need to fix the references by adding the $ sign. Please see Why use $ in Excel formulas for full details.

Excel AutoSum not working:

The most common reason for AutoSum not working in Excel is numbers formatted as text. At first sight, those values may look like normal numbers, but Excel regards them as text strings and does not include in calculations.

The most obvious indicators of numbers formatted as text are their default left alignment and little green triangles in top-left corner of the cells. To fix such text-numbers, select all problematic cells, click the warning sign, and then click Convert to Number. Select cells with numbers formatted as text, click the warning sign, and then click Convert to Number.

Numbers can become formatted as text because of various reasons, such as importing a dataset from an external source, or enclosing numeric values in double quotes in your Excel formulas. If the latter, neither green triangles nor the warning sign will appear in cells, because Excel assumes you want to output a text string on purpose.

For example, the following IF formula appears to be working fine:

=IF(A1=”OK”, “1”, “0”)

But the returned 1’s and 0’s are text values, not numbers! And therefore, when you try to do AutoSum on cells containing such formulas, you will always get ‘0’ as the result.

As soon as you remove “” surrounding 1 and 0 in the above formula, Excel AutoSum will treat the outputs as numbers and they will be added up correctly.

Formatting in Excel:

What is Formatting in Excel?

Formatting in Excel means a trick that we can use to modify the data’s appearance in a worksheet. We can format the data in various ways, like we can format the font of the cells or the table with the help of the styles and format tab present in the Home tab.

It’s easier than ever to format worksheet (or sheet) data in Excel. There are various quick and easy ways to generate professional-looking worksheets that efficiently present our data. For example, we can utilize document themes to give our Excel spreadsheets a consistent design, style to apply predetermined formats, and other manual formatting capabilities to highlight essential data.

Excel formatting is an optional step following data preparation, or all of the data cleansing, structuring, enriching, and standardizing necessary to prepare the data for analysis.

New data rarely comes without its own unique set of issues; it is up to the analysts to analyze their data and guarantee that it is ready to meet the exact requirements of their analytical project. Splitting columns, eliminating rows with incomplete data, and standardizing against a certain name.

Once completed, Excel formatting adds the finishing touches, ensuring that the data is properly prepared and presented.

Good formatting will improve our data in various ways:

• With the help of the formatting, we can present our data correctly; for example, formatting as dates or currency will provide more value to our data.
• Merging and aligning our data is a vital aspect of making our data more readable.
• Formatting our text by increasing the Size, bolding, adding italics, or changing the fonts will improve the overall appearance of our worksheet.
• Using styles (like table styles) can make our data stand out and helps the reader to focus on crucial portions of the worksheet.
• Conditional formatting is a useful tool for highlighting crucial portions of our worksheet graphically or visibly. These are dynamic tools. The Highlighted region changes as our data changes.

Benefits of Data Formatting in Excel:

The following are some of the benefits of Excel data formatting:

• The data appears to be more presentable.
• Data formatting saves a lot of time and effort.
• With the help of the chart, we can analyze the data.
• With the help of the formatting, we can highlight specific data such as profit or loss in business.

To change the font:

By default, the font of each new workbook is set to Calibri. However, Excel provides many other fonts you can use to customize your cell text. In the example below, we’ll format our title cell to help distinguish it from the rest of the worksheet.

• Select the cell(s) you want to modify.
• Click the drop-down arrow next to the Font command on the Home tab. The Font drop-down menu will appear. Select the desired font. A live preview of the new font will appear as you hover the mouse over different options. The text will change to the selected font.

To change the font size:

• Select the cell(s) you want to modify.
• Click the drop-down arrow next to the Font Size command on the Home tab. The Font Size drop-down menu will appear.
• Select the desired font size. A live preview of the new font size will appear as you hover the mouse over different options. The text will change to the selected font size. You can also use the Increase Font Size and Decrease Font Size commands or enter a custom font size using your keyboard.

To change the font color:

• Select the cell(s) you want to modify.
• Screenshot of Excel 2013
• Click the drop-down arrow next to the Font Color command on the Home tab. The Color menu will appear.
• Select the desired font color. A live preview of the new font color will appear as you hover the mouse over different options. The text will change to the selected font color. Select More Colors at the bottom of the menu to access additional color options.

To use the Bold, Italic, and Underline commands:

• Select the cell(s) you want to modify.
• Click the Bold (B), Italic (I), or Underline (U) command on the Home tab. In our example, we’ll make the selected cells bold. The selected style will be applied to the text. You can also press Ctrl+B on your keyboard to make selected text bold, Ctrl+I to apply italics, and Ctrl+U to apply an underline.

Text alignment:

By default, any text entered into your worksheet will be aligned to the bottom-left of a cell, while any numbers will be aligned to the bottom-right. Changing the alignment of your cell content allows you to choose how the content is displayed in any cell, which can make your cell content easier to read.

• Click the arrows in the slideshow below to learn more about the different text alignment options.
• Left align: Aligns content to the left border of the cell
• Center align: Aligns content an equal distance from the left and right borders of the cell
• Right Align: Aligns content to the right border of the cell
• Top Align: Aligns content to the top border of the cell
• Middle Align: Aligns content an equal distance from the top and bottom borders of the cell
• Bottom Align: Aligns content to the bottom border of the cell

To change horizontal text alignment:

• Select the cell(s) you want to modify.
• Select one of the three horizontal alignment commands on the Home tab. The text will realign.

To change vertical text alignment:

• Select the cell(s) you want to modify.
• Select one of the three vertical alignment commands on the Home tab. The text will realign.

Cell borders and fill colors:

Cell borders and fill colors allow you to create clear and defined boundaries for different sections of your worksheet. Below, we’ll add cell borders and fill color to our header cells to help distinguish them from the rest of the worksheet.

To add a border:

• Select the cell(s) you want to modify.
• Click the drop-down arrow next to the Borders command on the Home tab. The Borders drop-down menu will appear.
• Select the border style you want to use. In our example, we will choose to display All Borders.
• The selected border style will appear.
• You can draw borders and change the line style and color of borders with the Draw Borders tools at the bottom of the Borders drop-down menu.

To add a fill color:

• Select the cell(s) you want to modify.
• Click the drop-down arrow next to the Fill Color command on the Home tab. The Fill Color menu will appear.
• Select the fill color you want to use. A live preview of the new fill color will appear as you hover the mouse over different options. The selected fill color will appear in the selected cells.

Format Painter:

If you want to copy formatting from one cell to another, you can use the Format Painter command on the Home tab. When you click the Format Painter, it will copy all of the formatting from the selected cell. You can then click and drag over any cells you want to paste the formatting to.

Formatting text and numbers:

One of the most powerful tools in Excel is the ability to apply specific formatting for text and numbers. Instead of displaying all cell content in exactly the same way, you can use formatting to change the appearance of dates, times, decimals, percentages (%), currency ($), and much more.

To apply number formatting:

• Select the cells(s) you want to modify.
• Click the drop-down arrow next to the Number Format command on the Home tab. The Number Formatting drop-down menu will appear.
• Select the desired formatting option. In our example, we will change the formatting to Long Date.

The selected cells will change to the new formatting style. For some number formats, you can then use the Increase Decimal and Decrease Decimal commands (below the Number Format command) to change the number of decimal places that are displayed.

Name Manager:

The Name Manager is a dialog box in Excel that allows you to create, edit, and delete defined names. These names can be named ranges, named formulas, and named constants. Using named ranges can make formulas easier to read, understand. and maintain Names also provide simple navigation via the Name Box.

To open the Name Manager, navigate to Formulas > Name Manager. Or, you can use the keyboard shortcut Control + F3.

Names ranges:

A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box.

To quickly create a named range, select the range, then type a name into the name box and hit return.

Paste Special:

When you copy cell data, there are many ways it can be pasted. Some examples include:

• Transpose Data: Copy columns to rows or vice versa.
• Paste Values: Paste the results or values from a formula instead of copying and pasting the formulas themselves.
• Paste Formats: Copy formatting from a cell or cell range and paste — or apply it elsewhere.

Transpose Data:

Transposing data changes columns of copied data to rows, and vice versa.

• Select a cell range.
• Click Copy.
• Click the cell where you want to paste the data.
• Click the Paste list arrow.
• To preview a paste option before you commit to it, click the Paste list arrow and hold the mouse over the paste option.
• Select Transpose.

The selected data is transposed from columns to rows.

Paste Values:

You can paste only the values as displayed in the cells, instead of the formulas that are used to calculate those values.

• With a cell containing a value you want to copy selected, click Copy.
• Click the cell where you want to paste the data.
• Click the Paste list arrow.
• Select Values.
• Paste Values

Excel pastes the values of the copied cell into the selected cell.

• Tip: Whenever you paste data, the Paste Options Smart Tag appears next to the selected cell. You can also paste values by clicking this tag and selecting a paste option from the list.

Paste Formats:

You can also copy and paste cell formatting, applying formats such as a cell’s font, font size, and color to other cells.

• Select the cell that has the format you want to copy.
• Click the Copy button.
• Click the cell where you want to paste the format.
• Click the Paste list arrow.
• Select Formatting.
• Paste Formats

Excel applies the formatting of the copied cell to the selected cell.

Advanced Paste Special Commands:

If the paste commands you’re looking for aren’t available in the Paste menu, you can open the Paste Special dialog box to view advanced paste special commands.

• Copy a cell value.
• Click the cell where you want to paste the value.
• Click the Paste list arrow.
• Select Paste Special.
• The Paste Special dialog box displays, containing numerous paste options.
• Select a paste option.
• Click OK.

By : Chitta Ranjan Sahoo

Sorting:

Sorting is one of the most frequent task we do in Excel. Sorting lets you re-arrange data in alphabetical order, lowest to highest, highest to lowest, and even by cell color. We are used to having data arranged in a particular order — A to Z, January to December, 1 to 10, and so on. Below is an example of a table that has its records haphazardly arranged. First, let’s have the data sorted by State alphabetically. Below are the recommended steps to sorting a table. Select the table, go to the Home menu and click on Sort & Filter.

Filtering:

Filter is one of the Excel power user’s most used tool. It allows you selectively choose what you want to view in a table and hide the rest. It is very easy to access and can be accessed from three different places in Excel.

1.By right clicking and selecting Filter. 2.From the Home menu, clicking on Sort & Filter at the right. 3.From the Data menu. 4.Once you’ve turned on the Filter tool by clicking on it, you will see a dropdown box beside the headers of the table. 5.Clicking on the dropdown box shows you all the unique items in that field and you can select the ones you want to view (hiding the rest). By default, all items are selected so you will have to unselect the ones you don’t want to see.

Data Cleaning:

A lot of times the data you are given to work on in Excel is not in a format usable for you and need some cleaning before you can go ahead with the analysis you intended doing on it. In most cases you have to manually clean the data and fix whatever issues it has one by one before progressing with the original analysis you intended to do on the data.Fortunately, Excel has some nifty tools to help you automate some of this data cleaning process. The most common ones are,

1. Removing duplicates
2. Text to Columns

Removing Duplicates:

Occasionally, you will have a table and you’ll want to remove duplicate entries. If it were a sales transaction table, you might want to remove the duplicate sales entries.

1. Select the cells you wish to remove duplicates from.
2. Click on the “Data” tab at the top.
3. Click “Remove Duplicates” to reveal a pop-up.
4. Uncheck any columns with data you want to keep.
5. Click OK to delete the duplicates.

Text to Columns:

There will be times you will have data you would prefer split across multiple columns squeezed into one column. This happens a lot when you copy data from an external source into Excel or you open an exported data from other business software like CRMs and ERPs.

Excel’s Text to Columns tool is the magic tool for splitting such data entries into multiple columns provided there is a recognizable character separating each field or they have fixed lengths per field. Below is a simple example for splitting a full name in one column to first Name column and last Name field column.

Delimited is the option to specify that there is a recognizable character separating each field.

1. Select the cell or column that contains the text you want to split.
2. Select Data > Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited > Next.
4. Select the Delimiters for your data. For example, Comma and Space. You can see a preview of your data in the Data preview window.
5. Select Next.
6. Select the Destination in your worksheet which is where you want the split data to appear.
7. Select Finish.

Data Validation:

This is another secret but powerful tool in Excel. It helps you put in place some error check mechanism and can be used by a skilled Excel user to make powerful Excel dashboards.

1. Any Value. This is the default and it is same as not having any data validation. The user can enter any value into the cell.
2. Whole Number. This forces the user to enter only numeric values that are whole numbers. If the user enters a text or decimal entry he’ll get an error. This might be applicable in an invoice sheet, for the cells that hold the order quantity if you don’t sell fraction of your products.
3. Decimal. This forces the user to enter a whole number or decimal entry. A whole number is same as a decimal with zeros after the decimal point. This might be great in a financial model sheet, to hold values of growth assumption, exchange rates and risk premiums.
4. List. This is the one we are most interested in. It enables us to limit the cell entries to a list of options. We will use this in the example under review.
5. Date. This forces the user to enter a valid date entry.
6. Time. This forces the user to enter a valid time entry.
7. Text Length. This allows the user to enter any value as long as the character length is not more than the specified value here. It is good for fields that hold phone numbers, maybe you want to limit the entry to the +2348123456789 14 characters long entry format.
8. Custom. Just as the name specifies, you want to limit the cell entry to something less conventional and not covered by the other options. In this example we are going to use the List option. So let’s select it. Once you click the icon on the far right corner of the Source box, select entries to limit the users to. In this case we have typed out the list options in cells G2:G6, holding the different departments. And it’s done. The user is forced to choose between the options in the list. He even sees a dropdown arrow that expands to a dropdown lists the moment he tries to fill the cell. And that is how Data Validation works. When used creatively it can save you from the stress of making corrections to forms people filled and can be used in conjunction with formulas like VLOOKUP to make a dynamic report and dashboards.

Data Formatting:

There are some quick tips in Excel that would turn a bland looking data into a nice looking one. One of the best tip is to apply a table formatting to the data. An example, is taking a table like the one below and turning it in a well formatted one. Select the data and go to Home menu, Format as Table. Choose a color theme.

By : Chitta Ranjan Sahoo

In this blog we will understand about operators .

In Python, Operators in general are used to perform operations on values and variables. These are standard symbols used for the purpose of logical and arithmetic operations. In this article, we will look into different types of Python operators.

• OPERATORS: These are the special symbols. Eg - + , * , /, etc.
• OPERAND: It is the value on which the operator is applied.

Types of Operators in Python

1. Arithmetic Operator
2. Comparison Operator
3. Logical Operator
4. Bitwise Operator
5. Assignment Operator
6. Identity Operators and Membership Operators

Python Operators Precedence Rule — PEMDAS

Operator precedence in python follows the PEMDAS rule for arithmetic expressions. The precedence of operators is listed below in a high to low manner.

Firstly, parantheses will be evaluated, then exponentiation and so on.

• P — Parentheses
• E — Exponentiation
• M — Multiplication
• D — Division
• A — Addition
• S — Subtraction

In the case of tie means, if two operators whose precedence is equal appear in the expression, then the associativity rule is followed.

Associativity Rule

All the operators, except exponentiation(**) follow the left to right associativity. It means the evaluation will proceed from left to right, while evaluating the expression.

Example- (45+13−9/3∗7)

In this case, the precedence of multiplication and division is equal, but further, they will be evaluated according to the left to right associativity.

Let’s try to solve this expression by breaking it out and applying the precedence and associativity rule.

• Interpreter encounters the parenthesis (. Hence it will be evaluated first.
• Later there are four operators +, −, ∗ and /.
• Precedence of (/ and ∗)> Precedence of (+,−).
• We can use the operator precedence only if all operators belong to different-different levels from the hierarchy table, which is not the case in our example.
• Associativity rule will be followed for operators with the same precedence.
• This expression will be evaluated from left to right, 9/3∗7 = 3∗7= 21.
• Now, our expression has become 45+13−21.
• Left to right Associativity rule will be followed again. So, our final value of expression will be, 45+13−21 = 58−21 = 37.

Examples of Operator Precedence in Python

Below are two examples to illustrate the operator precedence in python. See the explanation to get a good idea of how these things work internally.

1. Precedence of Operators

a = (10 + 12 * 3 % 34 / 8)
b = (4 ^ 2 << 3 + 48 // 24)

print (a)
print (b)

Output:

10.25
68

2. Associativity Rule of Operators

a = (24 ** 2 // 4 % 25 / 19 * 8)
b = (4 << 8 >> 2)
c = (3 ** 2 ** 4)

print (a)
print (b)
print (c)

Output:

8.0
256
43046721

Conclusion

• The operator works on operands according to their specific order while evaluating the expression. This hierarchy is called operator precedence in python.
• In case of the same precedence, the left to right associativity is followed. But the exponentiation operator is an exception that follows right to left associativity.

Arithmetic Operators in Python -

Python Arithmetic Operator are used to perform basic mathematical operations like addition, subtraction, multiplication, and division.

In Python 3.x the result of division is a floating-point while in Python 2.x division of 2 integers was an integer. To obtain an integer result in Python 3.x floored (// integer) is used.

Operator Description Syntax

• Addition: adds two operands x + y
• –Subtraction: subtracts two operands x — y
• *Multiplication: multiplies two operands x * y
• /Division (float): divides the first operand by the second x / y
• //Division (floor): divides the first operand by the second x // y
• %Modulus: returns the remainder when the first operand is divided by the second x % y
• **Power: Returns first raised to power second x ** y

Example of Arithmetic Operators in Python

# Arithmetric Operators
print(5+6)

print(5-6)

print(5*6)

print(5/2)

print(5//2)

print(5%2)

print(5**2)

Output:
11
-1
30
2.5
2
1
25

There are two types of division operators:

1. Float division
2. Floor division

Float division

The quotient returned by this operator is always a float number, no matter if two numbers are integers.

For example:

# python program to demonstrate the use of "/"
print(5/5)
print(10/2)
print(-10/2)
print(20.0/2)

Output:
1.0
5.0
-5.0
10.0

Integer division( Floor division)

The quotient returned by this operator is dependent on the argument being passed. If any of the numbers is float, it returns output in float. It is also known as Floor division because, if any number is negative, then the output will be floored.

For example:

# python program to demonstrate the use of "//"
print(10//3)
print (-5//2)
print (5.0//2)
print (-5.0//2)

Output:
3
-3
2.0
-3.0

Precedence of Arithmetic Operators in Python

The precedence of Arithmetic Operators in python is as follows:

1. P — Parentheses
2. E — Exponentiation
3. M — Multiplication (Multiplication and division have the same precedence)
4. D — Division
5. A — Addition (Addition and subtraction have the same precedence)
6. S — Subtraction

Relational Operators -

Relational operators are used for comparing the values. It either returns True or False according to the condition. These operators are also known as Comparison Operators.

1. Greater than: This operator returns True if the left operand is greater than the right operand.
2. Less than: This operator returns True if the left operand is less than the right operand.
3. Equal to: This operator returns True if both the operands are equal i.e. if both the left and the right operand are equal to each other.
4. Not equal to: This operator returns True if both the operands are not equal.
5. Greater than or equal to: This operator returns True if the left operand is greater than or equal to the right operand.
6. Less than or equal to: This operator returns True if the left operand is less than or equal to the right operand.

# Relational Operators
print(4>5)

print(4<5)

print(4>=4)

print(4<=4)

print(4==4)

print(4!=4)

False
True
True
True
True
False

Logical Operators -

In Python, Logical operators are used on conditional statements (either True or False). They perform Logical AND, Logical OR and Logical NOT operations.

# Logical Operators
print(1 and 0)

print(1 or 0)

print(not 1)

Output
0
1
False

Logical AND operator

Logical operator returns True if both the operands are True else it returns False.

# Python program to demonstrate
# logical and operator

a = 10
b = 10
c = -10

if a > 0 and b > 0:
 print("The numbers are greater than 0")

if a > 0 and b > 0 and c > 0:
 print("The numbers are greater than 0")
else:
 print("Atleast one number is not greater than 0")

Output
The numbers are greater than 0
Atleast one number is not greater than 0

Logical OR operator

Logical or operator returns True if either of the operands is True.

# Python program to demonstrate
# logical or operator

a = 10
b = -10
c = 0

if a > 0 or b > 0:
 print("Either of the number is greater than 0")
else:
 print("No number is greater than 0")

if b > 0 or c > 0:
 print("Either of the number is greater than 0")
else:
 print("No number is greater than 0")

Output
Either of the number is greater than 0
No number is greater than 0

Logical not operator

Logical not operator work with the single boolean value. If the boolean value is True it returns False and Vice Versa.

# Python program to demonstrate
# logical not operator

a = 10
if not a:
 print("Boolean value of a is True")
if not (a%3 == 0 or a%5 == 0):
 print("10 is not divisible by either 3 or 5")
else:
 print("10 is divisible by either 3 or 5")

Output
10 is divisible by either 3 or 5

Bitwise Operator -

Computers store all kinds of information as a stream of binary digits called bits. Whether you’re working with text, images, or videos, they all boil down to ones and zeros. Python’s bitwise operators let you manipulate those individual bits of data at the most granular level.

In Python, bitwise operators are used to perform bitwise calculations on integers. The integers are first converted into binary and then operations are performed on each bit or corresponding pair of bits, hence the name bitwise operators. The result is then returned in decimal format.

1. Bitwise AND operator Returns 1 if both the bits are 1 else 0.

a = 10 = 1010 (Binary)
b = 4 =  0100 (Binary)

a & b = 1010
         &
        0100
      = 0000
      = 0 (Decimal)

2. Bitwise Or operator Returns 1 if either of the bit is 1 else 0.

a = 10 = 1010 (Binary)
b = 4 =  0100 (Binary)

a | b = 1010
         |
        0100
      = 1110
      = 14 (Decimal)

Bitwise or operator Returns 1 if either of the bit is 1 else 0.

Bitwise not operator: Returns one’s complement of the number.

x = ~a
x = -(a+1)

x = ~5
  = -6

Bitwise Xor operator: Returns 1 if one of the bits is 1 and the other is 0 else returns false.

a = 10 = 1010 (Binary)
b = 4 =  0100 (Binary)

a ^ b = 1010
         ^
        0100
      = 1110
      = 14 (Decimal)

Shift Operators -

These operators are used to shift the bits of a number left or right thereby multiplying or dividing the number by two respectively. They can be used when we have to multiply or divide a number by two.

Bitwise right shift: Shifts the bits of the number to the right and fills 0 on voids left( fills 1 in the case of a negative number) as a result. Similar effect as of dividing the number with some power of two.

x = a>>n
x = a//2^n

a = 10 = 0000 1010 (Binary)
a >> 1 = 0000 0101 = 5

a = -10 = 1111 0110 (Binary)
a >> 1 = 1111 1011 = -5

Bitwise left shift: Shifts the bits of the number to the left and fills 0 on voids right as a result. Similar effect as of multiplying the number with some power of two.

x = a<<n
x = a x 2^n

a = 5 = 0000 0101 (Binary)
a << 1 = 0000 1010 = 10
a << 2 = 0001 0100 = 20

b = -10 = 1111 0110 (Binary)
b << 1 = 1110 1100 = -20
b << 2 = 1101 1000 = -40

# Bitwise Operators

# bitwise and
print(2 & 3)

# bitwise or
print(2 | 3)

# bitwise xor
print(2 ^ 3)

print(~3)

print(4 >> 2)

print(5 << 2)

2
3
1
-4
1
20

Assignment Operators -

Python has an assignment operator that helps to assign values or expressions to the left-hand-side variable. The assignment operator is represented as the “=” symbol used in assignment statements and assignment expressions. In the assignment operator, the right-hand side value or operand is assigned to the left-hand operand.

Following are the different types of assignment operators in Python:

1. Simple assignment operator ( = )
2. Add and equal operator ( += )
3. Subtract and equal operator ( -= )
4. Asterisk and equal operator ( *= )
5. Divide and equal operator ( /= )
6. Modulus and equal operator ( %= )
7. Double divide and equal operator ( //= )
8. Exponent assign operator ( **= )
9. Bitwise And Operator ( &= )
10. Bitwise OR Operator ( |= )
11. Bitwise XOR Assignment Operator ( ^= )
12. Bitwise right shift assignment operator ( >>= )
13. Bitwise left shift assignment operator ( <<= )

# Assigning values using
# Assignment Operator

a = 3
b = 5

c = a + b

# Output
print(c)


8

a = 3
b = 5

# a = a + b
a += b

# Output
print(a)

8

same for substraction, multiplication and division 

a = 3
b = 5

# a = a % b
a %= b

# Output
print(a)

3

a = 3
b = 5

# a = a // b
a //= b

# Output
print(a)

0

a = 3
b = 5

# a = a ** b
a **= b

# Output
print(a)

243

Bitwise Operators we already covered above.

Membership Operators -

Python offers two membership operators to check or validate the membership of a value. It tests for membership in a sequence, such as strings, lists, or tuples.

in operator: The ‘in’ operator is used to check if a character/ substring/ element exists in a sequence or not. Evaluate to True if it finds the specified element in a sequence otherwise False.

‘not in’ operator- Evaluates to true if it does not finds a variable in the specified sequence and false otherwise.

# Membership Operators

# in/not in

print('D' not in 'Delhi')

print(1 in [2,3,4,5,6])

False
False

Identity operators

Identity operators are used to compare the objects if both the objects are actually of the same data type and share the same memory location.
There are different identity operators such as

‘is’ operator — Evaluates to True if the variables on either side of the operator point to the same object and false otherwise.

# 'is' identity operator
x = 5
y = 5
print(x is y)

True

‘is not’ operator: Evaluates True if both variables are not the same object.

#is not
print(x is not y)
#This returns true as x and y are not the same objects even though they have the same content.

print(type(z) is not list)
#This returns false as z is indeed list x.