SQL Experienced interview questions/SQL Interview Questions and Answers for Freshers & Experienced

What is the role of GRANT and REVOKE commands?

The GRANT command enables privileges on the database objects and the REVOKE command removes them. They are DCL commands

GRANT sysdba TO username

What does the BCP command do?

The BCP (Bulk Copy) is a utility or a tool that exports/imports data from a table into a file and vice versa

What is a SYSTEM Privilege?

Rights are given to a user, usually by the DBA, to perform a particular action on the database schema objects like creating tablespaces.
The following are examples of system privileges that can be granted to users:
1. CREATE TABLE allows a grantee to create tables in the grantee's schema.
2. CREATE USER allows a grantee to create users in the database.
3. CREATE SESSION allows a grantee to connect to an Oracle database to create a user session.

What is a shared lock?

When two transactions are granted read access to the same data, they are given a shared lock. This enables reading the same data, and data is not updated until the shared lock is released.

In SQL, what is the best thing about the Views you have come across?

There are several good things about them. The very first thing is they consume almost no space which makes them good enough to be considered in every situation. At the same time, the users are able to consider views for simply retrieving the outcomes that belong to queries that are complicated in nature.

How do you sort records in a table?

The ORDER BY Clause is used to sort records in a table.

What is T-SQL?

It is an extension of SQL(Structured Query Language) developed by Sybase and used by Microsoft.

Write a Query to display employee details who is working in ECE department & who his having more than 3 years of exp?

DATEDIFF(yy, doj, getdate()) AS ‘Exp’
FROM employee
WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’

Write a Query to display the date after 12 months?

SELECT DATEADD(mm, 2, getdate())

Can we hide the definition of a stored procedure from a user?

YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.

Can we store Videos inside the SQL Server table?

we can store Videos inside SQL Server by using FILESTREAM data type, which was introduced in SQL Server 2008.

Can we store PDF files inside the SQL Server table?

YES, we can store this sort of data using a blob datatype

Why we use the OPEN XML clause?

OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data into the DB.

What is lock escalation?

A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.

What is a forward cursor?

Forward cursors support fetching of rows from start to end from a result set. You cannot go to the previous row in the result set.

What is a set-based solution?

Cursors operate on individual rows, and in the case of a set, it works on a resultant set of data, which could be a table/view or a join of both. The resultant set is an output of a SQL query.

How can we determine what objects a user-defined function depends upon?

sp_depends system stored procedure or query the says depends on system table to return a list of objects that a user-defined function depends upon
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so1.name = '<>'

In what sequence SQL statements are processed?

The clauses of the subselect are processed in the following sequence (DB2):
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
7. FETCH FIRST clause

How will you create a column alias?

The AS keyword is optional when specifying a column alias.

What is a live lock?

A livelock 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 livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What is the difference between CHAR and VARCHAR2 datatype in SQL?

CHAR is used to store fixed-length character strings, and VARCHAR2 is used to store variable-length character strings.

For example, suppose you store the string ‘Database’ in a CHAR(20) field and a VARCHAR2(20) field.
The CHAR field will use 22 bytes (2 bytes for leading length).
The VARCHAR2 field will use 10 bytes only (8 for the string, 2 bytes for leading length).

What do you mean by data manipulation language - DML?

DML includes the most common SQL statements to store, modify, delete, and retrieve data. They are SELECT, UPDATE, INSERT, and DELETE

What is the difference between Union and Union All command?

This is one of the tricky SQL Interview Questions. Interviewer may ask you this question in another way as what are the advantages of Union All over Union.

Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different.
Union: It omits duplicate records and returns only distinct result set of two or more select statements.
Union All: It returns all the rows including duplicates in the result set of different select statements.

What Is Cascade delete/update?

CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed.

What is the largest value that can be stored in a BYTE data field?

The largest number that can be represented in a single byte is 11111111 or 255. The number of possible values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).

How to avoid duplicate records in a query?

The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.

What are the popular Database Management Systems in the IT Industry?

Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,

What is the OSQL utility?

OSQL is a command-line tool that is used to execute the query and display the result the same as a query analyzer but everything is in command prompt.

Tell something about the Temp Table?

Commands that are used for the purpose of managing the data present in the database:-

1. Update
2. Insert

Can You Explain Integration Between SQL Server 2005 And Visual Studio 2005?

This integration provides a wider range of development with the help of CLR for database servers because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net.

The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is used in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors

What are user-defined data types and when you should go for them?

User-defined data types let you extend the base SQL Server data types 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 data type called Flight_num_type of varchar(8) and use it across all your tables.

Name a few commands which you think are important in SQL for managing the database?

You can answer these questions based on the commands you have used in your past if you having a bit of experience in SQL. Else, the following commands are there which are widely adopted and are very useful.

Data Definition Language
Transaction Control Language
Data Query Language
Data Manipulation Language
Data Control Language

In SQL, what do you know about the composite primary key?

The key which is created on multiple columns in a table is generally considered as the Composite primary key. However, it is not always necessary that all of them have the same meaning.

What is the difference between LEFT JOIN with WHERE clause & LEFT JOIN with nowhere clause?

OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

Can we write a distributed query and get some data that is located on another server and on Oracle Database?

SQL Server can be lined to any server provided it has an OLE-DB provider from Microsoft to allow a link.

For E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as a linked server to the SQL Server group.

How do you generate file output from SQL?

While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR.QUERTY >> RESULT TO >> Result to FILE

What is collation?

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.

What is Cross-Join?

Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.

What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.

1.Query to find Second Highest Salary of Employee?(click for explaination)

Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;

What is the difference between the WHERE and HAVING clauses?

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.
However, when GROUP BY is used:
The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.
In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.

How the Inner Join in SQL is different from that of Outer Join?

An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer join is the one that is useful for returning the value of rows and tables that generally include the records that must be the same in all the tables.

What do you know about a Database Management system?

It is basically a program that is considered when it comes to maintaining, creating, deploying, controlling as well as monitoring the use of a database. It can also be considered as a file manager which is good enough to be trusted for managing the data kept in a database than a file system.

What is SQL Server?

SQL Server is Microsoft's relational database management system (RDBMS). End-user cannot interact directly with the database server. If we want to interact with the SQL database server then we have to interact with SQL.

R4R Team
R4R provides SQL Freshers questions and answers (SQL Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,SQL Experienced interview questions,SQL Freshers & Experienced Interview Questions and Answers,SQL Objetive choice questions and answers,SQL Multiple choice questions and answers,SQL objective, SQL questions , SQL answers,SQL MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for SQL fresher interview questions ,SQL Experienced interview questions,SQL fresher interview questions and answers ,SQL Experienced interview questions and answers,tricky SQL queries for interview pdf,complex SQL for practice with answers,SQL for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .