Top 100 Database Interview Questions

  1. Home
  2. AWS
  3. Top 100 Database Interview Questions
database questions

The database defines the specific ways for organizing, managing, updating, controlling, and accessing the collection of data. So, if you are willing to start your career in this sector, it is important to cover both major and minor areas of the database. Talking about the present job market, the competition for getting the job has become quite tough. Because everyone wants to earn a good position. 

However, in order to help you in your journey to earn a database role. In this blog, we will be discussing and learning about the topmost database questions. These questions will help you gain confidence by covering all the important concepts of the database. So, let’s begin!

Top Database Interview Questions

1. Define Database.

Database refers to the managed collection of related data used for easily storing, accessing, and organizing the data for a specific purpose. This lets you organize data into tables, rows, columns, and index it to make it easier to find relevant information. For Example, A chemist has to keep a record and information of all the medicine in the medical store.

2. What is DBMS?

DBMS is a Database Management System. This refers to a collection of application programs that provide users permission for organizing, restoring, and retrieving information about data efficiently and effectively. For example, MySQL

3. What is RDBMS?

RDBS stands for Relational Database Management System which is developed on a relational model of data. The major role of RDBMS is to store the data in separate tables and they are related to the use of a common column. However, data can be accessed easily from the relational database using Structured Query Language (SQL).

4. Can you provide some of the advantages of a Database Management System (DBMS)?

The advantages include:

  • Firstly, in DBMS, the data storing is performed in a structured way by managing the data redundancy.
  • Secondly, only authorized users to have access to the database. They must have a username and password for using the database.
  • Thirdly, it offers data integrity for checking data accuracy and consistency in the database.
  • Next, there is support for backup and recovery of the data when necessary.
  • Lastly, it offers multiple user interfaces.
5. Explain Data Redundancy.

Data redundancy refers to the duplication of data or the same data occurring at multiple locations. Further, this duplicate data can be present in multiple locations of the database which can lead to storage space wastage and can even affect the data integrity.

6. How many types of relationships are there in the Database?

There are three types of relationships:

1. One-to-one

This can be defined as when one table has a relationship with another table having the same column. In this, every primary key relates only to one or no record in the related table.

2. One-to-many

This can be defined as when one table has a relationship with another table containing primary and foreign key relations. However, the primary key table consists of only one record that relates to none, one or many records in the related table.

3. Many-to-many

This can be defined as when each record in both the tables can relate to as many numbers of records in another table.

7. Define Normalization.

Normalization refers to the process of eliminating the redundant data from the database by splitting the table in an understandable manner for maintaining data integrity. This process has the ability to manage and save storage space.

8. What is  De-normalization?

De-normalization refers to the process of adding up redundant copies of data on the table for enhancing the speed of the complex queries in order for achieving better performance.

9. Name the various types of Normalization?

1. 1NF

A relation is First Normal Form (1NF), when all the entities of the table cintains a unique or atomic values.

2. 2NF

A relation is Second Normal Form (2NF) only if it is in 1NF. And when all the non-key attribute of the table depends on the primary key.

3. 3NF

A relation is Third Normal Form (3NF) only if it is in 2NF and there is no transition dependency exists.

10. Define the following terms:

1. Table

A table refers to a set of data that are organized in a model with Columns and Rows. In this, columns can be classified as vertical, and Rows as horizontal. 

2. Field

There are specified numbers of columns in a table which are known as fields. However, fields can have various types of data like text, numbers, dates, and hyperlinks.

11. What is the Super key?

A superkey refers to a group of single or multiple keys which can identify a row in a table. This has additional attributes that are not necessary for unique identification.

12. What is a Primary Key?

The primary key refers to a column or group of columns in a table that can uniquely identify every row in that table. However, the Primary Key can’t be a duplicate. That is to say, no two same values can appear in a table more than once. A table can have only one primary key. For example, a unique identification number (ID) is a primary key.

13. What are the basic rules of a Primary key?
  • Firstly, there cannot be the same primary key value in two rows.
  • Secondly, there must be a primary key value in every row.
  • Thirdly, the primary key field can never be null.
  • Lastly, if any foreign key refers to that primary key then the value in a primary key column can never be modified or updated.
14. Differentiate the Alternate key and Candidate key.

1. Alternate key

This refers to a column or group of columns in a table that uniquely identifies every row in that table. There can be multiple choices for a primary key in a table but the only one can be set as the primary key. In other words, all the keys which are not primary key are Alternate Key.

2. Candidate Key

This is a set of attributes that uniquely identify tuples in a table. This also refers to a super key with no repeated attributes. However, the Primary key must be selected from the candidate keys. And, there should be at least a single candidate key in a table. 

15. What are the properties of the Candidate key?
  • Firstly, the candidate key must have a unique value.
  • Secondly, there can be multiple attributes in a candidate key.
  • Thirdly, it must not consist of any null values.
  • Next, there must be uniqueness in the fields.
  • Lastly, it can uniquely identify each record in a table
16. What is the Foreign key?

Foreign key refers to a column that builds a relationship between two tables. The main purpose is to maintain data integrity and allow navigation between two different instances of an entity or a cross-reference between two tables.

17. Define a unique key.

A Unique key refers to a constraint that uniquely identified each record in the database. This is for providing uniqueness for the column or set of columns. Further, there can be multiple unique constraints defined per table, but only one Primary key constraint defined per table.

18. What do you understand by the term join?

A join act as an SQL operation performed for establishing a connection between two or more database tables depending on matching columns by building a relationship between the tables. However, the most complex queries in an SQL database management system have the involvement of join commands.

19. Explaining the different types of join.

1. Inner Join

Rows return when there is at least one match of rows between the tables.

2. Right Join

All records return from the right table and the matching records from the left table.

3. Left Join

Left Join returns all rows from the left table and the matching rows records from the right table.

4. Full join

This join returns all records when there is a match in either left or the right table.

20. What type of interactions are provisioned by the Database Management System (DBMS)?

The interactions include:

  • Firstly, Retrieval
  • Secondly, Administration
  • Thirdly, Data definition
  • Lastly, Update
dp-300 database exam
21. How many types of database languages are there?

There are four types of database languages:

  • Firstly, Data Definition Language (DDL)
  • Secondly, Data Manipulation Language (DML)
  • Thirdly, Data Control Language (DCL)
  • Lastly, Transaction Control Language (TCL)
22. What is Data Definition Language (DDL)?

Data Definition Language is for defining database structure or pattern. Moreover, you can also use DDL statements for creating the structure of the database and storing the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.

23. Can you define some of the DDL tasks?
  • Firstly, Create. This is for creating objects in the database.
  • Secondly, Alter. This is for altering the structure of the database.
  • Thirdly, Drop. Deleting objects from the database.
  • Then, Truncate. This is for removing all records from a table.
  • Rename. This is for renaming an object.
  • Lastly, Comment. This is for commenting on the data dictionary.
24. Define Data Control Language (DCL).

DCL is for retrieving the stored or saved data. The DCL execution is transactional and it also contains the rollback parameters. Some of the DCL tasks include:

  • Firstly, Grant. This is for providing user access privileges to a database.
  • Secondly, Revoke. This is for taking back permissions from the user.
25. What is TCL?

TCL stands for Transaction Control Language which is used for running the changes made by the DML statement. This further can be grouped into a logical transaction. Some of the TCL tasks include:

  • Firstly, Commit. This is for saving the transaction on the database.
  • Secondly, Rollback. This is for restoring the database to its original form since the last Commit.
26. What is a database model?

A database model refers to a type of data model which regulates the logical structure of a database. This basically determines in which manner data can be stored, organized, and manipulated. For example, the relational model, which uses a table-based format.

27. Can you name some of the top database models?

Some of the database models include:

  • Firstly, the Entity-relationship model
  • Secondly, Document model
  • Thirdly, Entity-attribute-value model
  • Fourthly, Star schema
  • Then, Hierarchical database model
  • After that, the Relational model
  • Network model
  • Lastly, Object-oriented database model
28. Define a checkpoint in DBMS.

The Checkpoint refers to a type of process where all the previous logs are removed from the system and permanently stored in the storage disk.

29. What is the role of a checkpoint in DBMS?

A checkpoint act as a snapshot of the DBMS state. DBMS uses checkpoints for reducing the amount of work to be done during a restart in the event of subsequent crashes. Furter, checkpoints help in the recovery of the database after the system crash. Using checkpoints there is no need for performing the transactions from the very starting when there is a system crash.

30. Explain the following:

1. Relation Schema

A Relation Schema can be considered as a set of attributes. It is also called table schema as it defines what the name of the table is. Moreover, sometimes it is also referred to as the blueprint used for explaining how the data is organized into tables. This blueprint will not have any data.

2. Relation

A relation is considered a set of tuples or set of related attributes with identifying key attributes

31.  What do you understand by a degree of Relation?

The degree of a relation refers to a number of attributes of its relation schema. This is also knowns as Cardinality as it can define the number of occurrences of one entity connected to the number of occurrences of other entities. There are three degrees of relation:

  • Firstly, one-to-one(1:1)
  • Secondly, one-to-many(1:M)
  • Lastly, many-to-one(M:M).
32. Give some of the limitations of file processing systems?
  • Firstly, they are inconsistent and are not fully secured.
  • Secondly, there is data redundancy, data isolation, and data integrity.
  • Thirdly, it is difficult to access data, and also concurrent access is not possible.
  • Lastly, it offers limited data sharing.
33. Explain data abstraction in DBMS.

Data abstraction is a process of hiding irrelevant details from users in DBMS. These database systems are made of complex data structures. So for making them accessible for the user interaction with the database abstraction is used.

34. What are the levels of data abstraction?
  • Firstly, Physical level. This is the lowest level of abstraction which explains the data storing process.
  • Secondly, the Logical level. This level is above the physical level. It explains what type of data is stored in the database and what the relationship among those data is.
  • Lastly, View level. This is the highest level of data abstraction which explains the only part of the entire database.
35. Define DML.

DML stands for Data Manipulation Language which is used for accessing and manipulating data in a database. It is capable of handling user requests. 

36. Explain the main tasks of DML.

Some of the main DML tasks include:

  • Firstly, Select. This is for retrieving data from a database.
  • Secondly, Insert. For inserting details into a table.
  • Thirdly, Update. This updates the existing data within a table.
  • Then, Delete. For deleting all records from a table.
  • After that, Merge. This is for performing UPSERT operation, i.e., insert or update operations.
  • Lastly, Lock Table. This is for controlling concurrency.
37. How many types of DML are there?

There are two types of DML:

  • Procedural DML or Low-level DML. This needs a user for specifying what data is required and how to get those data.
  • Non-Procedural DML or High-level DML. This needs a user for specifying what data are required without specifying how to get those data.
38. What is SQL?

SQL stands for Structured Query Language which is used for communicating with the Database. This acts as a standard language that defines and performs the tasks like retrieval, updating, insertion, and deletion of data from a database.

39. What is a View?

A view refers to a virtual table that has a subset of data contained in a table. They are not present virtually and takes less space. However, a view can have data of one or more tables joined.

40. Define an Index.

An index refers to a performance tuning method for allowing faster retrieval of records from the table. This can build an entry for each value for faster retrieving the data.

41. How many types of indexes are there?

There are three types:

1. Unique Index

This indexing does not provide access to the field for having duplicate values if the column is unique indexed. Further, a unique index can be applied automatically when the primary key is specified.

2. Clustered Index.

This type of index is used for reordering the physical order of the table and search depending on the key values. There can be only one clustered value in a table.

3. Non-Clustered Index.

These types of indexes do not alter the physical order of the table and maintain the logical order of data. There can be 999 non-clustered indexes in a table.

42. Define Cursor in a database.

A database Cursor refers to a control that can enable traversal across the rows or records in the table. This can be considered as a pointer to one row in a set of rows. However, it is useful for traversing, retrieval, addition, and removal of database records.

43. What do you understand by the term query?

A DB query refers to a code written in order for getting the information back from the database. The query can be created in such a way that it matched our expectations of the result set. 

44. Define subquery.

A subquery is a query inside another query. In which the outer query is known as the main query, and the inner query is known as the subquery. However, the SubQuery is always executed first. Then, the result of the subquery is passed on to the main query.

45. Explain the types of the subquery.

There are two types of a subquery:

  • Firstly, correlated subquery. This refers to the column in a table listed in the FROM the list of the main query. However, it is not an independent query.
  • Secondly, non-Correlated subquery. This is an independent query and the output of the subquery is swapped in the main query.
46. Define stored procedure in SQL.

Stored Procedure refers to a function that consists of many SQL statements for accessing the database system. Several SQL statements are combines into a stored procedure and execute whenever and wherever needed.

47. What is a database trigger?

A DB trigger refers to a code or program that can automatically perform with response to some event on a table or view in a database. This helps in keeping the integrity of the database.

48. Differentiate DELETE and TRUNCATE commands.

Delete command is used for removing rows from the table. You can execute the commit and rollback after the delete statement. Whereas, the truncate command is used for removing all the rows from the table. And, you can roll back the truncate operations.

49. Explain the local and global variables.
  • Local Variables can be declared inside a programming block or subroutines. They can only be used inside the subroutine or code block in which it is declared. Further, the local variable exists until the block of the function is under execution and after that, it will be destroyed automatically.
  • A Global Variable in the program is defined outside the subroutine or function. This has a global scope as it can hold its value throughout the lifetime of the program. Further, it can be accessed throughout the program by any function defined within the program.
50. Define constraint.

A constraint can be used for specifying the limit on the data type of table. Further, this can be defined while building or altering the table statement. Examples include, Not null, Check, Default, Unique, Primary key, Foreign key, etc.

51. What do you understand by Data Integrity?

Data Integrity basically explains the accuracy and consistency of data stored in a database. This can also define integrity constraints for enforcing business rules on the data when it is entered into the application or database.

52. What is Auto Increment?

Autoincrement keyword provides access to the user for creating a unique number to be generated when a new record is inserted into the table. Further, it is commonly used for generating primary keys.

53. Define Database partitioning.

This refers to the division of logical databases into independent complete units for improving their management, availability, and performance.

54. Why Database partitioning is important?

It is important to split one large table into smaller database entities. However, database partitioning helps in:

  • Firstly, improving query performance when most rows are heavily accessed are in one partition.
  • Secondly, accessing large parts of a single partition
  • Lastly, slower and less costly storage media can be used for data that is hardly used.
55. Define the following:

1. Atomicity

It’s an all or none concept which helps in enabling the user to be assured of handling the incomplete transactions. 

2. Aggregation

In this, the collected entities and their relationship are combined in this model. This is majorly used for expressing relationships within relationships.

56. Explain when the functional dependency is said to be fully functional dependence?

To be a fully functional dependency, the relation must meet the need of functional dependency. That is to say, a functional dependency X and Y are fully functional dependent when there is the removal of any attribute say Z from X means the dependency does not hold anymore.

ciw database exam
57. Define the E-R model.

E-R model stands for Entity-Relationship model which explains the theoretical view of the database. This basically displays the real-world entities and their association/relations in which entities represent the set of attributes in the database.

58. What is Fragmentation?

Fragmentation refers to a feature used for controlling the logical data units, known as fragments. These fragments are stored at various sites of a distributed database system.

59. Write a query for the table name Customers in which I want to select all columns from a table for rows where the Last_Name column has Smith for its value.

SELECT * FROM Customers WHERE Last_Name=’Smith’;

60. What is Create table in SQL?

The CREATE TABLE statement is used for creating a table in a database. While creating a table, you also specify the columns and their data types, as well as any constraints.

For example:

CREATE TABLE Students(

 StudentId INT NOT NULL AUTO_INCREMENT,

 StudentName VARCHAR(255) NOT NULL,

 PRIMARY KEY ( StudentId));

61. What is SQL ALTER TABLE Statement?

The ALTER TABLE statement is used for changing the definition of a table. For example:

ALTER TABLE Movies

ADD COLUMN YearReleased DATETIME;

62. Explain the SQL DROP TABLE Statement.

The DROP TABLE statement is used for dropping (removing) a table. In this, just add the name of the table and the whole table will be removed from the database.

For example:

DROP TABLE Students;

63. Define SQL SELECT Statement.

The SELECT statement allows you to retrieve data from the database. Here, you can choose one or more tables including which specific columns you want to select data from.

For example:

SELECT StudentName, StudentBio FROM School;

School here is the table name.

64. Explain the SQL INSERT Statement.

The INSERT statement allows you to insert new rows into a table.

For example:

INSERT INTO Student (StudentName, StudentId) VALUES 

(John’,  ‘1012’);

65. What is an SQL UPDATE Statement?

The UPDATE statement is for updating one or more records in the database. For example:

UPDATE Student

SET StudentName = Mark’ 

WHERE StudentName = ‘John’;

66. Explain the SQL DELETE Statement.

The DELETE statement is for deleting the specified rows from a table. For example, 

DELETE FROM Student

WHERE StudentId = ‘6’;

67. What is the process of creating an empty table from an existing table?

For this, use the following query:

Select * into artistcopy from artist where 1=2

Here, we are copying the artist’s table to another table with the same structure with no rows copied.

68. Write a query for getting the common records from two tables.

Select artistID from artist INTERSECT Select artisitID from Movie

Here, we are using the table name as an artist.

69. Write a query for selecting unique records from a table.

For selecting unique records use DISTINCT keyword.

Select DISTINCT ArtistID, ArtisitName from Artist.

70. What is query optimization?

The query optimization explains an efficient execution plan for assessing a query that has the least estimated cost. However, this is a feature of many relational database management systems and other databases like graph databases. The query optimizer attempts to find out the most efficient way for executing a given query by considering the possible query plans.

71. What are the benefits of query optimization?
  • Firstly, it helps in decreasing the time and space complexity.
  • Secondly, many queries can be performed using optimization that makes every query takes less time.
  • Lastly, it provides the user with faster results.
72. Explain the DBMS durability.

After DBMS informs the user that a transaction has completed successfully, it continues even if the system crashes before all its changes are reflected on the disk. This feature of DBMS is called durability. However, durability ensures that once the transaction is applied into the database, it will be stored in the non-volatile memory and after that system failure cannot affect that data anymore.

73. Define an entity.

The Entity refers to a set of attributes in a database. However, it can be a real-world object which physically exists in this world. Further, all the entities have their attribute that in the real world are specified as the characteristics of the object. For example, in the student database of a school, the school, class, and the class section can be considered entities. 

74. Explain an Entity type.

An entity type is considered as the collection of entities, having the same attributes. They typically correspond to one or several related tables in the database. In other words, a characteristic that defines or uniquely identifies the entity is known as the entity type. For example, an artist has artist_id, movie name, and genre type as its characteristics.

75. What do you understand by an Entity set?

The entity set defines the collection of all entities of a particular entity type in the database. An entity set is called the set of all the entities which share the same properties. For example, a set of students, a set of companies, etc.

76. Define Data Independence.

Data independence defines the application independence of the storage structure and access strategy of data.  However, this helps in modifying the schema definition at one level without altering the schema definition at the next higher level.

77. How many types of Data Independence are there?

There are two types:

1. Physical Data Independence

This refers to the data stored in the database which is bit-format. However, the changes and modifications in the physical level should not affect the logical level.

2. Logical Data Independence

This refers to the data about the database which specifies the structure. However, the changes or modifications in the logical level should affect the view level. For example, tables stored in the database. 

78. What is the ACID property?

ACID property refers to basic rules that have to be satisfied by every transaction for preserving integrity. There are properties and rules which include:

1. Atomicity

It’s an all or none concept which helps in enabling the user to be assured of handling the incomplete transactions. In this, every transaction is taken as one unit and either run to completion or is not executed at all.

2. Consistency

This property defines the uniformity of the data. However, it implies that the database remains consistent before and after the transaction.

3. Isolation

This property defines the number of the transaction executed concurrently without leading to the inconsistency of the database state.

4. Durability

This property makes sure after the transaction is committed, it will be stored in the non-volatile memory. And, then even system crash cannot affect it anymore.

79. Differentiate Having and a Where Clause.
  • Having clause is used only with the select statement and is used in a GROUP BY clause in a query. However, if there is no GROUP BY then, HAVING works like a WHERE clause.
  • Where clause is applied to each row. This is performed until they become a part of the GROUP BY function in a query. However, it is used with SELECT, UPDATE, DELETE, etc.
80. Explain Data Mining.

Data mining defines the procedure for collecting, analyzing, and summarizing the contents of a database. This is used for concluding the success of a business, marketing campaigns, and for forecasting future trends.

81. What is the use of Update_statistics Command?

This command is used for processing large data. However, when there is deletion, modification, or copying of large data into the table, there is a need for indexes to be updated. So, for this UPDATE_STATISTICS is used.

82. What is the way for storing the Boolean Values In SQL Lite?

In SQL Lite, Boolean values are stored as integers 0 and 1. Where 0 means false and 1 means true. However, there is no separate boolean storage class in SQL Lite.

83. Can you name some of the standard SQL lite Commands?

The standard SQL Lite commands that interact with relational databases same as SQL. Some of them are:

  • Firstly, the SELECT command
  • Secondly, CREATE command
  • Thirdly, the DELETE command
  • Then, the INSERT command
  • After that, the UPDATE command
  • Lastly, the DROP command
84. Define transparent DBMS?

The transparent DBMS hides the physical structure from the users. However, physical structure or physical storage structure here means the memory manager of the DBMS. And, it also explains the process of storing data on a disk.

85. What is Relational Algebra?

Relational Algebra refers to a Procedural Query Language containing a set of operations that take one or two relations as input and produce a new relationship. Moreover, it is the basic set of operations for the relational model. Further, the major point of relational algebra is that it is similar to algebra which operates on the number.

86. Name the fundamental operations of relational algebra.

Some of the operations include:

  • Select
  • Project
  • Cartesian product
  • Set difference
  • Union
  • Rename
87. Name the unary operations in Relational Algebra.

PROJECTION and SELECTION refer to the unary operations in relational algebra. However, Unary operations are those operations that use single operands. For example, in SELECTION relational operators used are – =,<=,>=, etc.

88. How do you define the functionality of the DML Compiler?

The DML Compiler is responsible for translate the DML statements in a query language that the query evaluation engine can understand. DML Compiler is important because the DML is the family of syntax element which performs same as the other programming language which needs compilation. So, it is essential for compiling the code in a language understandable by the query evaluation engine can understand and then work on those queries.

89. Define Relational Calculus.

Relational Calculus refers to a Non-procedural Query Language that uses mathematical predicate calculus rather than algebra. However, it is considered predicate calculus because it doesn’t operate on mathematics fundamentals like algebra, differential, integration, etc.

90. How many types of relational calculus are there?

There are two types of relational calculus:

1. Tuple relational calculus

In this, we work on filtering tuples depending on the given condition.

2. Domain relational calculus

In this, filtering is done depending on the domain of the attributes and not on the tuple values.

91. Define BCNF.

BCMF stands for Boyce-Codd Normal Form which refers to an advanced version of 3NF. So, we can consider it as 3.5NF. However, a table follows with BCNF if it satisfies the following conditions:

  • Firstly, if it is in 3NF.
  • Secondly, if every functional dependency X → Y, X is the super key of the table.
  • Lastly, the table should be in 3NF, and for every FD, LHS is super key.
92. What is a shared lock?

A shared lock is necessary for reading a data item. However, in this, many transactions may hold a lock on the same data item. And, when more than one transaction gets access for reading the data items then that is known as the shared lock.

93. Define Exclusive lock.

When any transaction is about to execute the write operation, then the lock on the data item is an exclusive lock. This is because, if we give access to more than one transaction then that will lead to irregularity in the database.

94. Differentiate  BETWEEN and IN condition operators.

The BETWEEN operator is for displaying rows depending on a range of values. The values could be numbers, text, or dates. However, this operator provides us the count of all the values occurring between a particular range.

The IN condition operator is used for checking the values contained in a specific set of values. This is mostly used when we have more than one value to choose from.

95. Define the following in SQL:
  1. NULL value
  2. Zero
  3. Blank space.
  • A NULL value is not similar to zero or a blank space. This refers to a value that does not exist in the database.
  • Secondly, Zero refers to a number.
  • Lastly, the blank space refers to a character.
96. What are SQL functions?

SQL Functions are the measured values that cannot build permanent environment changes to the SQL server. It is:

  • Firstly, for performing calculations on data
  • Secondly, for modifying individual data items
  • Thirdly, for manipulating the output
  • Then, for formating dates and numbers
  • Lastly, for converting data types
97. Explain the case manipulation functions.

Case manipulation functions are used for converting the data from the state in which it is already stored in the table to upper, lower, or mixed case. This can work for every part of the SQL statement. For example, when searching for data for which you don’t have any idea whether it is lower case or upper case.

98. Name the various case manipulation functions in SQL?
  • Firstly, LOWER. This is for transforming the character into Lowercase.
  • Secondly, UPPER. This is for transforming the character into uppercase.
  • Lastly, INITCAP. This is for transforming the character values to uppercase for the initials of each word.
99. Can you provide a list of character-manipulation functions in SQL?
  • Firstly, CONCAT. This is for joining two or more values together.
  • Secondly, SUBSTR. This is for extracting the string of a specific length.
  • Thirdly, LENGTH. This returns the length of the string in numerical value.
  • Fourthly, INSTR. This is for finding the exact numeric position of a specified character.
  • Then, LPAD. This is for padding of the left-side character value for the right-justified value.
  • RPAD. This is for padding of right-side character value for left-justified value.
  • After that, TRIM. This is for removing all the defined characters from the beginning, end, or both beginning and end.
  • Lastly, REPLACE. This is for replacing a specific sequence of characters with other sequences of characters.
100. What are IFNULL() and ISNULL() functions?
  • The IFNULL() function returns a defined value if the expression is NULL. However, if the expression is NOT NULL then, this function returns the expression. This works only in MySQL 4.0.

Syntax

IFNULL(expression, alt_value)

  • The ISNULL() function returns a defined value if the expression is NULL. However, if the expression is NOT NULL then, this function returns the expression. This works in SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

Syntax

ISNULL(expression, value)

Final Words

We know that Databases refer to a collection of organized information for easily accessing, managing, and updating data. This as a result makes database systems an important area for organizations and businesses. Moreover, it helps business makes stronger by storing all the essential information related to the sales process, marketing, and more. So, there will never be a shortage of jobs in this sector. But, for earning a good position, it is important that you should concentrate on enhancing your knowledge by taking help from the above questions. Start preparing and become a database professional.

aws database specialty exam

Menu