Monday, June 15, 2020

SQL Interview Questions & Answers

Question 1. Explain What Is A Database?

Answer : A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views and other database objects.

Question 2. Explain What Is Dbms?

Answer : Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.

Question 3. Explain What Is Rdbms?

Answer : RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed using Structured Query Language (SQL)

Question 4. What Are The Popular Database Management Systems In The It Industry?

Answer : Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,

Question 5. Explain What Is Sql?

Answer : SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP’s), execute queries, retrieve, insert, update, delete data against a database.

Question 6. Explain What Is Table In A Database?

Answer : A table is a database object used to store records in a field in the form of columns and rows that holds data.

Question 7. Explain What Is A Field In A Database And Record In A Database?

Answer : A field in a Database table is a space allocated to store a particular record within a table.

A record (also called a row of data) is an ordered set of related data in a table.

Question 8. What Is The Use Of Nvl Function?

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

Question 9. Explain What Is A Column In A Table?

Answer : A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Question 10. What Are The Different Types Of Sql Commands?

Answer : SQL commands are segregated into following types:

DDL – Data Definition Language

DML – Data Manipulation Language

DQL – Data Query Language

DCL – Data Control Language

TCL – Transaction Control Language

Question 11. What Are The Different Ddl Commands In Sql?

Answer : DDL commands are used to define or alter the structure of the database.

CREATE: To create databases and database objects

ALTER: To alter existing database objects

DROP: To drop databases and databases objects

TRUNCATE: To remove all records from a table but not its database structure

RENAME: To rename database objects

Question 12. What Are The Different Dml Commands In Sql?

Answer : DML commands are used for managing data present in the database.

SELECT: To select specific data from a database

INSERT: To insert new records into a table

UPDATE: To update existing records

DELETE: To delete existing records from a table

Question 13. What Are The Different Dcl Commands In Sql?

Answer : DCL commands are used to create roles, grant permission and control access to the database objects.

GRANT: To provide user access

DENY: To deny permissions to users

REVOKE: To remove user access

Question 14. What Are The Different Tcl Commands In Sql?

Answer : TCL commands are used to manage the changes made by DML statements.

COMMIT: To write and store the changes to the database

ROLLBACK: To restore the database since the last commit

Question 15. Explain What Is An Index?

Answer : An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.

Question 16. Explain What Is A View?

Answer : A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.

Question 17. Explain What Is A Subquery ?

Answer : A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.

Question 18. What Is The Difference Between Rename And Alias?

Answer : ‘Rename’ is a permanent name given to a table or column

‘Alias’ is a temporary name given to a table or column.

Question 19. What Is A Join?

Answer : Join is a query, which retrieves related columns or rows from multiple tables.

Question 20. What Are The Different Types Of Joins?

Answer : Types of Joins are as follows:

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_name 1=column_name 2;

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_name 1=column_name 2;

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;

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;

Question 21. What Are Sql Constraints?

Answer : SQL constraints are the set of rules that enforced some restriction while inserting, deleting or updating of data in the databases.

Question 22. What Are The Constraints Available In Sql?

Answer : Some of the constraints in SQL are : Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.

Question 23. What Is A Unique Key And Primary Key And Foreign Key?

Answer :

A UNIQUE KEY constraint is used to ensure that there are no duplication values in the field/column.

A PRIMARY KEY constraint uniquely identifies each record in a database table. All columns participating in a primary key constraint must not contain NULL values.

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table is linked with the PRIMARY KEY of another table.

Question 24. What Is The Difference Between Unique And Primary Key Constraints?

Answer : There should be only one PRIMARY KEY in a table whereas there can be any number of UNIQUE Keys.

PRIMARY KEY doesn’t allow NULL values whereas Unique key allows NULL values.

Question 25. What Is A Null Value?

Answer : A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.

Question 26. What Is Normalization?

Answer : Normalization is the process of table design to minimize the data redundancy.

There are different types of Noramalization forms in SQL:-

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 27. What Is Stored Procedure?

Answer : A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).

Question 28. What Is A Trigger?

Answer : A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee Payroll, Employee Time Sheet etc.,

Question 29. List Out The Acid Properties And Explain?

Answer : Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.

·       Atomicity

·       Consistency

·       Isolation

·       Durability

Question 30. What Is The Difference Between Delete, Truncate And Drop Command?

Answer : The difference between the Delete, Truncate and Drop command is:

Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.

Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back.

Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows). All the tables’ rows, indexes, and privileges will also be removed.

Question 31. What Is The Difference Between Having And Where Clause?

Answer : Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with ‘GROUP BY’ to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

Question 32. What Are Aggregate Functions In Sql?

Answer : SQL aggregate functions return a single value, calculated from values in a column.

Some of the aggregate functions in SQL are as follows:

AVG() : This function returns the average value

COUNT() : This function returns the number of rows

MAX() : This function returns the largest value

MIN() : This function returns the smallest value

ROUND() : This function rounds a numeric field to the number of decimals specified

SUM() : This function returns the sum

Question 33. What Are String Functions In Sql?

Answer : SQL string functions are used primarily for string manipulation.

Some of the widely used SQL string functions are:

LEN() : It returns the length of the value in a text field

LOWER() : It converts character data to lower case

UPPER() : It converts character data to upper case

SUBSTRING() : It extracts characters from a text field

LTRIM() : It is to remove all whitespace from the beginning of the string

RTRIM() : It is to remove all whitespace at the end of the string

CONCAT() : Concatenate function combines multiple character strings together

REPLACE() :To update the content of a string.

Question 34. Explain The Working Of Sql Privileges?

Answer : 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 35. How Many Types Of Privileges Are Available In Sql?

Answer : 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 36. What Is Sql Injection?

Answer : 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.

Question 37. What Is The Difference Between Clustered And Non-clustered Indexes?

Answer : 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 38. What Is Relationship? How Many Types Of Relationship Are There?

Answer : 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 39. What Is Collation?

Answer : 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 40. What Is Database White Box Testing And Black Box Testing?

Answer :

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

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 41. What Are The Advantages Of Views?

Answer : 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.

Question 42. What Is Schema?

Answer : A schema is a collection of database objects of a User.

Question 43. What Is The Difference Between Sql And Pl/sql?

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

Question 44. What Is The Difference Between Sql And Mysql?

Answer : 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 45. What Is Sql Sandbox In Sql Server?

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

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

2.     External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.

3.     Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.