Basic Database Developer Interview Preparation Guide
Download PDF

Database Developer Frequently Asked Questions in various Database Developer job interviews by interviewer. The set of questions are here to ensures that you offer a perfect answer posed to you. So get preparation for your new job interview

70 Database Developer Questions and Answers:

Table of Contents:

Basic  Database Developer Job Interview Questions and Answers
Basic Database Developer Job Interview Questions and Answers

1 :: Please explain what is a NULL value?

A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.

2 :: Tell us how to Test for NULL Values?

A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, It is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL and IS NOT NULL operators.

3 :: Tell me what are the advantages of Views?

Some of the advantages of Views are

☛ Views occupy no space
☛ Views are used to simply retrieve the results of complicated queries that need to be executed often.
☛ Views are used to restrict access to the database or to hide data complexity.

4 :: Tell me what is the difference between NULL value, Zero, and Blank space?

As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.
Null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).

5 :: Explain me what is the difference between Rename and Alias?

‘Rename’ is a permanent name given to a table or column
‘Alias’ is a temporary name given to a table or column.

6 :: Tell me can a table contain multiple PRIMARY KEY’s?

The short answer is no, a table is not allowed to contain multiple primary keys but it allows to have one composite primary key consisting of two or more columns.

7 :: Tell us what is an Index?

An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.

8 :: Can you tell me the ACID properties and explain?

Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.

☛ Atomicity
☛ Consistency
☛ Isolation
☛ Durability

9 :: Tell us what are the different DML commands in SQL?

DML commands are used for managing data present in the database.

☛ SELECT: To select specific data from a database
☛ INSERT: To insert new records into a table
☛ UPDATE: To update existing records
☛ DELETE: To delete existing records from a table

10 :: Explain me what is Stored procedure?

A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).

11 :: Tell me 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.

12 :: Please explain what is DBMS?

Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.

13 :: Do you know what are the constraints available in SQL?

Some of the constraints in SQL are – Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.

14 :: What are the steps needed to Create the scheduled job?

Steps to create a Scheduled Job :

☛ Connect to the database of SQL server in SQL Server Management Studio. On the SQL Server Agent, we will find a Jobs folder.
☛ Right click on jobs and choose Add New.
☛ A New Job window will come into view. Give an associated name for the same.
☛ Click next on the “Steps” in the left list of options. An SQL job can have multiple steps either in the form of SQL declaration or a stored practice call.
☛ Click on the “Schedules” in the left list of options. An SQL job can comprise of one or supplementary schedules. It is basically the instance at which SQL job will jog itself. We can spell out returning schedules also.

15 :: Please explain what are string functions in SQL?

SQL string functions are used primarily for string manipulation. Some of the widely used SQL string functions are

☛ LEN() – It returns the length of the value in a text field
☛ LOWER() – It converts character data to lower case
☛ UPPER() – It converts character data to upper case
☛ SUBSTRING() – It extracts characters from a text field
☛ LTRIM() – It is to remove all whitespace from the beginning of the string
☛ RTRIM() – It is to remove all whitespace at the end of the string
☛ CONCAT() – Concatenate function combines multiple character strings together
☛ REPLACE() – To update the content of a string.

16 :: Tell me what are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;
Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

17 :: Explain me what is a column in a Table?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

18 :: Tell me what are Operators available in SQL?

SQL Operator is a reserved word used primarily in an SQL statement’s WHERE clause to perform operations, such as arithmetic operations and comparisons. These are used to specify conditions in an SQL statement.

There are three types of Operators.

☛ Arithmetic Operators
☛ Comparison Operators
☛ Logical Operators

19 :: Tell us what is SQL?

SQL Overview: SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP’s), execute queries, retrieve, insert, update, delete data against a database.

20 :: Tell us what is the difference between an inner and outer join?

An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.
An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.

21 :: Tell us what is a Trigger?

A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee_Payroll, Employee_Time_Sheet etc.,

22 :: Tell me referential integrity and where it can be enforced?

If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What’s an orphan? How do we make sure that we don’t end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?

23 :: Explain me what is a Primary Key?

A PRIMARY KEY constraint uniquely identifies each record in a database table. All columns participating in a primary key constraint must not contain NULL values.

24 :: Tell us what are the different types of SQL commands?

SQL commands are segregated into following types:

☛ DDL – Data Definition Language
☛ DML – Data Manipulation Language
☛ DQL – Data Query Language
☛ DCL – Data Control Language
☛ TCL – Transaction Control Language

25 :: Tell me why DBAs don’t like cursors?

I like to phrase this interview question this way because I’m not saying the DBA is right – I’m just asking the developer to explain the DBA’s point of view. I don’t have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question.

The candidate gets bonus points if they seem even vaguely aware of the terms “set-based processing” and “row-based processing”, but that’s purely a bonus. (I wish I could say that these concepts are requirements, but in today’s economic market, companies don’t always want to pay top dollar to get the best candidates.)
Database Developer Interview Questions and Answers
70 Database Developer Interview Questions and Answers