Practice SQL Interview questions and Answers

 

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.

Salesperson Customer
ID Name Age Salary
1 Abe 61 140000
2 Bob 34 44000
5 Chris 34 40000
7 Dan 41 52000
8 Ken 57 115000
11 Joe 38 38000
ID Name City Industry Type
4 Samsonic pleasant J
6 Panasung oaktown J
7 Samony jackson B
9 Orange Jackson B
Orders
Number order_date cust_id salesperson_id Amount
10 8/2/96 4 2 540
20 1/30/99 4 8 1800
30 7/14/95 9 1 460
40 1/29/98 7 2 2400
50 2/3/98 6 7 600
60 3/2/98 6 7 720
70 5/6/98 9 7 150

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.

Subscribe to our newsletter for more free interview questions.

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'}';

Click on the Next button below to check out the answer to parts B and C of this SQL interview question.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.



FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon

  • ceecee17

    Hello! Thought I should share a quiz I was given in an interview. Feel free to post solutions to the quiz, however I already submitted my answers and Im sure I failed

    Candidate _____________________________________________

    Sample Tables:

    employee (Table)

    emp_ID fname lname dept_ID

    ———– —— ——- ———–

    1 John Doe 3

    2 Jane Doe 2

    3 Ron Smith 3

    4 Nancy King 4

    5 James Monroe 1

    6 Lisa Jones 0

    department (Table)

    dept_ID dept_name

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

    1 Sales

    2 Customer Service

    3 Accounting

    4 Marketing

    5 Purchasing

    Write the SQL required
    to do the following:

    1.
    Add a new department named Janitorial Services whose department ID is 6.

    2.
    Modify the database to reflect that Ron Smith has changed jobs and now works in
    the Sales Department.

    3.
    Retrieve the last name and department name of all employees whose last name
    begins with A through J.

    • Varoon

      Thanks for the post!

    • Mohit Agarwal

      Ans.1> INSERT into department (dept_ID, dept_name)
      VALUES ('6','Janitorial Services')

      or in this case we can also write it like

      INSERT into department
      VALUES ('6','Janitorial Services')

      Ans.2> update into employee
      set dept_id = (select dept_id from department where dept_name = 'Sales')
      where fname = 'Ron' and lname = 'smith'

      Ans.3> For this particular problem we'll have to join both the table to get the desired output, as shown below:-

      select a.lname, b.dept_name from employee a, department b
      where a.dept_id = b.dept_id and lname in ('A%', 'B%','C%', 'D%','E%', 'F%','G%', 'H%','I%', 'J%)

      Kindly correct me if i'm wrong.

      Regards,
      Mohit Agarwal

      • Jithin

        The last query can be rewritten in the following way :-

        select a.lname, b.dept_name from employee a, department b
        where a.dept_id = b.dept_id and lname like [A-J]%;

  • Californian

    Dear Sql gurus:

    I have a series of numbers in my data source and all I want is the second digit to start with one when I search in my phpMyadmin area;

    It’s like this before:

    016340500000000000000000000
    006899400000000000000000000
    037898400000000000000000000

    and I want to find only 1′s in the second digit of these numbers.

    Can anything be done to achieve this?

    Thanks

    Californian

    • Raj

      You can use like operator with wildcard. i.e. column name like '_1%'

      • Rohan

        select case when CHARINDEX('1', MyValue)=0 then 0 else substring(RIGHT(MyValue, CHARINDEX('1', REVERSE(MyValue)) – 1),1,2) end from Mytable

    • Yousuf

      Hi i am not so experianced guy in SQL, but Substring can solve your query.

    • Mansur

      select substr('col_name', 1,1 ) from table_name;

  • hansraj

    Plz tell,,
    how to filter gmail and yahoomail from a table using sqlcommand

    • shekhar

      Let us suppose email_id is the column in user_table which contains email id's like user@gmail.com

      create table user_table_xx
      (
      user_name varchar2(15),
      email varchar2(50) primary key );

      Use below query to pick user_name from user_name@gmail.com

      select email_id, replace(email_id, '@gmail', '') user_name from user_table;

  • meghs

    First Answer should be like this because we have to sort by name not customer id!!!

    SELECT Salesperson.Name
    FROM Salesperson LEFT OUTER JOIN
    Orders ON Salesperson.ID = Orders.Salesperson_ID
    WHERE (Orders.CustID =
    (SELECT Id
    FROM Customer
    WHERE (Name = 'Samsonic')))

  • Nameless

    about the first sql query. its correct and wrong

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

    the number #1 is asking for "Samsonic" and not the cust_id. so the correct answer is the sql QUERY that have WHERE = 'Samsonic'

  • bangalore

    Given two tables: table1(player, ground_name, num_centuries);
    table2(ground_name, country);
    Write and SQL query for the following:
    " Select all the players from table1 who has made a century in every country."
    1 country could have more than 1 groudn name

  • HP

    select salesperson.name from salesperson,customer,orders where customer.name='samsonic' and customer.id=orders.cust_id and salesperson.id=orders.salesperson_id;

    I got correct results using this… is it fine to write like this?

  • shanmu

    how to apply instr and substr to this string ('1,234,,erer,-28,,').

    so as to find how many null values and commas present in that?

    and how to display the output in (dbms_output.put_line)?

  • Dipsee

    ANS 1:

    select * from Salesperson where ID in
    (
    select salespersonID from Orders where CustID in
    (
    select id from Customer where Name='Samsonic'
    )
    )

    ANS 2:

    select * from Salesperson where ID not in
    (
    select salespersonID from Orders where CustID in
    (
    select id from Customer where Name='Samsonic'
    )
    )

    • suresh

      short and sweet, good answer.

  • harsha

    i need a solution for this question…..?

    Mirrors Beauty Salon

    Marry and her husband James, opened Mirrors Beauty Salon on
    Main Road in Florida in 2008, offering nail styling, beauty treatments,
    hairdressing, manual therapy and massage. Originally from USA, they launched
    their business thanks to a loan from BOA.

    Nearly eighteen months on, Mary has established a
    reputation as one of the top nail technicians in the UK having won major
    Scottish and UK competitions for her nail designs. Last year at the Scottish
    Nail Technician of the Year Awards she came first in the category of Top Tech
    Sculpt and
    was declared runner up in the Nail Art Challenge category. She
    then went on to compete against nail technicians from throughout the UK and won
    the trophy, which was presented to her at the event in London, along with an
    award for coming first in the ‘free form sculpture’ event.

    Commenting on her award win, Marry said, “To compete at
    this level you need to be able to master everything from the latest trends such
    as 3D and stiletto, to embedded nail art, nail gems, baroque and free-hand
    sculpting. It’s a really exciting time to be part of such a dynamic, and
    creative industry and I am delighted to receive such prestigious recognition in
    the form of this award.”

    The husband and wife team have also recently secured the
    exclusive distribution rights for all Astonishing Nail and Spa products in England and USA, and plan to launch an AstonishingNails Training Academy for young beauticians and nail
    technicians to learn or refresh their skills.

    Questions

    1.
    Create all necessary tables, primary keys,
    foreign keys etc., Make sure all tables are in 3rd normal form.

    2.
    Enter required data in all tables to solve below
    questions. Make sure at least 50 to 100 rows in each main table and transaction
    tables at least 1000 rows.

    3.
    Write a query to display what all awards Marry
    received till now?

    4.
    From where Marry took bank loan and display
    complete loan repay history as of now.

    5.
    Write a query to display what all courses she
    did?

    6.
    List out the master tables and explain why they
    are master tables

    7.
    Write a query to display all their future plans.

    8.
    Write a Store procedure to calculate all their
    income and expenses which includes taxes, rewards etc.,

    9.
    Write a function to return award names by comma
    separated.

  • guest

    good one