Banners

Yahoo! Small Business - Web Hosting 468x60 $12.99 Go Daddy SSL Certificates
Microsoft Store

SQL 101

SQL 101 or Transact SQL Basic
The following assume that you have downloaded the Microsoft SQL Server Manager Studio Express. This is free software so use the custom Google search box on the right (under Search) and search for Microsoft SQL Server Manager Studio Express. You will be taken to www.microsoft.com/downloads....
What is SQL?
Structured Query Language is a database computer language designed for managing data in relational database management systems (RDBMS). Think of it as a Language that you write to get information stored somewhere. The database you get after you install Microsoft SQL Server Manages Studio Express is Northwind. It will be use though out this tutorial. Refer to SQL Server Tips for more info on this database.

How do you select data?
select * from dbo.[Customers]
It will give you all the data that is store in the [Customers] table. If you have 1000 rows in the table it will try to load all the data for you. You can enter top 10 if you only want it to return the first 10 rows if you do not know what kind of info information is stored in this table.

WHERE clause
It set the condition or limit the data you get
  • = the field needs to match eg. [Country] = 'USA'
  • in the field match more than one eg. [Country] in ('USA', 'UK')
  • like the field start with, end with or contain any part of a string. Eg. [Country] like 'A%' return Argentina, Austria, Argentina, Austria, and Argentina. Eg. [Country] like '%A' will give you all the countries that end with A. Eg. [Country] like '%A%' give you all the coutries that will have an A in the field no matter where it appears.

  • = Query: select * from dbo.[Customers]where [Country] = 'USA'
  • in Query: select * from dbo.[Customers]where [Country] in ('USA', 'UK')
  • like Query: select * from dbo.[Customers]where [Country] like 'A%'
    Let said you want to know how many Customers you have in your database.
    select count(*) as total_rec from dbo.[Customers]
    If you want to know how many customers you have by country.
    select [Country], count(*) as total_rec from dbo.[Customers] group by [Country]
    order by total_rec desc

    • Working with Dates
  • Date in US format: convert(varchar, OrderDate, 101)
  • Date in UK format: convert(varchar, OrderDate, 103)
  • Get Today Date: convert(varchar,getdate(),101)
  • Get Max or Min date max([OrderDate]
  • The folloiwng query will get you an example of what they will look like.
  • select CustomerID, OrderDate,
    convert(varchar, OrderDate, 101) as US_Date,
    convert(varchar, OrderDate, 103) as UK_Date,
    convert(varchar,getdate(),101) as Today_Date
    from dbo.Orders where [OrderDate]= (select max([OrderDate]) from dbo.Orders)
  • Other Date Example
  • select CustomerID, OrderDate,
    month(OrderDate) as theMonth, day(OrderDate) theDay, year(OrderDate) theYear,
    datename(mm, OrderDate) as MonthName,
    datediff(dd,OrderDate , GetDate()) as Day_from_today,
    dateadd(dd, 30, OrderDate) as Date_add_30,
    DATEPART(WW, OrderDate) AS 'WEEK',
    DATEPART(QQ, OrderDate) AS 'QUARTER'
    from dbo.Orders where [OrderDate]= (select max([OrderDate]) from dbo.Orders)
    • Working with Case
  • Case statement help you display or format the way your data look. Example the Status code can be 0 or 1, but your users do not know what they mean if you display it on a report. You can use case so that it display Open for 1 and Closed for 0.
  • More Example of Case Query:
  • select ProductName, UnitPrice, UnitsInStock,
    case when UnitsInStock < 1 then 'ReOrder Now' when UnitsInStock between 1 and 9 then 'Running Low' when UnitsInStock between 10 and 50 then 'OK' when UnitsInStock > 50 then 'Have too much' else 'N/A' end as ReOrder_Status,
    case when UnitsInStock < 1 then 'Not Available' else 'Available' end as InStock from dbo.Products

    • Working with String
  • SQL to get string starting at postion 1 and diplay 3 charectors
  • SELECT RegionDescription, SUBSTRING(RegionDescription,1,3) as NewDescription, Len(RegionDescription) as StringLength FROM dbo.Region
  • RTRIM OR LTRIM
    SELECT RTRIM(' Sample '), LTRIM(' Sample ')
  • Replace String
    SELECT RegionDescription, REPLACE(RegionDescription,'ern','') as NewDescription FROM dbo.Region

    • JOIN tables

  • Inner Join : Join tables must have a records in each table. Eg. You want to know all customers that have an order. The customer wills only shows when that customer has an order.
  • Left Join: Join tables but only the first (primary) table need to have a record. Eg. All customers data will show regardless if there is an order or not.
  • Let said you want to know how many order each customer has. You will see that SAVEA has 31 order after running the following query
  • select os.CustomerID, count(*) as total_rec
    from dbo.Employees emp left join dbo.Orders os on emp.EmployeeID=os.EmployeeID
    group by os.CustomerID
    order by total_rec desc.

    • Now that you know the basis SQL you can begin to create table, view, Procedure, and function using SQL. Let said you want to create another table that is like the Customers table. If you right click on the Customer Table > select Script Table as > CREATE To New Query Editor Window
  • CREATE To New
  • Just change the following and give it a new name
  • CREATE TABLE [dbo].[Customers] to CREATE TABLE [dbo].[Customers2]
  • CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED to CONSTRAINT [PK_Customers2] PRIMARY KEY CLUSTERED
  • Note you need to refresh the Tables object to see the new table. Right Click Tables and select refresh. See how fast the table is created. You can do the same for View, Procedure, and Function. The table and View has its Folder under the Database. Note that Procedure and Function are under Programmabilty.
  • Change your table after you created. Right click on the table name and choose Design. It will so a new screen. Just enter the name of the field under Column Name and select a data type and check the Allow Nulls box.

    • Delete the table. Be very careful about deleting table. Once it is deleted you will need restore the database to get it back. The data that is in the table is only update-to-date to the point the backup are done. Your DBA can do this for you.

    Insert Data into your new table

  • Insert into dbo.TableName (Column1, Column2,..) Values ('Value1','Values2',..)
  • Insert into dbo.Customers2 (CustomerID, CompanyName) Values ('ACB', 'Bottom-Dollar Markets')
  • See that it will display 1 row effected after you run the insert statement above.
  • To see the new data run.
  • select * from dbo.Customers2 where CustomerID='ACB'

    • Insert Data using a select query
  • Let say you want to insert all the customers that are in the USA in the original table (dbo.Customers) then you just need to write a query that select the customer where the country is 'USA'
  • 1. Insert into dbo.Customers2(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
  • 2. (select CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax from dbo.Customers where Country = 'USA')
  • Copy and page 1 and 2 above and run it
  • Note that you will see 13 row (s) affected. Run the following statement to see all the data for USA in the new table.
  • select * from dbo.Customers2 where Country = 'USA'


    • Deleting data in the table
  • Please be careful about this as well and always use a condition when deleting data.
  • delete dbo.Customers2 where Country = 'USA' will remove the 13 records you just added to dbo.Customers2.
  • If you run the Insert statement again it will add the data by.


    • Below are my Sponsor/Advertisements ads. Please do not click if you do not want to find out more about their services or products.

    Subscribe to: Posts (Atom)