SQL

Question 1

WHAT IS THE DIFFERENCE BETWEEN DBMS AND RDBMS?

Answer 1

1. DBMS stores data as file. RDBMS stores data in TABULAR form.
2. No relationship between
   data.
   Data is stored in the form of tables
   which are RELATED to each other.
   Eg: Foreign key relationship.
3. Normalization is not present. NORMALIZATION is present.
4. It deals with small quantity of
   data. It deals with LARGE amount of data.
5. Examples: XML Examples: MySQL, PostgreSQL, SQL
   Server, Oracle etc

Question 2

WHAT IS A CONSTRAINT IN SQL? WHAT ARE ITS TYPES.

Answer 2

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table. Constraints can be divided into the following two types, Column level constraints: Limits only column data. Table level constraints: Limits whole table data.

Types of constraints:

Not Null Constraint.
Check Constraint.
Default Constraint.
Unique Constraint.
Primary Constraint.
Foreign Constraint.

Question 3

WHAT IS THE DIFFERENCE BETWEEN PRIMARY KEY AND UNIQUE KEY?

Answer 3

Basic:
The primary key is accepted as a unique or sole identifier for every record in the table.
When the primary key is not present in the table, then the unique key is also used as a unique identifier for records

NULL:
In the case of a primary key, we cannot save NULL values.
In the case of a unique key, we can save a null value, however, only one NULL value is supported.

Purpose:
PK supports entity integrity. UK supports unique data.

Index:
The primary key tends to generate a clustered index by default. The unique key tends to generate a non-clustered index.

Number of Key:
Each table holds just one primary key. A table can hold more than one unique key.

Value Modification:
With the primary key, we cannot modify or delete the values.
With the unique key, we can modify the column values.

Uses:
PK is used to recognize specific records in the table. UK anticipates storing duplicate entries in a column except for a NULL value.

Pk use to distinguish between two records if they are different records.

UK use to distinguish that the values in a column are distinguish.

Question 4

WHAT ARE TRIGGERS AND TYPES OF TRIGGERS

Answer 4

Triggers are database object. Basically, these are a special type of stored procedure that is automatically fired/executed when a DDL or DML command statement related to the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

Types of Triggers:

In SQL Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers, and Logon triggers.

Question 5

WHAT IS A VIEW?

Answer 5

A view is a VIRTUAL table which consists of a subset of data contained in a table
or more than one table.

Views are not stored in memory like tables then why to use views?

Because of 2 reasons:
1. Views can be easily Indexed to improve the performance.
2. Extra security – DBA can hide the actual table names and expose views for
Read only operations.
Remember, in a view query is stored but the data is never stored like a table.

Question 6

WHAT IS THE DIFFERENCE BETWEEN HAVING CLAUSE AND WHERE
CLAUSE?

Answer 6

1. WHERE Clause is used before GROUP BY Clause.
   HAVING Clause is used after GROUP BY Clause.
2. WHERE Clause cannot contain AGGREGATE function.
   HAVING Clause can contain aggregate function

SELECT department_id,
SUM(salary) AS total_sal
FROM employee
WHERE hire_date > ‘01-JAN-2013’
GROUP BY department_id
HAVING SUM(salary) > 1000000
ORDER BY department_id;

Question 7

WHAT IS SUB QUERY OR NESTED QUERY OR INNER QUERY IN SQL?

Answer 7

A Subquery or Inner query or a Nested query is a query within another SQL query
and embedded within the WHERE clause.

SELECT name, listed_price
FROM paintings
WHERE listed_price > (
SELECT AVG(listed_price)
FROM paintings
);

Question 8

WHAT IS AUTO INCREMENT/ IDENTITY COLUMN IN SQL SERVER?

Answer 8

Auto-increment allows a unique number to be generated automatically when a
new record is inserted into a table.
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(255),
Age int
)

Question 9

WHAT ARE JOINS IN SQL?

Answer 9

A join clause is used to COMBINE rows from two or more tables, based on a
related column between them

Question 10

WHAT ARE THE TYPES OF JOINS IN SQL SERVER?

Answer 10

LEFT OUTER JOIN: As you can see in the diagram – It returns all records from
the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Returns all records from the right table, and the
matched records from the left table.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table.
(INNER) JOIN: Returns records that have matching values in both tables. Inner
join is the mostly used join in all joins

Question 11

What is self join?

Answer 11

In SQL Server, the self-join is like a regular join, but it joins a table to itself. Similar to any other join, a self-join requires at least two tables. But instead of adding a different table to the join, you add another instance of the same table. It is a join between two copies of the same table. Self-join is mainly used for querying the hierarchical data stored in a single table.

There is no Self Join keyword. You write a normal join where both the tables involved in the join are the same.

The following is the syntax of the self-join query.

Syntax: Self-join

SELECT a.column1, b.column2
FROM table1 a, table1 b
WHERE condition;
In the self-join, you have to specify the table alias to both the instance of a table; else you will get an error.

Question 12

WHAT ARE INDEXES IN SQL SERVER?

Answer 12

SQL Indexes are used in relational databases to retrieve data VERY FAST.
They are like indexes at the start of the BOOKS, which purpose is to find a topic
quickly

Question 13

WHAT IS CLUSTERED INDEX?

Answer 13

A clustered index defines the order in which data is physically stored in a table.
Clustered index are similar to the Dictionary.
There is only one way to find a word so similarly there can be only one clustered
index per table possible.

Question 14

NON-CLUSTERED INDEX

Answer 14

A non-clustered index is stored at one place and table data is stored in another
place. So, this index is not physically stored.
It is like the index of a BOOK.
A book can have multiple indexes like one at the start and one at the end

Similarly, a table can have multiple non-clustered indexes in a table.

Question 15

WHAT IS THE DIFFERENCE BETWEEN CLUSTERED AND NON-CLUSTERED
INDEX?

Answer 15

1. A clustered index defines the order in which data is physically stored in a table.
   For example, Dictionary.
   A non-clustered index is stored at one place and table data is stored in another
   place.
   For example, Book Index.
2. A table can have only one clustered index.
   A table can have multiple non-clustered index.
3. Clustered index is faster.
   Non-clustered index is slower

Question 16

WHAT IS THE DIFFERENCE BETWEEN STORED PROCEDURE AND FUNCTIONS?

Answer 16

1. SP may or may not return a value, but function must return a value.
2. SP can have input/output parameters, but function only has input parameters.
3. We can call function inside SP, but cannot call SP from a function.
4. We cannot use SP in SQL statements like SELECT, INSERT, UPDATE, DELETE,
   MERGE, etc, but we can use them with function.
   SELECT *, dbo.fnCountry(city.long) FROM city;
5. We can use try-catch exception handling in SP, but we cannot do that
   in function.
6. We can use transactions inside SP, but it is not possible in function.

–Stored Procedure

CREATE PROCEDURE proc_name
(@Ename varchar(50),
@EId int output)
AS
BEGIN
INSERT INTO Employee (EmpName) VALUES (@En
ame)
SELECT @EId= SCOPE_IDENTITY()
END

–UDF – User Defined Functions

CREATE FUNCTION function_name
(parameters) –only input parameter
RETURNS data_type AS
BEGIN
SQL statements
RETURN value
END;

Question 17

WHAT IS THE DIFFERENCE BETWEEN DELETE, TRUNCATE AND DROP
COMMANDS?

Answer 17

To remove specific rows, use DELETE.
To remove all rows from a large table and leave the table structure, use TRUNCATE TABLE. It’s faster than DELETE.
To remove an entire table, including its structure and data, use DROP TABLE.

Question 18

WHAT ARE ACID PROPERTIES?

Answer 18

ACID properties are used when you are handling transactions in SQL.

Atomicity: The atomicity property. It means either all the operations (insert, update, delete) inside a transaction take place or none. Or you can say, all the statements (insert, update, delete) inside a transaction are either completed or rolled back.

Consistency: This ensures database consistency. It means that whatever happens in the middle of the transaction, this will never leave your database in a half-completed state.
If the transaction is completed successfully, then it will apply all the changes to the database.
If there is an error in a transaction, then all the changes that have already been made will be rolled back automatically. It means the database will restore to its state before the transaction starts.
If there is a system failure in the middle of the transaction, all the changes already made will automatically roll back.

Isolation: Every transaction is individual, and One transaction can’t access the result of other transactions until the transaction completes. Or, you can’t perform the same operation using multiple transactions at the same time.

Durability: Once the transaction is completed, then the changes it has made to the database will be permanent. Even if there is a system failure or any abnormal changes also, this property will safeguard the committed data.

Question 19

WHAT ARE ALL THE DIFFERENT FORMS OF NORMALIZATION?

Answer 19

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 (4NF):.
Meeting all the requirements of third normal form and it should not have multivalued dependencies.

Question 20

WHAT ARE RELATIONSHIPS? WHAT ARE ITS TYPES?

Answer 20

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

Question 21

WHAT IS DATA INTEGRITY?

Answer 21

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

Question 22

HOW TO FETCH ALTERNATE RECORDS FROM A TABLE?

Answer 22

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

Question 23

.HOW TO SELECT UNIQUE RECORDS FROM A TABLE?

Answer 23

Select unique records from a table by using DISTINCT keyword.

Select DISTINCT StudentID, StudentName from Student.

Question 24

WHAT IS THE COMMAND USED TO FETCH FIRST 5 CHARACTERS OF THE
STRING?

Answer 24

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

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

Select LEFT(Studentname,5) as studentname from student

Question 25

WHAT ARE AGGREGATE AND SCALAR FUNCTIONS?

Answer 25

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.

Question 26

WHICH OPERATOR IS USED IN QUERY FOR PATTERN MATCHING?

Answer 26

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_’

Question 27

What do you mean by Magic Tables in SQL server?

Answer 27

Magic tables are virtual tables that exist in two types – INSERTED AND DELETED. They hold the information of the newly INSERTED and DELETED rows. The INSERTED table will have the newly inserted rows on top of it. The DELETED tables will have the recently deleted rows on top of it on similar tracks. Magic tables are stored in tempDB.

Question 28

How to improve query performance in SQL server?
Ans:

Answer 28

We can improve the query performance in the following ways:

Defining the transaction requirements precisely
Choosing the right query execution plan
Reducing table sizes and simplifying joins
Using ‘SELECT fields FROM’ instead of ‘SELECT*FROM’
Using EXIST() instead of COUNT()
Creating SQL query indexes
Avoiding running queries in loops

Question 29

What is the difference between ORDER BY expression and clustered index?

Answer 29

ORDER BY expression sorts the result set of a query as per the condition given in the statement. It can sort the result set either in ascending order or descending order. And, it can sort the result set based on attributes given in the statement such as country, company, etc. At the same time, clustered index sorts data physically only in a specific way in the table. So, every table will have one clustered index only.

Question 30

What is SQL Injection?

Answer 30

Sql injection is a most common web hacking technique used to destroy the database via by inserting logical code as html input. But we can avoid by using source code, we shouldn’t use the input directly. Then we have to initialize all the input. Eg using Entity Framework Core and ORM mapping.
-Prasanna