SQL Transact - Select Statements

SQL Transact – SELECT Statement

SELECT is probably the most versatile and widely used statement in SQL Transact. Another reason to begin with Select is that you are not altering the data so you do not risk destroying data in the tables while you are learning.

Lesson 1 – The basic SELECT statement

— SQL statement to SELECT all the fields in the customers table.

USE Northwind
SELECT *
FROM Customers
GO

Learning points

Note 1: USE Northwind. Means connect to the database called Northwind, as apposed to a database called library.

Note 2: Select * Means choose all the columns (also called fields) in the table.

Note 3: From Customers. Tells the server to go to the Customers table rather than the Products table in the Northwind database.

Lesson 2 – SELECT columns (Fields)

Suppose you only want to know the countries your customers live in.

— SQL statement to SELECT just the countries in the customers table.

USE Northwind
SELECT country
FROM Customers
GO

Learning Points

Note 1: Now you only get one column because we specified country not *.

Note 2: It should return about 90 rows of data

Lesson 3 – SELECT three columns.

Now you would like to see the names of the people in the countries.

— SQL statement to SELECT just the countries in the customers table.

USE Northwind
SELECT lastname, firstname, country
FROM Customers
GO

Learning Point

Note 1: Pay particular attention to commas in your transact statements, here each field is separated by a comma. Naturally, there is no comma after the last field.

Lesson 4 – SELECT with DISTINCT

— SQL statement to SELECT once instance of each country.

USE Northwind
SELECT DISTINCT country
FROM Customers
GO

Learning points

Note 1: Compared with lesson 2, we only have about 21 rows. Each row is unique, thanks to the DISTINCT qualifier, there are no duplicate countries.

Guy’s Out Takes

If you like a challenge, troubleshoot these statements and see if you can correct their errors.

SQL Transact Out Take 1

— SQL statement to SELECT all the fields in the customers table.

USE Northwinded
SELECT *
FROM Customers
GO

Mistake on line 3 Northwinded should be Northwind

SQL Transact Out Take 2

— SQL statement to SELECT once instance of each country.

USE Northwind
SELECT country DISTINCT
FROM Customers
GO

Sequencing mistake SELECT country DISTINCT, it should be
SELECT DISTINCT country

◦

SQL 2000 Transact – SELECT Statement