Home E-Commerce Program Web Applications Database Design
 Programming E-Business Networking Graphics Site Map Dunwoody
SQL Server
Go to SQL Statements View SQL Databases Page    
SQL Definition
SQL is the abbreviation for structured query language, which is a standardized query language for requesting information from a database.  Historically, SQL has been the favorite query language for database management systems running on minicomputers and mainframes.  Increasingly, however, SQL is being supported by PC database systems because it supports distributed databases (databases that are spread out over several computer systems).  This enables several users on a local-area network to access the same database simultaneously.

SQL Server has been designed to support high-volume, mission-critical databases in a number of different application areas, including online transaction processing (OLTP), data warehousing, and e-commerce. Enterprise Manager, the primary tool used to administer SQL Server, can do the following:
  • Define groups of SQL Server instances and register individual servers within a group
  • Configure all SQL Server options for each registered server
  • Create and administer all SQL Server databases, objects, logins, users, and permissions
  • Define and execute all SQL Server administrative tasks
  • View the contents of tables and views using the Query Designer
  • Design and test SQL statements, batches, and scripts interactively by invoking the Query Analyzer
Tools
Enterprise Manager
SQL Security - Login
SQL Query Analyzer
Code examples and results
SQL Statements
View SQL Databases
Table of Contents
Creating Databases Retrieving Data Working with Data Transact-SQL
Creating Tables The SELECT Statement
 Adding Rows Transact-SQL Operators
Creating Table Objects The WHERE Clause Updating Rows Transact-SQL Functions
    The FROM Clause   Programming Objects
    The GROUP BY Clause   Controlling Execution
        Transact-SQL Cursors
        Stored Procedures
        Triggers
        User-Defined Functions
Top
Creating & Managing Tables
-- Create Table
USE [Premier Products]
GO
CREATE TABLE Suppliers
(
     SupplierID smallint
          IDENTITY (1,1)
          PRIMARY KEY CLUSTERED,
     CompanyName varchar (50),
     ContactName varchar (50),
     Address varchar (50),
     City varchar (50),
     State udtState, ..........................
     ZipCode varchar (10),
     Phone varchar (50)
)



See Table Objects

Back      View Database
-- Alter Table
USE [Premier Products]
GO
ALTER TABLE Part
ADD SupplierID smallint
     REFERENCES Suppliers (SupplierID)


Back       View Database
-- Alter Column
USE [Premier Products]
GO
ALTER TABLE Suppliers
ALTER COLUMN Phone varchar (13)


Back       View Database
  Back Creating Table Objects - Defaults, Rules, User-Defined Data Types       Back to Create Table   


Back      View Database
The SELECT Statement - Creating Calculated Columns
-- Computed Field
USE [Premier Products]
GO
SELECT  CustomerNumber, LastName, FirstName, (CreditLimit - Balance) AS [Available Credit] FROM    Customer
WHERE   (CreditLimit >= 1500)


Back      View Database
The WHERE Clause - Combining Selection Criteria, Using Special Operators
-- Compound Condition (Combining Selection Criteria)
USE [Premier Products]
GO
SELECT  Description
FROM    Part
WHERE   (WareHouse = 3) AND (OnHand > 100)


Back      View Database
-- Nested Query:  IN (Special Operator)
USE [Premier Products]
GO
SELECT * FROM Customer
WHERE CreditLimit IN
     (SELECT MAX(CreditLimit)
      FROM Customer
      WHERE SalesRepNumber = '06')


Back      View Database
The FROM Clause - Joining Tables Back   
-- Join Query (draws rows by combining columns from multiple tables)
USE [Premier Products]
GO
SELECT   SalesRep.SalesRepNumber AS [Rep Num], SalesRep.FirstName AS [Rep Fname],
         SalesRep.LastName AS [Rep Lname], Customer.CustomerNumber AS [Cust Num],
         Customer.FirstName AS [Cust Fname], Customer.LastName AS [Cust Lname]
FROM     SalesRep INNER JOIN
         Customer ON Customer.SalesRepNumber = SalesRep.SalesRepNumber
ORDER BY SalesRep.SalesRepNumber


Back      View Database
-- Union with Identifying Tables Query (combines rows from 2 tables into a single column)
USE [Premier Products]
GO
SELECT  'SalesRep Table' AS [Table Name], SalesRep.FirstName, SalesRep.LastName
FROM     SalesRep
UNION
SELECT  'Customer Table' AS [Table Name], Customer.FirstName, Customer.LastName
FROM     Customer


Back      View Database
The GROUP BY Clause - Summarizing Data
-- Grouping
USE [Premier Products]
GO
SELECT   OrderNumber, SUM(NumberOrdered * QuotedPrice)
         AS [Total Price]
FROM     OrderLine
GROUP BY OrderNumber
ORDER BY OrderNumber

Back      View Database
-- Grouping:  HAVING Clause
USE [Premier Products]
GO
SELECT   OrderNumber, SUM(NumberOrdered * QuotedPrice)
         AS [Total Price]
FROM     OrderLine
GROUP BY OrderNumber
HAVING   (SUM(NumberOrdered * QuotedPrice) > 200)
ORDER BY OrderNumber

Back      View Database
Adding Rows Back   
-- Insert
USE [Premier Products]
GO
INSERT INTO Suppliers
     (CompanyName, ContactName, Address, City, State,
      ZipCode, Phone)
VALUES
     ('Doyle Lock Supply', 'Ralph', 'West River Road',
      'Minneapolis', 'MN', '55411', '612-521-6226')
INSERT INTO Suppliers
VALUES
     ('E.L. Reinhardt Co.', 'Henry', 'Fanum Rd',
      'Vadnais Hts', 'MN', '55110', '651-481-0566')


Back      View Database
Updating Rows
-- Update
USE [Premier Products]
GO
UPDATE Suppliers
SET State = 'MN'
WHERE (LEFT(Phone,5) = '(612)')

Back      View Database
Transact-SQL Operators
-- String Concatenation Operator (+)
USE Northwind
GO
SELECT   ContactName + ', ' + Country
         AS [Contact Name, Country]
FROM     Customers
ORDER BY Country

Back      View Database
-- Logical Operator:  OR (select for one or the other condition)
USE Northwind
GO
SELECT CompanyName, ContactName
FROM   Suppliers
WHERE  LEFT(CompanyName,1) = 'N' OR LEFT(ContactName,1) = 'A'


Back      View Database
-- Logical Operator:  AND (select for both conditions)
USE Northwind
GO
SELECT CompanyName, ContactName
FROM   Suppliers
WHERE  LEFT(CompanyName,1) = 'N' AND LEFT(ContactName,1) = 'S'


Back      View Database
Transact-SQL Functions
-- Date and Time Functions
SELECT  DATEADD(Month, 6, GETDATE()) AS [DateAdd (6 Mos)],
        DATEDIFF(Week, '25 November 1970', GETDATE()) AS [DateDiff (Week, 11/25/70)],
        DATENAME(Month, GETDATE()) AS [DateName],
        DATEPART(Quarter, GETDATE()) AS [DatePart (qtr/integer)],
        DAY(GETDATE()) AS [Day],
        MONTH(GETDATE()) AS [Month],
        YEAR(GETDATE()) AS [Year]


Back
-- Math Functions
SELECT  ABS (-5.6) AS [ABS -5.6],
        CEILING (7.32) AS [Ceiling 7.32],
        FLOOR (7.32) AS [Floor 7.32],
        EXP (7.32) AS [Exponent of float 7.32],
        POWER (2,5) AS [2 to 5th],
        SQUARE (12) AS [12 sq],
        SQRT (16) AS [Sq Rt 16]


Back
-- Aggregate Functions:  COUNT (returns number of values in collection, including NULL)
USE Northwind
GO
SELECT 'Employees Table' AS [Table Name],
        COUNT(EmployeeID) AS [#(Customers,Employees,Countries)]
FROM    Employees
UNION
SELECT 'Customers Table', COUNT(CustomerID)
FROM    Customers
UNION
SELECT 'Suppliers Table', COUNT(Country)
FROM    Suppliers


Back      View Database
-- Aggregate Functions:  AVG (returns average of values in collection, ignoring NULL)
USE Northwind
GO
SELECT   ShipCountry, AVG(Freight) AS [Avg Freight]
FROM     Orders
GROUP BY ShipCountry
ORDER BY AVG(Freight) DESC


Back      View Database
-- String Functions
USE Northwind
GO
SELECT LastName,
       UPPER(LastName) AS [Ucase],
       LOWER(LastName) AS [Lcase],
       LEFT(LastName,1) AS [1st],
       ASCII(LastName) AS [ASCII],
       UNICODE(LastName) AS [Unicode],
       LEN(LastName) AS [Length],
       SOUNDEX(LastName) AS [Soundex],
       UPPER(SUBSTRING(LastName,3,3)) AS [Substring 3,3]
FROM   Employees


Back      View Database
-- System Functions
SELECT APP_NAME() AS [Application],

-- Data Length = number of bytes in expression
DATALENGTH('Northwind') AS [Data Length],
SYSTEM_USER AS [System User],
USER_NAME() AS [User Name],

-- 1=TRUE, 0=FALSE
ISDATE('30 November 2002') AS [11/30=Date],
ISDATE('31 November 2002') AS [11/31=Date]


Back
Programming Objects
-- Variables:  Local
USE [Premier Products]
GO

-- Create a local variable
DECLARE @lName char(50)

-- Set value
SET @lName = 'Adams'

-- Show value of variable
SELECT LastName
FROM   Customer
WHERE  LastName = @lName
Back      View Database
-- Variables:  Local Table
USE [Premier Products]
GO

-- Create a local variable of table type
DECLARE @localSalesRepTable TABLE(LastName char(50), FirstName char(50), Commission int)

-- Use INSERT INTO to create rows
INSERT INTO @localSalesRepTable
       SELECT LastName, FirstName, Commission
       FROM SalesRep

-- Display results
SELECT *
FROM   @localSalesRepTable


Back      View Database
-- Global Variables
SELECT @@SERVERNAME AS 'Name',
       @@VERSION AS 'Version',
       @@LANGUAGE AS 'Language'


Back
Controlling Execution Back   
-- Case Function (Conditional Processing)
USE [Premier Products]
GO
SELECT Balance, CreditLimit,
CASE
       WHEN Balance < CreditLimit THEN 'OK'
       WHEN Balance > CreditLimit THEN 'Over Credit Limit'
END AS [Credit Status]
FROM   Customer


Back      View Database
-- While Loop
DECLARE @counter int
SET     @counter = 0
WHILE   @counter <= 11
BEGIN
        PRINT @counter
        SET @counter = @counter + 2
END


Back
Transact-SQL Cursors
A cursor is an object that points to a specific row within a set.   Depending on the nature of the cursor you create, you can move the cursor around in the set and update or delete data.

Each Transact-SQL cursor type has three more-or-less independent cursor characteristics:  its ability to reflect changes to the underlying data, its ability to scroll through the row set, and its ability to update the row set.

Transact-SQL supports four different types of cursors:   static, keyset, dynamic, and fast forward.
  • Static - takes a snapshot of the data specified by its SELECT statement and stores it in the tempdb database.  The rows in the cursor and the data values of the rows never change.  Static cursors are read-only, and they can be declared forward-only or scrollable.
  • Keyset - copies into tempdb only the columns required to uniquely identify each row.  Changes to the data in the rows that meet the SELECT criteria can be seen when the cursor is opened.  To declare a keyset cursor, each table involved in the defining SELECT statement must have a unique index that defines the keyset to be copied.  Keyset cursors can be updatable or read-only, and they can be scrollable or forward-only.
  • Dynamic - reflects changes to both membership and underlying data values, whether those changes were made through the cursor or by another user.
  • Fast Forward (Firehose) - a very efficient, read-only, forward-only, non-scrollable cursor.
Back
-- Cursor (an object that points to a specific row within a set)
USE [Premier Products]
GO
-- Create the cursor
DECLARE myCursor Cursor
        LOCAL
        FOR SELECT LastName, FirstName FROM Customer
-- Create the variables
DECLARE @lName char(20), @fName char(20)
DECLARE @i int
SET     @i = 1
-- Create the cursor set
OPEN   myCursor

PRINT 'Last Name:     First Name:'
WHILE  @i <= 10
BEGIN
-- Retrieve a row into the variables
FETCH myCursor
      INTO @lName, @fName
-- Display the results
PRINT RTRIM(@lName) + ',     ' + RTRIM(@fName)

SET   @i = @i + 1
END
-- Release the cursor set
CLOSE myCursor
-- Release the cursor
DEALLOCATE myCursor
 Back

Back      View Database
Stored Procedures Back   
-- Create Stored Procedure
USE [Premier Products]
GO
-- Check if procedure exists and drop it if it does
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'InputSP' AND type = 'P')
   DROP PROCEDURE InputSP
GO
CREATE PROCEDURE InputSP
@lName char(50)
AS
SELECT * FROM Customer
WHERE LastName = @lName
GO
-- Display results
EXEC InputSP 'Daniels'
EXEC InputSP 'Martin'
EXEC InputSP 'Williams'


Back      View Database
Triggers Back   

-- Create AFTER Trigger
USE FinalProject
GO
IF EXISTS (SELECT name FROM
   sysobjects WHERE name =
  'inventoryUpdate' AND type = 'TR')
   DROP TRIGGER inventoryUpdate
GO
CREATE TRIGGER inventoryUpdate
ON Sales
AFTER UPDATE
AS
INSERT INTO myTable (myText)
VALUES ('Inventory Update')
GO
UPDATE Sales
SET Misc = 'Expensive'
WHERE SalesPrice > 50

Back      View Database
User-Defined Functions Back   
-- Create Scalar Function (returns single-valued result)
USE [Premier Products]
GO
IF EXISTS (SELECT name FROM sysobjects
           WHERE name = 'scalarFunction' AND type = 'FN')
   DROP FUNCTION scalarFunction
GO
CREATE FUNCTION scalarFunction (@orderNum int)
RETURNS money
AS
BEGIN
   DECLARE @total money
   SELECT @total = NumberOrdered * QuotedPrice
       FROM OrderLine
       WHERE OrderNumber = @orderNum
       RETURN (@total)
END
GO
SELECT OrderNumber, NumberOrdered, QuotedPrice,
       dbo.scalarFunction(OrderNumber) AS [Total Price]
FROM   OrderLine


Back      View Database
-- Create Table-Valued Function (returns a table)
USE FinalProject
GO
IF EXISTS (SELECT name FROM sysobjects
           WHERE name = 'totalFunction' AND type = 'FN')
   DROP FUNCTION totalFunction
GO
CREATE FUNCTION totalFunction (@saleNum int)
RETURNS money
AS
BEGIN
   DECLARE @total money
   SELECT @total = Qty * SalesPrice
       FROM Sales
       WHERE SaleNum = @saleNum
       RETURN (@total)
END
GO
SELECT SaleNum, Qty, SalesPrice, Misc,
       dbo.totalFunction(SaleNum) AS [Total Price]
FROM   Sales
Back


Back      View Database