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.