MySQL Technical Interview Questions – part 3


What is a Database system?

The database and DBMS software together is called as Database system.

 
 

Advantages of DBMS?

  • Redundancy is controlled.
  • Unauthorised access is restricted.
  • Providing multiple user interfaces.
  • Enforcing integrity constraints.
  • Providing backup and recovery.
 
 

Disadvantage in File Processing System?

  • Data redundancy & inconsistency.
  • Difficult in accessing data.
  • Data isolation.
  • Data integrity.
  • Concurrent access is not possible.
  • Security Problems.
 
 

Describe the three levels of data abstraction?

The 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.
 
 

Define the “integrity rules”

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 Data Independence?

    Data independence means that “the application is independent of the storage structure and access strategy of data”.

    In other words, The ability to modify the schema definition in one level should not affect the schema

    definition in the next higher level.
    Two types of Data Independence:

    • Physical Data Independence: Modification in physical level should not affect the logical level.
    • Logical Data Independence: Modification in logical level should affect the view level.

     

     
     

    What is a view? How it is related to data independence?

    A view may be thought of as a virtual table, that is, a table that does not really exist in its own right

    but is instead derived from one or more underlying base table.

    In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
    Growth and restructuring of base tables is not reflected in views.

    Thus the view can insulate users from the effects of restructuring and growth in the database.

    Hence accounts for logical data independence.

     
     

    What is Data Model?

    A collection of conceptual tools for describing data, data relationships data semantics and constraints.

     
     

    What is E-R model?

    This data model is based on real world that consists of basic objects called entities and of relationship among these objects.

    Entities are described in a database by a set of attributes.

     
     

    What is Object Oriented model?

    This model is based on collection of objects. An object contains values stored in instance variables with in the object.

    An object also contains bodies of code that operate on the object. These bodies of code are called methods.

    Objects that contain same types of values and the same methods are grouped together into classes.

     
     

     
     

    What is an Entity?

    It is a ‘thing’ in the real world with an independent existence.

     
     
     
     
    What is an Entity type?

    It is a collection (set) of entities that have same attributes.

     
    What is an Entity set?

    It is a collection of all entities of particular entity type in the database.

     
    What is Weak Entity set?

    An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key

    and primary key of its parent entity, then it is said to be Weak Entity set.

     
    What is an attribute?

    It is a particular property, which describes the entity.

     
    What is a Relation Schema and a Relation?

    A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of attributes Ai that it contains.

    A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, …, tn).

    Each tuple is an ordered list of n-values t=(v1,v2, …, vn).

     
    What is degree of a Relation?

    It is the number of attribute of its relation schema.

     
    What is Relationship?

    It is an association among two or more entities.

     
    What is Relationship set?

    The collection (or set) of similar relationships.

     
    What is Relationship type?

    Relationship type defines a set of associations or a relationship set among a given set of entity types.

     
    What is DDL (Data Definition Language)?

    A data base schema is specifies by a set of definitions expressed by a special language called DDL.

     
    What is VDL (View Definition Language)?

    It specifies user views and their mappings to the conceptual schema.

     
    What is SDL (Storage Definition Language)?

    This language is to specify the internal schema. This language may specify the mapping between two schemas.

     
    What is Data Storage – Definition Language?

    The storage structures and access methods used by database system are specified by a set of definition

    in a special type of DDL called data storage-definition language.

     
    What is DML (Data Manipulation Language)?

    This language that enable user to access or manipulate data as organised by appropriate data model.

    • Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.
    • Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying 

                     how to get those data.

       
      What is DDL Interpreter?

      It interprets DDL statements and record them in tables containing metadata.

       
      What is normalization?

      It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs)

      and primary key to achieve the properties

      • Minimizing redundancy
      • Minimizing insertion, deletion and update anomalies.
       
      What is Functional Dependency?

      A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint

      on the possible tuple that can form a relation state r of R.

      The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y].

      This means the value of X component of a tuple uniquely determines the value of component Y.

       
      What is Multivalued dependency?

      Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R,

      specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X]

      then t3 and t4 should also exist in r with the following properties

      • t3[x] = t4[X] = t1[X] = t2[X]
      • t3[Y] = t1[Y] and t4[Y] = t2[Y]
      • t3[Z] = t2[Z] and t4[Z] = t1[Z]

      where [Z = (R-(X U Y)) ]

       
      What is Lossless join property?

      It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

       
      What is 1 NF (Normal Form)?

      The domain of attribute must include only atomic (simple, indivisible) values.

       
      What is Fully Functional dependency?

      It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency

      if removal of any attribute A from X means that the dependency does not hold any more.

       
      What is 2NF?

      A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent

      on primary key.

       
      What is 3NF?

      A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

      • X is a Super-key of R.
      • A is a prime attribute of R.

      In other words, if every non prime attribute is non-transitively dependent on primary key.

       
      What is BCNF (Boyce-Codd Normal Form)?

      A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X

      must be a candidate key.

       
      What is 4NF?

      A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true

        • X is subset or equal to (or) XY = R.
        • X is a super key.
       
      What is 5NF?

      A Relation schema R is said to be 5NF if for every join dependency {R1, R2, …, Rn} that holds R, one the following is true

        • Ri = R for some i.
        • The join dependency is implied by the set of FD, over R in which the left side is key of R.
       
      What are partial, alternate,, artificial, compound and natural key?

      Partial Key:
      It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity.

      It is sometime called as Discriminator.
      Alternate Key:
      All Candidate Keys excluding the Primary Key are known as Alternate Keys.
      Artificial Key:
      If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key,

      by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.

      Compound Key:
      If no single data element uniquely identifies occurrences within a construct, then combining

      multiple elements to create a unique identifier for the construct is known as creating a compound key.
      Natural Key:
      When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.

       
      What is indexing and what are the different kinds of indexing?

      Indexing is a technique for determining how quickly specific data can be found.
      Types:

      • Binary search style indexing
      • B-Tree indexing
      • Inverted list indexing
      • Memory resident table
      • Table indexing
       
      What is meant by query optimization?

      The phase that identifies an efficient execution plan for evaluating a query that has the least

      estimated cost is referred to as query optimization.

       
      What is a Phantom Deadlock?

      In distributed deadlock detection, the delay in propagating local information might cause the deadlock

      detection algorithms to identify deadlocks that do not really exist. Such situations are called phantom deadlocks

      and they lead to unnecessary aborts.

       
      What is “transparent DBMS”?

      It is one, which keeps its Physical Structure hidden from user.

       
      What is database Trigger?

      A database trigger is a PL/SQL block that can defined to automatically execute for insert, update,

      and delete statements against a table. The trigger can e defined to execute once for the entire statement

      or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for

      which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

       
      A B C is a set of attributes. The functional dependency is as follows

      AB -> B
      AC -> C
      C -> B
      a) is in 1NF
      b) is in 2NF
      c) is in 3NF
      d) is in BCNF

      (a) is in 1NF since (AC)+ = { A, B, C} hence AC is the primary key. Since C B is a FD given, where neither C is a Key

      nor B is a prime attribute, this it is not in 3NF. Further B is not functionally dependent on key AC thus it is not in 2NF.

      Thus the given FDs is in 1NF.

       
      What is Storage Manager?

      It is a program module that provides the interface between the low-level data stored in database,

      application programs and queries submitted to the system.

       
      What is Buffer Manager?

      It is a program module, which is responsible for fetching data from disk storage into main memory

      and deciding what data to be cache in memory.

       
      What is Transaction Manager?

      It is a program module, which ensures that database, remains in a consistent state despite system failures

      and concurrent transaction execution proceeds without conflicting.

       
      What is File Manager?

      It is a program module, which manages the allocation of space on disk storage and data structure used to

      represent information stored on a disk.

       
      What are cursors give different types of cursors.

      PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors

      • Implicit
      • Explicit
       
      how to display duplicate rows in a table? 


      select * from emp
      group by (empid)
      having count(empid)>1
       
      Difference between VARCHAR and VARCHAR2? 


      varchar means fixed length character data(size) ie., min size-1 and max-2000
      where as varchar2 means variable length character data ie., min-1 to max-4000
       
      diff b/w oracle and ms-access.
      One of the differences is:
      Oracle is multi-user where MS-Access is not.
       

      How to get the second Max sal from emp table in Oracle?

      Answer :
      select max(salary)from emp where salary<(select max(salary)from emp)

       
      how to display duplicate rows in a table?
      select * from emp
      group by (empid)
      having count(empid)>1
       
      diff b/w oracle and ms-access.
      One of the differences is:
      Oracle is multi-user where MS-Access is not.
       
      Q: How can you compare a part of the name rather than the entire name?
      A: SELECT * FROM people WHERE empname LIKE ‘%ab%’
      Would return a recordset with records consisting empname the sequence ‘ab‘ in empname .
       
      Q: How to get the results of a Query sorted in any order?
      A: You can sort the results and return the sorted results to your program by using ORDER BY keyword

      thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.

      SELECT empname, age, city FROM emptable ORDER BY empname

       
      Q: How can I find the total number of records in a table?
      A: You could use the COUNT keyword , example 

      SELECT COUNT(*) FROM emp WHERE age>40

       
      Q: What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
      A: Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes
      Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete
      Delete : (Data alone deleted), Doesn’t perform automatic commit
       
      Q: What are the Large object types suported by Oracle?
      A: Blob and Clob.
       
      Q: Difference between a “where” clause and a “having” clause.
      A: Having clause is used only with group functions whereas Where is not used with.
       
      Q: What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
      A: Cursors allow row-by-row prcessing of the resultsets. 

      Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

      Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
      Most of the times, set based operations can be used instead of cursors.

       
      Q: What are triggers? How to invoke a trigger on demand?
      A: Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. 

      Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
      Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

       
      Q: What is a join and explain different types of joins.
      A: Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
       
      Q: What is a self join?
      A: Self join is just like any other join, except that two instances of the same table will be joined in the query.
       

      Que : How many types of joins are exists ? explain with example?

      Answer:
      There are three basic types of joins, Cross, Inner & Outer join

      1. Cross Join – A cross join merges two tables on every record in a geometric fashion, every record of

      one table is combined with every record from the other table. Two tables of 10 records each in a cross join will

      create a table of 100 (10 X 10) records.

      2. Inner Join – An Inner join is used to match two tables based on values of a common field.

      It can be thought of as the Intersection of the two sets of data. Inner join is the records that appear in both tables.

      3. Outer Join – The Outer join is a type of join that returns all of the members of one set,

      and any matching members of the other set. Outer join can be Left Outer join or Right Outer join.

      For explaining this we will take two tables Students and Courses
      a. Left Outer join – This join will give a list of Students who are not enrolled in any course.
      b. Right Outer join – This join will give a list of courses which don’t have any Students enrolled.

       
      What is a transaction and what are ACID properties?
      A transaction is a logical unit of work in which, all the steps must be performed or none.
      ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.
      For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
       
      What’s the maximum size of a row?
      8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’.
      Check out SQL Server books online for the page titled: “Maximum Capacity Specifications”.
       
      What are constraints? Explain different types of constraints.
      Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers,
      rule or defaults.

       

      Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

      For an explanation of these constraints see books online for the pages titled: “Constraints” and “CREATE TABLE”, “ALTER TABLE”

       
      What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
      Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece
       
      Write down the general syntax for a SELECT statements covering all the options.
      Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax). 

      SELECT select_list
      [INTO new_table_]
      FROM table_source
      [WHERE search_condition]
      [GROUP BY group_by_expression]
      [HAVING search_condition]
      [ORDER BY order_expression [ASC | DESC] ]

      Advertisements

      One Response to “MySQL Technical Interview Questions – part 3”

      1. Tony Says:

        in the “mysql-technical-interview-questions-part-3/” page, text is truncated.

        From Sharag: HI tony, it been corrected. thanks for your update.


      Leave a Reply

      Fill in your details below or click an icon to log in:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s

      %d bloggers like this: