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.

66 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. 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
  5. This comment has been removed by the author.

    ReplyDelete
  6. 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

  7. 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
  8. This comment has been removed by the author.

    ReplyDelete
  9. 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
  10. Nice set of interview questions and answers.i have gone through it very helpful one thanks for sharing...
    SQL Interview Questions & Answers

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

    ReplyDelete
  12. 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
  13. 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
  14. This comment has been removed by the author.

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

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

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

    ReplyDelete
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. Situs judi bola online UFA88 Terpercaya, juga memiliki casino online seperti, Baccarat online, roulette, Judi slot online, sabung ayam dan dadu online.

    ReplyDelete
  24. Adam the alien - Animated films A close encounter with a likable robot from another solar system leads to a star-crossed friendship Do aliens exist and an impossible intergalactic mission to save Extraterrestrial life the robot’s friends— despite overwhelming New cartoon movies odds.

    ReplyDelete
  25. TogelResult.info - Adalah Situs data hk Khusus yang menyediakan Daftar keluaran hk Result Nomor Togel dan Data hasil keluaran live draw hk seperti hk sgp sydney yang sudah terpercaya keluaran togel hk.

    ReplyDelete
  26. Website laten maken? Onze professionals bouwen een omzetverhogende, professionele én unieke website of webshop! Een website laten maken kan vandaag nog

    Website laten maken | webdesigner | website freelancer | webdeveloper | webdesigner

    ReplyDelete
  27. Discover antimicrobial protection that’s built-in right into the finish of select TikTok Locksmith Services.

    ReplyDelete
  28. In either case, your attention to this serious problem is desperately needed on the part of the sales team. It cannot function as a sales team unless you build the confidence of the members that you're right for the job. Salesforce training in Chennai

    ReplyDelete
  29. Fire Alarm Houston, a RAB Security company, was founded in 2001. The purpose was to provide the Life Safety System to Houston, Harris County, Fort Bend County and the surrounding areas, a viable alternative to the overgrown nationwide companies. We pride ourselves on being the most trusted, well-rounded and customer-oriented security company in Houston, backed by over seven years of company history and national manufacturer brand names that you know and trust, such as Honeywell.

    ReplyDelete
  30. Fire Sprinkler Texas has been providing fire sprinkler installation, inspection and services for over 30 years in Texas, mainly in the Greater Houston, Dallas, San Antonio or Austin and surrounding area. If you are building a new facility which requires a fire sprinkler system by code, please give us a call for a free consultation.

    ReplyDelete
  31. We are a full service internet marketing company in the Pittsburgh area, that specializes in providing affordable internet marketing solutions for small businesses.
    Our Pittsburgh SEO companies team builds and enhances local business listings and seo search engine optimization. This service drives lead generation and brand recognition.
    Our goal is long term mutual relationships with small business owners, providing customized internet marketing services.

    ReplyDelete
  32. I read this article. I think You put a lot of effort to create this article. I appreciate your work.
    rebel without a cause jacket

    ReplyDelete
  33. Royal Moving Co. is an affordable Los Angeles Moving Company for both local moving, Movers near me long distance moving and packing services throughout Los Angeles county. We Moving company near me offer professional, affordable service.

    ReplyDelete
  34. Our the purpose is to share the reviews about the latest Jackets,Coats and Vests also shre the related Movies,Gaming, Casual,Faux Leather and Leather materials available Raiders White Jacket

    ReplyDelete
  35. Our the purpose is to share the reviews about the latest Jackets,Coats and Vests also shre the related Movies,Gaming, Casual,Faux Leather and Leather materials available Raiders White Jacket

    ReplyDelete
  36. I am really happy with your blog because your article is very unique and powerful for new.
    Best AWS Training in Pune
    Best RPA Training in Pune
    Selenium Training in Pune

    ReplyDelete
  37. Pretty good post. I have really enjoyed reading your blog posts.Any way Here I am Specialist in Manufacturing of Movies, Gaming, Casual, Faux Leather Jackets, Coats And Vests See 4th Hokage Cloak

    ReplyDelete
  38. According to a recent report from Bild reporter Christian Falk, Chelsea manager Thomas Tuchel has placed Salah as a key target for his reinforcement this summer. Along with options like Borussia Dortmund's Erlingbrout Haand and Inter Milan's Romelu Lukaku. ufabet

    ReplyDelete
  39. Internet slots (Slot Online) may be the release of a gambling machine. Slot machine As pointed out Used to produce electronic gaming systems referred to as web-based slots, as a result of the improvement era, folks have considered gamble by way of computers. Will achieve slot online games making internet gambling online games Via the internet network process Which players are able to play through the slot plan or maybe will play Slots with the system provider's site Which internet slots gaming systems are actually on hand in the type of taking part in policies. It's similar to taking part in on a slot machine. Each of those realistic pictures and also sounds are likewise thrilling since they go to living room in the casino in the world.บาคาร่า
    ufa
    ufabet
    แทงบอล
    แทงบอล
    แทงบอล

    ReplyDelete
  40. Great suggestion to learn programming. Thank you for this nice post.

    The gypsum decoration is a new material that can be installed without any special skills. It's so versatile and beautiful, you'll wonder why it isn't more popular! Choose from over 200 designs to find the one of your dreams - there are plenty for every taste, color palette, or era.

    ReplyDelete
  41. Pretty good post. Waiting for others.

    Eco technology purifies water. The scarcity of pure drinking water is a major concern. Through the use of various technologies a lot of campaigns have been successful in providing people with clean drinking water. Eco technology is the best environmental Testing Inspection and Service Provider in Bangladesh. They use the latest technology for this section.

    ReplyDelete
  42. Your site is good Actually, i have seen your post and That was very informative and very entertaining for me. Negan Jacket

    ReplyDelete
  43. I am really happy with your blog because your article is very unique and powerful

    Lyricstock means All Lyrics

    ReplyDelete