Please navigate to the bottom of the page for Table of Contents

Sunday, June 12, 2011

SQL Select Where Interview Questions

SQL is one of my favorite interview areas. If properly phrased, the interviewer can go in depth and look at a candidate’s ability to clearly define the logic and think on their feet. Most of the white boarding questions involve some flavor of SELECT statement and almost always include WHERE, GROUP BY, ORDER BY and JOINS. In this post we will explore the SELECT statement with WHERE clause. In addition, I will be focusing on Microsoft SQL Server 2008 version of the queries. We will explore Oracle, MySql, etc. in a later post.

SQL SELECT statement

A SELECT statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. In it’s fully complex form, a SELECT statement can be represented as:

<SELECT statement> ::=  

[WITH <common_table_expression> [,...n]]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } (expression )} [ ,...n ]
[ BY expression [ ,...n ] ]
]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]

<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]

<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]

< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]


The most basic form or a SELECT statement allows you to either retrieve all or selected columns from a given table. The only required condition is a FROM clause.

 

SELECT * FROM CUSTOMER ;

SELECT FIRSTNAME, LASTNAME FROM CUSTOMER ;


The FROM clause does not limit you to one table. In fact, you can specify multiple tables. In the query shown below, the  statement forms a virtual table that combines the data from the CUSTOMER table with the data from the INVOICE table. Each row in the CUSTOMER table combines with every row in the INVOICE table to form the new table. If the CUSTOMER table has 100 rows and the INVOICE table has 100, the new virtual table has 10,000 rows. This is a JOIN in it’s simplest form.


SELECT * FROM CUSTOMER, INVOICE ;


SQL WHERE clause


A WHERE clause allows you to specify the search condition for the rows returned by the SELECT statement. This clause is primarily used to limit the number of rows returned by or affected by the statement. The definition of the WHERE clause looks very simple.


[ WHERE <search_condition> ]

The <search_condition> defines the condition to be met for the rows to be returned. There is no limit to the number of predicates that can be included in a search condition. The condition in the WHERE clause may be simple or arbitrarily complex.You may join multiple conditions together by using the logical connectives AND, OR, and NOT. The comparison predicates (=, <, >, <>, <=, and >=) are the most common, but SQL offers several others that greatly increase your capability to distinguish, or filter out, a desired data item from others in the same column. The following list notes the predicates that give you that filtering capability: Comparison predicates (=, <, >, <>, <=, and >=), BETWEEN, IN [NOT IN], LIKE [NOT LIKE], SIMILAR, NULL, ALL, SOME, and ANY, EXISTS, UNIQUE, DISTINCT, OVERLAPS and MATCH.


Enough theory! Now let’s explore some simple interview questions that involve the SELECT and WHERE clauses. Many of the examples below also use some other SELECT constructs such as ORDER BY, etc.


Finding a row by using a simple equality



SELECT ID, Name
FROM Product
WHERE Name = 'Blade' ;


Finding rows that contain a value as a part of a string



SELECT ID, Name, Color
FROM Product
WHERE Name LIKE ('%Frame%');


Finding rows by using a comparison operator



SELECT ID, Name
FROM Product
WHERE ProductID <= 12 ;


Finding rows that meet any of three conditions



SELECT ID, Name
FROM Product
WHERE ProductID = 2
OR ProductID = 4
OR Name = 'Spokes' ;


Finding rows that must meet several conditions



SELECT ID, Name, Color
FROM Product
WHERE Name LIKE ('%Frame%')
AND Name LIKE ('HL%')
AND Color = 'Red' ;


Finding rows that are in a list of values



SELECT ID, Name, Color
FROM Product
WHERE Name IN ('Blade', 'Crown Race', 'Spokes');


Finding rows that have a value between two values



SELECT ID, Name, Color
FROM Production.Product
WHERE ProductID BETWEEN 725 AND 734;


Checking for NULL values



SELECT ProductID, Name, Weight
FROM Product
WHERE Weight IS NULL


The SELECT statement can also change the column heading and perform calculations. The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product.


Finding total sales and discounts for each product



SELECT p.Name AS ProductName, 
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Product AS p
INNER JOIN SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;


Calculate total revenue for each product



SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName
FROM Product AS p
INNER JOIN SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC;


This blog post barely touches the surface of the SELECT statement and the WHERE clause. In the next post we will explore the other clauses and keywords that are most popular in SQL interview questions.

63 comments:

  1. Replies
    1. Hi Nikhil,

      Great info! I recently came across your blog and have been reading along.
      I thought I would leave my first comment. I don’t know what to say except that I have
      I am searching information about video in java, do you know some framework?
      I need a little bit of orientation.
      By looking at the initial JVM configuration (start-up options) and nature of performance bottleneck, these configuration parameters can be set appropriately.
      By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
      Please keep providing such valuable information.

      Thanks a heaps,
      Lee

      Delete
  2. Thank you so much..Most of my doubts got cleared..Thank you so much ...
    The sap hana training in dubai
    will provide u the best training...

    ReplyDelete
  3. Hello, Thanks for sharing.
    These are good questions to refresh your knowledge before an interview. A technical interview would also consist of practical SQL Interview Questions

    ReplyDelete
  4. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like thisselenium training in chennai

    ReplyDelete
  5. The great service in this blog and the nice technology is visible in this blog. I am really very happy for the nice approach is visible in this blog and thank you very much for using the nice technology in this blog

    AWSTraining in Bangalore|

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. The great service in this blog and the nice technology is visible in this blog.It's very useful information. thanks for sharing..


    T-SQL Training

    ReplyDelete

  8. Hello Buddy,


    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came
    across this.

    I am trying to generate a flat file using SSIS 2014 and want to use ý ascii(0253) instead of comma in the delimited csv file, please can you advise.



    Thank you very much and will look for more postings from you.


    MuchasGracias,
    Irene Hynes

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Nice Post I learned a lot From the Post Thanks for sharing, learn the most ON-DEMAND software Training in Best Training Institutions
    Instructor-LED Salesforce Online Training
    Professional Salesforce CRM Training
    Salesforce Training online in India

    ReplyDelete
  11. Nice set of interview questions and answers.i have gone through it very helpful one thanks for sharing...
    SQL Interview Questions & Answers

    ReplyDelete
  12. The article is so informative. This is more helpful.
    software testing training courses
    selenium course
    Thanks for sharing

    ReplyDelete
  13. I Got Job in my dream company with decent 12 Lacks Per Annum salary, I have learned this world most demanding course out there in the current IT Market from the Big Data Hadoop Training In Bangalore experts who helped me a lot to achieve my dreams comes true. Really worth trying.

    ReplyDelete
  14. Although when you have become an expert you will be able to write many programs quickly, you must remember that many programs have taken whole teams of expert developers years to create.Why use Laravel

    ReplyDelete
  15. Nice post I have been searching for a useful post like this on salesforce course details, it is highly helpful for me and I have a great expereince with this
    Salesforce Training  which is a best institute for career building program.

    ReplyDelete
  16. It's very useful post and i had good experience with this salesforce training in bangalore who are offering good certification assistance. I would say salesforce training is a best way to get certified on crm.

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. I have been searching for a useful post like this on salesforce course details, it is highly helpful for me and I have a great expereince with this
    Salesforce Training who are providing certificaiton and job asistance and see Salesforce Training in Hyderabad
    Salesforce training in ameerpet

    Salesforce training in kukatpally
    Salesforce training in madhapur
    Salesforce training in gachibowli

    ReplyDelete
  19. Thanks for sharing the information...
    Salesforce CRM Training in Marathahalli - Bangalore | Salesforce CRM Training Institutes | Salesforce CRM Course Fees and Content | Salesforce CRM Interview Questions - eCare Technologies located in Marathahalli - Bangalore, is one of the best Salesforce
    CRM Training institute with 100% Placement support. Salesforce CRM Training in Bangalore provided by Salesforce CRM Certified Experts and real-time Working Professionals with handful years of experience in real time Salesforce CRM Projects.

    ReplyDelete
  20. Blockchain Course in Bangalore with 100% placement. We are the Best Blockchain Course Institute in Bangalore. Our Agile testing course and Certification courses are taught by working professionals who are experts in Blockchain.

    Blockchain Training in Bangalore

    Blockchain course in bangalore

    Blockchain in bangalore

    Blockchain classes in bangalore

    Blockchain course institute in bangalore

    Agile testing course and Certification course syllabus

    best Blockchain course

    Blockchain course centers

    ReplyDelete
  21. It's very useful post and i had good experience with this salesforce training in Chennai who are offering good certificaiton assistance. I would say salesforce training is a best way to get certified on crm.

    ReplyDelete
  22. The patent was conceded on August 17, 1966 and is by all accounts one of the principal software licenses.
    application

    ReplyDelete
  23. Pretty article! I found some useful information in your blog....

    so here we provide,

    We provide you with flexible services and complete hybrid network solutions. It can provide your organisation with exceptional data speeds, advanced external security protection, and high-resilience by leveraging the latest SD-WAN and networking technologies to monitor, manage and strengthening your organisation’s existing network devices.

    https://www.quadsel.in/networking/>
    https://twitter.com/quadsel/
    https://www.linkedin.com/company/quadsel-systems-private-limited/
    https://www.facebook.com/quadselsystems/

    #quadsel #network #security #technologies #managedservices #Infrastructure #Networking #OnsiteResources #ServiceDeskSupport #StorageServices #WarrantyAMCServices #datacentersolutions #DataCenterBuild #EWaste #InfraConsolidation #DisasterRecovery #NetworkingServices #ImagingServices #MPS #Consulting #WANOptimisation #enduserservices

    ReplyDelete
  24. A backlink is a link created when one website links to another. Backlinks are important to SEO & impact for higher ranking. In my 7+ years seo Career i see, without backlinks a website doesn't rank higher on google SERP.

    Get Your 300+ High Quality DoFollow Backlinks Here!

    Order Now with Full Confidence & 100% satisfaction.

    ReplyDelete
  25. With Brandsoo, it’s never been easier company brand names for sale or more convenient to shop high quality domain names and professional logos that’ll instantly give your brand a leg up and resonate with your audience! With many different domain sellers all competing for your business, you need to know where your business and brand will see.

    ReplyDelete
  26. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot.

    SAP Online Training

    SAP Classes Online

    SAP Training Online

    Online SAP Course

    SAP Course Online

    ReplyDelete
  27. Forex Signals, MT4 and MT5 Indicators, Strategies, Expert Advisors, Forex News, Technical Analysis and Trade Updates in the FOREX IN WORLD

    Forex Signals Forex Strategies Forex Indicators Forex News Forex World

    ReplyDelete