DBMS professional knowledge for IBPS IT Officer Exam

DBMS is one of the major topic for the IBPS IT officer exam.From this Topic you will get around 15-20 questions in the exam.So It is very important to study this topic carefully so that one can score well in the upcoming exam.
Syallabus for the DBMS:

  • RDBMS
  • Normalization
  • Overview on SQL Queries
  • E-R Diagrams
  • Transaction Management

So we will just Go through every topic one by one so that we can cover every part of DBMS starting with Overview on SQL Queries.On every topic we will try to provide 20-30 points so that it cover each and every topic.

Overview on SQL Queries

  • DML – Data Manipulation Language:
    CommandDescription
    SELECTRetrieves certain records from one or more tables
    INSERTCreates a record
    UPDATEModifies records
    DELETEDeletes records
  • DCL – Data Control Language:
    CommandDescription
    GRANTGives a privilege to user
    REVOKETakes back privileges granted from user
  • DDL – Data Definition Language:
    CommandDescription
    CREATECreates a new table, a view of a table, or other object in database
    ALTERModifies an existing database object, such as a table.
    DROPDeletes an entire table, a view of a table or other object in the database.
  • Dr. Edgar F. “Ted” Codd of IBM is known as the father of relational databases. He described a relational model for databases.
  • SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.
  • Primary and Unique Key difference:
    >>Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
  • Difference between GROUP BY and HAVING Clause
    >>Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
  • difference between a local and a global variable:
    >>A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
    A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears.
  • Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
  • Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.
  • SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later.
  • SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA).
  • Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server.
  • Difference between a Local and a Global temporary table:
    >>A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
    >>A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears.
  • STUFF function is used to overwrite existing characters.

DBMS professional knowledge for IBPS IT Officer Exam.