Friday, January 27, 2023

Scenario Based Sql Interview Questions

Don't Miss

What Are The Points To Be Considered For Creating A Fast Performing Stored Procedure

SQL Interview Questions and answers Part 1 | SQL Scenario based Interview Question and Answer

The performance of any application largely depends upon the backend process which extracts data. If it is faster then the application will be also responsive. The designing of the stored procedure is very critical and need to be careful to remove any We can consider the following points while designing stored procedure :

  • Variables: We should minimize the use of variables as it is stored in the cache.
  • Dynamic queries: We should minimize the use of dynamic query as dynamic query gets recompiled every time parameter gets changed.
  • The stored procedure should be always called with fully qualified names database_name.schema_name.sp_name , this avoids the search of an execution plan in the procedure cache.
  • We should always use SET NOCOUNT ON which suppresses rows affected to improve the performance of the query. This is very critical in case SP is called frequently as not using the above syntax can load the network.
  • We should not use sp_ in stored procedure name as it is used for system procedure so it causes an extra trip to the master database to see if any system procedure matches with the user-defined procedure
  • We can use sp_executeSQL or KEEPFIXED PLAN to get away with recompilation of stored procedures even if we have parameterized dynamic queries.
  • We should be careful in choosing WHERE condition as it triggers index seek. Sometimes we might end up doing full table scans if were conditions not chosen carefully.
  • How Can Sql Server Instances Be Hidden

    When you launch SQL Server Management Studio , you see the choice to connect with a DB instance. Whenever wanted, you can browse instances running on your system. Simply click on the drop down and at the base, there is a Browse for more alternative:

    This enables you to look for local or network instances. I have four instances running on my workstation you see them showed in this list:

    We should expect one of these instances is top secret and we don’t need clients to see the instance name. That is conceivable through SQL Server Configuration Manager . Open up SSCM and explore to your SQL Server instances:

    select the instance of SQL Server, right click and select Properties. Subsequent to choosing properties you will simply set Hide Instance to “Yes” and click OK or Apply. After the change is made, you have to restart the instance of SQL Server to not uncover the name of the instance.

    What Is An Outer Join

    With an SQL OUTER JOIN, unmatched rows in one or both tables can be returned. There are several variations of the OUTER JOIN clause, some of which we have covered already in this article. Here are the common types of OUTER JOIN clauses:

    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN

    LEFT JOIN is a synonym for LEFT OUTER JOIN. The functionality of both is identical. This may be one of the SQL JOIN interview questions you are asked! The same can be said for RIGHT JOIN and RIGHT OUTER JOIN, and FULL JOIN and FULL OUTER JOIN. Letâs look at an example for each.

    Don’t Miss: Pre Recorded Video Interview Introduction

    Q26 What Are Union Minus And Intersect Commands

    The UNION operator is used to combine the results of two tables while also removing duplicate entries.

    The MINUS operator is used to return rows from the first query but not from the second query.

    The INTERSECT operator is used to combine the results of both queries into a single row.Before running either of the above SQL statements, certain requirements must be satisfied

    Within the clause, each SELECT query must have the same amount of columns.

    The data types in the columns must also be comparable.

    In each SELECT statement, the columns must be in the same order.

    Lets move to the next question in this SQL Interview Questions.

    What Exactly Is Log Shipping

    ODI Real time Scenario

    The process of backing up and restoring database and transaction log files from a production SQL server to a backup SQL server is known as log shipping.

    Enterprise Editions are the only ones that can send logs. Therefore, when log shipping is employed, the transaction log file from one server is updated automatically into the backup database on the other server.

    You May Like: What Are Some Good Interview Questions To Ask The Employer

    What Is A Database

    This question can be most probably asked among various other SQL interview questions A database refers to a structured collection of data that can be stored, maintained, and accessed digitally from a local or remote computer network. A fixed design and modeling technique is used to build databases, which can be large and complex. Large databases are housed on multiple computers or cloud services, whereas smaller databases can be stored on a file system.

    Get Software Engineering degrees from the Worlds top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

    What Is The Difference Between Having And Where Clauses

    The distinction between HAVING and WHERE clauses in SQL is that while the WHERE clause cannot be used with aggregates, the HAVING clause is used with the aggregated data. The WHERE clause works on the data from a row and not with the aggregated data.

    Let us consider the employee table below.

    Name
    150,000

    You May Like: What Are Some Interview Questions For Customer Service

    What Is Lock Escalation In Sql

    This is the strategy used by SQL server to avoid locking a large number of database resources. The locking on resource use lot of memory spaces. Let’s try to understand this by a use case where we have to apply locks on 30,000 records where each record size is 500 bytes for triggering DELETE operation. If we consider the memory space requirement for the above use case then we would be requiring one shared lock on database, one intent lock on a table, exclusive locks on the pages would be in the range of 1875 and around 30000 exclusive locks on rows. If we consider 96 bytes size for each lock then total memory space requirement would be 3MB for single delete operation.

    To avoid such a SQL server leverage lock escalation strategy. This will prevent the need of large memory space requirement by escalating those locks to single lock instead of many locks. So in cases where we need thousands of locks on many resources, lock escalation will ensure single lock which will fulfill our objective and at the same time taking care of memory space issue. The exclusive lock on the table will ensure that we do not need page level lock to ensure data integrity. Instead of having so many locks required on many columns and pages lock, SQL Server will escalate to the exclusive lock on a table.

    What Are Different Replication Agents And What’s Their Purpose

    Complex SQL 3 | Scenario based Interviews Question for Product companies
    • Snapshot Agent– The Snapshot Agent is used with all types of replication for the initial copy of an article from publisher to the subscriber. It generates schema copy and bulk copy files of published tables and other objects. Later same is applied to the subscriber. Snapshot Agent runs at the Distributor.
    • Log Reader Agent – Log Reader Agent is used with transactional replication. Its responsible for moving transactions marked for replication from the transaction log on the Publisher to the distribution database.
    • Distribution Agent – Distribution Agent is responsible for applying Snapshot & Transaction logs at subscriber moved to the distributor by Snapshot or Log Reader agent. Distribution Agent runs on Distributor for push subscriptions or at the Subscriber for pull subscriptions.
    • Merge Agent – Merge Agent is used by merge replication to move initial snapshot followed by movement and reconciliation of incremental data changes. Each merge subscription has its own Merge Agent works with both the Publisher and the Subscriber and updates both. Merge Agent runs on Distributor for push subscriptions or at the Subscriber for pull subscriptions.

    You May Like: How To Prepare For Mckinsey Interview

    How To Find Count Of Duplicate Rows

    Answer:

    Select rollno, count from Student

    Group by rollno

    Having count > 1

    Order by count desc

    4.How to find Third highest salary in Employee table using self-join?

    Answer:

    5.How to Show the Max marks and min marks together from student table?

    Answer:

    Select max from Student

    Union

    Select min from Student

    Tip : Use the concept of union to show the max and min marks together.

    6.How to display following using query?

    Answer:

    We cannot use dual table to display output given above. To display output use any table. I am using Student table.

    SELECT lpad FROM Student WHERE ROWNUM < 4

    7.How to display Date in DD-MON-YYYY table?

    Answer:

    Select to_date Date_Format from Employee

    8.If marks column contain the comma separated values from Student table. How to calculate the count of that comma separated values?

    Student Name

    Want to display output like :

    Student Name

    Answer:

    Select Student_name, regexp_count + As Marks Count from Student

    Tip: In real scenarios, lot of times developer needs to calculate the number of commas in the column then regexp_count function is used.

    9.How to create the Student_1 table, which is exact replica of Student table?

    Answer:

    Create Table Student_1 as select * from Student

    10.What is Query to drop all user tables from Oracle?

    Answer:

    To Drop all tables user needs to write simple PLSQL block

    Begin

    For I In

    Tabs is system table in which user get the different user defined table names.

    Loop

    Execute immediate

    End loop

    Answer:

    See I Have An Environment Sunday Night Full Backup Everyday Night Diff Backup And Every 45 Min A Transactional Backup The Disaster Happened At : 30 Pm On Saturday You Suddenly Found That The Last Sunday Backup Has Been Corrupted Whats Your Recovery Plan

    When you find that the last full backup is corrupted or otherwise unrestorable, making all differentials after that point useless. You then need to go back a further week to the previous full backup and restore that, plus the differential from 8 days ago, and the subsequent 8 days of transaction logs .

    If youre taking daily full backups, a corrupted full backup only introduces an additional 24 hours of logs to restore.

    Alternatively, a log shipped copy of the database could save your bacon .

    Read Also: What Are The Most Common Interview Questions And Answers

    If A Column Is Having> 50 % Null Then Which Index We Should Choose

    If a column in the table is having > 50% NULL values then index selection if very selective.

    Index schema is based on B-Tree structure and if the column is having more than 50% NULL values then all data will reside on one side of the tree result ZERO benefits of the index on query execution.

    The SQL server is having a special index called filtered index which can be used here. You can create an index on column only on NON NULL values. NULL data will not be included in the index.

    What Is Udf In Sql And How Many Different Types Exist For Udf

    ODI Real time Scenario

    UDF represents user-defined represents which are designed to accept the parameter and does processing on parameter before returning a result set of processing or actions. The result could be either a scalar value or a complete result set. The UDFs are widely used in scripts, stored procedures and within others UDF as well.

    There are several benefits of using UDF :

  • UDF is designed keeping in mind modular programming. Once you have created UDF, we can call the UDF multiple times and it is not dependent on source code so we can easily modify it.
  • UDF is designed to reduce compilation cost by caching plan so we can reuse them without compiling it.
  • The WHERE clause is considered as an expensive operation so if we have some filter on complex constraints we can easily create UDF for the same and same UDF can be replaced in WHERE clause.
  • There are two types of UDF based on the output value of UDFs

    • The scalar functions-The output of this function is scalar values. It may accept zero or more parameters and can return any SQL defined data type other than text, ntext, image, cursor, and timestamp.
    • Table values functions- This type of function return table as resultset. It can accept zero or more parameters but always return table.

    Also Check: What Are Great Interview Questions

    What Are The Types Of Views In Sql

    In SQL, the views are classified into four types. They are:

    • Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features.
    • Complex View: A view that is built from several tables and includes a GROUP BY clause as well as functions.
    • Inline View: A view that is built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
    • Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.

    What Are The Subsets Of Sql

    SQL queries are divided into four main categories:

    • Data Definition Language DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.
    • CREATE Creates databases, tables, schema, etc.
    • DROP: Drops tables and other database objects
    • DROP COLUMN: Drops a column from any table structure
    • ALTER: Alters the definition of database objects
    • TRUNCATE: Removes tables, views, procedures, and other database objects
    • ADD COLUMN: Adds any column to the table schema
  • Data Manipulation Language These SQL queries are used to manipulate data in a database.
  • SELECT INTO: Selects data from one table and inserts it into another
  • INSERT: Inserts data or records into a table
  • UPDATE: Updates the value of any record in the database
  • DELETE: Deletes records from a table
  • Data Control Language These SQL queries manage the access rights and permission control of the database.
  • GRANT: Grants access rights to database objects
  • REVOKE: Withdraws permission from database objects
  • Transaction Control Language TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.
  • COMMIT: Commits an irreversible transaction, i.e., the previous image of the database prior to the transaction cannot be retrieved
  • ROLLBACK: Reverts the steps in a transaction in case of an error
  • SET TRANSACTION: Sets the characteristics of the transaction
  • Read Also: How To Write A Email Thank You After An Interview

    Q3 What Do You Mean By Dbms What Are Its Different Types

    A Database Management System is a software application that interacts with the user, applications, and the database itself to capture and analyze data. A database is a structured collection of data.

    A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images, etc.

    There are two types of DBMS:

    • Relational Database Management System: The data is stored in relations . Example MySQL.
    • Non-Relational Database Management System: There is no concept of relations, tuples and attributes. Example MongoDB

    Lets move to the next question in this SQL Interview Questions.

    How To Change A Table Name In Sql

    SQL Interview Questions and answers Part 7 | SQL Scenario based Interview Question and Answer

    This is the command to change a table name in SQL:

    ALTER TABLE table_nameRENAME TO new_table_name 

    We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.

    For example, if we want to change the employee table to employee_information, this will be the command:

    ALTER TABLE employeeRENAME TO employee_information 

    Don’t Miss: How To Prepare For Data Analyst Interview

    Q26 Write A Query To Display The First And The Last Record From The Employeeinfo Table

    To display the first record from the EmployeeInfo table, you can write a query as follows:

    SELECT * FROM EmployeeInfo WHERE EmpID =  FROM EmployeeInfo) 

    To display the last record from the EmployeeInfo table, you can write a query as follows:

    SELECT * FROM EmployeeInfo WHERE EmpID =  FROM EmployeeInfo) 

    Explain The Difference Between Sequence Vs Identity

    An identity column in the table has auto-generate & auto increase value with each new row insert. The user cannot insert value in the identity column.

    The sequence is a new feature introduced with SQL Server 2012 similar to Oracles sequence objects. A sequence object generates a sequence of unique numeric values as per the specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the NEXT VALUE FOR clause.

    • IDENTITY is column level property & tied to a particular table. This cannot be shared among multiple tables.
    • SEQUENCE is an object defined by the user with specific details and can be shared by multiple tables. This is not tied to any particular table.
    • IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to initial value any time.
    • Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE object.

    Read Also: What To Write In A Thank You Email After Interview

    What Are The Different Types Of Sql Server Replication

    • Snapshot replication Snapshot replication works on a snapshot of the published objects. Snapshot agent takes care of it and applies it to a subscriber. Snapshot replication overwrites the data at the subscriber each time a snapshot is applied. Its best suited for fairly static data or sync interval is not an issue. Subscriber does not always need to be connected.
    • Transactional replication Transactional replication replicates each transaction for the article being published. For initial setup, Snapshot or backup is required to copy article data at the subscriber. After that Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Its the most widely used replication.
    • Merge replication Merge replication is the most complex types of replication which allow changes to happen at both the publisher and subscriber. Changes happen at publisher & subscriber are merged to keep data consistency and a uniform set of data. For an initial setup like transactional replication, Snapshot or backup is required to copy article data at the subscriber. After that Merge Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. The merge agent is capable of resolving conflicts that occur during data synchronization.

    More articles

    Popular Articles