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

FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon
  • asjd

    using just 1 UPDATE statement how will you update salaries:

    1.increase salary by 5000 where salary between 30000 and 40000
    2.increase salary by 7000 where salary between 40000 and 50000
    3.increase salary by 9000 where salary between 50000 and 60000

  • KARTIK JADHAV

    Q.3) Consider the database given below, where the primary
    keys are underlined. Construct the following SQL queries for this relational
    database.

    employee (person-name, street, city)

    works (person-name, company-name, salary)

    company (company-name, city)

    manages (person-name,
    manager-name)

    a)
    create the above tables by properly specifying
    the primary keys and foreign keys and named constraints

    b)
    enter atleast 5 records in each table.

    c)
    Rename works table to emp_works

    d)
    Find the employee detail with highest salary

    e)
    Create a view with attributes
    person_name,salary,company_name

  • KARTIK JADHAV

    Q.1)Consider the database given below, where the primary
    keys are underlined. Construct the following SQL queries for this relational
    database.

    person (driver id, name, address)

    car (license, model, year)

    accident (report number, date, location)

    owns (driver id, license)

    participated (driver
    id, car, report number, damage amount)

    a)create the above tables by properly specifying the primary
    keys and foreign keys and named constraints.

    b)enter atleast 5 records in each table.

    c)list out all the report numbers where the damage amount is
    between 3000 and 8000

    d) increment the damage amount by 5% for the accidents
    reported I the month of june.

    e) list out yearwise count of accidents taking place

  • KARTIK JADHAV

    Q.2) Consider the database given below, where the primary
    keys are underlined. Construct the following SQL queries for this relational
    database.

    Customer(cust_id,cust_name,city,state,ph_no)

    Item(item_id,item_name,price

    Sale(bill_no,bill_date,cust_id,item_id,qty_sold)

    a)
    create the above tables by properly specifying
    the primary keys and foreign keys and named constraints

    b)
    enter atleast 5 records in each table.

    c)
    Display itemwise quantity sold

    d)
    Find out all customers who live in the same city
    as that of customer anil and pankaj

    e)
    Alter table sale to drop the column bill_date
    from the table

  • عبد الر حمن علي

    Explain the four techniques in creating a Performance Monitoring Strategy on SQL Server 2008.

  • Name

    Nice

  • JONES5122

    GIVEN TWO TABLES :
    FIRST TABLE NAME “EMPLOYEE ” ,CONSISTS OF ID,NAME,CITYCODE .
    SECOND TABLE NAME “CITY”,CONSISTS OF CITYCODE, CITYNAME .

    I NEED THE EMPLOYEE’S NAME WHO DOESNT HAVE THEIR CITYNAME FOR THEIR CITYCODE FROM TABLE EMPLOYEE(ONE)???
    CAN ANY ONE PLS HELP ME ??

  • kumar

    a)

    select s.name

    FROM

    sales_person s

    inner join

    orders o

    on(salesperson_id=id)

    inner join

    customer c

    on(o.cust_id=c.id and c.name=’samsonionic’)

    b)

    select s.name

    FROM

    sales_person s

    inner join

    orders o

    on(salesperson_id=id)

    inner join

    customer c

    on(o.cust_id=c.id and c.name !=’samsonionic’)

    c)select

    s.name,

    count(number) ,

    from salesperson

    inner join

    Orders

    on(s.id=o.salesperson_id)

    group by s.name

    having count(number)>=2

  • Sharanyu Ghosh

    The first answer is “select name from salesperson where ID in(select salesperson_id from orders where cust_id
    in(select ID from customer where name=’Samsonic’));”… as simple as that!!Join is not always necessary and also it increases complexity and space by combining tables!!

  • Emmy

    So I can’t figure out what’s wrong with my statement. I’m using SQL Management Studio and it is just giving me random syntax errors.:

    Select DISTINCT S.[NAME]
    FROM (CUSTOMER C LEFT JOIN ORDERS O ON (C.[ID]) = (O.[Cust_Id])) LEFT JOIN
    SALESPERSON S ON (O.[Sales_Id]) = (S.[ID]))
    WHERE C.NAME = “Samsonic”;

  • manoj

    Select Name from Salesperson Where ID IN (Select Salesperson_id from Orders Where Cust_id = 4);

  • Nivesh Singh

    Ans 1: select s.* from salesperson s, orders o, customer c where s.Id = o.salesperson_id and o.cust_id=c.id and c.name = ‘Samsonic';
    Ans 2: — c.name!=’Samsonic’
    Ans 3: Select * from salesperson where id in ( select cust_id from orders group by cust_id having count(number)>=2);
    Enjoy!!!

  • Barack

    question 3

    Select distinct
    salesperson.name

    From salesperson

    And salesperson.id IN

    (

    Select orders.salesperson_id

    From orders

    Group by orders.salesperson_id

    Having count(orders.id) >= 2

    )

  • Damir Olejar

    Naturally, the question B then becomes:

    Select Salesperson.Name from Salesperson
    where Salesperson.Name NOT IN
    (SELECT DISTINCT Salesperson.name
    FROM Salesperson
    JOIN (
    SELECT Orders.salesperson_id
    FROM Orders
    JOIN Customer ON Orders.cust_id = Customer.ID
    WHERE Customer.Name = ‘Samsonic’) AS d ON Salesperson.ID = d.salesperson_id) ;

  • Damir Olejar

    This one works just fine too…

    SELECT DISTINCT Salesperson.name
    FROM Salesperson
    JOIN (
    SELECT Orders.salesperson_id
    FROM Orders
    JOIN Customer ON Orders.cust_id = Customer.ID
    WHERE Customer.Name = ‘Samsonic’) AS d ON Salesperson.ID = d.salesperson_id ;

  • awinterviewee

    Hi can someone just confirm what I’m doing is correct? I am using MSFT SQL Serve 2008 for syntax.

    a.
    select salesperson.name from salesperson a inner join
    orders b on a.ID=b.salesperson_id
    where cust_id= 4

    b.
    select salesperson.name from salesperson a inner join
    orders b on a.ID=b.salesperson_id

    where salesperson_id not in(select salesperson_id from Orders where cust_id=4)

    c.
    select salesperson.Name from salesperson a
    inner join (select salesperson_id, sum(1.0) as NumOrders from Orders) b on a.salesperson_id=b.salesperson_id

    d.
    insert into highAchiever(Name, Age)
    select Name, Age from Salesperson where Salary>=100000

  • Kanishka

    Which is more efficient the joins or where clauses ?

  • http://www.dinelogik.com Ted Bradley

    Fyi order_date values are not in SQL format http://www.w3schools.com/sql/sql_dates.asp
    I found this out while trying to complete Practice Interview Question 2 using the tables from this question.

  • yhk

    I don’t know answer for question 3 and 4 plz help me

    • yhk

      question c and d

  • sanjay

    Query to list the student_id/student_name (first_name, space, last_name), who had taken exactly 2 CS courses (major_id=2) and had taken exactly 1 EE course(major_id=3) in the same trimester ‘SUM-2002’

    output

    STUDENT_ID NAME

    4962 Smith, John

    5089 Wilson, Lee

    • sanjay

      can anyone plz help me this really dnt knw how to solve this got a query from university. may b simple but no idea how to??

  • Shalu

    I have a web application of 100 users. I have a condition that only 10 users should able to access my web application at a time.How to use sql query or stored procedure for this requirement?.Help me out guys.Do we have any other feature helps to do this other than stored procedure?.

  • SMH

    Count number of Employee per team in single table

    I want o/p as
    Team A 20
    Team B 10
    ..
    ..
    ..

  • Mohamed salem

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

    select distinct Salesperson.Name from Salesperson, Customers,Orders

    where Customers.ID=Orders.cust_id and Orders.salesperson_id=Salesperson.ID

    and Orders.salesperson_id not in (select salesperson_id from Orders where Orders.cust_id=4)

    –OR

    select distinct Salesperson.Name from Salesperson

    where ID in ( select Orders.salesperson_id from Orders

    where Orders.salesperson_id not in (select salesperson_id

    from orders where ( Orders.cust_id in

    (select Customers.ID from Customers where

    Customers.Name =’Samsonic’))))

  • Mohamed salem

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

    select Salesperson.Name from Salesperson

    where ID in ( select Orders.salesperson_id from Orders where cust_id in (select Customers.ID

    from Customers where

    Customers.Name =’Samsonic’))

    –OR

    select Salesperson.Name from Salesperson, Customers,Orders

    where Customers.ID=Orders.cust_id and Orders.salesperson_id=Salesperson.ID and Customers.Name=’Samsonic’

    • pawan

      good one

  • guest

    good one

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

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

  • 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)?

  • 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?

  • 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

    • Priyanka

      select distinct(p.player) from table1 p

      join table2 g on p.ground_name=g.ground_name

      where not exists(

      select distinct(country) from table2

      where country not in

      (select distinct(country) from table1

      join table2 on table1.ground = table2.ground

      where player=p.player and num_centuries>0))

      • coder.girl

        Please explain your query. Please.

    • abhay mone

      select s1.player_id
      from (select count(distinct b.country),a.player_id
      from a,b
      where a.groundname=b.groundname
      group by a.player_id
      having count(distinct b.country)=(select count(distinct country) from b)
      order by player_id)s1

    • abc

      plz explain this query

    • Yash Wanth

      the best answer for this question is

      select player,country
      from table1,table2
      where table1.ground_name=table2.ground_name
      group by country,player

  • 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'

    • Abhaya

      This is the right ans But @Mega why Left Outer Join.
      Select s.Name from salesperson S , order O
      where S.Id=O.salesperson_ID
      and O.cust_id= (Select ID from Cutomer where Name =’Samsonic’)

  • 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')))

  • 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;

  • 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

      • sql guru

        SELECT YourSoCalledNumber FROM YourSoCalledTable WHERE YourSoCalledNumber LIKE ‘_1%’

        just kidding 😀 😉

      • Adarsh

        column name LIKE “_1%”

    • Yousuf

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

      • sql guru

        SELECT YourSoCalledNumber FROM YourSoCalledTable WHERE YourSoCalledNumber LIKE ‘_1%’

        just kidding :)

    • Mansur

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

      • Simon

        select substr(‘col_name’, 2,1 ) from table_name;

  • 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]%;

        • Priyanka

          This query in not working in mysql. May be working in other RDBMS. But I have different solution
          lname < 'K'

          • Edwin GK

            insert into department ( dept_id,dept_name) values (‘6′,’Janitorial Services’)

          • Edwin GK

            qstn 2

            update employee set dept_Id = ‘1’ where employee.fname = Ron