DBMS Oral Questions:

1. What is MySQL?

Why MySQL is used? MySQL database server is reliable, fast and very easy to use. This software
can be downloaded as freeware and can be downloaded from the internet.

2.What is the default port for MySQL Server?

The default port for MySQL server is 3306.

3. What are the advantages of MySQL when compared with Oracle?

MySQL is open source software which is available at any time and has no cost involved.
MySQL is portable GUI with command prompt. Administration is supported using MySQL
Query Browser

4. Difference between CHAR and VARCHAR?

Following are the differences between CHAR and VARCHAR:
CHAR and VARCHAR types differ in storage and retrieval
CHAR column length is fixed to the length that is declared while creating table. The length value
ranges from 1 and 255
When CHAR values are stored then they are right padded using spaces to specific length.
Trailing spaces are removed when CHAR values are retrieved.

5. What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a
table. Primary Key is also a candidate key. By common convention, candidate key can be
designated as primary and which can be used for any foreign key references.

6What is a foreign key?

A foreign key is one table which can be related to the primary key of another table. Relationship
needs to be created between two tables by referencing foreign key with the primary key of another
table.

7. What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Views are not
virtually present, and it takes less space to store. View can have data of one or more tables
combined, and it is depending on the relationship.

8. What is an Index?
An index is performance tuning method of allowing faster retrieval of records from the table. An
index creates an entry for each value and it will be faster to retrieve data.

9. What are all the different types of indexes?

There are three types of indexes -.
Unique Index.
This indexing does not allow the field to have duplicate values if the column is unique indexed.
Unique index can be applied automatically when primary key is defined.
Clustered Index.

This type of index reorders the physical order of the table and search based on the key values. Each
table can have only one clustered index.
NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of
data. Each table can have 999 nonclustered indexes.

10. What is a Cursor?

A database Cursor is a control which enables traversal over the rows or records in the table. This
can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing
such as retrieval, addition and removal of database records.

11. What happens when the column is set to AUTO INCREMENT and if you reach maximum
value in the table? It stops incrementing. Any further inserts are going to produce an error, since the
key has been used already

12. How can you see all indexes defined for a table? Indexes are defined for the table by:
SHOW INDEX FROM tablename

13. What do you mean by % and _ in the LIKE statement? % corresponds to 0 or more
characters, _ is exactly one character in the LIKE statement

14. What is the difference between NOW() and CURRENT_DATE()?

NOW() command is used to show current year, month, date with hours, minutes and seconds while
CURRENT_DATE() shows the current year with month and date only.

15. What is a JOIN?

It is a Keyword, used to query data from multiple tables based on relationship between the fields of
the tables. Keys plays major role in JOINs.

16.Differebt types of joins?

17.What is subquery?

A subquery is a query within another query. The outer query is called as main query, and inner
query is called subquery. SubQuery is always executed first, and the result of subquery is passed on
to the main query.

18. What is a trigger in MySQL?

A trigger is a set of codes that executes in response to some events.

19. How many Triggers are possible in MySQL?

There are six Triggers allowed to use in MySQL database. 1. Before Insert 2. After Insert 3. Before
Update 4. After Update 5. Before Delete 6. After Delete

20. What is PL/SQL?

PL/SQL (Procedural Language/SQL) is basically a procedural extension of Oracle – SQL. PL/SQL
helps the user to develop complex database applications using control structures, procedures, function,
modules, etc.

21. Explain the purpose of %TYPE and %ROWTYPE data types with the example?

Answer: PL/SQL uses %TYPE declaration attribute for anchoring. This attribute provides the datatype
of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same
datatype as a table column.
For example, the variable m_empno has the same data type and size as the column empno in table emp.
m_empno emp.empno%TYPE;
%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in
a table. The row is defined as a record and its fields have the same names and data types as the columns
in the table or view.
For example: dept_rec dept%ROWTYPE;
This declares a record that can store an entire row for DEPT table.

22. What do you understand by PL/SQL cursors?

Answer: PL/SQL requires a special capability to retrieve and process more than one row and that
resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory
containing SQL statements and information for processing the statements.
PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are
selected and then each row is individually processed inside a PL/SQL program.
Question #7) Explain cursor types?
Answer: There are two types of cursors. They are explained as follows
1) Explicit Cursors: For queries that return more than one row, an explicit cursor is declared and
named by a programmer. In order to use explicit cursor in PL/SQL, 4 steps are followed
Declare the cursor
Syntax: CURSOR <cursor_name> is
SELECT statement;
Where <cursor_name> is the name assigned to the cursor and SELECT statement is the query that
returns rows to the cursor active set.
Open the cursor
Syntax: OPEN <cursor_nam>;
Where, <cursor_name> is the name of the previously defined cursor.
Fetch rows from the cursor
Syntax: FETCH <cursor_name> INTO <record_list>;
Where <cursor_name> refers to the name of the previously defined cursor from which rows are being
fetched.
<record_list> represents the list of variables that will receive the data being fetched.
Closing the cursor
Syntax: CLOSE <cursor_name>;
Where <cursor_name> is the name of the cursor being closed.
2) Implicit cursors: When any SQL statement is executed, PL/SQL automatically creates a cursor
without defining such cursors are known as implicit cursors.
For following statements, PL/SQL employs implicit cursors
INSERT
UPDATE
DELETE
SELECT ( queries that return exactly one row)

23.Explain the difference in execution of triggers and stored procedures?

Answer: A stored procedure is executed explicitly by issuing procedure call statement from another
block via a procedure call with arguments.
The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements
happens.

24. Compare NoSQL & RDBMS

25. What is NoSQL?

NoSQL encompasses a wide variety of different database technologies that were developed in
response to a rise in the volume of data stored about users, objects and products. The frequency in
which this data is accessed, and performance and processing needs. Relational databases, on the
other hand, were not designed to cope with the scale and agility challenges that face modern
applications, nor were they built to take advantage of the cheap storage and processing power
available today

26.What are the features of NoSQL?

When compared to relational databases, NoSQL databases are more scalable and provide superior
performance, and their data model addresses several issues that the relational model is not designed
to address: Large volumes of structured, semi-structured, and unstructured data Agile sprints,
quick iteration, and frequent code pushes Object-oriented programming that is easy to use and
flexible Efficient, scale-out architecture instead of expensive, monolithic architecture

27. Explain what is MongoDB?

Mongo-DB is a document database which provides high performance, high availability and easy
scalability.
28) What is “Namespace” in MongoDB?
MongoDB stores BSON (Binary Interchange and Structure Object Notation) objects in the
collection. The concatenation of the collection name and database name is called a namespace.
29 What is sharding in MongoDB?
The procedure of storing data records across multiple machines is referred as Sharding. It is a
MongoDB approach to meet the demands of data growth. It is the horizontal partition of data in a
database or search engine. Each partition is referred as shard or database shard.

30. What is the syntax to create a collection and to drop a collection in MongoDB?

Syntax to create collection in MongoDB is db.createCollection(name,options)

Syntax to drop collection in MongoDB is db.collection.drop()
31. Mention what is the command syntax for inserting a document?

For inserting a document command syntax is database.collection.insert (document).

32.Explain what are indexes in MongoDB?

Indexes are special structures in MongoDB, which stores a small portion of the data set in an easy
to traverse form. Ordered by the value of the field specified in the index, the index stores the value
of a specific field or set of fields.

33. Mention what is the basic syntax to use index in MongoDB?

The basic syntax to use in MongoDB is >db.COLLECTION_NAME.ensureIndex ( {KEY:1} ). In

here the key is the the name of the COLUMN (or KEY:VALUE pair) which is present in the
documents.

34.Define cursor.
A database object which helps in manipulating data row by row representing a result set is called
cursor.
35. Enlist the types of cursor.
They types of cursor are: Implicit cursor: Declared automatically as soon as the execution of SQL
takes place without the awareness of the user. Explicit cursor: Defined by PL/ SQL which
handles query in more than one row.
36. Define sub-query.
A query contained by a query is called Sub-query.
37. Why is group-clause used?
Group-clause uses aggregate values to be derived by collecting similar data.
38.Define Aggregate functions.
Functions which operate against a collection of values and returning single value is called aggregate
functions.
39. What is JSON? Explain?

Ans: JSON is the abbreviation of JavaScript Object Notation. It is one of the simplest data interchange
format. It is also independent of programming language and platform. Its lightweight text-based
structure makes it easily readable by a human. It is derived from JavaScript for presenting simple data in
the form of key-value pairs.
It is often used for serialization and transmission of data between the network connections. It is mostly
used for data transmission between a web application and the server thereby making it a popular
alternative to the XML format.

40.What is meant by JSON objects?

Ans: An object is defined as a set of key-value pair. A JSON starts with a left brace “{“ and ends with
another right brace “}”. Every key is followed by a colon “:” and the key-value pairs are separated from
each other by using a comma “,”. So, basically, JSON object is a collection of keys along with their
values arranged in a pre-specified JSON format.

41. Explain JSON syntax rules?

Ans: There are several rules that describe the structure of the JSON. They are:
Data inside a JSON is arranged in Key value pair. The left side represents the key and the data at the
right side represents value. Both key and value are separated by a colon “:”.
Each set of key-value pair is separated from the other pair by using a comma “,”.
Curly braces define the JSON objects. Left curly brace “{“ represents the start of the object and right
curly brace “}” represents the end of an object.
Arrays are defined inside a JSON object by using square brackets “[ ]”.
42. What are the advantages of JSON over XML?
Ans: JSON has emerged as one of the most popular data interchange methods. It has several advantages
over the XML that has to help it to replace XML as a most popular data transfer format.
JSON is lighter and faster than the XML.
JSON has object types but XML doesn’t define objects as types. JSON have different object type for a
different set of data such as string, integer, Boolean, array etc. All XML objects are categorized as just
one data type, i.e. string.
JSON data can be easily accessed as a JSON object using JavaScript. On the other hand, the XML data
need to be parsed and allocated to the variables using APIs. Getting a value out of a JSON is as easy as
reading an object from your JavaScript programming.