SQL basic interview question

Question #1) What is SQL?

Structured Query Language is a database tool which is used to create and access database to support software application.

Question #2) What are tables in SQL?

The table is a collection of record and its information at a single view.

Question #3) What are different types of statements supported by SQL?

There are 3 types of SQL statements

1) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.

Some of the DDL Commands are listed below

CREATE: It is used for creating the table.

1CREATE TABLE&amp;amp;amp;nbsp;table_name

2column_name1 data_type(size),

3column_name2 data_type(size),

4column_name3 data_type(size),

ALTER: The ALTER table is used for modifying the existing table object in the database.

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name

DROP COLUMN column_name

2) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.

The Select statement is used as partial DML statement that is used to select all or relevant records in the table.

3) DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.

Question #4) How do we use DISTINCT statement? What is its use?

DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.

Syntax: SELECT DISTINCT column_name(s)
FROM table_name;

Question #5) What are different Clauses used in SQL?

WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition

Syntax: SELECT column_name(s)
FROM table_name
WHERE condition;

GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.

Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name;

HAVING clause: This clause is used in association with GROUP BY clause. It is applied to the each group of result or the entire result as single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause

Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name
HAVING condition;

ORDER BY clause: This clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.

Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC;

USING clause: USING clause comes in use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.

Syntax: SELECT column_name(s)
FROM table_name
JOIN table_name
USING (column_name);

Question #6) Why do we use SQL constraints? Which constraints we can use while creating database in SQL?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.

There are 5 major constraints are used in SQL, such as

NOT NULL: That indicates that the column must have some value and cannot be left null
UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.
FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
CHECK: It is used to ensure whether the value in columns fulfills the specified condition

Question #7) What are different JOINS used in SQL?

There are 4 major types of joins made to use while working on multiple tables in SQL databases

INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one column matched

Syntax: SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON column_name1=column_name2;

Example

In this example, we have a table Employee with the following data

The second Table is joining

Enter the following SQL statement

1SELECT Employee.Emp_id, Joining.Joining_Date

2  FROM Employee

3  INNER JOIN Joining

4  ON Employee.Emp_id = Joining.Emp_id

5  ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see

Employee and orders tables where there is a matching customer_id value in both the Employee and orders tables

LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from a LEFT table and its matched
rows from a RIGHT table.

Syntax: SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON column_name1=column_name2;

Example

In this example, we have a table Employee with the following data:

Second Table is joining

Enter the following SQL statement

1SELECT Employee.Emp_id, Joining.Joining_Date

2FROM Employee

3LEFT OUTER JOIN Joining

4ON Employee.Emp_id = Joining.Emp_id

5ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see:

RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from a LEFT table.

Syntax: SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON column_name1=column_name2;

Example

In this example, we have a table Employee with the following data

The second Table is joining

Enter the following SQL statement

1SELECT Employee.Emp_id, Joining.Joining_Date

2FROM Employee

3RIGHT OUTER JOIN Joining

4ON Employee.Emp_id = Joining.Emp_id

5ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see

FULL JOIN (FULL OUTER JOIN): This joins returns all when there is a match either in the RIGHT table or in the LEFT table.

Syntax: SELECT column_name(s)
FROM table_name1
FULL OUTER JOIN table_name2
ON column_name1=column_name2;

Example

In this example, we have a table Employee with the following data:

Second Table is joining

Enter the following SQL statement:

1SELECT Employee.Emp_id, Joining.Joining_Date

2FROM Employee

3FULL OUTER JOIN Joining

4ON Employee.Emp_id = Joining.Emp_id

5ORDER BY Employee.Emp_id;

There will be 8 records selected. These are the results that you should see

Question #8) What are transaction and its controls?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

COMMIT: It is used to save all changes made through the transaction
ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before
SET TRANSACTION: Set the name of transaction
SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

Question #9) What are properties of the transaction?

Properties of transaction are known as ACID properties, such as

Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
Consistency: Ensures that all changes made through successful transaction are reflected properly on database
Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure

Question #10) How many Aggregate Functions are available there in SQL?

SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.

There are 7 aggregate functions we use in SQL

AVG(): Returns the average value from specified columns
COUNT(): Returns number of table rows
MAX(): Returns largest value among the records
MIN(): Returns smallest value among the records
SUM(): Returns the sum of specified column values
FIRST(): Returns the first value
LAST(): Returns Last value

Question #11) What are Scalar Functions in SQL?

Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows

UCASE(): Converts the specified field in upper case
LCASE(): Converts the specified field in lower case
MID(): Extracts and returns character from text field
FORMAT(): Specifies the display format
LEN(): Specifies the length of text field
ROUND(): Rounds up the decimal field value to a number

Question #12) What are triggers?

Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as Insert, Update or Delete. You can invoke triggers explicitly on the table in the database.

Action and Event are two main components of SQL triggers when certain actions are performed the event occurs in response to that action.

Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}
ON table_name [FOR [EACH] {ROW|STATEMENT}]
EXECUTE PROCEDURE functionname {arguments}

Question #13) What is View in SQL?

A View can be defined as a virtual table that contains rows and columns with fields from one or more table.

Syntax: CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Question #14) How we can update the view?

SQL CREATE and REPLACE can be used for updating the view.

Following query syntax is to be executed to update the created view

Syntax: CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Question #15) Explain the working of SQL Privileges?

SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.

GRANT Command: This command is used provide database access to user apart from an administrator.

Syntax: GRANT privilege_name
ON object_name
TO {user_name|PUBLIC|role_name}
[WITH GRANT OPTION];

In above syntax WITH GRANT OPTIONS indicates that the user can grant the access to another user too.

REVOKE Command: This command is used provide database deny or remove access to database objects.

Syntax: REVOKE privilege_name
ON object_name
FROM {user_name|PUBLIC|role_name};

Question #16) How many types of Privileges are available in SQL?

There are two types of privileges used in SQL, such as

System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.
Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

Question #17) What is SQL Injection?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

For Example: SELECT column_name(s) FROM table_name WHERE condition;

Question #18) What is SQL Sandbox in SQL Server?

SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as

Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory and cannot create files.
External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.
Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

Question #19) What is the difference between SQL and PL/SQL?

SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

Question #20) What is the difference between SQL and MySQL?

SQL is a structured query language that is used for manipulating and accessing the relational database, on the other hand, MySQL itself is a relational database that uses SQL as the standard database language.

Question #21) What is the use of NVL function?

NVL function is used to convert the null value to its actual value.

Question #22) What is the Cartesian product of table?

The output of Cross Join is called as a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 Rows.

Question #23) What do you mean by Subquery?

Query within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query.

Question #24) How many row comparison operators are used while working with a subquery?

There are 3-row comparison operators which are used in subqueries such as IN, ANY and ALL.

Question #25) What is the difference between clustered and non-clustered indexes?

One table can have only one clustered index but multiple nonclustered indexes.
Clustered indexes can be read rapidly rather than non-clustered indexes.
Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row

Question #26) What is the difference between DELETE and TRUNCATE?

The basic difference in both is DELETE is DML command and TRUNCATE is DDL
DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table
We can use DELETE with WHERE clause but cannot use TRUNCATE with it
For example:
```
TRUNCATE TABLE customers;
```
This example would truncate the table called customers and remove all records from that table.

It would be equivalent to the following DELETE statement in Oracle:
```
DELETE FROM customers;
```
Both of these statements would result in all data from the customers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you can't roll back the TRUNCATE TABLE statement.

Question #27) What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.

Question #28) How to write a query to show the details of a student from Students table whose
name ends with K?

SELECT * FROM Student WHERE Student_Name like ‘%K’;

Here ‘like’ operator is used for pattern matching.

Question #29) What is the difference between Nested Subquery and Correlated Subquery?

Subquery within another subquery is called as Nested Subquery. If the output of a subquery is depending on column values of the parent query table then the query is called Correlated Subquery.

SELECT adminid(SELEC Firstname+’ ‘+Lastname FROM Employee WHERE
empid=emp. adminid)AS EmpAdminId FROM Employee

This query gets details of an employee from Employee table.

Question #30) What is Normalization? How many Normalization forms are there?

Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.

There are 5 forms of Normalization

First Normal Form (1NF): It removes all duplicate columns from the table. Creates table for related data and identifies unique column values
First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines relationship between tables using primary key
Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key
Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

Question #31) What is Relationship? How many types of Relationship are there?

The relationship can be defined as the connection between more than one tables in the database.

There are 4 types of relationships

One to One Relationship
Many to One Relationship
Many to Many Relationship
One to Many Relationship

Question #32) What do you mean by Stored Procedures? How do we use it?

A stored procedure is a collection of SQL statements which can be used as a function to access the database. We can create these stored procedures previously before using it and can execute these them wherever we require and also apply some conditional logic to it. Stored procedures are also used to reduce network traffic and improve the performance.

Syntax: CREATE Procedure Procedure_Name
(
//Parameters
)
AS
BEGIN
SQL statements in stored procedures to update/retrieve records
END

Question #33) State some properties of Relational databases?

In relational databases, each column should have a unique name
Sequence of rows and columns in relational databases are insignificant
All values are atomic and each row is unique

Question #34) What are Nested Triggers?

Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE statement. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.

Question #35) What is Cursor?

A cursor is a database object which is used to manipulate data in a row-to-row manner.

Cursor follows steps as given below

Declare Cursor
Open Cursor
Retrieve row from the Cursor
Process the row
Close Cursor
Deallocate Cursor

Question #36) What is Collation?

Collation is set of rules that check how the data is sorted by comparing it. Such as Character data is stored using correct character sequence along with case sensitivity, type, and accent.

Question #37) What do we need to check in Database Testing?

Generally, in Database Testing following thing is need to be tested

Database Connectivity
Constraint Check
Required Application Field and its size
Data Retrieval and Processing With DML operations
Stored Procedures
Functional flow

Question #38) What is Database White Box Testing?

Database White Box Testing involves

Database Consistency and ACID properties
Database triggers and logical views
Decision Coverage, Condition Coverage, and Statement Coverage
Database Tables, Data Model, and Database Schema
Referential integrity rules

Question #39) What is Database Black Box Testing?

Database Black Box Testing involves

Data Mapping
Data stored and retrieved
Use of Black Box techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)

Question #40) What are Indexes in SQL?

The index can be defined as the way to retrieve the data more quickly. We can define indexes using CREATE statements.

Syntax: CREATE INDEX index_name
ON table_name (column_name)

Further, we can also create Unique Index using following syntax;

Syntax: CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Q#41. What does SQL stand for?

Ans. SQL stands for Structured Query Language.

Q#42. How to select all records from the table?
Ans. To select all the records from the table we need to use the following syntax:

Select * from table_name;

Q#44. What is the syntax to add a record to a table?

Ans. To add a record in a table INSERT syntax is used.

Ex: INSERT into table_name VALUES (value1, value2..);

Q#45. How do you add a column to a table?
Ans. To add another column in the table following command has been used.

ALTER TABLE table_name ADD (column_name);

Q#46. Define SQL Delete statement.
Ans. Delete is used to delete a row or rows from a table based on the specified condition.
The basic syntax is as follows:

DELETE FROM table_name

WHERE <Condition>

Q#47. Define COMMIT?
Ans. COMMIT saves all changes made by DML statements.

Q#48. What is a primary key?
Ans. A Primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

Q#49. What are foreign keys?
Ans. When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.

Q#50. What is CHECK Constraint?
Ans. A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Q#51. Is it possible for a table to have more than one foreign key?
Ans. Yes, a table can have many foreign keys and only one primary key.

Q#52. What are the possible values for the BOOLEAN data field?
Ans. For a BOOLEAN data field, two values are possible: -1(true) and 0(false).

Q#54. What is identity in SQL?
Ans. An identity column in the SQL automatically generates numeric values. We can define a start and increment value of identity column.

Q#56. What is Trigger?
Ans. Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

Q#57. How to select random rows from a table?
Ans. Using SAMPLE clause we can select random rows.

Example:
SELECT * FROM table_name SAMPLE(10);

Q#58. Which TCP/IP port does SQL Server run?
Ans. By default SQL Server runs on port 1433.

Q#59. Write a SQL SELECT query that only returns each name only once from a table?
Ans. To get the each name only once, we need to use the DISTINCT keyword.

SELECT DISTINCT name FROM table_name;

Q#60. Explain DML and DDL?
Ans. DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements.

DDL stands for Data Definition Language. CREATE , ALTER, DROP, RENAME are DDL statements.

Q#61. Can we rename a column in the output of SQL query?
Ans. Yes using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

Q#62. Give the order of SQL SELECT?
Ans. Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.

Q#63. Suppose a Student table has two columns, Name and Marks. How to get name and marks of top three students.
Ans. SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)

Q#64. What is SQL comments?
Ans. SQL comments can be put by two consecutive hyphens (–).

Q#67. What do you mean by ROWID?
Ans. It’s an 18 character long pseudo column attached with each row of a table.

Q#68. Define UNION, MINUS, UNION ALL, INTERSECT ?
Ans. MINUS – returns all distinct rows selected by the first query but not by the second.

UNION – returns all distinct rows selected by either query

UNION ALL – returns all rows selected by either query, including all duplicates.

INTERSECT – returns all distinct rows selected by both queries.

Q#69. What is a transaction?
Ans. A transaction is a sequence of code that runs against a database. It takes the database from one consistent state to another.

Q#70. What is the difference between UNIQUE and PRIMARY KEY constraints?
Ans. A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.

The primary key cannot contain Null values whereas Unique key can contain Null values.

Q#71. What is a composite primary key?
Ans. Primary key created on more than one column is called composite primary key.

Q#74. What do you mean by query optimization?
Ans. Query optimization is a process in which database system compares different query strategies and select the query with the least cost.

Q#75. What is Collation?
Ans. Set of rules that define how data is stored, how case sensitivity and Kana character can be treated etc.

Q#76. What is Referential Integrity?
Ans. Set of rules that restrict the values of one or more columns of the tables based on the values of the primary key or unique key of the referenced table.

Q#77. What is Case Function?
Ans. Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of multiple possible result expressions.

Q#78. Define a temp table?
Ans. A temp table is a temporary storage structure to store the data temporarily.

Q#79. How can we avoid duplicating records in a query?
Ans. By using DISTINCT keyword duplicating records in a query can be avoided.

Q#80. Explain the difference between Rename and Alias?
Ans. Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column.

Q#82. What are the advantages of Views?

Ans. Advantages of Views:

Views restrict access to the data because the view can display selective columns from the table.
Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing.

Q#83. List the various privileges that a user can grant to another user?
Ans. SELECT, CONNECT, RESOURCES.

Q#84. What is schema?
Ans. A schema is a collection of database objects of a User.

Q#85. What is Table?
Ans. A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.

Q#86. Do View contain Data?
Ans. No, Views are virtual structure.

Q#87. Can a View based on another View?
Ans. Yes, A View is based on another View.

Q#88. What is the difference between Having clause and Where clause?
Ans. Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaves like WHERE clause only.

Q#89. What is the difference between Local and Global temporary table?
Ans. If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed.

Q#90. What is CTE?

Ans. A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.

1. What is DBMS?

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

2. What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.

Example: SQL Server.

3. What is SQL?

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Standard SQL Commands are Select.

4. What is a Database?

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Example: School Management Database, Bank Management Database.

5. What are tables and Fields?

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

Example:.

Table: Employee.

Field: Emp ID, Emp Name, Date of Birth.

Data: 201456, David, 11/15/1960.

6. What is a primary key?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

7. What is a unique key?

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.

A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.

There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

8. What 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.

9. What is a join?

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

10. What are the types of join and explain each?

There are various types of join which can be used to retrieve data and it depends on the relationship between tables.

Inner Join.

Inner join return rows when there is at least one match of rows between the tables.

Right Join.

Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

Left Join.

Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Full Join.

Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

11. What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

12. What is Denormalization.

DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

13. What are all the different normalizations?

The normal forms can be divided into 5 forms, and they are explained below -.

First Normal Form (1NF):.

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

Second Normal Form (2NF):.

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

Third Normal Form (3NF):.

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

Fourth Normal Form (3NF):.

Meeting all the requirements of third normal form and it should not have multi- valued dependencies.

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

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

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

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

18. What is a relationship and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.

One to One Relationship.
One to Many Relationship.
Many to One Relationship.
Self-Referencing Relationship.

19. What is a query?

A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.

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

21. What are the types of subquery?

There are two types of subquery – Correlated and Non-Correlated.

A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.

A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.

22. What is a stored procedure?

Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

23. What is a trigger?

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.

Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.

24. What is the difference between DELETE and TRUNCATE commands?

DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.

TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

25. What are local and global variables and their differences?

Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called.

Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

26. What is a constraint?

Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are.

NOT NULL.
CHECK.
DEFAULT.
UNIQUE.
PRIMARY KEY.
FOREIGN KEY.

27. What is data Integrity?

Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.

28. What is Auto Increment?

Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.

Mostly this keyword can be used whenever PRIMARY KEY is used.

29. What is the difference between Cluster and Non-Cluster Index?

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.

A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

30. What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.

31. What is Self-Join?

Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.

32. What is Cross-Join?

Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.

33. What is user defined functions?

User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.

34. What are all types of user defined functions?

Three types of user defined functions are.

Scalar Functions.
Inline Table valued functions.
Multi statement valued functions.

Scalar returns unit, variant defined the return clause. Other two types return table as a return.

35. What is collation?

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.

ASCII value can be used to compare these character data.

36. What are all different types of collation sensitivity?

Following are different types of collation sensitivity -.

Case Sensitivity – A and a and B and b.
Accent Sensitivity.
Kana Sensitivity – Japanese Kana characters.
Width Sensitivity – Single byte character and double byte character.

37. Advantages and Disadvantages of Stored Procedure?

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

38. What is Online Transaction Processing (OLTP)?

Online Transaction Processing (OLTP) manages transaction based applications which can be used for data entry, data retrieval and data processing. OLTP makes data management simple and efficient. Unlike OLAP systems goal of OLTP systems is serving real-time transactions.

Example – Bank Transactions on a daily basis.

39. What is CLAUSE?

SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.

Example – Query that has WHERE condition

Query that has HAVING condition.

40. What is recursive stored procedure?

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

41. What is Union, minus and Interact commands?

UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.

MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.

INTERSECT operator is used to return rows returned by both the queries.

42. What is an ALIAS command?

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.

Example-.

Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID

Here, st refers to alias name for student table and Ex refers to alias name for exam table.

43. What is the difference between TRUNCATE and DROP statements?

TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.

44. What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.

Example -.

Aggregate – max(), count - Calculated with respect to numeric.

Scalar – UCASE(), NOW() – Calculated with respect to strings.

45. How can you create an empty table from an existing table?

Example will be -.

Select * into studentcopy from student where 1=2

Here, we are copying student table to another table with the same structure with no rows copied.

46. How to fetch common records from two tables?

Common records result set can be achieved by -.

Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam

47. How to fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers -.

To display even numbers-.

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

To display odd numbers-.

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]

48. How to select unique records from a table?

Select unique records from a table by using DISTINCT keyword.

Select DISTINCT StudentID, StudentName from Student.

49. What is the command used to fetch first 5 characters of the string?

There are many ways to fetch first 5 characters of the string -.

Select SUBSTRING(StudentName,1,5) as studentname from student

Select RIGHT(Studentname,5) as studentname from student

50. Which operator is used in query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as -.

% - Matches zero or more characters.
_(Underscore) – Matching exactly one character.

Example -.

Select * from Student where studentname like 'a%'

Select * from Student where studentname like 'ami_'

1) What is SQL?

SQL stands for the Structured Query Language. SQL is a standard query language used for maintaining the relational database and perform many different operations of data manipulation on the data. SQL initially was invented in 1970. It is a database language used for database creation, deletion, fetching rows and modifying rows, etc. sometimes it is pronounced as 'sequel.'

2) When SQL appeared?

It appeared in 1974. SQL is one of the often used languages for maintaining the relational database. SQL. In 1986 SQL become the standard of American National Standards Institute (ANSI) and ISO(International Organization for Standardization) in 1987.

3) What are the usages of SQL?

SQL is responsible for maintaining the relational data and the data structures present in the database.
To execute queries against a database
To retrieve data from a database
To inserts records in a database
To updates records in a database
To delete records from a database
To create new databases
To create new tables in a database
To create views in a database
To perform complex operations on the database.

4) Does SQL support programming?

SQL refers to the Standard Query Language, which is not actually the programming language. SQL doesn't have a loop, Conditional statement, logical operations, it can not be used for anything other than data manipulation. It is used like commanding (Query) language to access databases. The primary purpose of SQL is to retrieve, manipulate, update and perform complex operations like joins on the data present in the database.

5) What are the subsets of SQL?

There is three significant subset of the SQL:

Data definition language (DDL):DDL is used to define the data structure it consists of the commands like CREATE, ALTER, DROP, etc.
Data manipulation language (DML):DML is used to manipulate already existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.
Data control language (DCL):DCL is used to control access to data in the database and includes commands such as GRANT, REVOKE.

6) What is a Data Definition Language?

Data definition language (DDL) is the subset of the database which defines the data structure of the database in the initial stage when the database is about to be created. It consists of the following commands: CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence, etc.

7) What is a Data Manipulation Language?

Data manipulation language makes the user able to retrieve and manipulate data. It is used to perform the following operations.

Insert data into database through INSERT command.
Retrieve data from the database through SELECT command.
Update data in the database through UPDATE command.
Delete data from the database through DELETE command.

8) What is Data Control Language?

Data control language allows you to control access to the database. DCL is the only subset of the database which decides that what part of the database should be accessed by which user at what point of time. It includes two commands GRANT and REVOKE.

GRANT: to grant the specific user to perform a particular task

REVOKE: to cancel previously denied or granted permissions.

9) What are tables and fields in the database?

A table is a set of organized data. It has rows and columns. Rows here refers to the tuples which represent the simple data item and columns are the attribute of the data items present in particular row. Columns can categorize as vertical, and Rows are horizontal.

A table contains a specified number of the column called fields but can have any number of rows which is known as the record. So, the columns in the table of the database are known as the fields and they represent the attribute or characteristics of the entity in the record.

10) What is a primary key?

A primary key is a field or the combination of fields which uniquely specify a row. The Primary key is a special kind of unique key. Primary key values cannot be NULL. For example, the Social Security Number can be treated as the primary key for any individual.

11) What is a foreign key?

A foreign key is specified as a key which is related to the primary key of another table. A relationship needs to be created between two tables by referencing foreign key with the primary key of another table. Foreign key acts like a cross-reference between tables as it refers to the primary key of other table and the primary key-foreign key relationship is a very crucial relationship as it maintains the ACID properties of database sometimes.

12) What is a unique key?

Unique key constraint uniquely identifies each record in the database. This key provides uniqueness for the column or set of columns.

The Unique key cannot accept a duplicate value.

The unique key can accept only on Null value.

13) What is the difference between primary key and unique key?

Primary key and unique key both are the essential constraints of the SQL, but there is a small difference between them

Primary key carries unique value but the field of the primary key cannot be Null on the other hand unique key also carry unique value but it can have a single Null value field.

14) What is a Database?

A Database is an organized form of data. The database is the electronic system which makes data access, data manipulation, data retrieval, data storing and data management very easy and structured. Almost every organization uses the database for storing the data due to its easily accessible and high operational ease. The database provides perfect access to data and lets us perform required tasks.

The Database is also called a structured form of data. Due to this structured format, you can access data very easily.

15) What is DBMS?

DBMS stands for Database Management System. This is a program which is used to control them. It is like a File Manager that manages data in a database rather than saving it in file systems.

Database management system is an interface between the database and the user. It makes the data retrieval, data access easier.

Database management system is a software which provides us the power to perform operations such as creation, maintenance and use of a data of the database using a simple query in almost no time.

Without the database management system, it would be far more difficult for the user to access the data of the database.

16) What are the different types of database management systems?

There are four types of database:

Hierarchical databases (DBMS)
Relational databases (RDBMS)
Network databases (IDMS)
Object-oriented databases

RDBMS is one of the most often used databases due to its easy accessibility and supports regarding complex queries.

17) What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database management system based on a relational model. RDBMS stores the data into the collection of tables and links those table using the relational operators easily whenever required. It facilitates you to manipulate the data stored in the tables by using relational operators. Examples of the relational database management system are Microsoft Access, MySQL, SQLServer, Oracle database, etc.

18) What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by organizing fields and table of a database.

There are some rules of database normalization which commonly known as Normal From and they are:

First normal form(1NF)
Second normal form(2NF)
Third normal form(3NF)
Boyce-Codd normal form(BCNF)

Using these steps, the redundancy, anomalies, inconsistency of the data in the database can be removed.

19) What is the primary use of Normalization?

Normalization is mainly used to add, delete or modify a field that can be made in a single table. The primary use of Normalization is to remove redundancy and to remove the insert, delete and update distractions. Normalization breaks the table into small partitions and then link them using different relationships so that it will avoid the chances of redundancy.

20) What are the disadvantages of not performing Database Normalization?

The major disadvantages are:

The occurrence of redundant terms in the database which causes the waste of the space in the disk.
Due to redundant terms inconsistency may also occur id any change will be made in the data of one table but not made in the same data of another table then inconsistency will take place, which will lead to the maintenance problem and effects the ACID properties as well.

21) What is an inconsistent dependency?

Inconsistent dependency refers to the difficulty of accessing particular data as the path to reach the data may be missing or broken. Inconsistent dependency will leads users to search the data in the wrong table which will afterward give the error as an output.

22) What is Denormalization in a Database?

Denormalization is used to access the data from higher or lower normal form of database. It also processes redundancy into a table by incorporating data from the related tables. Denormalization adds required redundant term into the tables so that we can avoid using complex joins and many other complex operations. Denormalization doesn?t mean that normalization will not be done, but the denormalization process takes place after the normalization process.

23) What are the types of operators available in SQL?

Operators are the special keywords or special characters reserved for performing particular operations and are used in the SQL queries. There is three type of operators used in SQL:

Arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), etc.
Logical operators: ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.
Comparison operator: =, !=, <>, <, >, <=, >=, !<, !>

24) What is view in SQL?

A view is a virtual table which contains a subset of data within a table. Views are not originally present, and it takes less space to store. A view can have data from one or more tables combined, and it depends on the relationship. Views are used to apply security mechanism in the SQL Server. The view of the database is the searchable object we can use a query to search the view as we use for the table.

25) What is an Index in SQL?

SQL indexes are the medium of reducing the cost of the query as the high cost of the query will lead to the fall in the performance of the query. An index is used to increase the performance and allow faster retrieval of records from the table. Indexing reduces the number of data pages we need to visit to find a particular data page. Indexing also has a unique value that means that the index cannot be duplicated. An index creates an entry for each value, and it will be faster to retrieve data. For example, suppose you have a book which carries the details of the countries, and you want to find out the information about India than why you will go through every page of that book you could directly go to the index, and then from index you can go to that particular page where all the information about India is given.

26) Which are the different types of indexes in SQL?

There are three types of Indexes in SQL:

Unique Index
Clustered Index
NonClustered Index
Bit-Map index
Normal index
Composite index
B-tree index
function based index

27) What is the unique Index?

Unique Index:

For creating a unique index, the user has to check the data in the column because the unique indexes are used when any column of the table has unique values. This indexing does not allow the field to have duplicate values if the column is unique indexed. A unique index can be applied automatically when a primary key is defined.

28) What is Clustered Index in SQL?

Clustered Index:

The clustered index is used to reorder the physical order of the table and search based on the key values. Each table can have only one clustered index. The Clustered index is the only index which has been automatically created when the primary key is generated. If moderate data modification needed to be done in the table then clustered indexes are preferred.

29) What is the Non-Clustered Index in SQL?

Non-Clustered Index:

The reason to create non-clustered index is searching the data. We well know that clustered indexes are created automatically primary keys are generated, but non-clustered indexes are created when multiple joins conditions and various filters are used in the query. Non-Clustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non-clustered indexes.

30) What is the difference between SQL, MySQL and SQL Server?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

MySQL is available for free as it is open source whereas SQL server is not an open source software.

31) What is the difference between SQL and PL/SQL?

SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90's. It adds procedural features of programming languages in SQL.

In SQL single query is being executed at once whereas in PL/SQL a whole block of code is executed at once.

SQL is like the source of data that we need to display on the other hand PL/SQL provides a platform where the SQL the SQL data will be shown.

SQL statement can be embedded in PL/SQL, but PL/SQL statement cannot be embedded in SQL as SQL do not support any programming language and keywords.

32) Is it possible to sort a column using a column alias?

Yes. You can use the column alias in the ORDER BY instead of WHERE clause for sorting.

33) What is the difference between clustered and non-clustered index in SQL?

There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective.

One table can have only one clustered index, but it can have many non-clustered index. (Approximately 250).
A clustered index determines how data is stored physically in the table. Clustered index stores data in the cluster, related data is stored together, so that retrieval of data becomes simple.
Clustered indexes store the data information and the data itself whereas non-clustered index stores only the information, and then it will refer you to the data stored in clustered data.
Reading from a clustered index is much faster than reading from non-clustered index from the same table.
Clustered index sort and store data row in the table or view based on their key value, while non-cluster has a structure separate from the data row.

34) What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate() which is used to return the current timestamp.

35) Which are the most commonly used SQL joins?

Most commonly used SQL joins are INNER JOIN and LEFT OUTER JOIN and RIGHT OUTER JOIN.

36) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables.

Following are the most commonly used joins in SQL:

Inner Join: inner joins are of three type:

Theta join
Natural join
Equijoin

Outer Join: outer joins are of three type:

right outer join
Left outer join
Full outer join

37) What is Inner Join in SQL?

Inner join:

Inner join returns rows when there is at least one match of rows between the tables. INNER JOIN keyword joins the matching records from two tables.

INNER JOIN

38) What is Right Join in SQL?

Right Join:

Right Join is used to retrieve rows which are common between the tables and all rows of a Right-hand side table. It returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.

RIGHT JOIN

39) What is Left Join in SQL?

Left Join:

The left join is used to retrieve rows which are common between the tables and all rows of the Left-hand side table. It returns all the rows from the Left-hand side table even though there are no matches on the Right-hand side table.

LEFT JOIN

40) What is Full Join in SQL?

Full Join:

Full join return rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

FULL OUTER JOIN

41) What is a "TRIGGER" in SQL?

A trigger allows you to execute a batch of SQL code when an insert, update or delete command is run against a specific table as TRIGGER is said to be the set of actions that are performed whenever commands like insert, update or delete are given through queries.
The trigger is said to be activated when these commands are given to the system.
Triggers are the particular type of stored procedures that are defined to execute automatically in place or after data modifications.
Triggers are generated using CREATE TRIGGER statement.

42) What is self-join and what is the requirement of self-join?

A self-join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.

43) What are the set operators in SQL?

SQL queries which contain set operations are called compound queries.

Union, Union All, Intersect or Minus operators are the set operators used in the SQL.

44) What is the difference between BETWEEN and IN condition operators?

The BETWEEN operator is used to display rows based on a range of values. The values can be numbers, text, and dates as well. BETWEEN operator gives us the count of all the values occurs between a particular range.

The IN condition operator is used to check for values contained in a specific set of values. IN operator is used when we have more than one value to choose.

45) What is a constraint? Tell me about its various levels.

Constraints are the rules and regulations which are applied to the table column which enforces yours to store valid data and prevents users to store irrelevant data. There are two levels :

column level constraint
table level constraint

46) Write an SQL query to find names of employee start with 'A'?

SELECT * FROM Employees WHERE EmpName like 'A%'

47) Write an SQL query to get the third maximum salary of an employee from a table named employee_table.

SELECT TOP 1 salary
FROM (
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC ) AS emp
ORDER BY salary ASC;

48) What is the difference between DELETE and TRUNCATE statement in SQL?

The main differences between SQL DELETE and TRUNCATE statements are given below:

No.	DELETE	TRUNCATE
1)	DELETE is a DML command.	TRUNCATE is a DDL command.
2)	We can use WHERE clause in DELETE command.	We cannot use WHERE clause with TRUNCATE
3)	DELETE statement is used to delete a row from a table	TRUNCATE statement is used to remove all the rows from a table.
4)	DELETE is slower than TRUNCATE statement.	TRUNCATE statement is faster than DELETE statement.
5)	You can rollback data after using DELETE statement.	It is not possible to rollback after using TRUNCATE statement.

49) What is ACID property in a database?

ACID property is used to ensure that the data transactions are processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.

50) What is the difference between NULL value, zero and blank space?

Ans: A NULL value is not the same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable.' On the other hand, zero is a number, and a blank space is treated as a character.

The NULL value can be treated as unknown and missing value as well, but zero and blank spaces are different from the NULL value.

51) What is the usage of SQL functions?

Functions are the measured values and cannot create permanent environment changes to SQL server. SQL functions are used for the following purpose:

To perform calculations on data
To modify individual data items
To manipulate the output
To format dates and numbers
To convert data types

52) What do you understand by case manipulation functions?

Case manipulation functions are the functions which convert the data from the state in which it is already stored in the table to upper, lower or mixed case.

Case manipulation function can be used in almost every part of the SQL statement.

Case manipulation functions are mostly used when you need to search for data, and you don?t have any idea that the data you are looking for is in lower case or upper case.

53) Which are the different case manipulation functions in SQL?

There are three case manipulation functions in SQL:

LOWER: converts character into Lowercase.
UPPER: converts character into uppercase.
INITCAP: converts character values to uppercase for the initials of each word.

54) Explain character-manipulation functions?

Character-manipulation functions are used to change, extract, alter the character string.

One or more than one characters and words should be passed into the function, and then the function will perform its operation on those words.

55) Which are the different character-manipulation functions in SQL?

CONCAT: join two or more values together.
SUBSTR: used to extract the string of specific length.
LENGTH: return the length of the string in numerical value.
INSTR: find the exact numeric position of a specified character.
LPAD: padding of the left-side character value for right-justified value.
RPAD: padding of right-side character value for left-justified value.
TRIM: remove all the defined character from the beginning, end or both beginning and end.
REPLACE: replace a specific sequence of character with other sequences of character.

56) What is the usage of NVL() function?

The NVL() function is used to convert NULL value to the other value. NVL() function is used in Oracle it is not in SQL and MySQL server.

Instead of NVL() function MySQL have IFNULL() and SQL Server have ISNULL() function.

57) Which function is used to return remainder in a division operator in SQL?

The MOD function returns the remainder in a division operation.

58) What are the syntax and use of the COALESCE function?

The syntax of COALESCE function:

COALESCE(exp1, exp2, .... expn)

The COALESCE function is used to return the first non-null expression given in the parameter list.

59) What is the usage of the DISTINCT keyword?

The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value. The DISTINCT keyword is used to SELECT DISTINCT, and it always fetches different (distinct) from the column of the table.

Q1) Difference between SQL Vs NoSQL ?

SQL Vs NoSQL
Feature	SQL	NoSQL
Type of Data Base	Relational data base	Non relational data base/Distributed data base
Standardardization	Standard Query Language exist	No proper standards defined
Reporting Tools	Various tools available to analyse performance	Unaivalability of tools to analyse data and performance
Development model	Fine grained database model	Architects can create new DB models
Price	Expensive compared to NoSQL	Low Cost - Mostly Open Source
Schema	Predefined Schema available	Unstructured data with dynamic schema
Database Examples	PostGres, Sqlite, Oracle, etc.,	BigTable, Cassandra, MongoDB, etc.,
Type of Data storage	Not suitable for hierarchial data storage	Best suitable for hierarchial data.

Q2) What do you know about a Database Management system?

It is basically a program which is considered when it comes to maintaining, creating, deploying, controlling as well as monitoring the use of a database. It can also be considered as a file manager which is good enough to be trusted for managing the data kept in a database than a file system. Database approach is really a good one as it being numerous benefits for the organizations. The entire data can easily be managed simply irrespective of its size and complexity.

Q3) What do you mean by Fields and Tables and how they are useful?

Basically, a table is a set of different rows and columns and is organized in a model. The manner of columns and rows are vertical and horizontal. In a table, there are some specific numbers of columns which remains present and is generally known as fields. There is no strict upper limit on the overall number of records which are defined by rows in the table.

Q4) Compare SQL with Oracle?

SQL	Oracle
It is more scalable and secure than Oracle	Oracle too is secure and scalable but not upto the extent SQL
It widely support procedural extensions	The support to the same is limited

Q5) How the Inner Join in SQL is different from that of Outer Join?

An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer Join is the one that is useful for returning the value of rows and tables that generally include the records that must be same in all the tables.

Q6) Can you tell something about the Primary key in SQL and what is its significance?

It is basically an array or a group of fields that generally specify a row. It is considered as one of the unique keys that always have some defined or specific value. Generally, the users need not to worry about anything when it is enabled as it cannot have a null value. It is capable to identify all the records in a database simply and the users are free to get the best possible outcome with minimum efforts. This is exactly what that makes sure of uniqueness.

Q7) What do you know about the database testing and how it can help getting useful results for the database users?

It is basically nothing but the back end testing or the data testing. It generally involves keeping an eye on the integrity of the data an organization use. It generally validates some of the very useful tasks such as database, indexes, columns, tables as well as triggers. IT also make sure that no duplicate data exist in the database which causes a very large number of problems and the best part is the junk records can also be trashed in a very reliable manner. The updating of record is also a task that can be made easy with the help of this approach.

Q8) Tell something you know about the SQL constraints?

These are some important rules in the SQL which are responsible for the restrictions when it comes to deleting, updating or changing the primary data present in the database.

Q9) In SQL, what do you know about the composite primary key?

The key which is created on multiple columns in a table is generally considered as the Composite primary key. However, it is not always necessary that all of them have a same meaning.

Q10) Difference Between Stored Procedure & Functions?

Stored Procedure	Functions
It is a set of pre-compiled SQL Statements which will gets executed when we call it	It will take input from user and return only one value of any data type
Compile only one time	Compile every time
Stored Procedure will have execution plan	Function will not have execution plan
Support DML Commands	Not supported DML Commands
Support TCL Commands	Not supported TCL Commands
It is may or may not have input parameter	Function must have at least one input parameter
It is accept both input and output parameters	Doesn’t have output parameters
We call call stored procedure in another stored procedure	We can call function in another function
It is support Exception Handling	It is doesn’t support Exception Handling
We can call function in stored Procedure	We can’t call stored procedure in function

Q11) What do you know about Field in a Database?

It is basically a space that is allotted for storing some records that are present within a table. There are actually different fields and it is not always necessary that all the fields are same in terms of size and allocation pattern.

Q12) Name a few commands which you think are important in SQL for managing the database?

You can answer this questions based on the commands you have used in your past if you having a bit experience in SQL. Else, the following commands are there which are widely adopted and are very useful.

1.Data Definition Language
2.Transaction Control Language
3. Data Query Language
4. Data Manipulation Language
5. Data Control Language

Q13) Name a few important DDL commands present in the SQL?

They are generally preferred when it comes to defining or changing the structure of a specific database in the shortest possible times due to security and other concerns. Some of the commands that can be applied and considered directly for this are as follows.
Create, Alter, Drop, Rename, Truncate and copy

Q14) Tell something about the Temp Table?

It is basically a structure in the SQL that is sued for storing any sort of data that is not permanent or need to be stored for a specific time period. Depending on the needs, it is possible to extend the space upto any extend. Generally, a limited space is kept reserved as the temp table.

Q15) Name any two commands that are used for the purpose of managing the data present in the database

These are Update and Insert

16) What do you mean by Term and how it is different from that of Index?

When it comes to handling the queries at a faster rate, the Indexes are preferred widely in SQL and they simply make sure of quick retrieval of the data and the concerned information from the table present in the database. It is possible to create the index on a single column or a group of same.

On the other side, a View is basically nothing but the subset of a table and is used for the purpose of storing the database in a logical manner. It is actually a virtual table that has rows as well as the columns which are similar to that of a real table. However, the views contain data that actually don’t belong to them. The same is considered when it comes to restricting the access of a database.

Q17) Is it possible for the users to compare the test for the NULL values in SQL?

No, the same is not possible

Q18) What do you mean by the term SQL?

It stands for Structured Query Language and is a powerful language to communicate the database and monitor the concerned tasks easily and reliably. A lot of important tasks such as updating the database, controlling, modifications, as well as deletion of data can be performed with this task. It comes with so many dedicated features in it that are good enough to make a database completely useful and reliable to consider. There are many commands that can be considered and help saving a lot of time when it comes to getting the best out of a database.

Q19) Tell something about Subquery in the SQL?

It is basically a SQL query and is generally regarded as the subset of the select statement and the process of those tasks that generally make sure of filtering the conditions related to the main query.

Q20) In a query, is it possible for the users to avoid the duplicate records? How this can be done?

Yes, the same is possible and there are many methods that can help users to get the favorable fortune in this matter. The best one is to deploy the SQL SELECT DISTINCT query which is sued to return the unique values. All the repeated values or the ones which are duplicate get deleted automatically.

Q21) What is the significance of the default constraint in SQL?

It is used when it comes to including a default value in a column in case there is no new value provided at the time a record is inserted.

Q22) What are the factors that can affect the functionality of a database according to you?

There are certain things that largely matters. The first and the foremost is nothing but the size of the database in terms of its storing capacity. Of course, for a bigger database, the needs are complex and so does its management. Thus, the first thing that can help keeping up the pace in this matter is a powerful query language or a controlling procedure. Next thing is the security of the database. In addition to this, the experience of the experts handling the important operations can also largely impact the database. Moreover, there are conditions on the operation of the same that also largely matter.

Q23) How can you put separate the Rename and the Alias?

A permanent name which is given to a table or a column in SQL is considered as “Rename” whereas the temporary name
given to the same is considered as “Alias”

Q24) What is a Join and what are the different types of same present in the SQL?

Join is basically a query that is useful for the purpose of retrieving the columns and the rows. It is useful when the users have to handle a very large number of tables at the same time. The different types of Joins that are present in the SQL are Right Jin, Inner Join, Left Join, Outer Join and Upper Join.

Q25) What do you know about the NULL value in the SQL?

It is basically a field which doesn’t have any value in SQL. It is totally different from that of a zero value and must not be put equal or confused with the same. These fields are left blank during the creation of the records.

Q26) How can you say Normalization is a useful process in database management?

It is basically an approach with one of its primary aim is to simply impose a strict upper limit on the redundancy of the data. The users are free to go ahead with many of the normalizations forms present in the SQL and a few of them are First, second, third and Boyce Normal Form.

Q27) What do you know about the stored procedure?

It is nothing but an array of some important SQL statements that are stored in the database and are responsible for performing a specific task.

Q28) Name the SQL procedure which makes sure an immediate action in response to an event?

The same is Trigger

Q29) In the Boolean Data Field, what are the possible values that users can simply store?

This can be TRUE or FALSE

Q30) Name the types of Indexes of which are available in SQL

There are three important types of Indexes and they are Unique Index, Clustered Index as well as Non Clustered Index.

Q31) In SQL, what is the best thing about the Views you have come across?

These are several good things about them. The very first thing is they consume almost no space which makes them good enough to be considered at every situation. At the same time, the users are able to consider views for simply retrieving the outcomes that belongs to queries which are complicated in nature. The same may need to be executed frequently. It is possible to consider this when it comes to restricting the access to the database.

Q32) What is SQL?

SQL- A Structured Query Language, It is also pronounced as “SEQUEL” and it an Non-procedural Language which is used to operate all relational database. Used for Database communication. Its a standard language that can be used to perform the tasks like data retrieval, data update, insert or delete data from an database.

Features of SQL:

Portability
Client server architecture,
Dynamic data definition,
Multiple views of data,
Complete database language,
Interactive,
High level,
Structure and SQL standards.

Q33) Difference between SQL & MYSQL ?

SQL is more natural than MYSQL. MySQL is a computer application. whose DBMS allows multiple users. It enables access to several database application and management system. SQL is more natural and standard language that is used with different applications alike. But, however no organization actually employs this standard language, rather every software firm follows its own kind of SQL version.

SQL Vs MySQL
SQL	MYSQL
SQL stands for Structured Query Language	MySQL is a RDMS (Relational Database Management System)
Allow for accessing and manipulating db's	MySQL is a database management system, like SQL Server, Oracle, Postgres, Informix etc
Basically works as the prompter to a DBMS	It Facilitates multi-user access to a huge number of DBs
SQL codes & commands are used in various DBMS and RDBMS systems such as MySQL.	MySQL has SQL at its core, and requires future upgrades mostly

Q34) What is SQL Server?

SQL Server is Microsoft's relational database management system (RDBMS). End user cannot interact directly with database server. If we want to interact with SQL database server then we have to interact with SQL.

Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL Server Training Demo!

Q35) What are the different types of SQL’s statements?

1. DQL - Data Query Language ( or) Data Retrival Language

SELECT Statement

2. DML – Data Manipulation Language
DML is used for manipulation of the data itself.

INSERT Statement
UPDATE Statement
DELETE Statement

3. DDL – Data Definition Language
DDL is used to define the structure that holds the data.

CREATE Statement
ALTER Statement
DROP Statement
RENAME Statement
TRUNCATE Statement

4. DCL – Data Control Language
DCL is used to control the visibility of data.

GRANT Statement
REVOKE Statement

5. TCL - Transaction Control Language

COMMIT Statement
ROLLBACK Statement
SAVEPOINT Statement

Q36) What are various DDL commands in SQL? Give brief description of their purposes.

DDL Commands are used to define structure of the table

1. CREATE
It is used to create database objects like tables, views, synonyms, indexes.
Creating Table:
Syntax-
Create table table_name(columname1 datatype(size), columname2 datatype(size),....);

2. ALTER
It is used to change existing table structure.
Alter:: a) add
b) modify
c)drop
a) Add:
It is used to add columns into existing table
Syntax:
Alter table table_name add(columnname1 datatype(size), columname2 datatype(size),....);

b) Modify:
It is used to change column Datatype or datatype size only.
Syntax:
Alter table table_name modify(columnname1 datatype(size), columnname2 datatype(size),....);

c) Drop:
It is used to drop columns from the table.
Method1:
If we want to drop single column at a time without using parentheses then we are using following syntax.
Syntax:
alter table table_namedrop column col_name1; -- drop ONE column

Method2:
If we want to drop single or multiple columns at a time with using paranthesis then we are using following syntax.
Syntax:
alter table table_name drop(column_name_list);

Note:
In all databases we can’t drop all columns in the table.

3. DROP
It is used to remove database objects from database.
Syntax:

Drop object object_name; 
       (or)
Drop table table_name;  
       (or)
Drop view view_name;

4. RENAME
It is used to renaming a table.
Syntax:
Rename old table_name to new table_name;

Renaming a column:
Syntax:
Alter table table_name rename column old column_name to new column_name;

5. TRUNCATE
Oracle 7.0 introduced truncate table command it is used to delete all rows permanently from the table.
Syntax:
truncate table table_name;

Q37) What are various DML commands in SQL? Give brief description of their purposes.

DML Commands are used to manipulate data within a table.
There are:: INSERT, UPDATE, DELETE

1. INSERT − It is used to insert data into in the table

Method1:
Syntax:
Insert into table_name values(values1, value2, value3,……);

Method2:- Using Substitutional operator (&)
Synatx:
Insert into table_name values(& columnname1, columnname2,.....);

Method3:- Skipping columns
Syntax:
Insert into table_name(col1, col2,...) values(val1, val2, val3,...);

2. UPDATE - It is used to change data in a table.
Syntax:
Update table_name set columnname=new value where columnname=old value;

Note: In all databases we can also use update statement for inserting data into particular shell.

3. DELETE - It is used to delete rows or particular rows from a table.
Syntax:
Delete from table_name;
(or)
Delete from tablename where condition;

Q38) Difference Between Delete & Truncate?

SQL Delete Vs SQL Truncate
DELETE	TRUNCATE
It is DML Command	It is DDL Command
It is used to delete all the records row by row	It is used to delete all the records at a time
By using delete command we can delete specific record	By using truncate we cannot delete specific record
Where condition we can use with delete command	Where condition will not work with truncate
Delete will work slow compare with truncate	Truncate will work fast compare with delete
Delete will not reset auto generate id. Once when we delete all the records from the table.	Truncate will reset auto generate id from starting number.

Q39) About The SQL Buffer?

All Commands of SQL are Typed at the SQL prompt.
Only One SQL Statements is Managed in The SQL Buffer.
The Current SQL Statement Replaces the Previous SQL Statement in the Buffer.
The SQL Statement Can be Divided Into Different Lines Within The SQL Buffer.
Only One Line i.e., The Current Line Can be Active at a Time in the SQL Buffer.
At SQL Prompt, Editing is Possible Only in The Current SQL Buffer Line.
Every Statement of SQL should be terminated Using Semi Colon ”;”
One SQL Statement can Contain Only One Semo Colon.
To Run the Previous OR Current SQL Statement in the Buffer Type “/” at SQL Prompt.
To Open The SQL Editor Type “ED” at SQL Prompt.

Q40) What are Important SQL Functions?

LOWER Function:(Column/Expression):

It Converts Alpha Character Values to Lower Case.
The Return Value Has The Same Data Type as Argument CHAR Type (CHAR or VARCHAR2)

UPPER Function:

It Converts the Alpha Character Values to Upper Case.
The Return Value Has The Same Data Type as Argument CHAR.

INITCAP Function:

It Converts Alpha Character Values into Upper Case For The First Letter of Each Word, keeping all Other Letter in Lower Case.
Words are Delimited by White Space or Characters That are Not Alphanumeric

LPAD Function:

Pads The Character Value Right Justified to a Total Width of ‘n’ Character Positions.
The Default Padding Character in Space.

RPAD Function:

Pads the Character Value Left Justified to a Total Width of ‘n’ Character ositions.
The Default Padding Character is Space.

LTRIM Function:

It Enables to TRIM Heading Character From a Character String.
All The Leftmost Character That Appear in The SET are Removed.

RTRIM Function:

It Enables the Trimming of Trailing Character From a Character STRING.
All the Right Most Characters That Appear in The Set are Removed.

TRIM Function:

It Enables to TRIM Heading or Trailing Character or Both From a Character String.
If LEADING is Specified Concentrates On Leading Characters.
If TRAILING is Specified Concentrates on Trailing Characters.
If BOTH OR None is Specified Concentrates Both on LEADING and TRAILING.
Return the VARCHAR2 Type.

Advanced SQL Interview Questions & Answers

Q41) How to Open SQL Server?

Goto -> Start -> All Programms -> Microsoft SQL Server 2008 R2 -> SQL Server management Studio.

Q42) What is SQL Injections? And How to Prevent SQL Injection Attacks?

It is a mechanism of getting secure data from database.

SQL Injection Attacks::

By providing proper validations for input fields.
By using parameterised queries.
By using stored procedures
By using frequent code reviews
We must not display database error messages in frontend
SQL injection is a code injection technique, used to attack data-driven applications.

Q43) Difference Between Scalar Valued Functions & Table Valued Functions in SQL?

SQL Scalar Valued Functions Vs SQL Table Valued Functions
Scalar Valued Functions	Table Valued Functions
It will process on single row ata time & return only one value of any database	It will process on multiple rows at a time & return multiple rows (or) single row from table
The return type of scalar valued function is datatype	The return type of table valued function is table
Scalar valued function will have as begin block end	Table valued function will not have as begin end
Syntax to call Scalar Valued Functions is:: `SELECT dbo. funname(values);`	Syntax to call Table Valued Functions is:: `SELECT * FROM dbo.funname(values);`

Q44) How can you say that Database testing is different from that of GUI testing?

GUI testing is always performed at the front end whereas the Database testing is performed at the back end
When it comes to dealing with the testable items, generally the users prefer GUI testing. These items are present clearly./ On the other hand the Database testing deals with the testable items that are hidden and are not directly visible to the users
Structured Query Language largely matters in Database approach where the same doesn’t have any application with the GUI
Invalidating the test boxes are a part of GUI database where as the Database testing is totally different in this manner

Q45) Write a Query to view the indexes that are applied on the table?

stored procedure_helpindex table_name

Q46) Difference Between Long & Lob Datatypes?

Long	Lob
It stores upto 2GB Data	It stores upto 4 GB Data
A table can contain only one long column	A table can contain more than Lob column
Subquery cannot select a Long datatype column	Subquery can select Lob Column

Q47) Difference Between (Null Value Function) nvl() & Coalesce()

Nvl is an oracle function whereas Coalesce is an ANSI Function and also coalesce performance is very high as compare to NVL Function.
NVL Function internally uses implicit conversions i.e NVL Function returns a value if the exp1, exp2 is not belong to same datatype also if exp2 automatically converted into exp1 where as in coalesce function exp1, exp2 must belongs to same datatype.

Examples1::
SELECT nvl(‘a’, sysdate) FROM dual;
Output::
a

Examples2::
SELECT Coalesce(‘a’, sysdate) FROM dual;
Error: inconsistent datatypes: expected CHAR got DATE

Q48) What is Tuple?

Tuples are the members of a relation. An entity type having attributes can be represented by set of these attributes called tuple.

Q49) What is Query & Query Language?

A query is a statement requesting the retrieval of information.
The portion of dimly that involves information retrieval is called a query language.

Q50) Difference Between Views & Materialized Views?

Views	Materialized Views
View does not store data	Materialized view stores data
Security purpose	Improved performance purpose
When we ar4e dropping base table then view can’t be accessible	When we are dropping base table also materialized view can be accessible
Through the view we can perform DML Operation	We can’t perform DML operation

CHECKOUT SQL TUTORIALS

Q51) What are the different aggregate functions in SQL?

AVG(), MIN(), MAX(), SUM(), COUNT()

Q52) What is data independence?

A database system keeps data separate from software data structure.

Q53) What is data integrity?

Data must satisfy the integrity constraints of the system for data Quality.

Q54) What is Dead locking?

It is the situation where two transactions are waiting for other to release a lock on an item.

Q55) What is decryption?

Taking encoded text and converting it into text that you are able to read.

Q56) What is projection?

The Projection of a relation is defined as projection of all its tuples over a set of attributes. It yields vertical subset of the relation. The projection operation is used to view the number of attributes in the resultant relation or to reorder attributes.

Q57) What is Encryption?

Encryption is the coding or scrambling of data so that humans can not read them directly.

Q58) What is cardinality?

The number of instances of each entity involved in an instance of a relation of a relationship describe how often an entity can participate in relation ship. (1:1, 1:many, many:many).

Q59) What is Transaction Control?

Oracle Server Ensures Data Consistency Based Upon Transaction.
Transactions Consist of DML Statements That Make Up One Consistent Change To The Data,

Q60) What are the Transaction Start & End Cases?

A Transaction Begins When The First Executable SQL Statement is Encountered.
The Transaction Terminates When The Following Specifications Occur.

A COMMIT OR ROLLBACK is Issued
A DDL Statement Issued.
A DCL Statement Issued.

The Usr Exists The SQL * Plus
Failure of Machine OR System Crashes.
A DDL Statement OR A DCL Statement is Automatically Committed And Hence Implicitly Ends A Transaction.

Q61) GRANT Command?

Syntax::

SQL> GRANT< Privilage Name1>, ,
ON
TO ;

GRANT Command is Used When We Want The Database To Be Shared With Other Users.

The Other Users Are GRANTED With Certain Type of RIGHTS.
GRANT Command Can Be issued Not Only on TABLE OBJECT, But Also on VIEWS, SYNONYMS, INDEXES, SEQUENCES Etc.

SQL> GRANT SELECT
ON EMP
TO ENDUSERS;
SQL> GRANT INSERT, SELECT, DELET
ON EMP
TO OPERATORS;
SQL> GRANT INSERT (Empno, Ename, Job)
ON Emp
To EndUsers;

Q62) REVOKE Command?

Syntax::

SQL> REVOKE< Privilage Name1>, ,
ON
FROM;

REVOKE Command is Used When We Want One Database To Stop Sharing The Information With Other Users.
Revoke Privileges is Assigned Not Only On TABLE Object, But Also on VIEWS, SYNONYMS, INDEXES Etc.
SQL> REVOKE INSERT, DELETE
ON EMP
FROM Operators;

Q63) Connecting to Oracle OR SQL * Plus?

Double Click the SQL*Plus ShortCut on the Desktop.

Start -> Run -> Type SQLPlus OR SQLPlusW in Open Box and Click OK.

Start -> Programs -> Oracle -> Application Development -> SQL*Plus

In the Login Box OR Login Prompt Type the User Name and Password as Supplied by the Administrator.

The Host String is Optional and is provided by the Administrator.

Q64) About PL/SQL Tables?

Objects of Type “TABLE” Are Called PL/SQL Tables.
They Are Modeled As Database Tables, But Are Not Same.
PL/SQL TABLES Use A “PRIMARY KEY” To Give Array Like Access To Rows.
PL/SQL Tables Are Very Dynamic in Operation, Giving The Simulation To Pointers in ‘C’ Language.
They Help in Integrating The Cursors For Dynamic Management of Records At Run Time.
They Make Runtime Management of Result Sets Very Convenient.

Frequently Asked Oracle PL SQL Interview Questions & Answers

Q65) Difference Between SQL and PL/SQL?

SQL	PL/SQL
It’s complete name is structured query language	It’s complete name is procedural Language / Structured Query Language
It doesn’t have the any facility of branching or looping	It has the complete facility of branching or looping
In SQL, only one statement can be sent to Oracle Engine.	It increase the execution time In PL/SQL, a complete block of statements can be sent to Oracle engine at a time, reducing traffic
In SQL, the use of variables is not possible	In PL/SQL, the results of the statements can be stored in variables and can be used further as per the requirement
It doesn’t have the capacity for procedural language	It is fully support procedural language
In SQL, there is no facility of error management. In case of error condition, It is the Oracle Engine that tracks it.	In PL/SQL, the results of the statements can be stored in variables and can be used further as per the requirement

Q66) What is a CURSOR?

CURSOR is a Handle, OR Pointer To The CONTEXT AREA

Q67) What is The CURSOR Usage?

Using a CURSOR, The PL/SQL program can control the CONTEXT AREA, As the SQL Statement is being processed.

Q68) What are the CURSOR Features?

CURSOR Allows to FETCH and process Rows returned by a SELECT statement, One Row at a time.
A CURSOR is named, such that it can be referenced by the PL/SQL programmer dynamically at run time.

Q69) What are the Different Types of Constraints?

Null Constraint
Not Null Constarint
Primary Key Constraint
Unique Key Constraint
Foreign Key Constraint
Composite Primary Key Constraint
Default Constraint
Check Constraint

Q70) %Found

This attribute returns boolean value either true or false.
This attribute returns true when fetch statement returns atleast one records.

Syntax:

cursorname%found
SQL>declare
Cursor c1 is select * from emp
Where ename =’&ename’;
i emp% rowtype;
begin
open c1;
fetch c1 into i;
If c1%found then
dbms_outpit.put_line(your employee exists’||’ ‘||i.ename|| ‘ ‘||i.sal);
else if c1%not found then
dbms_output.put_line(‘your employee does not exists’);
end if;
close c1;
end;
/

Output: enter value for ename:murali
Employee doe snot exists

Output: enter value for ename:KING
Employee exists KING 7400

Q71) Explain Eliminating Explicit Cursor Life Cycle (or) Cursor FOR Loops?

Using cursor for loop we are eliminating explicit cursor life cycle i.e whenever we are using cursor for loop no need to use open, fetch, close statement explicitly i.e when we are using cursor for loop oracle server only internally automatically open the cursor, and then fetch data from the cursor and close the cursor.

Syntax:For index

varname in cursorname
Loop
stmts;
end loop;

Note:
In cursor for loop index variable internally behaves like a record type variable. (%row type)

Q72) What are the Cursor Attributes?

Attribute Name	Return Value	Condition
%found	True ------------- False	If fetch statement return at least one row ------------------------------------------------- If fetch statement doesn't returns any row
%notfound	True ------------ False	If fetch statement doesn't returns any row -------------------------------------------------- If fetch statement return at least one row
%isopen	True ---------- False	If cursor is already opened -------------------------------------------------- If cursor is not opened
%rowcount	Number	If counts number of records number fetches from the cursor

Q73) What is Autonomous Transaction?

Autonomous transactions are independent transaction used in anonymous blocks, procedures, functions, triggers.
Generally we are defining autonomous transaction is child procedure.
Whenever we are calling autonomous procedure in main transaction and also main transaction TCL commands never affected on autonomous TCL commands procedure, because these are independence procedure.
If we want to procedure autonomous then we are using autonomous transaction pragma, commit i.e in declare section of the procedure we are defining autonomous transaction pragma and also we must use commit in procedure coding.

Q74) What is Out Mode?

We can also use out mode parameter i function, but these functions are not allowed to execute by using select statement. If we want to return more no.of values from a function then only we are allowed to use out parameter. Here also out parameter behaves like a uninitialized variables.

Q75) What is SQL Loader?

SQL Loader is an utility program which is used to transfer data from flat into oracle database. SQL Loader always executes control file based on the type of flat file we are creating control file and then submit conrol file to SQL loader then only SQL loader transfer file into flat file into oracle Data Base during this file some other files also created.

Logfile
Badfile
Discardfile

Q76) SQL%bulk_rowcount

Oracle introduced sql%bulk_rowcount attribute which is used to count affected number of rows within each group in bulk bind process. (forall statements).
Syntax:

sql%bulk_rowcount(index varname);

Q77) Authid current_user

When a procedure have a authid current_user clause then those procedures are allowed to execute only owner of the procedure.
These procedures are not allowed to executes by another users if any user givings permission also. Generally whenever we are reading data from table and performs some DML operations then only data security principles of view developers uses this clause in procedures.
This clause are used in procedures specification only.

Synatx:

Create or replace procedure procedurename(formal parameters)

Authid current_user
is/as
-----------
Begin
----------
[exeception]
------------
End[procedurename];

Q78) What is Row-Level-Attribute?

In this method a single variables can represent all different datatype into single unit. This variable is also called as record type variable.

Ro Level Attribute are represented by using %rowtype.

Syntax:
variable_name table_name%rowtype;

Q79) What are types of Blocks in PL/SQL?

PL/SQL having 2 types of blocks

1. Anonymous Block
2. Nammed Block

Anonymous Block	Nammed Block
This block doesnot have a name	This block having a name
These blocks are not stored in oracle database	These blocks are automatically permanently stored in Database
Thess blocks are not allowed to call in client application	These blocks are allowed to call in client application

Q80) Write a PL/SQL cursor program which is used to display total salary from emp table without using sum() function by using cursor for loop?

sql> declare
cursor c1 is select*from emp
n number(10):=0;
begin
for i in c1
loop
n:=n+i.sal;
end loop;
dbms_output.put_line (‘total salary is: ‘||’ ‘||n);
end;
/
Output: total salary is: 42075

Q81) What is Normalization?

Normalization is a scientific process which is called to decomposing a table into number of tables. This process automatically reduces duplicate data and also automatically avoids insertion, updation, deletion problems.

In design phase of SDLC database designers designs LOGICAL MODEL of the database in this logical model only database designers uses normalization process by using normal forms.

Q82) What is Super Key?

A columns or a combination of columns which uniquely identifying a record in a table is called a Super Key.

Q83) What is Candidate Key?

A minimal super key uniquely identifying a record a table is called candidate key

(or)

A super key which is a subset of another super key then those super keys are not a candidate key.

Q84) What is Bad File?

This file extension is .bad
Bad file stores rejected records based on

Data type mismatch
Business rule violation

Bad file is automatically created as same name as Flat file, we can also create Bad file explicitally by using bad file clause within control file.

Q85) What is Discard File?

This file extension is .dsc
Discards file we must specify within control file by using discard file clause.
Discard file also stores rejected record based on when clause condition within control file. This condition must be satisfied into table table_name clause.

Q86) What is Autoincrement?

In all databases generating primary key value automatically is called auto increment concept. In Oracle we are implementing auto increment concept by using row level triggers, sequences. i.e here we creating sequence in sql and use this sequence in PL/SQL row level trigger.

SQL> create table test (sno number(10), primary key, name varchar2(10));

Q87) What is Dynamic SQL?

It is the combination of SQL, PL/SQL i.e SQL statements are executed dynamically with PL/SQL block using execute immediate clause.

Generally in PL/SQL block we are not allow to use DDL, DCL statements using Dynamic SQL DDL, DCL statement within PL/SQL block.

Syntax:

begin
execute immediate ‘sql statement’
end;
/

Q88) What are the Different SQL Servers Versions avialable in the market?

SQL Servers Versions
SQL Server	Code Name
SQL Server 2017	vNext
SQL Server 2016	Helsinki
SQL Server 2014	Hekaton
SQL Server 2012	Denali
SQL Server 2008 R2	Kilimanjaro
SQL Server 2008	Katmai
SQL Server 2005	Yukon
SQL Server 2000	Shiloh
SQL Server 7.0	Sphinx

Q89) Write a dynamic SQL program to display number of records from emp table?

SQL> declare
z number(10);
begin
execute immediate ‘select count * FROM emp’
into z;
dbms_output.put_line(z);
end;
/

Q90. Write a dynamic SQL program for passing department number 20 retrieve deptnames, Loc from dept table?

SQL> declare
v_deptno number(10):=20;
v_dname varchar2(10);
v_loc varchar2(10);
begin
execute immediate ‘select dname, loc FROM dept where deptno=1’ into v_dname, v_loc using v_deptno;
dbms_output.put_line(v_dname ||’ ‘|| v_loc);
end;
/

https://www.softwaretestingmaterial.com/sql-interview-questions/

https://www.geeksforgeeks.org/sql-interview-questions-set-2/

https://www.toptal.com/sql/interview-questions

https://www.geeksforgeeks.org/sql-interview-questions-set-1/

https://docs.oracle.com/cd/E17952_01/mysql-5.0-en/date-and-time-functions.html