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

76 Responses to “Practice Interview Question 1”

  1. 387836 390299Wow you hit it on the dot we shall submit to Plurk in addition to Squidoo well done انواع محركات الطائرات 91932

  2. 449302 362375I like this post a whole lot. I will undoubtedly be back. Hope that I will likely be able to read a lot more insightful posts then. Will probably be sharing your information with all of my associates! 396520

  3. 707968 756810Really usefull weblog. i will follow this weblog. maintain up the excellent function. 433127

  4. 650545 948278Hey I was just searching at your website in Firefox and the image at the top of the link cant show up properly. Just thought I would let you know. 19410

  5. 487772 790285Glad to be one of numerous visitants on this awesome website : D. 584819

  6. 686862 608920There is noticeably a bundle to learn about this. I assume you made specific nice points in functions also. 889092

  7. 963709 18987This is the first time I frequented your web page and so far? I amazed with the research you made to create this actual submit amazing. 917794

  8. 439611 251371The overall look of your website is excellent, let neatly as the content material! 952058

  9. 40224 321093What cell telephone browser is this internet site page optimized for Internet explorer? 411662

  10. 972174 692676When I originally commented I clicked the -Notify me when new surveys are added- checkbox and from now on whenever a comment is added I purchase four emails sticking with the same comment. Perhaps there is by any means you may get rid of me from that service? Thanks! 654635

  11. pest control says:

    926675 442461Spot on with this write-up, I actually suppose this internet web site needs rather a lot more consideration. most likely be once a lot more to learn significantly a lot more, thanks for that information. 426274

  12. pest control says:

    751426 365582Cheapest speeches and toasts, as properly as toasts. probably are produced building your personal at the party and will likely be most likely to turn into witty, humorous so new even. finest man toast 999618

  13. 524359 373592Really nice style and design and excellent content , nothing at all else we need : D. 724846

  14. 775754 851317That is some inspirational stuff. Never knew that opinions could be this varied. Be positive to maintain writing. 513624

  15. 262683 470744I view something genuinely special in this website . 253239

  16. YMgSzGqkiehV says:

    206407 547151Ive writers block that comes and goes and I require to discover a method to get rid of my writers block. It can occasionally be so bad I can barley make sentences. Any suggestions? 310490

  17. asjd says:

    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

  18. Yash Wanth says:

    the best answer for this question is

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

  19. KARTIK JADHAV says:

    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

  20. KARTIK JADHAV says:

    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

  21. KARTIK JADHAV says:

    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

  22. Adarsh says:

    column name LIKE “_1%”

  23. عبد الر حمن علي says:

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

  24. JONES5122 says:

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

  25. kumar says:

    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

  26. Sharanyu Ghosh says:

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

  27. abc says:

    plz explain this query

  28. Emmy says:

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

  29. manoj says:

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

  30. Nivesh Singh says:

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

  31. abhay mone says:

    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

  32. Barack says:

    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

    )

  33. coder.girl says:

    Please explain your query. Please.

  34. Damir Olejar says:

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

  35. Damir Olejar says:

    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 ;

  36. awinterviewee says:

    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

  37. Kanishka says:

    Which is more efficient the joins or where clauses ?

  38. pawan says:

    good one

  39. Ted Bradley says:

    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.

  40. yhk says:

    question c and d

  41. yhk says:

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

  42. sanjay says:

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

  43. sanjay says:

    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

  44. Abhaya says:

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

  45. Shalu says:

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

  46. Edwin GK says:

    qstn 2

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

  47. Edwin GK says:

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

  48. Priyanka says:

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

  49. Priyanka says:

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

  50. SMH says:

    Count number of Employee per team in single table

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

  51. Mohamed salem says:

    — 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’))))

  52. Mohamed salem says:

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

  53. Simon says:

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

  54. sql guru says:

    SELECT YourSoCalledNumber FROM YourSoCalledTable WHERE YourSoCalledNumber LIKE ‘_1%’

    just kidding 😀 😉

  55. sql guru says:

    SELECT YourSoCalledNumber FROM YourSoCalledTable WHERE YourSoCalledNumber LIKE ‘_1%’

    just kidding :)

  56. Mansur says:

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

  57. suresh says:

    short and sweet, good answer.

  58. guest says:

    good one

  59. harsha says:

    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.

  60. Yousuf says:

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

  61. Dipsee says:

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

  62. Rohan says:

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

  63. shanmu says:

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

  64. HP says:

    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?

  65. Raj says:

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

  66. bangalore says:

    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

  67. Jithin says:

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

  68. Nameless says:

    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'

  69. meghs says:

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

  70. shekhar says:

    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;

  71. hansraj says:

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

  72. Mohit Agarwal says:

    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

  73. Californian says:

    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

  74. Varoon says:

    Thanks for the post!

  75. ceecee17 says:

    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.

Leave a Reply

*