SQL Interview Questions and Answers for Experienced

SQL Interview Questions and Answers for Experienced

“SQL Interview Questions And Answers For Experienced”

SQL is one of the most asked topic in the Interview. Here below you will get the SQL interview questions for experienced 2020. This page will help you in preparing for Interviews.

PrepInsta provides you the most asked Interview Question of SQL.

Along with SQL Interview Questions and Answers for Experienced you also need to prepare well for SQL “Structured Query Language

This page will provide you detailed information about all the type of questions asked in SQL Interview Questions and Answers for Experienced

What is Structured Query Language?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

Also, they are using different dialects, such as −

  • MS SQL Server using T-SQL,
  • Oracle using PL/SQL,
  • MS Access version of SQL is called JET SQL (native format) etc.

SQL Interview Questions and Answers for Experienced

1. Write a SQL query to find the names of employees that begin with ‘A’?

Solution:- To display name of the employees that begin with ‘A’, type in the below command:

SELECT * FROM Table_name WHERE EmpName like ‘A%’

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

Solution:- Group functions work on the set of rows and returns one result per group. Some of the commonly used group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.

3. What is an Alias in SQL?

Solution:-

An alias is a SQL function that most, if not all, RDBMSs support. It’s a fictitious name given to a table or table column for the purposes of a SQL query. Furthermore, aliasing can be used as an obfuscation strategy to protect the true names of database fields. A correlation name is another name for a table alias.

The AS keyword  represents an alias

4. What are the various forms of Normalization?

Solution– 

  • First Normal Form

A relation is in first normal form if every attribute in that relation is a single-valued attribute. If a relation contains composite or multi-valued attribute, it violates the first normal form.

  • Second Normal Form

A relation is in second normal form if it satisfies the conditions for first normal form and does not contain any partial dependency. A relation in 2NF has no partial dependency, i.e., it has no non-prime attribute that depends on any proper subset of any candidate key of the table.

  • Third Normal Form

A relation is said to be in the third normal form, if it satisfies the conditions for second normal form and there is no transitive dependency between the non-prime attributes, i.e.,all non-prime attributes are determined only by the candidate keys of the relation and not by any other non-prime attribute.

5. What is User-defined function? What are its various types?

Solution:- 

The user-defined functions in SQL are like functions in any other programming language that accept parameters, perform complex calculations, and return a value. They are written to use the logic repetitively whenever required. There are two types of SQL user-defined functions:

  • Scalar Function: As explained earlier, user-defined scalar functions return a single scalar value.
  • Table Valued Functions: User-defined table-valued functions return a table as output.
    • Inline: returns a table data type based on a single SELECT statement.
    • Multi-statement: returns a tabular result-set but, unlike inline, multiple SELECT statements can be used inside the function body.

6. What is CLAUSE in SQL?

Solution:- SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.

For example – WHERE, HAVING clause.

7. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?

Solution:-

HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.

8. What are the differences between OLTP and OLAP?

Solution:-

  • OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An important attribute of an OLTP system is its ability to maintain concurrency. OLTP systems often follow a decentralized architecture to avoid single points of failure. These systems are generally designed for a large audience of end users who conduct short transactions. Queries involved in such databases are generally simple, need fast response times and return relatively few records. Number of transactions per second acts as an effective measure for such systems.
  • OLAP stands for Online Analytical Processing, 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. For OLAP systems, the effectiveness measure relies highly on response time. Such systems are widely used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.

9. How to create empty tables with the same structure as another table?

Solution:- Creating empty tables with the same structure can be done smartly 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. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.

SELECT * INTO Students_copy
FROM Students WHERE 1 = 2;

10. What is AUTO_INCREMENT?

Solution:- 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, we add this primary field as the AUTO_INCREMENT field so that it is incremented when a new record is inserted.

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

11. How to create empty tables with the same structure as another table?

Solution:- Creating empty tables with the same structure can be done smartly 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. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.

12. What is the main difference between SQL and PL/SQL?

Solution:- SQL is a query language that allows you to issue a single query or execute a single insert/update/delete whereas PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as selects/inserts/updates/deletes. 

13. What are Local and Global variables?

Solution:-

  • Local variables : These variables can be used or exist only inside the function. These variables are not used or referred by any other function.
  • Global variables : These variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called.

14. How can you fetch first 5 characters of the string?

Solution:-

There are a lot of ways to fetch characters from a string. For example:

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

15. What is a View?

Solution:- A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.

16. How to change column data-type in SQL?

Solution:-

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.

17. What is the difference between SQL and NoSQL databases?

Solution:-

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 NoSQL database, we will be working with non-relational databases.

18. What are the types of Views?

Solution:- 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 as 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)

19. How to remove duplicate rows in SQL?

Solution:- There are a lot of ways to remove duplicate rows in SQL. Let’s look at this example:

SELECT [Name],
    [Age],
    [Gender],
    COUNT(*) AS CNT
FROM [mydata].[dbo].[Employees]
GROUP BY [Name],
      [Age],
      [Gender]
HAVING COUNT(*) > 1;

In the above command, we are using group by and having to count the duplicate records.

20. How to join two tables in SQL?

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