DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to define an SQL database?

The following SQL Server T-SQL database script in Management Studtio Query Editor demonstrates the definition of an SQL Database. The SQL database statements are samples only. For actual execution changes, such as SQL database object names, file paths, are necessary.

 

/*************************************************/

-- SQL Database Administration Statements

/*************************************************/

 

-- T-SQL environment control statement

USE [master]

GO

 

-- Create sql database statement

 

CREATE DATABASE [Northwind] ON  PRIMARY

( NAME = N'Northwind',

FILENAME = N'F:\UTIL\SQL2008\northwnd.mdf' , SIZE = 42048KB ,

MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON

( NAME = N'Northwind_log', FILENAME = N'F:\UTIL\SQL2008\northwnd.ldf' ,

SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

 

ALTER DATABASE [Northwind] SET COMPATIBILITY_LEVEL = 80

GO

 

-- T-SQL condtional branching statement

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

-- T-SQL execute system stored procedure statement

EXEC [Northwind].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

 

-- Alter sql database statements

ALTER DATABASE [Northwind] SET ANSI_NULL_DEFAULT OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_NULLS OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_PADDING OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_WARNINGS OFF

GO

 

ALTER DATABASE [Northwind] SET ARITHABORT OFF

GO

 

ALTER DATABASE [Northwind] SET AUTO_CLOSE OFF

GO

 

ALTER DATABASE [Northwind] SET AUTO_CREATE_STATISTICS ON

GO

 

ALTER DATABASE [Northwind] SET AUTO_SHRINK OFF

GO

 

ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS ON

GO

 

ALTER DATABASE [Northwind] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

 

ALTER DATABASE [Northwind] SET CURSOR_DEFAULT  GLOBAL

GO

 

ALTER DATABASE [Northwind] SET CONCAT_NULL_YIELDS_NULL OFF

GO

 

ALTER DATABASE [Northwind] SET NUMERIC_ROUNDABORT OFF

GO

 

ALTER DATABASE [Northwind] SET QUOTED_IDENTIFIER OFF

GO

 

ALTER DATABASE [Northwind] SET RECURSIVE_TRIGGERS OFF

GO

 

ALTER DATABASE [Northwind] SET  DISABLE_BROKER

GO

 

ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

 

ALTER DATABASE [Northwind] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

 

ALTER DATABASE [Northwind] SET TRUSTWORTHY OFF

GO

 

ALTER DATABASE [Northwind] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

 

ALTER DATABASE [Northwind] SET PARAMETERIZATION SIMPLE

GO

 

ALTER DATABASE [Northwind] SET HONOR_BROKER_PRIORITY OFF

GO

 

ALTER DATABASE [Northwind] SET  READ_WRITE

GO

 

ALTER DATABASE [Northwind] SET RECOVERY SIMPLE

GO

 

ALTER DATABASE [Northwind] SET  MULTI_USER

GO

 

ALTER DATABASE [Northwind] SET PAGE_VERIFY TORN_PAGE_DETECTION 

GO

 

ALTER DATABASE [Northwind] SET DB_CHAINING OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_NULL_DEFAULT OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_NULLS OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_PADDING OFF

GO

 

ALTER DATABASE [Northwind] SET ANSI_WARNINGS OFF

GO

 

ALTER DATABASE [Northwind] SET ARITHABORT OFF

GO

 

ALTER DATABASE [Northwind] SET AUTO_CLOSE OFF

GO

 

ALTER DATABASE [Northwind] SET AUTO_CREATE_STATISTICS ON

GO

 

ALTER DATABASE [Northwind] SET AUTO_SHRINK OFF

GO

 

ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS ON

GO

 

ALTER DATABASE [Northwind] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

 

ALTER DATABASE [Northwind] SET CURSOR_DEFAULT  GLOBAL

GO

 

ALTER DATABASE [Northwind] SET CONCAT_NULL_YIELDS_NULL OFF

GO

 

ALTER DATABASE [Northwind] SET NUMERIC_ROUNDABORT OFF

GO

 

ALTER DATABASE [Northwind] SET QUOTED_IDENTIFIER OFF

GO

 

ALTER DATABASE [Northwind] SET RECURSIVE_TRIGGERS OFF

GO

 

ALTER DATABASE [Northwind] SET  DISABLE_BROKER

GO

 

ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

 

ALTER DATABASE [Northwind] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

 

ALTER DATABASE [Northwind] SET TRUSTWORTHY OFF

GO

 

ALTER DATABASE [Northwind] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

 

ALTER DATABASE [Northwind] SET PARAMETERIZATION SIMPLE

GO

 

ALTER DATABASE [Northwind] SET  READ_WRITE

GO

 

ALTER DATABASE [Northwind] SET RECOVERY SIMPLE

GO

 

ALTER DATABASE [Northwind] SET  MULTI_USER

GO

 

ALTER DATABASE [Northwind] SET PAGE_VERIFY TORN_PAGE_DETECTION 

GO

 

ALTER DATABASE [Northwind] SET DB_CHAINING OFF

GO

 

 

 

-- T-SQL statement

USE [Northwind]

GO

 

-- T-SQL command

DBCC CheckDB

GO

 

/*************************************************/

-- Data Definition Language (DDL) Statements

/*************************************************/

 

-- Create table statement

CREATE TABLE [dbo].[Customers](

      [CustomerID] [nchar](5) NOT NULL,

      [CompanyName] [nvarchar](40) NOT NULL,

      [ContactName] [nvarchar](30) NULL,

      [ContactTitle] [nvarchar](30) NULL,

      [Address] [nvarchar](60) NULL,

      [City] [nvarchar](15) NULL,

      [Region] [nvarchar](15) NULL,

      [PostalCode] [nvarchar](10) NULL,

      [Country] [nvarchar](15) NULL,

      [Phone] [nvarchar](24) NULL,

      [Fax] [nvarchar](24) NULL,

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

      [CustomerID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,

 ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]

) ON [PRIMARY]

GO

 

-- Create view statement

create view [dbo].[Invoices] AS

SELECT Orders.ShipName, Orders.ShipAddress,

    Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,

      Orders.ShipCountry, Orders.CustomerID,

      Customers.CompanyName AS CustomerName,

      Customers.Address, Customers.City,

      Customers.Region, Customers.PostalCode, Customers.Country,

      (FirstName + ' ' + LastName) AS Salesperson,

      Orders.OrderID, Orders.OrderDate,

      Orders.RequiredDate, Orders.ShippedDate,

      Shippers.CompanyName As ShipperName,

      "Order Details".ProductID, Products.ProductName,

      "Order Details".UnitPrice, "Order Details".Quantity,

      "Order Details".Discount,

      (CONVERT(money,("Order Details".UnitPrice*Quantity*

       (1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight

FROM Shippers INNER JOIN

            (Products INNER JOIN

                  (

                        (Employees INNER JOIN

                              (Customers INNER JOIN Orders

                                ON Customers.CustomerID = Orders.CustomerID)

                        ON Employees.EmployeeID = Orders.EmployeeID)

                  INNER JOIN "Order Details"

                   ON Orders.OrderID = "Order Details".OrderID)

            ON Products.ProductID = "Order Details".ProductID)

      ON Shippers.ShipperID = Orders.ShipVia

GO

 

-- Create stored procedure

create procedure [dbo].[Sales by Year]

      @Beginning_Date DateTime, @Ending_Date DateTime AS

SELECT Orders.ShippedDate, Orders.OrderID,

"Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year

FROM Orders

INNER JOIN "Order Subtotals"

   ON Orders.OrderID = "Order Subtotals".OrderID

WHERE Orders.ShippedDate

     Between @Beginning_Date And @Ending_Date

GO

 

/*************************************************/

-- Database Manipulation Language (DML)

/*************************************************/

 

-- SQL Query

 

SELECT [ProductID]

      ,[ProductName]

      ,[SupplierID]

      ,[CategoryID]

      ,[QuantityPerUnit]

      ,[UnitPrice]

      ,[UnitsInStock]

      ,[UnitsOnOrder]

      ,[ReorderLevel]

      ,[Discontinued]

  FROM [Northwind].[dbo].[Products]

GO

 

-- SQL insert statement

INSERT INTO [Northwind].[dbo].[Products]

           ([ProductName]

           ,[SupplierID]

           ,[CategoryID]

           ,[QuantityPerUnit]

           ,[UnitPrice]

           ,[UnitsInStock]

           ,[UnitsOnOrder]

           ,[ReorderLevel]

           ,[Discontinued])

     VALUES

           ('Delta Cellphone',

           ,44

           ,4

           ,1

           ,200.0

           ,43

           ,10

           ,20

           ,0)

GO

 

 

-- SQL update statement

UPDATE [Northwind].[dbo].[Products]

   SET [ProductName] = 'Delta PowerPhone'

    

 WHERE ProducID = 3420000

GO

 

 

 

-- SQL Delete Statement

DELETE FROM [Northwind].[dbo].[Products]

      WHERE ProductID = 320000

GO

 

USE [master]

GO

 

-- SQL Database administration statement

 

IF  EXISTS (SELECT name FROM sys.databases

 WHERE name = N'Northwind')

DROP DATABASE [NameChangedtoFailDrop of Northwind]

GO

 

-- SQL Database complex SELECT query statement

 

USE AdventureWorks2008;

GO

 

SELECT DISTINCT

Subcategory = ps.Name,

Product = p1.Name,

ListPrice='$'+convert(varchar,p1.ListPrice)

FROM Production.Product p1

    INNER JOIN Production.Product p2

    ON p1.ProductSubcategoryID = p2.ProductSubcategoryID

    AND p1.ListPrice <> p2.ListPrice

    INNER JOIN Production.ProductSubcategory ps

    ON ps.ProductSubcategoryID = p1.ProductSubcategoryID

WHERE p1.ListPrice < $25 AND p2.ListPrice < $25

ORDER BY Subcategory, ListPrice DESC;

GO

 

USE [AdventureWorks2008]

GO

 

    SET NOCOUNT ON;

    DECLARE @JobTitle varchar(50)

    SET @JobTitle = 'Sales Representative';

    -- select * from HumanResources.Employee

    WITH cteEmployee([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns

    AS (

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee

        FROM [HumanResources].[Employee] e

                  INNER JOIN [Person].[Person] as p

                  ON p.[BusinessEntityID] = e.[BusinessEntityID]

        WHERE e.JobTitle = @JobTitle

        UNION ALL

        -- Recursive term

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor

        FROM [HumanResources].[Employee] e

            INNER JOIN cteEmployee ce

            ON e.[OrganizationNode] = ce.[OrganizationNode].GetAncestor(1)

            INNER JOIN [Person].[Person] p

            ON p.[BusinessEntityID] = e.[BusinessEntityID]

    )

    -- Join cteEmployee and Employee table for manager name

    SELECT distinct

        Employee= ce.[FirstName]+' '+ce.[LastName],

        ce.[OrganizationNode].ToString() AS [OrganizationNode],

        p.[FirstName] + ' ' +

        p.[LastName] AS 'ManagerName',

        ce.[BusinessEntityID]

    FROM cteEmployee ce

        INNER JOIN [HumanResources].[Employee] e

        ON ce.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]

        INNER JOIN [Person].[Person] p

        ON p.[BusinessEntityID] = e.[BusinessEntityID]

    ORDER BY  ManagerName, Employee, ce.[OrganizationNode].ToString()

   

 

GO

 

    -- The following T-SQL script will find all managers for Sales Representatives

USE [AdventureWorks2008]

GO

 

    SET NOCOUNT ON;

    DECLARE @JobTitle varchar(50)

    SET @JobTitle = 'Sales Representative';

    -- select * from HumanResources.Employee

    WITH cteEmployee([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns

    AS (

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee

        FROM [HumanResources].[Employee] e

                  INNER JOIN [Person].[Person] as p

                  ON p.[BusinessEntityID] = e.[BusinessEntityID]

        WHERE e.JobTitle = @JobTitle

        UNION ALL

        -- Recursive term

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor

        FROM [HumanResources].[Employee] e

            INNER JOIN cteEmployee ce

            ON e.[OrganizationNode] = ce.[OrganizationNode].GetAncestor(1)

            INNER JOIN [Person].[Person] p

            ON p.[BusinessEntityID] = e.[BusinessEntityID]

    )

    -- Join cteEmployee and Employee table for manager name

    SELECT distinct

        Employee= ce.[FirstName]+' '+ce.[LastName],

        ce.[OrganizationNode].ToString() AS [OrganizationNode],

        p.[FirstName] + ' ' +

        p.[LastName] AS 'ManagerName',

        ce.[BusinessEntityID]

    FROM cteEmployee ce

        INNER JOIN [HumanResources].[Employee] e

        ON ce.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]

        INNER JOIN [Person].[Person] p

        ON p.[BusinessEntityID] = e.[BusinessEntityID]

    ORDER BY  ManagerName, Employee, ce.[OrganizationNode].ToString()

   

 

GO

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE