Basic SQL Interview Questions

Basic SQL Interview Questions

All set to kickstart your career in SQL? Look no further and start your professional career with these SQL interview questions for freshers. We will start with the basics and slowly move towards slightly advanced questions to set the pace. If you are an experienced professional, this section will help you brush up on your SQL skills.

What is SQL?

The acronym SQL stands for Structured Query Language. It is the typical language used for relational database maintenance and a wide range of data processing tasks. The first SQL database was created in 1970. It is a database language used for operations such as database creation, deletion, retrieval, and row modification. It is occasionally pronounced “sequel.” It can also be used to manage structured data, which is made up of variables called entities and relationships between those entities.

What is Database?

A database is a system that helps in collecting, storing and retrieving data. Databases can be complex, and such databases are developed using design and modelling approaches.

What is DBMS?

DBMS stands for Database Management System which is responsible for the creating, updating, and managing of the database. 

What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System that stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables.

How to create a table in SQL?

The command to create a table in SQL is extremely simple:

 CREATE TABLE table_name (

               column1 datatype,

               column2 datatype,

               column3 datatype,

   ….

);

We will start off by giving the keywords, CREATE TABLE, and then we will give the name of the table. After that in braces, we will list out all the columns along with their data types.

For example, if we want to create a simple employee table:

CREATE TABLE employee (

               name varchar(25),

               age int,

               gender varchar(25),

   ….

);

How to delete a table in SQL?

There are two ways to delete a table from SQL: DROP and TRUNCATE. The DROP TABLE command is used to completely delete the table from the database. This is the command:

DROP TABLE table_name;

The above command will completely delete all the data present in the table along with the table itself.

But if we want to delete only the data present in the table but not the table itself, then we will use the truncate command:

DROP TABLE table_name ;

How to change a table name in SQL?

This is the command to change a table name in SQL:

ALTER TABLE table_name

RENAME TO new_table_name;

We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.

For example, if we want to change the “employee” table to “employee_information”, this will be the command:

ALTER TABLE employee

RENAME TO employee_information;

How to delete a row in SQL?

We will be using the DELETE query to delete existing rows from the table:

DELETE FROM table_name

WHERE [condition];

We will start off by giving the keywords DELETE FROM, then we will give the name of the table, and after that we will give the WHERE clause and give the condition on the basis of which we would want to delete a row.

For example, from the employee table, if we would like to delete all the rows, where the age of the employee is equal to 25, then this will be the command:

DELETE FROM employee

WHERE [age=25];

How to create a database in SQL?

A database is a repository in SQL, which can comprise multiple tables.

This will be the command to create a database in sql:

CREATE DATABASE database_name.

What is Normalization in SQL?

Normalization is used to decompose a larger, complex table into simple and smaller ones. This helps us in removing all the redundant data.

Generally, in a table, we will have a lot of redundant information which is not required, so it is better to divide this complex table into multiple smaller tables which contain only unique information.

First normal form:

A relation schema is in 1NF, if and only if:

• All attributes in the relation are atomic(indivisible value)
• And there are no repeating elements or groups of elements.

Second normal form:

A relation is said to be in 2NF, if and only if:

• It is in 1^st Normal Form.
• No partial dependency exists between non-key attributes and key attributes.

Third Normal form:

A relation R is said to be in 3NF if and only if:

• It is in 2NF.
• No transitive dependency exists between non-key attributes and key attributes through another non-key attribute

What is join in SQL?

Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

• INNER JOIN − Returns rows when there is a match in both tables.

• LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

• RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

• FULL OUTER JOIN − Returns rows when there is a match in one of the tables.

• SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

• CARTESIAN JOIN (CROSS JOIN) − Returns the Cartesian product of the sets of records from the two or more joined tables.

INNER JOIN:

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

INNER JOIN table2

ON table1.commonfield = table2.commonfield;

LEFT JOIN:

The LEFT JOIN returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

LEFT JOIN table2

ON table1.commonfield = table2.commonfield;

RIGHT JOIN:

The RIGHT JOIN returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

RIGHT JOIN table2

ON table1.commonfield = table2.commonfield;

FULL OUTER JOIN:

The FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

Left JOIN table2

ON table1.commonfield = table2.commonfield;

Union

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

Right JOIN table2

ON table1.commonfield = table2.commonfield;

SELF JOIN:

The SELF JOIN joins a table to itself; temporarily renaming at least one table in the SQL statement.

SYNTAX:

SELECT a.col1, b.col2,…, a.coln

FROM table1 a, table1 b

WHERE a.commonfield = b.commonfield;

How to insert a date in SQL?

If the RDBMS is MYSQL, this is how we can insert date:

“INSERT INTO tablename (col_name, col_date) VALUES (‘DATE: Manual Date’, ‘2020-9-10’)”;

What is Primary Key in SQL?

Primary Key is a constraint in SQL. So, before understanding what exactly is a primary key, let’s understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level. 

Let’s look at the different types of constraints which are present in SQL:

Constraint	Description
NOT NULL	Ensures that a column cannot have a NULL value.
DEFAULT	Provides a default value for a column when none is specified.
UNIQUE	Ensures that all the values in a column are different
PRIMARY	Uniquely identifies each row/record in a database table
FOREIGN	Uniquely identifies a row/record in any another database table
CHECK	The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX	Used to create and retrieve data from the database very quickly.

You can consider the Primary Key constraint to be a combination of UNIQUE and NOT NULL constraint. This means that if a column is set as a primary key, then this particular column cannot have any null values present in it and also all the values present in this column must be unique.

How do I view tables in SQL?

To view tables in SQL, all you need to do is give this command:

Show tables;

What is PL/SQL?

PL SQL stands for Procedural language constructs for Structured Query Language. PL SQL was introduced by Oracle to overcome the limitations of plain sql. So, pl sql adds in procedural language approach to the plain vanilla sql.

One thing to be noted over here is that pl sql is only for oracle databases. If you don’t have an Oracle database, then you cant work with PL SQL. However, if you wish to learn more about Oracle, you can also take up free oracle courses and enhance your knowledge.

While, with the help of sql, we were able to DDL and DML queries, with the help of PL SQL, we will be able to create functions, triggers and other procedural constructs.

How can I see all tables in SQL?

Different database management systems have different queries to see all the tables.

To see all the tables in MYSQL, we would have to use this query:

show tables;

This is how we can see all tables in ORACLE:

SELECT

    table_name

FROM

    User_tables;

This is how we can extract all tables in SQL Server:

SELECT

    *

FROM

    Information_schema.tables;

What is ETL in SQL?

ETL stands for Extract, Transform and Load. It is a three-step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into the tidy format, which will come in the second phase. Finally, we would have to load this tidy data into tools which would help us to find insights.

How to install SQL?

SQL stands for Structured Query Language and it is not something you can install. To implement sql queries, you would need a relational database management system. There are different varieties of relational database management systems such as:

• ORACLE
• MYSQL
• SQL Server

Hence, to implement sql queries, we would need to install any of these Relational Database Management Systems.

What is the update command in SQL?

The update command comes under the DML(Data Manipulation Langauge) part of sql and is used to update the existing data in the table.

UPDATE employees

SET last_name=‘Cohen’

WHERE employee_id=101;

With this update command, I am changing the last name of the employee.

How to rename column name in SQL Server?

Rename column in SQL: When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.

What are the types of SQL Queries?

We have four types of SQL Queries:

• DDL (Data Definition Language): the creation of objects
• DML (Data Manipulation Language): manipulation of data
• DCL (Data Control Language): assignment and removal of permissions
• TCL (Transaction Control Language): saving and restoring changes to a database

Let’s look at the different commands under DDL:

Command	Description
CREATE	Create objects in the database
ALTER	Alters the structure of the database object
DROP	Delete objects from the database
TRUNCATE	Remove all records from a table permanently
COMMENT	Add comments to the data dictionary
RENAME	Rename an object

Write a Query to display the number of employees working in each region? 

SELECT region, COUNT(gender) FROM employee GROUP BY region;

What are Nested Triggers?

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

Write SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal 10000.

By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 20000 and <=10000.

SELECT FullName FROM EmployeeDetails WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary WHERE Salary BETWEEN 5000 AND 10000)

Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 asc;

“Order by 2” is valid when there are at least 2 columns used in SELECT statement. Here this query will throw error because only one column is used in the SELECT statement. 

What is OLTP?

OLTP stands for Online Transaction Processing. And is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. 

What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

What is OLAP?

OLAP stands for Online Analytical Processing. And a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. 

Find the Constraint information from the table?

There are so many times where user needs to find out the specific constraint information of the table. The following queries are useful, SELECT * From User_Constraints; SELECT * FROM User_Cons_Columns;

Can you get the list of employees with same salary? 

Select distinct e.empid,e.empname,e.salary from employee e, employee e1 where e.salary =e1.salary and e.empid != e1.empid 

What is an alternative for the TOP clause in SQL?

1. ROWCOUNT function 
2. Set rowcount 3
3. Select * from employee order by empid desc Set rowcount 0 

Will the following statement gives an error or 0 as output? SELECT AVG (NULL)

Error. Operand data type NULL is invalid for the Avg operator. 

What is the Cartesian product of the table?

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.

What is a schema in SQL?

Our database comprises of a lot of different entities such as tables, stored procedures, functions, database owners and so on. To make sense of how all these different entities interact, we would need the help of schema. So, you can consider schema to be the logical relationship between all the different entities which are present in the database.

Once we have a clear understanding of the schema, this helps in a lot of ways:

• We can decide which user has access to which tables in the database.
• We can modify or add new relationships between different entities in the database.

Overall, you can consider a schema to be a blueprint for the database, which will give you the complete picture of how different objects interact with each other and which users have access to different entities.

How to delete a column in SQL?

To delete a column in SQL we will be using DROP COLUMN method:

ALTER TABLE employees

DROP COLUMN age;

We will start off by giving the keywords ALTER TABLE, then we will give the name of the table, following which we will give the keywords DROP COLUMN and finally give the name of the column which we would want to remove.

What is a unique key in SQL?

Unique Key is a constraint in SQL. So, before understanding what exactly is a primary key, let’s understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level. 

Unique Key: 

Whenever we give the constraint of unique key to a column, this would mean that the column cannot have any duplicate values present in it. In other words, all the records which are present in this column have to be unique.

How to implement multiple conditions using the WHERE clause?

We can implement multiple conditions using AND, OR operators:

SELECT * FROM employees WHERE first_name = ‘Steven’ AND salary <=10000;

In the above command, we are giving two conditions. The condition ensures that we extract only those records where the first name of the employee is ‘Steven’ and the second condition ensures that the salary of the employee is less than $10,000. In other words, we are extracting only those records, where the employee’s first name is ‘Steven’ and this person’s salary should be less than $10,000.

What is the difference between SQL vs PL/SQL?

BASIS FOR COMPARISON	SQL	PL/SQL
Basic	In SQL you can execute a single query or a command at a time.	In PL/SQL you can execute a block of code at a time.
Full form	Structured Query Language	Procedural Language, an extension of SQL.
Purpose	It is like a source of data that is to be displayed.	It is a language that creates an application that displays data acquired by SQL.
Writes	In SQL you can write queries and commands using DDL, DML statements.	In PL/SQL you can write a block of code that has procedures, functions, packages or variables, etc.
Use	Using SQL, you can retrieve, modify, add, delete, or manipulate the data in the database.	Using PL/SQL, you can create applications or server pages that display the information obtained from SQL in a proper format.
Embed	You can embed SQL statements in PL/SQL.	You can not embed PL/SQL in SQL

What is the difference between SQL having vs where?

S. No.	Where Clause	Having Clause
1	The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP by clause	The HAVING clause cannot be used without the GROUP BY clause
2	The WHERE clause selects rows before grouping	The HAVING clause selects rows after grouping
3	The WHERE clause cannot contain aggregate functions	The HAVING clause can contain aggregate functions
4	WHERE clause is used to impose a condition on SELECT statement as well as single row function and is used before GROUP BY clause	HAVING clause is used to impose a condition on GROUP Function and is used after GROUP BY clause in the query
5	SELECT Column,AVG(Column_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmae	SELECT Columnq, AVG(Coulmn_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmae Having column_name>or<value

SQL Interview Questions for Experienced

Planning to switch your career to SQL or just need to upgrade your position? Whatever your reason, this section will better prepare you for the SQL interview. We have compiled a set of advanced SQL questions that may be frequently asked during the interview. 

What is SQL injection?

SQL injection is a hacking technique which is widely used by black-hat hackers to steal data from your tables or databases. Let’s say, if you go to a website and give in your user information and password, the hacker would add some malicious code over there such that, he can get the user information and password directly from the database. If your database contains any vital information, it is always better to keep it secure from SQL injection attacks.

What is a trigger in SQL?

A trigger is a stored program in a database which automatically gives responses to an event of DML operations done by inserting, update, or delete. In other words, is nothing but an auditor of events happening across all database tables.

Let’s look at an example of a trigger:

CREATE TRIGGER bank_trans_hv_alert

               BEFORE UPDATE ON bank_account_transaction

               FOR EACH ROW

               begin

               if( abs(:new.transaction_amount)>999999)THEN

    RAISE_APPLICATION_ERROR(-20000, ‘Account transaction exceeding the daily deposit on SAVINGS account.’);

               end if;

               end;

How to insert multiple rows in SQL?

To insert multiple rows in SQL we can follow the below syntax:

INSERT INTO table_name (column1, column2,column3…)

VALUES

    (value1, value2, value3…..),

    (value1, value2, value3….),

    …

    (value1, value2, value3);

We start off by giving the keywords INSERT INTO then we give the name of the table into which we would want to insert the values. We will follow it up with the list of the columns, for which we would have to add the values. Then we will give in the VALUES keyword and finally, we will give the list of values.

Here is an example of the same:

INSERT INTO employees (

    name,

    age,

    salary)

VALUES

    (

        ‘Sam’,

        21,

       75000

    ),

    (

        ‘ ‘Matt’,

        32,

       85000    ),

    (

        ‘Bob’,

        26,

       90000

    );

In the above example, we are inserting multiple records into the table called employees.

How to find the nth highest salary in SQL?

This is how we can find the nth highest salary in SQL SERVER using TOP keyword:

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM #Employee ORDER BY salary DESC ) AS temp ORDER BY salary

This is how we can find the nth highest salary in MYSQL using LIMIT keyword:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1

How to copy table in SQL?

We can use the SELECT INTO statement to copy data from one table to another. Either we can copy all the data or only some specific columns.

This is how we can copy all the columns into a new table:

SELECT *

INTO newtable

FROM oldtable

WHERE condition;

If we want to copy only some specific columns, we can do it this way:

SELECT column1, column2, column3, …

INTO newtable

FROM oldtable

WHERE condition;

How to add a new column in SQL?

We can add a new column in SQL with the help of alter command:

ALTER TABLE employees ADD COLUMN contact INT(10);

This command helps us to add a new column named as contact in the employees table.

How to use LIKE in SQL?

The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator

SELECT * FROM employees WHERE first_name like ‘Steven’; 

With this command, we will be able to extract all the records where the first name is like “Steven”.

If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?

Yes, SQL server drops all related objects, which exists inside a table like constraints, indexex, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table. 

Can we disable a trigger? If yes, How?

Yes, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON<>. We also have an option to disable all the trigger by using, “DISABLE Trigger ALL ON ALL SERVER”.

What is a Live Lock?

A live lock is one where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keep interferring. A live lock also occurs when read transactions create a table or page. 

How to fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers – To display even numbers –

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0

To display odd numbers –

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

Define COMMIT and give an example?

When a COMMIT is used in a transaction, all changes made in the transaction are written into the database permanently.

Example:

BEGIN TRANSACTION; DELETE FROM HR.JobCandidate WHERE JobCandidateID = 20; COMMIT TRANSACTION;

The above example deletes a job candidate in a SQL server.

Can you join the table by itself? 

A table can be joined to itself using self join, when you want to create a result set that joins records in a table with other records in the same table.

Explain Equi join with an example.

When two or more tables have been joined using equal to operator then this category is called an equi join. Just we need to concentrate on the condition is equal to (=) between the columns in the table.

Example:

Select a.Employee_name,b.Department_name from Employee a,Employee b where a.Department_ID=b.Department_ID

How do we avoid getting duplicate entries in a query?

The SELECT DISTINCT is used to get distinct data from tables using a query. The below SQL query selects only the DISTINCT values from the “Country” column in the “Customers” table:

SELECT DISTINCT Country FROM Customers;

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

Lets take an example:

Select * into studentcopy from student where 1=2

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

Write a Query to display odd records from student table?

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS RowID FROM student) WHERE row_id %2!=0

Explain Non-Equi Join with an example?

When two or more tables are joining without equal to condition, then that join is known as Non Equi Join. Any operator can be used here, that is <>,!=,<,>,Between.

Example:

Select b.Department_ID,b.Department_name from Employee a,Department b where a.Department_id <> b.Department_ID;

How can you delete duplicate records in a table with no primary key?

By using the SET ROWCOUNT command. It limits the number of records affected by a command. Let’s take an example, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0.

Difference between NVL and NVL2 functions?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null. With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1. With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

What is the difference between clustered and non-clustered indexes?

1. Clustered indexes can be read rapidly rather than non-clustered indexes. 
2. 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.

What does this query says? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION];

The given syntax indicates that the user can grant access to another user too.

Where MyISAM table is stored?

Each MyISAM table is stored on disk in three files. 

1. The “.frm” file stores the table definition. 
2. The data file has a ‘.MYD’ (MYData) extension. 
3. The index file has a ‘.MYI’ (MYIndex) extension. 

What does myisamchk do?

It compresses the MyISAM tables, which reduces their disk or memory usage.

What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

What is Database White box testing?

White box testing includes: 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.

What are the different types of SQL sandbox?

There are 3 different 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.

What is Database Black Box Testing?

This testing involves:

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

Explain Right Outer Join with Example?

This join is usable, when user wants all the records from Right table (Second table) and only equal or matching records from First or left table. The unmatched records are considered as null records. Example: Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 where t1.col(+)=t2.col;

What is a Subquery?

A SubQuery is a SQL query nested into a larger query. Example: SELECT employeeID, firstName, lastName FROM employees WHERE departmentID IN (SELECT departmentID FROM departments WHERE locationID = 2000) ORDER BY firstName, lastName; 

SQL Interview Questions for Developers

How to find duplicate records in SQL?

There are multiple ways to find duplicate records in SQL. Let’s see how can we find duplicate records using group by:

SELECT

    x,

    y,

    COUNT(*) occurrences

FROM z1

GROUP BY

    x,

    y

HAVING

    COUNT(*) > 1;

We can also find duplicates in the table using rank:

SELECT * FROM ( SELECT eid, ename, eage, Row_Number() OVER(PARTITION BY ename, eage ORDER By ename) AS Rank FROM employees ) AS X WHERE Rank>1

What is Case WHEN in SQL?

If you have knowledge about other programming languages, then you’d have learnt about if-else statements. You can consider Case WHEN to be analogous to that.

In Case WHEN, there will be multiple conditions and we will choose something on the basis of these conditions.

Here is the syntax for CASE WHEN:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    WHEN conditionN THEN resultN

    ELSE result

END;

We start off by giving the CASE keyword, then we follow it up by giving multiple WHEN, THEN statements.

How to find 2nd highest salary in SQL?

Below is the syntax to find 2nd highest salary in SQL:

SELECT name, MAX(salary)

  FROM employees

 WHERE salary < (SELECT MAX(salary)

                 FROM employees);

How to delete duplicate rows in SQL?

There are multiple ways to delete duplicate records in SQL.

Below is the code to delete duplicate records using rank:

alter table emp add  sid int identity(1,1)

    delete e

    from  emp e

    inner join

    (select *,

    RANK() OVER ( PARTITION BY eid,ename ORDER BY id DESC )rank

    From emp )T on e.sid=t.sid

    where e.Rank>1

    alter table emp

    drop  column sno

Below is the syntax to delete duplicate records using groupby and min:

alter table emp add  sno int identity(1,1)

                   delete E from emp E

                   left join

                   (select min(sno) sno From emp group by empid,ename ) T on E.sno=T.sno

                   where T.sno is null

                   alter table emp

                   drop  column sno          

What is cursor in SQL?

Cursors in SQL are used to store database tables. There are two types of cursors:

• Implicit Cursor
• Explicit Cursor

Implicit Cursor:

These implicit cursors are default cursors which are automatically created. A user cannot create an implicit cursor.

Explicit Cursor:

Explicit cursors are user-defined cursors. This is the syntax to create explicit cursor:

DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

We start off by giving by keyword DECLARE, then we give the name of the cursor, after that we give the keywords CURSOR FOR SELECT * FROM, finally, we give in the name of the table.

How to create a stored procedure using SQL Server?

If you have worked with other languages, then you would know about the concept of Functions. You can consider stored procedures in SQL to be analogous to functions in other languages. This means that we can store a SQL statement as a stored procedure and this stored procedure can be invoked whenever we want.

This is the syntax to create a stored procedure:

CREATE PROCEDURE procedure_name

AS

sql_statement

GO;

We start off by giving the keywords CREATE PROCEDURE, then we go ahead and give the name of this stored procedure. After that, we give the AS keyword and follow it up with the SQL query, which we want as a stored procedure. Finally, we give the GO keyword.

Once, we create the stored procedure, we can invoke it this way:

EXEC procedure_name;

We will give in the keyword EXEC and then give the name of the stored procedure.

Let’s look at an example of a stored procedure:

CREATE PROCEDURE employee_location @location nvarchar(20)

AS

SELECT * FROM employees WHERE location = @location

GO;

In the above command, we are creating a stored procedure which will help us to extract all the employees who belong to a particular location.

EXEC employee_location @location = ‘Boston’;

With this, we are extracting all the employees who belong to Boston.

How to create an index in SQL?

We can create an index using this command:

CREATE INDEX index_name

ON table_name (column1, column2, column3 …);

We start off by giving the keywords CREATE INDEX and then we will follow it up with the name of the index, after that we will give the ON keyword. Then, we will give the name of the table on which we would want to create this index. Finally, in parenthesis, we will list out all the columns which will have the index. Let’s look at an example:

CREATE INDEX salary

ON Employees (Salary);

In the above example, we are creating an index called a salary on top of the ‘Salary’ column of the ‘Employees’ table.

Now, let’s see how can we create a unique index:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2,column3 …);

We start off with the keywords CREATE UNIQUE INDEX, then give in the name of the index, after that, we will give the ON keyword and follow it up with the name of the table. Finally, in parenthesis, we will give the list of the columns which on which we would want this unique index.

How to change the column data type in SQL?

We can change the data type of the column using the alter table. This will be the command:

ALTER TABLE table_name

MODIFY COLUMN column_name datatype;

We start off by giving the keywords ALTER TABLE, then we will give in the name of the table. After that, we will give in the keywords MODIFY COLUMN. Going ahead, we will give in the name of the column for which we would want to change the datatype and finally we will give in the data type to which we would want to change.

Difference between SQL and NoSQL databases?

SQL stands for structured query language and is majorly used to query data from relational databases. When we talk about a SQL database, it will be a relational database. 

But when it comes to the NoSQL databases, we will be working with non-relational databases.

SQL Joins Interview Questions

How to change column name in SQL?

The command to change the name of a column is different in different RDBMS.

This is the command to change the name of a column in MYSQL:

ALTER TABLE Customer CHANGE Address Addr char(50);

IN MYSQL, we will start off by using the ALTER TABLE keywords, then we will give in the name of the table. After that, we will use the CHANGE keyword and give in the original name of the column, following which we will give the name to which we would want to rename our column.

This is the command to change the name of a column in ORACLE:

ALTER TABLE Customer RENAME COLUMN Address TO Addr;

In ORACLE, we will start off by using the ALTER TABLE keywords, then we will give in the name of the table. After that, we will use the RENAME COLUMN keywords and give in the original name of the column, following which we will give the TO keyword and finally give the name to which we would like to rename our column.

When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.

What is a view in SQL?

A view is a database object that is created using a Select Query with complex logic, so views are said to be a logical representation of the physical data, i.e Views behave like a physical table and users can use them as database objects in any part of SQL queries.

Let’s look at the types of Views:

• Simple View
• Complex View
• Inline View
• Materialized View

Simple View:

Simple views are created with a select query written using a single table. Below is the command to create a simple view:

Create VIEW Simple_view as Select * from BANK_CUSTOMER ;

Complex View:

Create VIEW Complex_view as SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.balance > 300000

Inline View:

A subquery is also called an inline view if and only if it is called in FROM clause of a SELECT query.

SELECT * FROM ( SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.balance > 300000)

How to drop a column in SQL?

To drop a column in SQL, we will be using this command:

ALTER TABLE employees

DROP COLUMN gender;

We will start off by giving the keywords ALTER TABLE, then we will give the name of the table, following which we will give the keywords DROP COLUMN and finally give the name of the column which we would want to remove.

How to use BETWEEN in SQL?

The BETWEEN operator checks an attribute value within a range. Here is an example of BETWEEN operator:

SELECT * FROM employees WHERE salary between 10000 and 20000;

With this command, we will be able to extract all the records where the salary of the employee is between 10000 and 20000.

Advanced SQL Interview Questions

What are the subsets of SQL?

• DDL (Data Definition Language): Used to define the data structure it consists of the commands like CREATE, ALTER, DROP, etc. 
• DML (Data Manipulation Language): Used to manipulate already existing data in the database, commands like SELECT, UPDATE, INSERT 
• DCL (Data Control Language): Used to control access to data in the database, commands like GRANT, REVOKE.

Difference between CHAR and VARCHAR2 datatype in SQL?

CHAR is used to store fixed-length character strings, and VARCHAR2 is used to store variable-length character strings.

How to sort a column using a column alias?

By using the column alias in the ORDER BY instead of where clause for sorting

Difference between COALESCE() & ISNULL() ?

COALESCE() accepts two or more parameters, one can apply 2 or as many parameters but it returns only the first non NULL parameter. 

ISNULL() accepts only 2 parameters. 

The first parameter is checked for a NULL value, if it is NULL then the 2nd parameter is returned, otherwise, it returns the first parameter.

What is “Trigger” in SQL?

A trigger allows you to execute a batch of SQL code when an insert,update or delete command is run against a specific table as Trigger is said to be the set of actions that are performed whenever commands like insert, update or delete are given. 

Write a Query to display employee details along with age.

SELECT * DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee

Write a Query to display employee details along with age?

SELECT SUM(salary) FROM employee

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

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

What are aggregate and scalar functions?

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

Example -. Aggregate – max(), count – Calculated with respect to numeric. Scalar – UCASE(), NOW() – Calculated with respect to strings.

What is a deadlock?

It is an unwanted situation where two or more transactions are waiting indefinitely for one another to release the locks. 

Explain left outer join with example.

Left outer join is useful if you want all the records from the left table(first table) and only matching records from 2nd table. The unmatched records are null records. Example: Left outer join with “+” operator Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 where t1.col=t2.col(+);

What is SQL injection?

SQL injection is a code injection technique used to hack data-driven applications.

What is a UNION operator?

The UNION operator combines the results of two or more Select statements by removing duplicate rows. The columns and the data types must be the same in the SELECT statements.

Explain SQL Constraints.

SQL Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY

What is the ALIAS command?

This command provides another name to a table or a column. It can be used in the WHERE clause of a SQL query using the “as” keyword. 

What are Group Functions? Why do we need them?

Group functions work on a set of rows and return a single result per group. The popularly used group functions are AVG, MAX, MIN, SUM, VARIANCE, and COUNT.

How can dynamic SQL be executed?

• By executing the query with parameters 
• By using EXEC 
• By using sp_executesql

What is the usage of NVL() function?

This function is used to convert the NULL value to the other value.

Write a Query to display employee details belongs to ECE department?

SELECT EmpNo, EmpName, Salary FROM employee WHERE deptNo in (select deptNo from dept where deptName = ‘ECE’)

What are the main differences between #temp tables and @table variables and which one is preferred?

1. SQL server can create column statistics on #temp tables. 

2. Indexes can be created on #temp tables 

3. @table variables are stored in memory up to a certain threshold

What is CLAUSE?

SQL clause is defined to limit the result set by providing conditions to the query. This usually filters some rows from the whole set of records. Example – Query that has WHERE condition.

What is a recursive stored procedure?

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

What does the BCP command do?

The Bulk Copy is a utility or a tool that exports/imports data from a table into a file and vice versa. 

What is a Cross Join?

In SQL cross join, a combination of every row from the two tables is included in the result set. This is also called cross product set. For example, if table A has ten rows and table B has 20 rows, the result set will have 10 * 20 = 200 rows provided there is a NOWHERE clause in the SQL statement.

Which operator is used in query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as- 1. % – Matches zero or more characters. 2. _(Underscore) – Matching exactly one character.

Write a SQL query to get the current date?

SELECT CURDATE();

State the case manipulation functions in SQL?

• LOWER: converts all the characters to lowercase.
• UPPER: converts all the characters to uppercase. 
• INITCAP: converts the initial character of each word to uppercase

How to add a column to an existing table?

ALTER TABLE Department ADD (Gender, M, F)

Define lock escalation?

A query first takes the lowest level lock possible with the smallest row level. When too many rows are locked, the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock. 

How to store Videos inside SQL Server table?

By using FILESTREAM datatype, which was introduced in SQL Server 2008.

State the order of SQL SELECT?

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

What is the difference between IN and EXISTS?

IN: Works on List result set Doesn’t work on subqueries resulting in Virtual tables with multiple columns Compares every value in the result list.

Exists: Works on Virtual tables Is used with co-related queries Exits comparison when the match is found

How do you copy data from one table to another table?

INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition;

List the ACID properties that make sure that the database transactions are processed

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. 

What will be the output of the following Query, provided the employee table has 10 records? 

BEGIN TRAN TRUNCATE TABLE Employees ROLLBACK SELECT * FROM Employees

This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.

What do you mean by Stored Procedures? How do we use it?

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.

What does GRANT command do?

This command is used to provide database access to users other than the administrator in SQL privileges.

What does the First normal form do?

First Normal Form (1NF): It removes all duplicate columns from the table. It creates a table for related data and identifies unique column values.

How to add e record to the table?

INSERT syntax is used to add a record to the table. INSERT into table_name VALUES (value1, value2..);

What are the different tables present in MySQL?

There are 5 tables present in MYSQL.

• MyISAM 
• Heap 
• Merge 
• INNO DB 
• ISAM

What is BLOB and TEXT in MySQL?

BLOB stands for the large binary objects. It is used to hold a variable amount of data. TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character strings). 

What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. Though it can be used to close a connection opened by mysql_connect().

Write a query to find out the data between ranges?

In day-to-day activities, the user needs to find out the data between some range. To achieve this user needs to use Between..and operator or Greater than and less than the operator. 

Query 1: Using Between..and operator

Select * from Employee where salary between 25000 and 50000;

Query 2: Using operators (Greater than and less than)

Select * from Employee where salary >= 25000 and salary <= 50000;

How to calculate the number of rows in a table without using the count function?

There are so many system tables which are very important. Using the system table user can count the number of rows in the table. following query is helpful in that case, Select table_name, num_rows from user_tables where table_name=’Employee’;

What is wrong with the following query? SELECT empName FROM employee WHERE salary <> 6000

The following query will not fetch a record with the salary of 6000 but also will skip the record with NULL. 

Will the following statements execute? if yes what will be output? SELECT NULL+1 SELECT NULL+’1′

Yes, no error. The output will be NULL. Performing any operation on NULL will get the NULL result.

SQL Server Interview Questions

What is an SQL server?

SQL server has stayed on top as one of the most popular database management products ever since its first release in 1989 by Microsoft Corporation. The product is used across industries to store and process large volumes of data. It was primarily built to store and process data that is built on a relational model of data. 

SQL Server is widely used for data analysis and also scaling up of data. SQL Server can be used in conjunction with Big Data tools such as Hadoop. 

SQL Server can be used to process data from various data sources such as Excel, Table, .Net Framework application, etc.

How to install SQL Server?

• Click on the below SQL Server official release link to access the latest version: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
• Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system. 
• Click on the Download Now button.
• Save the .exe file on your system. Right-click on the .exe file and click on Open.
• Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed.
• Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

How to create a stored procedure in SQL Server?

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query saved within the Stored Procedure.

Syntax to create a Stored Proc:

1 2 3 4

CREATE PROCEDURE PROCEDURE_NAME AS SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE) GO;

Stored procedures can be user-defined or built-in. Various parameters can be passed onto a Stored Procedure.

How to install SQL Server 2008?

• Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
• Click on the search icon and type in – SQL Server 2008 download
• Click on the result link to download and save SQL Server 2008.
• Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
• Click on the Download Now button.
• Save the .exe file on your system. Right-click on the .exe file and click on Open.
• Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server installed.
• Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application.

How to install SQL Server 2017?

• Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
• Click on the search icon and type in – SQL Server 2017 download
• Click on the result link to download and save SQL Server 2017.
• Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
• Click on the Download Now button.
• Save the .exe file on your system. Right-click on the .exe file and click on Open.
• Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server installed.
• Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

How to restore the database in SQL Server?

Launch the SQL Server Management Studio application and from the Object Explorer window pane, right-click on Databases and click on Restore. This would automatically restore the database.

How to install SQL Server 2014?

• Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
• Click on the search icon and type in – SQL Server 2014 download
• Click on the result link to download and save SQL Server 2014.
• Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
• Click on the Download Now button.
• Save the .exe file on your system. Right-click on the .exe file and click on Open.
• Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed.
• Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

How to get the connection string from SQL Server?

Launch the SQL Server Management Studio. Go to the Database for which you require the Connection string. Right-click on the database and click on Properties. In the Properties window that is displayed, you can view the Connection String property.

Connection strings help connect databases to another staging database or any external source of data.

How to install SQL Server 2012?

• Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
• Click on the search icon and type in – SQL Server 2012 download
• Click on the result link to download and save SQL Server 2012.
• Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
• Click on the Download Now button.
• Save the .exe file on your system. Right-click on the .exe file and click on Open.
• Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed.
• Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

What is cte in SQL Server?

CTEs are Common Table Expressions that are used to create temporary result tables from which data can be retrieved/ used. The standard syntax for a CTE with a SELECT statement is:

1 2 3 4 5

WITH RESULT AS (SELECT COL1, COL2, COL3 FROM EMPLOYEE) SELECT COL1, COL2 FROM RESULT CTEs can be used with Insert, Update or Delete statements as well.

Few examples of CTEs are given below:

Query to find the 10 highest salaries.

with result as 

(select distinct salary, dense_rank() over (order by salary desc) as salary rank from employees)

select result. salary from result where the result.salaryrank = 10

Query to find the 2^nd highest salary

with the result as 

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result. salary from result where the result.salaryrank = 2

In this way, CTEs can be used to find the nth highest salary within an organisation.

How to change the SQL Server password?

Launch your SQL Server Management Studio. Click on the Database connection for which you want to change the login password. Click on Security from the options that get displayed. 

Click on Logins and open your database connection. Type in the new password for login and click on ‘OK’ to apply the changes. 

How to delete duplicate records in SQL Server?

Select the duplicate records in a table HAVING COUNT(*)>1 

Add a delete statement to delete the duplicate records.

Sample Query to find the duplicate records in a table-

(SELECT COL1, COUNT(*) AS DUPLICATE

FROM EMPLOYEE

GROUP BY COL1

HAVING COUNT(*) > 1)

How to uninstall SQL Server?

In Windows 10, go to the START menu and locate the SQL Server.

Right-click and select uninstall to uninstall the application.

How to check SQL Server version?

You can run the below query to view the current version of SQL Server that you are using.

1	SELECT @@version;

How to rename column name in SQL Server?

From the Object Explorer window pane, go to the table where the column is present and choose Design. Under the Column Name, select the name you want to rename and enter the new name. Go to the File menu and click Save. 

What is the stored procedure in SQL Server?

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query saved within the Stored Procedure.

Syntax to create a Stored Proc:

1 2 3 4

CREATE PROCEDURE PROCEDURE_NAME AS SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE) GO;

You can execute the Stored Proc by using the command Exec Procedure_Name;

How to create a database in SQL Server?

After installing the required version of SQL Server, it is easy to create new databases and maintain them. 

1. Launch the SQL Server Management Studio
2. In the Object Explorer window pane, right-click on Databases and select ‘New Database’
3. Enter the Database Name and click on ‘Ok’.
4. Voila! Your new database is ready for use.

What is an index in SQL Server?

Indexes are database objects which help in retrieving records quickly and more efficiently. Column indexes can be created on both Tables and Views. By declaring a Column as an index within a table/ view, the user can access those records quickly by executing the index. Indexes with more than one column are called Clustered indexes.

Syntax:

1 2	CREATE INDEX INDEX_NAME ON TABLE_NAME(COL1, COL2);

The syntax to drop an Index is DROP INDEX INDEX_NAME;

Indexes are known to improve the efficiency of SQL Select queries. 

How to create the table in SQL Server?

Tables are the fundamental storage objects within a database. A table is usually made up of 

Rows and Columns. The below syntax can be used to create a new table with 3 columns.

CREATE TABLE TABLE_NAME(

COLUMN1 DATATYPE, 

COLUMN2 DATATYPE, 

COLUMN3 DATATYPE

);

Alternatively, you can right-click on Table in the Object Explorer window pane and select ‘New -> Table’.

You can also define the type of Primary/ Foreign/ Check constraint when creating a table.

How to connect to SQL Server?

• Launch the SQL Server Management Studio from the START menu.
• In the dialogue box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system.
• Select the appropriate Authentication type and click on the Connect button.
• A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.
  

How to delete duplicate rows in SQL Server?

Select the duplicate records in a table HAVING COUNT(*)>1 

Add a delete statement to delete the duplicate records.

Sample Query to find the duplicate records in a table –

(SELECT COL1, COUNT(*) AS DUPLICATE

FROM EMPLOYEE

GROUP BY COL1

HAVING COUNT(*) > 1);

How to download SQL Server?

The Express and Developer versions (open-source versions) of the latest SQL Server release can be downloaded from the official Microsoft website. The link is given below for reference.
https://www.microsoft.com/en-in/sql-server/sql-server-downloads

How to connect SQL Server management studio to the local database?

• Launch the SQL Server Management Studio from the START menu.
• In the dialogue box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system and click on the Connect button.
• Select the Authentication as ‘Windows Authentication.
• A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.
  

How to download SQL Server 2014?

• Both the Express and Developer versions (free editions) of SQL Server can be downloaded from the official Microsoft website. The link is given below for reference.
• Click on the link below: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
• Click on the search icon and type in – SQL Server 2014 download
• Click on the result link to download and save SQL Server 2014.

How to uninstall SQL Server 2014?

From the START menu, type SQL Server. Right-click on the app and select uninstall to uninstall the application from your system. Restart the system, if required, for the changes to get affected. 

How to find server names in SQL Server?

Run the query SELECT @@version; to find the version and name of the SQL Server you are using.

How to start SQL Server?

Launch the SQL Server Management Studio from the START menu. Login using Windows Authentication. In the Object Explorer window pane, you can view the list of databases and corresponding objects. 

What is the case when in SQL Server?

Case When statements in SQL are used to run through many conditions and to return a value when one such condition is met. If none of the conditions is met in the When statements, then the value mentioned in the Else statement is returned. 

Syntax:

1 2 3 4 5 6 7 8

CASE WHEN CONDITION1 THEN RESULT1   WHEN CONDITION2 THEN RESULT2   ELSE RESULT END;

Sample query:

HOW MANY HEAD OFFICES/ BRANCHES ARE THERE IN CANADA

select 
sum ( 
case 
when region_id >=  5 AND region_id <= 7 then  
1
else 
0
end ) as Canada
from company_regions;
Nested CASE statement:
SELECT
SUM (
CASE
WHEN rental_rate = 0.99 THEN
1
ELSE
0
END
) AS “Mass”,
SUM (
CASE
WHEN rental_rate = 2.99 THEN
1
ELSE
0
END
) AS “Economic”,
SUM (
CASE
WHEN rental_rate = 4.99 THEN
1
ELSE
0
END
) AS ” Luxury”
FROM
film;

How to install SQL Server management studio?

Launch Google and in the Search toolbar, type in SQL Server Management Studio download. 

Go to the routed website and click on the link to download. Once the download is complete, open the .exe file to install the content of the file. Once the installation is complete, refresh or restart the system, as required.

Alternatively, once SQL Server is installed and launched, it will prompt the user with an option to launch SQ Server Management Studio. 

How to write a stored procedure in SQL Server?

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query saved within the Stored Procedure.

Syntax to create a Stored Proc:

1 2 3 4

CREATE PROCEDURE PROCEDURE_NAME AS SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE) GO;

You can execute the Stored Proc by using the command Exec Procedure_Name;

How to open SQL Server?

Launch the SQL Server Management Studio from the START menu. Login using Windows Authentication. In the Object Explorer window pane, you can view the list of databases and corresponding objects. 

How to use SQL Server?

SQL Server is used to retrieve and process various data that is built on a relational model.

Some of the common actions that can be taken on the data are CREATE, DELETE, INSERT, UPDATE, SELECT, REVOKE, etc.

SQL Server can also be used to import and export data from different data sources. SQL Server can also be connected to various other databases/ .Net frameworks using Connection Strings.

SQL Server can also be used in conjunction with Big Data tools like Hadoop. 

What is a function in SQL Server?

Functions are pre-written codes that return a value and which help the user achieve a particular task concerning viewing, manipulating, and processing data.

Examples of a few functions are:

AGGREGATE FUNCTIONS:

• MIN()- Returns the minimum value
• MAX()- Returns the maximum value
• AVG()- Returns the average value
• COUNT()

STRING FUNCTIONS:

• COALESCE()
• CAST()
• CONCAT()
• SUBSTRING()

DATE FUNCTIONS:

• GETDATE()
• DATEADD()
• DATEDIFF()

There are many types of functions such as Aggregate Functions, Date Functions, String Functions, Mathematical functions, etc.

How to find nth highest salary in SQL Server without using a subquery

Query to find the 10 highest salaries. For up-gradation of the b10 band.

with result as 

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result.salary from result where result.salaryrank = 10

Query to find the 2^nd highest salary

with the result as 

(select distinct salary, dense_rank() over (order by salary desc) as salary rank from employees)

select result.salary from result where result.salaryrank = 2

In this way, by replacing the salary rank value, we can find the nth highest salary in any organisation.

How to install SQL Server in Windows 10?

Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads

Click on the search icon and type in – SQL Server 2012 download

Click on the result link to download and save SQL Server 2012.

Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.

Click on the Download Now button.

Save the .exe file on your system. Right-click on the .exe file and click on Open.

Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed

How to create a temp table in SQL Server?

Temporary tables can be used to retain the structure and a subset of data from the original table from which they were derived.

Syntax:

1 2 3

SELECT COL1, COL2 INTO TEMPTABLE1 FROM ORIGTABLE;

Temporary tables do not occupy any physical memory and can be used to retrieve data faster.

PostgreSQL Interview Questions

What is PostgreSQL?

PostgreSQL is one of the most widely and popularly used languages for Object-Relational Database Management systems. It is mainly used for large web applications. It is an open-source, object-oriented, -relational database system. It is extremely powerful and enables users to extend any system without problem. It extends and uses the SQL language in combination with various features for safely scaling and storage of intricate data workloads.

List different datatypes of PostgreSQL?

Listed below are some of the new data types in PostgreSQL

• UUID
• Numeric types
• Boolean
• Character types
• Temporal types
• Geometric primitives
• Arbitrary precision numeric
• XML
• Arrays etc

What are the Indices of PostgreSQL?

Indices in PostgreSQL allow the database server to find and retrieve specific rows in a given structure. Examples are B-tree, hash, GiST, SP-GiST, GIN and BRIN.  Users can also define their indices in PostgreSQL. However, indices add overhead to the data manipulation operations and are seldom used

What are tokens in PostgreSQL?

Tokens in PostgreSQL act as the building blocks of a source code. They are composed of various special character symbols. Commands are composed of a series of tokens and terminated by a semicolon(“;”). These can be a constant, quoted identifier, other identifiers, keyword or a constant. Tokens are usually separated by whitespaces.

How to create a database in PostgreSQL?

Databases can be created using 2 methods 

• First is the CREATE DATABASE SQL Command

We can create the database by using the syntax:-

1	CREATE DATABASE <dbname>;

• The second is by using the createdb command

We can create the database by using the syntax:-

1	createdb [option…] <dbname> [description]

Various options can be taken by the createDB command based on the use case.

How to create a table in PostgreSQL?

You can create a new table by specifying the table name, along with all column names and their types:

CREATE TABLE [IF NOT EXISTS] table_name (

column1 datatype(length) column_contraint,

column2 datatype(length) column_contraint,

.

.

.

columnn datatype(length) column_contraint,

table_constraints

);

How can we change the column datatype in PostgreSQL?

The column the data type can be changed in PostgreSQL by using the ALTER TABLE command:

ALTER TABLE table_name

ALTER COLUMN column_name1 [SET DATA] TYPE new_data_type,

ALTER COLUMN column_name2 [SET DATA] TYPE new_data_type,

…;

Compare ‘PostgreSQL’ with ‘MongoDB’

PostgreSQL

MongoDB

PostgreSQL is an SQL database where data is stored as tables, with structured rows and columns. It supports concepts like referential integrity entity-relationship and JOINS. PostgreSQL uses SQL as its querying language. PostgreSQL supports vertical scaling. This means that you need to use big servers to store data. This leads to a requirement of downtime to upgrade. It works better if you require relational databases in your application or need to run complex queries that test the limit of SQL.

MongoDB, on the other hand, is a NoSQL database. There is no requirement for a schema, therefore it can store unstructured data. Data is stored as BSON documents and the document’s structure can be changed by the user. MongoDB uses JavaScript for querying. It supports horizontal scaling, as a result of which additional servers can be added as per the requirement with minimal to no downtime.  It is appropriate in a use case that requires a highly scalable distributed database that stores unstructured data

What is Multi-Version concurrency control in PostgreSQL?

MVCC or better known as Multi-version concurrency control is used to implement transactions in PostgreSQL. It is used to avoid unwanted locking of a database in the system. while querying a database each transaction sees a version of the database. This avoids viewing inconsistencies in the data, and also provides transaction isolation for every database session. MVCC locks for reading data do not conflict with locks acquired for

How do you delete the database in PostgreSQL?

Databases can be deleted in PostgreSQL using the syntax

1	DROP DATABASE [IF EXISTS] <database_name>;

Please note that only databases having no active connections can be dropped.

What does a schema contain?

• Schemas are a part of the database that contains tables. They also contain other kinds of named objects, like data types, functions, and operators.
• The object names can be used in different schemas without conflict; Unlike databases, schemas are separated more flexibly. This means that a user can access objects in any of the schemas in the database they are connected to, till they have privileges to do so.
• Schemas are highly beneficial when there is a need to allow many users access to one database without interfering with each other. It helps in organizing database objects into logical groups for better manageability. Third-party applications can be put into separate schemas to avoid conflicts based on names.

What is the square root operator in PostgreSQL?

It is denoted by ‘|/” and returns the square root of a number. Its syntax is

1	Select |/ <number>

Egs:- Select |/16

How are the stats updated in Postgresql?

To update statistics in PostgreSQL a special function called an explicit ‘vacuum’ call is made. Entries in pg_statistic are updated by the ANALYZE and VACUUM ANALYZE commands

What Is A Candid?

The CTIDs field exists in every PostgreSQL table. It is unique for every record of a table and exactly shows the location of a tuple in a particular table. A logical row’s CTID changes when it is updated, thus it cannot be used as a permanent row identifier. However, it is useful when identifying a row within a transaction when no update is expected on the data item.

What is Cube Root Operator (||/) in PostgreSQL?

It is denoted by ‘|/” and returns the square root of a number. Its syntax is

1	Select |/ <number>

Egs:- Select |/16

Explain Write-Ahead Logging?

Write-ahead logging is a method to ensure data integrity. It is a protocol that ensures writing the actions as well as changes into a transaction log. It is known to increase the reliability of databases by logging changes before they are applied or updated onto the database. This provides a backup log for the database in case of a crash.

What is a non-clustered index?

A non-clustered index in PostgreSQL is a simple index, used for fast retrieval of data, with no certainty of the uniqueness of data. It also contains pointers to locations where other parts of data are stored

How is security ensured in PostgreSQL?

PostgreSQL uses 2 levels of security

• Network-level security uses Unix Domain sockets, TCP/IP sockets, and firewalls.
• Transport-level security which uses SSL/TLS to enable secure communication with the database
• Database-level security with features like roles and permissions, row-level security (RLS), and auditing.

SQL Practice Questions

PART 1

This covers SQL basic query operations like creating databases forms scratch, creating a table, inserting values etc.

It is better to get hands-on in order to have practical experience with SQL queries. A small error/bug will make you feel surprised and next time you will get there!

Let’s get started!

1) Create a Database bank

1 2	CREATE DATABASE bank; use bank

2) Create a table with the name “bank_details” with the following columns

— Product  with string data type 

— Quantity with numerical data type 

— Price with real number data type 

— purchase_cost with decimal data type 

— estimated_sale_price with data type float 

1 2 3 4 5 6

Create table bank_details( Product CHAR(10) , quantity INT, price Real , purchase_cost Decimal(6,2), estimated_sale_price  Float);

3) Display all columns and their datatype and size in Bank_details

1	Describe bank_details;

4) Insert two records into Bank_details.

— 1st record with values —

— Product: PayCard

— Quantity: 3 

— price: 330

— Puchase_cost: 8008

— estimated_sale_price: 9009

— Product: PayPoints —

— Quantity: 4

— price: 200

— Puchase_cost: 8000

— estimated_sale_price: 6800

1 2	Insert into Bank_detailsvalues ( ‘paycard’ , 3 , 330, 8008, 9009); Insert into Bank_detailsvalues ( ‘paypoints’ , 4 , 200, 8000, 6800);

5) Add a column: Geo_Location to the existing Bank_details table with data type varchar and size 20

1	Alter table Bank_details add  geo_location Varchar(20);

6) What is the value of Geo_location for a product : “PayCard”?

1	Select geo_location  from Bank_details where Product = ‘PayCard’;

7) How many characters does the  Product : “paycard” have in the Bank_details table.

1	select char_length(Product) from Bank_details where Product = ‘PayCard’;

8) Alter the Product field from CHAR to VARCHAR in Bank_details

1	Alter table  bank_details modify PRODUCT varchar(10);

9) Reduce the size of the Product field from 10 to 6 and check if it is possible

1	Alter table bank_details modify product varchar(6);

10) Create a table named as Bank_Holidays with below fields 

— a) Holiday field which displays only date 

— b) Start_time field which displays hours and minutes 

— c) End_time field which also displays hours and minutes and timezone

1 2 3 4

Create table bank_holidays (             Holiday  date ,             Start_time datetime ,             End_time timestamp);

11) Step 1: Insert today’s date details in all fields of Bank_Holidays 

— Step 2: After step1, perform the below 

— Postpone Holiday to next day by updating the Holiday field

1 2 3 4 5 6 7 8

— Step1: Insert into bank_holidays  values ( current_date(),          current_date(), current_date() );   — Step 2: Update bank_holidays set holiday = DATE_ADD(Holiday , INTERVAL 1 DAY);

Update the End_time with current European time.

1	Update Bank_Holidays Set End_time = utc_timestamp();

12)  Display output of PRODUCT field as NEW_PRODUCT in  Bank_details table

1	Select PRODUCT as NEW_PRODUCT from bank_details;

13)  Display only one record from bank_details

1	Select * from Bank_details limit 1;

15) Display the first five characters of the Geo_location field of Bank_details.

1	SELECT substr(Geo_location  , 1, 5)  FROM `bank_details`;

PART 2

— ——————————————————–

# Datasets Used: cricket_1.csv, cricket_2.csv

— cricket_1 is the table for cricket test match 1.

— cricket_2 is the table for cricket test match 2.

— ——————————————————–

Find all the players who were present in the test match 1 as well as in the test match 2.

1 2 3

SELECT * FROM cricket_1 UNION SELECT * FROM cricket_2;

Write a MySQl query to find the players from the test match 1 having popularity higher than the average popularity.

1	select player_name , Popularity from cricket_1 WHERE Popularity > (SELECT AVG(Popularity) FROM cricket_1);

  Find player_id and player name that are common in the test match 1 and test match 2.

1 2	SELECT player_id , player_name FROM cricket_1 WHERE cricket_1.player_id IN (SELECT player_id FROM cricket_2);

Retrieve player_id, runs, and player_name from cricket_1 and cricket_2 table and display the player_id of the players where the runs are more than the average runs.

1	SELECT player_id , runs , player_name FROM cricket_1 WHERE  cricket_1.RUNS > (SELECT AVG(RUNS) FROM cricket_2);

Write a query to extract the player_id, runs and player_name from the table “cricket_1” where the runs are greater than 50.

1 2	SELECT player_id , runs , player_name FROM cricket_1 WHERE cricket_1.Runs > 50 ;

Write a query to extract all the columns from cricket_1 where player_name starts with ‘y’ and ends with ‘v’.

1	SELECT * FROM cricket_1 WHERE player_name LIKE ‘y%v’;

Write a query to extract all the columns from cricket_1 where player_name does not end with ‘t’.

1 2	SELECT * FROM cricket_1 WHERE player_name NOT LIKE ‘%t’;

# Dataset Used: cric_combined.csv

Write a MySQL query to create a new column PC_Ratio that contains the popularity to charisma ratio.

1 2 3 4

ALTER TABLE cric_combined ADD COLUMN PC_Ratio float4;   UPDATE cric_combined SET PC_Ratio =  (Popularity / Charisma);

 Write a MySQL query to find the top 5 players having the highest popularity to charisma ratio

1	SELECT Player_Name , PC_Ratio  FROM cric_combined ORDER BY  PC_Ratio DESC LIMIT 5;

Write a MySQL query to find the player_ID and the name of the player that contains the character “D” in it.

1	SELECT Player_Id ,  Player_Name FROM cric_combined WHERE Player_Name LIKE ‘%d%’;

Dataset Used: new_cricket.csv

Extract the Player_Id and Player_name of the players where the charisma value is null.

1	SELECT Player_Id , Player_Name FROM new_cricket WHERE Charisma  IS NULL;

Write a MySQL query to impute all the NULL values with 0.

1	SELECT IFNULL(Charisma, 0) FROM new_cricket;

Separate all Player_Id into single numeric ids (example PL1 =  1).

1 2	SELECT Player_Id, SUBSTR(Player_Id,3) FROM  new_cricket;

Write a MySQL query to extract Player_Id, Player_Name and charisma where the charisma is greater than 25.

1	SELECT Player_Id , Player_Name , charisma FROM new_cricket WHERE charisma > 25;

# Dataset Used: churn1.csv

Write a query to count all the duplicate values from the column “Agreement” from the table churn1.

1	SELECT Agreement, COUNT(Agreement) FROM churn1 GROUP BY Agreement HAVING COUNT(Agreement) > 1;

Rename the table churn1 to “Churn_Details”.

1	RENAME TABLE churn1 TO Churn_Details;

Write a query to create a new column new_Amount that contains the sum of TotalAmount and MonthlyServiceCharges.

1 2 3 4 5

ALTER TABLE Churn_Details ADD COLUMN new_Amount FLOAT; UPDATE Churn_Details SET new_Amount = (TotalAmount + MonthlyServiceCharges);   SELECT new_Amount FROM CHURN_DETAILS;

 Rename column new_Amount to Amount.

1 2 3

ALTER TABLE Churn_Details CHANGE new_Amount Amount FLOAT;   SELECT AMOUNT FROM CHURN_DETAILS;

Drop the column “Amount” from the table “Churn_Details”.

1	ALTER TABLE Churn_Details DROP COLUMN Amount ;

Write a query to extract the customerID, InternetConnection and gender from the table “Churn_Details ” where the value of the column “InternetConnection” has ‘i’ at the second position.

1	SELECT customerID, InternetConnection,  gender FROM Churn_Details WHERE InternetConnection LIKE ‘_i%’;

Find the records where the tenure is 6x, where x is any number.

1	SELECT * FROM Churn_Details WHERE tenure LIKE ‘6_’;

Part 3

# DataBase = Property Price Train

Dataset used: Property_Price_Train_new

Write An MySQL Query To Print The First Three Characters Of  Exterior1st From Property_Price_Train_new Table.

1	Select substring(Exterior1st,1,3) from Property_Price_Train_new;

Write An MySQL Query To Print Brick_Veneer_Area Of Property_Price_Train_new Excluding Brick_Veneer_Type, “None” And “BrkCmn” From Property_Price_Train_new Table.

1	Select  Brick_Veneer_Area, Brick_Veneer_Type from Property_Price_Train_new  where Brick_Veneer_Type not in (‘None’,’BrkCmn’);

Write An MySQL Query to print Remodel_Year , Exterior2nd of the Property_Price_Train_new Whose Exterior2nd Contains ‘H’.

1	Select Remodel_Year , Exterior2nd from Property_Price_Train_new where Exterior2nd like ‘%H%’ ;

Write MySQL query to print details of the table Property_Price_Train_new whose Remodel_year from 1983 to 2006

1	select * from Property_Price_Train_new where Remodel_Year between 1983 and 2006;

Write MySQL query to print details of Property_Price_Train_new whose Brick_Veneer_Type ends with e and contains 4 alphabets.

1	Select * from Property_Price_Train_new where Brick_Veneer_Type like ‘____e’;

Write MySQl query to print nearest largest integer value of column Garage_Area from Property_Price_Train_new

1	Select ceil(Garage_Area) from Property_Price_Train_new;

Fetch the 3 highest value of column Brick_Veneer_Area from Property_Price_Train_new table

1	Select Brick_Veneer_Area from Property_Price_Train_new order by Brick_Veneer_Area desc limit 2,1;

Rename column LowQualFinSF to Low_Qual_Fin_SF fom table Property_Price_Train_new

1	Alter table Property_Price_Train_new change LowQualFinSF Low_Qual_Fin_SF varchar(150);

Convert Underground_Full_Bathroom (1 and 0) values to true or false respectively.

# Eg. 1 – true ; 0 – false

1	SELECT CASE WHEN Underground_Full_Bathroom = 0 THEN ‘false’ ELSE ‘true’ END FROM Property_Price_Train_new;

Extract total Sale_Price for each year_sold column of Property_Price_Train_new table.

1	Select Year_Sold, sum(Sale_Price) from Property_Price_Train_new group by Year_Sold;

Extract all negative values from W_Deck_Area

1	Select W_Deck_Area from Property_Price_Train_new where W_Deck_Area < 0;

Write MySQL query to extract Year_Sold, Sale_Price whose price is greater than 100000.

1	Select Sale_Price , Year_Sold from Property_Price_Train_new group by Year_Sold having Sale_Price  >  100000;

Write MySQL query to extract Sale_Price and House_Condition from Property_Price_Train_new and Property_price_train_2 perform inner join. Rename the table as PPTN and PPTN2.

1	Select Sale_Price , House_Condition from Property_Price_Train_new AS PPTN inner join Property_price_train_2 AS PPT2 on PPTN.ID= PPTN2.ID;

Count all duplicate values of column Brick_Veneer_Type from tbale Property_Price_Train_new

1	Select Brick_Veneer_Type, count(Brick_Veneer_Type) from Property_Price_Train_new group by Brick_Veneer_Type having count(Brick_Veneer_Type) > 1;

# DATABASE Cricket

Find all the players from both matches.

1 2 3

SELECT * FROM cricket_1 UNION SELECT * FROM cricket_2;

Perform right join on cricket_1 and cricket_2.

1 2 3 4 5 6

SELECT     cric2.Player_Id, cric2.Player_Name, cric2.Runs, cric2.Charisma, cric1.Popularity FROM     cricket_1 AS cric1         RIGHT JOIN     cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

 Perform left join on cricket_1 and cricket_2

1 2 3 4 5 6

SELECT  cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma FROM     cricket_1 AS cric1         LEFT JOIN     cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Perform left join on cricket_1 and cricket_2.

1 2 3 4 5 6

SELECT     cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma FROM     cricket_1 AS cric1         INNER JOIN     cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Create a new table and insert the result obtained after performing inner join on the two tables cricket_1 and cricket_2.

1 2 3 4 5 6 7

CREATE TABLE Players1And2 AS SELECT     cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma FROM     cricket_1 AS cric1         INNER JOIN     cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Write MySQL query to extract maximum runs of players get only top two players

1	select Player_Name, Runs from cricket_1 group by Player_Name having max(Runs) limit 2;

PART 4

# Pre-Requisites

# Assuming Candidates are familiar with “Group by” and “Grouping functions” because these are used along with JOINS in the questionnaire. 

# Create below DB objects 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

CREATE TABLE BANK_CUSTOMER ( customer_id INT ,                 customer_name VARCHAR(20),                 Address     VARCHAR(20),                 state_code  VARCHAR(3) ,                         Telephone   VARCHAR(10) ); INSERT INTO BANK_CUSTOMER VALUES (123001,”Oliver”, “225-5, Emeryville”, “CA” , “1897614500”); INSERT INTO BANK_CUSTOMER VALUES (123002,”George”, “194-6,New brighton”,”MN” , “1897617000”); INSERT INTO BANK_CUSTOMER VALUES (123003,”Harry”, “2909-5,walnut creek”,”CA” , “1897617866”); INSERT INTO BANK_CUSTOMER VALUES (123004,”Jack”, “229-5, Concord”,      “CA” , “1897627999”); INSERT INTO BANK_CUSTOMER VALUES (123005,”Jacob”, “325-7, Mission Dist”,”SFO”, “1897637000”); INSERT INTO BANK_CUSTOMER VALUES (123006,”Noah”, “275-9, saint-paul” ,  “MN” , “1897613200”); INSERT INTO BANK_CUSTOMER VALUES (123007,”Charlie”,”125-1,Richfield”,   “MN” , “1897617666”); INSERT INTO BANK_CUSTOMER VALUES (123008,”Robin”,”3005-1,Heathrow”,     “NY” , “1897614000”);   CREATE TABLE BANK_CUSTOMER_EXPORT ( customer_id CHAR(10), customer_name CHAR(20), Address CHAR(20), state_code  CHAR(3) ,        Telephone  CHAR(10));       INSERT INTO BANK_CUSTOMER_EXPORT VALUES (“123001 “,”Oliver”, “225-5, Emeryville”, “CA” , “1897614500”) ; INSERT INTO BANK_CUSTOMER_EXPORT VALUES (“123002 “,”George”, “194-6,New brighton”,”MN” , “189761700”); CREATE TABLE Bank_Account_Details(Customer_id INT,                                               Account_Number VARCHAR(19),                                 Account_type VARCHAR(25),                                 Balance_amount INT,                                 Account_status VARCHAR(10),                                                  Relationship_type varchar(1) ) ; INSERT INTO Bank_Account_Details  VALUES (123001, “4000-1956-3456”,  “SAVINGS” , 200000 ,”ACTIVE”,”P”); INSERT INTO Bank_Account_Details  VALUES (123001, “5000-1700-3456”, “RECURRING DEPOSITS” ,9400000 ,”ACTIVE”,”S”);  INSERT INTO Bank_Account_Details  VALUES (123002, “4000-1956-2001”,  “SAVINGS”, 400000 ,”ACTIVE”,”P”); INSERT INTO Bank_Account_Details  VALUES (123002, “5000-1700-5001”,  “RECURRING DEPOSITS” ,7500000 ,”ACTIVE”,”S”); INSERT INTO Bank_Account_Details  VALUES (123003, “4000-1956-2900”,  “SAVINGS” ,750000,”INACTIVE”,”P”); INSERT INTO Bank_Account_Details  VALUES (123004, “5000-1700-6091”, “RECURRING DEPOSITS” ,7500000 ,”ACTIVE”,”S”); INSERT INTO Bank_Account_Details  VALUES (123004, “4000-1956-3401”,  “SAVINGS” , 655000 ,”ACTIVE”,”P”); INSERT INTO Bank_Account_Details  VALUES (123005, “4000-1956-5102”,  “SAVINGS” , 300000 ,”ACTIVE”,”P”); INSERT INTO Bank_Account_Details  VALUES (123006, “4000-1956-5698”,  “SAVINGS” , 455000 ,”ACTIVE” ,”P”); INSERT INTO Bank_Account_Details  VALUES (123007, “5000-1700-9800”,  “SAVINGS” , 355000 ,”ACTIVE” ,”P”); INSERT INTO Bank_Account_Details  VALUES (123007, “4000-1956-9977”,  “RECURRING DEPOSITS” , 7025000,”ACTIVE” ,”S”); INSERT INTO Bank_Account_Details  VALUES (123007, “9000-1700-7777-4321”,  “Credit Card” ,0  ,”INACTIVE”, “P”); INSERT INTO Bank_Account_Details  VALUES (123007, ‘5900-1900-9877-5543’, “Add-on Credit Card” ,   0   ,”ACTIVE”, “S”); INSERT INTO Bank_Account_Details  VALUES (123008, “5000-1700-7755”,  “SAVINGS”      ,0      ,”INACTIVE”,”P”); INSERT INTO Bank_Account_Details  VALUES (123006, ‘5800-1700-9800-7755’, “Credit Card”   ,0     ,”ACTIVE”, “P”); INSERT INTO Bank_Account_Details  VALUES (123006, ‘5890-1970-7706-8912’, “Add-on Credit Card”   ,0      ,”ACTIVE”, “S”);   # CREATE Bank_Account Table: # Create Table CREATE TABLE BANK_ACCOUNT ( Customer_id INT,                                          Account_Number VARCHAR(19),              Account_type VARCHAR(25),              Balance_amount INT ,             Account_status VARCHAR(10), Relation_ship varchar(1) ) ; # Insert records: INSERT INTO BANK_ACCOUNT  VALUES (123001, “4000-1956-3456”,  “SAVINGS”            , 200000 ,”ACTIVE”,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123001, “5000-1700-3456”,  “RECURRING DEPOSITS” ,9400000 ,”ACTIVE”,”S”);  INSERT INTO BANK_ACCOUNT  VALUES (123002, “4000-1956-2001”,  “SAVINGS”            , 400000 ,”ACTIVE”,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123002, “5000-1700-5001”,  “RECURRING DEPOSITS” ,7500000 ,”ACTIVE”,”S”); INSERT INTO BANK_ACCOUNT  VALUES (123003, “4000-1956-2900”,  “SAVINGS”            ,750000,”INACTIVE”,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123004, “5000-1700-6091”,  “RECURRING DEPOSITS” ,7500000 ,”ACTIVE”,”S”); INSERT INTO BANK_ACCOUNT  VALUES (123004, “4000-1956-3401”,  “SAVINGS”            , 655000 ,”ACTIVE”,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123005, “4000-1956-5102”,  “SAVINGS”            , 300000 ,”ACTIVE”,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123006, “4000-1956-5698”,  “SAVINGS”            , 455000 ,”ACTIVE” ,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123007, “5000-1700-9800”,  “SAVINGS”            , 355000 ,”ACTIVE” ,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123007, “4000-1956-9977”,  “RECURRING DEPOSITS” , 7025000,”ACTIVE” ,”S”); INSERT INTO BANK_ACCOUNT  VALUES (123007, “9000-1700-7777-4321”,  “CREDITCARD”    ,0      ,”INACTIVE”,”P”); INSERT INTO BANK_ACCOUNT  VALUES (123008, “5000-1700-7755”,  “SAVINGS”            ,NULL   ,”INACTIVE”,”P”);         # CREATE TABLE Bank_Account_Relationship_Details   CREATE TABLE Bank_Account_Relationship_Details                                 ( Customer_id INT,                                 Account_Number VARCHAR(19),                                 Account_type VARCHAR(25),                                 Linking_Account_Number VARCHAR(19)); INSERT INTO Bank_Account_Relationship_Details  VALUES (123001, “4000-1956-3456”,  “SAVINGS” , “”); INSERT INTO Bank_Account_Relationship_Details  VALUES (123001, “5000-1700-3456”,  “RECURRING DEPOSITS” , “4000-1956-3456”);  INSERT INTO Bank_Account_Relationship_Details  VALUES (123002, “4000-1956-2001”,  “SAVINGS” , “” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (123002, “5000-1700-5001”,  “RECURRING DEPOSITS” , “4000-1956-2001” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (123003, “4000-1956-2900”,  “SAVINGS” , “” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (123004, “5000-1700-6091”,  “RECURRING DEPOSITS” , “4000-1956-2900” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (123004, “5000-1700-7791”,  “RECURRING DEPOSITS” , “4000-1956-2900” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (123007, “5000-1700-9800”,  “SAVINGS” , “” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (123007, “4000-1956-9977”,  “RECURRING DEPOSITS” , “5000-1700-9800” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, “9000-1700-7777-4321”,  “Credit Card” , “5000-1700-9800” ); INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, ‘5900-1900-9877-5543’, ‘Add-on Credit Card’, ‘9000-1700-7777-4321’ ); INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, ‘5800-1700-9800-7755’, ‘Credit Card’, ‘4000-1956-5698’ ); INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, ‘5890-1970-7706-8912’, ‘Add-on Credit Card’, ‘5800-1700-9800-7755’ );       # CREATE TABLE BANK_ACCOUNT_TRANSACTION   CREATE TABLE BANK_ACCOUNT_TRANSACTION (                  Account_Number VARCHAR(19),                 Transaction_amount Decimal(18,2) ,                 Transcation_channel VARCHAR(18) ,                 Province varchar(3) ,                 Transaction_Date Date) ;     INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-3456”,  -2000, “ATM withdrawl” , “CA”, “2020-01-13”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-2001”,  -4000, “POS-Walmart”   , “MN”, “2020-02-14”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-2001”,  -1600, “UPI transfer”  , “MN”, “2020-01-19”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-2001”,  -6000, “Bankers cheque”, “CA”, “2020-03-23”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-2001”,  -3000, “Net banking”   , “CA”, “2020-04-24”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-2001”,  23000, “cheque deposit”, “MN”, “2020-03-15”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “5000-1700-6091”,  40000, “ECS transfer”  , “NY”, “2020-02-19”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “5000-1700-7791”,  40000, “ECS transfer”  , “NY”, “2020-02-19”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-3401”,   8000, “Cash Deposit”  , “NY”, “2020-01-19”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-5102”,  -6500, “ATM withdrawal” , “NY”, “2020-03-14”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-5698”,  -9000, “Cash Deposit”  , “NY”, “2020-03-27”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “4000-1956-9977”,  50000, “ECS transfer”  , “NY”, “2020-01-16”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “9000-1700-7777-4321”,  -5000, “POS-Walmart”, “NY”, “2020-02-17”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “9000-1700-7777-4321”,  -8000, “Shopping Cart”, “MN”, “2020-03-13”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “9000-1700-7777-4321”,  -2500, “Shopping Cart”, “MN”, “2020-04-21”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( “5800-1700-9800-7755”, -9000, “POS-Walmart”,”MN”, “2020-04-13”); INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( ‘5890-1970-7706-8912’, -11000, “Shopping Cart” , “NY” , “2020-03-12”) ;       # CREATE TABLE BANK_CUSTOMER_MESSAGES   CREATE TABLE BANK_CUSTOMER_MESSAGES (                  Event VARCHAR(24),                 Customer_message VARCHAR(75),                 Notice_delivery_mode VARCHAR(15)) ;     INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( “Adhoc”, “All Banks are closed due to announcement of National strike”, “mobile” ) ; INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( “Transaction Limit”, “Only limited withdrawals per card are allowed from ATM machines”, “mobile” ); INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES (‘4000-1956-9977′ ,    10000.00     ,’ECS transfer’,     ‘MN’ ,    ‘2020-02-16’ ) ;   — inserted for queries after 17th  INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES (‘4000-1956-9977′ ,    40000.00     ,’ECS transfer’,     ‘MN’ ,    ‘2020-03-18’ ) ;   INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES (‘4000-1956-9977′ ,    60000.00     ,’ECS transfer’,     ‘MN’ ,    ‘2020-04-18’ ) ;   INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES (‘4000-1956-9977′ ,    20000.00     ,’ECS transfer’,     ‘MN’ ,    ‘2020-03-20’ ) ;   — inserted for queries after 24th   INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES (‘4000-1956-9977′ ,    49000.00     ,’ECS transfer’,     ‘MN’ ,    ‘2020-06-18’ ) ;         # CREATE TABLE BANK_INTEREST_RATE   CREATE TABLE BANK_INTEREST_RATE(                  account_type varchar(24),                 interest_rate decimal(4,2),                 month varchar(2),                 year  varchar(4)                 )   ;   INSERT  INTO BANK_INTEREST_RATE VALUES ( “SAVINGS” , 0.04 , ’02’ , ‘2020’ ); INSERT  INTO BANK_INTEREST_RATE VALUES ( “RECURRING DEPOSITS” , 0.07, ’02’ , ‘2020’ ); INSERT  INTO BANK_INTEREST_RATE VALUES   ( “PRIVILEGED_INTEREST_RATE” , 0.08 , ’02’ , ‘2020’ );     # Bank_holidays:   Insert into bank_holidays values( ‘2020-05-20’, now(), now() ) ;   Insert into bank_holidays values( ‘2020-03-13’ , now(), now() ) ;

Print customer Id, customer name and average account_balance maintained by each customer for all of his/her accounts in the bank.

1 2 3 4 5 6

Select bc.customer_id , customer_name, avg(ba.Balance_amount) as All_account_balance_amount from bank_customer bc inner join Bank_Account_Details ba on bc.customer_id = ba.Customer_id group by bc.customer_id, bc.customer_name;

Print customer_id , account_number and balance_amount , 

#condition that if balance_amount is nil then assign transaction_amount  for account_type = “Credit Card”

1 2 3 4 5 6 7

Select customer_id , ba.account_number, Case when ifnull(balance_amount,0) = 0 then   Transaction_amount else balance_amount end  as balance_amount from Bank_Account_Details ba  inner join bank_account_transaction bat on ba.account_number = bat.account_number and account_type = “Credit Card”;

Print customer_id , account_number and balance_amount , 

# conPrint account number,  balance_amount, transaction_amount from Bank_Account_Details and bank_account_transaction 

# for all the transactions occurred during march,2020 and april, 2020

1 2 3 4 5 6 7 8 9

Select ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Date from Bank_Account_Details ba  inner join bank_account_transaction bat on ba.account_number = bat.account_number And ( Transaction_Date between “2020-03-01” and “2020-04-30”); — or use below condition —  # (date_format(Transaction_Date , ‘%Y-%m’)  between “2020-03” and “2020-04”);

Print all of the customer id, account number,  balance_amount, transaction_amount from bank_customer, 

# Bank_Account_Details and bank_account_transaction tables where excluding all of their transactions in march, 2020  month 

1 2 3 4 5 6 7

Select ba.Customer_id, ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Date from Bank_Account_Details ba  Left join bank_account_transaction bat on ba.account_number = bat.account_number And NOT ( date_format(Transaction_Date , ‘%Y-%m’) = “2020-03” );

Print only the customer id, customer name, account_number, balance_amount who did transactions during the first quarter. 

# Do not display the accounts if they have not done any transactions in the first quarter.

1 2 3 4 5 6 7

Select ba.Customer_id, ba.Account_Number, Balance_amount , transaction_amount , transaction_date from Bank_Account_Details ba  Inner join bank_account_transaction bat on ba.account_number = bat.account_number And ( date_format(Transaction_Date , ‘%Y-%m’) <= “2020-03” );

Print account_number, Event adn Customer_message from BANK_CUSTOMER_MESSAGES and Bank_Account_Details to display an “Adhoc” 

# Event for all customers who have  “SAVINGS” account_type account.

1 2 3 4 5

SELECT Account_Number, Event , Customer_message FROM Bank_Account_Details CROSS JOIN BANK_CUSTOMER_MESSAGES ON Event  = “Adhoc”  And ACCOUNT_TYPE = “SAVINGS”;

Print Customer_id, Account_Number, Account_type, and display deducted balance_amount by  

# subtracting only negative transaction_amounts for Relationship_type = “P” ( P – means  Primary , S – means Secondary )

1 2 3 4 5 6 7 8 9

SELECT     ba.Customer_id,     ba.Account_Number,        (Balance_amount + IFNULL(transaction_amount, 0)) deducted_balance_amount    FROM Bank_Account_Details ba LEFT JOIN bank_account_transaction bat ON ba.account_number = bat.account_number AND Relationship_type = “P”;

Display records of All Accounts, their Account_types, the transaction amount.

# b) Along with the first step, Display other columns with the corresponding linking account number, account types 

1 2 3 4 5 6 7

SELECT  br1.Account_Number primary_account ,         br1.Account_type primary_account_type,         br2.Account_Number Seconday_account,         br2.Account_type Seconday_account_type FROM `bank_account_relationship_details` br1  LEFT JOIN `bank_account_relationship_details` br2 ON br1.account_number = br2.linking_account_number;

Display records of All Accounts, their Account_types, the transaction amount.

# b) Along with the first step, Display other columns with corresponding linking account number, account types 

# c) After retrieving all records of accounts and their linked accounts, display the transaction amount of accounts appeared in another column.

1 2 3 4 5 6 7 8 9 10

SELECT br1.Account_Number primary_account_number , br1.Account_type         primary_account_type, br2.Account_Number      secondary_account_number, br2.Account_type        secondary_account_type,  bt1.Transaction_amount   primary_acct_tran_amount from bank_account_relationship_details br1 LEFT JOIN bank_account_relationship_details br2 on br1.Account_Number = br2.Linking_Account_Number LEFT JOIN bank_account_transaction bt1 on br1.Account_Number  = bt1.Account_Number;

Display all saving account holders have “Add-on Credit Cards” and “Credit cards” 

1 2 3 4 5 6 7 8 9

SELECT  br1.Account_Number  primary_account_number , br1.Account_type  primary_account_type, br2.Account_Number secondary_account_number, br2.Account_type secondary_account_type from bank_account_relationship_details br1 JOIN bank_account_relationship_details br2 on br1.Account_Number = br2.Linking_Account_Number and br2.Account_type like ‘%Credit%’ ;

That covers the most asked or SQL practised questions.

Frequently Asked Questions in SQL

1. How do I prepare for the SQL interview?

There are many sources online that can help you prepare for an SQL interview. You can go through brief tutorials and free online courses on SQL (eg.: SQL basics on Great Learning Academy) to revise your knowledge of SQL. You can also practice projects to help you with practical aspects of the language. Lastly, there are many blogs such as this that list out all the probable questions that an interviewer might ask. 

2. What are the 5 basic SQL commands?

The five basic SQL commands are:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Data Query Language (DQL)

3. What are basic SQL skills?

SQL is a vast topic and there is a lot to learn. But the most basic skills that an SQL professional should know are:

• How to structure a database
• Managing a database
• Authoring SQL statements and clauses
• Knowledge of popular database systems such as MySQL
• Working knowledge of PHP
• SQL data analysis
• Creating a database with WAMP and SQL

4. How can I practice SQL?

There are some platforms available online that can help you practice SQL such as SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others. Also take up a Oracle SQL to learn more.

5. Where can I practice SQL questions?

There are some platforms available online that can help you practice SQL such as SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others. 

You can also refer to articles and blogs online that list the most important SQL interview questions for preparation.

6. What is the most common SQL command?

Some of the most common SQL commands are:

• CREATE DATABASE 
• ALTER DATABASE
• CREATE TABLE
• ALTER TABLE 
• DROP TABLE
• CREATE INDEX
• DROP INDEX

7. How are SQL commands classified?

SQL Commands are classified under four categories, i.e.,

• Data Definition Language (DDL)
• Data Query Language (DQL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)

8. What are basic SQL commands?

Basic SQL commands are:

• CREATE DATABASE 
• ALTER DATABASE
• CREATE TABLE
• ALTER TABLE 
• DROP TABLE
• CREATE INDEX
• DROP INDEX

9. Is SQL coding?

Yes, SQL is a coding language/ programming language that falls under the category of domain-specific programming language. It is used to access relational databases such as MySQL.

10. What is SQL example?

SQL helps you update, delete, and request information from databases. Some of the examples of SQL are in the form of the following statements:

• SELECT 
• INSERT 
• UPDATE
• DELETE
• CREATE DATABASE
• ALTER DATABASE 

11. What is SQL code used for?

SQL code is used to access and communicate with a database. It helps in performing tasks such as updating and retrieving data from the databases.

Top SQL Interview Questions – Beginners

1. What do you understand by database, and what does it have?

A database can be defined as the structured form of data storage from which data can be retrieved and managed based on requirements. Basically, a database consists of tables where data is stored in an organized manner. Each table consists of rows and columns to store data. Data can be stored, modified, updated, and accessed easily in a database. For instance, a bank management database or school management database are a few examples of databases.

2. What are DBMS and RDBMS?

DBMS – Database Management System.

DBMS is the software that allows storing, modifying, and retrieving data from a database. And it is a group of programs that act as the interface between data and applications. DBMS supports receiving queries from applications and retrieving data from the database.

RDBMS – Relational Database Management System

Like DBMS, RDBMS is also the software that allows storing, modifying, and retrieving data from a database but a RELATIONAL database. In a relational database, the data in the tables have a relationship. Besides, RDBMS is useful when data in tables are being managed securely and consistently.

3. What are Query and Query language?

A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.

Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language. SQL returns data as columns and rows in a table, whereas other languages return data in other forms, like graphs, charts, etc.

4. What do you mean by subquery?

It is a query that exists inside the statements such as SELECT, INSERT, UPDATE, and DELETE. It may exist inside a subquery too. A subquery is also known as an inner query or inner select. The statement with a subquery is an outer query or outer select.

Let’s see the example shown below in which the maximum unit price is the result that will be returned by the subquery using the SELECT statement. Also, orders is the value that will be returned by the outer query using the SELECT statement.

5. What is SQL, and mention its uses?

SQL – Structured Query Language

SQL is known as the query programming language. It uses SQL queries to store, modify and retrieve data into and from databases. Briefly, SQL inserts, updates, and deletes data in databases; creates new databases and new tables; creates views and stored procedures; and sets permissions on the database objects.

6. What is Dynamic SQL, and when can you use it?

Dynamic SQL is the programming method that allows building SQL statements during runtime. You can use dynamic SQL when you do not know the full text of the SQL statements used in the program until runtime. Moreover, dynamic SQL can execute SQL statements that are not supported by static SQL programs. So, Dynamic SQL helps to build more flexible applications.

7. What do you understand by tables and fields in a database?

Tables are the database objects where data is stored logically. Like a spreadsheet, data is stored in the form of rows and columns in a database table. A row in a table represents a record, and columns represent the different fields. Fields have the data types such as text, dates, numbers, and links.

For example, consider the below customer database in which rows consist of the company names and columns consist of the various details of customers like first name, last name, age, location, etc. Here, number 1 indicates a record, number 2 indicates a field, and number 3 indicates the field value. 

8. What are the different types of tables used in SQL?

The following are the table types used in SQL:

• Partitioned tables
• Temporary tables
• System tables
• Wide tables

9. What are temporary tables?

Temporary tables only store data during the current session, and they will be dropped once the session is over. With temporary tables, you can create, read, update and delete records like permanent tables. Know that there are two types of temporary tables: local and global temporary tables.

Local temporary tables are only visible to the user who created them, and they are deleted the moment the user disconnects from the instance of the SQL server.

On the contrary, global temporary tables are visible to all users, and they are deleted only when all the users who reference the tables get disconnected.

10. What do you mean by Primary Key and Foreign Key in SQL?

Primary Key: A primary is a field or combination of many fields that help identify records in a table. Note that there can be only one primary key for a table. The table that has the primary key is known as the parent table.

Foreign Key: A foreign key is the field or combination of fields of a table that links the primary key of another table. A foreign key is used to create a connection between two tables. Unlike a primary key, a table can have one or many foreign keys. The table that has a foreign key is known as the child table.

For example, customer ID (1) is the primary key of the Customers table, and customer ID (2) in the orders table is identified as the foreign key to the customer’s table. 

11. What are Superkey and candidate key?

A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.

A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.

Note that all the candidate keys can be Super keys, but all the super keys cannot be candidate keys.

12. What are composite keys?

A composite key is the combination of two or more columns in a table used to identify a row in a table. Know that a combination of columns is essential in creating composite keys because a single column in a composite key cannot identify a row in a table. We can say that the composite key is the primary key with a few more attributes or columns. Also, a composite key can be a combination of candidate keys.

13. What is JOIN operation in SQL, and mention their types?

JOIN is the logical operation used to retrieve data from two or more tables. It can be applied only when there is a logical relationship between two tables. Moreover, the JOIN operator uses the data of one table to retrieve data from another table.

Following are the different types of logical operations:

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

14. What do you mean by Self Join?

In self-join operation, a table is joined with itself to retrieve the desired data. Every join operation needs two tables as a basic rule. Therefore, in self-join, a table is joined with an instance of the same table. By doing this, values of the two table columns are compared with each other, and the desired data is retrieved as the result set.

15. What do you mean by Cross Join?

Cross Join is basically the Cartesian product type in which each row in a table is paired with all the rows of another table. So, the result set will be the paired combinations of the rows of two tables. Generally, cross join is not preferred by developers as it increases complexity in programs when there are many rows in tables. But, it can be used in queries if you identify normal join operation won’t be effective for your query.

16. What are the SQL constraints?

 SQL constraints specify conditions for a column or table to manage the data stored in tables effectively.

 The following are the commonly used SQL constraints.

• NOT NULL – This condition ensures columns won’t accept a NULL value.
• UNIQUE – It ensures that all the values in a column must be unique.
• CHECK – It ensures that all the column fields obey a specific condition.
• DEFAULT – It provides a default value for the fields of a column unless no value is specified for the fields
• CREATE INDEX – It ensures creating an index for tables so that retrieving data from the tables becomes easier
• PRIMARY KEY – It must identify every row of a table
• FOREIGN KEY –  It must link tables based on common attributes

17. What are local and global variables?

Local variables are declared inside a function so that only that function can call them. They only exist until the execution of that specific function. Generally, local variables are stored in stack memory and cleaned up automatically.

Global variables are declared outside of a function. They are available until the execution of the entire program. Unlike local variables, global variables are stored in fixed memory and not cleaned up automatically.

18. What is an index in SQL, and mention its types?

An index is used to retrieve data from a database quickly. Generally, indexes have keys taken from the columns of tables and views. We can say, SQL indexes are similar to the indexes in books that help to identify pages in the books quickly.

There are two types of indexes:

• Clustered indexes
• Non-clustered indexes

19. Mention the different types of SQL commands or SQL subsets?

There are five types of SQL commands offered in SQL. They are given as follows;

• DDL – Data Definition Languages
• DML – Data Manipulation Languages
• DCL – Data Control Language
• TCL – Transaction Control Language
• DQL – Data Query Language

20. What are the Various Commands used in SQL Subsets?

DDL	CREATE, DROP, ALTER, TRUNCATE, ADD COLUMN, and DROP COLUMN
DML	INSERT, DELETE, and UPDATE
DCL	GRANT and REVOKE
TCL	COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION
DQL	SELECT

21. Can you brief me on a few DDL Commands?

ALTER	This command allows changing the structure of a table
CREATE	It allows the creation of database objects such as tables, views, and indexes.
DROP	This command allows removing database objects from a database
TRUNCATE	This command helps to delete all the rows of a table permanently.

22. Can you brief the DML Commands?

INSERT	This command allows inserting a data into a table of a database
DELETE	This command allows deleting specific rows from a table
UPDATE	This command allows modifying a data in a table

23. Can you brief me on a few DCL Commands?

GRANT	This command can be used to share a database with other users. All the database objects can be granted access with certain rights to users.
REVOKE	This command can be applied if you want to restrict the access of database objects by other users.

24. Can you brief me about TCL commands?

COMMIT	This command allows for saving the transactions made in a database.
ROLLBACK	This command helps undo the transactions made in a database with the condition that the transactions shouldn’t be saved yet.
SAVEPOINT	This command helps to roll the transactions up to a certain point but not the entire transaction.

25. What are Stored Procedures?

It is a function that consists of a group of statements that can be stored and executed whenever it is required. Know that stored procedures are compiled only once. They are stored as ‘Named Object’ in the SQL server database. Stored procedures can be called at any time during program execution. Moreover, a stored procedure can be called another stored procedure.

Explore Oracle PL SQL Interview Questions

26. What are the SQL database functions?

SQL offers the flexibility to developers to use built-in functions as well as user-defined functions.

The functions are categorized as follows:

• Aggregate functions: They process a group of values and return a single value. They can combine with GROUP BY, OVER, HAVING clauses and return values. They are deterministic functions.
• Analytic functions: They are similar to aggregate functions but return multiple rows as result set after processing a group of values. They help calculate moving averages, running totals, Top-N results, percentages, etc.
• Ranking functions: They return ranking values for rows in a table based on the given conditions. Here, the results are non-deterministic.
• Rowset functions: They return an object used as the table reference.
• Scalar functions: They operate on a single value and return a single value.

27. Mention the different types of operators used in SQL?

There are six types of operators used in SQL. They are given as follows:

Arithmetic Operators	Addition, Subtraction, Multiplication, Division, and Remainder/Modulus
Bitwise Operators	Bitwise AND, Bitwise OR, Bitwise XOR, etc.
Comparison Operators	Equal to, Not equal to, Greater than, Not greater than, Less than, Not less than, Not equal to, etc.
Compound Operators	Add equals, Multiply equals, Subtract equals, Divide equals, and Modulo equals
Logical Operators	ALL, ANY/SOME, AND, BETWEEN, NOT, EXISTS, OR, IN, LIKE, and ISNULL
String Operators	String concatenation, wildcard, character matches, etc.

28. What are the Set Operators?

There are four types of set operators available in SQL. They are given as follows:

Union	This operator allows combining result sets of two or more SELECT statements.
Union All	This operator allows combining result sets of two or more SELECT statements along with duplicates.
Intersect	This operator returns the common records of the result sets of two or more SELECT statements.
Minus	This operator returns the exclusive records of the first table when two tables undergo this operation.

29. What do you mean by buffer pool and mention its benefits?

A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server. The number of pages that can be stored in a buffer pool depends on its size.

The following are the benefits of a buffer pool:

•  Increase in I/O performance
•  Reduction in I/O latency
• Increase in transaction throughput
•  Increase in reading performance

30. What are Tuple and tuple functions?

A tuple is a single row in a table that represents a single record of a relation. A tuple contains all the data that belongs to a record. At the same time, tuple functions allow retrieving tuples from a database table. They are extensively used in analysis services that have multidimensional structures.

For example, the highlighted row in the below table shows all the data belonging to a customer, which is nothing but a tuple.

Customer Name	Phone Number	Email Address	Postal Address
Naren	123 -456 -789	xyz@gmail.com	PO No:123, New Delhi
Raman	234 -567 -891	abc@gmail.com	PO No:143, Mumbai
Krishna	345 -678 -912	pqr@gmail.com	PO No:443, Hyderabad

31. What do you mean by dependency and mention the different dependencies?

Dependency is the relation between the attributes of a table. The following are the different types of dependencies in SQL.

• Functional dependency
• Fully-functional dependency
• Multivalued dependency
• Transitive dependency
• Partial dependency

32. What do you mean by Data Integrity?

Data integrity ensures the accuracy and consistency of data stored in a database. Data integrity, in a way, represents the data quality. So, the data characteristics defined for a column should be satisfied while storing data in the columns. For instance, if a column in a table is supposed to store numeric values, then it should not accept Alphabetic values; otherwise, you can mean that data integrity is lost in the table.

33. What is Database Cardinality?

Database Cardinality denotes the uniqueness of values in the tables. It supports optimizing query plans and hence improves query performance. There are three types of database cardinalities in SQL, as given below:

• Higher Cardinality
• Normal Cardinality
•  Lower Cardinality

34. What are database Normalisation and various forms of Normalisation?

It is the process that reduces data redundancy and improves data integrity by restructuring the relational database.

The following are the different forms of normalization:

• First normal form – 1NF
• Second normal form – 2 NF
• Third normal form – 3 NF
• Boyce Codd Normal Form/Fourth Normal form – BCNF/4NF

35. What is Cursor, and how to use it?

In general, the result set of a SQL statement is a set of rows. If we need to manipulate the result set, we can act on a single row of the result set at a time. Cursors are the extensions to the result set and help point a row in the result set. Here, the pointed row is known as the current row.

Cursors can be used in the following ways:

• Positions a row in a result set
• Supports retrieving the current row from the result set
• Supports data modifications in the current row
• Allowing  SQL statements in stored procedures, scripts, and triggers to access the result set

36. Mention the different types of Cursors?

• Forward Only: It is known as the firehose cursor that can make only a forward movement. The modification made by the current user and other users is visible while using this cursor. As it is the forward-moving cursor, it fetches rows of the result set from the start to end serially.
• Static: This cursor can move forward and backward on the result set. Here, only the same result set is visible throughout the lifetime of the cursor. In other words, once the cursor is open, it doesn’t show any changes made in the database that is the source for the result set.
• Keyset: This cursor is managed by a set of identifiers known as keys or keysets. Here, the keysets are built by the columns that derive the rows of a result set. When we use this cursor, we can’t view the records created by other users. Similarly, if any user deletes a record, we can’t access that record too.
• Dynamic: Unlike static cursors, once the cursor is open, all the modifications performed in the database are reflected in the result set. The UPDATE, INSERT and DELETE operations made by other users can be viewed while scrolling the cursor.

37. What are Entity and Relationship?

Entities are real-world objects that are individualistic and independent. Rows of a table represent the members of the entity, and columns represent the attributes of the entity. For instance, a ‘list of employees of a company is an entity where employee name, ID, address, etc., are the attributes of the entity.

 A relationship indicates how entities in a database are related to each other. Simply put, how a row in a table is related to row(s) of another table in a database. The relationship is made using the primary key and the foreign key primarily.

There are three types of relationships in DBMS, as mentioned below:

• One-to-one relationship
•  One-to-many relationship
• Many-to-many relationship

38. What is a Trigger, and mention its various types?

Triggers are nothing but they are special stored procedures. When there is an event in the SQL server, triggers will be fired automatically.

There are three types of triggers – LOGON, DDL, and DML.

             LOGON triggers: They get fired when a user starts a Logon event

           DDL triggers: They get fired when there is a DDL event

          DML Triggers: They get fired when there is a modification in data due to DML

39. What is Schema in SQL, and mention its advantages?

The schema represents the logical structures of data. Using schemas, the database objects can be grouped logically in a database. Schema is useful for segregating database objects based on different applications, controlling access permissions, and managing a database’s security aspects. Simply out, Schemas ensure database security and consistency.

Advantages:

• Schemas can be easily transferred
• You can transfer database objects between schemas
• It protects database objects and achieves effective access control

40. What are the types of UDFs?

There are three types of UDFs. They are defined as follows:

• User-defined scalar functions
• Table-valued functions
• System functions

41. What is the difference between char and varchar data types?

Char data type is a fixed-length data type in which the length of the character cannot be changed during execution. It supports storing normal and alphanumeric characters.

 On the other hand, varchar is the variable-length data type in which the length of the character can be changed during execution. That’s why, it is known as a dynamic data type.

42. Mention the Aggregate Functions used in SQL?

The following aggregate functions are used in SQL.

• COUNT
•  SUM
• AVG
• MAX
• MIN

43. What are Case Manipulation Functions used in SQL?

The following are the case manipulation functions used in SQL.

• LOWER
• UPPER
• INITCAP

44. What are Character Manipulation Functions used in SQL?

The following are the character manipulation functions used in SQL.

• CONCAT
• SUBSTR
• LENGTH
• INSTR
• LPAD
• RPAD
• TRIM
• REPLACE

45. How would you differentiate single-row functions from multiple-row functions?

Single row functions can act on a single row of a table at a time. They return only one result after executing a row. Length and case conversions are known to be single-row functions.

Multiple row functions can act on multiple rows of a table at a time. They are also called group functions and return a single output after executing multiple rows.

Experienced:

46. What is the difference between SQL and NoSQL?

SQL	No SQL
Works on relational databases	Works on non-relational databases
Stores data in tables based on schemas so that data are organized and structured	No specific method is followed for data storage, so it offers flexibility in storing data.
Easy to execute complex queries	Difficult to execute complex queries
Scaling is performed vertically increasing the processing power of servers	Scaling is performed horizontally adding more servers and nodes
SQL satisfies ACID Properties such as atomicity, consistency, isolation, and durability.	Follows CAP theory – according to this, any two of the following need to be satisfied – Consistency, Availability, and Partition tolerance.

 47. What is the difference between SQL and MySQL?

SQL	MySQL
It is the programming language	It is the RDMS – Relational Database Management System
It is used for querying relational database systems	It is used to store, modify and delete data in a database in an organized way.
It is a licensed product of Microsoft.	It is an open-source platform managed by Oracle corporation
It provides adequate protection to SQL servers against intruders	As it is an open-source platform, security cannot be reliable
It doesn’t support any connectors	Support connectors such as the Workbench tool to build databases

48. What is the difference between Index and View?

Generally, an index is created in a separate table. They are the pointers that indicate the address of data in a database table. An index helps speed up querying and the data retrieval process in a database.

On the other hand, a view is a virtual table created from the rows and columns of one or more tables. The main thing about a view is that the rows and columns are grouped logically. With the support of views, you can restrict access to the entire data in a database.

49. What is the use of Views, and mention its types in SQL?

Views are the virtual database tables created by selecting rows and columns from one or more tables in a database. They support developers in multiple ways, such as simplifying complex queries, restricting access to queries, and summarising data from many tables.

There are two types of views, as mentioned below:

• System-defined views: They can be used for specific purposes and perform specific actions only. It provides all the information and properties of databases and tables.
• User-defined views: They are created as per the requirements of users. They are routines that accept parameters, perform complex functions, and return a value.

50. Compare: LONG and LOB Data types

LONG Datatype	LOB Datatype
helps store large scale semi-structured and unstructured data	Known as Large Objects. It is used to store large size data
Stores up to 2GB of data	Can store up to 4GB  of data
Difficult to maintain	Supports manipulating and accessing data easily
A table can have only one LONG column	A table can have multiple LOB columns where LOB type data is stored
Subqueries cannot select LONG data types	Subqueries can select LOB datatypes
Access data only sequentially	Access data randomly

51. What is the difference between Zero and NULL values in SQL?

When a field in a column doesn’t have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.

52. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN	OUTER JOIN
It is the intersection of two tables	It is the union of two tables
Only retrieves rows that are common to two tables	Retrieves the rows common to two tables and all the values of one table

53. What are Database Testing and its benefits?

Database testing is also known as back-end testing. It consists of the SQL queries executed to validate database operations, data structures, and attributes of a database. It helps to ensure the data integrity by eliminating duplicate entries of data in a database, failing which will create many problems while managing the database. Besides, it deals with testable items hidden and not visible to users.

54. What is Database Black box testing?

Blackbox testing helps to examine the functionality of a database. It is performed by validating the integration level of a database. The incoming and outgoing data are verified by various test cases such as the cause-effect graphing technique, equivalence partitioning, and boundary value analysis. This kind of testing can be performed at the early stages of development to ensure better performance.

55. What is the use of Defaults in SQL?

In a database, default values are substituted when no value is assigned to a field in a table column. Basically, each column can be specified with a default value. In this way, SQL server management studio specifies default values, which can be created only for the current databases. Note that if the default value exceeds the size of the column field, it can be truncated.

56. What is SQL Injection, and how to avoid it?

SQL injection is a malicious attack sent targeting an SQL server instance. It is usually sent through strings of statements and passed into the SQL server for execution. To avoid SQL injection, all statements must be verified for malicious vulnerabilities before allowing for execution.

In addition to that, the following methods can be applied to avoid SQL injections. They are given as follows:

• Using type-safe SQL parameters
• Using parameterized input with stored procedures
• Filtering inputs
• Reviewing codes
• Wrapping parameters

57. What do you mean by Autonomous Transaction?

An autonomous transaction is an independent transaction initiated by a transaction that is the main transaction. Autonomous transaction holds the main transaction, performs SQL operations, and commits or rolls back. After that, it resumes the main transaction. Note that autonomous transaction doesn’t share locks and resources with the main transaction.

58. Write the SQL statements that can be used to return even number records and odd number records?

You can use the following statement to retrieve even number records from a table.

SELECT * from table where id % 2 = 0

You can use the following statement to retrieve odd number records from a table.

SELECT * from table where id % 2 ! = 0

59. What is Alias in SQL?

SQL aliases help to assign temporary names for a table or column. It is used to simplify table or column names. And aliases can exist only for that query period. It can be created using the ‘AS’ keyword. Know that creation of an alias is in no way affecting the column names in the database. It can be applied when more than one table is involved in a query.

60. What is the difference between OLAP and OLTP?

OLAP is known as Online Analytical Processing. It consists of tools used for data analysis that will be used for making better decisions. It can work on multiple database systems’ historical data and provide valuable insights. For example, NETFLIX and SPOTIFY generate insights from past data.

On the other hand, OLTP is known as Online Transaction Processing, and it works on operational data. OLTP manages ACID properties during transactions. Specifically, it performs faster than OLAP so that it can be used in online ticket booking, messaging services, etc.

61. What do you mean by Data Inconsistency?

Data inconsistency occurs when the same data exists in many tables in different formats. In other words, the same information about an object or person may be spread across the database in various places creating duplication. It decreases the reliability of the data and decreases the query performance significantly. To overcome this drawback, we can use constraints on the database.

62. What do you mean by Collation in SQL?

Collation allows to sort and compare data with pre-defined rules. These rules help to store, access and compare data effectively. The collation rules are applied while executing insert, select, update and delete operations. SQL servers can store objects that have different collations in a single database. Note that collation offers case-sensitivity and accent sensitivity for datasets.

63. How to create a table from an existing table?

A copy of a table can be created from an existing table using the combination of CREATE and SELECT statements. Using these statements, you can select all the columns or specific columns from an existing table. As a result, the new table will be replaced with all the values of the existing table. Here, the WHERE clause can select the specific columns from the table.

The syntax for this type of table creation is given below:

CREATE TABLE NEW_TABLE_NAME1 AS                 SELECT [column1,column2,…..columnN]                 FROM EXISTING_TABLE_NAME1                 [WHERE]

64. How to fetch common records from two tables?

We can fetch common records using INTERSECT commands in SQL. The main thing about this statement is that it returns only the common records. It means that this statement helps to eliminate duplication of data.

The syntax for this statement is given as below:

SELECT CustomerID              FROM Sales. customers INTERSECT               SELECT CustomerID               FROM Sales. Orders               WHERE Month (Orderdate) = December;

65. What are the common clauses used with SELECT Statements?

The common clauses such as FOR, ORDER BY, GROUP BY, and HAVING are used with SELECT statements.

• FOR Clause – it specifies the different formats for viewing result sets such as browser mode cursor, XML, and JSON file.
• ORDER BY Clause – It sorts the data returned by a query in a specific order. It helps to determine the order for ranking functions.
• GROUP BY Clause – It groups the result set of the SELECT statement. It returns one row per group.
• HAVING Clause – It is used with the GROUP BY clause and specifies a search condition for a group.

66. What is COALESCE and describe any two properties of COALESCE functions?

COALESCE is an expression that evaluates arguments in a list and only returns the non-NULL value.

For example, consider the following statement:

SELECT COALESCE (NULL, 14, 15);

This statement will return 14 after the execution since the first value is the NULL in this argument list.

                        Properties of COALESCE function:

• The datatype must be the same
• It functions as a syntactic shortcut for the case expression

67. What is the use of the MERGE statement?

MERGE allows combining the INSERT, DELETE and UPDATE functions altogether. This statement can be applied when two statements have complex matching characteristics. Though the MERGE statement seems to be complex, it provides much more advantages to developers when they get familiar with this statement. It reduces I/O operations significantly and allows to read data only from the source.

68. What is the use of CLAUSE in SQL?

Clauses are nothing but they are the built-in functions of SQL. They help to retrieve data very quickly and efficiently. Clauses are much-needed for developers when there is a large volume of data in a database. The result set of clauses would be a pattern, group, or an ordered format.

The following are the various clauses used in SQL:

• WHERE Clause
• OR Clause
• And Clause
• Like Clause
• Limit Clause
• Order By
• Group By

69. How to change a table name in SQL?

If you need to rename a table name in SQL, you can use the RENAME OBJECT statement to achieve the same.

You have to execute the following steps to change a table name using SQL.

• First, connect to a database engine in Object Explorer
• Select a new query on the standard bar
• Then, write the query and execute it.

The following example will show the use of rename query.

Advanced SQL Interview Questions & Answers

70. What are the differences between SQL and PL/SQL?

SQL	PL/SQL
It is a Structured Query Language	It is a Procedural Language where SQL statements are processed effectively
Only a single operation can be performed at a time	A Group of operations as a single block can be performed at a time
SQL executes the queries such as creating tables, deleting tables, and inserting into tables.	It is used to write program blocks, functions, procedures, triggers, packages, and cursors.
Mainly, it is used to retrieve data from databases and modify tables.	Used for creating web applications and server pages
Processing speed is low	It has the excellent processing speed

71. What are the advantages of PL/SQL functions?

• It has tight interaction with SQL.
• It has high performance and productivity.
• It has high portability and scalability.
•  It is highly flexible and secure.
• Supports developing web applications and server pages

72. Differentiate: CHAR and VARCHAR data types in SQL?

CHAR	VARCHAR
It is a fixed-length character string data type	It is a variable-length character string data type.
The data type can be a single byte or multiple-byte	It can accept character strings up to 255 bytes
This data type can be used when the character length is known	This data type is used when the character length is not clear
It uses static memory location	It uses dynamic memory location
This is used when the character length of the data is the same.	This is used when the character length of the data is variable.

73. How can you avoid Duplicate Keys in SQL?

We can eliminate duplicate keys in SQL by using the following methods:

• Using INSERT INTO SELECT
•  Using WHERE NOT IN
• Using WHERE NOT EXISTS
• Using IF NOT EXISTS
• Using COUNT(*)=0

74. Brief the factors that affect the functionalities of databases?

The following five factors affect the functionalities of databases.

• Workload
• Throughput
• Resources
• Optimization
• Contention

75. List out the factors that affect the query performance?

The following are the factors that affect the performance of queries.

• Number of nodes, processors, or slices
• Node types
• Data distribution
• Data sort order
• Dataset size
• Concurrent operations
• Query structure
• Code compilation

76. Differentiate: UNION and INTERSECT statements?

UNION: It is the operator that returns a single result set for two separate queries. And this operator functions based on specific conditions.

Syntax: query 1 UNION query2

INTERSECT: It is the operator that returns only the distinct rows from two separate queries.

Syntax: query 1 INTERSECT query2

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

DROP	TRUNCATE
It removes a whole database	It removes a table or data or index
All the constraints will be removed after the execution of the DROP function.	Constraints don’t get affected because of the execution of this statement
The structure of the data also will be removed	The structure of the data won’t get affected
It is a slow process	It is faster than the DROP statement

78. What is the use of the SELECT DISTINCT statement?

This statement is used to select distinct values from a table. The table might consist of many duplicate records, whereas this statement helps to return only the distinct values.

The syntax for the statement is given as follows;

SELECT DISTINCT column1, column2, FROM table_name1;

79. How can you differentiate the RANK and DENSE_RANK functions?

Both RANK and DENSE_RANK are used as the ranking functions, which perform ranking of data based on specific conditions. When the RANK statement is executed, it returns a ranking of values of a table based on specific conditions. At the same time, the result set up skip positions in the ranking if there are the same values. Simply put, there will be a discontinuity in the numbering of ranking. On the other hand, when the RANK_DENSE function is executed, it doesn’t skip any position in the ranking of values even though there are the same values present in the table. It returns continuous numbering of ranking.

The following example will explain the use of the RANK and DENSE_RANK functions.

80. What is the difference between IN and BETWEEN operators?

Both IN and BETWEEN operators are used to return records for multiple values from a table. The IN operator is used to return records from a table for the multiple values specified in the statement. On the other side, BETWEEN operator is used to return records within a range of values specified in the statement.

             Syntax for the IN statement is given as:

SELECT * FROM table_name1 WHERE column_name1 IN (value 1,value2)

The syntax for the BETWEEN statement is given as:

SELECT * FROM table_name1 WHERE column_name1 BETWEEN ‘value 1’ AND ‘value2’

81. Compare: STUFF and REPLACE statements?

Both STUFF and REPLACE statements are used to replace characters in a string. The STUFF statement inserts the specific characters in a string replacing existing characters. In comparison, the REPLACE statement replaces existing characters with specific characters throughout the string.

For example, consider the following examples:

For the STUFF statement;

SELECT STUFF (‘raman’,2,3,’aja’) Output: rajan

For the REPLACE statement;

SELECT REPLACE (‘ramanathan’,’an’,’ar’) Output: ramarathar

82. What do you mean by COMMIT in SQL?

COMMIT statement allows saving the changes made in a transaction permanently. Once a transaction is committed, the previous values cannot be retrieved.

The following syntax is used for this operation:

SELECT * FROM Staff WHERE incentive = 1000; sql>COMMIT;

83. What is the use of the GRANT Statement?

This statement grants permissions for users to perform operations such as SELECT, UPDATE, INSERT, DELETE, or any other operations on tables and views.

For example, if you would like to provide access to a user for updating tables, then the following statement must be used. In addition, the user too can grant permissions to other users.

GRANT UPDATE ON table_name TO user_name WITH GRANT OPTION

84. What is the difference between White Box Testing and Black Box Testing?

Black Box Testing	White Box Testing
The internal structure of the program is hidden from testers	Testers know the internal structure of the program
It is performed by software testers	It is performed by software developers
Testing is known as outer or external software testing	Testing is known as inner or internal software testing
Programming knowledge is not required for testers	Programming knowledge is a must for testers
Functional testing, non-functional testing, and regression testing are the types of black-box testing.	Path testing, loop testing, and condition testing are types of white box testing.

85. What do you mean by ETL in SQL?

ETL in SQL represents Extract, Transform and Load.

Extracting – It is about extracting data from the source, which can be a data warehouse, CRMs, databases, etc.

Transforming – It includes many processes such as cleansing, standardization, deduplication, verification, and sorting.

Loading – It is the process of loading the transformed data into the new destination. There are two types of loading data: full loading and incremental loading.

86. What do you mean by NESTED triggers?

If a trigger fires another trigger while being executed, it is known as a NESTED trigger. Nested triggers can be fired while executing DDL and DML operations such as INSERT, DROP and UPDATE. Nested triggers help to back up the rows affected by the previous trigger. There are two types of nested triggers: AFTER triggers and INSTEAD OF triggers.

87. How to insert multiple rows in a database table in SQL?

We can use the INSERT INTO statement to insert multiple rows in a database table in SQL.

The following syntax can be used for this case:

INSERT INTO table_name VALUES (value1, value), (value3, value4)…;

The inserted data can be selected using the following syntax:

SELECT * FROM table_name;

88. What do you mean by live-lock in SQL?

When two processes repeat the same type of interaction continually without making any progress in the query processing, it leads to a live-lock situation in the SQL server. There is no waiting state in live-lock, but the processes are happening concurrently, forming a closed loop.

 For example, let us assume process A holds a resource D1 and requests resource D2. At the same time, assume that process B holds a resource D2 and requests resource D1. This situation won’t progress any further until any of the processes should either drop holding a resource or drop requesting a resource.

89. What do you mean by Equi-JOIN and non-Equi-JOIN?

Equi-join creates a join operation to match the values of the relative tables. The syntax for this operation can be given as follows:

SELECT column_list FROM table1, table2,….. WHERE table1.column_name = table.2column_name;

On the other side, Non-Equi join performs join operations except equal. This operator works with <,>,>=, <= with conditions.

SELECT * FROM table_name1,table_name2 WHERE table_name1.column[>|<|>=|<=] table_name2.column;

90. What are the different types of SQL sandboxes?

There are three types of SQL sandboxes. They are given as follows:

• Safe access sandbox
• Unsafe access sandbox
• External access sandbox

91. What do you mean by lock escalation?

It is the process of converting row and page locks into table locks. Know that Reduction of lock escalation would increase the server performance. To improve performance, we need to keep transactions short and reduce lock footprints in queries as low as possible. Besides, we can disable lock escalation at the table and instance levels, but it is not recommended.

92. How can you update a table using SQL?

The UPDATE statement allows you to update a database table in SQL. After the execution, one or more columns in a table will be replaced by new values.

The syntax for the UPDATE statement is given as follows:

UPDATE table_name SET   Column1 = new_value1,   Column2 = new_value2,   ..….. WHERE    Condition;

This statement requires a table name, new values, and conditions to select the rows. Here, the WHERE statement is not mandatory. Suppose the WHERE clause is used, all the rows in a table will be updated by the new values.

93. How to create a Stored Procedure using T-SQL?

• Connect to the instance of a database engine in ‘object explorer’
• Click ‘new query’ from the ‘files menu’
• Copy and paste the following sample codes in the query window

USE AdventureWorks2012;  GO  CREATE PROCEDURE HR.GetEmployeesTest2       @LastName nvarchar(25),       @FirstName nvarchar(25)   AS       SET NOCOUNT ON      SELECT FirstName, LastName, Division      FROM HR.vEmployeeDivisionHistory       WHERE FirstName = @FirstName AND LastName = @LastName       AND EndDate IS NULL;  GO

• Now, execute the codes

You can use the following statement to run the newly created stored procedure.

EXECUTE HR.GetEmployeesTest2 N’Ackerman’, N’Pilar’;

94. What do you mean by DELETE CASCADE constraint?

When a foreign key is created under this option, and if a referenced row in the parent table is deleted, the referencing row(s) in a child table also gets deleted.

On similar tracks, when a referenced row is updated in a parent table, the referencing row(s) in a child table is also updated.

95. Explain the different types of indexes in SQL?

The following are the different types of indexes in SQL.

• Single-column indexes
• Unique indexes
• Composite indexes
• Implicit indexes

96. What do you mean by auto-increment?

It is a unique number that will be generated when a new record is inserted into a table. Mainly, it acts as the primary key for a table.

The following syntax is used for this purpose:

IDENTITY (starting_value, increment_value)

97. What do you mean by Pattern Matching?

We can use the LIKE command in SQL to identify patterns in a database using character strings. Generally, a pattern may be identified using wildcard characters or regular characters. So, pattern matching can be performed using both wildcard characters and string comparison characters as well. However, pattern matching through wildcard characters is more flexible than using string comparison characters.

98. What is the difference between blocking and deadlocking?

Blocking is a phenomenon that occurs when a process locks a resource ‘A’, and the same resource is requested by another process ‘B’. Now, process ‘B’ can access the resource ‘A’ only when process ‘A’ releases the lock. The process ‘B’ has to wait until the process ‘A’ releases the lock. The SQL server doesn’t interfere and stops any process in this scenario.

On the contrary, deadlocking is the phenomenon that occurs when a resource ‘A’ is locked by a process ‘A’ and the same resource is requested by another process ‘B’. Similarly, a resource ‘B’ is locked by process ‘B’ and requested by process A. This scenario causes a deadlock situation, and it is a never-ending process. So, the SQL server interferes and voluntarily stops any one of the processes to remove the deadlock.

99. What is the difference between COALESCE ( ) and ISNULL ( )?

COALESCE function returns the first value that is non-NULL in the expression, whereas ISNULL is used to replace the non-NULL values in the expression.

Syntax for COALESCE function is given as:

SELECT column(s),COALESCE (exp_1,…..,exp_n) FROM table_name;

Syntax for ISNULL is given as:

SELECT column(s),ISNULL(column_name,value_to_replace) FROM table_name;

100. What is the difference between NVL and the NVL (2) functions in SQL?

Both the functions are used to find whether the first argument in the expression is NULL. The NVL function in the SQL query returns the second argument if the first argument is NULL. Otherwise, it returns the first argument.

The NVL2 function in SQL query returns the third argument if the first argument is NULL. Otherwise, the second argument is returned.

What is SQL?

SQL stands for Structured Query Language. It is the standard language for RDBMS and is useful in handling organized data that has entities or variables with relations between them. SQL is used for communicating with databases.

According to ANSI, SQL is used for maintaining RDBMS and for performing different operations of data manipulation on different types of data by using the features of SQL. Basically, it is a database language that is used for the creation and deletion of databases. It can also be used, among other things, to fetch and modify the rows of a table.

4. What is normalization and its types?

Normalization is used in reducing data redundancy and dependency by organizing fields and tables in databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make normalization more flexible.

The different forms of normalization are: 

• First Normal Form: If every attribute in a relation is single-valued, then it is in the first normal form. If it contains a composite or multi-valued attribute, then it is in violation of the first normal form.
• Second Normal Form: A relation is said to be in the second normal form if it has met the conditions for the first normal form and does not have any partial dependency, i.e., it does not have a non-prime attribute that relies on any proper subset of any candidate key of the table. Often, the solution to this problem is specifying a single-column primary key.
• Third Normal Form: A relation is in the third normal form when it meets the conditions for the second normal form and there is not any transitive dependency between the non-prime attributes, i.e., all the non-prime attributes are decided only by the candidate keys of the relation and not by other non-prime attributes.
• Boyce-Codd Normal Form: A relation is in the Boyce-Codd normal form or BCNF if it meets the conditions of the third normal form, and for every functional dependency, the left-hand side is a super key. A relation is in BCNF if and only if X is a super key for every nontrivial functional dependency in form X –> Y.

5. What is denormalization?

Denormalization is the opposite of normalization; redundant data is added to speed up complex queries that have multiple tables that need to be joined. Optimization of the read performance of a database is attempted by adding or grouping redundant copies of data.

6. What are Joins in SQL?

Join in SQL is used to combine rows from two or more tables based on a related column between them. There are various types of Joins that can be used to retrieve data, and it depends on the relationship between tables.

There are four types of Joins:

• Inner Join
• Left Join
• Right Join
• Full Join

7. Explain the types of SQL joins.

There are four different types of SQL Joins:

• • (Inner) Join: It is used to retrieve the records that have matching values in both the tables that are involved in the join. Inner Join is mostly used to join queries.

• SELECT *
  • FROM Table_A
  • JOIN Table_B;
  • SELECT *
  • FROM Table_A

INNER JOIN Table_B;

• • Left (Outer) Join: Use of left join is to retrieve all the records or rows from the left and the matched ones from the right.

• SELECT *
  • FROM Table_A A
  • LEFT JOIN Table_B B

ON A.col = B.col;

• • Right (Outer) Join: Use of Right join is to retrieve all the records or rows from the right and the matched ones from the left.

• SELECT *
  • FROM Table_A A
  • RIGHT JOIN Table_B B

ON A.col = B.col;

• Full (Outer) Join: The use of Full join is to retrieve the records that have a match either in the left table or the right table.

• SELECT *
• FROM Table_A A
• FULL JOIN Table_B B

ON A.col = B.col;

Get 100% Hike!

Master Most in Demand Skills Now !

Top of Form

+1  US          UNITED STATES +44  UK          UNITED KINGDOM +1  CA          CANADA ——  —          ———————— +376  AD          ANDORRA +971  AE          UNITED ARAB EMIRATES +93  AF          AFGHANISTAN +1268  AG          ANTIGUA AND BARBUDA +1264  AI          ANGUILLA +355  AL          ALBANIA +374  AM          ARMENIA +599  AN          NETHERLANDS ANTILLES +244  AO          ANGOLA +672  AQ          ANTARCTICA +54  AR          ARGENTINA +1684  AS          AMERICAN SAMOA +43  AT          AUSTRIA +61  AU          AUSTRALIA +297  AW          ARUBA +994  AZ          AZERBAIJAN +387  BA          BOSNIA AND HERZEGOVINA +1246  BB          BARBADOS +880  BD          BANGLADESH +32  BE          BELGIUM +226  BF          BURKINA FASO +359  BG          BULGARIA +973  BH          BAHRAIN +257  BI          BURUNDI +229  BJ          BENIN +590  BL          SAINT BARTHELEMY +1441  BM          BERMUDA +673  BN          BRUNEI DARUSSALAM +591  BO          BOLIVIA +55  BR          BRAZIL +1242  BS          BAHAMAS +975  BT          BHUTAN +267  BW          BOTSWANA +375  BY          BELARUS +501  BZ          BELIZE +61  CC          COCOS (KEELING ISLANDS +243  CD          CONGO, THE DEMOCRATIC REPUBLIC OF THE +236  CF          CENTRAL AFRICAN REPUBLIC +242  CG          CONGO +41  CH          SWITZERLAND +225  CI          COTE D IVOIRE +682  CK          COOK ISLANDS +56  CL          CHILE +237  CM          CAMEROON +86  CN          CHINA +57  CO          COLOMBIA +506  CR          COSTA RICA +53  CU          CUBA +238  CV          CAPE VERDE +61  CX          CHRISTMAS ISLAND +357  CY          CYPRUS +420  CZ          CZECH REPUBLIC +49  DE          GERMANY +253  DJ          DJIBOUTI +45  DK          DENMARK +1767  DM          DOMINICA +1809  DO          DOMINICAN REPUBLIC +213  DZ          ALGERIA +593  EC          ECUADOR +372  EE          ESTONIA +20  EG          EGYPT +291  ER          ERITREA +34  ES          SPAIN +251  ET          ETHIOPIA +358  FI          FINLAND +679  FJ          FIJI +500  FK          FALKLAND ISLANDS (MALVINAS +691  FM          MICRONESIA, FEDERATED STATES OF +298  FO          FAROE ISLANDS +33  FR          FRANCE +241  GA          GABON +1473  GD          GRENADA +995  GE          GEORGIA +233  GH          GHANA +350  GI          GIBRALTAR +299  GL          GREENLAND +220  GM          GAMBIA +224  GN          GUINEA +240  GQ          EQUATORIAL GUINEA +30  GR          GREECE +502  GT          GUATEMALA +1671  GU          GUAM +245  GW          GUINEA-BISSAU +592  GY          GUYANA +852  HK          HONG KONG +504  HN          HONDURAS +385  HR          CROATIA +509  HT          HAITI +36  HU          HUNGARY +62  ID          INDONESIA +353  IE          IRELAND +972  IL          ISRAEL +44  IM          ISLE OF MAN +964  IQ          IRAQ +98  IR          IRAN, ISLAMIC REPUBLIC OF +354  IS          ICELAND +39  IT          ITALY +1876  JM          JAMAICA +962  JO          JORDAN +81  JP          JAPAN +254  KE          KENYA +996  KG          KYRGYZSTAN +855  KH          CAMBODIA +686  KI          KIRIBATI +269  KM          COMOROS +1869  KN          SAINT KITTS AND NEVIS +850  KP          KOREA DEMOCRATIC PEOPLES REPUBLIC OF +82  KR          KOREA REPUBLIC OF +965  KW          KUWAIT +1345  KY          CAYMAN ISLANDS +7  KZ          KAZAKSTAN +856  LA          LAO PEOPLES DEMOCRATIC REPUBLIC +961  LB          LEBANON +1758  LC          SAINT LUCIA +423  LI          LIECHTENSTEIN +94  LK          SRI LANKA +231  LR          LIBERIA +266  LS          LESOTHO +370  LT          LITHUANIA +352  LU          LUXEMBOURG +371  LV          LATVIA +218  LY          LIBYAN ARAB JAMAHIRIYA +212  MA          MOROCCO +377  MC          MONACO +373  MD          MOLDOVA, REPUBLIC OF +382  ME          MONTENEGRO +1599  MF          SAINT MARTIN +261  MG          MADAGASCAR +692  MH          MARSHALL ISLANDS +389  MK          MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF +223  ML          MALI +95  MM          MYANMAR +976  MN          MONGOLIA +853  MO          MACAU +1670  MP          NORTHERN MARIANA ISLANDS +222  MR          MAURITANIA +1664  MS          MONTSERRAT +356  MT          MALTA +230  MU          MAURITIUS +960  MV          MALDIVES +265  MW          MALAWI +52  MX          MEXICO +60  MY          MALAYSIA +258  MZ          MOZAMBIQUE +264  NA          NAMIBIA +687  NC          NEW CALEDONIA +227  NE          NIGER +234  NG          NIGERIA +505  NI          NICARAGUA +31  NL          NETHERLANDS +47  NO          NORWAY +977  NP          NEPAL +674  NR          NAURU +683  NU          NIUE +64  NZ          NEW ZEALAND +968  OM          OMAN +507  PA          PANAMA +51  PE          PERU +689  PF          FRENCH POLYNESIA +675  PG          PAPUA NEW GUINEA +63  PH          PHILIPPINES +92  PK          PAKISTAN +48  PL          POLAND +508  PM          SAINT PIERRE AND MIQUELON +870  PN          PITCAIRN +1  PR          PUERTO RICO +351  PT          PORTUGAL +680  PW          PALAU +595  PY          PARAGUAY +974  QA          QATAR +40  RO          ROMANIA +381  RS          SERBIA +7  RU          RUSSIAN FEDERATION +250  RW          RWANDA +966  SA          SAUDI ARABIA +677  SB          SOLOMON ISLANDS +248  SC          SEYCHELLES +249  SD          SUDAN +46  SE          SWEDEN +65  SG          SINGAPORE +290  SH          SAINT HELENA +386  SI          SLOVENIA +421  SK          SLOVAKIA +232  SL          SIERRA LEONE +378  SM          SAN MARINO +221  SN          SENEGAL +252  SO          SOMALIA +597  SR          SURINAME +239  ST          SAO TOME AND PRINCIPE +503  SV          EL SALVADOR +963  SY          SYRIAN ARAB REPUBLIC +268  SZ          SWAZILAND +1649  TC          TURKS AND CAICOS ISLANDS +235  TD          CHAD +228  TG          TOGO +66  TH          THAILAND +992  TJ          TAJIKISTAN +690  TK          TOKELAU +670  TL          TIMOR-LESTE +993  TM          TURKMENISTAN +216  TN          TUNISIA +676  TO          TONGA +90  TR          TURKEY +1868  TT          TRINIDAD AND TOBAGO +688  TV          TUVALU +886  TW          TAIWAN, PROVINCE OF CHINA +255  TZ          TANZANIA, UNITED REPUBLIC OF +380  UA          UKRAINE +256  UG          UGANDA +598  UY          URUGUAY +998  UZ          UZBEKISTAN +39  VA          HOLY SEE (VATICAN CITY STATE +1784  VC          SAINT VINCENT AND THE GRENADINES +58  VE          VENEZUELA +1284  VG          VIRGIN ISLANDS, BRITISH +1340  VI          VIRGIN ISLANDS, U.S. +84  VN          VIET NAM +678  VU          VANUATU +681  WF          WALLIS AND FUTUNA +685  WS          SAMOA +381  XK          KOSOVO +967  YE          YEMEN +262  YT          MAYOTTE +27  ZA          SOUTH AFRICA +260  ZM          ZAMBIA +263  ZW          ZIMBABWE

Bottom of Form

8. What are the subsets of SQL?

SQL queries are divided into four main categories:

• Data Definition Language (DDL)
  DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.
  • CREATE Creates databases, tables, schema, etc.
  • DROP: Drops tables and other database objects
  • DROP COLUMN: Drops a column from any table structure
  • ALTER: Alters the definition of database objects
  • TRUNCATE: Removes tables, views, procedures, and other database objects
  • ADD COLUMN: Adds any column to the table schema
• Data Manipulation Language (DML)
  These SQL queries are used to manipulate data in a database.
  • SELECT INTO: Selects data from one table and inserts it into another
  • INSERT: Inserts data or records into a table
  • UPDATE: Updates the value of any record in the database
  • DELETE: Deletes records from a table
• Data Control Language (DCL)
  These SQL queries manage the access rights and permission control of the database.
  • GRANT: Grants access rights to database objects
  • REVOKE: Withdraws permission from database objects
• Transaction Control Language (TCL)
  TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions. 
  • COMMIT: Commits an irreversible transaction, i.e., the previous image of the database prior to the transaction cannot be retrieved
  • ROLLBACK: Reverts the steps in a transaction in case of an error
  • SAVEPOINT: Sets a savepoint in the transaction to which rollback can be executed
  • SET TRANSACTION: Sets the characteristics of the transaction

9. What are the applications of SQL?

The major applications of SQL include:

• Writing data integration scripts
• Setting and running analytical queries
• Retrieving subsets of information within a database for analytics applications and transaction processing
• Adding, updating, and deleting rows and columns of data in a database

10. What is a DEFAULT constraint?

Constraints in SQL are used to specify some sort of rules for processing data and limiting the type of data that can go into a table. Now, let us understand what is a default constraint.

A default constraint is used to define a default value for a column so that it is added to all new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the following table and set the default value to 85000, then all the entries of this column will have the default value of 85000, unless no other value has been assigned during the insertion.

Now, let us go through how to set a default constraint. We will start by creating a new table and adding a default constraint to one of its columns.

Code:

create table stu1(s_id int, s_name varchar(20), s_marks int default 50)

select *stu1

Output:

Now, we will insert the records.

Code:

insert into stu1(s_id,s_name) values(1,’Sam’)

insert into stu1(s_id,s_name) values(2,’Bob’)

insert into stu1(s_id,s_name) values(3,’Matt’)select *from stu1

Output:

Also, learn from our blog on MySQL Interview Questions and Answers to crack any Interview.

11. What is a UNIQUE constraint?

Unique constraints ensure that all the values in a column are different. For example, if we assign a unique constraint to the e_name column in the following table, then every entry in this column should have a unique value.

First, we will create a table.

create table stu2(s_id int unique, s_name varchar(20))

Now, we will insert the records.

insert into stu2 values(1,’Julia’)

insert into stu2 values(2,’Matt’)

insert into stu2 values(3,’Anne’)

Output:

A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However, unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.

12. What is meant by table and field in SQL?

An organized data in the form of rows and columns is said to be a table. Simply put, it is a collection of related data in a table format.

Here rows and columns are referred to as tuples and attributes, and the number of columns in a table is referred to as a field. In the record, fields represent the characteristics and attributes and contain specific information about the data.

13. What is a primary key?

A primary key is used to uniquely identify all table records. It cannot have NULL values and must contain unique values. Only one primary key can exist in one table, and it may have single or multiple fields, making it a composite key.

Now, we will write a query for demonstrating the use of a primary key for the employee table:

//

CREATE TABLE Employee (

ID int NOT NULL,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255),

Employee_Age int,

PRIMARY KEY (ID)

);

14. What is a unique key?

The key that can accept only a null value and cannot accept duplicate values is called a unique key. The role of a unique key is to make sure that all columns and rows are unique.

The syntax for a unique key will be the same as the primary key. So, the query using a unique key for the employee table will be:

//

CREATE TABLE Employee (

ID int NOT NULL,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255),

Employee_Age int,

UNIQUE(ID)

);

15. What is the difference between primary key and unique key?

Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.

•  
•  

16. What is a foreign key?

A foreign key is an attribute or a set of attributes that reference the primary key of some other table. Basically, a foreign key is used to link together two tables.

Let us create a foreign key for the following table:

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

)

17. What are the subsets of SQL?

The main subsets of SQL are:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

18. Explain the different types of SQL commands.

• DDL: DDL is that part of SQL that defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are:
  • Create table
  • Alter table
  • Drop table
• DML: DML is used to manipulate already existing data in a database, i.e., it helps users to retrieve and manipulate data. It is used to perform operations such as inserting data into the database through the insert command, updating data with the update command, and deleting data from the database through the delete command.
• DCL: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.
• TCL: TCL is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction with logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

Also, Have a look at SQL Command Cheatsheet.

19. What are the usages of SQL?

The following operations can be performed by using SQL database:

• Creating new databases
• Inserting new data
• Deleting existing data
• Updating records
• Retrieving the data
• Creating and dropping tables
• Creating functions and views
• Converting data types

20. What is an index?

Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check each and every row to find matches, which may result in slow operations in large data.

Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches.

Syntax:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

21. Explain the types of indexes.

Single-column Indexes: A single-column index is created for only one column of a table.

Syntax:

CREATE INDEX index_name

ON table_name(column_name);

Composite-column Indexes: A composite-column index is created for two or more columns of a table.

Syntax:

CREATE INDEX index_name

ON table_name (column1, column2)

Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.

Syntax:

CREATE UNIQUE INDEX index

ON table_name(column_name)

Courses you may like

22. What are entities and relationships?

Entities: An entity can be a person, place, thing, or any identifiable object for which data can be stored in a database.

For example, in a company’s database, employees, projects, salaries, etc., can be referred to as entities.

Relationships: A relationship between entities can be referred to as a connection between two tables or entities.

For example, in a college database, the student entity and the department entities are associated with each other.

That ends the section of basic interview questions. Let us now move on to the next section of intermediate interview questions.

Intermediate SQL Interview Questions

23. What are SQL operators?

SQL operators are the special keywords or characters that perform specific operations. They are also used in SQL queries. These operators can be used within the WHERE clause of SQL commands. Based on the specified condition, SQL operators filter the data. 

The SQL operators can be categorized into the following types:

• Arithmetic Operators:For mathematical operations on numerical data 
  • addition (+)
  • subtraction (-)
  • multiplication (*)
  • division (/)
  • remainder/modulus (%)
• Logical Operators:For evaluating the expressions and return results in True or False
  • ALL
  • AND
  • ANY
  • ISNULL
  • EXISTS
  • BETWEEN
  • IN 
  • LIKE
  • NOT
  • OR 
  • UNIQUE
• Comparison Operators:For comparisons of two values and checking whether they are the same or not 
  • equal to (=)
  • not equal to (!= or <>)
  • less than (<), 
  • greater than (>)
  • less than or equal to (<=)
  • greater than or equal to (>=)
  • not less than (!<)
  • not greater than (!>)
• Bitwise Operators:For bit manipulations between two expressions of integer type. It first performs conversion of integers into binary bits and then applied operators 
  • AND (& symbol)
  • OR (|, ^)
  • NOT (~)
• Compound Operators:For operations on a variable before setting the variable’s result to the operation’s result
  • Add equals (+=)
  • subtract equals (-=)
  • multiply equals (*=)
  • divide equals (/=)
  • modulo equals (%=)
• String Operators:For concatenation and pattern matching of strings
  • + (String concatenation)
  • += (String concatenation assignment)
  • % (Wildcard)
  • [] (Character(s) matches)
  • [^] (Character(s) not to match)
  • _ (Wildcard match one character)

24. What do you mean by data integrity?

Data integrity is the assurance of accuracy and consistency of data over its whole life cycle. It is a critical aspect of the design, implementation, and usage of systems that store, process, or retrieve data.

Data integrity also defines integrity constraints for enforcing business rules on data when it is entered into a database or application.

25. What is a data warehouse?

A data warehouse is a large store of accumulated data, from a wide range of sources, within an organization. The data helps drive business decisions.

26. How would you find the second highest salary from the following table?

Code:

select * from employee

select max(e_salary) from employee where e_salary not in (select max(e_salary) from employee)

Output:

27. Why is the FLOOR function used in SQL Server?

The FLOOR() function helps to find the largest integer value to a given number, which can be an equal or lesser number.

28. State the differences between clustered and non-clustered indexes

• Clustered Index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. We can open the book at “David” (for “David, Thompson”) and find information for all Davids right next to each other. Since the data is located next to each other, it helps a lot in fetching the data based on range-based queries. A clustered index is actually related to how the data is stored; only one clustered index is possible per table.
• Non-clustered Index: It stores data at one location and indexes at another location. The index has pointers that point to the location of the data. As the indexes in a non-clustered index are stored in a different place, there can be many non-clustered indexes for a table.

Now, we will see the major differences between clustered and non-clustered indexes:

Parameters	Clustered Index	Non-clustered Index
Used For	Sorting and storing records physically in memory	Creating a logical order for data rows; pointers are used for physical data files
Methods for Storing	Stores data in the leaf nodes of the index	Never stores data in the leaf nodes of the index
Size	Quite large	Comparatively, small
Data Accessing	Fast	Slow
Additional Disk Space	Not required	Required to store indexes separately
Type of Key	By default, the primary key of a table is a clustered index	It can be used with the unique constraint on the table that acts as a composite key
Main Feature	Improves the performance of data retrieval	Should be created on columns used in Joins

29. What do you know about CDC in SQL Server?

CDC refers to change data capture. It captures recent INSERT, DELETE, and UPDATE activity applied to SQL Server tables. It records changes to SQL Server tables in a compatible format.

30. What is the difference between SQL and MySQL?

Now Let’s compare the difference between SQL and MySQL.

SQL	MySQL
It is a structured query language used in a database	It is a database management system
It is used for query and operating database system	It allows data handling, storing, and modifying in an organized manner
It is always the same	It keeps updating
It supports only a single storage engine	It supports multiple storage engines
The server is independent	During backup sessions, the server blocks the database

31. State the differences between SQL and PL/SQL

SQL	PL/SQL
It is a database structured query language	It is a programming language for a database that uses SQL
It is an individual query that is used to execute DML and DDL commands	It is a block of codes that are used to write the entire procedure or a function
It is a declarative and data-oriented language	It is a procedural and application-oriented language
It is mainly used for data manipulation	It is used for creating applications
It provides interaction with the database server	It does not provide interaction with the database server
It cannot contain PL/SQL code	It can contain SQL because it is an extension of SQL

32. What is the ACID property in a database?

The full form of ACID is atomicity, consistency, isolation, and durability. ACID properties are used to check the reliability of transactions.

• • Atomicity refers to completed or failed transactions, where a transaction refers to a single logical operation on data. This implies that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged.
• Consistency means that the data meets all validity guidelines. The transaction never leaves the database without finishing its state.
• Concurrency management is the primary objective of isolation.
• Durability ensures that once a transaction is committed, it will occur regardless of what happens in between such as a power outage, fire, or some other kind of disturbance.

33. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG(), and VARIANCE() are some of the most widely used group functions.

34. What do you understand about a character manipulation function?

Character manipulation functions are used for the manipulation of character data types.
Some of the character manipulation functions are:

UPPER: It returns the string in uppercase.

Syntax:

UPPER(‘ string’)

Example:

SELECT UPPER(‘demo string’) from String;

Output:

DEMO STRING

LOWER: It returns the string in lowercase.

Syntax:

LOWER(‘STRING’)

Example:

SELECT LOWER (‘DEMO STRING’) from String

Output:

demo string

INITCAP: It converts the first letter of the string to uppercase and retains others in lowercase.

Syntax:

Initcap(‘sTRING’)

Example:

SELECT Initcap(‘dATASET’) from String

Output:

Dataset

CONCAT: It is used to concatenate two strings.

Syntax:

CONCAT(‘str1’,’str2’)

Example:

SELECT CONCAT(‘Data’,’Science’) from String

Output:

Data Science

LENGTH: It is used to get the length of a string.

Syntax:

LENGTH(‘String’)

Example:

SELECT LENGTH(‘Hello World’) from String

Output:

11

35. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary field is added as the AUTO_INCREMENT field so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a new record is inserted.

Syntax:

CREATE TABLE Employee(

Employee_id int NOT NULL AUTO-INCREMENT,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255)

Age int,

PRIMARY KEY (Employee_id)

)

Do check out our Blog on PL/SQL Interview Questions to crack your SQL Interview. 

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

• DELETE: This query is used to delete or remove one or more existing tables.
• TRUNCATE: This statement deletes all the data from inside a table.

The difference between DELETE and TRUNCATE commands are as follows:

• TRUNCATE is a DDL command, and DELETE is a DML command.
• With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger.
• If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command.

The syntax for the DELETE command:

DELETE FROM table_name

[WHERE condition];

Example:

select * from stu

Output:

delete from stu where s_name=’Bob’

Output:

The syntax for the TRUNCATE command:

TRUNCATE TABLE

Table_name;

Example:

select * from stu1

Output:

truncate table stu1

Output:

This deletes all the records from a table.

37. What is the difference between DROP and TRUNCATE commands?

If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints. 

To create and use the table again in its original form, all the elements associated with the table need to be redefined. 

However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.

38. What is a “TRIGGER” in SQL?

The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands, such as insert, update, and delete, are given.

The syntax used to generate the trigger function is:

CREATE TRIGGER trigger_name

39. Where are usernames and passwords stored in SQL Server?

In SQL Server, usernames and passwords are stored in the main database in the sysxlogins table.

40. What are the types of relationships in SQL Server databases?

Relationships are developed by interlinking the column of one table with the column of another table. There are three different types of relationships, which are as follows:

• One-to-one relationship
• Many-to-one relationship
• Many-to-many relationship

41. What are the third-party tools that are used in SQL Server?

The following is the list of third-party tools that are used in SQL Server:

• SQL CHECK
• SQL DOC 2
• SQL Backup 5
• SQL Prompt
• Litespeed 5.0

42. How can you handle expectations in SQL Server?

TRY and CATCH blocks handle exceptions in SQL Server. Put the SQL statement in the TRY block and write the code in the CATCH block to handle expectations. If there is an error in the code in the TRY block, then the control will automatically move to that CATCH block.

43. How many authentication modes are there in SQL Server? And what are they?

Two authentication modes are available in SQL Server. They are:

• Windows Authentication Mode: It allows authentication for Windows but not for SQL Server.
• Mixed Mode: It allows both types of authentication—Windows and SQL Server.

44. What is a function in SQL Server?

A function is an SQL Server database object. It is basically a set of SQL statements that allow input parameters, perform processing, and return results only. A function can only return a single value or table; the ability to insert, update, and delete records in database tables is not available.

45. Mention different types of replication in SQL Server?

In SQL Server, three different types of replications are available:

• Snapshot replication
• Transactional replication
• Merge replication

46. Which command is used to find out the SQL Server version?

The following command is used to identify the version of SQL Server:

Select SERVERPROPERTY(‘productversion’)

47. What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null value.

Syntax:

COALESCE(val1,val2,val3,……,nth val)

Example:

SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Output:

1

48. Can we link SQL Server with others?

SQL Server allows the OLEDB provider, which provides the link, to connect to all databases.

Example: Oracle, I have an OLEDB provider that has a link to connect with an SQL Server group.

49. What is SQL Server Agent?

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

50. What do you know about magic tables in SQL Server?

A magic table can be defined as a provisional logical table that is developed by an SQL Server for tasks such as insert, delete, or update (DML) operations. The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.

51. What are some common clauses used with SELECT queries in SQL?

There are many SELECT statement clauses in SQL. Some of the most commonly used clauses with SELECT queries are:

• FROM
  The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given.
• WHERE
  The WHERE clause defines the parameters that are used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects.
• GROUP BY
  The GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions.
• ORDER BY
  The ORDER BY clause helps in choosing the columns on which the table’s result should be sorted.
• HAVING
  The HAVING clause filters the results of the GROUP BY clause by using an aggregate function.

52. What is wrong with the following SQL query?

SELECT gender, AVG(age) FROM employee WHERE AVG(age)>30 GROUP BY gender

When this command is executed, it gives the following error:

Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list; the column being aggregated is an outer reference.

Msg 147, Level 16, State 1, Line 1

Invalid column name ‘gender’.

This basically means that whenever we are working with aggregate functions and are using the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.

When we are using the HAVING clause, the GROUP BY clause should come first, followed by the HAVING clause.

select e_gender, avg(e_age) from employee group by e_gender having avg(e_age)>30

Output:

53. What do you know about the stuff() function?

The stuff() function deletes a part of the string and then inserts another part into the string, starting at a specified position.

Syntax:

STUFF(String1, Position, Length, String2)

Here, String1 is the one that will be overwritten. Position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1.

Example:

select stuff(‘SQL Tutorial’,1,3,’Python’)

This will change ‘SQL Tutorial’ to ‘Python Tutorial’

Output:

Python Tutorial

54. What are views? Give an example.

Views are virtual tables used to limit the tables that we want to display. Views are nothing but the result of an SQL statement that has a name associated with it. Since views are not physically present, they take less space to store.

Let us consider an example. In the following employee table, say we want to perform multiple operations on the records with gender “Female”. We can create a view-only table for the female employees from the entire employee table.

Now, let us implement it on SQL Server.

This is the employee table:

select * from employee

Now, we will write the syntax for the view.

Syntax:

create view female_employee as select * from employee where e_gender=’Female’

select * from female_employee

Output:

55. What are the types of views in SQL?

In SQL, the views are classified into four types. They are:

• Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features.
• Complex View: A view that is built from several tables and includes a GROUP BY clause as well as functions.
• Inline View: A view that is built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
• Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.

Advanced SQL Interview Questions

56. What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system. We can consolidate several SQL statements into a stored procedure and execute them whenever and wherever required.

A stored procedure can be used as a means of modular programming, i.e., we can create a stored procedure once, store it, and call it multiple times as required. This also supports faster execution when compared to executing multiple queries.

Syntax:

CREATE PROCEDURE procedure_name

AS

Sql_statement

GO;

To execute we will use this:

EXEC procedure_name

Example:

We are going to create a stored procedure that will help us extract the age of the employees.

create procedure employee_age

as

select e_age from employee

go

Now, we will execute it.

exec employee_age

Output:

57. Explain Inner Join with an example.

Inner Join basically gives us those records that have matching values in two tables.

Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.

Syntax:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column_x=table2.column_y;

Example:

select * from employee

select * from department

Output:

Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.

Syntax:

select employee.e_name, employee.e_dept, department.d_name, department.d_location

from employee inner join department

on

employee.e_dept=department.d_name

Output:

After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.

58. State the differences between views and tables.

Views	Tables
A view is a virtual table that is extracted from a database	A table is structured with a set number of columns and a boundless number of rows
A view does not hold data itself	A table contains data and stores it in databases
A view is utilized to query certain information contained in a few distinct tables	A table holds fundamental client information and cases of a characterized object
In a view, we will get frequently queried information	In a table, changing the information in the database changes the information that appears in the view

59. What do you understand about a temporary table? Write a query to create a temporary table

A temporary table helps us store and process intermediate results. Temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where temporary data needs to be stored.

Syntax:

CREATE TABLE #table_name();

The below query will create a temporary table:

create table #book(b_id int, b_cost int)

Now, we will insert the records.

insert into #book values(1,100)

insert into #book values(2,232)

select * from #book

Output:

60. Explain the difference between OLTP and OLAP.

OLTP: It stands for online transaction processing, and we can consider it to be a category of software applications that are efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to keep up the consistency. The OLTP system often follows decentralized planning to keep away from single points of failure. This system is generally designed for a large audience of end users to perform short transactions. The queries involved in such databases are generally simple, need fast response time, and, in comparison, return in only a few records. So, the number of transactions per second acts as an effective measure for those systems.

OLAP: It stands for online analytical processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions. For OLAP systems, the efficiency of computing depends highly on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multidimensional schemas.

61. What is Hybrid OLAP?

Hybrid OLAP (HOLAP) uses a combination of multidimensional data structures and relational database tables to store multidimensional data. The aggregations for a HOLAP partition are stored by analysis services in a multidimensional structure. The facts are stored in a relational database.

62. What do you understand by Self Join? Explain using an example

Self Join in SQL is used for joining a table with itself. Here, depending on some conditions, each row of the table is joined with itself and with other rows of the table.

Syntax:

SELECT a.column_name, b.column_name

FROM table a, table b

WHERE condition

Example:

Consider the customer table given below.

Example:

Consider the customer table given below.

ID	Name	Age	Address	Salary
1	Anand	32	Ahmedabad	2,000.00
2	Abhishek	25	Delhi	1,500.00
3	Shivam	23	Kota	2,000.00
4	Vishal	25	Mumbai	6,500.00
5	Sayeedul	27	Bhopal	8,500.00
6	Amir	22	MP	4,500.00
7	Arpit	24	Indore	10,000.00

We will now join the table using Self Join:

SQL> SELECT a.ID, b.NAME, a.SALARY

FROM CUSTOMERS a, CUSTOMERS b

WHERE a.SALARY < b.SALARY;

Output:

ID	Name	Salary
2	Anand	1,500.00
2	Abhishek	1,500.00
1	Vishal	2,000.00
2	Vishal	1,500.00
3	Vishal	2,000.00
6	Vishal	4,500.00
1	Sayeedul	2,000.00
2	Sayeedul	1,500.00
3	Sayeedul	2,000.00
4	Sayeedul	6,500.00
6	Sayeedul	4,500.00
1	Amir	2,000.00
2	Amir	1,500.00
3	Amir	2,000.00
1	Arpit	2,000.00
2	Arpit	1,500.00
3	Arpit	2,000.00
4	Arpit	6,500.00
5	Arpit	8,500.00
6	Arpit	4,500.00

63. What is the difference between Union and Union All operators?

The Union operator is used to combine the result set of two or more select statements. For example, the first select statement returns the fish shown in Image A, and the second statement returns the fish shown in Image B. The Union operator will then return the result of the two select statements as shown in Image A U B. If there is a record present in both tables, then we will get only one of them in the final result.

Syntax:

SELECT column_list FROM table1

Union:

SELECT column_list FROM table2

Now, we will execute it in the SQL Server.

These are the two tables in which we will use the Union operator.

select * from student_details1

Union:

select * from student_details2

Output:

The Union All operator gives all the records from both tables including the duplicates.

Let us implement it in the SQL Server.

Syntax:

select * from student_details1

Union All:

select * from student_details2

Output:

64. What is a cursor? How to use a cursor?

A database cursor is a control that allows you to navigate around a table’s rows or documents. It can be referred to as a pointer for a row in a set of rows. Cursors are extremely useful for database traversal operations such as extraction, insertion, and elimination.

• After any variable declaration, DECLARE a cursor. A SELECT statement must always be aligned with the cursor declaration.
• To initialize the result set, OPEN statements must be called before fetching the rows from the result table.
• To grab and switch to the next row in the result set, use the FETCH statement.
• To deactivate the cursor, use the CLOSE expression.
• Finally, use the DEALLOCATE clause to uninstall the cursor description and clear all the resources associated with it.

Here is an example SQL cursor:

DECLARE @name VARCHAR(50)

DECLARE db_cursor CURSOR FOR

SELECT name

From myDB.company

WHERE employee_name IN (‘Jay’, ‘Shyam’)

OPEN db_cursor

FETCH next

FROM db_cursor

Into @name

Close db_cursor

DEALLOCATE db_cursor

65. What is the use of the INTERSECT operator?

The INTERSECT operator helps combine two select statements and returns only those records that are common to both the select statements. So, after we get Table A and Table B over here, and if we apply the INTERSECT operator on these two tables, then we will get only those records that are common to the result of the select statements of these two tables.

Syntax:

SELECT column_list FROM table1

INTERSECT

SELECT column_list FROM table2

Now, let us take a look at an example for the INTERSECT operator.

select * from student_details1

select * from student_details1

Output:

select * from student_details1

intersect

select * from student_details2

Output:

66. How can you copy data from one table into another table?

Here, we have our employee table.

We have to copy this data into another table. For this purpose, we can use the INSERT INTO SELECT operator. Before we go ahead and do that, we will have to create another table that will have the same structure as the above-given table.

Syntax:

create table employee_duplicate(

e_id int,

e_name varchar(20),

e_salary int,

e_age int,

e_gender varchar(20)

e_dept varchar(20)

)

For copying the data, we will use the following query:

insert into employee_duplicate select * from employees

Let us take a look at the copied table.

select * from employee_duplicate

Output:

67. What is the difference between BETWEEN and IN operators in SQL?

The BETWEEN operator is used to represent rows based on a set of values. The values may be numbers, text, or dates. The BETWEEN operator returns the total number of values that exist between two specified ranges.

The IN condition operator is used to search for values within a given range of values. If we have more than one value to choose from, then we use the IN operator.

68. Describe how to delete duplicate rows using a single statement but without any table creation.

Let us create an employee table where the column names are ID, NAME, DEPARTMENT, and EMAIL. Below are the SQL scripts for generating the sample data:

CREATE TABLE EMPLOYEE

(

ID INT,

NAME Varchar(100),

DEPARTMENT INT,

EMAIL Varchar(100)

)

INSERT INTO EMPLOYEE VALUES (1,’Tarun’,101,’tarun@intellipaat.com’)

INSERT INTO EMPLOYEE VALUES (2,’Sabid’,102,’sabid@intellipaat.com’)

INSERT INTO EMPLOYEE VALUES (3,’Adarsh’,103,’adarsh@intellipaat.com’)

INSERT INTO EMPLOYEE VALUES (4,’Vaibhav’,104,’vaibhav@intellipaat.com’)

–These are the duplicate rows:

INSERT INTO EMPLOYEE VALUES (5,’Tarun’,101,’tarun@intellipaat.com’)

INSERT INTO EMPLOYEE VALUES (6,’Sabid’,102,’sabid@intellipaat.com’)

We can see the duplicate rows in the above table.

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id

The SQL query above will delete the rows, where the name fields are duplicated, and it will retain only those unique rows in which the names are unique and the ID fields are the lowest, i.e., the rows with IDs 5 and 6 are deleted, while the rows with IDs 1 and 2 are retained.

69. Can you identify the employee who has the third-highest salary from the given employee table (with salary-related data)?

Consider the following employee table. In the table, Sabid has the third-highest salary (60,000).

Name	Salary
Tarun	70,000
Sabid	60,000
Adarsh	30,000
Vaibhav	80,000

Below is a simple query to find out the employee who has the third-highest salary. The functions RANK, DENSE RANK, and ROW NUMBER are used to obtain the increasing integer value by imposing the ORDER BY clause in the SELECT statement, based on the ordering of the rows. The ORDER BY clause is necessary when RANK, DENSE RANK, or ROW NUMBER functions are used. On the other hand, the PARTITION BY clause is optional.

WITH CTE AS

(

    SELECT Name, Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM EMPLOYEE

)

SELECT Name, Salary FROM CTE WHERE RN =3

70. What is the difference between HAVING and WHERE clauses?

The distinction between HAVING and WHERE clauses in SQL is that while the WHERE clause cannot be used with aggregates, the HAVING clause is used with the aggregated data. The WHERE clause works on the data from a row and not with the aggregated data.

Let us consider the employee table below.

Name	Department	Salary
Tarun	Production	50,000
Tarun	Testing	60,000
Sabid	Marketing	70,000
Adarsh	Production	80,000
Vaibhav	Testing	90,000

The following will select the data on a row-by-row basis:

SELECT Name, Salary FROM Employee WHERE Salary >=50000

Output:

Name	Salary
Tarun	50,000
Tarun	60,000
Sabid	70,000
Adarsh	80,000
Vaibhav	90,000

The HAVING clause, on the other hand, operates on the aggregated results.

SELECT Department, SUM(Salary) AS total FROM Employee GROUP BY Department

Output:

Department	Total
Marketing	70,000
Production	130,000
Testing	150,000

Now, let us see the output when we apply HAVING to the above query.

SELECT Department, SUM(Salary) AS total FROM Employee GROUP BY Department HAVING SUM(Salary)>70000

Output:

Department	Total
Production	130,000
Testing	150,000

71. Explain database white box testing and black box testing.

The white box testing method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following:

• As the coding error can be detected by testing the white box, it can eliminate internal errors.
• To check for the consistency of the database, it selects the default table values.
• This method verifies the referential integrity rule.
• It helps perform the module testing of database functions, triggers, views, and SQL queries.

The black box testing method generally involves interface testing, followed by database integration. The black box testing method involves the following:

• Mapping details
• Verification of incoming data
• Verification of outgoing data from the other query functions

72. How can you create empty tables with the same structure as another table?

This can be achieved by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. In this way, SQL prepares the new table with a duplicate structure to accept the fetched records. However, there are no records that will get fetched due to the WHERE clause in action. Therefore, nothing is inserted into the new table, thus creating an empty table.

SELECT * INTO Students_copy

FROM Students WHERE 1 = 2;

Next 

• Que-1: Explain the meaning of ‘index’.
  Explanation:
  Indexes help retrieve information from the database faster and with higher efficiency. In other words, it’s a method that enhances performance and there are 3 types of indexes:
  • Clustered – reorders the table and searches for information with the use of key values
  • Non-clustered – maintains the order of the table
  • Unique – forbids fields to have duplicated values

Moreover, a table can have multiple non-cluster indexes, but only 1 single clustered one.

• Que-2: You forgot your root password, what do you do ?
  Explanation:
  • Start the database with the command of “skip-grants-table”.
  • After you set the new password, restart the database in normal mode and enter the new password.

• Que-3: Are NULL values equal to a zero ?
  Explanation:
  No, because a “zero” has a numerical manner and NULL represent the absence of a character. This happens when the character is unknown or unavailable. Additionally, NULL shouldn’t be confused with blank space because data record without any value assigned is not the same as a plain blank space, with no data records attached.

• Que-4: Data disk gets overloaded, what do you do ?
  Explanation:
  You should apply a soft link: these links create a location where you are able to store your .frm and .idb files. This will resolve the overload problem.

• Que-5: Explain what‘auto increment’ is?
  Explanation:
  This command allows you to generate a unique number when a new record is written to a table. When you want to the primary key field value to be generated automatically each time you insert a new record, this is when this function comes in handy.

Another thing worth noting is that the command can be used on various platforms. For SQL Servers the “auto increment” command is “identity”.

• Que-6: What are the most basic MySQL architecture components ?
  Explanation:
  There are three main components:
  • Query optimizer;
  • Connection manager;
  • Pluggable engine.

• Que-7: Using an existing table, make an empty one.
  Explanation:

Select * into employeecopy from employee where 1=2

• Que-8: How would you check your current SQL version ?
  Explanation:
  You can get the most current SQL version by issuing this command:

SELECT VERSION()

• Que-9: Get alternative odd records from the table.
  Explanation:
  This can be achieved using the command:

Select employeeId from (Select rowno, employeetId from employee) where mod(rowno, 2)=1

• Que-10: What command would select a unique record from the table ?
  Explanation:
  The “distinct” command. Here’s an example:

Select DISTINCT employeeID from Employee

• Que-11: What are variables of SQL ?
  Explanation:
  In SQL, there are two different variables:
  • Local – these variables can only exist in one single function
  • Global – are the opposite of local, which means they can be located through ought the entire program.

• Que-12: What is a ‘datawarehouse’ and what it does ?
  Explanation:
  A “datawarehouse” is a system used for data analysis and reporting. Basically, it’s a warehouse of data. Data in DWs can be stored from various areas and sources and thus makes them central repositories of integrated data that is ready for usage.

• Que-13: For what ‘recursive stored procedure’ is mainly used ?
  Explanation:
  A recursive stored procedure is a procedure that will make the code calls itself until specific boundary condition is reached. This is a productivity type of thing, that allows programmers to use the same code a number of times.

• Que-14: Retrieve the first 3 characters from a character string.
  Explanation:
  There are a few ways to do this. Nevertheless, the command presented below can be treated as a more popular and easier one:

Select SUBSTRING(EmployeeSurname, 1, 5) as employeesurname from employee

• Que-15: How would you retrieve common records from two tables ?
  Explanation:
  By performing the task below:

Select employeeID from employee. INTERSECT Select EmployeeID from