Database Questions 101 To Know

Database Questions 101

• Q1. What are the different subsets of SQL?
  • Language Data Definition (DDL) – enables you to perform different database operations, for example, CREATE, ALTER and DELETE objects.
  • Data Manipulation Language (DML) – You can access and manipulate data. You can upload, edit, delete and retrieve your data from the database.
  • Data Control Language(DCL) – allows access to the database to be controlled. Example – Grant, Revoke access allowances.

• Q2. What is the difference between CHAR and VARCHAR2 datatype in SQL?
  • Char and Varchar2 are used in data center characters; varchar2 is used in variable-length character strings while Char is used in fixed-length lines. For instance, Char (10) can only store ten characters and cannot hold any other series, while varchar2(10) can store any length, i.e., 6.8.2, in this variable.

• Q3. What do you mean by data integrity?
  • Data integrity determines the quality and continuity of the stored data in a database. It also defines integrity constraints to impose business rules on the data as entered into an application or a database.

• Q4. What do you mean by Denormalization?
  • Denormalization means a method used to access data from higher to lower database types. It allows the database managers to improve the efficiency of the whole infrastructure by adding redundancy to a table. It adds redundant data to a table by incorporating a table of database queries that combine data from different tables.

• Q5. What is Normalization, and what are the advantages of it?
  • SQL standardization is the method of grouping data to prevent repetition and redundancy. Some of the benefits are:
    • Better organization of databases
    • More tables and fewer rows
    • Efficient access to data
    • Greater Queries Flexibility
    • Find facts quickly
    • Easier security implementation
    • Allows quick change
    • Redundant and duplicate data reduction
    • More Database Compact
    • Ensure consistent data after change

• Q6.  Are NULL values the same as that of zero or a blank space?
  • A value of NULL is not the same as a value of zero or a blank space. NULL is a value inaccessible, undefined, allocated, or not applicable, while a zero is a number and a room is a character.

• Q7. What is a subquery in SQL?
  • A subquery is a query within another query that defines the query to retrieve data or information from the database. In a subquest, the external query is the main query, while the internal question is called the subquery. A subquery is often run first, and subquery results are transferred to the main query. It can be nested within a SELECT, UPDATE, or any other question. A sub-quest may also use any contrast operators such as >,< or =.

• Q8. What do you mean by recursive stored procedure?
  • A recursive stored process refers to a stored procedure that calls for itself until it reaches some limit. This recursive function or process allows programmers to use n number of times for the same collection of code.

• Q9. What is the difference between a ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
  • The HAVING clause can only be used with the SELECT declaration. It is usually used under the GROUP BY Clause and, if GROUP BY is not used, HAVING is complied with as a WHERE clause. The GROUP BY function uses a Clause only in a query, while the WHERE clause is used for each row until the GROUP BY process in a question is used.

• Q10. Advantages and Disadvantages of Stored Procedure?
  • The stored process can be used as modular programming, creating once, storing, and calling multiple times when necessary. This supports quicker execution rather than various queries. This reduces network traffic and improves data protection. The downside is that it can only be executed on the database and uses more data server memory.

• Q11. What are the differences between OLTP and OLAP?
  • OLTP stands for Online Transaction Processing, a software class that supports transaction-oriented programs. The ability to sustain competition is an essential feature of an OLTP scheme. To prevent individual failure points, OLTP systems also adopt a decentralized architecture. Generally, these systems are built for a large number of end-users who perform quick transactions. In general, queries in these databases are easy, require fast reaction times, and return relatively few records. For such systems, the number of transactions per second serves as an appropriate indicator.
    
    
  • OLAP stands for Online Analytical Processing, a software program that describes online transactions with a relatively low frequency. Questions are often too complex and require a whole range of aggregations. The efficiency measurement for OLAP systems relies heavily on response time. Such systems are commonly used in multi-dimensional schemes for data mining or maintenance of aggregated historical information.

• Q12. What is a SQL Server Index?
  • One of the most significant variables in the performance tuning phase is the SQL Server index. Indexes are created to speed up data recovery and query processing from a database table or view by providing fast access to the database table rows without searching all table data to get the requested information. You can imagine a table index similar to a book index which allows you, rather than reading all the book pages, to find the information you want very quickly in your book to find a particular object.

• Q13. Why is it not recommended to use GUID and CHARACTER columns as Clustered index keys?
  • The main problem for GUID columns stored in the UNIQUE IDENTIFIER columns is the existence of the GUID that is greater than 16-byte sizes and that is created randomly, other than the IDENTITY integer values that are continuously growing. For the columns of CHARACTER. The fundamental problems include a limited sorting output of data types, large size, unincreasing values, and non-static values, which often shift in business applications and are not compared to binary values during the sorting process character mechanism depends on the collection being used.

• Q14. How could we benefit from the INCLUDE feature to overcome Non-Clustered index limitations?
  • Instead of creating an unclustered index with a comprehensive key, large columns that are used for the query cover can be used as non-key columns in the non-clustered Index up to 1023 columns, using the CREATE INDEX T-SQL declaration INCLUDE Clause implemented with at least one key column in the SQL Server 2005. This INCLUDE functionality expands the functionality of non-clustered indexes by allowing further queries to be covered by inserting columns as non-key columns, which are only saved and sorted at the index level without having to take into consideration the column values at the root and intermediate levels of the non-clustered Index. In this case, the SQL Server Query Optimizer will find all columns available from this Index without having to search additionally. The use of the included columns will help prevent exceeding the non-clustered size limit of 900 bytes and 16 columns in an index key. When measuring the size and number of columns of the index key, the SQL Server Database Engine does not include columns in the non-clustered Index. Furthermore, SQL Server allows you to have columns of non-clustered indices non-key columns with data types such as VARCHAR(MAX), NVARCHAR(MAX), text, text, and image that are not permitted in the index key.

• Q15. What is the difference between index Rebuild and Index Reorganize operations?
  • Fragmentation of indexes can be solved by routine reconstruction and reorganization of SQL Server indexes. The Rebuild Index operation eliminates fragmentation by removing the Index and restoring it, by defragmenting all index levels, by compacting index pages using Fill Factor values specified in a reconstruction command or with the current value set, and by updating FULLSCAN index statistics of all data.
    
    
  • The Index Reorganizes procedure rearranges the index leaf-level pages physically by the logical order of the leaf nodes. The Index will still reorganize the service online. Microsoft recommends fixing index fragmentation problems by reconstructing the Index if the percentage fragmentation reaches 30%. Microsoft recommends setting the fragmentation index by reorganizing the Index if the index fragmentation exceeds 5% and under 30%.

• Q16. What is PL SQL?
  • PL SQL stands for Structured Query Language Procedural language constructs. Oracle has introduced PL SQL to solve the constraints of a single SQL. Pl SQL thus adds to the simple vanilla SQL in the procedural language approach. One thing to remember here is that pl SQL is for Oracle databases only. You can’t deal with PL SQL because you don’t have an Oracle database. Although we have been able to generate DDL and DML queries with SQL, we can build functions, triggers, and other procedural constructs with the aid of PL SQL.

• Q17. What is ETL in SQL?
  • Extract, transform, and load is ETL. This is a three-step method in which the data from sources should be extracted. When we collect data from various sources, raw data is what we have. This raw data must be converted to a clean format in the second process. Finally, we will need to load this clever data into software to help us find insights.

Also read Top Software Quality Assurance (QA) Interview Questions