Database administration level 3 coc exam pdf

  • Hire Us for
  • SQL SERVER – Questions and Answers with Database Administrators
  • Top 50 Database (DBMS) Interview Questions & Answers (2021)
  • Database Administrator Interview Questions
  • Database Administration L-4
  • Hire Us for

    I also stay on top of new ways to use Hadoop by participating in technical forums and a Hadoop Slack channel. A: A database administrator needs a strong process for identifying and addressing issues. While automated tools help lighten their load, you get to see their overall thought process and troubleshooting strategy with this answer.

    I regularly check tickets to see if anyone is reporting performance issues or other concerns. When I identify something that requires troubleshooting, I begin by diagnosing the symptoms and probable causes. I will delegate the hands-on repair to a lower-level tech if needed, depending on my task list and the complexity of the repair. What was your process for handling this? A: Losing company data is typically an unfortunate, but common occurrence that can happen to database administrators.

    This answer should help interviewers understand how the candidate handles this situation and if they have a process in place to successfully recover this data. Database administrators are expected to act quickly and logically in this high-pressure situation to ensure significant pieces of data are recovered and secured.

    The candidate's answer should emphasize: Ability to think quickly in high-pressure situations A process for backing up and recovering data Critical thinking and problem solving skills An answer to this question could look like this: Example: "I was once working on a data migration project and was in charge of conducting a test migration.

    When I conducted the test, I realized that several pieces of our data were either lost or corrupted. I decided to troubleshoot the issue to find more specific reasons this problem was occurring. I realized that there were several outdated systems that were getting lost in the file updates.

    Luckily, I had downloaded a high-quality backup program to recover these lost files and took note of this issue to ensure the corruption and loss of these data files wouldn't take place again.

    SQL SERVER – Questions and Answers with Database Administrators

    Top 50 database administrator interview questions [updated in ] May 18, by Kurt Ellzey Share: There are very few things in the IT realm that cover as much territory as databases.

    Before the web, and more specifically before the advent of dynamic programming languages, these immense collections of data were the exclusive territory of the deep-down programmers — rulers of their vast domain of cells and tables.

    As time progressed, however, more and more methods of accessing data and then displaying it in a way that was not hardcoded became easier to understand and manipulate. Today, it is now possible for a user with no knowledge of database programming to create a database-driven website in under 3 minutes I timed it , and manipulate it easier than it is to get around in the newer versions of a word processor that shall remain nameless.

    Therefore, the role of the Database Administrator DBA is one that truly has a huge variety of skill sets in it, especially since so many organizations have their own particular slants on things. Becoming a master in one particular kind of database does not necessarily mean that you could jump into another immediately without having to learn some of its own quirks. That being said, we will be dividing up different questions you may face in a DBA Interview into 5 levels — least experienced to most experienced.

    Rather, this means that in order to reach level 5, you want to be comfortable with everything up to that point — not necessarily remember everything by rote, but at least be able to have a resource you can get the answers from quickly. Level 1 Databases at their very core are methods of storing information for later use in a safe and secure place, using a strange language that at first it seems that everybody but you can understand.

    As a result, at this stage you most likely have a particular project that you need to maintain or implement — one that requires vast amounts of information to be cared for and kept tidy. Bananas are optional, but tasty. What is a database?

    A Database Administrator needs to know what a database is before they can administer it, right? At its most basic, a database is a collection of tables, structured in such a way that it can be navigated like you would any sort of table. If you remember in math class, you may have had a number of tables that allowed you to quickly find a value if you multiplied an x and y value together — or in this case, what it would be if you were looking for a particular row and column value.

    Why should I go to all the trouble of creating a database when I have a perfectly good excel spreadsheet? 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.

    What is a query? A query in normal terms is a question, simple enough. While many times a query is an actual question asking for an answer, it can also be the statement to modify, insert, or remove data in the database as well.

    What is SQL? Structured Query Language is the basic way of asking a database server to talk to you. Whether that is in the context of asking it a question, giving it answers to questions it is asking you, or updating answers that have already been stored in the database. The art of asking the right question is critical to getting back the right data you need, which is incredibly valuable when dealing with databases, as it is very easy to receive far more data than you know what to do with, or nothing at all.

    It looks across the specified table s , finds the data you are looking for and then presents it to the user for consideration. Depending on the query, this can be an awful lot of data, so again, asking the right question is critical. What is a primary key? A primary key is usually used as the index for a particular table — a value that the table can depend upon to be a reliable unique value in every row.

    When trying to pull data for a particular row, the primary key will normally be used to pull that information, usually a numeric value. For example, if you are trying to pull up data on a specific person, and that database is using their unencrypted ssn as the primary key naughty , then that could be used in the query to identify that particular row since there could be other people present in the database with that specific name or other identifying characteristics.

    What is a database management system? A Database Management System, or DBMS, is essentially the application that handles the heavy lifting between you the user , and the raw data.

    The database itself is just that — the database; it cannot alter its own data any more than the average person can re-arrange their genetic code. The DBMS is what you are talking to when you are asking the questions. It is what looks at your question, thinks about it for a while, goes to the database, picks up the data, hands it back to you, and asks you to come again. What is the difference between a navigational database and a relational database?

    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. Why do most database types not talk to each other? 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. That being said however, there would be a lot lost in translation without help from higher up. What is a frontend? In essence a management program, a frontend allows admins to be able to view and modify high level database functions without the need to use the command line for every single thing.

    This can be extremely useful not only for efficiency, but also for safety, as it can prevent accidental data modification. Level 2 You now can navigate a database backend at will, and depending on the rest of your skill sets, can access things that may or may not have you locked up in a federal penitentiary. However, when someone comes asking you for a question, you are the person they rely on for answers fast and accurately.

    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.

    What is a foreign key? 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. 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 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. What is SQL injection? For instance, say that you are on an online banking website. But what if you want to see the listing of everybody else that banks at this particular location? What is input sterilization? 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. 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. Big Data is essentially looking at the forest for the forest instead of the trees.

    An individual person is a unique entity with a specific set of actions and reasons for why they do what they do. A good example of this was published in the New York Times : Walmart discovered that just prior to a major storm, there was a run on the usual items such as bottled water, batteries and flashlights — but also strawberry pop tarts.

    This pattern was consistent across the board, so they were able to bundle these items together in certain parts of the store and increase profits. Amazon Suggestions, Google Analytics and other entities that run off of Big Data are huge moneymakers for their respective entities for being able to consistently give relatively accurate recommendations to users based on their past interests or purchases.

    A flatfile is a catch-all term used for concepts like Comma Separated Values. While there are a lot of different ways to create such a file, they all share ideas that they can be created and manipulated easily and without necessarily requiring a standard database application. These can also be used to transfer data from system to system due to their lightweight status. In some cases, these have been replaced by XML files, however XML can when compared to certain kinds of flatfiles, be very large.

    How would I do this? The easy answer would be to contact Microsoft Tech Support and bring your checkbook. A more difficult answer would be to bring it down to a. This is not saying that this would work in all cases, but it is at least an option. 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. LIKE, however, can be used with a number of different wildcard mechanics, allowing you to be a bit more flexible in your rules.

    What is a Null Value? A Null Value is an absence of data. This one is a bit misleading sometimes, because depending on who you ask, it can be considered many possible things. If a column allows for null values, and no value is submitted, then it allows it to be Null. Being able to generate data and then know that it is safe for future use is vital, since you never know when some scrap of information might be useful in your next caper project.

    While forms can take many…forms…, the most common uses are through either a dedicated application or through the use of an HTML form. DROP removes a table from a database or a database from a server.

    Top 50 Database (DBMS) Interview Questions & Answers (2021)

    According to you what goes into making the best Database Administrator? The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

    I have all the primary data files, secondary data files as well as logs. Now, tell me can I still restore the database without having a full backup? You cannot restore the database without having a full database backup. However, if you have the copy of all the data files. As per your opinion what are the five top responsibilities of a DBA? I rate the following five tasks as the key responsibilities of a DBA.

    Securing database from physical and logical integrity damage. Restoring database from backup as a part of disaster management plan. Optimizing queries performance by appropriate indexing and optimizing joins, where conditions, select clause etc. Designing new schema, support legacy schema, and legacy database systems.

    Helping developers improve their SQL-related code writing skill. One of the developers in my company moved one of the columns from one table to some other table in the same database.

    How can I find the name of the new table where the column has been moved? This question can be answered by querying system views. This is a very small but a very handy script.

    Every time a user is dropped or modified, system admin has to undergo this inconvenient process. In SQL Server and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as pokecord command schema to which they are assigned.

    Because of the same reason in SQL Server and the later versions — when a user is dropped from database — there is no negative effect on the database itself.

    Database Administrator Interview Questions

    What is BI? I have heard this term before but I have no idea about it? BI stands for Business Intelligence. However, it has been in use for a long time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting accurate and timely key enterprise data to CXO, IT Managers, Business Consultants, and distributed teams of a company, to provide them with up-to-date information to drive intelligent decisions for business success, which ultimately leads to enhanced revenue, reduced risk, decreased cost, and better operational control for business agility and competitiveness.

    An effective BI empowers end users to use data to understand the cause that led to a particular business result, to decide on the course of action based on past data, and to accurately forecast future results.

    A data model determining fundamentally how data can be stored, manipulated and organised and the structure of the database logically is called database model.

    The various relationships of database are: One-to-one: Single table having drawn relationship with another table having similar kind of columns. One-to-many: Two tables having primary and foreign key relation. Many-to-many: Junction table having many tables related to many tables. Organized data void of inconsistent dependency and redundancy within a database is called normalization.

    Advantages of normalizing database are: No duplicate entries Boasts the query performances. Boosting up database performance, adding of redundant data which in turn helps rid of complex data is called denormalization. Manipulating data in a database such as inserting, updating, deleting is defined as Data Manipulation Language.

    It destroys an existing database, index, table or view. Its syntax is: DROP objecttype objectname. Full recordings of two tables is Union All operator. A distinct recording of two tables is Union. A database object which helps in manipulating data row by row representing a result set is called cursor. They are: Dynamic: it reflects changes while scrolling. Keyset: data modification without reflection of new data is seen. They types of cursor are: Implicit cursor: Declared automatically as soon as the execution of SQL takes place without the awareness of the user.

    A query contained by a query is called Sub-query. Group-clause uses aggregate values to be derived by collecting similar data. Functions which operate against a collection of values and returning single value is called aggregate functions 24 Define Scalar functions. Scalar function is depended on the argument given and returns sole value. Restrictions that are applied are: Only the current database can have views. You are not liable to change any computed value in any particular view.

    Database Administration L-4

    Full-text index definitions cannot be applied. Temporary views cannot be created. Temporary tables cannot contain views. In case of execution, the sub query is executed first and then the correlated query. Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing.

    Enterprise management is used for managing the information whose framework is known as Data Warehousing. Joins help in explaining the relation between different tables.

    Database administration level 3 coc exam pdf