Database Developer Interview Questions & Answers

Download PDF

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.

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.

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.

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

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

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.

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.

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

☛ Atomicity
☛ Consistency
☛ Isolation
☛ Durability

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

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

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

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

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

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.

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.

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;

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

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

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.

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.

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

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?

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.

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

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