41 Essential SQL Interview Questions and Answers

Each character set in MySQL might have more than one collation, and has, at least, one default collation. For more details please refer What is collation and character set in MySQL? In contrast, global variables are variables that are defined outside of functions.

• It’s based on the older ISAM code, but it comes with a lot of extra features.
• Data control language allows users to control access and permission management to the database.
• These are preferred when there isthe more than one table involved in a query.
• The consistency property ensures that any transaction will bring the database from one valid state to another.
• But, it can be used in queries if you identify normal join operation won’t be effective for your query.
• According to my knowledge, we can store the data of any length but it should not exceed 10 characters( if we define char).

This article is the perfect guide for you to learn all the concepts related to SQL, Oracle, MS SQL Server, and MySQL database. The NVL2 function in SQL query returns the third argument if the first argument is NULL. COMMIT statement allows saving the changes made in a transaction permanently. Once a transaction is committed, the previous values cannot be retrieved. The following five factors affect the functionalities of databases. It is used to write program blocks, functions, procedures, triggers, packages, and cursors.

Q16. What are SQL comments?

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records. Unique index can be applied automatically when primary key is defined. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

Note that autonomous transaction doesn’t share locks and resources with the main transaction. SQL injection is a malicious attack sent targeting an SQL server instance.

How to change a table name in SQL?

It builds and specifies the relationships among the database’s numerous entities. It refers to the several kinds of constraints that may be applied to a database. A table is made up of numerous records , each of which can be split down into smaller units called Fields. ID, Name, Department, and Salary are the four fields in the Employee table above. In a table, a record represents a collection of connected data. 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.

A tuple is a single row in a table that represents a single record of a relation. At the same time, tuple functions allow retrieving tuples from a database table. They are extensively used in analysis services that have multidimensional structures. IntersectThis operator returns the common records of the result sets of two or more SELECT statements. An index is used to retrieve data from a database quickly. Generally, indexes have keys taken from the columns of tables and views. We can say, SQL indexes are similar to the indexes in books that help to identify pages in the books quickly.

What is an SQL server?

The Full Join results from a combination of both left and right join that contains all the records from both tables. It fetches rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side tables. The Left Join is used to fetch all rows from the left-hand table and common records between the specified tables. It returns all the rows from the left-hand side table even though there are no matches on the right-hand side table.

A trigger is a set of SQL statements that reside in a system catalog. It is a special type of stored procedure that is invoked automatically in response to an event. UNIQUE INDEX is used to enforce the uniqueness of values in single or multiple columns. We can create more than one unique index in a single table.

What is Dynamic SQL, and when can you use it?

SQL, which stands for Structured Query Language, is a programming language to communicate with data stored in a relational database management system. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently . To defend against power loss, transactions must be recorded in a non-volatile memory. The RANK function determines the rank for each row within your ordered partition in the result set. If the two rows are assigned the same rank, then the next number in the ranking will be its previous rank plus a number of duplicate numbers. For example, if we have three records at rank 4, the next rank listed would be ranked 7.

• All the constraints will be removed after the execution of the DROP function.
• Did the candidate remember to use a LEFT JOIN instead of an inner JOIN when joining the customer table for the referring customer name?
• Its best example is a book where the content is written in one place, and the index is at a different place.
• A live lock also occurs when read transactions create a table or page.
• The ACID properties are meant for the transaction that goes through a different group of tasks.
• Both IN and BETWEEN operators are used to return records for multiple values from a table.

In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” . If this option is turned on, it will display query execution plans in a separate window when a query is run.

Q61. What is the difference between DROP and TRUNCATE commands?

If you lose your index file, you may always restore it by recreating indexes. SQL skills aid data analysts in the creation, maintenance, and retrieval of data from relational databases, which divide data into columns and rows.

Tokens in PostgreSQL act as the building blocks of a source code. Commands are composed of a series of tokens and terminated by a semicolon(“;”).