Colleen

Question 1

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

Answer 1

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

Question 2

What is the difference between SQL and PL/SQL?

Answer 2

PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

Question 3

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

Answer 3

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

CREATE − it creates a new table, a view of a table, or other object in database.

ALTER − it modifies an existing database object, such as a table.

DROP − it deletes an entire table, a view of a table or other object in the database.

Question 4

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

Answer 4

Following are various DML or Data Manipulation Language commands in SQL −

SELECT − it retrieves certain records from one or more tables.

INSERT − it creates a record.

UPDATE − it modifies records.

DELETE − it deletes records.

Question 5

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

Answer 5

Following are various DCL or Data Control Language commands in SQL −

GRANT − it gives a privilege to user.

REVOKE − it takes back privileges granted from user.

Question 6

Can you sort a column using a column alias?

Answer 6

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

Question 7

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

Answer 7

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

Question 8

True or False. Give explanation if False.

If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

Answer 8

True.

Question 9

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

Answer 9

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.

Question 10

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

Answer 10

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.

Question 11

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

Answer 11

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called ‘wildcard’ search.

Question 12

What is the default ordering of data using the ORDER BY clause? How could it be changed?

Answer 12

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

Question 13

What are the specific uses of SQL functions?

Answer 13

SQL functions have the following uses −

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

Question 14

What are the case manipulation functions of SQL?

Answer 14

LOWER, UPPER, INITCAP

Question 15

Which function returns the remainder in a division operation?

Answer 15

The MOD function returns the remainder in a division operation.

Question 16

What is the purpose of the NVL function?

Answer 16

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

Question 17

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

Answer 17

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.

The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

Question 18

What is the use of the NULLIF function?

Answer 18

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.

Question 19

Discuss the syntax and use of the COALESCE function?

Answer 19

The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)

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

Question 20

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

Answer 20

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

Using CASE expression

Using the DECODE function

Question 21

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

Answer 21

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

Question 22

What is the difference between cross joins and natural joins?

Answer 22

The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.

Question 23

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

Answer 23

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

Question 24

True or False. Give explanation if False.

By default the group functions consider only distinct values in the set.

Answer 24

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

Question 25

True or False. Give explanation if False.

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

Answer 25

True.