Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in MS SQL Server

Returning all data from a table
Select some columns
Filtering rows
Logic
Text patterns
To be or NULL to be
A bit of mathematics
Let's practice
23. Put your skills into practice

Instruction

Nicely done! Here's a quick recap of what we learned today:

  • Each query starts with SELECT, followed by comma‐separated column names. To retrive all columns from a table, use the asterisk (*).
  • After SELECT comes the FROM caluse which indicates the name of the table from which you want to retrive the above data.
  • Optionally, you can use a WHERE clause to filter the rows.
  • You can use mathematical signs for comparison: >, >=, =, <=, <, !=, <>.
  • You can check, whether a value is inside an interval using BETWEEN.
  • You can compare string and text patterns using LIKE and the following two wildcards:
    • % for any number of characters (including zero)
    • _ for only one character
  • To list more than one conditional expression, use the AND and OR operators as needed. Remember: grouping matters. If needed use parentheses to group expressions.
  • NULLs are a little bit more complicated. You have to use IS NULL to check whether a value is NULL.
  • All conditions can be negated with the NOT operator.

Now, let's put together all the information we've learned so far. Let's imagine a customer who walks in and wants to know if we have any cars that meet his needs.

Exercise

Select all columns for cars that:

  • Were produced between 1999 and 2005.
  • Are not Volkswagens.
  • Have a model beginning with either 'P' or 'F'.
  • Have a set price.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM Car
WHERE ProductionYear BETWEEN 1999 AND 2005
  AND Brand != N'Volkswagen'
  AND (Model LIKE N'P%' OR Model LIKE N'F%')
  AND Price IS NOT NULL;