What is SQL?

SQL stands for Structured Query Language. It is an ANSI standard language which means it is an industry recognised standard language which can be used across a plethora of database management systems. I must caveat the fact that although it is an ANSI standard language minor variations do happen in T-SQL ( SQL Server ), PL/SQL ( Oracle)…..

SQL is used as a programming language in relational database management systems.

Create a new column with an Expression

Sometimes there may be a requirement to create a new column based upon existing columns with a minor manipulation.

For example in the AdventureWorks database the table TransactionHistoryArchive shows the quantity and the actual cost of each unit but not the total value of the order.

That would be achieved by multiplying the Quantity and AcutalCost columns.

Current results from the table are below.

</p>
<p>SELECT [TransactionID]</p>
<p>,[ProductID]</p>
<p>,[ReferenceOrderID]</p>
<p>,[ReferenceOrderLineID]</p>
<p>,[TransactionDate]</p>
<p>,[TransactionType]</p>
<p>,[Quantity]</p>
<p>,[ActualCost]</p>
<p>,[ModifiedDate]</p>
<p>FROM [AdventureWorks].[Production].[TransactionHistoryArchive]<br />

1

However we need to multiply Quantity * ActualCost  and call the column TotalOrderValue.

</p>
<p>SELECT [TransactionID]</p>
<p>,[ProductID]</p>
<p>,[ReferenceOrderID]</p>
<p>,[ReferenceOrderLineID]</p>
<p>,[TransactionDate]</p>
<p>,[TransactionType]</p>
<p>,[Quantity]</p>
<p>,[ActualCost]</p>
<p>,[ModifiedDate]</p>
<p>,[Quantity] * [ActualCost] AS TotalOrderValue</p>
<p>FROM [AdventureWorks].[Production].[TransactionHistoryArchive]<br />

2

AS – Rename output columns

Sometimes when you run a query you may want to rename the columns in the results. This is quite a simple process within SQL.

First you will need to know the source column names.

For example

USE AdventureWorks

GO

SELECT * FROM [HumanResources].[Department]

1

Or you can open up the table which gives the column names.

2

You can change the name of the column by using the below SYNTAX

[ SourceColumn Name] AS [DesiredOutputName]

SELECT [DepartmentID] AS ID,

[Name] AS [Department Name],

[GroupName] AS [Function],

[ModifiedDate] AS LastChangeDate

FROM [HumanResources].[Department]

3

SELECT – Returning specific columns

There are many times where returning all the data held within all the columns in a table are unnecessary and inefficient.

For example.

SELECT * FROM HumanResources.Department

If you expand out the HumanResources.Department then you will get a list of the columns as well as some information on the columns.

1

To get only the DepartmentID and Name columns the following query would be needed.

SELECT DepartmentID,Name FROM HumanResources.Department

2

SELECT – Return results from all columns

There is a simple way to return information from all the columns in SQL.

SELECT * FROM INSERTTABLENAME

For example

SELECT * FROM HumanResources.Department

1

As you can see the query returned 16 rows of information with all the columns.

USE – Select a database in SQL

Before you can start querying a database you will need to select the database first.

This is done using the USE command

USE Enteryourdatabasenamehere

For example

USE AdventureWorks

This will change the queried database to AdventureWorks

SQL – Hello World

In a time-honoured tradition in every programming language the first thing they teach you is how to get the system to put ‘Hello World’ on to your screen.

This is accomplished by using the below command.

PRINT ‘HELLO WORLD’

1

Play about with the PRINT command and see what else you can get the system to reproduce.

SELECT – The basis of the SQL language

The SELECT query is the basis of SQL and is the most commonly used syntax within SQL.

SELECT basically means retrieve.

For example the query SELECT 1 returns 1.

1

SELECT ‘This is an example of the SELECT statement’

2

As you can see the SELECT query is simply retrieving the number or word and returning that as a result.

If you want information from a database within a table then you must select a database and table.

3

Welcome to my FREE SQL blog – Learn SQL Free

Hi everyone.

My name is Parvinder Nijjar and I work as a  Microsoft SQL Server Database Administrator (DBA).  I am certified at MCITP level for SQL Server and have almost seven years of experience with Database technologies.

I have a huge love of Windows Server, SQL Server, C# and PowerShell. I hope that through this blog that I’ve created I can help spread SQL knowledge to everyone so that they can improve their SQL knowledge and become better data professionals.

Throughout this blog I will share my knowledge and experiences of Indexing, Policy Based Management, Replication, Mirroring, Clustering, Log Shipping, Backup and Restore, SQL internals, Security, SSAS, SSRS, SSIS best practices and programming.