Some More Questions

Some questions for your practice :
How to remove duplicate rows

Hint: use group by and count.
Find difference between two date fields & retrieve the difference - in
days/hour/year
What is the difference between DELETE and TRUNCATE statements?
DELETE TRUNCATE
Delete command is used to delete a row in a table. Truncate is used to delete
all the rows from a table.
You can rollback data after using delete statement. You cannot rollback data.
It is a DML command. It is a DDL command.
It is slower than truncate statement. It is faster.
What is the difference between clustered and non-clustered index in SQL?

The differences between the clustered and non clustered index in SQL are :
Clustered index is used for easy retrieval of data from the database and its faster
whereas reading from non clustered index is relatively slower.
Clustered index alters the way records are stored in a database as it sorts out
rows by the column which is set to be clustered index whereas in a non clustered
index, it does not alter the way it was stored but it creates a separate object
within a table which points back to the original table rows after searching.
One table can only have one clustered index whereas it can have many non clustered
index.
Are NULL values same as that of zero or a blank space?

A NULL value is not at all same as that of zero or a blank space. NULL value
represents a value which is unavailable, unknown, assigned or not applicable
whereas a zero is a number and blank space is a character.
Explain character-manipulation functions? Explains its different types in SQL.

Change, extract, and edit the character string using character manipulation
routines. The function will do its action on the input strings and return the
result when one or more characters and words are supplied into it.
The character manipulation functions in SQL are as follows:
A) CONCAT (joining two or more values): This function is used to join two or more
values together. The second string is always appended to the end of the first
string.
B) SUBSTR: This function returns a segment of a string from a given start point to
a given endpoint.
C) LENGTH: This function returns the length of the string in numerical form,
including blank spaces.
D) INSTR: This function calculates the precise numeric location of a character or

word in a string.
E) LPAD: For right-justified values, it returns the padding of the left-side

character value.
F) RPAD: For a left-justified value, it returns the padding of the right-side

character value.
G) TRIM: This function removes all defined characters from the beginning, end, or
both ends of a string. It also reduced the amount of wasted space.
H) REPLACE: This function replaces all instances of a word or a section of a string

(substring) with the other string value specified.
What is the difference between NOW() and CURRENT_DATE()?

NOW() returns a constant time that indicates the time at which the statement began
to execute. (Within a stored function or trigger, NOW() returns the time at which
the function or triggering statement began to execute.
The simple difference between NOW() and CURRENT_DATE() is that NOW() will fetch the
current date and time both in format ‘YYYY-MM_DD HH:MM:SS’ while CURRENT_DATE()
will fetch the date of the current day ‘YYYY-MM_DD’.
What are the different types of a subquery?

There are two types of subquery namely, Correlated and Non-Correlated.
Correlated subquery: These are queries which select the data from a table
referenced in the outer query. It is not considered as an independent query as it
refers to another table and refers the column in a table.
Non-Correlated subquery: This query is an independent query where the output of

subquery is substituted in the main query.
What is a Relationship and what are they?

Relation or links are between entities that have something to do with each other.
Relationships are defined as the connection between the tables in a database. There
are various relationships, namely:
One to One Relationship.

One to Many Relationship.
Many to One Relationship.
Self-Referencing Relationship.
Single-Row functions: These functions operate on a single row to give one result
per row.
Types of Single-Row functions:
Character
Number
Date
Conversion
General
Multiple-Row functions: These functions operate on groups of rows to give one
result per group of rows.
Types of Multiple-Row functions:
AVG
COUNT
MAX
MIN
SUM
STDDEV
VARIANCE
What is the use of Double Ampersand (&&) in SQL Queries? Give an example?
Use “&&” if you want to reuse the variable value without prompting the user each
time.
For ex: Select empno, ename, &&column_name from employee order by &column_name;
What is locking in SQL? Describe its types?

Locking prevents destructive interaction between concurrent transactions. Locks
held until Commit or Rollback. Types of locking are:
Implicit Locking: This occurs for all SQL statements except SELECT.
Explicit Locking: This can be done by the user manually.
Further, there are two locking methods:
Exclusive: Locks out other users
Share: Allows other users to access
What is the difference between Commit, Rollback, and Savepoint?

COMMIT: Ends the current transaction by making all pending data changes permanent.
ROLLBACK: Ends the current transaction by discarding all pending data changes.
SAVEPOINT: Divides a transaction into smaller parts. You can roll back the
transaction to a particular named savepoint.
What are the advantages of COMMIT and ROLLBACK statements?
Advantages of COMMIT and ROLLBACK statements are:

Ensure data consistency
Can preview data changes before making changes permanent.
Group logically related operations.

Some More Questions

Uploaded by

Copyright:

Available Formats

Some More Questions

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Some More Questions

Uploaded by

Copyright:

Available Formats

Some questions for your practice :

How to remove duplicate rows

What is the difference between DELETE and TRUNCATE statements?

What is the difference between clustered and non-clustered index in SQL?

Are NULL values same as that of zero or a blank space?

Explain character-manipulation functions? Explains its different types in SQL.

The character manipulation functions in SQL are as follows:

D) INSTR: This function calculates the precise numeric location of a character or

E) LPAD: For right-justified values, it returns the padding of the left-side

F) RPAD: For a left-justified value, it returns the padding of the right-side

H) REPLACE: This function replaces all instances of a word or a section of a string

What is the difference between NOW() and CURRENT_DATE()?

What are the different types of a subquery?

Non-Correlated subquery: This query is an independent query where the output of

What is a Relationship and what are they?

One to One Relationship.

Types of Single-Row functions:

Types of Multiple-Row functions:

What is locking in SQL? Describe its types?

What is the difference between Commit, Rollback, and Savepoint?

What are the advantages of COMMIT and ROLLBACK statements?

Advantages of COMMIT and ROLLBACK statements are:

You might also like