SQL test for job interview

There’s 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(Name, Age), where a salesperson must have a salary of 100, 000 or greater to be included in the table.

Let’s start by answering part a. It’s 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.

Now, what tables should we use for the join? We know that the customer ID of Samsonic is 4, so we can use that information and do a simple join with the salesperson and customer tables. The SQL would look like this:

select Salesperson.Name from Salesperson, Orders where Salesperson.ID = Orders.salesperson_id and cust_id = '4';

We can also use subqueries (a query within a query) to come up with another possible answer. Here is an alternative, but less efficient, solution using a subquery:

select Salesperson.Name from Salesperson where Salesperson.ID = '{select Orders.salesperson_id from Orders, Customer where Orders.cust_id = Customer.id and Customer.name = 'Samsonic'}';