Q1. 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.
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 the 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.
Q2. 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. A view can have data from one or more tables. If any changes occur in the underlying table, the same changes are reflected in the views also.
Q3. Use of Views in SQL.
The primary use of a view is to implement the security mechanism. It is the searchable object where we can use a query to search the view as we use for the table. It only shows the data returned by the query that was declared when the view was created.
Q4. 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.
Q5. What are the different types of indexes in SQL?
Unique Index
Clustered Index
Non-Clustered Index
Bit-Map Index
Normal Index
Composite Index
B-Tree Index
Function-Based Index
Q6. 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. A unique index can be applied automatically when a primary key is defined.
Q7. 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. It is the only index, which has been automatically created when the primary key is generated.
Q8. What is the non-clustered index in SQL?
The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. We know that clustered indexes are created automatically when primary keys are generated, and 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.
Q9. 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.
Q10. What is the difference between clustered and non-clustered indexes in SQL?
1). A clustered index is a table or view where the data for the rows are stored. In a relational database, if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY. The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. It has a structure separate from the data row. The non-clustered indexes are also known as secondary indexes.
2). Clustered indexes store the data information and the data itself. Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data.
3). There can only be one clustered index per table. There can be one or more non-clustered indexes in a table.
4). A clustered index always contains an index id of 0. A non-clustered index always contains an index id>0.
Q11. Which are joins in SQL?
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.
Q12. What are the different types of joins in SQL?
INNER JOIN
SELF JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
Q13. What is INNER JOIN in SQL?
Inner join returns only those records from the tables that match the specified condition and hides other rows and columns. In simple words, it fetches rows when there is at least one match of rows between the tables found.
In the below image the region in red color is the output of the inner join
Q14. What is the Right JOIN in SQL?
The Right join is used to retrieve all rows from the right-hand table and only those rows from the other table that fulfilled the join condition. It returns all the rows from the right-hand side table even though there are no matches in the left-hand side table. If it finds unmatched records from the left side table, it returns a Null value. This join is also known as Right Outer Join.
In the below image the red color region is the output of the right join.
Q15. What is Left Join in SQL?
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. If it will not find any matching record from the right side table, then it returns null. This join can also be called a Left Outer Join.
In the below image the red color region is the output of the left join.
Q16. What is Full Join in SQL?
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. If a match is not found, it puts a NULL value. It is also known as FULL OUTER JOIN.
In the below image red color region is the output of the full join.
Q17. What is a "TRIGGER" in SQL?
A trigger is a set of SQL statements 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.
Q18. 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.
Q19. 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.
1) 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.
2) 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.
3) 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.
4) MINUS: This operator returns the records from the first query, which is not found in the second query. It does not return duplicate values.
Q20. What is the difference between IN and BETWEEN operators?
1) BETWEEN Operator is used to select the range of data between two values. The values can be numbers, text, and dates as well. IN 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.
2) BETWEEN Operator returns records whose column value lies in between the defined range. IN Operator compares the specified column's value and returns the records when the match exists in the set of values.
Q21. What is a constraint?
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. It enforces us to store valid data and prevents us from storing irrelevant data.
Q22. Constrainst levels.
1). 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.
2) Table Level Constraints: These constraints are applied to the entire table and limit the type of data that can be entered.
Q23. What is the ACID property in a database?
The ACID property is an acronym for Atomicity, Consistency, Isolation, and Durability. The ACID properties are meant for transaction that goes through 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.
Q24. What is 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.
Q25. What is Consistency?
The database changes state only when a transaction will be committed successfully. It also protects data from crashes.
Q26. What is Isolation?
It 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.
Q27. What is Durability?
It guarantees that once a transaction has been committed, it persists permanently even if the system crashes, power loss, or failed.
Q28. 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.
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.
Q29. Differenrt types of SQL functions.
1) User-Defined Function: Functions created by a user based on their needs are termed user-defined functions.
2) System Defined Function: Functions whose definition is defined by the system are termed system-defined functions. They are built-in database functions.
Q30. 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.
Single Line Comments: It starts with two consecutive hyphens (--).
Multi-line Comments: It starts with /* and ends with */.
Exception Handling Interview Questions
DBMS Interview Questions Set -1
DBMS Interview Questions Set -2
JPA Interview Questions Set -1
Spring Boot Interview Questions Set 1
Spring Boot Interview Questions Set 2
Core Java Interview Questions Set -1
Docker interview question Set -1
Docker interview question Set -2
No comments:
Post a Comment