SQL Queries For Mere Mortals: Creating a Simple Query

Simple Select Statment:

Don't write SQL Query immediately after reading the problem statement:

• Search for nouns in the problem statement as it may be a table name or a column name.
• Map the extracted nouns with the suitable table and column names.
• Try to translate the statement to an abstract query

• Finally, Write The SQL Version of it.

  • Example:
    Problem statement: "I need the names and addresses of all our employees."
    Translation: Select first name, last name, street address, city, state and ZIP Code from the employees table
    SQL:

    SELECT FirstName, LastName,StreetAddress, 
    City, State, ZipCode FROM Employees;
    

  • Example 2:
    Problem statement: "Show me a list of vendor names in ZIP Code order in descending order:"
    Translation: Select vendor name and ZIP Code from the vendors table and order by ZIP Code DESC.
    SQL:

    SELECT VendName, VendZipCode
    FROM Vendors
    ORDER BY VendZipCode DESC;
    

Sorting Tips:

Simple Select Statment With Order by:

• In "Example 2" , if there is more than one vendor has the same ZIP Code, your DBMS determines the sort order by ordering vendor names Ascendingly.

• By default DBMS sorts columns in ascending order even if you don't explicitly write ASC

• The sequence of the columns in the ORDER BY clause is important, because DBMS will evaluate the columns in the ORDER BY clause from left to right.

• You can explicitly define how you want to order every column in the query, so if there is a tie in first one, the second will fire.
  • Example:
    Problem statement: Give me a list of all tournament dates and locations. I need the dates in descending order and the locations in alphabetical order.”
    Translation: SELECT tournament date , tournament location from the tournaments table sort date descending and location alphabetically.
    SQL:

    SELECT 
    TourneyDate, TourneyLocation
    FROM
    tournaments
    ORDER BY TourneyDate DESC , TourneyLocation ASC;
    

    Explanation: The DBMS will sort first every row by "TourneyDate" descendingly, when there is a tie i.e. two rows have the same value for "TourneyDate" the DBMS will use the second column "TourneyLocation" defined in the ORDER BY Clause to sort and so on...

Eliminating Duplicate Rows:

• DISTINCT is an optional keyword that precedes the list of columns specified in the SELECT clause.
• The DISTINCT keyword asks your DBMS to evaluate the values of all the columns as a single unit on a row-by-row basis and eliminate any redundant rows it finds.
• The remaining unique rows are then returned to the result set.
  • Example:
    Problem statement: “Which cities are represented by our bowling league membership?”
    Translation: Select Unique city name from the bowlers table
    SQL: sql SELECT DISTINCT City FROM Bowlers;sql

• You can use the DISTINCT keyword on multiple columns as well.
  • Example 2:
    SQL: sql SELECT DISTINCT City, State FROM Bowlerssql
    Explanation: This Query will guarantee that for every combination of city and state will be only one raw in the result set.

Side Note:

• The DBMS products from Microsoft like Microsoft Office Access and Microsoft SQL Server include an interesting extension that allows you to request a subset of rows based on your ORDER BY clause by using the TOP keyword in the SELECT clause.
  • Example:
    Problem statement: “List the five most expensive products”
    SQL:

    SELECT TOP 5 ProductName, RetailPrice FROM Products ORDER BY RetailPrice DESC;
    

• Both database systems also allow you to specify the number of rows returned as a percentage of all the rows.
  • Example 2:
    Problem statement: “List the top 10 percent of products by price”
    SQL:

    SELECT TOP 10 PERCENT ProductName, RetailPrice FROM Products ORDER BY RetailPrice DESC;