Q #1) What is SQL?

Answer: Structured Query Language SQL is a database tool that is used to create and access the database to support software applications.

Q #2) What are tables in SQL?

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

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


statements supported by SQL

There are 3 types of SQL statements:

a) 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.

CREATE TABLE table_name
column_name1 data_type(size),
column_name2 data_type(size),
column_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

b) 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 a partial DML statement, used to select all or relevant records in the table.

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

SET versus SELECT when assigning variables?

  1. SET is the ANSI standard for variable assignment, SELECT is not.
  2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
  3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you’d likely never know why something was going wrong elsewhere – have fun troubleshooting that one)
  4. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from its previous value)
  5. As far as speed differences – there are no direct differences between SET and SELECT. However SELECT’s ability to make multiple assignments in one shot does give it a slight speed advantage over SET.
declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var is now NULL */

set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty'
select @var /* @var is still equal to 'Joe' */

What are Entities and Relationships?

Entity: An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity.

Relationships: Relations or links between entities that have something to do with each other. For example – The employees table in a company’s database can be associated with the salary table in the same database

Primary key VS Unique key

A unique key has a couple of purposes.

For me the most important is data integrity. Use a unique key whenever you want to ensure uniqueness for N number of columns in a table. Sometimes this is for identification of a canonical or business key. Sometimes this is for an additional column that has business requirements to also be unique in addition to a canonical key.

A unique key is usually a combination of a unique index and a unique constraint. A thing that all indexes are supposed to help with is retrieval performance. A unique index often is most ideal for this when it applies.

A unique key can also be used to establish foreign key constraints for the child record though this is most commonly done on the primary key.

One could argue that a primary key is a specific type of unique key. I prefer to separate them completely in my day to day language though depending on the platform the differences can be subtle at best.

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

Answer: The DISTINCT statement is used with the SELECT statement. If the record contains duplicate values then the DISTINCT statement is used to select different values among duplicate records.


SELECT DISTINCT column_name(s)FROM table_name;

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


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.


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.


SELECT column_name(s) FROM table_name GROUP BY column_name;

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


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

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


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 JOIN. It is used to check equality based on columns when tables are joined. It can be used instead of the ON clause in JOIN.



SELECT column_name(s) FROM table_name
JOIN table_name USING (column_name);
Why? Because USING performs an equality join and can only be used when the column names are identical, it’s unnecessary to include the column twice. A nice side-effect of this is convenience – when you’re using USING, if you want to refer to the column in your SELECT clause or elsewhere, you don’t need to prefix the column name with the table name!

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

Answer: 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 the CREATE TABLE statement or even after the table is created once with the ALTER TABLE statement.

There are 5 major constraints are used in SQL, such asUPN-FC

  • 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 a 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 column to identify the particular record with a unique identity.
  • FOREIGN KEY: It is used to ensure the referential integrity of data in the table. It matches the value in one table with another using the PRIMARY KEY.
  • CHECK: It ensures whether the value in columns fulfills the specified condition.

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


SQL Joins

4 major types of Joins are used 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 matching column.


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

For Example,

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

Employee table

The second table’s name is Joining.


Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id;

There will be 4 records selected. Results are:

result of innerjoin

Employee and Orders tables have a matching customer_id value.

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


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

For Example,

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

Employee table

The second table’s name is Joining.

joining 1

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id;

There will be 4 records selected. You will see the following results:


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


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

For Example,

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

Employee table

The second table’s name is Joining.

joining table

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id;



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


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

For Example,

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

Employee table

The second table’s name is Joining.

joining 1

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id;

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


Q #8) What are transactions and their controls?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations like Creating, updating, deleting records performed in the database come from transactions.
In simple words, 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. All changes made by the transaction are reverted back and the database remains as before.
  • SET TRANSACTION: Set the name of the transaction.
  • SAVEPOINT: It is used to set the point where the transaction is to be rolled back.

Q #9) What are the properties of the transaction?

Answer: Properties of the transaction are known as ACID properties. These are:

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

Q #10) How many Aggregate functions are available in SQL?

Answer: SQL Aggregate functions determine and calculate values from multiple columns in a table and return a single value.

There are 7 aggregate functions in SQL:

  • AVG(): Returns the average value from specified columns.
  • COUNT(): Returns number of table rows.
  • MAX(): Returns the 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.

Q #11) What are Scalar functions in SQL?

Answer: 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 the upper case.
  • LCASE(): Converts the specified field in lower case.
  • MID(): Extracts and returns character from the text field.
  • FORMAT(): Specifies the display format.
  • LEN(): Specifies the length of the text field.
  • ROUND(): Rounds up the decimal field value to a number.

Q #12) What are triggers?

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


(event [OR..]}ON
PROCEDURE functionname {arguments}

Explanation of syntax:
create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
[before | after]: This specifies when the trigger will be executed.
{insert | update | delete}: This specifies the DML operation.
on [table_name]: This specifies the name of the table associated with the trigger.
[for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
[trigger_body]: This provides the operation to be performed as trigger is fired
BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is ru

Q #13) What is View in SQL?

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


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

Q #14) How we can update the view?

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

Execute the below query to update the created view.


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

Q #15) 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 objects by using commands like SELECT, INSERT, UPDATE, DELETE, ALL, etc.

GRANT Command: This command is used to provide database access to users other than the administrator.


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

In the above syntax, the GRANT option indicates that the user can grant access to another user too.

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


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

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

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

  • System privilege: System privilege deals with the object of a particular type and provides users the right to perform one or more actions on it. These actions include performing 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.

Q #17) 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 in a way that once it is executed, the database is exposed to an attacker for the attack. This technique is usually used for attacking data-driven applications to have access to sensitive data and perform administrative tasks on databases.

For Example,


What’s the worst thing that could happen when you suffer a SQL injection attack?

Our example hack showed you how to bypass the login page: a huge security flaw for a banking site. More complex attacks will allow an attacker to run arbitrary statements on the database. In the past, hackers have used injection attacks to:

  • Extract sensitive information, like Social Security numbers, or credit card details.
  • Enumerate the authentication details of users registered on a website, so these logins can be used in attacks on other sites.
  • Delete data or drop tables, corrupting the database, and making the website unusable.
  • Inject further malicious code to be executed when users visit the site.

So SQL Injection is a serious risk. How can you protect yourself?

Parameterized Statements

Programming languages talk to SQL databases using database drivers. A driver allows an application to construct and run SQL statements against a database, extracting and manipulating data as needed. Parameterized statements make sure that the parameters (i.e. inputs) passed into SQL statements are treated in a safe manner.

For example, a secure way of running a SQL query in JDBC using a parameterized statement would be:

// Define which user we want to find.
String email = "user@email.com";

// Connect to the database.
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();

// Construct the SQL statement we want to run, specifying the parameter.
String sql = "SELECT * FROM users WHERE email = ?";

// Run the query, passing the 'email' parameter value...
ResultSet results = stmt.executeQuery(sql, email);

while (results.next()) {
  // ...do something with the data returned.

Contrast this to explicit construction of the SQL string, which is very, very dangerous:

// The user we want to find.
String email = "user@email.com";

// Connect to the database.
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();

// Bad, bad news! Don't construct the query with string concatenation.
String sql = "SELECT * FROM users WHERE email = '" + email + "'";

// I have a bad feeling about this...
ResultSet results = stmt.executeQuery(sql);

while (results.next()) {
  // ...oh look, we got hacked.

The key difference is the data being passed to the executeQuery(...) method. In the first case, the parameterized string and the parameters are passed to the database separately, which allows the driver to correctly interpret them. In the second case, the full SQL statement is constructed before the driver is invoked, meaning we are vulnerable to maliciously crafted parameters.

You should always use parameterized statements where available, they are your number one protection against SQL injection.

You can see more examples of parameterized statements in various languages in the code samples below.

Object Relational Mapping

Many development teams prefer to use Object Relational Mapping (ORM) frameworks to make the translation of SQL result sets into code objects more seamless. ORM tools often mean developers will rarely have to write SQL statements in their code – and these tools thankfully use parameterized statements under the hood.

The most well-known ORM is probably Ruby on Rails’ Active Record framework. Fetching data from the database using Active Record looks like this:

def current_user(email)
  # The 'User' object is an Active Record object, that has find methods 
  # auto-magically generated by Rails.

Code like this is safe from SQL Injection attacks.

Using an ORM does not automatically make you immune to SQL injection, however. Many ORM frameworks allow you to construct SQL statements, or fragments of SQL statements, when more complex operations need to be performed on the database. For example, the following Ruby code is vulnerable to injection attacks:

def current_user(email)
  # This code would be vulnerable to a maliciously crafted email parameter.
  User.where("email = '" + email + "'")

As a general rule of thumb: if you find yourself writing SQL statements by concatenating strings, think very carefully about what you are doing.

Escaping Inputs

If you are unable to use parameterized statements or a library that writes SQL for you, the next best approach is to ensure proper escaping of special string characters in input parameters.

Injection attacks often rely on the attacker being able to craft an input that will prematurely close the argument string in which they appear in the SQL statement. (This is why you will often see ' or " characters in attempted SQL injection attacks.)

Programming languages have standard ways to describe strings containing quotes within them – SQL is no different in this respect. Typically, doubling up the quote character – replacing ' with '' – means “treat this quote as part of the string, not the end of the string”.

Escaping symbol characters is a simple way to protect against most SQL injection attacks, and many languages have standard functions to achieve this. There are a couple of drawbacks to this approach, however:

  • You need to be very careful to escape characters everywhere in your codebase where an SQL statement is constructed.
  • Not all injection attacks rely on abuse of quote characters. For example, when an numeric ID is expected in a SQL statement, quote characters are not required. The following code is still vulnerable to injection attacks, no matter how much you play around with quote characters:

def current_user(id)
  User.where("id = " + id)

Sanitizing Inputs

Sanitizing inputs is a good practice for all applications. In our example hack, the user supplied a password as ' or 1=1--, which looks pretty suspicious as a password choice.

Developers should always make an effort to reject inputs that look suspicious out of hand, while taking care not to accidentally punish legitimate users. For instance, your application may clean parameters supplied in GET and POST requests in the following ways:

  • Check that supplied fields like email addresses match a regular expression.
  • Ensure that numeric or alphanumeric fields do not contain symbol characters.
  • Reject (or strip) out whitespace and new line characters where they are not appropriate.

Client-side validation (i.e. in JavaScript) is useful for giving the user immediate feedback when filling out a form, but is no defense against a serious hacker. Most hack attempts are performed using scripts, rather than the browser itself.

// Create the SQL command.
SqlCommand command = new SqlCommand("select * from Users where email = @email", conn);

// Add the parameter values in separately.
command.Parameters.Add(new SqlParameter("email", email);

using (SqlDataReader reader = command.ExecuteReader())
  while (reader.Read())
    // Do something with the retrieved data.


using (ServiceContext ctx = new ServiceContext(...))
  // LINQ will ensure safe passing of parameters.
  var users = from user in ctx.Users
             where user.email equals email
            select user;

  foreach (var user in users)
    // Do something with the retrieved data
Principle of Least Privilege

Applications should ensure that each process or software component can access and affect only the resources it needs. Apply “levels of clearance” as appropriate, in the same way that only certain bank employees have access to the vault. Applying restricted privileges can help mitigate a lot of the risk around injection attacks.

It is rarely necessary for applications to change the structure of the database at run-time – typically tables are created, dropped, and modified during release windows, with temporarily elevated permissions. Therefore, it is good practice to reduce the permissions of the application at runtime, so it can at most edit data, but not change table structures. In a SQL database, this means making sure your production accounts can only execute DML statements, not DDL statements.

With complex database designs, it can be worth making these permissions even more fine-grained. Many processes can be permissioned to perform data edits only through stored procedures, or to execute with read-only permissions.

Sensibly designing access management in this way can provide a vital second line of defense. No matter how the attacker gets access to your system, it can mitigate the type of damage they can possibly do.

Password Hashing

Our example hack relied on the fact that the password was stored as plain-text in the database. In fact, storing unencrypted passwords is a major security flaw in itself. Applications should store user passwords as strong, one-way hashes, preferably salted. This mitigates the risk of malicious users stealing credentials, or impersonating other users.

Third Party Authentication

As a final note, it is often worth considering out-sourcing the authentication workflow of your application entirely. Facebook, Twitter, and Google all provide mature OAuth APIs, which can be used to let users log into your website using their existing accounts on those systems. This saves you as an application developer from rolling your own authentication, and assures your users that their passwords are only stored in a single location.

Steps to prevent SQL injection attacks

The good news is that there actually are a lot of things that website owners can do to prevent SQL injection. Although there is no such thing as a foolproof solution in network security, formidable obstacles can be placed in the path of SQL injection attempts.

Here are ten steps you can take to significantly reduce the risk of falling victim to a SQL injection attack:

  1. Trust no one: Assume all user-submitted data is evil so use input validation via a function such as MySQL’s mysql_real_escape_string() to ensure that any dangerous characters such as ‘ are not passed to a SQL query in data. You should also sanitize everything by filtering user data by context. For example, email addresses should be filtered to allow only the characters allowed in an e-mail address, phone numbers should be filtered to allow only the digits allowed in a phone number, and so on.
  2. Don’t use dynamic SQL – don’t construct queries with user input: Even data sanitization routines can be flawed, so use prepared statements, parameterized queries or stored procedures instead whenever possible. But don’t forget that while stored procedures prevent some types of SQL injection attacks, they fail to protect against many others, so don’t rely exclusively on their use for your security.
  3. Update and patch: Vulnerabilities in applications and databases that hackers can exploit using SQL injection are regularly discovered, so it’s vital to apply patches and updates as soon as practical. A patch management solution might be worth the investment.
  4. Firewall: Consider a web application firewall (WAF) – either software or appliance-based – to help filter out malicious data. Good ones will have a comprehensive set of default rules, and make it easy to add new ones whenever necessary. A WAF can be particularly useful to provide some security protection against a new vulnerability before a patch is available. A popular example is the free, open source module ModSecurity, which is available for Apache, Microsoft IIS, and nginx web servers. ModSecurity provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.
  5. Reduce your attack surface: Get rid of any database functionality that you don’t need to prevent a hacker taking advantage of it. For example, the xp_cmdshell extended stored procedure in MS SQL spawns a Windows command shell and passes in a string for execution, which could be very useful indeed for a hacker. The Windows process spawned by xp_cmdshell has the same security privileges as the SQL Server service account.
  6. Use appropriate privileges: Don’t connect to your database using an account with admin-level privileges unless there is some compelling reason to do so. Using a limited access account is far safer, and can limit what a hacker is able to do. For example, the code behind a login page should query the database using an account limited only to the relevant credentials table. This way, a breach through this channel cannot be leveraged to compromise the entire database.
  7. Keep your secrets secret: Assume that your application is not secure and act accordingly by encrypting or hashing passwords and other confidential data, including connection strings.
  8. Don’t divulge more information than you need to: Hackers can learn a great deal about database architecture from error messages, so ensure that they display minimal information. Use the “RemoteOnly” customErrors mode (or equivalent) to display verbose error messages on the local machine while ensuring that an external hacker gets nothing more than the fact that his or her actions resulted in an unhandled error.
  9. Continuously monitor SQL statements from database-connected applications: This will help identify rogue SQL statements and vulnerabilities. Monitoring tools that utilize machine learning and/or behavioral analysis can be especially useful.
  10. Buy better software: Make code writers responsible for checking the code and for fixing security flaws in custom applications before the software is delivered.  SANS suggests you incorporate terms from this sample contract into your agreement with any software vendor.

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

Answer: SQL Sandbox is a safe place in the SQL server environment where untrusted scripts are executed. There are 3 types of SQL sandbox:

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

Q #19) 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.

Q #20) 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.

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

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

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server. For example, if we have the following table,

Table Sales_Data

Store A300
Store C150

The following SQL,SELECT SUM (NVL(Sales,100)) FROM Sales_Data;

would generate result below:

SUM (NVL(Sales,100))

This is because NULL has been replaced by 100 via the NVL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

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

Answer: The output of Cross Join is called 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.

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement.

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Q #23) What do you mean by Subquery?

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

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

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

A subquery is a SQL query nested inside a larger query.

  • A subquery may occur in :
    • – A SELECT clause
    • – A FROM clause
    • – A WHERE clause
  • The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
  • A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
  • You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
  • A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
  • The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.

You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to perform the following tasks:

  • Compare an expression to the result of the query.
  • Determine if an expression is included in the results of the query.
  • Check whether the query selects any rows.

Syntax :

sql subquery syntax
  • The subquery (inner query) executes once before the main query (outer query) executes.
  • The main query (outer query) use the subquery result.

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

Answer: The differences between the two are as follows:

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

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

Answer: The differences are:

  • The basic difference in both is DELETE command is DML command and the TRUNCATE command is DDL.
  • DELETE command is used to delete a specific row from the table whereas the TRUNCATE command is used to remove all rows from the table.
  • We can use the DELETE command with WHERE clause but cannot use the TRUNCATE command with it.

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

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

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

Answer: Query:

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

Here ‘like’ operator is used to perform pattern matching.

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

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

SELECT admin id(SELEC Firstname+' '+Lastname  FROM Employee WHEREempid=emp. adminid)AS EmpAdminId FROM Employee;

The result of the query is the details of an employee from the Employee table.

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

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

There are forms of Normalization:

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

Q #31) What is a Relationship? How many types of Relationships are there?

Answer: The relationship can be defined as the connection between more than one table 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

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

Answer: A stored procedure is a collection of SQL statements that can be used as a function to access the database. We can create these stored procedures earlier before using it and can execute them wherever required by applying some conditional logic to it. Stored procedures are also used to reduce network traffic and improve performance.


CREATE Procedure Procedure_Name(//Parameters)
SQL statements in stored procedures to update/retrieve records

Q #33) State some properties of Relational databases?

Answer: Properties are as follows:

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

Q #34) What are Nested Triggers?

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

Q #35) What is a Cursor?

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

SQL works based on set e.g., SELECT statement returns a set of rows which is called a result set. However, sometimes, you may want to process a data set on a row by row basis. This is where cursors come into play.
->A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

SQL Server Cursor

Cursor follows steps as given below:

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

Q #36) What is Collation?

Answer: Collation is a 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.

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

Answer: In Database testing, the following thing is required to be tested:

  • Database connectivity
  • Constraint check
  • Required application field and its size
  • Data Retrieval and processing with DML operations
  • Stored Procedures
  • Functional flow

Q #38) What is Database White 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

Q #39) What is Database Black Box Testing?

Answer: Database Black Box testing involves:

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

Q #40) What are Indexes in SQL?

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


CREATE INDEX index_name
ON table_name (column_name)

Further, we can also create a Unique Index using the following syntax:

ON table_name (column_name)

Q #45) How do you add a column to a table?

Answer: To add another column in the table, use the following command:

ALTER TABLE table_name ADD (column_name);

Q #46) Define the SQL DELETE statement.

Answer: 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 #50) What is CHECK Constraint?

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

Answer: Yes, a table can have many foreign keys but only one primary key.

Q #52) What are the possible values for the BOOLEAN data field?

Answer: For a BOOLEAN data field, two values are possible: -1(true) and 0(false).

Q #54) What is identity in SQL?

Answer: An identity column in where SQL automatically generates numeric values. We can define a start and increment value of the identity column.

Q #57) How to select random rows from a table?

Answer: Using a SAMPLE clause we can select random rows.

For Example,

SELECT * FROM table_name SAMPLE(10);

Q #58) Which TCP/IP port does SQL Server run?

Answer: By default SQL Server runs on port 1433.

SELECT DISTINCT name FROM table_name;

Q #61) Can we rename a column in the output of the SQL query?

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

Answer: Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clauses are mandatory.

Q #67) What do you mean by ROWID?

Answer: It’s an 18 character long pseudo column attached with each row of a table.


  • 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 #71) What is a composite key?

Answer: The unique key created on more than one column is called composite key.

Q #74) What do you mean by query optimization?

Answer: Query optimization is a process in which a database system compares different query strategies and select the query with the least cost.

Q #76) What is Referential Integrity?

Answer: 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 the Case function?

Answer: Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of the multiple possible result expressions.

Q #78) Define a temp table?

Answer: A temp table is a temporary storage structure to store the data temporarily.

Q #79) How can we avoid duplicating records in a query?

Answer: By using the DISTINCT keyword, duplication of records in a query can be avoided.

Q #80) Explain the difference between Rename and Alias?

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

Answer: Advantages of Views are:

  • 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?


Q #84) What is schema?

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

A database schema represents the logical configuration of all or part of a relational database. It can exist both as a visual representation and as a set of formulas known as integrity constraints that govern a database. These formulas are expressed in a data definition language, such as SQL. As part of a data dictionary, a database schema indicates how the entities that make up the database relate to one another, including tables, views, stored procedures, and more.

logical and physical database schema

Typically, a database designer creates a database schema to help programmers whose software will interact with the database. The process of creating a database schema is called data modeling. When following the three-schema approach to database design, this step would follow the creation of a conceptual schema. Conceptual schemas focus on an organization’s informational needs rather than the structure of a database.

There are two main kinds of database schema:

  1. A logical database schema conveys the logical constraints that apply to the stored data. It may define integrity constraints, views, and tables.
  2. A physical database schema lays out how data is stored physically on a storage system in terms of files and indices.

At the most basic level, a database schema indicates which tables or relations make up the database, as well as the fields included on each table. Thus, the terms schema diagram and entity-relationship diagram are often interchangeable.

Q #86) Does View contain Data?

Answer: No, Views are virtual structures.

Q #87) Can a View based on another View?

Answer: Yes, A View is based on another View.

Q #88) What is the difference between the HAVING clause and WHERE clause?

Answer: 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 behaved like WHERE clause only.

Q #89) What is the difference between Local and Global temporary tables?

Answer: If defined 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?

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

When Should Indexes Be Avoided?

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

Although indexes are intended to enhance a database’s performance, there are times when they should be avoided.

The following guidelines indicate when the use of an index should be reconsidered.

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch updates or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.

What do you understand by SQL Server Agent?

SQL Server Agent is a Windows service which is used to schedule and execute jobs. Here, each job contains one or more step, and each step contains a task. So, the Server Agent uses the SQL Server to store job information and run a job on a schedule.

The main components of the SQL Server Agent are Jobs, Schedules, Operators, and Alerts.


If an enterprise wishes to take a backup of the company servers at 9:00 pm on every Friday, then you can very well automate this task to let the schedule happen on its own. In a scenario, the backup encounters an error, the SQL Server Agent records the event and notifies the corresponding team.

How can you check the version of SQL Server?

To check the version of SQL Server, you can use the following command:

1SELECT @@version

The @@VERSION gives output as one nvarchar string. 

What is SQL Server Profiler?

The Microsoft SQL Server Profiler is an interface used to create and manage traces. It also analyzes and replays the trace results. Here, events are saved in a trace file which are later analyzed or used to replay a specific series of steps while debugging an issue.

You can use SQL Server Profiler for activities such as:

  1. Finding the root cause of the problem
  2. Monitoring the performance of SQL Server to handle the workloads.
  3. Diagnosing the slow queries
  4. Capturing a series of SQL statements causing problem, to further replicate the problem on the test server, while debugging the issue.
  5. It also helps in correlating performance counters to debug issues easily.

 Explain Magic Tables in SQL server

Magic Tables are tables automatically created tables in SQL Server used to internally store the inserted, updated values for DML operations such as (SELECT, DELETE, INSERT, UPDATE, etc).

What do you understand by User-Defined function in the SQL Server and explain the steps to create and execute a user-defined function in the SQL Server?

A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table.

To create a user-defined function, refer to the following example:


To execute the above-created function, refer to the following command:

1SELECT * FROM samplefunc(10)

What do you understand by CHECK constraint in SQL Server?

The CHECK constraint in SQL Server is used to limit the values or type of data stored in a column. Once you apply the CHECK constraint on a single column, you can go forward and apply specific values for that particular column.


CREATE TABLE Customer (  
    Cust_ID int NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    City varchar(255),  
    CONSTRAINT CHK_Customer CHECK (Age>20 AND City= 'Hyderabad')  

 What do you understand by COALESCE in SQL Server?

This function is used to return the first non-null expression within arguments. The COALESCE command is used to return a non-null value from more than a single column in arguments.


1SELECT COALESCE(CustID, CustName, Amount) from Customers;

What are the common performance issues in SQL Server?

The common performance issues in SQL Server are as follows:

  • Fragmentation
  • Input/Output bottlenecks
  • Blocking Queues
  • Deadlocks
  • Missing and unused indexes

Temporary Tables And Table Variables In SQL

⇒ Table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.

⇒ Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table.

⇒ Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

⇒ Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.

⇒ Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.

⇒ Temporary table allows Schema modifications unlike Table variables.

Table Variable in SQL Server – Example

Table variable is a very useful programming construct, like that of any other variable.

  1.  DECLARE @TStudent TABLE  
  2.  (  
  3.     RollNo INT IDENTITY(1,1),  
  4.     StudentID INT,  
  5.     Name INT  
  6.  )   
  7.  –Insert data to Table variable @TStudent   
  8.  INSERT INTO @TStudent(StudentID,Name)  
  9.  SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
  11.  –Select data from Table variable @TStudent  
  12.  SELECT * FROM @TStudent  
  14.  –Next batch  
  15.  GO  
  16.  SELECT * FROM @TStudent –gives error  
  17. DECLARE @TStudent TABLE  
  18.  (  
  19.     RollNo INT IDENTITY(1,1),  
  20.     StudentID INT,  
  21.     Name INT  
  22.  )   
  23.  –Insert data to Table variable @TStudent   
  24.  INSERT INTO @TStudent(StudentID,Name)  
  25.  SELECT DISTINCT StudentID, Name FROM StudentMaster ORDER BY StudentID ASC   
  27.  –Select data from Table variable @TStudent  
  28.  SELECT * FROM @TStudent  
  30.  –Next batch  
  31.  GO  
  32.  SELECT * FROM @TStudent —gives error 

Temporary Tables in SQL Server – Example

In SQL Server, based on the scope and behavior, temporary tables are of two types, 

  1. Local Temporary Tables (#temp)  
  2. Global Temporary Tables (##temp)  
  4. CREATE TABLE #StudentTemp  
  5. (  
  6.     StudentID int,  
  7.     Name varchar(50),   
  8.     Address varchar(150)  
  9. )  
  10. GO  
  11. INSERT INTO #StudentTemp VALUES ( 1, ‘Dipendra’,’Pune’);  
  12. GO  
  13. SELECT * FROM #StudentTemp  
  14. CREATE TABLE #StudentTemp  
  15. (  
  16.     StudentID int,  
  17.     Name varchar(50),   
  18.     Address varchar(150)  
  19. )  
  20. GO  
  21. INSERT INTO #StudentTemp VALUES ( 1, ‘Dipendra’,’Pune’);  
  22. GO  
  23. SELECT * FROM #StudentTemp   

Points to Remember

  • Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
  • Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.
  • Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.

I hope I was able to explain the difference between Temporary Tables and Table variables in SQL Server.


Types of Window functions

  • Aggregate Window Functions
    SUM(), MAX(), MIN(), AVG(). COUNT()
  • Ranking Window Functions
  • Value Window Functions

Difference between RANK() and DENSE_RANK()

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

How many time recursion happen for CTE

 In theory, rCTE’s would run forever except for two things. One would be the default limit on such recursive structures being 100. If the query above didn’t have the WHERE clause, it would “iterate” 100 times and then fail with a “recursive limit” failure.

Obviously, the other thing that stops the recursion is the WHERE clause. The WHERE clause acts like the WHILE statement in a loop in that that’s where the “recursive limit” is defined just like the “iteration limit” in a While loop is defined.

How many type of execution plan available

There are two different execution plans – Estimated and Actual.

Estimated execution plan indicates optimizer view.

Actual execution plan indicates what executed the query and how was it done.

Execution plans are stored in memory called plan cache, hence can be reused. Each plan is stored once unless optimizer decides parallelism for the execution of the query.

There are three different formats of execution plans available in SQL Server – Graphical plans, Text plans, and XML plans.

SHOWPLAN is the permission which is required for the user who wants to see the execution plan.


Index in sql

The index is termed as a structure in SQL server maintained or stored wither in-memory structure or on disk associated with a View or table, which is used primarily to identify any particular row or a set of rows from Views or Table. Indexes in SQL are the individual lookup tables, which are used by the database search engine to speed up the overall data retrieval.

An index in the table is used to increase the overall speed required for searching for any particular data in the database. An index in the SQL database can be used to identify all the rows efficiently and some of the matching columns in query and then, the user can quickly enter the subset of the table to determine the exact matches of the proposed question.

SQL Index is used to quickly find the data in a database table without searching every row of it. In SQL Index, it is imperative to maintain more storage space to make a duplicate copy of the database. An index stores the complete data in the table, which is organized logically with columns and rows, and physically maintained and stored in row-wise data known as row store and in case if the records are stored in column-wise data, known as Columnstore.

There are different types of Indexes in SQL:

  • Clustered Index
  • Non-Clustered Index
  • Unique Index
  • Filtered Index
  • Columnstore Index
  • Hash Index

What is an XML index?


Order of execution of a Query

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
1. FROM and JOINs

The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. This includes subqueries in this clause, and can cause temporary tables to be created under the hood containing all the columns and rows of the tables being joined.


Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.


The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.


If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don’t satisfy the constraint. Like the WHERE clause, aliases are also not accessible from this step in most databases.


Any expressions in the SELECT part of the query are finally computed.


Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.


If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order. Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.


Finally, the rows that fall outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.