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

Posted Date:- 2021-08-17 08:21:48

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

Posted Date:- 2021-08-17 08:20:57

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.

Posted Date:- 2021-08-17 08:20:04

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.

Posted Date:- 2021-08-17 08:18:13

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.

Posted Date:- 2021-08-17 08:17:35

How do you sort records in a table?

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

Posted Date:- 2021-08-17 08:15:54

What is T-SQL?

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

Posted Date:- 2021-08-17 08:14:46

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’

Posted Date:- 2021-08-17 08:13:30

Write a Query to display the date after 12 months?

SELECT DATEADD(mm, 2, getdate())

Posted Date:- 2021-08-17 08:12:44

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.

Posted Date:- 2021-08-17 08:12:00

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.

Posted Date:- 2021-08-17 08:11:06

Can we store PDF files inside the SQL Server table?

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

Posted Date:- 2021-08-17 08:10:23

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.

Posted Date:- 2021-08-17 08:09:28

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.

Posted Date:- 2021-08-17 08:08:40

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.

Posted Date:- 2021-08-17 08:07:41

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.

Posted Date:- 2021-08-17 08:07:03

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 = '<>'

Posted Date:- 2021-08-17 08:04:46

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

Posted Date:- 2021-08-17 08:02:02

How will you create a column alias?

The AS keyword is optional when specifying a column alias.

Posted Date:- 2021-08-17 07:59:38

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.

Posted Date:- 2021-08-17 07:55:59

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).

Posted Date:- 2021-08-17 07:53:51

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

Posted Date:- 2021-08-17 07:52:48

Is it possible for the users to compare the test for the NULL values in SQL?

No, the same is not possible

Posted Date:- 2021-08-17 07:49:35

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.

Posted Date:- 2021-08-17 07:47:48

What is the maximum number of tables that can join in a single query?

256, check SQL Server Limit

Posted Date:- 2021-08-17 07:46:14

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.

Posted Date:- 2021-08-17 07:44:18

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).

Posted Date:- 2021-08-17 07:42:43

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.

Posted Date:- 2021-08-17 07:41:21

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

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

Posted Date:- 2021-08-17 07:37:04

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.

Posted Date:- 2021-08-17 07:32:42

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

Posted Date:- 2021-08-17 07:30:37

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

Posted Date:- 2021-08-17 07:29:29

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.

Posted Date:- 2021-08-17 07:28:54

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

Posted Date:- 2021-08-17 07:27:46

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.

Posted Date:- 2021-08-17 07:26:35

What are the multiple ways to execute a dynamic query?

EXEC sp_executesql,

Posted Date:- 2021-08-17 07:25:13

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.

Posted Date:- 2021-08-17 07:24:42

Can we add an identity column to the decimal datatype?

YES, SQL Server support this

Posted Date:- 2021-08-17 07:23:56

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.

Posted Date:- 2021-08-17 07:23:22

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

Posted Date:- 2021-08-17 07:20:54

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.

Posted Date:- 2021-08-17 07:20:02

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.

Posted Date:- 2021-08-17 07:18:23

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.

Posted Date:- 2021-08-17 07:17:29

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;

Posted Date:- 2021-08-17 07:15:58

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).

Posted Date:- 2021-08-17 07:13:35

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}.

Posted Date:- 2021-08-17 07:12:01

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.

Posted Date:- 2021-08-17 07:10:32

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.

Posted Date:- 2021-08-17 07:08:08

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.

Posted Date:- 2021-08-17 07:07:34

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.

Posted Date:- 2021-08-17 07:05:49

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 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 .