Are you preparing for Database job interview and wondering what questions you might be asked? In this blog we have hand-picked the questions you are likely to be asked in Database job interview and provided to-the-point answers to each one of them so as to help you prepare better for Database job interviews.
Always keep in the mind that, only academic knowledge is not enough to crack an interview. Employers expects from the candidate to have practical knowledge and hands-on experience as well. This Database Interview Questions designed by industry expert will help you to gain practical knowledge of Database.
Database Interview Questions and Answers
- Explain what is SQL LITE?
Answer: SQL LITE is a mostly ACID compliant relational database management system contained in a relatively small C programming library.
- Comment on Transactions.
- Using transactions we can group all SQL commands into a single unit.
- The transaction begins with some task and finishes only when all tasks within it are over.
- The transaction gets over successfully only when all commands in it are successfully over. Even if one command fails, the whole transaction fails.
- The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions.
- A group of tasks starts with the begin statement.
- In case of any problem, the rollback command is executed to abort the transaction.
- If all the tasks run successfully, all commands are executed through commit statement.
- List out the standard SQL Lite commands?
Answer: The standard SQL Lite commands interact with relational databases are similar to SQL. They are
Based on their operational nature these commands can be classified.
- Define the "integrity rules"?
Answer: There are two Integrity rules.
- Entity Integrity: States that "Primary key cannot have NULL value"
- Referential Integrity: States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.
- What is database?
Answer: A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.
- Describe the three levels of data abstraction?
Answer: There are three levels of abstraction:
- Physical level: The lowest level of abstraction describes how data are stored.
- Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
- View level: The highest level of abstraction describes only part of entire database.
- What is DBMS?
Answer: It is a collection of programs that enables user to create and maintain a database. In other words it is general-purpose software that provides the users with the processes of defining, constructing and manipulating the database for various applications.
- Explain what is the use of SQLITE group by clause?
Answer: The SQLITE group by clause is used in collaboration with the SELECT statement to arrange identical data into groups.
- When is the UPDATE_STATISTICS command used?
- When the processing of large data is done, this command is used.
- Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes.
UPDATE_STATISTICS performs this job.
- Explain how Boolean values in SQL Lite are stored?
Answer: Boolean values in SQL lite are stored as integers 0 (false) and 1 (true). SQL Lite does not have a separate Boolean storage class.
- Differentiate between a HAVING CLAUSE and a WHERE CLAUSE.
- HAVING CLAUSE is used only with the SELECT statement.
- It is generally used in a GROUP BY clause in a query.
- If GROUP BY is not used, HAVING works like a WHERE clause.
It is applied to each row before they become a part of the GROUP BY function in a query.
- Mention what are the SQL lite storage classes?
Answer: SQL lite storage classes include
- Null: The value is a NULL value
- Integer: The value is a signed integer (1,2,3, etc.)
- Real: The value is a floating point value, stored as an 8 byte IEEE floating point number
- Text: The value is a text string, stored using the database encoding ( UTF-8, UTF-16BE)
- BLOB (Binary Large Object): The value is a blob of data, exactly stored as it was input
- What is database normalization and why is it done?
Answer: This is the process of organizing data in a database efficiently, and it is done to make sure that connections and dependencies between data make sense, and to get rid of redundant data.
- What do constraints do? What are the types of constraints?
Answer: Constraints are used to prevent the database from losing internal and external integrity. The types are: check, not null, unique, primary key, and foreign key.
- What are fact tables?
Answer: Tables which track the progress of a certain process or activity, the primary tables of databases.
- What does RDMBS stand for, and what does it do?
Answer: Relational database management systems are the basis of SQL, and they are used to manage the database and intelligently store data in related rows and columns with identifiers and indexes.
- Explain what is SQLite transactions?
Answer: The transaction is referred as a unit of work that is performed against a database. It is the propagation of one or more changes to the database. Properties of transactions are determined by ACID.
- Atomicity: It ensures that all work unit are successfully completed
- Consistency: It ensures that the database changes states upon a successfully committed transaction
- Isolation: It enables transactions to operate independently of and transparent to each other
- Durability: It ensures that the result or effect of a committed transaction persists in case of a system failure
- What does SQL stand for, and what is it used for?
Answer: SQL stands for structured query language, used with relational databases. It is used to query, update, and retrieve the contents of databases.
- List out the areas where SQL Lite works well?
Answer: SQL lite works well with
- Embedded devices and the internet of things
- Application file format
- Data Analysis
- Cache for enterprise data
- Server side database
- File archives
- Internal or temporary databases
- Replacement for ad hoc disk files
- Experimental SQL language extensions
- Stand-in for an enterprise database during demos or testing
- What are attributes?
Answer: An attribute is a column in a table.
Use Full Link for Training and Certification