Verbal Ability. Interview Questions. Company Questions. Artificial Intelligence. Cloud Computing. Data Science. Angular 7. Machine Learning. Data Structures.
Operating System. Computer Network. Compiler Design. Computer Organization. Discrete Mathematics. Ethical Hacking. Computer Graphics. Software Engineering. Web Technology. Cyber Security.
C Programming. 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. For example: Suppose we have a book which carries the details of the countries. If you want to find out information about India, why will you go through every page of that book? You could directly go to the index.
Then from the index, you can go to that particular page where all the information about India is given. 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. The following are the different types of Indexes supported in SQL:. 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. 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. 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. It cannot be able to alter the physical order of the table and maintains the logical order of data. The purpose of creating a non-clustered index is for searching the data. Its best example is a book where the content is written in one place, and the index is at a different place.
We can create 0 to non-clustered indexes in each table. The non-clustered indexing improves the performance of the queries which use keys without assigning the primary key. 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. The following comparison chart explains their main differences:.
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. Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables. 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 is found. 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. 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. 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 NULL value. 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. 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. It is often very useful to convert a hierarchical structure to a flat structure.
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:. It has a default feature to remove the duplicate rows from the tables. The following syntax illustrates the Union operator:. 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. MINUS: This operator returns the records from the first query, which is not found in the second query.
It does not return duplicate values. It ensures data accuracy and integrity inside the table. It enforces us to store valid data and prevents us from storing irrelevant data. If any interruption occurs between the constraint and data action, the action is failed.
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. Here is the demo example where we have a table named student that contains two names starting with the 'A' character. 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.
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. 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.
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. The NULL value is not the same as zero or a blank space.
The following points explain their main differences:. 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:. 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.
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':. 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. It always appends the second string into the end of the first string. For example:. It also trimmed extra spaces. 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.
It does not allow to have duplicate values. We can use it with the help of the following syntax:. Suppose we have a table 'customer' containing eight records in which the name column has some duplicate values.
Executing the below command will return a name column with unique values. By default, it will sort the table in ascending order. We have taken a customer table in the previous example. In the below output, we can see that the first query will sort the table data in ascending order based on the name column. However, if we run the second query by specifying the DESC keyword, the table's order is changed in descending order.
Answer: No. The above query does not return the output because we cannot use the WHERE clause to restrict the groups. The below comparison chart explains the most common differences:. All Interview. It is portable to all environments that support Oracle. Stored procedures help better sharing of application. Why it is used? What are the types of exceptions? A function returns a value while a stored procedure doesn? Faster access of data blocks in the table. Tell me any three.
Reinforcement Learning. R Programming. React Native. Python Design Patterns. Python Pillow. Python Turtle. Verbal Ability. Interview Questions. Company Questions. Artificial Intelligence. Cloud Computing. Data Science. Angular 7. Machine Learning. Data Structures. Operating System. Computer Network. Compiler Design. Computer Organization. Discrete Mathematics. Ethical Hacking. Computer Graphics. Software Engineering.
0コメント