Sql interview questions and answers for experienced pdf

The role of the unique key is to make sure that each column and row are unique. The syntax will be the same as the Primary key. So, the query using a Unique Key for the Employee table will be:. Both Primary and Unique key carry unique values but the primary key can not have a null value where the Unique key can.

And in a table, there cannot be more than one Primary key but unique keys can be multiple. A foreign key is an attribute or a set of attributes that references to the primary key of some other table. Basically, it is used to link together two tables. Indexes help speed up searching in the database. If there is no index on any column in the WHERE clause, then SQL Server has to skim through the entire table and check each and every row to find matches, which might result in slow operation on large data.

Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches. Composite-column Indexes: A composite-column index is an index created for two or more columns of the table. Unique Indexes: Unique indexes are used for maintaining the data integrity of the table. They do not allow multiple values to be inserted into the table. Entities: Entity can be a person, place, thing, or any identifiable object for which data can be stored in a database.

Relationships: Relationships between entities can be referred to as the connection between two tables or entities.

For example: In a college database, the student entity and department entities are associated with each other.

That is all in the section of Basic SQL practice questions. The FLOOR function helps us to find the largest integer value to a given number which can be an equal or lesser number. Clustered index : It is used to sort the rows of data by their key values.

A clustered index is like the contents of a phone book. Since the data is located next to each other, it helps a lot in fetching data based on range-based queries. Also, the clustered index is actually related to how the data is stored. There is only one clustered index possible per table. Non-clustered index : It stores data at one location and indexes at some other location. The index has pointers that point to the location of the data. As the index in the non-clustered index is stored in different places, there can be many non-clustered indexes for a table.

Now, we will see the major differences between clustered and non-clustered indexes:. CDC is called change data capture. It records changes to SQL Server tables in a compatible format.

SQL MySQL It is a structured query language used in a database It is a database management system It is used for query and operating database system It allows data handling, storing, and modifying data in an organized manner. MySQL supports multiple storage engines. The server is independent in SQL During backup sessions, the server blocks the database. It is a programming language for a database that uses SQL.

SQL is a declarative and data-oriented language. It is mainly used for the manipulation of data. It is used for creating an application.

It provides interaction with the database server. It does not provide interaction with the database server. To check the reliability of the transactions, ACID properties are used.

Group functions operate on a series of rows and return a single result for each group. The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands like insert, update, and delete are given. Normalization is used in reducing data redundancy and dependency by organizing fields and tables in databases.

It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make it more flexible. Denormalization is contrary to normalization.

In this, we basically add redundant data to speed up complex queries involving multiple tables to join. Here, we attempt to optimize the read performance of a database by adding redundant data or by grouping the data.

The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given. The WHERE clause defines the parameters that would be used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects. This GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions.

A function is an SQL Server database object. It is basically a set of SQL statements that allow input parameters, perform processing, and return results only. The function can only return a single value or table. The ability to insert, update, and delete records in database tables is not available. The stuff function deletes a part of the string and then inserts another part into the string starting at a specified position. Here, String1 is the one that would be overwritten. Position indicates the starting location for overwriting the string.

Length is the length of the substitute string, and String2 is the string that would overwrite String1. Views are virtual tables used to limit the tables that we want to display, and these are nothing but the result of a SQL statement that has a name associated with it. Since views are not physically present, they take less space to store.

We can create a view-only table for the female employees from the entire employee table. A stored procedure is a prepared SQL code that can be saved and reused.

In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system. SQL is the most popular data management query language. In addition, it supports relational data, which helps to manage huge amount of data with required relations. You can refer the following data tables for examples. Here, we can use Self-Join as the requirement wants us to analyze the StudentDetails table as two different tables, each for Student and enrollment records.

In the mentioned below query first, we fetch the project-wise count and then sort the result by count. SQL queries to find the nth highest stipend form the table for various Databases are as described below. Meeting all the requirements of third normal form and it should not have multi- valued dependencies. A view is a virtual table which consists of a subset of data contained in a table.

Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship. An index is performance tuning method of allowing faster retrieval of records from the table.

An index creates an entry for each value and it will be faster to retrieve data. This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index. NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have nonclustered indexes. A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.

A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database. A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.

A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query. A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query. Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database. Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables. Commit and Rollback can be performed after delete statement.

Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called. Global variables are the variables which can be used or exist throughout the program.

Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called. Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are. Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.

Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored.