SQL Important questions for Experienced Candidate!!

SQL Important questions for Experienced!!

1 >What are the differences between SQL and PL/SQL?

SQL:

1. It is a Structured Query Language
2. Only a single operation can be performed at a time
3. SQL executes the queries such as creating tables, deleting tables, and inserting into tables.
4. Mainly, it is used to retrieve data from databases and modify tables.
5. Processing speed is low

PL/SQL:

1. It is a Procedural Language where SQL statements are processed effectively
2. A Group of operations as a single block can be performed at a time
3. It is used to write program blocks, functions, procedures, triggers, packages, and cursors.
4. Used for creating web applications and server pages
5. It has the excellent processing speed

2> What are the advantages of PL/SQL functions?

• It has tight interaction with SQL.
• It has high performance and productivity.
• It has high portability and scalability.
• ?It is highly flexible and secure.
• Supports developing web applications and server pages

3>. Differentiate: CHAR and VARCHAR data types in SQL?

CHAR

1. It is a fixed-length character string data type
2. The data type can be a single byte or multiple-byte?
3. This data type can be used when the character length is known
4. It uses static memory location
5. This is used when the character length of the data is the same.

VARCHAR

1. It is a variable-length character string data type.
2. It can accept character strings up to 255 bytes
3. This data type is used when the character length is not clear
4. It uses dynamic memory location
5. This is used when the character length of the data is variable.

4>. How can you avoid Duplicate Keys in SQL?

We can eliminate duplicate keys in SQL by using the following methods:

• Using INSERT INTO SELECT
• ?Using WHERE NOT IN
• Using WHERE NOT EXISTS
• Using IF NOT EXISTS
• Using COUNT(*)=0

5>. Brief the factors that affect the functionalities of databases?

The following five factors affect the functionalities of databases.

• Workload
• Throughput
• Resources
• Optimization
• Contention

6>. List out the factors that affect the query performance?

The following are the factors that affect the performance of queries.

• Number of nodes, processors, or slices
• Node types
• Data distribution
• Data sort order
• Dataset size
• Concurrent operations
• Query structure
• Code compilation

7>. Differentiate: UNION and INTERSECT statements?

UNION: The operator returns a single result set for two separate queries. This operator functions based on specific conditions.

Syntax: query 1 UNION query2

INTERSECT: The operator returns only the distinct rows from two separate queries.

Syntax: query 1 INTERSECT query2

8>. What is the difference between DROP and TRUNCATE statements?

DROP:

1. It removes a whole database
2. All the constraints will be removed after the execution of the DROP function.
3. The structure of the data also will be removed
4. It is a slow process

TRUNCATE:

1. It removes a table or data or index
2. Constraints don’t get affected because of the execution of this statement
3. The structure of the data won’t get affected
4. It is faster than the DROP statement

9>. What is the use of the SELECT DISTINCT statement?

This statement is used to select distinct values from a table. The table might consist of many duplicate records, whereas this statement helps to return only the distinct values.

The syntax for the statement is given as follows;

SELECT DISTINCT column1, column2,

FROM table_name1;

10>. How can you differentiate the RANK and DENSE_RANK functions?

RANK and DENSE_RANK are used as the ranking functions, which perform data ranking based on specific conditions. When the RANK statement is executed, it returns a ranking of values of a table based on specific conditions. At the same time, the result sets up skip positions in the ranking if there are the same values. Simply put, there will be a discontinuity in the numbering of rankings. On the other hand, when the RANK_DENSE function is executed, it doesn’t skip any position in the ranking of values even though there are the same values present in the table. It returns continuous ranking.

80. What is the difference between IN and BETWEEN operators?

Both IN and BETWEEN operators are used to return records for multiple values from a table. The IN operator is used to return records from a table for the multiple values specified in the statement. On the other hand, BETWEEN operator is used to return records within a range of values specified in the statement.

Syntax for the IN statement is given as:

SELECT * FROM table_name1 WHERE column_name1 IN (value 1,value2)

The syntax for the BETWEEN statement is given as:

SELECT * FROM table_name1 WHERE column_name1 BETWEEN ‘value 1’ AND ‘value2’

11>. Compare: STUFF and REPLACE statements?

Both STUFF and REPLACE statements are used to replace characters in a string. The STUFF statement inserts the specific characters in a string replacing existing characters. In comparison, the REPLACE statement replaces existing characters with specific characters throughout the string.

For example, consider the following examples:

For the STUFF statement;

SELECT STUFF (‘raman’,2,3,’aja’)

Output: rajan

For the REPLACE statement;

SELECT REPLACE (‘ramanathan’,’an’,’ar’)

Output: ramarathar

12>. What do you mean by COMMIT in SQL?

COMMIT statement allows saving the changes made in a transaction permanently. Once a transaction is committed, the previous values cannot be retrieved.

The following syntax is used for this operation:

SELECT * FROM Staff

WHERE incentive = 1000;

sql>COMMIT;

13>. What is the use of the GRANT Statement?

This statement grants permissions for users to perform operations such as SELECT, UPDATE, INSERT, DELETE, or any other operations on tables and views.

For example, if you would like to provide access to a user for updating tables, then the following statement must be used. In addition, the user too can grant permissions to other users.

GRANT UPDATE ON table_name TO user_name WITH GRANT OPTION

14. What do you mean by ETL in SQL?

ETL in SQL represents Extract, Transform and Load.

Extracting – It is about extracting data from the source, which can be a data warehouse, CRMs, databases, etc.

Transforming – It includes many processes such as cleansing, standardization, deduplication, verification, and sorting.

Loading – It is the process of loading the transformed data into the new destination. There are two types of loading data: full loading and incremental loading.

15. What do you mean by NESTED triggers?

If a trigger fires another trigger while being executed, it is known as a NESTED trigger. Nested triggers can be fired while executing DDL and DML operations such as INSERT, DROP and UPDATE. Nested triggers help to back up the rows affected by the previous trigger. There are two types of nested triggers: AFTER triggers and INSTEAD OF triggers.

16. How to insert multiple rows in a database table in SQL?

We can use the INSERT INTO statement to insert multiple rows in a database table in SQL.

The following syntax can be used for this case:

INSERT INTO table_name VALUES (value1, value), (value3, value4)…;

The inserted data can be selected using the following syntax:

SELECT * FROM table_name;

17. What do you mean by live-lock in SQL?

When two processes repeat the same type of interaction continually without making any progress in the query processing, it leads to a live-lock situation in the SQL server. There is no waiting state in live-lock, but the processes are happening concurrently, forming a closed loop.

?For example, let us assume process A holds a resource D1 and requests resource D2. At the same time, assume that process B holds a resource D2 and requests resource D1. This situation won’t progress any further until any of the processes should either drop holding a resource or drop requesting a resource.

18. What do you mean by Equi-JOIN and non-Equi-JOIN?

Equi-join creates a join operation to match the values of the relative tables. The syntax for this operation can be given as follows:

SELECT column_list FROM table1, table2,…..

WHERE table1.column_name = table.2column_name;

On the other side, Non-Equi join performs join operations except equal. This operator works with <,>,>=, <= with conditions.

SELECT * FROM table_name1,table_name2

WHERE table_name1.column[>|<|>=|<=] table_name2.column;

19. What are the different types of SQL sandboxes?

There are three types of SQL sandboxes. They are given as follows:

• Safe access sandbox
• Unsafe access sandbox
• External access sandbox

20. What do you mean by lock escalation?

It is the process of converting row and page locks into table locks. Know that Reduction of lock escalation would increase the server performance. To improve performance, we need to keep transactions short and reduce lock footprints in queries as low as possible. Besides, we can disable lock escalation at the table and instance levels, but it is not recommended.

21. How can you update a table using SQL?

The UPDATE statement allows you to update a database table in SQL. After the execution, one or more columns in a table will be replaced by new values.

The syntax for the UPDATE statement is given as follows:

UPDATE table_name

SET

??Column1 = new_value1,

??Column2 = new_value2,

??..…..

WHERE

???Condition;

This statement requires a table name, new values, and conditions to select the rows. Here, the WHERE statement is not mandatory. Suppose the WHERE clause is used, all the rows in a table will be updated by the new values.

22. How to create a Stored Procedure using T-SQL?

• Connect to the instance of a database engine in ‘object explorer’
• Click ‘new query’ from the ‘files menu’
• Copy and paste the following sample codes in the query window

USE AdventureWorks2012;?

GO?

CREATE PROCEDURE HR.GetEmployeesTest2??

????@LastName nvarchar(25),??

????@FirstName nvarchar(25)??

AS?

?????SET NOCOUNT ON

?????SELECT FirstName, LastName, Division

?????FROM HR.vEmployeeDivisionHistory?

?????WHERE FirstName = @FirstName AND LastName = @LastName?

?????AND EndDate IS NULL;?

GO

• Now, execute the codes

You can use the following statement to run the newly created stored procedure.

EXECUTE HR.GetEmployeesTest2 N'Ackerman', N'Pilar';

23. What do you mean by DELETE CASCADE constraint?

When a foreign key is created under this option, and if a referenced row in the parent table is deleted, the referencing row(s) in a child table also gets deleted.

On similar tracks, when a referenced row is updated in a parent table, the referencing row(s) in a child table is also updated.

24. Explain the different types of indexes in SQL?

The following are the different types of indexes in SQL.

• Single-column indexes
• Unique indexes
• Composite indexes
• Implicit indexes

25. What do you mean by auto-increment?

It is a unique number that will be generated when a new record is inserted into a table. Mainly, it acts as the primary key for a table.

The following syntax is used for this purpose:

IDENTITY (starting_value, increment_value)

26. What do you mean by Pattern Matching?

We can use the LIKE command in SQL to identify patterns in a database using character strings. Generally, a pattern may be identified using wildcard characters or regular characters. So, pattern matching can be performed using both wildcard characters and string comparison characters as well. However, pattern matching through wildcard characters is more flexible than using string comparison characters.

27. What is the difference between blocking and deadlocking?

Blocking is a phenomenon that occurs when a process locks a resource ‘A’, and the same resource is requested by another process ‘B’. Now, process ‘B’ can access the resource ‘A’ only when process ‘A’ releases the lock. The process ‘B’ has to wait until the process ‘A’ releases the lock. The SQL server doesn't interfere and stops any process in this scenario.

On the contrary, deadlocking is the phenomenon that occurs when a resource 'A' is locked by a process 'A' and the same resource is requested by another process 'B'. Similarly, a resource 'B' is locked by process 'B' and requested by process A. This scenario causes a deadlock situation, and it is a never-ending process. So, the SQL server interferes and voluntarily stops any one of the processes to remove the deadlock.

28. What is the difference between COALESCE ( ) and ISNULL ( )?

COALESCE function returns the first value that is non-NULL in the expression, whereas ISNULL is used to replace the non-NULL values in the expression.

Syntax for COALESCE function is given as:

SELECT column(s),COALESCE (exp_1,…..,exp_n) FROM table_name;

Syntax for ISNULL is given as:

SELECT column(s),ISNULL(column_name,value_to_replace) FROM table_name;

29. What is the difference between NVL and the NVL (2) functions in SQL?

Both the functions are used to find whether the first argument in the expression is NULL. The NVL function in the SQL query returns the second argument if the first argument is NULL. Otherwise, it returns the first argument.

The NVL2 function in SQL query returns the third argument if the first argument is NULL. Otherwise, the second argument is returned.