Created by:-Lalit Ingale

MySQL
1)What is Databases?
➔A databases is collection of interrelated data that is stored and accessed
electronically .It helps stored information in structured way,making it easy
to search,update and manage.

2) Difference between varchar and varchar2 data types?

➔Varchar can store upto 2000 bytes and varchar2 can store upto 4000
bytes. Varchar will occupy space for NULL values and Varchar2 will not
occupy any space. Both are differed with respect to space.

3) In which language Oracle has been developed?

➔Oracle has been developed using C Language.

4) What is RAW datatype?

➔RAW datatype is used to store values in binary data format. The
maximum size for a raw in a table in 32767 bytes.

5) What is the use of NVL function?

➔The NVL function is used to replace NULL values with another or given
value. Example is – NVL(Value, replace value)

6) Whether any commands are used for Months calculation? If so, What
are they?
Created by:-Lalit Ingale

➔ In Oracle, months_between function is used to find number of months

between the given dates. Example is –
Months_between(Date 1, Date 2)

7) What are nested tables?

➔ A nested table means storing a table inside another table—like having
a list of items inside one row of another table.

8) What is COALESCE function?

➔ COALESCE function is used to return the value which is set to be not
null in the list. If all values in the list are null, then the coalesce function
will return NULL.
Coalesce(value1, value2,value3,…)

9) What is BLOB datatype?

➔The BLOB datatype in MySQL is used to store large binary data.
like Images ,Audio files ,Videos ,PDFs ,or any kind of files.

10) How do we represent comments in Oracle?

➔-Single Line Comments
Use two hyphens (--) before the comment.
-Multi Line Comments
Use /* to start the comment and */ to end it.

11) What is DML?

Created by:-Lalit Ingale

➔ DML stands for Data Manipulation Language. It's a part of SQL that is
used to work with the data inside a database like insert data ,update data
,delete data ,and select data.

12) What is the difference between TRANSLATE and REPLACE?

➔ Translate is used for character by character substitution and Replace is
used substitute a single character with a word.

13) How do we display rows from the table without duplicates?

➔ Duplicate rows can be removed by using the keyword DISTINCT in the
select statement.

14) What is the usage of Merge Statement?

➔ The MERGE statement in SQL is used to combine insert and update
operations in one statement . It's often called an "upsert" (update +
insert).

15) What is NULL value in oracle?

➔ In Oracle, a NULL value means "no value" or "unknown". It doesn't
mean zero or an empty string — it means nothing is there.

16) What is USING Clause and give example?

➔- The USING clause is used to specify with the column to test for equality
when two tables are joined.
-Select * from employee join salary using employee ID
-Employee tables join with the Salary tables with the Employee ID.
Created by:-Lalit Ingale

17) What is key preserved table?

➔ A table is set to be key preserved table if every key of the table can also
be the key of the result of the join. It guarantees to return only one copy
of each row from the base table.

18) What is WITH CHECK OPTION?

➔The WITH CHECK OPTION clause indicates that any updated or inserted
row to the view must be checked against the view definition, and rejected
if it does not conform.

19) What is the use of Aggregate functions in Oracle?

➔ Aggregate function is a function where values of multiple rows or
records are joined together to get a single value output. Common
aggregate functions are –Average ,Count ,Sum.

20) What do you mean by GROUP BY Clause?

➔ A GROUP BY clause can be used in select statement where it will collect
data across multiple records and group the results by one or more
columns.

21) What is a sub query and what are the different types of subqueries?
➔ A subquery is a query within a query. It’s like asking a question inside
another question. A subquery is used to retrieve data that will be used by
the main query to help it make decisions.
- Correlated Subquery
Created by:-Lalit Ingale

A correlated subquery is a type of subquery that depends on the outer

query for its values. Unlike a regular subquery, which is independent and
can run on its own.
- Non-Correlated subquery
A non-correlated subquery is a type of subquery that does not depend on
the outer query for its values. It can run independently of the outer query,
meaning it can be executed on its own and still return a result. The
subquery runs once, and its result is then used by the outer query.

22) What is cross join?

➔ Cross join is defined as the Cartesian product of records from the tables
present in the join. Cross join will produce result which combines each
row from the first table with the each row from the second table.

23) What are temporal data types in Oracle?

➔ Date Data Type – Different formats of Dates
TimeStamp Data Type – Different formats of Time Stamp
Interval Data Type – Interval between dates and time

24) How do we create privileges in Oracle?

➔ A privilege is nothing but right to execute an SQL query or to access
another user object. Privilege can be given as system privilege or user
privilege.
GRANT user1 TO user2 WITH MANAGER OPTION;

25) What is VArray?

Created by:-Lalit Ingale

➔ A VARRAY is a type of collection in Oracle that allows you to store

multiple values of the same type (such as numbers, strings, etc.) in a single
variable.

26) How do we get field details of a table?

➔ Describe <Table_Name> is used to get the field details of a specified
table.

27) What is the difference between rename and alias?

➔ Rename is a permanent name given to a table or a column whereas
Alias is a temporary name given to a table or column. Rename is nothing
but replacement of name and Alias is an alternate name of the table or
column.

28) What is a View?

➔ A View in SQL is like a virtual table. It doesn’t store data by itself, but it
shows data from one or more real tables. You can use it just like a regular
table in your queries.

29) What is a cursor variable?

➔ A cursor variable in Oracle is a pointer that refers to a query result set.
Instead of running a query and immediately processing the results, a
cursor variable stores the query result and allows you to fetch the data
row by row, often inside a PL/SQL block.

30) What are cursor attributes?

Created by:-Lalit Ingale

➔ Each cursor in Oracle has set of attributes which enables an application

program to test the state of the cursor. The attributes can be used to check
whether cursor is opened or closed, found or not found and also find row
count.

31) What are SET operators?

➔ SET operators are used with two or more queries and those operators
are Union, Union All, Intersect and Minus.

32) How can we delete duplicate rows in a table?

➔ Duplicate rows in the table can be deleted by using ROWID.

33) What are the attributes of Cursor?

➔ -%FOUND
Returns NULL if cursor is open and fetch has not been executed
Returns TRUE if the fetch of cursor is executed successfully.
Returns False if no rows are returned.
-%NOT FOUND
Returns NULL if cursor is open and fetch has not been executed
Returns False if fetch has been executed
Returns True if no row was returned
-%ISOPEN
Returns true if the cursor is open
Returns false if the cursor is closed
-%ROWCOUNT
Created by:-Lalit Ingale

Returns the number of rows fetched. It has to be iterated through entire

cursor to give exact real count.

34) Can we store pictures in the database and if so, how it can be done?
➔ Yes, we can store pictures in the database by Long Raw Data type. This
datatype is used to store binary data for 2 gigabytes of length. But the
table can have only on Long Raw data type.

35) What is an integrity constraint?

➔ An integrity constraint is a declaration defined a business rule for a
table column. Integrity constraints are used to ensure accuracy and
consistency of data in a database. There are types – Domain Integrity,
Referential Integrity and Domain Integrity.

36) What is an ALERT?

➔ In Oracle Forms, an ALERT is a popup message box used to display
important information or ask the user to make a decision (like Yes/No or
OK/Cancel).

37) What is hash cluster?

➔ A hash cluster in Oracle is a way of storing related rows together in the
same place on disk using a hashing function. It helps speed up data
retrieval, especially when you search for rows using an exact match (like
searching by ID).

38) What are the various constraints used in Oracle?

➔ - NULL – It is to indicate that particular column can contain NULL values
Created by:-Lalit Ingale

- NOT NULL – It is to indicate that particular column cannot contain NULL

values
- CHECK – Validate that values in the given column to meet the specific
criteria
- DEFAULT – It is to indicate the value is assigned to default value

39) What is difference between SUBSTR and INSTR?

➔ SUBSTR returns specific portion of a string and INSTR provides
character position in which a pattern is found in a string.SUBSTR returns
string whereas INSTR returns numeric.

40) What is the parameter mode that can be passed to a procedure?

➔ IN, OUT and INOUT are the modes of parameters that can be passed to
a procedure.

41) What are the different Oracle Database objects?

➔ Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespaces – Logical storage unit in Oracle

42) What are the differences between LOV and List Item?
Created by:-Lalit Ingale

➔ LOV is property whereas list items are considered as single item. List of
items is set to be a collection of list of items. A list item can have only one
column, LOV can have one or more columns.

43) What are privileges and Grants?

➔ Privileges are the rights to execute SQL statements – means Right to
connect and connect. Grants are given to the object so that objects can
be accessed accordingly. Grants can be provided by the owner or creator
of an object.

44) What is the difference between $ORACLE_BASE and

$ORACLE_HOME?
➔ Oracle base is the main or root directory of an oracle whereas
ORACLE_HOME is located beneath base folder in which all oracle products
reside.

45) What is the fastest query method to fetch data from the table?
➔ Row can be fetched from table by using ROWID. Using ROW ID is the
fastest query method to fetch data from the table.

46) What is the maximum number of triggers that can be applied to a

single table?
➔ 12 is the maximum number of triggers that can be applied to a single
table.
Created by:-Lalit Ingale

THANK
YOU