Interview Question for Database Manager

What is the difference between SQL and My SQL or SQL Server?

SQL or Structured Query Language is a language; that communicates with a relational database thus providing ways of manipulating and creating databases.

My SQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

What is the difference between SQL and PL/SQL?

PL/SQL is a dialect of SQL that adds the procedural feature of programming languages in SQL.

It was developed by Oracle Corporation in the early ’90s to enhance the capabilities of SQL.

What are various DDL commands in SQL? Give a brief description of their purposes.

Following are various DDL or Data Definition Language commands in SQL:

• CREATE: It creates a new table, a view of a table, Or other objects in databases.
• ALTER: It modifies an existing database object, such as a table.
• DROP: It deletes an entire table, a view of a table, or other objects in the database.

What are various DML commands in SQL? Give a brief description of their purposes.

The following are various DML or Data Manipulation Language commands in SQL:

• SELECT: It retrieves a certain record from one or more tables
• INSERT: It creates records
• UPDATE: It modifies records
• DELETE: It deletes records

What are the various DCL commands in SQL? brief description of their purposes.

Following are various DCL or Data Control Language in SQL:

• GRANT: It gives a privilege to the user.
• REVOKE: It takes back privileges granted by the user.

Can you sort a column using a column alias?

Yes, a column alias could be used in the ORDER BY clause.

Is a NULL value the same as zero or blank space? If not then what is the difference?

A NULL value is not the same as zero or blank space.

A NULL value is a value that is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character

If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULL M. Is this TRUE?

Yes, it is true.

If a table contains duplicate rows, does a query result display the duplicate values by default? How can you duplicate rows from a query result?

A query result displays all rows including the duplicate rows.

To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator display rows based on a range of values.

The IN condition operator checks for values contained in a specific set of values.

How do you search for value in a database table when you don’t have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern.

This is also called “wild card ” search

What is the default ordering of data using the DESC keyword, after the column name in the ORDER BY clause? How could it be changed?

The Default sorting order is ascending.

It can be changed using the DESC keyword, after the column name in the ORDER By clause

What the specific uses of SQL functions?

SQL functions have the following uses:

• Performing calculations on data
• Modifying individual data items
• Manipulating the output
• Formatting dates and numbers
• Converting data types

What are the case manipulating functions of SQL?

LOWER, UPPER, INITCAP

Which functions return the remainder in its division operation?

The MOD function returns the remainder in a division operation.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL [exp1, exp2] function converts the source expression [or value] exp1 to the target expression [or value] exp 2, if exp1 contain NULL.

The return value has the same data type as that of exp1.

The NVL2[exp1, exp2, exp3] function checks the first expression exp1, it is not null then, the second expression exp2 is returned

if the first expression exp1 is null then third expression exp3 is returned

What is the use of the NULLIF function?

The NULLIF function compares two expressions.

• If they are equal, the function returns null.
• If they are not equal, the first expression is returned

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE [exp1, exp2, ….., expn]

It returns the first non-null expression given in the parameter list.

Which Expressions or functions allow you to implement conditional processing in SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement:

• Using CASE expression
• Using the DECODE function

You want to display a result query from joining two tables with 20 and 10 raws respectively. Erroneously you forget to write the WHERE clause. What would be the result?

The result would be the Cartesian product of two tables with 20*10=200 rows

What the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian Product of two tables.

The natural join is based on all the column having the same name and data types in both the tables

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and return one result per group.

Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

Say True or False. Give an explanation if False.

By default, the group function considers only distinct values in the set.

By default, group functions consider all values including duplicate values.

Say True or False. Give an explanation if false.

The DISTINCT keyword allows a function to consider only non-duplicate values.

True

Say True or False. Give an explanation if false.

All group functions ignore null values.

True

Say True or False. Give an explanation if false.

COUNT(*) returns the number of columns in a table.

False. COUNT(*) returns the number of rows in a table.

What’s wrong with the following query?

SELECT subject_code, count[name] FROM students;

It does not have a GROUP BY clause.

The subject_code should be in the GROUP BY clause.

Say True or False. Give an explanation if false.

Group functions can not be nested.

False. Group functions can be nested to a depth of two.

What do you understand by subquery? when is it used?

A subquery is a SELECT statement embedded in a clause of another SELECT statement.

It is used when the inner query or the subquery returns a value that is used by the outer query.

It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table

Say True or False. Give an explanation if false.

A single row subquery returns only one row from the outer SELECT statement.

False. A single row subquery returns only one row from the inner SELECT statement.

Say True or False. Give an explanation if false.

A multiple-row subquery returns more than one row the inner SELECT statement.

True

Say True or False. Give an explanation if false.

Multiple column subqueries return more than one column the inner SELECT statement.

True

What’s wrong with the following query?

SELECT student_code, name FROM students WHERE marks = [SELECT MAX(marks) FROM students GROUP BY subject_code];

Here a single-row operator = is used with a multiple-row subquery.

What are the various row comparison operators in SQL?

IN, ANY, ALL.

What is the purpose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

Say True or False. Give an explanation if false.

While inserting new rows in a table must list values in the default order of the columns.

True

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways:

• Implicitly by omitting the column from the column list.
• Explicitly by specifying the NULL keyword in the VALUES clause.

Say True or False. Give an explanation if false.

INSERT statement does not allow copying rows from one table to another

False. INSERT Statement allows add rows to a table copying rows from an existing table.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying from another table

In this case, a subquery is used in the place of the VALUES clause.

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Can you modify the rows in a table based on the values from another table? Explain

Yes, Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.

Say True or False. Give an explanation if false. The DELETE statement is used to delete a table from the database.

False. The DELETE statement is used for removing existing rows from a table.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

Can you remove rows from a table based on values from another table? Explain

Yes, subqueries can be used to remove rows from a table based on values from another table.

Say True or False. Give an explanation if False.

Attempting to delete a record with a value attached to an integrity constraint, returns an error.

True

Say True or False. Give an explanation if False.

You can use a subquery in an INSERT statement

True

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exist, or an INSERT if the row does not exist

Say True or False. Give an explanation if false.

A DDL statement or a DCL statement is automatically committed.

True

What is the difference between VARCHAR2 AND CHAR data types?

VARCHAR2 represents variable-length character data, whereas CHAR represents fixed-length character data.

Say True or False. Give an explanation if false.

A DROP TABLE statement can be rolled back.

False. A DROP TABLE statement cannot be rolled back.

Which SQL statement is used to add, modify, or drop columns in a database table?

The AlTER TABLE statement

What is a view? Why should you use a view?

A view is a logical snapshot based on a table or another view. It is used for:

• Restricting access to data
• Making complex queries simple
• Ensuring data independency
• Providing different views of some data

Say True or False. Give an explanation if false.

A view does not have data of its self

True

Have you ever worked on an SQL project?

• Mention project and advantage of the same
• Mention the concept learned