How To Practice Sql For Interview

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 Difference Between Sql And Pl/sql

The following comparison chart explains their main differences:

Subqueries Common Table Expressions & Window Functions

Subquery

A subquery is a query found within the query. It can occur in a SELECT clause, FROM clause, or WHERE clause.

A CTE or a Common Table Expression is a temporary result set returned by a query and used by another query. In that way, its similar to subquery. But the main difference is CTE can be named and can reference itself.

Window functions

The window functions are SQL functions performing calculations over the defined set of rows . Compared to the aggregate functions, which return a single value as a result, the window functions allow you to add the aggregated value to each row in a separate column. This means the rows are not grouped and all the rows are kept as a query result. The window functions are:

• row_number
• sum

Recommended Reading: What Are Some Good Interview Questions To Ask

Sql Query Interview Questions & Answers

The query for this condition is:

SELECT UPPER AS EmpName FROM EmployeeInfo

Heres the query for this demand:

SELECT COUNT FROM EmployeeInfo WHERE Department = HR

To fetch the current date, you can write this query in the SQL server:

SELECT GETDATE

To fetch the current date, you can write this query in MySQL:

SELECT SYSTDATE

You can use the MID function in MySQL to create the following query:

SELECT MID) FROM EmployeeInfo

You can use SUBSTRING to create the following query:

SELECT SUBSTRING) FROM EmployeeInfo

You can use the SELECT INTO command to create the following query:

SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0

You can use the CREATE command in MySQL to create the following query:

CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo

This is the query you need to write to get the names of the employees whose names start withS:

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE S%

SELECT ISDATE AS MM/DD/YY

Scenario : Finding The Procedures Information

There are situations in Real Time Scenarios of SQL where user needs to find out the procedures information.

Query 1 :How to check Procedures?

SELECT * FROM User_Source

AND NAME IN

Query 2:How to find procedure columns information?

select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE

Don’t Miss: How To Prepare For An Engineering Interview

Technical Concepts Tested In Sql Interview Questions

Since SQL roles differ widely, the testing areas vary quite a bit as well. Depending on the type of role that you are applying for and the organization, you can expect one or more of these SQL Data Science Interview Question types

• Fundamental SQL concepts

• – When should one use a CTE over a subquery?- What are window functions?
• Knowledge of various SQL functions- What is the difference between WHERE and HAVING? Examples of where one should use one over the other- What does the COALESCE function do?

While one may not find them in the interviews initially, these questions might be asked as follow up questions to the coding solutions submitted by you. For example, if you used an inner join in your solution, you might be asked why you did not use a left join or what would have happened if you did?

Interview Scenario : Join Columns Having Duplicate Values

Let insert value 1 into the table tbl_samplejoin2 so that this tables contains the duplicate values. So after inserting the duplicate record in table 2, both tables contains the following data .

Now if the interviewer asked any of the 4 SQL joins queries again then, duplicate record in the 2nd table may put confusion in the candidate mind. Let check how this duplicate record impact the outcomes of these queries.

a) Answer of the first question will be

With duplicate values in the Join column, we found one extra row has been added to the result-set. This is because we have now 2 matching rows in table tbl_samplejoin2 containg value 1 for the value 1 of the table tbl_samplejoin1.

b) Answer of the 2nd question will be

Again if look at both tables data, we found that two records of the table tbl_samplejoin1 has the matching records in the second table tbl_samplejoin2. But the remaining two records dont have the matching record in second table. So result-set returns values of 1 & 2 from table tbl_samplejoin1 with matching values 1 & 2 from second table and for records 3 & 5, it return with NULL value.

c) Answer of the 3rd question will be

d) Answer of the 4th question will be

Read Also: How To Crack Business Analyst Interview

What Is Sql Injection

SQL injection is a type of vulnerability in website and web app code that allows attackers to control back-end operations and access, retrieve, and destroy sensitive data from databases. In this technique, malicious SQL statements are inserted into a database entry field, and once they are performed, the database becomes vulnerable to an attacker. This technique is commonly used to access sensitive data and perform administrative activities on databases by exploiting data-driven applications. It is also known as SQLi attack.

Some common examples of SQL injection are:

• Accessing confidential data to modify an SQL query to get desired results.
• UNION attacks to steal data from different database tables.
• Examine the database to extract information regarding the version and structure of the database.

Difference Between Scalar Valued Functions & Table Valued Functions In Sql

SELECT dbo. funname 

Don’t Miss: Where Can I Watch The Interview

Can I Request For A Support Session If I Need To Better Understand The Topics

Intellipaat is offering 24/7 query resolution, and you can raise a ticket with the dedicated support team at any time. You can avail of email support for all your queries. If your query does not get resolved through email, we can also arrange one-on-one sessions with our support team. However, 1:1 session support is provided for a period of 6 months from the start date of your course.

Practice Sql Interview Questions And Answers

Theres no better way to improve your SQL skills than to practice with some real SQL interview questions and these SQL practice problems are a great way to improve your SQL online. We recommend first creating the following simple tables presented below in the RDBMS software of your choice MySQL, Oracle, DB2, SQL Server, etc, and then actually try to figure out the answer on your own if possible.

The following SQL practice exercises were actually taken from real interview tests with Google and Amazon. Once again, we highly recommended that you try finding the answers to these SQL practice exercises on your own before reading the given solutions. The practice problems are based on the tables presented below.

Given the tables above, find the following:

a. The names of all salespeople that have an order with Samsonic.

b. The names of all salespeople that do not have any order with Samsonic.

c. The names of salespeople that have 2 or more orders.

d. Write a SQL statement to insert rows into a table called highAchiever, where a salesperson must have a salary of 100,000 or greater to be included in the table.

Lets start by answering part a. Its obvious that we would need to do a SQL join, because the data in one table will not be enough to answer this question. This is a good question to get some practice with SQL joins, so see if you can come up with the solution.

select Salesperson.Name from Salesperson where Salesperson.ID = '' 

Also Check: How To Start An Interview As The Interviewer Example

Amazon Sql Interview Questions

Regardless of the position, SQL is a common feature of the Amazon interview. Its best to be familiar with basic syntax all the way through complex queries and sub-queries. Doing well in this portion of the interview could make the difference between getting the job or being asked to look elsewhere.

What Sql Practice Do You Need To Prepare For A Technical Job Interview

Youâve scored yourself an interview for an SQL-related job. Nice work! But perhaps uncertainty is creeping in as your interview date looms closer.

• Do you know enough SQL to make the cut?
• What questions are you likely to be asked?
• What SQL practice should you be doing before your interview?

Almost all SQL job candidates go through exactly the same nerve-wracking process. Here at LearnSQL.com, we have the lowdown on all the SQL practice and preparation youâll need to ace those interview questions and take your career to the next level.

People are choosing to learn SQL for a wide variety of reasons. There are many different types of SQL jobs just crying out for your newfound skills. What they all have in common is the technical job interview. This can be intimidating if youâve never encountered one before and arenât sure what to expect.

Here are our top tips for preparing for an SQL interview!

Also Check: How Do You Prepare For A Job Interview

Sql Interview Question #3

You have two SQL tables! The first one is called employees and it contains the employee names, the unique employee ids and the department names of a company. Sample:

The second one is named salaries. It holds the same employee names and the same employee ids and the salaries for each employee. Sample:

The company has 546 employees, so both tables have 546 rows.

Print every department where the average salary per employee is lower than $500!

Sql Exercise 9 Write A Complex Query

Write a query that lists coursesâ subject names and the number of students taking the course only if the course has three or more students enrolled.

Table: subjectsubject_id  subject_name       max_score        lecturer----------  ----------         ----------       -----------   11         Math                    130         Christena Solem  12         Computer Science         50         Jaime Pille  13         Biology                 300         Carrol Denmark  14         Geography               220         Yuette Galang  15         Physics                 110         Colton Rather  16         Chemistry               400         Nan Mongeau

Read Also: How To Prepare For Product Manager Interview

What Do You Know About Joins Define Different Types Of Joins

The Join clause 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 upon the relationship between tables.

There are four types of Joins:

• Inner Join: Inner Join basically returns records that have matching values in both tables.
• Left Join: Left Join returns rows that are common between the tables and all the rows of the left-hand-side table, i.e., it returns all the rows from the left-hand-side table even if there are no matches available in the right-hand-side table.
• Right Join: Right Join returns rows that are common between the tables and all the rows of the right-hand-side table, i.e., it returns all the rows from the right-hand-side table even if there are no matches available in the left-hand-side table.
• Full Join: Full Join returns all the rows from the left-hand-side table and all the rows from the right-hand-side table.

How Can You Say That Database Testing Is Different From That Of Gui Testing

• GUI testing is always performed at the front end whereas Database testing is performed at the back end
• When it comes to dealing with testable items, generally the users prefer GUI testing. These items are present clearly./ On the other hand, the Database testing deals with the testable items that are hidden and are not directly visible to the users
• Structured Query Language largely matters in Database approach where the same doesnt have any application with the GUI
• Invalidating the test boxes are a part of the GUI database whereas the Database testing is totally different in this manner

Don’t Miss: How To Interview A Caregiver

What Are Entities And Relationships

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

For example: In a companys database, employees, projects, salaries, etc can be referred to as entities.

Relationships: Relationships between entities can be referred to as the connection between two tables or entities.

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

That is all in the section of Basic SQL practice questions. Lets move on to the next section of SQL intermediate interview questions.

What Is Sql Server

To understand what exactly is SQL Server, we need to understand what is DBMS and RDBMS. DBMS stands for Database Management System. When we have a huge database with us, we would need a proper management system which would help us organise this database. There are 4 types of database management systems:

• Hierarchical
• Relational
• Object-Oriented.

Out of these database management systems, SQL Server comes under the category of Relational database management system. A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easier to locate and access specific values within the database. It is relational because the values within each table are related to each other. The relational structure makes it possible to run queries across multiple tables at once.

You May Like: How To Start A Phone Interview As The Interviewer

What Is The Difference Between The Rank And Dense: Rank Functions

The RANK function determines the rank for each row within your ordered partition in the result set. If the two rows are assigned the same rank, then the next number in the ranking will be its previous rank plus a number of duplicate numbers. For example, if we have three records at rank 4, the next rank listed would be ranked 7.

The DENSE_RANK function assigns a unique rank for each row within a partition as per the specified column value without any gaps. It always specifies ranking in consecutive order. If the two rows are assigned the same rank, this function will assign it with the same rank, and the next rank being the next sequential number. For example, if we have 3 records at rank 4, the next rank listed would be ranked 5.

Sql & Database Generalities

SQL definition

SQL stands for Structured Query Language. It is a programming language used for creating database structure, retrieving and manipulating data in it.

Types of the SQL commands Relational database

• Data Definition Language
• – REVOKE

Relational database

A relational database is one based on the relational data model. This means the database is a collection of relations. Those relations are shown as tables, which consist of columns, rows, and values. The relational database aims to minimize or completely avoid data redundancy, leading to data integrity and speeding up its retrieval.

Relationships in the database

The relationship defines the type of connection between the tables in the database. There are three main types of relationships:

• one-to-one relationship
• one-to-many relationship or many-to-one relationship
• many-to-many relationship

Database normalization

Database normalization is a process of organizing data in the database to achieve its purpose: data integrity, its non-redundancy, and speed of retrieval.

Constraints

The constraints are the rules that define what type of data can and cant be entered as a value in the database. The most common attributes are:

• NOT NULL

• hash index

View

A view is a virtual table containing data from one or more tables resulting from a SQL statement.

Stored procedure

A stored procedure is an SQL code consisting of one or several SQL statements that are saved and can be called and executed whenever required.

Trigger

You May Like: What Questions To Ask In A Sales Interview