Sql Coding Interview Questions For Experienced

Differentiate: Char And Varchar Data Types In Sql

What Is A Tablesample

Ans: This is the SQL statement that allows extracting random samples from a table using FROM statements. When users dont require the entire dataset of a table, this statement can be applied to extract only the necessary samples. Note that the extracted rows won’t be in any order, and sampling can be performed based on the percentage of rows.

Sql Coding Interview Questions

Consider the student table as shown below for question no. 1, 2, and 3.

Q1. Write a query to extract username from the Email_ID column.

Answer:

SELECT SUBSTR - 1) FROM STUDENT 

Extract the position of @ from the email id first using INSTR function then pass this position as an argument for length in SUBSTR function.

Output

Q2. Write a query to extract domain name like .com, .in, .au etc. from the Email_ID column.

Answer:

SELECT SUBSTR) FROM STUDENT 

Extract the position of . from the email id first using INSTR function then pass this position as an argument for starting position in SUBSTR function.

Output

Q3. Write a query to extract email service provider names like google, yahoo, outlook, etc. from the Email_ID column.

Answer:

SELECT SUBSTR + 1, INSTR - INSTR - 1) FROM STUDENT 

Extract the position of @ from the email id first using INSTR function, and pass it as an argument for starting position in SUBSTR function.

Now extract this position of . and subtract it from the earlier extracted @ position and pass it as an argument for length in SUBSTR function.

Output

Q4. What is the output of the following query?

SELECT CEIL, FLOOR FROM DUAL 

a. -13, -12

Q5. Write a query to extract all the consonants present in your name.

Answer:

SELECT TRANSLATE FROM DUAL 

Output

Q6. Write a query to extract all the vowels present in your name.

Answer:

SELECT TRANSLATE, 'a') FROM DUAL  

Output

Q7. Write a query to extract the employees details who joined in the year 1981.

You May Like: Workforce Management Analyst Interview Questions

What Do You Mean By Er Diagram

Ans: The ER diagram is known as the Entity-Relationship diagram. This diagram shows the visual representation of the structure of tables in databases and their logical relationships. ER Diagram displays table structures with column names and the associated data types, primary and foreign keys used, and relationships between the tables.

Xml In Sql Interview Questions And Answers

Q. What is XDR?

A. In Microsoft® SQL Server⢠2000, the XML-Data Reduced language is used to create the schemas. The XDR is flexible and overcomes some of the limitations of the Document Type Definition , which also describes the document structure. Unlike DTDs, XDR schemas describe the structure of the document using the same syntax as the XML document. Additionally, in a DTD, all the data contents are character data. XDR language schemas allow you to specify the data type of an element or an attribute.

Q. What is the difference between FOR AUTO and FOR NESTED?

A. The NESTED mode of the client-side FOR XML is similar to the AUTO mode of the server-side FOR

Q. What is the difference between FOR XML RAW and FOR XML AUTO?A. RAW: Takes the query result and transforms each row in the result set into an XML element with a generic identifier as the element tag.

Recommended Reading: How To Create A Presentation For An Interview

Explain The Types Of Sql Joins

There are four different types of SQL Joins:

• Join: It is used to retrieve the records that have matching values in both the tables that are involved in the join. Inner Join is mostly used to join queries.

  SELECT *FROM Table_AJOIN Table_B SELECT *FROM Table_AINNER JOIN Table_B 

• Left Join: Use of left join is to retrieve all the records or rows from the left and the matched ones from the right.

  SELECT *FROM Table_A ALEFT JOIN Table_B BON A.col = B.col 

• Right Join: Use of Right join is to retrieve all the records or rows from the right and the matched ones from the left.

  SELECT *FROM Table_A ARIGHT JOIN Table_B BON A.col = B.col 

• Full Join: The use of Full join is to retrieve the records that have a match either in the left table or the right table.

  SELECT *FROM Table_A AFULL JOIN Table_B BON A.col = B.col 

Get 100% Hike!

Master Most in Demand Skills Now !

Prepare Sample Data To Practice Sql Skill

Sample Table Worker

To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. Weve tested them with MySQL Server 5.7 and MySQL Workbench 6.3.8 query browser. You can also download these Softwares and install them to carry on the SQL exercise.

SQL Script to Seed Sample Data.

CREATE DATABASE ORG SHOW DATABASES USE ORG CREATE TABLE Worker ,LAST_NAME CHAR,SALARY INT,JOINING_DATE DATETIME,DEPARTMENT CHAR) INSERT INTO Worker  VALUES,,,,,,, CREATE TABLE Bonus ,BONUS_DATE DATETIME,FOREIGN KEY REFERENCES Worker        ON DELETE CASCADE) INSERT INTO Bonus  VALUES,,,, 

CREATE TABLE Title ,AFFECTED_FROM DATETIME,FOREIGN KEY REFERENCES Worker        ON DELETE CASCADE) INSERT INTO Title  VALUES , , , , , , ,  

Once above SQL would run, youll see a result similar to the one attached below.

Creating Sample Data to Practice SQL Skill.

Read Also: How To Interview With Amazon

What Do You Understand By Database And What Does It Have

A database can be defined as the structured form of data storage from which data can be retrieved and managed based on requirements. Basically, a database consists of tables where data is stored in an organized manner. Each table consists of rows and columns to store data. Data can be stored, modified, updated, and accessed easily in a database. For instance, a bank management database or school management database are a few examples of databases.

What Do You Mean By Lock Escalation

It is the process of converting row and page locks into table locks. Know that Reduction of lock escalation would increase the server performance. To improve performance, we need to keep transactions short and reduce lock footprints in queries as low as possible. Besides, we can disable lock escalation at the table and instance levels, but it is not recommended.

Also Check: What Questions They Ask In A Job Interview

What Is Data Manipulation Language

DML or Data Manipulation Language is a set of commands that are classified pertaining to its capability to give users permission to change entries within the database. This may be through Inserting, Retrieving, Deleting or Updating data within tables. Popular DML statements arise from these core functionalities and are listed below:

• SELECT â used to highlight a row within a table and retrieve it.

  SELECT  FROM 

• DELETE â used to delete entries from an existing table

  DELETE FROM 

Explain The Types Of Indexes

Single-column Indexes: A single-column index is created for only one column of a table.

Syntax:

CREATE INDEX index_nameON table_name 

Composite-column Indexes: A composite-column index is created for two or more columns of a table.

Syntax:

CREATE INDEX index_nameON table_name 

Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.

Syntax:

CREATE UNIQUE INDEX indexON table_name

Courses you may like

Also Check: Amazon Business Intelligence Engineer Interview

What Do You Mean By Data Inconsistency

Data inconsistency occurs when the same data exists in many tables in different formats. In other words, the same information about an object or person may be spread across the database in various places creating duplication. It decreases the reliability of the data and decreases the query performance significantly. To overcome this drawback, we can use constraints on the database.

What Is The Use Of Sql Server Locks And What Resources Can Be Locked By Server Locks

Ans: If an exclusive lock locks a row in a table during a specific transaction, that row cannot be accessed by other transactions. So, others can view or modify that row only when the lock is released. Also, locks reduce concurrency in transactions, so locks must be minimized to improve transaction performance.

Note that the resources such as AllocUnit, application, database, extent, file, key, metadata, etc., can be locked using lock functions.

You May Like: How To Test For Attention To Detail In An Interview

Q73 What Is The Acid Property In A Database

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.

• Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
• Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
• Isolation: The main goal of isolation is concurrency control.
• Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

Q20 What Is The Difference Between The Rank And Dense: Rank Functions

The RANK function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.

The DENSE_RANK function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.

Q21. What are Tables and Fields?

A table is a collection of data components organized in rows and columns in a relational database. A table can also be thought of as a useful representation of relationships. The most basic form of data storage is the table. An example of an Employee table is shown below.

A Record or Row is a single entry in a table. In a table, a record represents a collection of connected data. The Employee table, for example, has four records.

A table is made up of numerous records , each of which can be split down into smaller units called Fields. ID, Name, Department, and Salary are the four fields in the Employee table above.

Read Also: How To Prepare For Technical Interview Software Engineer

What Are The Differences Between Oltp And Olap

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An important attribute of an OLTP system is its ability to maintain concurrency. OLTP systems often follow a decentralized architecture to avoid single points of failure. These systems are generally designed for a large audience of end-users who conduct short transactions. Queries involved in such databases are generally simple, need fast response times, and return relatively few records. A number of transactions per second acts as an effective measure for such systems.

OLAP stands for Online Analytical Processing, a class of software programs that are characterized by the relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. For OLAP systems, the effectiveness measure relies highly on response time. Such systems are widely used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.

Basic Sql Server Interview Questions And Answers

Q. What are the types of relationships between tables?

A. There are three possible types of relationships between tables. The type of relationship depends on how the related columns are defined. The three types of relationships are:

One-to-Many Relationships

One-to-One Relationships

Q. What is One-to-Many Relationships?

A. A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in Table A can have many matching rows in Table B, but a row in Table B can have only one matching row in Table A. For example, the Sales and Stores tables have a one-to-many relationship with each other: each Store can have many sales, but each sale can have only one store. This relationship uses the Stor_ID field for its key field.

Q. What is Many-to-Many Relationships?

A. In a many-to-many relationship, a row in Table A can have many matching rows in Table B, and vice versa. In SQL Server, you create such a relationship by defining a third table, called a âjunction tableâ, whose primary key consists of the foreign keys from both Table A and Table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the Titleauthors table. The primary key of the Titleauthors table is the combination of the au_id column and the title_id column .

Q. What is One-to-One Relationships?

Q. What is a subquery?

USE northwind

SELECT * FROM Orders WHERE orderId IN

Also Check: Where Can I Watch The Oprah Interview

How To Delete A Table In Sql

There are two ways to delete a table from SQL: DROP and TRUNCATE. The DROP TABLE command is used to completely delete the table from the database. This is the command:

DROP TABLE table_name 

The above command will completely delete all the data present in the table along with the table itself.

But if we want to delete only the data present in the table but not the table itself, then we will use the truncate command:

DROP TABLE table_name  

How Can You Differentiate The Rank And Dense: Rank Functions

Both RANK and DENSE_RANK are used as the ranking functions, which perform ranking of data based on specific conditions. When the RANK statement is executed, it returns a ranking of values of a table based on specific conditions. At the same time, the result set up skip positions in the ranking if there are the same values. Simply put, there will be a discontinuity in the numbering of ranking. On the other hand, when the RANK_DENSE function is executed, it doesnt skip any position in the ranking of values even though there are the same values present in the table. It returns continuous numbering of ranking.

The following example will explain the use of the RANK and DENSE_RANK functions.

Recommended Reading: How To Prepare For Data Science Interview

How Can You Create Empty Tables With The Same Structure As Another Table

This can be achieved by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. In this way, SQL prepares the new table with a duplicate structure to accept the fetched records. However, there are no records that will get fetched due to the WHERE clause in action. Therefore, nothing is inserted into the new table, thus creating an empty table.

SELECT * INTO Students_copyFROM Students WHERE 1 = 2 

Compare The Difference Between Clustered Index And The Non

Ans:

Read Also: How To Give Candidate Feedback After Interview

Sql Server Dba Interview Questions

Q. How you can get a list of all the table constraints in a database?

A. SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.

See the question on ANSI SQL Schema view for detail.

SQL Server Administration Interview Questions

Q. How you can get the list of largest tables in a database?

A. System table âsysindexesâ has all the information, you need.

SELECT object_Name , dpages * 8FROM sysindexesWHERE indid in AND objectproperty = 1 ORDER BY rowcnt desc

Q. How you can move data or databases between servers and databases in SQL Server?

A. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, log shipping, INSERTâ¦SELECT, SELECTâ¦INTO, creating INSERT scripts to generate data.

Q. If no size is defined while creating the database, what size will the database have?A. The size of the model database determines the initial size of a database if no size is indicated in the CREATE DATABASE statement.

Q. Can a database be shrunk with users active?

A. Yes. Users can be working in the database when it is shrunk. This includes system databases.

Q. How can you set the database to single user mode and restrict the access to dbo use only?

A. In SQL Server 2000, a database cannot be in single-user mode with dbo use only. Instead, the following alternative options are available by using the ALTER DATABASE command:

ALTER DATABASE database SET SINGLE_USER.

This command restricts access to the database to only one user at a time.