Sql Developer Interview Questions For 10 Years Experience

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

DBMS and SQL interview questions | SQL interview questions

The RANK function determines the rank for each row within your ordered partition in the result set. If the two rows are assigned the same rank, then the next number in the ranking will be its previous rank plus a number of duplicate numbers. For example, if we have three records at rank 4, the next rank listed would be ranked 7.

The DENSE_RANK function assigns a unique rank for each row within a partition as per the specified column value without any gaps. It always specifies ranking in consecutive order. If the two rows are assigned the same rank, this function will assign it with the same rank, and the next rank being the next sequential number. For example, if we have 3 records at rank 4, the next rank listed would be ranked 5.

Sql Query Interview Questions For Experienced Professionals

The interviewers might present a SQL query and ask questions on the same. They can also ask you to write SQL queries. Here are some SQL query interview questions for experienced professionals.

Write an SQL query to display the current date.

Write an SQL query to verify if the data passed to the query is of the given format: DD/MM/YY.

Write an SQL query to print the candidates name, whose birth date is 08/09/1970 to 30/11/1975.

Write a query to print an employees name whose name starts with S.

Write a query to find an employee whose salary is less than or equal to 10000.

Write a query to find the month from a given date.

Write a query to join three tables containing two NULL values.

Write a query to fetch the first three characters of the employee name.

Consider the following data table for answering the question given below:

EmpID

Answer the following SQL query interview questions with reference to the above table:

Write a SQL query to fetch the EmpFname in the upper case. Use the ALIAS name as EmpName.
Write a SQL query to retrieve the number of employees who are a part of the HR department.
Write a query to fetch the first four characters of EmpLname whose name starts with W.
Write a SQL query to retrieve the place name, i.e., the string before brackets, from the Address column.
Write a SQL query to fetch the names of employees that begin with R.

Recommended Reading:SQL query interview questions based on a sample data table

Ssis How To Access A Recordset Variable Inside A Script Task

On the script tab, make sure you put the variable in either the read-only variables or readwritevariables text boxes.

Here is a simple script that I use to format the errors in a data flow into the body of an email. Basically, I read the record set variable into a data table and process it row by row with the for loops. After this task completes I examine the value of uvErrorEmailNeeded to determine if there is anything to email using a conditional process flow connector. You will also need to add a reference to system.xml in your vb script. This is in SQL 2005.

Don’t Miss: What Is Your Sales Process Interview Question

Advanced Sql Server Interivew Questions And Answers

Q. What is XML Schema?

A. An XML schema describes the structure of an XML document and also describes the various constraints on the data in the document. When you specify XPath queries against the schema, the structure of the XML document returned is determined by the schema against which the XPath query is executed.

In an XSD schema, the < xsd:schema> element encloses the entire schema all element declarations must be contained within the < xsd:schema> element. You can describe attributes that define the namespace in which the schema resides and the namespaces that are used in the schema as properties of the < xsd:schema> element.

The minimum XSD schema is:

< xsd:schema xmlns:xsd=âhttps://www.w3.org/2001/XMLSchemaâ xmlns:sql=âurn:schemas-microsoft-com:mapping-schemaâ> < /xsd:schema>

Q. What is mapping schema?

A. A mapping schema is, in effect, an XML view of the relational data. These mappings can be used to retrieve relational data as an XML document. In the context of the relational database, it is useful to map the arbitrary XSD schema to a relational store. One way to achieve this is to annotate the XSD schema. An XSD schema with the annotations is referred to as a mapping schema, which provides information pertaining to how XML data is to be mapped to the relational store.

Q. What is XPath?

Q. What keyword you will use to get schema appended to the result set of a âfor XMLâ query?

What Is A Literal Give An Example Of Where It Can Be Used

Sql Queries Interview Questions And Answers For 4 Years Experience

A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks , number literals need not.

For exp: Select last_name|| is a||job_id As emp details from the employee .

Q2 What Are The Various Types Of Sql Joins

Joins in SQL are of the following types:

Right Join: It concatenates all rows from the right table and the matching left table rows.
Left Join: It concatenates all rows from the left table and the corresponding right table rows.
Inner Join: It selects and returns records with matching values in both tables.
Full Join: It returns all records of both tables if there is a matching record in either left or the right table.
Self Join: It is a regular join, and it simply joins a table to itself. The table is treated similarly to two different tables.
Cartesian Join: It is also called a cross join as it returns the rows in the first table by multiplying them with the second table rows.

Recommended Reading:Top SQL Joins Interview Questions and Answers you should practice.

What’s A Pl/sql Package

Packages in PL/SQL are schema objects that group related data. Your interviewer may use this question to evaluate your experience applying PL/SQL packages in large database systems. You can include the definition of PL/SQL packages in your answer and list their content.

Example: “A PL/SQL package is a database-schema object. The object contains groupings of related data types and subprograms. These data types include cursors, exception names and type statements. A package can have two components, which are specification and body. You can define public procedures, functions, variables, objects, cursors, subprograms, constants and exceptions with package specifications.

Because a program can access all these declared elements from outside the package, they are public elements. The package body contains coded definitions of all the declared subprograms and elements and some undeclared and private ones.”

What Do You Mean By Cte

Ans: CTE is represented as Common Table Expression. It specifies the temporary named result set. This result set is obtained by executing simple queries. CTE can be referred to in SELECT, INSERT, UPDATE, DELETE and MERGE statements. Moreover, CTE can also be used in VIEW statements. There are two types of CTE recursive and non-recursive.

Complex Sql Server Interview Questions And Answers

SQL Interview Questions And Answers | SQL Interview Preparation | SQL Training | Simplilearn

Q. During a recent migration project, John inserted 10,000 records in a table that has an Identity Column called ticketID, which automatically increases by 1 each time a record is inserted in the table. A month after the database went live John noticed that record with ticketID 5123 has some incorrect information. So John deletes this record and decides to re-insert this record in the table. He wants to re-use the ticketID 5123. He needs to achieve this while the database is in production. What should he do?

A. John can do this with the following code:

SET IDENTITY_INSERT Tickets ON

INSERT INTO Tickets Values GO

Set IDENTITY_INSERT Tickets ON allows explicit values to be inserted into the identity column of a table.

Q. Jenny wants to export data to Pivot table in Excel spreadsheet from a table in SQL Server. This data changes frequently. She wants to automate the process of updating the Excel spreadsheet using the SQL Job Scheduler. What tool is the best choice for the task?

A. Use Data Transformation Services to populate the spreadsheet. Using DTS you can import and export data between heterogeneous sources. DTS is covered in depth later in the book.

Q. You have a table with employee information that rarely changes. However this table is used from many applications in the organization to validate the data and to produce reports. What would be the optimal fill factor to choose for indexes created on this table?

Q. What is the difference between a fill factor of 100 and 0?

Don’t Miss: What To Wear For A Virtual Interview

Compare Triggers With Event Notifications

Ans:

Triggers respond to both DML and DLL events	Event notifications respond to DDL events and a subset of SQL trace events
They run T-SQL or CLR codes	They dont run any codes
They are processed synchronously within the scope of transactions	They are processed asynchronously, not within the scope of transactions
They must be processed on local servers	They can be processed on remote servers

Sql Server Interivew Questions For Experienced

Q. What are the restraints imposed on the table design by a Merge Replication?

A. You should not use a timestamp column in the table. Merge replication does not support timestamp columns. Timestamp values are generated automatically by the local server and guaranteed unique within a specific database only. Therefore, it is impossible for a change to the timestamp value created at one server to be applied to the timestamp column at another server. You must remove the timestamp column from any table you want to publish using merge replication.

SQL Security Interview Questions

Q. A user is a member of the Public role and the Sales role. The Public role has select permission on all the tables. The Sales role does not have select permission on some of the tables. Will the user be able to select from all tables?

A. No, the user will not be able to select information from tables. Permissions assigned to a role supercede those assigned to the role Public. This is true whether permissions assigned to the role are more restrictive or less restrictive than those assigned to the role Public.

Q. If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?

A. Yes. The user permission for the object referenced by a view is ignored. If a user does not have select permission on the table, but if he has permission on the view based on same table, he can select from the view.

SQL Transactions Interview Questions

Recommended Reading: What To Write In A Thank You Email After Interview

Database Modeling Interview Questions

These questions are designed to test how good you are at database design or database modeling. What is meant by that? You need to show the ability to design and build the database from scratch according to the business processes and business needs. This requires a high level of both technical and business knowledge. You will be working with both technical and non-technical colleagues. So, you need to understand both the business side of their requirement and how to, in the soundest way, technically cater to their business needs regarding the data. Generally, this is a process that goes through these steps :

defining the database purpose

creating the physical model

Question Examples

One of the typical questions that occur in the SQL interviews is this one by Audible:

Build a Recommendation System

“Can you walk us through how you would build a recommendation system?”

Link to the question:

Answer: Since there is a wide variety of approaches to answer this question, we will leave you to come up with your own way of building one.

The database design question can also include SQL coding, such as this one from Facebook:

GROUP or ORDER BY

“Write a SQL query to compute a frequency table of a certain attribute involving two joins. What if you want to GROUP or ORDER BY some attribute? What changes would you need to make? How would you account for NULLs?”

What Do You Mean By Relationships Between Tables And Mention Their Types

12+ Python Sample Resume For 2 Years Experience

Ans: Relationships between tables describe how a row in a table is related to a row or rows of another table in a database.

There are three types of relationships, as mentioned below:

One to one: When a row in a table has only one related row in another table

One to many: When a row in a table has multiple related rows in another table

Many to many: When a row has multiple related rows in another table and vice-versa.

Recommended Reading: How To Prepare For A Talent Acquisition Interview

What Are The Standard Sql Commands Every Sql Developer Should Know

Your interviewer will likely want to check your knowledge to see if you can name the most essential commands necessary for programmers.Your answer might include commands like:

Some of the standard commands every SQL developer should know include:

SELECT which allows you to select data from a database
WHERE which allows you to apply specific criteria to what you select
ORDER which is used to sort the results
JOIN which is used to join the related data thats stored in one or more tables
UNION which allows you to append rows to each other

What Is The Non

The indexes other than PRIMARY indexes are called non-clustered indexes. We know that clustered indexes are created automatically when primary keys are generated, and non-clustered indexes are created when multiple joins conditions and various filters are used in the query. The non-clustered index and table data are both stored in different places. It cannot be able to alter the physical order of the table and maintains the logical order of data.

The purpose of creating a non-clustered index is for searching the data. Its best example is a book where the content is written in one place, and the index is at a different place. We can create 0 to 249 non-clustered indexes in each table. The non-clustered indexing improves the performance of the queries which use keys without assigning the primary key.

Read Also: What Can I Ask In An Interview

What Does Schema Mean

Sample answer:

A schema refers to a collection of database objectssuch as tables, functions, indexes, and proceduresassociated with a database.

The schema helps segregate database objects for different applications and access rights its generally used to define who can and who cannot view specific objects in the database.

Can You Identify The Employee Who Has The Third

SQL Interview Questions and Answers | Intellipaat

Consider the following employee table. In the table, Sabid has the third-highest salary .

Name
Vaibhav	80,000

Below is a simple query to find out the employee who has the third-highest salary. The functions RANK, DENSE RANK, and ROW NUMBER are used to obtain the increasing integer value by imposing the ORDER BY clause in the SELECT statement, based on the ordering of the rows. The ORDER BY clause is necessary when RANK, DENSE RANK, or ROW NUMBER functions are used. On the other hand, the PARTITION BY clause is optional.

WITH CTE AS OVER  FROM EMPLOYEE)SELECT Name, Salary FROM CTE WHERE RN =3

Recommended Reading: Best Wireless Lavalier Mic For Interviews

What Are Some Of The Most Important Scalar Functions

Sample answer:

Scalar functions are user-defined functions applied to a set of data to return a single value. Some of the most common scalar functions include:

UCASE converts values to uppercase
LCASE converts values to lowercase
MID extracts textual data based on specified criteria
ROUND rounds numerical data to a specified number of decimals
NOW returns the current system date and time

What Are Cursors And Mention Their Types

Ans: Cursors are known to be the extensions to result in sets that are the group of rows returned for a statement. They help retrieve one or more blocks of rows from the current position. Similarly, they can support data modifications for the rows in the current position in the result set.

There are four cursor types, as you can find below:

Forward only

To implement a business rule

Read Also: What’s An Exit Interview

What Is Pattern Matching In Sql

10 Frequently asked SQL Query Interview Questions

SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information.

Using the % wildcard to perform a simple search

The % wildcard matches zero or more characters of any type and can be used to define wildcards both before and after the pattern. Search a student in your database with first name beginning with the letter K:

SELECT*FROM studentsWHERE first_name LIKE'K%'

Omitting the patterns using the NOT keyword

Use the NOT keyword to select records that don’t match the pattern. This query returns all students whose first name does not begin with K.

SELECT*FROM studentsWHERE first_name NOTLIKE'K%'

Matching a pattern anywhere using the % wildcard twice

Search for a student in the database where he/she has a K in his/her first name.

SELECT*FROM studentsWHERE first_name LIKE'%Q%'

Using the _ wildcard to match pattern at a specific position

The _ wildcard matches exactly one character of any type. It can be used in conjunction with % wildcard. This query fetches all students with letter K at the third position in their first name.

SELECT*FROM studentsWHERE first_name LIKE'__K%'

Matching patterns for a specific length

Recommended Reading: How To Answer The Most Common Interview Questions