Best oracle sql interview questions:Part 2

Best oracle sql interview questions:Part 2

1. Why we use %ROWTYPE and %TYPE in Oracle PLSQL?


Answer: %ROWTYPE and %TYPE are the attributes in PL/SQL which can inherit the datatypes of a table defined in a database. The purpose of using these


attributes is to provide data independence and integrity.


The PL/SQL code is automatically updated to reflect any changes made to the precision or data types in the database.


When declaring a variable that must have the same data type as a table column, use %TYPE.


On the other hand, %ROWTYPE will be used to specify a whole record row with a structure akin to a table.


2. What is the purpose of PL/SQL Stored Procedures & Functions, and what distinguishes them?


Answer:In response, a stored procedure is a collection of SQL statements designed to carry out a particular function.


These statements can be saved as a group in the


database with an assigned name and can be shared with different programs if permissions are there to access the same.


Again, functions are subprograms designed to carry out particular duties, but they are not the same as one another.


3. What do functions in SQL mean? Could you briefly explain the many kinds of SQL functions?


Answer:The answer is that one of SQL's most potent features is its functions. Although SQL functions can accept parameters, they always produce a value.


There are two distinct types of SQL functions:--


a) Single-Row functions: These functions operate on a single row to give one result per row.


Types of Single-Row functions:


Character


Number


Date


Conversion


General


b) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.


Types of Multiple-Row functions:


AVG


COUNT


MAX


MIN


SUM


STDDEV


VARIANCE


4. Describe different types of General Function used in SQL?


Answer: General functions are of following types:


NVL: Compares a value if it's null then coverting null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.


NVL2: Returned exp2 if exp1 is not null, and exp3 if exp1 is null. Any form of data can be used in the argument exp1. (exp1, exp2, exp3) in NVL2.


When two expressions are compared, NULLIF returns null if they are equal and the first expression if they are not. (exp1, exp2) NULLIF.


COALESCE: Provides the expression list's first non-null expression. COALESCE (expn, exp1, exp2,... The COALESCE function has an advantage over the NVL function in that it has numerous possible values.


Conditional Expressions: Allow IF-THEN-ELSE reasoning to be used in a SQL statement. CASE expression and DECODE function are two examples. (training in data science).


5. Find the error in the below code snippet if any?


Answer: SELECT student_id, student_name, student_number, and student_birthdate FROM students;


In this case, the column birthday is being utilized as an alias for the reserved keyword "date." In Oracle SQL, this behavior is prohibited. Quotation marks can be used to set a reserved keyword as an alias.


FROM students, SELECT student_id (s_id), student_name (name), birthdate (date), and student_number (s_no);


6. What is the difference between a Primary Key & a Unique Key?


Answer: Primary key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.


Given below are a few differences:


Unique keys can have more than one in the table, but the primary key can only have one.


While the unique key can have one null value per column, the primary key cannot hold any null values at all.


A unique key is a non-clustered index, whereas the primary key is a clustered index.


note: Why are integrity restrictions necessary for databases?


To preserve the integrity of the database and stop incorrect data from being entered into the tables, integrity constraints are necessary to enforce business rules. The constraints listed below can be used to keep the relationships between the tables intact.


7. What are the set operators UNION, UNION ALL, MINUS & INTERSECT meant to do?


Answer: Set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are the same in the source tables.


With the exception of duplicate entries, the UNION operator returns every row from both tables.


The duplicate rows are returned along with every record from both tables via UNION ALL.


Rows from the first table that are absent from the second table are returned by MINUS.


Only the shared rows from the two tables are returned by INTERSECT.


8. What do you understand by a database object? Can you list a few of them?


Answer: An object used to store the data or references of the data in a database is known as a Database object.


The database consists of various types of DB objects such as tables, views, indexes, constraints, stored procedures, triggers, etc.


9. What do you understand by database schema and what does it hold?


Answer: Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema.


The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions, etc


10. What is a database trigger? How to create it?


Answer:One type of stored PL/SQL block is a database trigger. If certain requirements are met, the Oracle database will carry it out automatically.


There is a connection between the stored PL/SQL block and a table, schema, or database.


The generate TRIGGER clause can be used to generate a trigger. Using the ENABLE and DISABLE clauses of the ALTER TABLE or ALTER TRIGGER statement, we can decide whether to enable it or not. This is a list of commonly asked interview questions for Oracle.