22. What are wild cards used in database for Pattern Matching ?
SQL Like operator is used for pattern matching. SQL 'Like' command takes more time to process. So before using "like" operator, consider suggestions given below on when and where to use wild card search.
1) Don't overuse wild cards. If another search operator will do, use it instead.
2) When you do use wild cards, try not to use them at the beginning of the search pattern, unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
3) Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended.
SQL Joins Interview Questions and answers23. Define Join and explain different type of joins?
Another frequently asked SQL Interview Questions on Joins. In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related tables. "Join" return rows when there is at least one match in both table. Type of joins are Right Join Return all rows from the right table, even if there are no matches in the left table.
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.
Database Views Interview Questions
26. What is a view?
The 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 are also a view but are disk based. Materialized views get updates 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 don't store data in a physical location. 2. The view can be used to hide some of the columns from the table. 3. Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view. Disadvantages: 1. When a table is dropped, associated view become irrelevant. 2. Since the view is created when a query requesting data from view is triggered, its a bit slow. 3. When views are created for large tables, it occupies more memory.