≡ Menu

Essential SQL

Want to Get Started Using SQL Server?

Databases Have Beautiful Structure

Now it is time to learn SQL in simple English.

  • Receive our free guide Getting Started with SQL Server!
  • Understand fundamental database concepts
  • Take your job to the next level.

Click Here To Get Started!

Get Ready to Learn SQL: 12. Introduction to Database Joins

Tiling_by_Patterns_stock

Introduction

One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table.  In this series of articles we are going to show you how to write a query that combines, or joins, data from more than one table. Once you have gone through the examples you will understand how to write the basic commands to make this happen and why data is separated in the first place.

This first article introduces the concept of joining tables.  The focus is going to be more on the type of joins, not necessarily their syntax.  The later articles focus on the various types of joins.  Through the narrative and examples you’ll become very comfortable with each one.

In my prior articles you learned about the need to normalize to make it easier to maintain the data.  Though this makes it easier to maintain and update the data, it makes it very inconvenient to view and report information.  Typically the information you need to see has to be cross referenced across several tables for you to see the full picture. [click to continue…]

5 comments

Introduction to SQL Server’s Mathematical Functions

Mathematical Functions to Old Way - Slide Rule!

Math Rocks!

SQLServer includes many mathematical formulas you can use to perform business and engineering calculations.  Many of these aren’t used in typical day-to-day operations; however, there are several commonly used functions we’ll cover.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

Introduction to SQL Server’s Mathematical Functions

There are many mathematical functions within SQLServer at your disposal.  I would generally categorize them as the following:

  • Scientific and Trig Functions
  • Rounding Functions
  • Signs
  • Random Numbers

All of the functions are listed on the Mathematical Functions (Transact-SQL) page.  I would recommend visiting that page to learn about each function. [click to continue…]

0 comments

Introduction to SQL Server’s Built-In Logical Functions

Logical Circuit

Logical functions provide a way to use logical conditions to display one of several values.  You can use logical functions to test a field’s value such as gender (M or F) and display another value(‘Male’ or ‘Female’) as a result.
In this article we describe how to use the CHOOSE and IIF functions.  CHOOSE is really a great way to pick one value from a list of indexed values, whereas IIF provides a compact means to provide the same type of conditional testing found with in the CASE statement.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

Introduction to SQL Server’s Built-In Logical Functions

The IIF and CHOOSE functions are new to SQL Server 2012.  They allow you to perform comparisons within the select statement to decide which of several values to return. [click to continue…]

0 comments

Introduction to SQL Server’s Built-In Functions

Built in SQL Functions

Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data.  These functions can be used anywhere expressions are allowed.  Common uses of functions include to change a name to all upper case.  In this article we’ll introduce you to basic concepts.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server. [click to continue…]

0 comments

Introduction to SQL Server’s Built-In Conversion Functions

colorpattern

SQL server uses data types to store a specific kind of value such as numbers, dates, or text in table columns and to use in functions, such as mathematical expressions.
One issue with data types is that they don’t usually mix well.

Though there are time when types are automatically (implicitly) converted from one type to another, in other cases, SQL needs more convincing.  In these cases, the CAST and CONVERT functions come into play.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server. [click to continue…]

0 comments

How can I find Duplicate Values in SQL Server?

Find Duplicate Data with an INNER JOIN

In this article find out how to find duplicate values in a table or view using SQL.  We’ll go step by step through the process.  We’ll start with a simple problem, slowly build up the SQL, until we achieve the end result.

By the end you’ll understand the pattern used to identify duplicate values and be able to use in in your database.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server. [click to continue…]

2 comments