SQL - Sample Interview Question (Senior Level)

Considering the following tables

Books
Columns: book_id (number, primary key), book_title (char), author_name (char)

Shops
Columns: shop_id (number, primary key), shop_name (char), location (char)

Shop_Books
Columns: shop_id (number), book_id (number)

Combination of values of shop_id and book_id in 
Shop_Books table is unique.

Which SELECT statement shows all the books which author name contains 'Allen' and can be purchased in shops situated in USA (location = 'USA')?

  1. SELECT B.BOOK_TITLE
    FROM BOOKS B
    WHERE B.AUTHOR_NAME = 'Allen'
    AND EXISTS (SELECT NULL
    FROM SHOP_BOOKS SB
    , SHOPS S
    WHERE SB.BOOK_ID = B.BOOK_ID
    AND SB.SHOP_ID = S.SHOP_ID
    AND S.LOCATION = 'USA')
  2. SELECT B.BOOK_TITLE
    FROM BOOKS B
    WHERE B.AUTHOR_NAME LIKE '%Allen%'
    AND EXISTS (SELECT NULL
    FROM SHOP_BOOKS SB
    , SHOPS S
    WHERE SB.BOOK_ID = B.BOOK_ID
    AND SB.SHOP_ID = S.SHOP_ID
    AND S.LOCATION = 'USA')
  3. SELECT B.BOOK_TITLE
    FROM BOOKS B
    WHERE B.AUTHOR_NAME LIKE '%Allen%'
    AND EXISTS (SELECT NULL
    FROM SHOP_BOOKS SB
    WHERE SB.BOOK_ID = B.BOOK_ID
    AND SB.SHOP_ID = SB.SHOP_ID)
  4. None of the above
Tests in SQL for Seniors

Test Name Difficulty Time (minutes) No. of Questions
SQL Online Test for Seniors SQL Senior 20 10