DBA Interview Questions and Answers 2019

Q7. When would you use ‘char’ versus ‘varchar’?

Ans: This is a bit of a difficult question, mostly because it depends so much on what your application is. For example, if you have a form field that can be nearly any length and changes every single time, then varchar is a much more practical choice, since it gives you much more flexibility. If however you have a field where every value is going to be exactly the same length, then you can get more efficient performance out of a char. Again, it depends on exactly what your application is, and how you plan to cook it — seasoning as you see fit.

Q8. What are the roles of DBA?

Ans: DBA has the authority to create new users, remove existing users or modify any of the environment variables or privileges assigned to other users.

•  Manage database storage
• Administer users and security
•  Manage schema objects
•  Monitor and manage database performance
•  Perform backup and recovery
• Schedule and automate jobs

Q9. Why should I go to all the trouble of creating a database when I have a perfectly good Excel Spreadsheet?

Ans: Scale. If you were to take a (singular) spreadsheet and a (singular) table and place them side by side, there would be effectively no difference in the data you are seeing or what you could do with it. As you go bigger and bigger with more and more tables and spreadsheets, if you have a black belt in spreadsheet-fu you can accomplish many of the same tasks that a database could do as well. The problem is, as you go larger and larger and larger, that it becomes much more difficult to be human-friendly and still be efficient when it comes to processing data. So should you replace every single spreadsheet with a database? Not necessarily, but if the data on that spreadsheet needs to be accessed quickly by multiple users simultaneously and is growing rapidly, it may be time to consider going to the dark side (they have cookies).

Q10. Why do most database types not talk to each other?

Ans: In a word: money. In three words: a lotttta money. Different database vendors spend a huge amount of research time trying to find ways to give them a leg up on the competition; whether that may be by performance, storage capacity, longevity, reliability, scalability, the list goes on and on. As a result, trying to be compatible and backwards engineer every single feature of a particular database type is difficult in the extreme before you even get to the patent violations. Most databases can be simplified down to filetypes like .csv files, which can be used to transport basic data from vendor to vendor. That being said however, there would be a lot lost in translation without help from higher up.

Q11. What is a ‘join’?

Ans: Well when two tables love each other very much…not that much happens actually. However when you need to search across multiple tables simultaneously, a join can help make that happen. For example, if you were searching for information on a particular product and one table has the description while the other has pricing information, you can use a join to search across both tables simultaneously using a single query.

Q12. What is a foreign key?

Ans: When using a join or other type of query that goes across multiple tables, it can sometimes be difficult to make sure they are talking on the same page. A primary key can help with this, but sometimes this is impractical, and thus you need a secondary value that is consistent across multiple tables. For example, say that in a series of tables for product listings you have your primary key assigned to an auto-increment ID based on when the product was entered (a typical setup), and then none of these rows are able to line up with their counterparts in other tables. So if you have one table for product listings, another for price information, another for reviews, etc. — this could be a fairly major problem. However, if you know for a certainty that your part numbers for these products are going to be unique values, you can use that as a foreign key and suddenly everything lines up all nice and neat. This is possible since it exists in more than one table, and since is being referenced from outside its own table; it is designated ‘foreign’. This does not mean it still could not be the primary key for that particular table as well, it just means it has a reference that can be looked to from another point of view.

Q13. What is SQL Injection?

Ans: Also known as asking a question and getting the answer you want, rather than the answer they want to give you (anybody that has tried to navigate certain nameless support phones knows that this isn’t necessarily a bad thing); however in the context of a database application, this can be “a very bad thing”™. For instance, say that you are on an online banking website. You’re at the login screen, and it is waiting for you to enter your login and password so it can display your particular financial information. But what if you want to see the listing of everybody else that banks at this particular location? Depending on how the bank’s site is hardened against such an attack, you could get their personal information, current balances, PIN numbers, or even worse, enter your own data directly into the database — able to create new accounts, set up transaction history, active balances, the list goes on and on.

Q14. What is the difference between a Navigational database and a Relational database?

Ans: The best way to describe a Navigational DBMS is through that of a tree. Each value was associated with another through the use of a parent, most of the time with no other direct way to access the data. Relational Databases on the other hand use values common to multiple tables to establish a unique key — making sure that they are talking on the same page so that there are many, many ways to get to the same place. To put it another way, if you were trying to get from point A to point B, a navigational database would have one specific path to get there — via a freeway. A relational database on the other hand would have options for taking the freeway, a back road, a boat, a plane, a bus and sometimes a rocket — provided that each of those methods were set up correctly to talk to each other. Most modern databases use the relational database model.

Q15. What is a Database Schema?

Ans: If you’ve ever seen one of those Visio diagrams with 40 different tables with lines connecting particular columns on one with those on another, that’s a database schema. Essentially a two-dimensional representation of how each table talks to other ones, it is the way to view the design of a database as a single entity and not as a jumble of different tables.

Q16. What are Nested Queries?

Ans: A query within a query, this particular method can be tremendously difficult to troubleshoot and even harder to manage without a lot of overhead. In most cases, a nested query can be replaced with a JOIN, allowing for much more efficient use of resources.

Q17. What does ‘INSERT’ do?

Ans: INSERT submits data into a database as a new row, usually through the use of a form. While forms can take many…forms…, the most common uses are through either a dedicated application or through the use of an HTML form. Clicking on the ‘submit’ button will trigger the built in form reaction to scan the form for particular fields, making sure the required ones are entered correctly, make sure the user isn’t being naughty in what they are trying to enter, then submit the data to the database.

Q18. What is input sterilization?

Ans: One of the main answers to SQL Injection, input sterilization allows the database to selectively ignore data coming in from an input field and strip out non-required data. For example, if a field is expecting only a numeric value, there is no need for letters or symbols to be present in the user input. Therefore, these values can be safely ignored but still keep the functionality of the form intact. While not an end-all beat-all, it goes a long way to helping mitigate attacks on this vector.

Q19. What does ‘UPDATE’ do?

Ans: UPDATE allows values to be modified where they meet specific criteria. For example, say that you were on Amazon and were about to move. As a result, you would want to adjust your mailing address so that you actually got your stuff. You would therefore go into your settings and it would show you your current address. Modifying this address and then submitting the form would update your address based on your particular user profile. If it updated anybody else’s address to match that would be a serious problem — at least for the person doing the paying.

Q20. What does ‘DROP’ do?

Ans: DROP removes a table from a database or a database from a server. A very dangerous command indeed, it is only to be used in situations that absolutely require it, as unless you have a backup of it handy, there is no coming back from this.

Q21. What is the difference between T-SQL and PL/SQL?

Ans: T-SQL or Transact-SQL is Microsoft’s version of SQL. The main additions Microsoft made to the main branch of SQL involve the addition of procedures or routines — scripts essentially — that can be run under certain criteria. PL/SQL, on the other hand, is Oracle’s version of SQL, and conceptually the two are very similar. However, because of the nature in how they were developed, trying to move data from one to the other involves quite a bit of work. The main differences deal with how they multi-task and how they lock elements when they are in use.

Q22. What is a database instance? Explain.

Ans: A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.
The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

Q23. What is Parallel Server?

Ans: Multiple instances accessing the same database (only in multi-CPU environments)

Q24. Which operation is faster COMMIT or ROLLBACK? Why?

Ans: It’s obviously COMMIT is faster than ROLLBACK. Let me explain with an example: Let’s say we opened a transaction and updated 8000 records:

Commit: It’s completed quickly as the operation is already completed and it just marks those dirty pages as committed and when checkpoint happens all those dirty pages will be written to disk.

Rollback: The operation is already updated 8000 records if we need to rollback then again all these updates has to be rolled back which means there are another 8000 log records will be written to LDF which will take time when compared to commit.

Q25. SQL Vs NoSQL.

Ans: NoSQL (Also called Not Only SQL), is a different form of database than the standard relational type. While it can use a lot of the same kinds of query language, it doesn’t necessarily use the same type of table structure that standard relational databases use, and thus in some cases can be more efficient. That efficiency depends greatly on its application however, and many times you will see NoSQL used in Big Data crunching and analysis applications that require real-time feedback.

Q26. What is the difference between ‘=’ and ‘LIKE’?

Ans: When crafting a query, or using programming to display data in certain ways depending on the values being returned, you may want to think that these can be used interchangeably. There is one big difference, however: equal means equal. The value being returned must match the value it is being compared to 100%. LIKE, however, can be used with a number of different wildcard mechanics, allowing you to be a bit more flexible in your rules.

Q27. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Ans: A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Q28. What is difference between UNIQUE constraint and PRIMARY KEY constraint?

Ans: A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? - A Cluster with an index on the Cluster Key 48.When does a Transaction end? - When it is committed or Rollbacked.

Q29. What is ODBC?

Ans: Open Database Connectivity is a way to make different kinds of frontends talk to different data sources (DSNs) such as Databases. The specifics available depend on the type of application being used, the driver being used and the backend to which it is being applied.

Q30. What are different Oracle database objects?

Ans: 

• TABLES
• VIEWS
• INDEXES
• SYNONYMS
• SEQUENCES
• TABLESPACES

Q31. What are the benefits of ORDBMS?

Ans: The objects as such can be stored in the database. The language of the DBMS can be integrated with an object- oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.

Q32. What is database link?

Ans: A database link is a named object that describes a "path" from one database to another.

Q33. What are the types of database links?

Ans: Private database link, public database link & network database link.

Q34. Can you give some examples for One to One, One to Many and Many to Many relationships?

Ans:

One to One: Citizen – UID
A citizen can have only one UID – A UID can represent only one citizen

One to Many: Customer – Products
A customer can sale number of products – A product can be brought by only one customer

Many to Many: Book – Author

A book can be written by more than one author – An author can write more than one book

Q35. What is Distributed database?

Ans: A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.

Q36. What is private database link?

Ans: Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

Q37. Differentiate between a cluster and a grid?

Ans: Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.
At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered – as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don’t know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘The Grid’. This view of utility computing is, of course, a “client side” view.
From the “server side”, or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.

Q38. What is public database link?

Ans: Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

Q39. What is network database link?

Ans: Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.