Top 90 SQL Interview Questions
What is the difference between DELETE and TRUNCATE?
The differences are: The basic difference in both is DELETE command is DML command and the TRUNCATE command is DDL. DELETE command is used to delete a specific row from the table whereas the TRUNCATE command is used to remove all rows from the table. We can use the DELETE command with WHERE clause but cannot use the TRUNCATE command with it.
What is an inconsistent dependency?
An Inconsistent dependency refers to the difficulty of getting relevant data due to a missing or broken path to the data. It leads users to search the data in the wrong table, resulting in an error as an output.
What is a Database?
An organized collection of related data
How to write a query to show the details of a student from Students table whose name start with K?
SELECT * FROM Student WHERE Student_Name like 'K%'; Here 'like' operator is used to perform pattern matching.
What is the use of the NVL function?
NVL function is used to convert the null value to its actual value.
How to write an SQL query to find students' names start with 'A'?
SELECT * FROM student WHERE stud_name like 'A%';
How many row comparison operators are used while working with a subquery?
There are 3-row comparison operators that are used in subqueries such as IN, ANY and ALL.
Is the following query return the output?
SELECT subject_code, AVG (marks) FROM Students WHERE AVG(marks) > 70 GROUP BY subject_code; Answer: No. The above query does not return the output because we cannot use the WHERE clause to restrict the groups. We need to use the HAVING clause instead of the WHERE clause to get the correct output.
What is self-join and what is the requirement of self-join?
A SELF JOIN is used to join a table with itself. This join can be performed using table aliases, which allow us to avoid repeating the same table name in a single sentence. It will throw an error if we use the same table name more than once in a single query without using table aliases. A SELF JOIN is required when we want to combine data with other data in the same table itself. It is often very useful to convert a hierarchical structure to a flat structure.
What is view in SQL?
A View can be defined as a virtual table that contains rows and columns with fields from one or more tables.
What is clustered index in SQL?
A clustered index is actually a table where the data for the rows are stored. It determines the order of the table data based on the key values that can sort in only one direction. Each table can have only one clustered index. It is the only index, which has been automatically created when the primary key is generated. If many data modifications needed to be done in the table, then clustered indexes are preferred.
What is a Cursor?
A cursor is a database object which is used to manipulate data in a row-to-row manner. Declare Cursor Open Cursor Retrieve row from the Cursor Process the row Close Cursor Deallocate Cursor
What is a unique key?
A unique key is a single or combination of fields that ensure all values stores in the column will be unique. It means a column cannot stores duplicate values. This key provides uniqueness for the column or set of columns. For example, the email addresses and roll numbers of student's tables should be unique. It can accept a null value but only one null value per column. It ensures the integrity of the column or group of columns to store different values into a table.
What is a primary key?
A primary key is a field or the combination of fields that uniquely identify each record in the table. It is one of a special kind of unique key. If the column contains a primary key, it cannot be null or empty. A table can have duplicate columns, but it cannot have more than one primary key. It always stores unique values into a column. For example, the ROLL Number can be treated as the primary key for a student in the university or college.
What do you mean by Stored Procedures? How do we use it?
A stored procedure is a collection of SQL statements that can be used as a function to access the database. We can create these stored procedures earlier before using it and can execute them wherever required by applying some conditional logic to it. Stored procedures are also used to reduce network traffic and improve performance.
What are tables and fields in the database?
A table is a set of organized data in the form of rows and columns. It enables users to store and display records in the structure format. It is similar to worksheets in the spreadsheet application. Here rows refer to the tuples, representing the simple data item, and columns are the attribute of the data items present in a particular row. Columns can categorize as vertical, and Rows are horizontal. Fields are the components to provide the structure for the table. It stores the same category of data in the same data type. A table contains a fixed number of columns but can have any number of rows known as the record. It is also called a column in the table of the database. It represents the attribute or characteristics of the entity in the record.
What are transactions and their controls?
A transaction means a group of SQL queries executed on database records. There are 4 transaction controls such as COMMIT: It is used to save all changes made through the transaction. ROLLBACK: It is used to roll back the transaction. All changes made by the transaction are reverted back and the database remains as before. SET TRANSACTION: Set the name of the transaction. SAVEPOINT: It is used to set the point where the transaction is to be rolled back.
What are SQL comments?
Comments are explanations or annotations in SQL queries that are readable by programmers. It's used to make SQL statements easier to understand for humans. During the parsing of SQL code, it will be ignored. Comments can be written on a single line or across several lines. Single Line Comments: It starts with two consecutive hyphens (--). Multi-line Comments: It starts with /* and ends with */.
What is meant by DBMS?
DBMS stands for Database Management System. It is a software program that primarily functions as an interface between the database and the end-user. It provides us the power such as managing the data, the database engine, and the database schema to facilitate the organization and manipulation of data using a simple query in almost no time. The following are the components of a DBMS: Software Data Procedures Database Languages Query Processor Database Manager Database Engine Reporting
What is the purpose of DDL Language?
DDL stands for Data definition language. It is the subset of a database that defines the data structure of the database when the database is created. For example, we can use the DDL commands to add, remove, or modify tables. It consists of the following commands: CREATE, ALTER and DELETE database objects such as schema, tables, indexes, view, sequence, etc.
What are different JOINS used in SQL?
INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one matching column. LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from the LEFT table and its matched rows from a RIGHT table. RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from the LEFT table. FULL JOIN (FULL OUTER JOIN): This joins returns all results when there is a match either in the RIGHT table or in the LEFT table.
What is the primary use of Normalization?
Normalization is mainly used to add, delete or modify a field that can be made in a single table. The primary use of Normalization is to remove redundancy and remove the insert, delete and update distractions. Normalization breaks the table into small partitions and then links them using different relationships to avoid the chances of redundancy.
What is Normalization in a Database?
Normalization is used to minimize redundancy and dependency by organizing fields and tables of a database. First normal form(1NF) Second normal form(2NF) Third normal form(3NF) Boyce-Codd normal form(BCNF) Using these steps, the redundancy, anomalies, inconsistency of the data in the database can be removed.
What is Normalization? How many Normalization forms are there?
Normalization is used to organize the data in such a manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies. There are 5 forms of Normalization: First Normal Form (1NF): It removes all duplicate columns from the table. It creates a table for related data and identifies unique column values. First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using the primary key. Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through the primary key. Fourth Normal Form (4NF): Follows 3NF and does not define multi-valued dependencies. 4NF is also known as BCNF.
What is SQL Sandbox in SQL Server?
SQL Sandbox is a safe place in the SQL server environment where untrusted scripts are executed. There are 3 types of SQL sandbox: Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory as well as cannot create files. External Access Sandbox: Users can access files without having the right to manipulate the memory allocation. Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.
When SQL appeared?
SQL first appeared in 1974. It is one of the most used languages for maintaining the relational database. In 1986, SQL became the standard of the American National Standards Institute (ANSI) and ISO (International Organization for Standardization) in 1987.
What are the different types of indexes in SQL?
SQL indexes are nothing more than a technique of minimizing the query's cost. The higher the query's cost, the worse the query's performance. Unique Index Clustered Index Non-Clustered Index Bit-Map Index Normal Index Composite Index B-Tree Index Function-Based Index
What is the difference between SQL and PL/SQL?
SQL is a Structured Query Language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.
Does SQL support programming language features?
SQL refers to the Standard Query Language. Therefore, it is true that SQL is a language but does not actually support the programming language. It is a common language that doesn't have a loop, conditional statements, and logical operations. It cannot be used for anything other than data manipulation. It is a command language to perform database operations. The primary purpose of SQL is to retrieve, manipulate, update, delete, and perform complex operations like joins on the data present in the database.
What is SQL?
SQL stands for the Structured Query Language. It is the standard language used to maintain the relational database and perform many different data manipulation operations on the data. SQL was initially invented in 1970. It is a database language used for database creation, deletion, fetching and modifying rows, etc. sometimes, it is pronounced as 'sequel.' We can also use it to handle organized data comprised of entities (variables) and relations between different entities of the data.
What is the usage of the NVL() function?
The NVL() function is used to convert the NULL value to the other 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 unchanged. This function is used in Oracle, not in SQL and MySQL. Instead of NVL() function, MySQL have IFNULL() and SQL Server have ISNULL() function.
What is the default ordering of data using the ORDER BY clause? How could it be changed?
The ORDER BY clause is used to sort the table data either in ascending or descending order. By default, it will sort the table in ascending order. If we want to change its default behavior, we need to use the DESC keyword after the column name in the ORDER BY clause. SELECT expressions FROM tables WHERE conditions ORDER BY expression [ASC | DESC];
What is the difference between the RANK() and DENSE_RANK() functions?
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. The DENSE_RANK function assigns a unique rank for each row within a partition as per the specified column value without any gaps. It always specifies ranking in consecutive order. If the two rows are assigned the same rank, this function will assign it with the same rank, and the next rank being the next sequential number. For example, if we have 3 records at rank 4, the next rank listed would be ranked 5.
What is a constraint? Tell me about its various levels.
The constraint is used to specify the rule and regulations that allows or restricts what values/data will be stored in the table. It ensures data accuracy and integrity inside the table. If any interruption occurs between the constraint and data action, the action is failed. Some of the most commonly used constraints are NOT NULL, PRIMARY KEY, FOREIGN KEY, AUTO_INCREMENT, UNIQUE KEY, etc. CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ......... ); SQL categories the constraints into two levels: Column Level Constraints: These constraints are only applied to a single column and limit the type of data that can be stored in that column. Table Level Constraints: These constraints are applied to the entire table and limit the type of data that can be entered.
What is the difference between clustered and non-clustered indexes?
The differences between the two are as follows: - One table can have only one clustered index but multiple non-clustered indexes. - Clustered indexes can be read rapidly rather than non-clustered indexes. - Clustered indexes store data physically in the table or view whereas, non-clustered indexes do not store data in the table as it has separate structure from the data row.
What is the difference between DELETE and TRUNCATE statements in SQL?
The main difference between them is that the delete statement deletes data without resetting a table's identity, whereas the truncate command resets a particular table's identity.
What is the difference between DROP and TRUNCATE?
TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it also cannot be retrieved back.
State some properties of Relational databases?
Properties are as follows: In relational databases, each column should have a unique name. The sequence of rows and columns in relational databases is insignificant. All values are atomic and each row is unique.
What are the properties of the transaction?
Properties of the transaction are known as ACID properties. These are: Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully or not. If not, then the transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes are undone. Consistency: Ensures that all changes made through successful transactions are reflected properly on the database. Isolation: Ensures that all transactions are performed independently, and changes made by one transaction are not reflected on others. Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure.
What do you mean by Subquery?
Query within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query.
What is RDBMS?
RDBMS stands for Relational Database Management System. It is a database management system based on a relational model. It facilitates you to manipulate the data stored in the tables by using relational operators. RDBMS stores the data into the collection of tables and links those tables using the relational operators easily whenever required. Examples of relational database management systems are Microsoft Access, MySQL, SQL Server, Oracle database, etc.
What is a "TRIGGER" in SQL?
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. It allows us to execute a batch of code when an insert, update or delete command is run against a specific table because the trigger is the set of activated actions whenever DML commands are given to the system. SQL triggers have two main components one is action, and another is an event. When certain actions are taken, an event occurs as a result of those actions.
What is a view in SQL?
A view is a database object that has no values. It is a virtual table that contains a subset of data within a table. It looks like an actual table containing rows and columns, but it takes less space because it is not present physically. It is operated similarly to the base table but does not contain any data of its own. Its name is always unique. A view can have data from one or more tables. If any changes occur in the underlying table, the same changes reflected in the views also.
What is an Index in SQL?
An index is a disc structure associated with a table or view that speeds up row retrieval. It reduces the cost of the query because the query's high cost will lead to a fall in its performance. It is used to increase the performance and allow faster retrieval of records from the table. Indexing reduces the number of data pages we need to visit to find a particular data page. It also has a unique value meaning that the index cannot be duplicated. An index creates an entry for each value which makes it faster to retrieve data.
What is the difference between IN and BETWEEN operators?
BETWEEN operator is used to selects the range of data between two values. The values can be numbers, text, and dates as well. IN is a logical operator to determine whether or not a specific value exists within a set of values. This operator reduces the use of multiple OR conditions with the query.
What is meant by case manipulation functions?
Case manipulation functions are part of the character functions. It converts the data from the state in which it is already stored in the table to upper, lower, or mixed case. The conversion performed by this function can be used to format the output. We can use it in almost every part of the SQL statement. Case manipulation functions are mostly used when you need to search for data, and you don't have any idea that the data you are looking for is in lower case or upper case. There are three case manipulation functions in SQL: LOWER: This function is used to converts a given character into lowercase. The following example will return the 'STEPHEN' as 'stephen': SELECT LOWER ('STEPHEN') AS Case_Reault FROM dual; NOTE: Here, 'dual' is a dummy table. UPPER: This function is used to converts a given character into uppercase. The following example will return the 'stephen' as 'STEPHEN': SELECT UPPER ('stephen') AS Case_Reault FROM dual; INITCAP: This function is used to converts given character values to uppercase for the initials of each word. It means every first letter of the word is converted into uppercase, and the rest is in lower case. The following example will return the 'hello stephen' as 'Hello Stephen': SELECT INITCAP ('hello stephen') AS Case_Reault FROM dual;
Explain character-manipulation functions?
Character-manipulation functions are used to change, extract, and alter the character string. When one or more characters and words are passed into the function, the function will perform its operation on those input strings and return the result. A) CONCAT: This function is used to join two or more values together. It always appends the second string into the end of the first string. For example: Input: SELECT CONCAT ('Information-', 'technology') FROM DUAL; Output: Information-technology B) SUBSTR: It is used to return the portion of the string from a specified start point to an endpoint. For example: Input: SELECT SUBSTR ('Database Management System', 9, 11) FROM DUAL; Output: Management C) LENGTH: This function returns the string's length in numerical value, including the blank spaces. For example: Input: SELECT LENGTH ('Hello Javatpoint') FROM DUAL; Output: 16 D) INSTR: This function finds the exact numeric position of a specified character or word in a given string. For example: Input: SELECT INSTR ('Hello Javatpoint', 'Javatpoint'); Output: 7 E) LPAD: It returns the padding of the left-side character value for right-justified value. For example: Input: SELECT LPAD ('200', 6,'*'); Output: ***200 F) RPAD: It returns the padding of the right-side character value for left-justified value. For example: Input: SELECT RPAD ('200', 6,'*'); Output: 200*** G) TRIM: This function is used to remove all the defined characters from the beginning, end, or both. It also trimmed extra spaces. For example: Input: SELECT TRIM ('A' FROM 'ABCDCBA'); Output: BCDCB H) REPLACE: This function is used to replace all occurrences of a word or portion of the string (substring) with the other specified string value. For example: Input: SELECT REPLACE ( 'It is the best coffee at the famous coffee shop.', 'coffee', 'tea'); Output: It is the best tea at the famous tea shop.
Why do we use SQL constraints? Which constraints we can use while creating a database in SQL?
Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it. Constraints are defined while creating the database itself with the CREATE TABLE statement or even after the table is created once with the ALTER TABLE statement. There are 5 major constraints are used in SQL, such as NOT NULL: That indicates that the column must have some value and cannot be left NULL. UNIQUE: This constraint is used to ensure that each row and column has a unique value and no value is being repeated in any other row or column. PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity. FOREIGN KEY: It is used to ensure the referential integrity of data in the table. It matches the value in one table with another using the PRIMARY KEY. CHECK: It ensures whether the value in columns fulfills the specified condition.
What is the purpose of DCL Language?
Data control language allows users to control access and permission management to the database. It is the subset of a database, which decides that what part of the database should be accessed by which user at what point of time. It includes two commands, GRANT and REVOKE. GRANT: It enables system administrators to assign privileges and roles to the specific user accounts to perform specific tasks on the database. REVOKE: It enables system administrators to revoke privileges and roles from the user accounts so that they cannot use the previously assigned permission on the database.
What is the purpose of DML Language?
Data manipulation language makes the user able to retrieve and manipulate data in a relational database. The DML commands can only perform read-only operations on data. We can perform the following operations using DDL language: Insert data into the database through the INSERT command. Retrieve data from the database through the SELECT command. Update data in the database through the UPDATE command. Delete data from the database through the DELETE command.
What is Denormalization in a Database?.
Denormalization is a technique used by database administrators to optimize the efficiency of their database infrastructure. The denormalization concept is based on Normalization., which allows us to add redundant data into a normalized database to alleviate issues with database queries that merge data from several tables into a single table. It adds redundant terms into the tables to avoid complex joins and many other complex operations. Denormalization doesn't mean that normalization will not be done. It is an optimization strategy that takes place after the normalization process
What is the difference between clustered and non-clustered indexes in SQL?
Indexing is a method to get the requested data very fast. There are mainly two types of indexes in SQL, clustered index and non-clustered index. The differences between these two indexes are very important from an SQL performance perspective.
What are the different types of joins in SQL?
Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables. INNER JOIN SELF JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN
Is a blank space or zero the same as a NULL value?
No. The NULL value is not the same as zero or a blank space. A NULL value is a value, which is 'unavailable, unassigned, unknown or not applicable.' It would be used in the absence of any value. We can perform arithmetic operations on it. Zero is a number, and a blank space is treated as a character. We can compare a blank space or a zero to another blank space or a zero. On the other hand, one NULL may not be the same as another NULL. NULL indicates that no data has been provided or that no data exists.
What are the different types of SQL operators?
Operators are the special keywords or special characters reserved for performing particular operations. They are also used in SQL queries. We can primarily use these operators within the WHERE clause of SQL commands. It's a part of the command to filters data based on the specified condition. The SQL operators can be categorized into the following types: Arithmetic operators: These operators are used to perform mathematical operations on numerical data. The categories of this operators are addition (+), subtraction (-), multiplication (*), division (/), remainder/modulus (%), etc. Logical operators: These operators evaluate the expressions and return their results in True or False. This operator includes ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE. Comparison operators: These operators are used to perform comparisons of two values and check whether they are the same or not. It includes equal to (=), not equal to (!= or <>), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), not less than (!<), not greater than (!>), etc. Bitwise operators: It is used to do bit manipulations between two expressions of integer type. It first performs conversion of integers into binary bits and then applied operators such as AND (& symbol), OR (|, ^), NOT (~), etc. Compound operators: These operators perform operations on a variable before setting the variable's result to the operation's result. It includes Add equals (+=), subtract equals (-=), multiply equals (*=), divide equals (/=), modulo equals (%=), etc. String operators: These operators are primarily used to perform concatenation and pattern matching of strings. It includes + (String concatenation), += (String concatenation assignment), % (Wildcard), [] (Character(s) matches), [^] (Character(s) not to match), _ (Wildcard match one character), etc.
How we can update the view?
SQL CREATE and REPLACE can be used for updating the view. Syntax: CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Explain the working of SQL Privileges?
SQL GRANT / REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database objects by using commands like SELECT, INSERT, UPDATE, DELETE, ALL, etc.
What is SQL Injection?
SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database in a way that once it is executed, the database is exposed to an attacker for the attack. This technique is usually used for attacking data-driven applications to have access to sensitive data and perform administrative tasks on databases.
What are functions and their usage in SQL?
SQL functions are simple code snippets that are frequently used and re-used in database systems for data processing and manipulation. Functions are the measured values. It always performs a specific task. The following rules should be remembered while creating functions: A function should have a name, and the name cannot begin with a special character such as @, $, #, or other similar characters. Functions can only work with the SELECT statements. Every time a function is called, it compiles. Functions must return value or result. Functions are always used with input parameters. SQL categories the functions into two types: User-Defined Function: Functions created by a user based on their needs are termed user-defined functions. System Defined Function: Functions whose definition is defined by the system are termed system-defined functions. They are built-in database functions. SQL functions are used for the following purposes: To perform calculations on data To modify individual data items To manipulate the output To format dates and numbers To convert data types
What is SQL Injection?
SQL injection is a type of vulnerability in website and web app code that allows attackers to control back-end operations and access, retrieve, and destroy sensitive data from databases. In this technique, malicious SQL statements are inserted into a database entry field, and once they are performed, the database becomes vulnerable to an attacker. This technique is commonly used to access sensitive data and perform administrative activities on databases by exploiting data-driven applications. It is also known as SQLi attack. Some common examples of SQL injection are: Accessing confidential data to modify an SQL query to get desired results. UNION attacks to steal data from different database tables. Examine the database to extract information regarding the version and structure of the database.
What is the difference between SQL and MySQL?
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
What are the usages of SQL?
SQL is responsible for maintaining the relational data and the data structures present in the database. To execute queries against a database To retrieve data from a database To inserts records in a database To updates records in a database To delete records from a database To create new databases To create new tables in a database To create views in a database To perform complex operations on the database.
Which are joins in SQL? Name the most commonly used SQL joins?
SQL joins are used to retrieve data from multiple tables into a meaningful result set. It is performed whenever you need to fetch records from two or more tables. They are used with SELECT statement and join conditions. INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN
What are Scalar functions in SQL?
Scalar Functions are as follows: UCASE(): Converts the specified field in the upper case. LCASE(): Converts the specified field in lower case. MID(): Extracts and returns character from the text field. FORMAT(): Specifies the display format. LEN(): Specifies the length of the text field. ROUND(): Rounds up the decimal field value to a number.
What is the difference between Nested Subquery and Correlated Subquery?
Subquery within another subquery is called Nested Subquery. If the output of a subquery depends on column values of the parent query table then the query is called Correlated Subquery. SELECT adminid(SELEC Firstname+' '+Lastname FROM Employee WHERE empid=emp. adminid)AS EmpAdminId FROM Employee; The result of the query is the details of an employee from the Employee table.
What is the ACID property in a database?
The ACID properties are meant for the transaction that goes through a different group of tasks. A transaction is a single logical order of data. It provides properties to maintain consistency before and after the transaction in a database. It also ensures that the data transactions are processed reliably in a database system. The ACID property is an acronym for Atomicity, Consistency, Isolation, and Durability. Atomicity: It ensures that all statements or operations within the transaction unit must be executed successfully. If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. Its main features are COMMIT, ROLLBACK, and AUTO-COMMIT. Consistency: This property ensures that the data must meet all validation rules. In simple words, we can say that the database changes state only when a transaction will be committed successfully. It also protects data from crashes. Isolation: This property guarantees that the concurrent property of execution in the transaction unit must be operated independently. It also ensures that statements are transparent to each other. The main goal of providing isolation is to control concurrency in a database. Durability: This property guarantees that once a transaction has been committed, it persists permanently even if the system crashes, power loss, or failed.
What are the syntax and use of the COALESCE function?
The COALESCE() function evaluates the arguments in sequence and returns the first NON-NULL value in a specified number of expressions. If it evaluates arguments as NULL or not found any NON-NULL value, it returns the NULL result. The syntax of COALESCE function is given below: COALESCE (exp1, exp2, .... expn) Example: SELECT COALESCE(NULL, 'Hello', 'Javatpoint', NULL) AS Result;
How do we use the DISTINCT statement? What is its use?
The DISTINCT keyword is used to ensure that the fetched value always has unique values. It does not allow to have duplicate values. The DISTINCT keyword is used with the SELECT statement and retrieves different values from the table's column. SELECT DISTINCT column_lists FROM table_name WHERE [condition];
Which function is used to return remainder in a division operator in SQL?
The MOD function returns the remainder in a division operation.
How many Aggregate functions are available in SQL?
The aggregate function is used to determine and calculate several values in a table and return the result as a single number. For example, the average of all values, the sum of all values, and the maximum and minimum value among particular groupings of values. function_name (DISTINCT | ALL expression) SQL provides seven (7) aggregate functions, which are given below: AVG(): This function is used to returns the average value from specified columns. COUNT(): This function is used to returns the number of table rows, including rows with null values. MAX(): This function is used to returns the largest value among the group. MIN(): This function is used to returns the smallest value among the group. SUM(): This function is used to returns the total summed values(non-null) of the specified column. FIRST(): This function is used to returns the first value of an expression. LAST(): This function is used to returns the last value of an expression.
What are the different types of database management systems?
The database management systems can be categorized into several types. Hierarchical databases (DBMS) Network databases (IDMS) Relational databases (RDBMS Object-oriented databases Document databases (Document DB) Graph databases ER model databases NoSQL databases
What are the subsets of SQL?
The following are four significant subsets of the SQL: Data definition language (DDL): It defines the data structure that consists of commands like CREATE, ALTER, DROP, etc. Data manipulation language (DML): It is used to manipulate existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc. Data control language (DCL): It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE. Transaction Control Language (TCL): It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.
Write the SQL query to get the third maximum salary of an employee from a table named employees.
The following query is the simplest way to get the third maximum salary of an employee: SELECT * FROM `employees` ORDER BY `salary` DESC LIMIT 1 OFFSET 2 A. Using LIMIT Keyword SELECT salary FROM employees ORDER BY salary DESC LIMIT 2, 1; B. Using Subquery SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 3) AS Temp ORDER BY salary LIMIT 1; C. Using TOP Keyword SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 3 salary FROM employees ORDER BY salary DESC) AS Temp ORDER BY salary ASC;
What is a foreign key?
The foreign key is used to link one or more tables together. It is also known as the referencing key. A foreign key is specified as a key that is related to the primary key of another table. It means a foreign key field in one table refers to the primary key field of the other table. It identifies each row of another table uniquely that maintains the referential integrity. The primary key-foreign key relationship is a very crucial relationship as it maintains the ACID properties of the database sometimes. It also prevents actions that would destroy links between the child and parent tables.
What is the non-clustered index in SQL?
The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. Non-clustered indexes are created when multiple joins conditions and various filters are used in the query. The non-clustered index and table data are both stored in different places. It cannot be able to alter the physical order of the table and maintains the logical order of data.
What is the difference between the WHERE and HAVING clauses?
The main difference is that the WHERE clause is used to filter records before any groupings are established, whereas the HAVING clause is used to filter values from a group.
What are the disadvantages of not performing database Normalization?
The major disadvantages are: The occurrence of redundant terms in the database causes the waste of space in the disk. Due to redundant terms, inconsistency may also occur. If any change is made in the data of one table but not made in the same data of another table, then inconsistency will occur. This inconsistency will lead to the maintenance problem and effects the ACID properties as well.
What is the Cartesian product of the table?
The output of Cross Join is called as a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 Rows.
What is the difference between a primary key and a unique key?
The primary key and unique key both are essential constraints of the SQL. The main difference among them is that the primary key identifies each record in the table. In contrast, the unique key prevents duplicate entries in a column except for a NULL value.
What is a Relationship? How many types of Relationships are there?
The relationship can be defined as the connection between more than one table in the database. There are 4 types of relationships: One to One Relationship Many to One Relationship Many to Many Relationship One to Many Relationship
How many Aggregate functions are available in SQL?
There are 7 aggregate functions in SQL: AVG(): Returns the average value from specified columns. COUNT(): Returns number of table rows. MAX(): Returns the largest value among the records. MIN(): Returns smallest value among the records. SUM(): Returns the sum of specified column values. FIRST(): Returns the first value. LAST(): Returns last value.
How many types of Privileges are available in SQL?
There are two types of privileges used in SQL, such as System privilege: System privilege deals with the object of a particular type and provides users the right to perform one or more actions on it. These actions include performing administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc. Object privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.
What is the SQL query to display the current date?
There is a built-in function in SQL called GetDate(), which is used to return the current timestamp.
What are triggers?
Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as INSERT, UPDATE or DELETE. You can invoke triggers explicitly on the table in the database. Action and Event are two main components of SQL triggers. When certain actions are performed, the event occurs in response to that action.
What are Nested Triggers?
Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE statements. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.
What is the unique index?
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. For creating a unique index, the user has to check the data in the column because the unique indexes are used when any column of the table has unique values. This indexing does not allow the field to have duplicate values if the column is unique indexed. A unique index can be applied automatically when a primary key is defined.
What are the different Clauses used in SQL?
WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition. GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly. HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of results or the entire result as a single group. It is much similar as WHERE clause but the only difference is you cannot use it without GROUP BY clause ORDER BY clause: This clause is used to define the order of the query output either in ascending (ASC) or in descending (DESC). Ascending (ASC) is set as the default one but descending (DESC) is set explicitly. USING clause: USING clause comes in use while working with SQL JOIN. It is used to check equality based on columns when tables are joined. It can be used instead of the ON clause in JOIN.
What are the set operators in SQL?
We use the set operators to merge data from one or more tables of the same kind. Although the set operators are like SQL joins, there is a significant distinction. SQL joins combine columns from separate tables, whereas SQL set operators combine rows from different queries. SQL queries that contain set operations are called compound queries. The set operators in SQL are categories into four different types: A. UNION: It combines two or more results from multiple SELECT queries into a single result set. It has a default feature to remove the duplicate rows from the tables. The following syntax illustrates the Union operator: SELECT columns FROM table1 UNION SELECT columns FROM table2; B. UNION ALL: This operator is similar to the Union operator, but it does not remove the duplicate rows from the output of the SELECT statements. The following syntax illustrates the UNION ALL operator: SELECT columns FROM table1 UNION ALL SELECT columns FROM table2; C. INTERSECT: This operator returns the common records from two or more SELECT statements. It always retrieves unique records and arranges them in ascending order by default. Here, the number of columns and data types should be the same. The following syntax illustrates the INTERSECT operator: SELECT columns FROM table1 INTERSECT SELECT columns FROM table2; D. MINUS: This operator returns the records from the first query, which is not found in the second query. It does not return duplicate values. The following syntax illustrates the MINUS operator:
Is it possible to implicitly insert a row for the identity column?
Yes. We can implicitly insert a row for the identity column. SET IDENTITY_INSERT TABLE1 ON INSERT INTO demo_table1 (id, name, branch) SELECT id, name, branch FROM demo_table2 SET IDENTITY_INSERT OFF
Is it possible to sort a column using a column alias?
Yes. We can use the alias method in the ORDER BY instead of the WHERE clause for sorting a column.
