Q1. What are the different subsets of SQL?
- Data Definition Language (DDL) – It allows you to perform various operations on the database such as CREATE, ALTER, and DELETE objects.
- Data Manipulation Language(DML) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
- Data Control Language(DCL) – It allows you to control access to the database. Example – Grant, Revoke access permissions.
Q2. What do you mean by DBMS? What are its different types?
A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images, etc.
- Relational Database Management System: The data is stored in relations (tables). Example – MySQL.
- Non-Relational Database Management System: Non-relational databases (often called NoSQL databases) are different from traditional relational databases in that they store their data in a non-tabular form. Example – MongoDB
Q3. What is a Self-Join?
A self join allows you to join a table to itself. It helps query hierarchical data or compare rows within the same table
Q4. What is the SELECT statement?
A SELECT command gets zero or more rows from one or more database tables or views. This is a data manipulation language (DML) command.
Q5. What are some common clauses used with SELECT query in SQL?
The following are some frequent SQL clauses used in conjunction with a SELECT query:
WHERE clause: In SQL, the WHERE clause is used to filter records that are required depending on certain criteria.
ORDER BY clause: The ORDER BY clause in SQL is used to sort data in ascending (ASC) or descending (DESC) order depending on specified field(s) (DESC).
GROUP BY clause: GROUP BY clause in SQL is used to group entries with identical data and may be used with aggregation methods to obtain summarized database results.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.
Q6. What are UNION, MINUS and INTERSECT commands?
The UNION operator is used to combine the results of two tables while also removing duplicate entries.
The MINUS operator is used to return rows from the first query but not from the second query.
The INTERSECT operator is used to combine the results of both queries into a single row.
Q7. What is Cursor? How to use a Cursor?
After any variable declaration, DECLARE a cursor. A SELECT Statement must always be coupled with the cursor definition.
To start the result set, move the cursor over it. Before obtaining rows from the result set, the OPEN statement must be executed.
To retrieve and go to the next row in the result set, use the FETCH command.
To disable the cursor, use the CLOSE command.
Finally, use the DEALLOCATE command to remove the cursor definition and free up the resources connected with it.
Q8. List the different types of relationships in SQL.
There are different types of relations in the database:
One-to-One – This is a connection between two tables in which each record in one table corresponds to the maximum of one record in the other.
One-to-Many and Many-to-One – This is the most frequent connection, in which a record in one table is linked to several records in another.
Many-to-Many – This is used when defining a relationship that requires several instances on each sides.
Self-Referencing Relationships – When a table has to declare a connection with itself, this is the method to employ.
Q9. What is OLTP?
OLTP, or online transactional processing, allows huge groups of people to execute massive amounts of database transactions in real time, usually via the internet. A database transaction occurs when data in a database is changed, inserted, deleted, or queried.
Q10. What are the differences between OLTP and OLAP?
OLTP stands for online transaction processing, whereas OLAP stands for online analytical processing. OLTP is an online database modification system, whereas OLAP is an online database query response system.
Q11. How to create empty tables with the same structure as another table?
To create empty tables:
Using the INTO operator to fetch the records of one table into a new table while setting a WHERE clause to false for all entries, it is possible to create empty tables with the same structure. As a result, SQL creates a new table with a duplicate structure to accept the fetched entries, but nothing is stored into the new table since the WHERE clause is active.
Q12. What is PostgreSQL?
In 1986, a team lead by Computer Science Professor Michael Stonebraker created PostgreSQL under the name Postgres. It was created to aid developers in the development of enterprise-level applications by ensuring data integrity and fault tolerance in systems. PostgreSQL is an enterprise-level, versatile, resilient, open-source, object-relational database management system that supports variable workloads and concurrent users. The international developer community has constantly backed it. PostgreSQL has achieved significant appeal among developers because to its fault-tolerant characteristics.
It’s a very reliable database management system, with more than two decades of community work to thank for its high levels of resiliency, integrity, and accuracy. Many online, mobile, geospatial, and analytics applications utilise PostgreSQL as their primary data storage or data warehouse.
Q13. What are SQL comments?
SQL Comments are used to clarify portions of SQL statements and to prevent SQL statements from being executed. The comments are not supported by a Microsoft Access database.
Single Line Comments: It starts with two consecutive hyphens (–).
Multi-line Comments: It starts with /* and ends with */.
Q14. What is the usage of the NVL() function?
You may use the NVL function to replace null values with a default value. The function returns the value of the second parameter if the first parameter is null. If the first parameter is anything other than null, it is left alone.
This function is used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have IFNULL() and SQL Server have ISNULL() function.
Q15. Explain character-manipulation functions? Explains its different types in SQL.
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.
Q16. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.
The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.
Q17. What are Tables and Fields?
A table is a collection of data components organized in rows and columns in a relational database. A table can also be thought of as a useful representation of relationships. The most basic form of data storage is the table. An example of an Employee table is shown below.
| ID | Name | Department | Salary |
| 1 | Rahul | Sales | 24000 |
| 2 | Rohini | Marketing | 34000 |
| 3 | Shylesh | Sales | 24000 |
| 4 | Tarun | Analytics | 30000 |
A Record or Row is a single entry in a table. In a table, a record represents a collection of connected data. The Employee table, for example, has four records.
A table is made up of numerous records (rows), each of which can be split down into smaller units called Fields(columns). ID, Name, Department, and Salary are the four fields in the Employee table above.
Q18. What is a UNIQUE constraint?
The UNIQUE constraint guarantees that every value in a column is unique.
Q19. What is schema in SQL Server?
In SQL Server, a schema is a logical collection of database objects such as tables, views, stored procedures, indexes, triggers, functions. It can be thought of as a container, created by a database user. The database user who creates a schema is the schema owner.
- A schema can belong to only one database whereas a database can have one or multiple schemas.
- There are no restrictions on the number of objects in a schema.
- SQL Server provides us with a few built-in schemas such as dbo, guest, sys, etc.
- A database schema can be owned by a database role or an application role along with the database user. They are called schema owners.
- dbo is the default schema for a newly created database.
- Schema ownership can be transferred from one user to another user in the same database.
- A database user can be dropped without dropping the database objects owned by the user. But the schema cannot be deleted if it owns database objects.
Q20. How to create a temp table in SQL Server?
Temporary tables are created in TempDB and are erased automatically after the last connection is closed. We may use Temporary Tables to store and process interim results. When we need to store temporary data, temporary tables come in handy.
The following is the syntax for creating a Temporary Table:
CREATE TABLE #Employee (id INT, name VARCHAR(25))
INSERT INTO #Employee VALUES (01, ‘Ashish’), (02, ‘Atul’)
Q21. What is the case when in SQL Server?
1 2 3 4 5 6 | CASEWHEN condition1 THEN result1WHEN condition2 THEN result2WHEN conditionN THEN resultNELSE resultEND; |
Q22. NoSQL vs SQL
In summary, the following are the five major distinctions between SQL and NoSQL:
- Relational databases are SQL, while non-relational databases are NoSQL.
- SQL databases have a specified schema and employ structured query language. For unstructured data, NoSQL databases use dynamic schemas.
- SQL databases scale vertically, but NoSQL databases scale horizontally.
- NoSQL databases are document, key-value, graph, or wide-column stores, whereas SQL databases are table-based.
- SQL databases excel in multi-row transactions, while NoSQL excels at unstructured data such as documents and JSON.
Q23. 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’.
Q24. What is BLOB and TEXT in MySQL?
BLOB stands for Binary Large Objects and can be used to store binary data, whereas TEXT may be used to store a large number of strings. BLOB may be used to store binary data, which includes images, movies, audio, and applications.
BLOB values function similarly to byte strings, and they lack a character set. As a result, bytes’ numeric values are completely dependent on comparison and sorting.
TEXT values behave similarly to a character string or a non-binary string. The comparison/sorting of TEXT is completely dependent on the character set collection.
Q25. How to create a stored procedure using SQL Server?
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
Stored Procedure Syntax
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;Execute a Stored Procedure
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';Q26. What is Database Black Box Testing?
Black Box Testing is a software testing approach that involves testing the functions of software applications without knowing the internal code structure, implementation details, or internal routes. Black Box Testing is a type of software testing that focuses on the input and output of software applications and is totally driven by software requirements and specifications. Behavioral testing is another name for it.
Q27. What are the different types of SQL sandbox?
SQL Sandbox is a secure environment within SQL Server where untrusted programmes can be run. There are three different types of SQL sandboxes:
Safe Access Sandbox: In this environment, a user may execute SQL activities like as building stored procedures, triggers, and so on, but they can’t access the memory or create files.
Sandbox for External Access: Users can access files without having the ability to alter memory allocation.
Unsafe Access Sandbox: This contains untrustworthy code that allows a user to access memory.
Q28. What are joins in SQL?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 types of joins, as you can refer to below:
- Inner join: Inner Join in SQL is the most common type of join. It is used to return all the rows from multiple tables where the join condition is satisfied.
Left Join: Left Join in SQL is used to return all the rows from the left table but only the matching rows from the right table where the join condition is fulfilled.
Right Join: Right Join in SQL is used to return all the rows from the right table but only the matching rows from the left table where the join condition is fulfilled.
Full Join: Full join returns all the records when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
Q29. What is the difference between CHAR and VARCHAR2 datatype in SQL?
Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length. For example, char(10) can only store 10 characters and will not be able to store a string of any other length whereas varchar2(10) can store any length i.e 6,8,2 in this variable.
Q30. What is a Primary key?
A Primary key in SQL is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.- Uniquely identifies a single row in the table
- Null values not allowed
Example- In the Student table, Stu_ID is the primary key.
Q31. What are Constraints?
Constraints in SQL are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are:
Q32. What is the difference between DELETE and TRUNCATE statements?
Q33. What is a Unique key?
- Uniquely identifies a single row in the table.
- Multiple values allowed per table.
- Null values allowed.
Q34. What is a Foreign key in SQL?
- Foreign key maintains referential integrity by enforcing a link between the data in two tables.
- The foreign key in the child table references the primary key in the parent table.
- The foreign key constraint prevents actions that would destroy links between the child and parent tables.
Q35. Write a SQL query to display the current date?
In SQL, there is a built-in function called GetDate() which helps to return the current timestamp/date.
Q36. What do you mean by Denormalization?
In SQL, normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Denormalization, on the other hand, involves intentionally introducing redundancy into a database design to improve performance by reducing the need for expensive join operations.
Q37. What are Entities and Relationships?
Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables store data that represents one type of entity. For example – A bank database has a customer table to store customer information. The customer table stores this information as a set of attributes (columns within the table) for each customer.
Relationships: Relation or links between entities that have something to do with each other. For example – The customer name is related to the customer account number and contact information, which might be in the same table. There can also be relationships between separate tables (for example, customer to accounts).
Q38. What is an Index?
An index refers to a performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and hence it will be faster to retrieve data.
Q39. Explain different types of index in SQL.
There are three types of index in SQL namely:
Unique Index:
This index does not allow the field to have duplicate values if the column is unique indexed. If a primary key is defined, a unique index can be applied automatically.
Clustered Index:
This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index. Clustered index is used for easy retrieval of data from the database and its faster
Non-Clustered Index:
Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data. Each table can have many nonclustered indexes. Reading from non clustered index is relatively slower.
Q40. What is Normalization and what are the advantages of it?
Normalization in SQL is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:
- Better Database organization
- More Tables with smaller rows
- Efficient data access
- Greater Flexibility for Queries
- Quickly find the information
- Easier to implement Security
- Allows easy modification
- Reduction of redundant and duplicate data
- More Compact Database
- Ensure Consistent data after modification
Q41. What is the difference between DROP and TRUNCATE commands?
DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.
Q42. Explain different types of Normalization.
There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one.The first three normal forms are usually adequate.
Normal Forms are used in database tables to remove or decrease duplication. The following are the many forms:
First Normal Form:
When every attribute in a relation is a single-valued attribute, it is said to be in first normal form. The first normal form is broken when a relation has a composite or multi-valued property.
Second Normal Form:
A relation is in second normal form if it meets the first normal form’s requirements and does not contain any partial dependencies. In 2NF, a relation has no partial dependence, which means it has no non-prime attribute that is dependent on any suitable subset of any table candidate key. Often, the problem may be solved by setting a single column Primary Key.
Third Normal Form:
If a relation meets the requirements for the second normal form and there is no transitive dependency, it is said to be in the third normal form.
Q43. What is SQL Injection?
SQL injection is a sort of flaw in website and web app code that allows attackers to take control of back-end processes and access, retrieve, and delete sensitive data stored in databases. In this approach, malicious SQL statements are entered into a database entry field, and the database becomes exposed to an attacker once they are executed. By utilising data-driven apps, this strategy is widely utilised to get access to sensitive data and execute administrative tasks on databases. SQLi attack is another name for it.
The following are some examples of SQL injection:
- Getting access to secret data in order to change a SQL query to acquire the desired results.
- UNION attacks are designed to steal data from several database tables.
- Examine the database to get information about the database’s version and structure
Q44. How many Aggregate functions are available in SQL?
SQL provides seven (7) aggregate functions, which are given below:
AVG(): returns the average value from specified columns.
COUNT(): returns the number of table rows, including rows with null values.
MAX(): returns the largest value among the group.
MIN(): returns the smallest value among the group.
SUM(): returns the total summed values(non-null) of the specified column.
FIRST(): returns the first value of an expression.
LAST(): returns the last value of an expression.
Q45. What is the default ordering of data using the ORDER BY clause? How could it be changed?
The ORDER BY clause in MySQL can be used without the ASC or DESC modifiers. The sort order is preset to ASC or ascending order when this attribute is absent from the ORDER BY clause.
Q46. How do we use the DISTINCT statement? What is its use?
The SQL DISTINCT keyword is combined with the SELECT query to remove all duplicate records and return only unique records. There may be times when a table has several duplicate records.
The DISTINCT clause in SQL is used to eliminate duplicates from a SELECT statement’s result set.
Q47. What are the syntax and use of the COALESCE function?
The SQL Server COALESCE() function is useful to handle NULL values. The NULL values are replaced with the user-given value during the expression value evaluation process. The SQL Server Coalesce function evaluates the expression in a definite order and always results first not null value from the defined expression list.
Syntax :
COALESCE ( exv1, exv2..., exvN )
Where –
exv1, exv2…, exvN are expression values.Q48. What is the ACID property in a database?
ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
- Atomicity: Atomicity is also known as the ‘All or nothing rule’. The transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all.
- Consistency: This means that integrity constraints must be maintained so that the database is consistent before and after the transaction.
- Isolation: This ensures that multiple transactions can occur concurrently without leading to the inconsistency of the database state. Transactions occur independently without interference.
- Durability: This ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs. These updates now become permanent and are stored in non-volatile memory.
Q49. What do you mean by “Trigger” in SQL?
Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table.
Q50. 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.
Q51. What is the difference between cross join and natural join?
The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only. There is one significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned
Q52. What is subquery in SQL?
A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.
Q53. 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.
Q54. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?
BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;SELECT * FROM students where ROLL_NO IN (8,15,25);Q55. Why are SQL functions used?
SQL functions are used for the following purposes:
- To perform some calculations on the data
- To modify individual data items
- To manipulate the output
- To format dates and numbers
- To convert the data types
Q56. What is the need for MERGE statement?
This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
Q57. What do you mean by recursive stored procedure?
Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code n number of times.
Q58. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.
Q59. What are the various levels of constraints?
Constraints are the representation of a column to enforce data entity and consistency. There are two levels of a constraint, namely:
- column level constraint
- table level constraint
Q60. What is an ALIAS command?
ALIAS command in SQL is the name that can be given to any table or a column. This alias name can be referred in WHERE clause to identify a particular table or a column.
For example-
Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empIDIn the above example, emp refers to alias name for employee table and dept refers to alias name for department table.
Q61. What are aggregate and scalar functions?
Aggregate functions are used to evaluate mathematical calculation and returns a single value. These calculations are done from the columns in a table. For example- max(),count() are calculated with respect to numeric.
Scalar functions return a single value based on the input value. For example – UCASE(), NOW() are calculated with respect to string.
Q62. What is the main difference between SQL and PL/SQL?
SQL is a query language that allows you to issue a single query or execute a single insert/update/delete whereas PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as selects/inserts/updates/deletes.
Q63. What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.
Q64. What are Views used for?
A view refers to a logical snapshot based on a table or another view. It is used for the following reasons:
- Restricting access to data.
- Making complex queries simple.
- Ensuring data independence.
- Providing different views of same data.
Q65. What is a Stored Procedure?
A Stored Procedure is a function which consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required which saves time and avoid writing code again and again.
Q66. List some advantages and disadvantages of Stored Procedure?
Advantages:
A Stored Procedure can be used as a modular programming which means create once, store and call for several times whenever it is required. This supports faster execution. It also reduces network traffic and provides better security to the data.
Disadvantage:
The only disadvantage of Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server.
Q67. List all the types of user-defined functions?
There are three types of user-defined functions, namely:
- Scalar Functions
- Inline Table-valued functions
- Multi-statement valued functions
Scalar returns the unit, variant defined the return clause. Other two types of defined functions return table.
Q68. What do you mean by Collation?
Collation is defined as a set of rules that determine how data can be sorted as well as compared. Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc.
Q69. What are the different types of Collation Sensitivity?
Following are the different types of collation sensitivity:
- Case Sensitivity: A and a and B and b.
- Kana Sensitivity: Japanese Kana characters.
- Width Sensitivity: Single byte character and double-byte character.
- Accent Sensitivity.
Q70. What are Local and Global variables?
Local variables:
These variables can be used or exist only inside the function. These variables are not used or referred by any other function.
Global variables:
These variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called.
Q71. What is Auto Increment in SQL?
Autoincrement keyword allows the user to create a unique number to get generated whenever a new record is inserted into the table.
This keyword is usually required whenever PRIMARY KEY in SQL is used.
AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
Q72. What is a Datawarehouse?
Datawarehouse refers to a central repository of data where the data is assembled from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing. Warehouse data also have a subset of data called Data Marts.
Q73. What are STUFF and REPLACE function?
STUFF(string_expression,start, length, replacement_characters)string_expression: it is the string that will have characters substituted
length: It refers to the number of characters in the string which are substituted.
REPLACE (string_expression, search_string, replacement_string)Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called.Q74. Difference between Stored Procedure and Function in SQL Server?
Stored Procedures
Functions A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters.
Basic Differences between Stored Procedure and Function
1. The function must return a value but in Stored Procedure it is optional.
Even a procedure can return zero or n values.
2. Functions can have only input parameters for it whereas Procedures
can have input or output parameters.
3. Functions can be called from Procedure whereas Procedures cannot be
called from a Function.Advance Differences between Stored Procedure and Function
1. The procedure allows SELECT as well as DML(INSERT/UPDATE/
DELETE) statement in it whereas Function allows only SELECT
statement in it.
2. Procedures cannot be utilized in a SELECT statement whereas
Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere
in the WHERE/HAVING/SELECT section whereas Function can be.
4. Functions that return tables can be treated as another rowset.
This can be used in JOINs with other tables.
5. Inline Function can be though of as views that take parameters
and can be used in JOINs and other Rowset operations.
6. An exception can be handled by try-catch block in a Procedure
whereas try-catch block cannot be used in a Function.
7. We can use Transactions in Procedure whereas we can't use
Transactions in Function.
No comments:
Post a Comment