SQL Interview Questions and Answers for Freshers, Experienced | Database - Page 4
22. What are wild cards used in database for Pattern Matching ?
Frequently asked SQL Interview Questions
SQL 'Like' search takes more time to process . So before using like operator following tips should be considered .
Don't overuse wild cards. If another search operator will do, use it instead.
When you do use wild cards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended
Don't overuse wild cards. If another search operator will do, use it instead.
When you do use wild cards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended
SQL Interview Questions and answers on SQL Joins
23. Define Join and explain different type of joins?
Frequently asked SQL Interview Questions
In order to avoid data duplication , data is stored in related tables . Join keyword is used to fetch data from related table. Join return rows when there is at least one match in both tables . Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the left table .
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the right table .
Full Join
Return rows when there is a match in one of the tables .
24. What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.
25. What is Cross Join?
Cross Join will return all records where each row from the first table is combined with each row from the second table.
SQL Interview Questions and answers on Database Views
26. What is a view?
Frequently asked SQL Interview Questions
Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
27. What is a materialized view?
Materialized views is also a view but are disk based . Materialized views get updated on specific duration, base upon the interval specified in the query definition. We can index materialized view.
28. What are the advantages and disadvantages of views in a database?
Advantages:
1. Views doesn't store data in a physical location.
2. View can be use to hide some of the columns from the table
3. Views can provide Access Restriction, since data insertion , update and deletion is not possible on the view.
Disadvantages:
1. When a table is dropped , associated view become irrelevant.
2. Since view are created when a query requesting data from view is triggered, its bit slow
3. When views are created for large tables, it occupy more memory .