Sql Interview Questions With Answers

What Are All The Different Normalizations

Database Normalization can be easily understood with the help of a case study. The normal forms can be divided into 6 forms, and they are explained below -.

• First Normal Form :.

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

• Second Normal Form :.

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

• Third Normal Form :.

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

• Fourth Normal Form :.

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.

• Fifth Normal Form :.

A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

• Sixth Normal Form :.

6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardized definition for 6th Normal Form in the near future

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.

Write The Syntaxes For Joins

Ans.

• INNER JOIN: select column_name From table1 INNER JOIN table2 on table1.column_name = table2.column_name
• OUTER JOIN: select column_name From table1 OUTER JOIN table2 on table1.column_name = table2.column_name
• LEFT JOIN: select column_name From table1 LEFT JOIN table2 on table1.column_name = table2.column_name
• RIGHT JOIN: select column_name From table1 RIGHT JOIN table2 on table1.column_name = table2.column_name
• FULL JOIN: select column_name From table1 INNER JOIN table2 on table1.column_name = table2.column_name WHERE condition

• INNER JOIN: select column_name From table1 INNER JOIN table2 on table1.column_name = table2.column_name

You May Like: Amazon Leadership Principle Interview Questions

What Is An Execution Plan When Would You Use It How Would You View The Execution Plan

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL servers query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.

In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called Show Execution Plan . If this option is turned on, it will display query execution plans in a separate window when a query is run.

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.

Lets 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.

You May Like: How To Score A Job Interview

What Is Hibernate And Its Relation To Sql

Hibernate is Object Relational Mapping tool in Java. Hibernate letâs us write object-oriented code and internally converts them to native SQL queries to execute against a relational database. Hibernate uses its own language like SQL which is called Hibernate Query Language. The difference is that HQL boasts on being able to query Hibernateâs entity objects. It also has an object-oriented query language in Hibernate which is called Criteria Query. It proves very beneficial and helpful to developers who primarily use objects in their front-end applications and Criteria Query can cater to those objects in even add SQL-like features such as security and restriction-access.52. ### How can we solve SQL Error: ORA-00904: invalid identifier?

This error usually appears due to syntax errors on calling a column name in Oracle database, notice the ORA identifier in the error code. Make sure you typed in the correct column name. Also, take special note on the aliases as they are the one being referenced in the error as the invalid identifier.53. ### What is a SQL Profiler?

It will require a JDBC driver to link these two. Also, you must add the corresponding dependencies to your build.gradle file along with the permissions and grants.

Reference: Wikipedia

If youâve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.

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:

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

You May Like: Franchise Interview Questions And Answers

Mention The Aggregate Functions Used In Sql

The following aggregate functions are used in SQL.

47. What is the difference between SQL and MySQL?

Define Data Integrity And How Its Important In Your Role

Data integrity is vital in developing an enterprise business environment thats structured around data. When it comes to keeping data useable, SQL developers need to understand what data integrity means and why its important. An example can be given here about how data integrity impacted your role in an organization.

Example:Data integrity is the ideal outcome for corporate data. It is characterized by the accuracy and consistency of data that makes it highly usable. It is the hopeful outcome of maintaining the data lifecycle, or the process by which data is collected, processed and maintained.

Read Also: Identity Access Management Interview Questions

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_nameASSql_statementGO 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_ageasselect e_age from employeegoNow, we will execute it.exec employee_age

Output:

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 table1INTERSECTSELECT column_list FROM table2

Now, let us take a look at an example for the INTERSECT operator.

select * from student_details1select * from student_details1

Output:

select * from student_details1intersectselect * from student_details2

Output:

Read Also: Ux Designer Interview Questions And Answers

When Would It Be More Appropriate To Use A Materialized View Over A View

Both Materialized Views and Views make it easier to query data by transforming some table data into its own structure. Materialized Views persist data whereas Views compute its data each time it is queried.

This means that Materialized Views have better read performance because the data is persisted. When performance is sufficient, its sometimes recommended to go with a View to reduce the data that would be stored with a Materialized View.

When hiring managers and tech recruiters ask SQL interview questions like this one, they are interested in knowing that you understand how to make performance tradeoffs in SQL. Even if an interviewer doesnt ask this exact question, you may be able to reference the benefits of a Materialized View or View in a situation where you have to craft complex queries.

How To Create A Table In Sql

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

 CREATE TABLE table_name  

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 ,age int,gender varchar,   ....) 

You May Like: How To Have A Great Job Interview

Faqs On Sql Interview Questions For Experienced Professionals

Q1. Are SQL interview questions for experienced professionals important for data analysts?

If you are a data analyst, you need to have SQL knowledge to completely understand data available in Relational Databases such as MySQL, Oracle, and Microsoft SQL. You must get well-versed with SQL interview questions for experienced professionals related to Data Preparation and Wrangling. Also, if your job requires you to use Big Data Tools for analysis, then SQL interview questions are a must for your interview preparation.

Q2. What topics are important for SQL interview questions for experienced professionals?

If you aim to perform well at an SQL job interview, you must have complete knowledge of the vital concerts related to SQL, including Data Definition Language keywords, Data Manipulation Language keywords, Data Control Language keywords, Transaction Control Language keywords, SQL constraints, joins, indexes, and transactions.

Q3. Is coding in SQL interview questions for experienced professionals hard?

If you are an experienced professional, you will not find coding questions in SQL hard. They are relatively easy as compared to other languages. With that said, you must practice as many coding problems as possible to ace the interview rounds.

Q4. How long does it take to learn SQL before an interview?

Q5. Which top companies make use of SQL?

What Is The Usage Of The Nvl Function

The NVL function is used to convert the NULL value to the other value. The function returns the value of the second parameter if the first parameter is NULL. If the first parameter is anything other than NULL, it is left unchanged. This function is used in Oracle, not in SQL and MySQL. Instead of NVL function, MySQL have IFNULL and SQL Server have ISNULL function.

You May Like: Where Can I Do A Video Interview

What Are Tables And Fields

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

Example:.

Field: Emp ID, Emp Name, Date of Birth.

Data: 201456, David, 11/15/1960.

Sql Usage In Data Science By Role

Not every position in Data Science uses the same SQL concepts. While some roles will focus heavily on queries and query optimization, others will tend towards data architecture and ETL processes. One can divide SQL Commands asked in Data Science Interviews into the following categories.

• Data Definition Language

A Data Analyst or a Data Scientist will be expected to mostly be working with the SELECT statement and associated advanced level concepts like subqueries, Grouping / Rollups, Window Functions and CTEs. If you work as an analyst, you probably already use SQL. It does not matter if you are a data analyst, reporting analyst, product analyst, or even financial analyst. Your position generally requires handling the raw data and using your skills to provide the management and other stakeholders with the decision-making insight.

Machine learning engineers are hybrid experts who are bridging the gap between data scientists and software engineers. As they serve as a bridge between those two positions, they need to have a certain set of skills from both worlds. They use those skills to design, build, and maintain the machine learning systems. To achieve that, they usually use several sets of skills:

• predictive analytics

Develop a naive forecast for a new metric: “distance per dollar”. Distance Per Dollar is defined as the in our dataset. Calculate the metric and measure its accuracy.

Algorithm Outline:

Recommended Reading: How To Study For A Technical Interview

What Is Meant By Case Manipulation Functions Explains Its Different Types In Sql

Case manipulation functions are part of the character functions. It converts the data from the state in which it is already stored in the table to upper, lower, or mixed case. The conversion performed by this function can be used to format the output. We can use it in almost every part of the SQL statement. Case manipulation functions are mostly used when you need to search for data, and you don’t have any idea that the data you are looking for is in lower case or upper case.

There are three case manipulation functions in SQL:

LOWER: This function is used to converts a given character into lowercase. The following example will return the ‘STEPHEN’ as ‘stephen’:

NOTE: Here, ‘dual’ is a dummy table.

UPPER: This function is used to converts a given character into uppercase. The following example will return the ‘stephen’ as ‘STEPHEN’:

INITCAP: This function is used to converts given character values to uppercase for the initials of each word. It means every first letter of the word is converted into uppercase, and the rest is in lower case. The following example will return the ‘hello stephen’ as ‘Hello Stephen’:

Explain Transactions And Describe Their Controls

Transactions represent routine operations that database administrators work with. Data professionals may need to be able to explain transactions and list important controls.

Example:”Transactions are sequential tasks that produce operations like creating, updating and deleting records. There are four transaction controls: commit, rollback, set traction, and savepoint.”

Recommended Reading: How To Draft A Thank You Email After An Interview

Why Are Sql Functions Used

SQL functions provide ways to perform calculations on the database. These can include aggregations that are often used for analytics. An example of an aggregation function would be the AVG function that returns the average prices paid for a purchase: SELECT AVG AS average_price FROM purchases.

When asked this question, the interviewer will be impressed if you are able to provide some examples of SQL functions that you have used and how it was used in your query.

Explain Your Answer And Assumptions

Even if you did not get the required answer, it does not mean you failed the interview. That is why you should always know why you did something and explain why you did it. Maybe you did not get the answer to the question they asked, but you did get the answer to some questions. So make sure that you state your assumption and explain why you did what you did. Again, they may be looking exactly for that: the right reasoning in line with assumptions, even though the assumptions were wrong. That also shows you know what you are doing, even if it is not what they asked.

Also, one of the reasons for explaining the assumptions is there may be a loophole in the question. So imagine pointing at it right there at the interview by simply explaining why you did something while you thought you were all wrong.

Here is the video where Nate from StrataScratch shares some tips on how to organize your SQL interview solution:

Recommended Reading: Indeed Automated Phone 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.