sybase sql anywhere12

Sybase SQL AnyWhere 12

BySunny Okoro

1

ContentsDatabase Platform...................................................................................................................................................................... 2

Applications.................................................................................................................................................................................. 2

Database Diagram................................................................................................................................................................... 5

Functions........................................................................................................................................................................................ 7

User Defined Function........................................................................................................................................................... 10

Stored Procedures................................................................................................................................................................... 11

DDL................................................................................................................................................................................................. 14

Triggers........................................................................................................................................................................................ 21

XML................................................................................................................................................................................................ 21

Cursors.......................................................................................................................................................................................... 23

DBA................................................................................................................................................................................................. 23

2

Database Platform

Sybase SQLAnyWhere 12

Applications

Sybase Central

Sybase PowerDesigner

4

Microsoft Excel and Power Pivot

Microsoft Visual Studio 2012

5

Sybase InfoMaker 11

6

Database Diagram

Captured from Sybase PowerDesigner 16

7

FK_Store_SalesPerson_SalesPersonID

FK_Store_BusinessEntity_BusinessEntityID

FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID

FK_SpecialOfferProduct_Product_ProductID

FK_ShoppingCartItem_Product_ProductID

FK_SalesTerritoryHistory_SalesTerritory_TerritoryID

FK_SalesTerritoryHistory_SalesPerson_BusinessEntityID

FK_SalesTerritory_CountryRegion_CountryRegionCode

FK_SalesTaxRate_StateProvince_StateProvinceID

FK_SalesPersonQuotaHistory_SalesPerson_BusinessEntityID

FK_SalesPerson_SalesTerritory_TerritoryID

FK_SalesPerson_Employee_BusinessEntityID

FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID

FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID

FK_SalesOrderHeader_SalesTerritory_TerritoryID

FK_SalesOrderHeader_ShipMethod_ShipMethodID

FK_SalesOrderHeader_SalesPerson_SalesPersonID

FK_SalesOrderHeader_Customer_CustomerID

FK_SalesOrderHeader_CurrencyRate_CurrencyRateID

FK_SalesOrderHeader_CreditCard_CreditCardID

FK_SalesOrderHeader_Address_ShipToAddressID

FK_SalesOrderHeader_Address_BillToAddressID

FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID

FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID

FK_PersonCreditCard_CreditCard_CreditCardID

FK_PersonCreditCard_Person_BusinessEntityID

FK_Customer_SalesTerritory_TerritoryID

FK_Customer_Store_StoreID

FK_Customer_Person_PersonID

FK_CurrencyRate_Currency_ToCurrencyCode

FK_CurrencyRate_Currency_FromCurrencyCode

FK_CountryRegionCurrency_Currency_CurrencyCode

FK_CountryRegionCurrency_CountryRegion_CountryRegionCode

FK_Vendor_BusinessEntity_BusinessEntityID

FK_PurchaseOrderHeader_ShipMethod_ShipMethodID

FK_PurchaseOrderHeader_Vendor_VendorID

FK_PurchaseOrderHeader_Employee_EmployeeID

FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID

FK_PurchaseOrderDetail_Product_ProductID

FK_ProductVendor_Vendor_BusinessEntityID

FK_ProductVendor_UnitMeasure_UnitMeasureCode

FK_ProductVendor_Product_ProductID

FK_WorkOrderRouting_WorkOrder_WorkOrderID

FK_WorkOrderRouting_Location_LocationID

FK_WorkOrder_ScrapReason_ScrapReasonID

FK_WorkOrder_Product_ProductID

FK_TransactionHistory_Product_ProductID

FK_ProductSubcategory_ProductCategory_ProductCategoryID

FK_ProductReview_Product_ProductID

FK_ProductProductPhoto_ProductPhoto_ProductPhotoID

FK_ProductProductPhoto_Product_ProductID

FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID

FK_ProductModelProductDescriptionCulture_Culture_CultureID

FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID

FK_ProductModelIllustration_Illustration_IllustrationID

FK_ProductModelIllustration_ProductModel_ProductModelID

FK_ProductListPriceHistory_Product_ProductID

FK_ProductInventory_Product_ProductID

FK_ProductInventory_Location_LocationID

FK_ProductDocument_Document_DocumentNodeID

FK_ProductDocument_Product_ProductID

FK_ProductCostHistory_Product_ProductID

FK_Product_ProductSubcategory_ProductSubcategoryID

FK_Product_ProductModel_ProductModelID

FK_Product_UnitMeasure_WeightUnitMeasureCode

FK_Product_UnitMeasure_SizeUnitMeasureCode

FK_Document_Employee_Owner

FK_BillOfMaterials_UnitMeasure_UnitMeasureCode

FK_BillOfMaterials_Product_ComponentID

FK_BillOfMaterials_Product_ProductAssemblyID

FK_StateProvince_SalesTerritory_TerritoryID

FK_StateProvince_CountryRegion_CountryRegionCode

FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID

FK_PersonPhone_Person_BusinessEntityID

FK_Person_BusinessEntity_BusinessEntityID

FK_Password_Person_BusinessEntityID

FK_EmailAddress_Person_BusinessEntityID

FK_BusinessEntityContact_BusinessEntity_BusinessEntityID

FK_BusinessEntityContact_ContactType_ContactTypeID

FK_BusinessEntityContact_Person_PersonID FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID

FK_BusinessEntityAddress_AddressType_AddressTypeID

FK_BusinessEntityAddress_Address_AddressID

FK_Address_StateProvince_StateProvinceID

FK_JobCandidate_Employee_BusinessEntityID

FK_EmployeePayHistory_Employee_BusinessEntityID

FK_EmployeeDepartmentHistory_Shift_ShiftID

FK_EmployeeDepartmentHistory_Employee_BusinessEntityID

FK_EmployeeDepartmentHistory_Department_DepartmentID

FK_Employee_Person_BusinessEntityID

Document

DocumentNodeIDTitleOwnerFolderFlagFileNameFileExtensionRevisionChangeNumberStatusDocumentSummaryDocumentrowguidModifiedDate

integernvarcharintegerbitnvarcharnvarcharncharintegertinyintnvarcharvarbinary(32767)uniqueidentifiertimestamp

<pk>

<fk>

<ak>

Culture

CultureIDNameModifiedDate

ncharnvarchartimestamp

<pk>

BillOfMaterials

BillOfMaterialsIDProductAssemblyIDComponentIDStartDateEndDateUnitMeasureCodeBOMLevelPerAssemblyQtyModifiedDate

integerintegerintegertimestamptimestampncharsmallintdecimal(8,2)timestamp

<pk><fk1><fk2>

<fk3>

StateProvince

StateProvinceIDStateProvinceCodeCountryRegionCodeIsOnlyStateProvinceFlagNameTerritoryIDrowguidModifiedDate

integerncharnvarcharbitnvarcharintegeruniqueidentifiertimestamp

<pk>

<fk1>

<fk2>

PhoneNumberType

PhoneNumberTypeIDNameModifiedDate

integernvarchartimestamp

<pk>

PersonPhone

BusinessEntityIDPhoneNumberPhoneNumberTypeIDModifiedDate

integernvarcharintegertimestamp

<pk,fk1><pk><pk,fk2>

Person

BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastNameSuffixEmailPromotionAdditionalContactInfoDemographicsrowguidModifiedDate

integerncharbitnvarcharnvarcharnvarcharnvarcharnvarcharintegerxmlxmluniqueidentifiertimestamp

<pk,fk>

Password

BusinessEntityIDPasswordHashPasswordSaltrowguidModifiedDate

integervarchar(128)varchar(10)uniqueidentifiertimestamp

<pk,fk>

EmailAddress

BusinessEntityIDEmailAddressIDEmailAddressrowguidModifiedDate

integerintegernvarcharuniqueidentifiertimestamp

<pk,fk><pk>

CountryRegion

CountryRegionCodeNameModifiedDate

nvarcharnvarchartimestamp

<pk>

ContactType

ContactTypeIDNameModifiedDate

integernvarchartimestamp

<pk>

BusinessEntityContact

BusinessEntityIDPersonIDContactTypeIDrowguidModifiedDate

integerintegerintegeruniqueidentifiertimestamp

<pk,fk3><pk,fk1><pk,fk2> BusinessEntityAddress

BusinessEntityIDAddressIDAddressTypeIDrowguidModifiedDate

integerintegerintegeruniqueidentifiertimestamp

<pk,fk3><pk,fk1><pk,fk2>

BusinessEntity

BusinessEntityIDrowguidModifiedDate

integeruniqueidentifiertimestamp

<pk>

AddressType

AddressTypeIDNamerowguidModifiedDate

integernvarcharuniqueidentifiertimestamp

<pk>

Address

AddressIDAddressLine1AddressLine2CityStateProvinceIDPostalCodeSpatialLocationrowguidModifiedDate

integernvarcharnvarcharnvarcharintegernvarcharst_geometry(32767)uniqueidentifiertimestamp

<pk>

<fk>

Shift

ShiftIDNameStartTimeEndTimeModifiedDate

tinyintnvarchartimetimetimestamp

<pk>

JobCandidate

JobCandidateIDBusinessEntityIDResumeModifiedDate

integerintegerxmltimestamp

<pk><fk>

EmployeePayHistory

BusinessEntityIDRateChangeDateRatePayFrequencyModifiedDate

integertimestampnumeric(19,4)tinyinttimestamp

<pk,fk><pk>

EmployeeDepartmentHistory

BusinessEntityIDDepartmentIDShiftIDStartDateEndDateModifiedDate

integersmallinttinyintdatedatetimestamp

<pk,fk2><pk,fk1><pk,fk3><pk>

Employee

BusinessEntityIDNationalIDNumberLoginIDJobTitleBirthDateMaritalStatusGenderHireDateSalariedFlagVacationHoursSickLeaveHoursCurrentFlagrowguidModifiedDate

integernvarcharnvarcharnvarchardatencharnchardatebitsmallintsmallintbituniqueidentifiertimestamp

<pk,fk>

Department

DepartmentIDNameGroupNameModifiedDate

smallintnvarcharnvarchartimestamp

<pk>

Illustration

IllustrationIDDiagramModifiedDate

integerxmltimestamp

<pk>

Location

LocationIDNameCostRateAvailabilityModifiedDate

smallintnvarcharnumeric(10,4)decimal(8,2)timestamp

<pk>

Product

ProductIDProductNumberMakeFlagFinishedGoodsFlagNameColorSafetyStockLevelReorderPointListPriceSizeSizeUnitMeasureCodeStandardCostWeightUnitMeasureCodeWeightDaysToManufactureProductLineClassStyleProductSubcategoryIDProductModelIDSellStartDateSellEndDateDiscontinuedDaterowguidModifiedDate

integernvarcharbitbitnvarcharnvarcharsmallintsmallintnumeric(19,4)nvarcharncharnumeric(19,4)nchardecimal(8,2)integerncharncharncharintegerintegertimestamptimestamptimestampuniqueidentifiertimestamp

<pk>

<fk1>

<fk2>

<fk4><fk3>

ProductCategory

ProductCategoryIDNamerowguidModifiedDate


<pk>

ProductCostHistory

ProductIDStartDateEndDateStandardCostModifiedDate

integertimestamptimestampnumeric(19,4)timestamp

<pk,fk><pk>

ProductDescription

ProductDescriptionIDDescriptionrowguidModifiedDate


<pk>

ProductDocument

ProductIDDocumentNodeIDModifiedDate

integerintegertimestamp

<pk,fk1><pk,fk2>

ProductInventory

ProductIDLocationIDShelfBinQuantityrowguidModifiedDate

integersmallintnvarchartinyintsmallintuniqueidentifiertimestamp

<pk,fk2><pk,fk1>

ProductListPriceHistory

ProductIDStartDateEndDateListPriceModifiedDate

integertimestamptimestampnumeric(19,4)timestamp

<pk,fk><pk>

ProductModel

ProductModelIDNameCatalogDescriptionInstructionsrowguidModifiedDate

integernvarcharxmlxmluniqueidentifiertimestamp

<pk>

ProductModelIllustration

ProductModelIDIllustrationIDModifiedDate


<pk,fk1><pk,fk2>

ProductModelProductDescriptionCulture

ProductModelIDProductDescriptionIDCultureIDModifiedDate

integerintegernchartimestamp

<pk,fk3><pk,fk1><pk,fk2>

ProductPhoto

ProductPhotoIDThumbNailPhotoThumbnailPhotoFileNameLargePhotoLargePhotoFileNameModifiedDate

integervarbinary(32767)nvarcharvarbinary(32767)nvarchartimestamp

<pk>

ProductProductPhoto

ProductIDProductPhotoIDPrimaryModifiedDate

integerintegerbittimestamp

<pk,fk1><pk,fk2>

ProductReview

ProductReviewIDProductIDReviewerNameReviewDateEmailAddressRatingCommentsModifiedDate

integerintegernvarchartimestampnvarcharintegernvarchartimestamp

<pk><fk>

ProductSubcategory

ProductSubcategoryIDProductCategoryIDNamerowguidModifiedDate

integerintegernvarcharuniqueidentifiertimestamp

<pk><fk>

ScrapReason

ScrapReasonIDNameModifiedDate

smallintnvarchartimestamp

<pk>

TransactionHistory

TransactionIDProductIDReferenceOrderIDReferenceOrderLineIDTransactionDateTransactionTypeQuantityActualCostModifiedDate

integerintegerintegerintegertimestampncharintegernumeric(19,4)timestamp

<pk><fk>

UnitMeasure

UnitMeasureCodeNameModifiedDate


<pk>

WorkOrder

WorkOrderIDProductIDOrderQtyStockedQtyScrappedQtyStartDateEndDateDueDateScrapReasonIDModifiedDate

integerintegerintegerintegersmallinttimestamptimestamptimestampsmallinttimestamp

<pk><fk1>

<fk2>

WorkOrderRouting

WorkOrderIDProductIDOperationSequenceLocationIDScheduledStartDateScheduledEndDateActualStartDateActualEndDateActualResourceHrsPlannedCostActualCostModifiedDate

integerintegersmallintsmallinttimestamptimestamptimestamptimestampdecimal(9,4)numeric(19,4)numeric(19,4)timestamp

<pk,fk2><pk><pk><fk1>

ProductVendor

ProductIDBusinessEntityIDAverageLeadTimeStandardPriceLastReceiptCostLastReceiptDateMinOrderQtyMaxOrderQtyOnOrderQtyUnitMeasureCodeModifiedDate

integerintegerintegernumeric(19,4)numeric(19,4)timestampintegerintegerintegernchartimestamp

<pk,fk1><pk,fk3>

<fk2>

PurchaseOrderDetail

PurchaseOrderIDPurchaseOrderDetailIDDueDateOrderQtyProductIDUnitPriceLineTotalReceivedQtyRejectedQtyStockedQtyModifiedDate

integerintegertimestampsmallintintegernumeric(19,4)numeric(19,4)decimal(8,2)decimal(8,2)decimaltimestamp

<pk,fk2><pk>

<fk1>

PurchaseOrderHeader

PurchaseOrderIDRevisionNumberStatusEmployeeIDVendorIDShipMethodIDOrderDateShipDateSubTotalTaxAmtFreightTotalDueModifiedDate

integertinyinttinyintintegerintegerintegertimestamptimestampnumeric(19,4)numeric(19,4)numeric(19,4)numeric(19,4)timestamp

<pk>

<fk1><fk2><fk3>

ShipMethod

ShipMethodIDNameShipBaseShipRaterowguidModifiedDate

integernvarcharnumeric(19,4)numeric(19,4)uniqueidentifiertimestamp

<pk>

Vendor

BusinessEntityIDAccountNumberNameCreditRatingPreferredVendorStatusActiveFlagPurchasingWebServiceURLModifiedDate

integernvarcharnvarchartinyintbitbitnvarchartimestamp

<pk,fk>

CountryRegionCurrency

CountryRegionCodeCurrencyCodeModifiedDate

nvarcharnchartimestamp

<pk,fk1><pk,fk2>

CreditCard

CreditCardIDCardTypeCardNumberExpMonthExpYearModifiedDate

integernvarcharnvarchartinyintsmallinttimestamp

<pk>

Currency

CurrencyCodeNameModifiedDate


<pk>

CurrencyRate

CurrencyRateIDCurrencyRateDateFromCurrencyCodeToCurrencyCodeAverageRateEndOfDayRateModifiedDate

integertimestampncharncharnumeric(19,4)numeric(19,4)timestamp

<pk>

<fk1><fk2>

Customer

CustomerIDPersonIDStoreIDTerritoryIDAccountNumberrowguidModifiedDate

integerintegerintegerintegervarchar(10)uniqueidentifiertimestamp

<pk><fk1><fk2><fk3>

PersonCreditCard

BusinessEntityIDCreditCardIDModifiedDate


<pk,fk1><pk,fk2>

SalesOrderDetail

SalesOrderIDSalesOrderDetailIDCarrierTrackingNumberOrderQtyProductIDSpecialOfferIDUnitPriceUnitPriceDiscountLineTotalrowguidModifiedDate

integerintegernvarcharsmallintintegerintegernumeric(19,4)numeric(19,4)numeric(19,4)uniqueidentifiertimestamp

<pk,fk1><pk>

<fk2><fk2>

SalesOrderHeader

SalesOrderIDRevisionNumberOrderDateDueDateShipDateStatusOnlineOrderFlagSalesOrderNumberShipMethodIDPurchaseOrderNumberAccountNumberCustomerIDSalesPersonIDTerritoryIDBillToAddressIDShipToAddressIDCreditCardIDCreditCardApprovalCodeCurrencyRateIDSubTotalTaxAmtFreightTotalDueCommentrowguidModifiedDate

integertinyinttimestamptimestamptimestamptinyintbitnvarcharintegernvarcharnvarcharintegerintegerintegerintegerintegerintegervarchar(15)integernumeric(19,4)numeric(19,4)numeric(19,4)numeric(19,4)nvarcharuniqueidentifiertimestamp

<pk>

<fk7>

<fk5><fk6><fk8><fk1><fk2><fk3>

<fk4>

SalesOrderHeaderSalesReason

SalesOrderIDSalesReasonIDModifiedDate


<pk,fk2><pk,fk1>

SalesPerson

BusinessEntityIDTerritoryIDSalesQuotaBonusCommissionPctSalesYTDSalesLastYearrowguidModifiedDate

integerintegernumeric(19,4)numeric(19,4)numeric(10,4)numeric(19,4)numeric(19,4)uniqueidentifiertimestamp

<pk,fk1><fk2>

SalesPersonQuotaHistory

BusinessEntityIDQuotaDateSalesQuotarowguidModifiedDate

integertimestampnumeric(19,4)uniqueidentifiertimestamp

<pk,fk><pk>

SalesReason

SalesReasonIDNameReasonTypeModifiedDate

integernvarcharnvarchartimestamp

<pk>

SalesTaxRate

SalesTaxRateIDStateProvinceIDTaxTypeTaxRateNamerowguidModifiedDate

integerintegertinyintnumeric(10,4)nvarcharuniqueidentifiertimestamp

<pk><fk>

SalesTerritory

TerritoryIDNameCountryRegionCodeGroupSalesYTDSalesLastYearCostYTDCostLastYearrowguidModifiedDate

integernvarcharnvarcharnvarcharnumeric(19,4)numeric(19,4)numeric(19,4)numeric(19,4)uniqueidentifiertimestamp

<pk>

<fk>

SalesTerritoryHistory

BusinessEntityIDTerritoryIDStartDateEndDaterowguidModifiedDate

integerintegertimestamptimestampuniqueidentifiertimestamp

<pk,fk1><pk,fk2><pk>

ShoppingCartItem

ShoppingCartItemIDShoppingCartIDQuantityProductIDDateCreatedModifiedDate

integernvarcharintegerintegertimestamptimestamp

<pk>

<fk>

SpecialOffer

SpecialOfferIDDescriptionDiscountPctTypeCategoryStartDateEndDateMinQtyMaxQtyrowguidModifiedDate

integernvarcharnumeric(10,4)nvarcharnvarchartimestamptimestampintegerintegeruniqueidentifiertimestamp

<pk>

SpecialOfferProduct

SpecialOfferIDProductIDrowguidModifiedDate

integerintegeruniqueidentifiertimestamp

<pk,fk2><pk,fk1>

Store

BusinessEntityIDNameSalesPersonIDDemographicsrowguidModifiedDate

integernvarcharintegerxmluniqueidentifiertimestamp

<pk,fk1>

<fk2>

8

Functions

SELECT c.CustomerID, c.AccountNumber, soh.SalesOrderID, convert (date, soh.DueDate) as DueDate, cast (soh.ShipDate as date) as ShipDate, soh.SalesOrderNumber, soh.PurchaseOrderNumber, soh.TerritoryID,soh.ShipMethodID, soh.CreditCardID, soh.SubTotal, soh.Freight, soh.TaxAmt, soh.TotalDue, dateformat( soh.OrderDate,'MM-DD-YYYY')as OrderDateFROM Sales.Customer cinner join Sales.SalesOrderHeader soh on c.CustomerID = soh.CustomerIDinner join Sales.SalesOrderDetail sodon soh.SalesOrderID = sod.SalesOrderID

CustomerID AccountNumber

SalesOrderID DueDate ShipDate SalesOrderNumber

PurchaseOrderNumber TerritoryID ShipMethodID CreditCardID SubTotal Freight TaxAmt TotalDue OrderDate

29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 16281 24643.9362 616.0984 1971.5149 27231.5495 07-01-200129825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 16281 24643.9362 616.0984 1971.5149 27231.5495 07-01-200129825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 16281 24643.9362 616.0984 1971.5149 27231.5495 07-01-200129825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 16281 24643.9362 616.0984 1971.5149 27231.5495 07-01-200129825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 16281 24643.9362 616.0984 1971.5149 27231.5495 07-01-2001

Results Abridged

SELECT string(LastName, FirstName) AS Person_Name, lastName||','||firstName as Person_Name2FROM Person.Person

Person_Name Person_Name2

SánchezKen Sánchez,Ken

DuffyTerri Duffy,Terri

TamburelloRoberto

Tamburello,Roberto

WaltersRob Walters,Rob

EricksonGail Erickson,Gail

GoldbergJossef Goldberg,Jossef

Results Abridged

SELECT c.CustomerID, c.AccountNumber, soh.SalesOrderID, convert (date, soh.DueDate) as DueDate, cast (soh.ShipDate as date) as ShipDate, soh.SalesOrderNumber, soh.PurchaseOrderNumber, soh.TerritoryID,soh.ShipMethodID, soh.SubTotal, soh.Freight, soh.TaxAmt, soh.TotalDue, year( soh.OrderDate) ||'-'|| monthname( soh.OrderDate) ||'-'|| day( soh.OrderDate) ||'-'||dayname( soh.OrderDate)||','|| quarter( soh.OrderDate) as OrderDateFROM Sales.Customer cinner join Sales.SalesOrderHeader soh on c.CustomerID = soh.CustomerIDinner join Sales.SalesOrderDetail sodon soh.SalesOrderID = sod.SalesOrderID


SalesOrderID DueDate ShipDate SalesOrderNumber PurchaseOrderNumber

TerritoryID ShipMethodID

SubTotal Freight TaxAmt TotalDue OrderDate

29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495

2001-July-1-Sunday,3

29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


29825 AW00029825 43659 2001-07-13 2001-07-08 SO43659 PO522145787 5 5 24643.9362 616.0984 1971.5149 27231.5495


9

29672 AW00029672 43660 2001-07-13 2001-07-08 SO43660 PO18850127500 5 5 1553.1035 38.8276 124.2483 1716.1794 2001-July-1-Sunday,329672 AW00029672 43660 2001-07-13 2001-07-08 SO43660 PO18850127500 5 5 1553.1035 38.8276 124.2483 1716.1794 2001-July-1-Sunday,329734 AW00029734 43661 2001-07-13 2001-07-08 SO43661 PO18473189620 6 5 39422.1198 985.5530 3153.7696 43561.442


29734 AW00029734 43661 2001-07-13 2001-07-08 SO43661 PO18473189620 6 5 39422.1198 985.5530 3153.7696 43561.4424


29734 AW00029734 43661 2001-07-13 2001-07-08 SO43661 PO18473189620 6 5 39422.1198 985.5530 3153.7696 43561.4424


29734 AW00029734 43661 2001-07-13 2001-07-08 SO43661 PO18473189620 6 5 39422.1198 985.5530 3153.7696 43561.4424


29734 AW00029734 43661 2001-07-13 2001-07-08 SO43661 PO18473189620 6 5 39422.1198 985.5530 3153.7696 43561.4424


Results Abridged

SELECT c.CustomerID, c.AccountNumber, soh.SalesOrderID, convert (date, soh.DueDate) as DueDate, cast (soh.ShipDate as date) as ShipDate, soh.SalesOrderNumber, soh.PurchaseOrderNumber, j.Name as TerritoryName, soh.ShipMethodID, cast( soh.SubTotal as decimal(35,2))as Subtotal, cast( soh.Freight as decimal(35,2))as Freight, cast(soh.TaxAmt as decimal(35,2))as TaxAmt , cast (soh.TotalDue as decimal(35,2)) as TotalDue2 , soh.TotalDue, year( soh.OrderDate) ||'-'|| monthname( soh.OrderDate) ||'-'|| day( soh.OrderDate) ||'-'||dayname( soh.OrderDate)||','|| quarter( soh.OrderDate) as OrderDateFROM Sales.Customer cinner join Sales.SalesOrderHeader soh on c.CustomerID = soh.CustomerIDinner join Sales.SalesOrderDetail sodon soh.SalesOrderID = sod.SalesOrderIDinner join Sales.SalesTerritory jon soh.TerritoryID = j.TerritoryID


SalesOrderID DueDate ShipDate SalesOrderNumber

PurchaseOrderNumber TerritoryName ShipMethodID Subtotal Freight TaxAmt

TotalDue2 TotalDue OrderDate

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29614 AW00029614 43668 2001-07-13 2001-07-08 SO43668 PO14732180295 Canada 5 43272.07

1081.80 3461.77 47815.63 47815.6341 2001-July-1-Sunday,3

29747 AW00029747 43669 2001-07-13 2001-07-08 SO43669 PO14123169936 Northwest 5 881.47 22.04 70.52 974.02 974.0229 2001-July-1-Sunday,329566 AW00029566 43670 2001-07-13 2001-07-08 SO43670 PO14384116310 Central 5 7344.50 183.61 587.56 8115.68 8115.6763 2001-July-1-Sunday,329566 AW00029566 43670 2001-07-13 2001-07-08 SO43670 PO14384116310 Central 5 7344.50 183.61 587.56 8115.68 8115.6763 2001-July-1-Sunday,329566 AW00029566 43670 2001-07-13 2001-07-08 SO43670 PO14384116310 Central 5 7344.50 183.61 587.56 8115.68 8115.6763 2001-July-1-Sunday,329566 AW00029566 43670 2001-07-13 2001-07-08 SO43670 PO14384116310 Central 5 7344.50 183.61 587.56 8115.68 8115.6763 2001-July-1-Sunday,329890 AW00029890 43671 2001-07-13 2001-07-08 SO43671 PO13978119376 Northwest 5 9760.17 244.00 780.81 10784.99 10784.9873 2001-July-1-Sunday,329890 AW00029890 43671 2001-07-13 2001-07-08 SO43671 PO13978119376 Northwest 5 9760.17 244.00 780.81 10784.99 10784.9873 2001-July-1-Sunday,329890 AW00029890 43671 2001-07-13 2001-07-08 SO43671 PO13978119376 Northwest 5 9760.17 244.00 780.81 10784.99 10784.9873 2001-July-1-Sunday,3

Results Abridged

SELECT string(LastName, FirstName) AS Person_Name, UPPER (lastName)||','|| LOWER(firstName) as Person_Name2FROM Person.Person

Person_Name Person_Name2SánchezKen SÁNCHEZ,kenDuffyTerri DUFFY,terriTamburelloRoberto TAMBURELLO,robertoWaltersRob WALTERS,robEricksonGail ERICKSON,gailGoldbergJossef GOLDBERG,jossefMillerDylan MILLER,dylanMargheimDiane MARGHEIM,dianeMatthewGigi MATTHEW,gigiRaheemMichael RAHEEM,michaelCraciumOvidiu CRACIUM,ovidiuD'HersThierry D'HERS,thierryGalvinJanice GALVIN,janiceSullivanMichael SULLIVAN,michaelSalavariaSharon SALAVARIA,sharonBradleyDavid BRADLEY,davidBrownKevin BROWN,kevinWoodJohn WOOD,johnDempseyMary DEMPSEY,maryBenshoofWanida BENSHOOF,wanidaEminhizerTerry EMINHIZER,terry

10

HarnpadoungsatayaSariya HARNPADOUNGSATAYA,sariyaResults Abridged

WITH ORDERS4(CUSTOMER_ID,TOTAL_DUE)AS(SELECT CUSTOMERID AS CUSTOMER_ID, TotalDue AS TOTAL_DUEFROM ORDERS)

SELECT DISTINCT (CUSTOMER_ID)as CUSTOMER_ID, cast(TOTAL_DUE as decimal (23,2))as TOTAL_DUEFROM ORDERS4WHERE TOTAL_DUE <> (SELECT cast (max(TotalDue)as decimal (23,2)) FROM ORDERS)

CUSTOMER_ID TOTAL_DUE29825 27231.5529672 1716.1829734 43561.4429994 38331.9629565 556.2029898 32390.2029580 19005.2130052 6718.0529974 8095.7929614 47815.6329747 974.0229566 8115.6829890 10784.99

Results AbridgedSELECT HRE.BusinessEntityID, HRE.BirthDate, HRE.Gender, HRE.MaritalStatus, HRE.NationalIDNumber, HRE.SickLeaveHours, HRE.VacationHours,DATEDIFF(YY, BirthDate , CURRENT_TIMESTAMP) AS AGEFROM HumanResources.Employee HRE

BusinessEntityID

BirthDate Gender MaritalStatus NationalIDNumber SickLeaveHours

VacationHours AGE

1 1959-03-02 M S 295847284 69 99 542 1961-09-01 F S 245797967 20 1 523 1964-12-13 M M 509647174 21 2 494 1965-01-23 M S 112457891 80 48 485 1942-10-29 F M 695256908 22 5 716 1949-04-11 M M 998320692 23 6 647 1977-03-27 M M 134969118 50 61 368 1976-07-06 F S 811994146 51 62 379 1969-02-21 F M 658797903 51 63 4410 1975-01-01 M M 879342154 64 16 3811 1968-02-18 M S 974026903 23 7 4512 1949-08-29 M M 480168528 24 9 6413 1979-06-29 F M 486228782 24 8 3414 1969-07-17 M S 42487730 21 3 4415 1951-06-03 F M 56920285 22 4 6216 1965-04-19 M S 24756624 40 40 4817 1977-06-03 M S 253022876 41 42 36

Results Abridged

SELECT SOH.CustomerID, CAST (SOH.OrderDate AS DATE)OrderDate, SOH.SalesOrderID,CAST(SOH.Freight AS DECIMAL(30,2))AS Freight, CAST(SOH.TaxAmt AS DECIMAL(30,2))AS TaxAmt,CAST(SOH.TotalDue AS DECIMAL(30,2))AS TotalDue,SCC.CardType, encrypt(SCC.CardNumber, 'AES256') AS CardNum, SCC.ExpMonth||'-'||SCC.ExpYear AS EXP_DATE,SCY.Name AS Currency_Name, STR.CountryRegionCode, STR.Name AS Territory_Name, STR."Group" AS TerritoryGroupFROM Sales.SalesOrderHeader SOHINNER JOIN Sales.Customer SCONSOH. CUSTOMERID = SC.CUSTOMERIDINNER JOINSales.CreditCard SCCON SCC.CREDITCARDID = SOH.CREDITCARDIDINNER JOIN SALES.CurrencyRate SCRON SOH.CurrencyRateID = SCR.CurrencyRateIDINNER JOINSALES.SalesTerritory STRON STR.TerritoryID = SOH.TerritoryIDINNER JOINSALES.Currency SCYONSCY.CurrencyCode = SCR.FromCurrencyCodeOR

11

SCY.CurrencyCode = SCR.ToCurrencyCode

CustomerID

OrderDate SalesOrderID

Freight TaxAmt TotalDue CardType CardNum EXP_DATE Currency_Name

CountryRegionCode Territory_Name

TerritoryGroup

29734 2001-07-01 43661 985.55 3153.77 43561.44 Distinguish 0xefb7785a52c60cb9c9a3d3f0f28f3a3b3fd0867fa05bdb18db2f989ce78d0894 9-2007 US Dollar CA Canada North America29734 2001-07-01 43661 985.55 3153.77 43561.44 Distinguish 0xefb7785a52c60cb9c9a3d3f0f28f3a3b3fd0867fa05bdb18db2f989ce78d0894 9-2007 Canadian Dollar CA Canada North America29994 2001-07-01 43662 867.24 2775.16 38331.96 ColonialVoice 0x48dc87c0e811fa57175e155dd70fbf1955c087036917ff8264b9939a8c3e54d4 1-2006 US Dollar CA Canada North America29994 2001-07-01 43662 867.24 2775.16 38331.96 ColonialVoice 0x48dc87c0e811fa57175e155dd70fbf1955c087036917ff8264b9939a8c3e54d4 1-2006 Canadian Dollar CA Canada North America29614 2001-07-01 43668 1081.80 3461.77 47815.63 ColonialVoice 0x35897b5ee7b21cf16e3a9a7960a4c153552917e14c6094338c808810207de212 11-2008 US Dollar CA Canada North America29614 2001-07-01 43668 1081.80 3461.77 47815.63 ColonialVoice 0x35897b5ee7b21cf16e3a9a7960a4c153552917e14c6094338c808810207de212 11-2008 Canadian Dollar CA Canada North America30067 2001-07-01 43672 183.63 587.60 8116.26 Vista 0x64a8d7a7dab2cecd5f6c22668b736db6bc47dd8bc6d1abc5d16c2e11ec58c4bd 3-2006 US Dollar CA Canada North America30067 2001-07-01 43672 183.63 587.60 8116.26 Vista 0x64a8d7a7dab2cecd5f6c22668b736db6bc47dd8bc6d1abc5d16c2e11ec58c4bd 3-2006 Canadian Dollar CA Canada North America29596 2001-07-01 43674 78.73 251.94 3479.93 SuperiorCard 0x8219aaaac5c8bf1d0e92301fefb2f876244494067ff7c8357f29ce4cd2b9ced8 4-2005 US Dollar CA Canada North America29596 2001-07-01 43674 78.73 251.94 3479.93 SuperiorCard 0x8219aaaac5c8bf1d0e92301fefb2f876244494067ff7c8357f29ce4cd2b9ced8 4-2005 Canadian Dollar CA Canada North America29824 2001-07-01 43677 233.47 747.10 10319.33 SuperiorCard 0xae39eae197c2691c9a7b5e3356175a8f53f1d7bc5e6dbed1359c0ca358098495 4-2006 US Dollar CA Canada North America29824 2001-07-01 43677 233.47 747.10 10319.33 SuperiorCard 0xae39eae197c2691c9a7b5e3356175a8f53f1d7bc5e6dbed1359c0ca358098495 4-2006 Canadian Dollar CA Canada North America29761 2001-07-01 43679 39.31 125.80 1737.66 SuperiorCard 0x394b3d44f4e5b7fccbd26d6d4e3fba6d4121dcf0472f7a4ac9eb922222f9a020 9-2008 US Dollar CA Canada North America29761 2001-07-01 43679 39.31 125.80 1737.66 SuperiorCard 0x394b3d44f4e5b7fccbd26d6d4e3fba6d4121dcf0472f7a4ac9eb922222f9a020 9-2008 Canadian Dollar CA Canada North America21768 2001-07-01 43697 89.46 286.26 3953.99 SuperiorCard 0x477e43dc99fc3fc8e8dc127b735cf3c51b37b17addfb8f0064d3e3c2fffc0d0e 7-2008 US Dollar CA Canada North America

Results Abridged

SELECT SOH.SalesOrderID, SOH.CustomerID,CAST(SOH.TotalDue AS DECIMAL(30,2))AS TotalDue,STR.CountryRegionCode, STR.Name AS Territory_Name, STR."Group" AS TerritoryGroup,SUM(CAST(SOH.TotalDue AS DECIMAL(30,2)))over(PARTITION BY STR.CountryRegionCode) AS TOTAL_Territor_Sales_Region,SUM(CAST(SOH.TotalDue AS DECIMAL(30,2)))over(PARTITION BY STR."Group") AS TOTAL_Territor_Sales_Group,SUM(CAST(SOH.TotalDue AS DECIMAL(30,2)))over(PARTITION BY STR.Name) AS TOTAL_Territor_Sales_NameFROM Sales.SalesOrderHeader SOHINNER JOINSALES.SalesTerritory STRON STR.TerritoryID = SOH.TerritoryIDwhere STR.CountryRegionCode = 'US'

SalesOrderID CustomerID

TotalDue CountryRegionCode

Territory_Name TerritoryGroup TOTAL_Territor_Sales_Region TOTAL_Territor_Sales_Group TOTAL_Territor_Sales_Name

66560 21246 1320.44 US Southwest North America 82425339.37 82425339.37 31213458.7170656 24831 2649.85 US Southwest North America 82425339.37 82425339.37 31213458.7170912 17441 2657.47 US Southwest North America 82425339.37 82425339.37 31213458.7144544 29522 27847.06 US Southwest North America 82425339.37 82425339.37 31213458.7145568 29637 20205.63 US Southwest North America 82425339.37 82425339.37 31213458.7146336 29944 556.20 US Southwest North America 82425339.37 82425339.37 31213458.7147104 13279 2410.63 US Southwest North America 82425339.37 82425339.37 31213458.7147360 29696 1051.23 US Southwest North America 82425339.37 82425339.37 31213458.7148128 26534 2288.92 US Southwest North America 82425339.37 82425339.37 31213458.7148384 29562 90625.93 US Southwest North America 82425339.37 82425339.37 31213458.7148640 26681 2288.92 US Southwest North America 82425339.37 82425339.37 31213458.7149152 29792 30842.05 US Southwest North America 82425339.37 82425339.37 31213458.7149408 15535 865.20 US Southwest North America 82425339.37 82425339.37 31213458.7150688 29793 77584.01 US Southwest North America 82425339.37 82425339.37 31213458.7151968 13171 132.60 US Southwest North America 82425339.37 82425339.37 31213458.7155296 29850 2916.30 US Southwest North America 82425339.37 82425339.37 31213458.7157344 12047 38.68 US Southwest North America 82425339.37 82425339.37 31213458.7158368 25208 30.14 US Southwest North America 82425339.37 82425339.37 31213458.7160160 13454 2590.41 US Southwest North America 82425339.37 82425339.37 31213458.71

Results Abridged

User Defined Function

CREATE OR REPLACE FUNCTION EMPAGE( )RETURNS INTEGER

BEGIN

SELECT DATEDIFF(YY, HRE.BirthDate , CURRENT_TIMESTAMP) AS EAGEFROM HumanResources.Employee HRE

END;

SELECT SELECT "DBA"."EMPAGE"();

12

CREATE OR REPLACE FUNCTION EMPAGE( in @SNN integer)RETURNS INTEGER

BEGIN DECLARE @EMPAGE1 integer;

SELECT DATEDIFF(YY, HRE.BirthDate , CURRENT_TIMESTAMP) AS EAGEINTO @EMPAGE1FROM HumanResources.Employee HRE WHERE HRE.NationalIDNumber = @SNN;

RETURN (@EMPAGE1);END;

SELECT "DBA"."EMPAGE"(509647174 ) AS EMP_AGE;

Stored Procedures

CREATE OR REPLACE PROCEDURE SalesTerritory_Report(@Region_Code Char(8))

13

ASSELECT SOH.SalesOrderNumber, CAST (SOH.OrderDate AS DATE) AS OrderDate , CAST (SOH.DueDate AS DATE) AS DueDate, CAST (SOH.ShipDate AS DATE) AS ShipDate, SOD.ProductID,SOH.CUSTOMERID AS CustomerID, cast (SOH.SUBTOTAL as Decimal (35,2))AS SubTotal, cast (SOH.TAXAMT as Decimal (35,2))AS TaxAmt, cast (SOH.FREIGHT as Decimal (35,2))AS Freight, cast (SOH.TOTALDUE as Decimal (35,2))AS TotalDue, PD.Name AS ProductName, ST.NAME as TerritoryName, ST.CountryRegionCode,ST."Group" AS TerritoryGroup, ST.TerritoryIDFROM Sales.SalesOrderDetail SODINNER JOIN Sales.SalesOrderHeader SOHON SOD.SalesOrderID = SOH.SalesOrderIDINNER JOIN Production.Product PDON SOD.PRODUCTID = PD.PRODUCTIDINNER JOIN SALES.SalesTerritory STON SOH.TerritoryID = ST.TerritoryIDwhere CountryRegionCode = @Region_CodeORDER BY PD.NAME;

EXEC SalesTerritory_Report('GB')

SalesOrderNumber

OrderDate DueDate ShipDate ProductID CustomerID SubTotal TaxAmt

Freight TotalDue ProductName TerritoryName CountryRegionCode TerritoryGroup TerritoryID

SO73803 2004-06-26 2004-07-08

2004-07-03 879 16938 928.49 74.28 23.21 1025.98 All-Purpose Bike Stand

United Kingdom GB Europe 10

SO70784 2004-05-17 2004-05-29



SO71313 2004-05-25 2004-06-06



SO74525 2004-07-12 2004-07-24



SO69201 2004-04-28 2004-05-10



SO69205 2004-04-28 2004-05-10



SO55484 2003-10-03 2003-10-15



SO60357 2003-12-19 2003-12-31



SO57657 2003-11-09 2003-11-21



SO64640 2004-02-21 2004-03-04



SO62727 2004-01-25 2004-02-06



SO56027 2003-10-13 2003-10-25



SO59644 2003-12-08 2003-12-20



SO57775 2003-11-11 2003-11-23



SO64183 2004-02-15 2004-02-27



Results Abridged

CREATE OR REPLACE PROCEDURE PRODUCTS()ASSELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PPD.Description,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'

exec PRODUCTS;

ProductID

NAME COLOR

Size SizeUnitMeasureCode

Description SubCatgo_Name

680 HL Road Frame - Black, 58 Black 58 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames706 HL Road Frame - Red, 58 Red 58 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames

14

707 Sport-100 Helmet, Red Red Universal fit, well-vented, lightweight , snap-on visor. Helmets708 Sport-100 Helmet, Black Black Universal fit, well-vented, lightweight , snap-on visor. Helmets709 Mountain Bike Socks, M White M Combination of natural and synthetic fibers stays dry and provides just the right cushioning. Socks710 Mountain Bike Socks, L White L Combination of natural and synthetic fibers stays dry and provides just the right cushioning. Socks711 Sport-100 Helmet, Blue Blue Universal fit, well-vented, lightweight , snap-on visor. Helmets712 AWC Logo Cap Multi Traditional style with a flip-up brim; one-size fits all. Caps713 Long-Sleeve Logo Jersey, S Multi S Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys714 Long-Sleeve Logo Jersey, M Multi M Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys715 Long-Sleeve Logo Jersey, L Multi L Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys716 Long-Sleeve Logo Jersey, XL Multi XL Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys717 HL Road Frame - Red, 62 Red 62 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames718 HL Road Frame - Red, 44 Red 44 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames719 HL Road Frame - Red, 48 Red 48 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames720 HL Road Frame - Red, 52 Red 52 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames721 HL Road Frame - Red, 56 Red 56 CM Our lightest and best quality aluminum frame made from the newest alloy; it is welded and heat-treated for strength. Our innovative design results in maximum comfort and performance. Road Frames

Results Abridged

CREATE OR REPLACE PROCEDURE PRODUCTS2 (@sub_catgo_name char(35))ASSELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PPD.Description,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PSC.NAME = @sub_catgo_name;

Exec PRODUCTS2( ‘Jerseys’);

ProductID NAME COLOR Size SizeUnitMeasureCode Description SubCatgo_Name

713 Long-Sleeve Logo Jersey, S Multi S Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys714 Long-Sleeve Logo Jersey, M Multi M Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys715 Long-Sleeve Logo Jersey, L Multi L Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys716 Long-Sleeve Logo Jersey, XL Multi XL Unisex long-sleeve AWC logo microfiber cycling jersey Jerseys881 Short-Sleeve Classic Jersey, S Yellow S Short sleeve classic breathable jersey with superior moisture control, front zipper, and 3 back

pockets.Jerseys

882 Short-Sleeve Classic Jersey, M Yellow M Short sleeve classic breathable jersey with superior moisture control, front zipper, and 3 back pockets.

Jerseys

883 Short-Sleeve Classic Jersey, L Yellow L Short sleeve classic breathable jersey with superior moisture control, front zipper, and 3 back pockets.

Jerseys

884 Short-Sleeve Classic Jersey, XL Yellow XL Short sleeve classic breathable jersey with superior moisture control, front zipper, and 3 back pockets.

Jerseys

/* Sybase Watcon SQL Syntax*/CREATE OR REPLACE PROCEDURE SALES_TERRITORY_GB( in @COUNTRYCODE char(8)) RESULT(SalesOrderNumber NVARCHAR(25),AccountNumber VARCHAR (10),OrderDate DATE ,DueDate DATE,ShipDate DATE,ProductID INTEGER, CustomerID INTEGER,SubTotal DECIMAL(35,2),TaxAmt DECIMAL (35,2),Freight DECIMAL(35,2),TotalDue DECIMAL(32,2), PRODUCTNAME NAME,"Description" NVARCHAR(400),COLOR NVARCHAR (15),"Size" NVARCHAR(5), SizeUnitMeasureCode NVARCHAR (3),SubCatgoName NAME,ProductSubcategoryID INTEGER,CategoryName NAME,ProductCategoryID INTEGER , TerritoryName NAME,CountryRegionCode NVARCHAR (3),TerritoryGroup NVARCHAR (50),TerritoryID INTEGER )--on exception resume--begin CASE WHEN @COUNTRYCODE = 'GB' THEN select SalesOrderNumber,AccountNumber,OrderDate,DueDate,ShipDate,ProductID, CustomerID,SubTotal,TaxAmt,Freight,TotalDue, PRODUCTNAME,Description,COLOR,Size,SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID,CategoryName,ProductCategoryID, TerritoryName,CountryRegionCode,TerritoryGroup,TerritoryID from SalesReports where CountryRegionCode = @COUNTRYCODE END CASE;End

15

EXEC SALES_TERRITORY_GB('US')Execution time: 0.064 seconds

EXEC SALES_TERRITORY_GB('UK')

SalesOrderNumber OrderDate DueDate ShipDate ProductID CustomerID SubTotal TaxAmt Freight

TotalDue

ProductName TerritoryName CountryRegionCode TerritoryGroup

TerritoryID

SO73803 2004-06-26 2004-07-08 2004-07-03

879 16938 928.49 74.28 23.21 1025.98 All-Purpose Bike Stand United Kingdom GB Europe 10

SO70784 2004-05-17 2004-05-29 2004-05-24


SO71313 2004-05-25 2004-06-06 2004-06-01


SO74525 2004-07-12 2004-07-24 2004-07-19


SO69201 2004-04-28 2004-05-10 2004-05-05


SO69205 2004-04-28 2004-05-10 2004-05-05


SO55484 2003-10-03 2003-10-15 2003-10-10


SO60357 2003-12-19 2003-12-31 2003-12-26


SO57657 2003-11-09 2003-11-21 2003-11-16


Results Abridged

CREATE OR REPLACE PROCEDURE SALES_TERRITORY_GB( in @COUNTRYCODE char(8), OUT SalesOrderNumber NVARCHAR(25), OUT AccountNumber VARCHAR (10), OUT OrderDate DATE , OUT DueDate DATE, OUT ShipDate DATE, OUT ProductID INTEGER, OUT CustomerID INTEGER, OUT SubTotal DECIMAL(35,2), OUT TaxAmt DECIMAL (35,2), OUT Freight DECIMAL(35,2), OUT TotalDue DECIMAL(32,2), OUT PRODUCTNAME NAME, OUT "Description" NVARCHAR(400), OUT COLOR NVARCHAR (15), OUT "Size" NVARCHAR(5), OUT SizeUnitMeasureCode NVARCHAR (3), OUT SubCatgoName NAME, OUT ProductSubcategoryID INTEGER, OUT CategoryName NAME,OUT ProductCategoryID INTEGER , OUT TerritoryName NAME, OUT CountryRegionCode NVARCHAR (3), OUT TerritoryGroup NVARCHAR (50), OUT TerritoryID INTEGER )

--on exception resume--begin

CASE WHEN @COUNTRYCODE = 'GB' THEN select SalesOrderNumber,AccountNumber,OrderDate,DueDate,ShipDate,ProductID, CustomerID,SubTotal,TaxAmt,Freight,TotalDue, PRODUCTNAME,Description,COLOR,Size,SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID,CategoryName,ProductCategoryID, TerritoryName,CountryRegionCode,TerritoryGroup,TerritoryID from SalesReports where CountryRegionCode = @COUNTRYCODE END CASE;End

SalesOrderNumber OrderDate DueDate ShipDate ProductID CustomerID SubTotal TaxAmt Freight

TotalDue

ProductName TerritoryName CountryRegionCode TerritoryGroup

TerritoryID

SO73803 2004-06-26 2004-07-08 2004-07-03


SO70784 2004-05-17 2004-05-29 2004-05-24


SO71313 2004-05-25 2004-06-06 2004-06-01


SO74525 2004-07-12 2004-07-24 2004-07- 879 13830 195.59 15.65 4.89 216.13 All-Purpose Bike Stand United Kingdom GB Europe 10

16

19SO69201 2004-04-28 2004-05-10 2004-05-


SO69205 2004-04-28 2004-05-10 2004-05-05


SO55484 2003-10-03 2003-10-15 2003-10-10


SO60357 2003-12-19 2003-12-31 2003-12-26


SO57657 2003-11-09 2003-11-21 2003-11-16


Results Abridged

DDL

CREATE VIEW ORDERSASSELECT c.CustomerID, c.AccountNumber, soh.SalesOrderID, convert (date, soh.DueDate) as DueDate, cast (soh.ShipDate as date) as ShipDate, soh.SalesOrderNumber, soh.PurchaseOrderNumber, j.Name as TerritoryName, soh.ShipMethodID, cast( soh.SubTotal as decimal(35,2))as Subtotal, cast( soh.Freight as decimal(35,2))as Freight, cast(soh.TaxAmt as decimal(35,2))as TaxAmt , soh.TotalDue, year( soh.OrderDate) ||'-'|| monthname( soh.OrderDate) ||'-'|| day( soh.OrderDate) ||'-'||dayname( soh.OrderDate)||','|| quarter( soh.OrderDate) as OrderDateFROM Sales.Customer cinner join Sales.SalesOrderHeader soh on c.CustomerID = soh.CustomerIDinner join Sales.SalesOrderDetail sodon soh.SalesOrderID = sod.SalesOrderIDinner join Sales.SalesTerritory jon soh.TerritoryID = j.TerritoryID

SELECT CustomerID AS CUSTOMER_ID, CAST (AVG(TotalDue)AS DECIMAL (35,2)) AS AVG_TOTAL_DUE, CAST (SUM(TOTALDUE)AS DECIMAL(35,2)) SUM_TOTAL, CAST (MAX(TotalDue)AS DECIMAL(35,2))AS MAX_TOTAL_DUE, CAST (MIN(TotalDue)AS DECIMAL(35,2))AS MIN_TOTAL_DUE2FROM ORDERSGROUP BY CustomerID

CUSTOMER_ID AVG_TOTAL_DUE SUM_TOTAL MAX_TOTAL_DUE

MIN_TOTAL_DUE

20818 1259.80 7558.83 3953.99 720.9724921 1254.27 5017.08 1303.87 1105.4812648 2596.35 20770.84 2699.01 2264.2516727 2708.69 5417.37 2708.69 2708.6929012 87.27 174.55 87.27 87.2712655 2814.05 30954.56 3953.99 2673.0620817 2275.34 4550.68 3953.99 596.6924920 1219.70 3659.09 1276.81 1105.4816726 8.04 16.09 8.04 8.0429019 1890.64 5671.91 3953.99 858.9620816 1257.95 7547.72 3953.99 718.7512654 32.54 130.17 40.08 9.9324927 211.35 845.41 211.35 211.3529018 70.69 212.06 70.69 70.6916725 15.45 30.90 15.45 15.4529017 3953.99 3953.99 3953.99 3953.99

Results Abridged

SELECT SOH.SalesOrderNumber, CAST (SOH.OrderDate AS DATE) AS OrderDate , CAST (SOH.DueDate AS DATE) AS DueDate, CAST (SOH.ShipDate AS DATE) AS ShipDate, SOD.ProductID,SOH.CUSTOMERID AS CustomerID, cast (SOH.SUBTOTAL as Decimal (35,2))AS SubTotal, cast (SOH.TAXAMT as Decimal (35,2))AS TaxAmt, cast (SOH.FREIGHT as Decimal (35,2))AS Freight, cast (SOH.TOTALDUE as Decimal (35,2))AS TotalDue, PD.Name AS ProductName, ST.NAME as TerritoryName,ST.CountryRegionCode,ST."Group" AS TerritoryGroup, ST.TerritoryIDINTO ORDERS_RECORDFROM Sales.SalesOrderDetail SODINNER JOIN Sales.SalesOrderHeader SOHON SOD.SalesOrderID = SOH.SalesOrderIDINNER JOIN Production.Product PDON SOD.PRODUCTID = PD.PRODUCTIDINNER JOIN SALES.SalesTerritory STON SOH.TerritoryID = ST.TerritoryIDORDER BY ST.CountryRegionCode

Note Group is a reserved word and to use that column with name group, a double quotation has to be embraced to allow Sybase SQL Anywhere database to process the query.

17

Select * from ORDERS_RECORD

SalesOrderNumber

OrderDate DueDate ShipDate ProductID CustomerID SubTotal

TaxAmt Freight TotalDue ProductName TerritoryName

CountryRegionCode TerritoryGroup TerritoryID

SO43701 2001-07-01 2001-07-13 2001-07-08

773 11003 3399.99 272.00 85.00 3756.99 Mountain-100 Silver, 44 Australia AU Pacific 9

SO43703 2001-07-02 2001-07-14 2001-07-09

749 16624 3578.27 286.26 89.46 3953.99 Road-150 Red, 62 Australia AU Pacific 9

SO43704 2001-07-02 2001-07-14 2001-07-09

778 11005 3374.99 270.00 84.37 3729.36 Mountain-100 Black, 48 Australia AU Pacific 9

SO43705 2001-07-02 2001-07-14 2001-07-09


SO43709 2001-07-03 2001-07-15 2001-07-10


SO43710 2001-07-03 2001-07-15 2001-07-10


SO43715 2001-07-05 2001-07-17 2001-07-12


SO43716 2001-07-05 2001-07-17 2001-07-12


SO43717 2001-07-05 2001-07-17 2001-07-12

767 25249 699.10 55.93 17.48 772.50 Road-650 Black, 62 Australia AU Pacific 9

SO43724 2001-07-07 2001-07-19 2001-07-14


SO43727 2001-07-08 2001-07-20 2001-07-15


SO43731 2001-07-09 2001-07-21 2001-07-16


SO43732 2001-07-09 2001-07-21 2001-07-16


SO43735 2001-07-10 2001-07-22 2001-07-17


SO43736 2001-07-10 2001-07-22 2001-07-17


SO43740 2001-07-11 2001-07-23 2001-07-18


SO43743 2001-07-12 2001-07-24 2001-07-19


SO43744 2001-07-12 2001-07-24 2001-07-19


SO43745 2001-07-13 2001-07-25 2001-07-20


SO43746 2001-07-13 2001-07-25 2001-07-20


Results Abridged

select top(5) * from ORDERS_RECORD

SalesOrderNumber

OrderDate DueDate ShipDate ProductID CustomerID SubTotal

TaxAmt Freight TotalDue ProductName TerritoryName

CountryRegionCode TerritoryGroup TerritoryID

SO43701 2001-07-01 2001-07-13 2001-07-08


SO43703 2001-07-02 2001-07-14 2001-07-09


SO43704 2001-07-02 2001-07-14 2001-07-09


SO43705 2001-07-02 2001-07-14 2001-07-09


SO43709 2001-07-03 2001-07-15 2001-07-10


Results Abridged

/*This table would be used to hold data relating to GB customers*/

Create table Territory_ReportGB(SalesOrderNumber NVARCHAR(25), ORDERDATE DATE, DUEDATE DATE, SHIPDATE DATE, PRODUCTID INTEGER , CUSTOMERID INTEGER , SUBTOTAL DECIMAL (35,2), TAXAMT DECIMAL(35,2), FREIGHT DECIMAL(35,2), TOTALDUE DECIMAL(35,2), PRODUCTNAME NAME, DESCRIPTION NVARCHAR(400), COLOR NVARCHAR(15) , SIZE NVARCHAR(15) , SizeUnitMeasureCode NCHAR(3), SubCatgoName NAME, ProductSubcategoryID INTEGER,

18

CategoryName NAME , ProductCategoryID INTEGER, TerritoryName NAME , CountryRegionCode NVARCHAR(3), TerritoryGroup NVARCHAR(50), TerritoryID INTEGER, REPORT_ID INTEGER IDENTITY , REPORT_DATE TIMESTAMP default TIMESTAMP );

ALTER TABLE Territory_ReportGB ADD CONSTRAINT TERRITRPORT_RPID_PK PRIMARY KEY(REPORT_ID); ALTER TABLE Territory_ReportGB add AccountNumber varchar(30);

/* A stored procedure will call this view to populate the report table for GB customers*/ create view SalesReportsASSELECT SOH.SalesOrderNumber, CU.AccountNumber, CAST (SOH.OrderDate AS DATE) AS OrderDate , CAST (SOH.DueDate AS DATE) AS DueDate, CAST (SOH.ShipDate AS DATE) AS ShipDate, PD.ProductID,SOH.CUSTOMERID AS CustomerID, cast (SOH.SUBTOTAL as Decimal (35,2))AS SubTotal, cast (SOH.TAXAMT as Decimal (35,2))AS TaxAmt, cast (SOH.FREIGHT as Decimal (35,2))AS Freight, cast (SOH.TOTALDUE as Decimal (35,2))AS TotalDue,PD.NAME AS PRODUCTNAME, PPD.Description,PD.COLOR,PD.Size, PD.SizeUnitMeasureCode,PSC.Name AS SubCatgoName,PSC.ProductSubcategoryID,PC.Name AS CategoryName, PC.ProductCategoryID, ST.NAME as TerritoryName,ST.CountryRegionCode ,ST."Group" AS TerritoryGroup, ST.TerritoryIDFROM Sales.SalesOrderDetail SODINNER JOIN Sales.SalesOrderHeader SOHON SOD.SalesOrderID = SOH.SalesOrderIDINNER JOIN Production.Product PDON SOD.PRODUCTID = PD.PRODUCTIDINNER JOIN SALES.SalesTerritory STON SOH.TerritoryID = ST.TerritoryIDINNER JOIN Production.ProductModel PMON PM.ProductModelID = PD.ProductModelIDINNER JOIN Production. ProductModelProductDescriptionCulture PMPDCON PM.ProductModelID = PMPDC.ProductModelIDINNER JOIN Production.ProductDescription PPD ON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureID INNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDINNER JOIN SALES.Customer CU ON SOH.CustomerID = CU.CustomerIDINNER JOIN PRODUCTION.ProductCategory PCON PSC.ProductCategoryID = PC.ProductCategoryID;

/* Report audit table that would be used to keep tracks of the records inserted, deleted and updated to the territory report GB table*/

CREATE TABLE REPORT_ADUIT(SalesOrderNumber NVARCHAR(25), ORDERDATE DATE, DUEDATE DATE, SHIPDATE DATE, PRODUCTID INTEGER , CUSTOMERID INTEGER , SUBTOTAL DECIMAL (35,2), TAXAMT DECIMAL(35,2), FREIGHT DECIMAL(35,2), TOTALDUE DECIMAL(35,2), PRODUCTNAME NAME, DESCRIPTION NVARCHAR(400), COLOR NVARCHAR(15) , SIZE NVARCHAR(15) ,

19

SizeUnitMeasureCode NCHAR(3), SubCatgoName NAME, ProductSubcategoryID INTEGER, CategoryName NAME , ProductCategoryID INTEGER, TerritoryName NAME , CountryRegionCode NVARCHAR(3), TerritoryGroup NVARCHAR(50), TerritoryID INTEGER, REPORT_ID INTEGER, ADUIT_ID INTEGER IDENTITY , ADUIT_DATE TIMESTAMP DEFAULT TIMESTAMP, REPORT_DATE TIMESTAMP );

ALTER TABLE REPORT_ADUIT ADD CONSTRAINT REPORTAD_ADID_PK PRIMARY KEY(ADUIT_ID);

ALTER TABLE REPORT_ADUIT add AccountNumber varchar(30);

/* This table would hold data associated with customers from different regions */

Create table Territory_ReportS(SalesOrderNumber NVARCHAR(25), ORDERDATE DATE, DUEDATE DATE, SHIPDATE DATE, PRODUCTID INTEGER , CUSTOMERID INTEGER , SUBTOTAL DECIMAL (35,2), TAXAMT DECIMAL(35,2), FREIGHT DECIMAL(35,2), TOTALDUE DECIMAL(35,2), PRODUCTNAME NAME, DESCRIPTION NVARCHAR(400), COLOR NVARCHAR(15) , SIZE NVARCHAR(15) , SizeUnitMeasureCode NCHAR(3), SubCatgoName NAME, ProductSubcategoryID INTEGER, CategoryName NAME , ProductCategoryID INTEGER, TerritoryName NAME , CountryRegionCode NVARCHAR(3), TerritoryGroup NVARCHAR(50), TerritoryID INTEGER, REPORT_ID INTEGER IDENTITY , REPORT_DATE TIMESTAMP default TIMESTAMP , AccountNumber varchar(30),CONSTRAINT REPORTID_PK PRIMARY KEY(REPORT_ID)); INSERT INTO "DBA"."Territory_ReportGB"(SalesOrderNumber,AccountNumber, ORDERDATE,DUEDATE,SHIPDATE, PRODUCTID, CUSTOMERID ,SUBTOTAL,TAXAMT,FREIGHT, TOTALDUE, PRODUCTNAME,"DESCRIPTION",COLOR,"SIZE",SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID)SELECT SalesOrderNumber, AccountNumber, OrderDate , DueDate, ShipDate, ProductID, CustomerID, SubTotal,TaxAmt, Freight, TotalDue,PRODUCTNAME, "Description","COLOR" ,"Size", SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName,CountryRegionCode , TerritoryGroup, TerritoryIDFROM SalesReportsWHERE CountryRegionCode ='GB' select * from REPORT_ADUIT

/*The null column has been removed to allow the report fit to the document*/

SalesOrderNu

mberORDERDA

TEDUEDAT

ESHIPDATE

PRODUCTID

CUSTOMERID

SUBTOTAL

TAXAMT

FREIGHT

TOTALDUE

PRODUCTNAME

DESCRIPTION

COLOR

SubCatgoName

ProductSubcategoryID

CategoryName

ProductCategoryID

TerritoryName

CountryRegionCode

TerritoryGroup

TerritoryID

REPORT_ID

ADUIT_ID

ADUIT_DATE

REPORT_DATE

SO57240 11/1/2003

11/13/2003

11/8/2003

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap

经典式样，带有翻边；均码。

Multi Caps 19 Clothing 3 United Kingdom

GB Europe 10 8 3 5/6/2013 0:02

5/6/2013 0:02

20

SO57240 11/1/2003

11/13/2003

11/8/2003

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap

סגנון עם מסורתי מצחייה

מתרוממת; גודל אחד

מתאיםלכולם.

Multi Caps 19 Clothing 3 United Kingdom

GB Europe 10 9 4 5/6/2013 0:02

5/6/2013 0:02

Results Abridged

/* A trigger will populate the territory reports for GB customer*/

INSERT INTO Territory_ReportS(SalesOrderNumber,AccountNumber, ORDERDATE,DUEDATE,SHIPDATE, PRODUCTID, CUSTOMERID ,SUBTOTAL,TAXAMT,FREIGHT, TOTALDUE, PRODUCTNAME,"DESCRIPTION",COLOR,"SIZE",SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID)SELECT SalesOrderNumber, AccountNumber, OrderDate , DueDate, ShipDate, ProductID, CustomerID, SubTotal,TaxAmt, Freight, TotalDue,PRODUCTNAME, "Description","COLOR" ,"Size", SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName,CountryRegionCode , TerritoryGroup, TerritoryIDFROM SalesReportsWHERE CountryRegionCode ='GB'

INSERT INTO Territory_ReportS(SalesOrderNumber,AccountNumber, ORDERDATE,DUEDATE,SHIPDATE, PRODUCTID, CUSTOMERID ,SUBTOTAL,TAXAMT,FREIGHT, TOTALDUE, PRODUCTNAME,"DESCRIPTION",COLOR,"SIZE",SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID)SELECT SalesOrderNumber, AccountNumber, OrderDate , DueDate, ShipDate, ProductID, CustomerID, SubTotal,TaxAmt, Freight, TotalDue,PRODUCTNAME, "Description","COLOR" ,"Size", SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName,CountryRegionCode , TerritoryGroup, TerritoryIDFROM SalesReportsWHERE CountryRegionCode ='US'

select TOP(2) * from Territory_ReportGB;

SalesOrderNumber

ORDERDATE

DUEDATE

SHIPDATE

PRODUCTID

CUSTOMERID

SUBTOTAL

TAXAMT

FREIGHT

TOTALDUE

PRODUCTNAME

DESCRIPTION

COLOR

SIZE

SizeUnitMeasureCode

SubCatgoName


CategoryName

ProductCategoryID

TerritoryName

CountryRegionCode

TerritoryGroup

TerritoryID

REPORT_ID

REPORT_DATE

AccountNumber

SO51411 2003-07-15

2003-07-27

2003-07-22

712 11239 2353.95

188.32

58.85 2601.11 AWC Logo Cap


Multi

Caps 19 Clothing 3 United Kingdom

GB Europe 10 88 2013-05-09 16:55:53.763

AW00011239

SO51411 2003-07-15

2003-07-27

2003-07-22

712 11239 2353.95

188.32

58.85 2601.11 AWC Logo Cap

סגנון מסורתי עם מצחייה מתרוממת;גודל אחד מתאים .לכולם

Multi


GB Europe 10 89 2013-05-09 16:55:53.764

AW00011239

select * from Territory_ReportGBWHERE CountryRegionCode = 'US'

SalesOrderNumber ORDERDATE DUEDATE SHIPDATE PRODUCTID CUSTOMERID SUBTOTAL TAXAMT FREIGHT TOTALDUE PRODUCTNAME DESCRIPTION COLOR SIZE SizeUnitMeasureCode SubCatgoName ProductSubcategoryID CategoryName ProductCategoryID TerritoryName CountryRegionCode TerritoryGroup TerritoryID REPORT_ID REPORT_DATE AccountNumber ------------------------- ---------- ---------- ---------- ----------- ----------- ------------------------------------- ------------------------------------- ------------------------------------- ------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- --------------- ------------------- -------------------------------------------------- -------------------- -------------------------------------------------- ----------------- -------------------------------------------------- ----------------- -------------------------------------------------- ----------- ----------- ----------------------- ------------------------------ (0 rows)Execution time: 0.208 seconds

select TOP(5) * from Territory_ReportSWHERE CountryRegionCode ='GB’

21

SalesOrderNumber

ORDERDATE

DUEDATE

SHIPDATE

PRODUCTID

CUSTOMERID

SUBTOTAL

TAXAMT

FREIGHT

TOTALDUE

PRODUCTNAME

DESCRIPTION

COLOR

SIZE

SizeUnitMeasureCode

SubCatgoName


CategoryName

ProductCategoryID

TerritoryName

CountryRegionCode

TerritoryGroup

TerritoryID

REPORT_ID

REPORT_DATE

AccountNumber

SO57240 2003-11-01

2003-11-13

2003-11-08

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap


Multi


GB Europe 10 4 2013-05-09 16:55:53.552

AW00011238

SO57240 2003-11-01

2003-11-13

2003-11-08

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap

סגנון מסורתי עם מצחייה מתרוממת;גודל אחד מתאים .לכולם

Multi


GB Europe 10 5 2013-05-09 16:55:53.656

AW00011238

SO57240 2003-11-01

2003-11-13

2003-11-08

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap

แบบดั้��งเดั้�ม พร้�อมขอบพ�บ ใช้�ไดั้�กั�บทุ�กัคน

Multi


GB Europe 10 6 2013-05-09 16:55:53.657

AW00011238

SO57240 2003-11-01

2003-11-13

2003-11-08

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap

Style classique avec une visière relevable ; taille unique.

Multi


GB Europe 10 7 2013-05-09 16:55:53.660

AW00011238

SO57240 2003-11-01

2003-11-13

2003-11-08

712 11238 562.96 45.04 14.07 622.07 AWC Logo Cap

نمط ذو تقليدي

حافة قابلة لالنقالب ألعلى، بحجم

يالئم واحد.الجميع

Multi


GB Europe 10 8 2013-05-09 16:55:53.661

AW00011238

select TOP(5) * from Territory_ReportSWHERE CountryRegionCode ='US'

alesOrderNumber

ORDERDATE

DUEDATE

SHIPDATE

PRODUCTID

CUSTOMERID

SUBTOTAL

TAXAMT

FREIGHT

TOTALDUE

PRODUCTNAME

DESCRIPTION COLOR

SIZE

SizeUnitMeasureCode

SubCatgoName


CategoryName

ProductCategoryID

TerritoryName

CountryRegionCode

TerritoryGroup

TerritoryID

REPORT_ID

REPORT_DATE

AccountNumber

SO43670 2001-07-01

2001-07-13

2001-07-08

773 29566 7344.50

587.56

183.61

8115.68

Mountain-100 Silver, 44

สุ�ดั้ ยอดั้จั�กัร้ยานภู�เขาสุ�าหร้�บกัาร้แข�งข�น สุมบ�ร้ณ์!แบบดั้�วยองค!ปร้ะกัอบสุ�าหร้�บปร้ะสุ�ทุธิ�ภูาพสุ�งสุ�ดั้ เช้�น เฟร้ม HL นว�ตกัร้ร้มใหม� ร้ะบบกั�นกัร้ะเทุ(อนดั้�านหน�าทุ)*น��มนวลเป,นพ�เศษ และยางทุ)*ย/ดั้เกัาะทุ�กัสุภูาพเสุ�นทุาง

Silver

44 CM Mountain Bikes

1 Bikes 1 Central US North America

3 62796 2013-05-09 17:06:14.134

AW00029566

SO43670 2001-07-01

2001-07-13

2001-07-08

773 29566 7344.50

587.56

183.61

8115.68


VTT de compétition haut de gamme. Plusieurs options d'amélioration des performances : cadre HL, suspension avant particulièrement souple et traction adaptée à tous les terrains.

Silver



3 62797 2013-05-09 17:06:14.134

AW00029566

SO43670 2001-07-01

2001-07-13

2001-07-08

773 29566 7344.50

587.56

183.61

8115.68


سباقات دراجةفي للقيادة مخصصةأعلى من الجبالتتضمن. طراز

األداء تحسين خيارات HL Frame هيكلوالتعليق اإلبداعي،

السالسة ذي األماميالجر وقوة الفائقة،

أنواع لجميع المناسبة.األراضي

Silver



3 62798 2013-05-09 17:06:14.134

AW00029566

SO43670 2001-07-01

2001-07-13

2001-07-08

773 29566 7344.50

587.56

183.61

8115.68


Top-of-the-line competition mountain bike. Performance-enhancing options

Silver



3 62799 2013-05-09 17:06:14.134

AW00029566

22

include the innovative HL Frame, super-smooth front suspension, and traction for all terrain.

SO43670 2001-07-01

2001-07-13

2001-07-08

776 29566 7344.50

587.56

183.61

8115.68

Mountain-100 Black, 42

高档竞赛山地车。性能得到进一步增强，包括创新的 HL 车架、极其平稳的前悬架以及适用于所有地形的出色牵引力。

Black


1

CREATE or replace VIEW PRODUCTHASSELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PPD.Description,PSC.Name AS SubCatgo_Name, PSC.ProductSubcategoryID AS SubCatgo_ID,PPI.Quantity,PPI.Shelf, PPI.LocationID, CAST(PPI.ModifiedDate AS DATE)AS ModifiedDate_SubCatgo,PPI.Bin ,CUL.Name AS Culture_Name,PCI.Name AS Catego_Name, PCI.ProductCategoryID , cast( PCI.ModifiedDate as date)AS ModifiedDate_CategoFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDINNER JOIN PRODUCTION.ProductInventory PPION PD.ProductID = PPI.ProductIDINNER JOIN PRODUCTION.ProductCategory PCION PSC.ProductCategoryID = PCI.ProductCategoryID

CREATE TABLE PRODHISTORY (Product_ID INTEGER, Product_Name NAME, Product_Color Nvarchar(15), Product_Size Nvarchar(5), SizeUnitMeasureCode nchar(3), Product_Description nvarchar(400), SubCatgo_Name name, Quantity char(8), Shelf nvarchar(10), LocationID char(8), ModifiedDate_SubCatgo Timestamp, P_Bin CHAR(8), Culture_Name NAME, History_Date Timestamp Default Timestamp , History_ID INTEGER IDENTITY , Category_Name Name, ModifiedDate_Catgo Timestamp, SubCatgo_ID integer, Prod_CatgoID INTEGER, constraint PHISTORY_PRODID2_PK PRIMARY KEY (HISTORY_ID) )

/*This stored procedure will populate the above table*/

CREATE OR REPLACE PROCEDURE ProdHistory( in @ProductID INTEGER )

BEGIN

IF @ProductID IS NOT NULL

23

THEN INSERT INTO PRODHISTORY (Product_ID, Product_Name ,Product_Color , Product_Size , SizeUnitMeasureCode , Product_Description, SubCatgo_Name, Quantity ,Shelf ,LocationID , ModifiedDate_SubCatgo, P_Bin , Culture_Name, Category_Name, ModifiedDate_Catgo, SubCatgo_ID,Prod_CatgoID) SELECT ProductID, "Name" ,Color , "Size" , SizeUnitMeasureCode , "Description", SubCatgo_Name,Quantity, Shelf, LocationID, ModifiedDate_SubCatgo, Bin ,Culture_Name,Catego_Name, ModifiedDate_Catego,SubCatgo_ID, ProductCategoryID FROM PRODUCTH WHERE ProductID = @ProductID ; END IF ; End

SELECT * FROM PRODHISTORY

Product_ID

Product_Name

Product_Color

Product_Size

SizeUnitMeasureCode

Product_Description SubCatgo_Name

Quantity

Shelf

LocationID

ModifiedDate_SubCatgo

P_Bin

Culture_Name

History_Date

History_ID

Category_Name

ModifiedDate_Catgo

SubCatgo_ID

Prod_CatgoID

865 Classic Vest, M

Blue M Light-weight, wind-resistant, packs to fit into a pocket.

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 English 2013-05-09 21:06:18.654

1 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M الوزن، خفيفة علبتناسب للريح، ومقاومة

الجيب .حجم

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Arabic 2013-05-09 21:06:18.655

2 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M Sacs légers et résistants au vent ; tiennent dans la poche.

V ests 216 N/A 7 2003-06-01 00:00:00.000

0 French 2013-05-09 21:06:18.655

3 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M น��าหน�กัเบา กั�นลม ขนาดั้กัะทุ�ดั้ร้�ดั้พอดั้)กัร้ะเป0า

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Thai 2013-05-09 21:06:18.655

4 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M קל-משקל, מגן מרוח, מתקפל לגודל המתאים .לכיס

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Hebrew 2013-05-09 21:06:18.655

5 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M 轻型抗风，可折叠放入口袋。

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Chinese 2013-05-09 21:06:18.656

6 Clothing 1998-06-01 00:00:00.000

25 3

Triggers

CREATE OR REPLACE TRIGGER T_Reports BEFORE INSERT ON Territory_ReportSREFERENCING NEW AS NFOR EACH ROW BEGIN

SET self_recursion OFF; CASE WHEN N.CountryRegionCode ='GB'

THEN INSERT INTO Territory_ReportGB

(SalesOrderNumber,AccountNumber, ORDERDATE,DUEDATE,SHIPDATE, PRODUCTID, CUSTOMERID ,SUBTOTAL,TAXAMT,FREIGHT, TOTALDUE, PRODUCTNAME,DESCRIPTION,COLOR,SIZE,SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID,REPORT_ID, REPORT_DATE

)VALUES(N.SalesOrderNumber,N.AccountNumber,N.ORDERDATE,N.DUEDATE,N.SHIPDATE, N.PRODUCTID,

N.CUSTOMERID ,N.SUBTOTAL,N.TAXAMT,N.FREIGHT, N.TOTALDUE, N.PRODUCTNAME,N.DESCRIPTION,N.COLOR,N.SIZE,N.SizeUnitMeasureCode,N.SubCatgoName, N.ProductSubcategoryID, N.CategoryName, N.ProductCategoryID, N.TerritoryName, N.CountryRegionCode,N.TerritoryGroup,N.TerritoryID,N.REPORT_ID, N.REPORT_DATE);

END CASE END

CREATE OR REPLACE TRIGGER TERRITORYRPT_GB AFTER INSERT ON Territory_ReportGBREFERENCING NEW AS NFOR EACH ROW BEGIN

SET self_recursion OFF; INSERT INTO REPORT_ADUIT

(SalesOrderNumber,AccountNumber, ORDERDATE,DUEDATE,SHIPDATE, PRODUCTID, CUSTOMERID ,SUBTOTAL,TAXAMT,FREIGHT, TOTALDUE,

24

PRODUCTNAME,DESCRIPTION,COLOR,SIZE,SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID, CategoryName, ProductCategoryID, TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID,REPORT_ID, REPORT_DATE

)VALUES(N.SalesOrderNumber,N.AccountNumber,N.ORDERDATE,N.DUEDATE,N.SHIPDATE, N.PRODUCTID,

N.CUSTOMERID ,N.SUBTOTAL,N.TAXAMT,N.FREIGHT, N.TOTALDUE, N.PRODUCTNAME,N.DESCRIPTION,N.COLOR,N.SIZE,N.SizeUnitMeasureCode,N.SubCatgoName, N.ProductSubcategoryID, N.CategoryName, N.ProductCategoryID, N.TerritoryName, N.CountryRegionCode,N.TerritoryGroup,N.TerritoryID,N.REPORT_ID, N.REPORT_DATE); END

XML

SELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PD.ProductID = 713FOR XML AUTO

forxml(2,'PD',forxml_sep(),'ProductID',xmltab."1",'NAME',xmltab."2",'COLOR',xmltab."3",'Size',xmltab."4",'SizeUnitMeasureCode',xmltab."5",'PSC',forxml_sep(),'SubCatgo_Name',xmltab."6") ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <PD ProductID="713" NAME="Long-Sleeve Logo Jersey, S" COLOR="Multi" Size="S"><PSC SubCatgo_Name="Jerseys"/></PD> (1 rows)

Execution time: 0.125 seconds

SELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PD.ProductID = 713FOR XML RAW

forxml(1,'ProductID',xmltab."1",'NAME',xmltab."2",'COLOR',xmltab."3",'Size',xmltab."4",'SizeUnitMeasureCode',xmltab."5",'SubCatgo_Name',xmltab."6") ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <row ProductID="713" NAME="Long-Sleeve Logo Jersey, S" COLOR="Multi" Size="S" SubCatgo_Name="Jerseys"/> (1 rows)Execution time: 0.044 seconds

SELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN

25

PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PD.ProductID = 713FOR XML RAW, ELEMENTS

forxml(33,'ProductID',xmltab."1",'NAME',xmltab."2",'COLOR',xmltab."3",'Size',xmltab."4",'SizeUnitMeasureCode',xmltab."5",'SubCatgo_Name',xmltab."6") ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <row><ProductID>713</ProductID><NAME>Long-Sleeve Logo Jersey, S</NAME><COLOR>Multi</COLOR><Size>S</Size><SubCatgo_Name>Jerseys</SubCatgo_Name></row> (1 rows)Execution time: 0.065 seconds

SELECT ProductID, xmlelement(NAME DESCRIPTION,xmlforest( PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PSC.Name AS SubCatgo_Name ) ) AS Prod_Info

FROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PD.ProductID = 713FOR XML RAW, ELEMENTS

forxml(1,'ProductID',xmltab."1",'Prod_Info',xmltab."2") ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <row ProductID="713" Prod_Info="<DESCRIPTION><ProductID>713</ProductID><NAME>Long-Sleeve Logo Jersey, S</NAME><COLOR>Multi</COLOR><Size>S</Size><SubCatgo_Name>Jerseys</SubCatgo_Name></DES (1 rows)Execution time: 0.029 seconds

SELECT ProductID, xmlelement(NAME DESCRIPTION,xmlforest( PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PSC.Name AS SubCatgo_Name ) ) AS Prod_Info

FROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PD.ProductID = 713

ProductID Prod_Info ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 713 <DESCRIPTION><ProductID>713</ProductID><NAME>Long-Sleeve Logo Jersey, S</NAME><COLOR>Multi</COLOR><Size>S</Size><SubCatgo_Name>Jerseys</SubCatgo_Name></DESCRIPTION> (1 rows)Execution time: 0.036 seconds

26

CursorsCREATE VIEW EMOINFOASSELECT HRE.BusinessEntityID,PER.FirstName, PER.MiddleName , PER.LastName, HRE.BirthDate, HRE.Gender, HRE.MaritalStatus, HRE.NationalIDNumber, HRE.SickLeaveHours, HRE.VacationHours,DATEDIFF(YY, BirthDate , CURRENT_TIMESTAMP) AS AGEFROM HumanResources.Employee HRE INNER JOIN PERSON.Person PERON HRE.BusinessEntityID = PER.BusinessEntityID

BEGIN

DECLARE @BusinessEntityID INTEGER; DECLARE @EMP_NMAME VARCHAR(55); DECLARE @BDATE DATE; DECLARE @GENDER CHAR(2); DECLARE @MSTATUS CHAR(8); DECLARE @NATIONALID INTEGER; DECLARE @SICKH INTEGER; DECLARE @VHOURS INTEGER; DECLARE @EMPAGE INTEGER; DECLARE @EMPRED CURSOR FOR SELECT BusinessEntityID,FirstName||','|| MiddleName||','||LastName as Emp_Name, BirthDate, Gender, MaritalStatus, NationalIDNumber, SickLeaveHours, VacationHours,AGEFROM EMOINFO where BusinessEntityID = 19;OPEN @EMPRED; fetch next @EMPRED into @BusinessEntityID, @EMP_NMAME, @BDATE, @GENDER,@MSTATUS, @NATIONALID, @SICKH,@VHOURS, @EMPAGE;select @BusinessEntityID , @EMP_NMAME , @BDATE , @GENDER ,@MSTATUS , @NATIONALID , @SICKH ,@VHOURS, @EMPAGE ;CLOSE @EMPRED;END;

@BusinessEntityID @EMP_NMAME @BDATE @GENDER @MSTATUS @NATIONALID @SICKH @VHOURS @EMPAGE ----------------- ------------------------------------------------------- ---------- ------- -------- ----------- ----------- ----------- ----------- 19 Mary,A,Dempsey 1968-03-01 F S 52541318 41 43 45 Execution time: 0.047 seconds

27

DBA

Copying data from Sybase SQL AnyWhere AdventureWorks Database to Sybase SQL AnyWhere Rock Database using Sybase Central

29

SELECT * FROM "SUNNY"."PRODHISTORY"

Product_ID

Product_Name

Product_Color

Product_Size

SizeUnitMeasureCode

Product_Description

SubCatgo_Name

Quantity

Shelf

LocationID

ModifiedDate_SubCatgo

P_Bin

Culture_Name

History_Date

History_ID

Category_Name

ModifiedDate_Catgo

SubCatgo_ID

Prod_CatgoID

865 Classic Vest, M

Blue M Light-weight, wind-resistant, packs to fit into a pocket.

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 English 2013-05-09 21:06:18.654

1 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M الوزن، خفيفة علبللريح، ومقاومةحجم تناسب.الجيب

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Arabic 2013-05-09 21:06:18.655

2 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M Sacs légers et résistants au vent ; tiennent dans la poche.

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 French 2013-05-09 21:06:18.655

3 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M น��าหน�กัเบา กั�นลม ขนาดั้กัะทุ�ดั้ร้�ดั้พอดั้)กัร้ะเป0า

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Thai 2013-05-09 21:06:18.655

4 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M קל-משקל, מגן מרוח,מתקפל לגודל .המתאים לכיס

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Hebrew 2013-05-09 21:06:18.655

5 Clothing 1998-06-01 00:00:00.000

25 3

865 Classic Vest, M

Blue M 轻型抗风，可折叠放入口袋。

Vests 216 N/A 7 2003-06-01 00:00:00.000

0 Chinese 2013-05-09 21:06:18.656

6 Clothing 1998-06-01 00:00:00.000

25 3

select * from sysprocedure where PROC_NAME ='ProdHistory' OR PROC_NAME ='PRODUCTS'OR PROC_NAME ='PRODUCTS2'OR PROC_NAME ='SALES_TERRITORY_GB' OR PROC_NAME = 'SalesTerritory_Report'

proc_id

creator

object_id

proc_name proc_defn remarks

replicate

srvid

source avg_num_rows

avg_cost

stats

353 1 4728 ProdHistory create procedure DBA.ProdHistory( in @ProductID integer ) begin if @ProductID is not null then insert into PRODHISTORY( Product_ID,Product_Name,Product_Color,Product_Size,SizeUnitMeasureCode,Product_Description, SubCatgo_Name,Quantity,Shelf,LocationID,ModifiedDate_SubCatgo,P_Bin,Culture_Name,Category_Name, ModifiedDate_Catgo,SubCatgo_ID,Prod_CatgoID ) select ProductID,Name,Color,Size,SizeUnitMeasureCode,Description, SubCatgo_Name,Quantity,Shelf,LocationID,ModifiedDate_SubCatgo, Bin,Culture_Name,Catego_Name,ModifiedDate_Catego,SubCatgo_ID, ProductCategoryID from PRODUCTH where ProductID = @ProductID end ifend

N create PROCEDURE DBA."ProdHistory"( in @ProductID INTEGER )

BEGIN

IF @ProductID IS NOT NULL

THEN INSERT INTO PRODHISTORY (Product_ID, Product_Name ,Product_Color , Product_Size , SizeUnitMeasureCode , Product_Description, SubCatgo_Name, Quantity ,Shelf ,LocationID , ModifiedDate_SubCatgo, P_Bin , Culture_Name, Category_Name, ModifiedDate_Catgo, SubCatgo_ID,Prod_CatgoID) SELECT ProductID, "Name" ,Color , "Size" , SizeUnitMeasureCode , "Description", SubCatgo_Name,Quantity, Shelf, LocationID, ModifiedDate_SubCatgo, Bin ,Culture_Name,Catego_Name, ModifiedDate_Catego,SubCatgo_ID, ProductCategoryID FROM PRODUCTH WHERE ProductID = @ProductID ; END IF ;

End

Binary Data

347 1 4384 PRODUCTS create procedure DBA.PRODUCTSasselect PD.ProductID,PD.NAME,PD.COLOR,PD.Size,PD.SizeUnitMeasureCode, PPD.Description,SubCatgo_Name=PSC.Name from Production.ProductDescription as PPD join Production.ProductModelProductDescriptionCulture as PMPDC on PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONID join Production.ProductModel as PM on PMPDC.ProductModelID = PM.ProductModelID join PRODUCTION.Product as PD on PD.ProductModelID = PM.ProductModelID join PRODUCTION.Culture as CUL

N create PROCEDURE PRODUCTS()ASSELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PPD.Description,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelID

Binary Data

30

on PMPDC.CultureID = CUL.CultureID join PRODUCTION.ProductSubcategory as PSC on PSC.ProductSubcategoryID = PD.ProductSubcategoryID where CUL.NAME = 'ENGLISH'

INNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'

348 1 4385 PRODUCTS2 create procedure DBA.PRODUCTS2( @sub_catgo_name char(35) ) asselect PD.ProductID,PD.NAME,PD.COLOR,PD.Size,PD.SizeUnitMeasureCode, PPD.Description,SubCatgo_Name=PSC.Name from Production.ProductDescription as PPD join Production.ProductModelProductDescriptionCulture as PMPDC on PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONID join Production.ProductModel as PM on PMPDC.ProductModelID = PM.ProductModelID join PRODUCTION.Product as PD on PD.ProductModelID = PM.ProductModelID join PRODUCTION.Culture as CUL on PMPDC.CultureID = CUL.CultureID join PRODUCTION.ProductSubcategory as PSC on PSC.ProductSubcategoryID = PD.ProductSubcategoryID where CUL.NAME = 'ENGLISH' and PSC.NAME = @sub_catgo_name

N create PROCEDURE PRODUCTS2 (@sub_catgo_name char(35)

)ASSELECT PD.ProductID, PD.NAME, PD.COLOR , PD.Size, PD.SizeUnitMeasureCode,PPD.Description,PSC.Name AS SubCatgo_NameFROM Production.ProductDescription PPDInner join Production. ProductModelProductDescriptionCulture PMPDCON PPD.PRODUCTDESCRIPTIONID = PMPDC.PRODUCTDESCRIPTIONIDInner Join Production.ProductModel PMON PMPDC.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Product PDON PD.ProductModelID = PM.ProductModelIDINNER JOIN PRODUCTION.Culture CULON PMPDC.CultureID = CUL.CultureIDINNER JOINPRODUCTION.ProductSubcategory PSC ON PSC.ProductSubcategoryID = PD.ProductSubcategoryIDWHERE CUL.NAME = 'ENGLISH'AND PSC.NAME = @sub_catgo_name

Binary Data

352 1 4659 SALES_TERRITORY_GB

create procedure DBA.SALES_TERRITORY_GB( in @COUNTRYCODE char(8), out SalesOrderNumber nvarchar(25), out AccountNumber varchar(10), out OrderDate date, out DueDate date, out ShipDate date, out ProductID integer, out CustomerID integer, out SubTotal decimal(35,2), out TaxAmt decimal(35,2), out Freight decimal(35,2), out TotalDue decimal(32,2), out PRODUCTNAME NAME, out Description nvarchar(400), out COLOR nvarchar(15), out Size nvarchar(5), out SizeUnitMeasureCode nvarchar(3), out SubCatgoName NAME, out ProductSubcategoryID integer, out CategoryName NAME, out ProductCategoryID integer, out TerritoryName NAME, out CountryRegionCode nvarchar(3), out TerritoryGroup nvarchar(50), out TerritoryID integer ) --on exception resume--begin case when @COUNTRYCODE = 'GB' then select SalesOrderNumber,AccountNumber,OrderDate,DueDate,ShipDate,ProductID, CustomerID,SubTotal,TaxAmt,Freight,TotalDue, PRODUCTNAME,Description,COLOR,Size,SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID,CategoryName,ProductCategoryID, TerritoryName,CountryRegionCode,TerritoryGroup,TerritoryID from SalesReports where CountryRegionCode = @COUNTRYCODE end caseend

N create PROCEDURE SALES_TERRITORY_GB( in @COUNTRYCODE char(8), OUT SalesOrderNumber NVARCHAR(25), OUT AccountNumber VARCHAR (10), OUT OrderDate DATE , OUT DueDate DATE, OUT ShipDate DATE, OUT ProductID INTEGER, OUT CustomerID INTEGER, OUT SubTotal DECIMAL(35,2), OUT TaxAmt DECIMAL (35,2), OUT Freight DECIMAL(35,2), OUT TotalDue DECIMAL(32,2), OUT PRODUCTNAME NAME, OUT "Description" NVARCHAR(400), OUT COLOR NVARCHAR (15), OUT "Size" NVARCHAR(5), OUT SizeUnitMeasureCode NVARCHAR (3), OUT SubCatgoName NAME, OUT ProductSubcategoryID INTEGER, OUT CategoryName NAME,OUT ProductCategoryID INTEGER , OUT TerritoryName NAME, OUT CountryRegionCode NVARCHAR (3),OUT TerritoryGroup NVARCHAR (50),OUT TerritoryID INTEGER )

--on exception resume--begin

CASE WHEN @COUNTRYCODE = 'GB' THEN select SalesOrderNumber,AccountNumber,OrderDate,DueDate,ShipDate,ProductID, CustomerID,SubTotal,TaxAmt,Freight,TotalDue, PRODUCTNAME,Description,COLOR,Size,SizeUnitMeasureCode,SubCatgoName,ProductSubcategoryID,CategoryName,ProductCategoryID, TerritoryName,CountryRegionCode,TerritoryGroup,TerritoryID from SalesReports where CountryRegionCode = @COUNTRYCODE END CASE;end

Binary Data

346 1 4383 SalesTerritory_Report

create procedure DBA.SalesTerritory_Report( @Region_Code char(8) ) asselect SOH.SalesOrderNumber,OrderDate=convert(date,SOH.OrderDate),DueDate=convert(date,SOH.DueDate),ShipDate=conve

N create PROCEDURE SalesTerritory_Report (@Region_Code Char(8))ASSELECT SOH.SalesOrderNumber, CAST (SOH.OrderDate AS DATE) AS OrderDate , CAST (SOH.DueDate AS DATE) AS DueDate, CAST (SOH.ShipDate AS DATE)

Binary Data

31

rt(date,SOH.ShipDate),SOD.ProductID, CustomerID=SOH.CUSTOMERID,SubTotal=convert(decimal(35,2),SOH.SUBTOTAL), TaxAmt=convert(decimal(35,2),SOH.TAXAMT),Freight=convert(decimal(35,2),SOH.FREIGHT),TotalDue=convert(decimal(35,2),SOH.TOTALDUE),ProductName=PD.Name,TerritoryName=ST.NAME, ST.CountryRegionCode, TerritoryGroup=ST."Group",ST.TerritoryID from Sales.SalesOrderDetail as SOD join Sales.SalesOrderHeader as SOH on SOD.SalesOrderID = SOH.SalesOrderID join Production.Product as PD on SOD.PRODUCTID = PD.PRODUCTID join SALES.SalesTerritory as ST on SOH.TerritoryID = ST.TerritoryID where CountryRegionCode = @Region_Code order by PD.NAME asc

AS ShipDate, SOD.ProductID,SOH.CUSTOMERID AS CustomerID, cast (SOH.SUBTOTAL as Decimal (35,2))AS SubTotal,cast (SOH.TAXAMT as Decimal (35,2))AS TaxAmt, cast (SOH.FREIGHT as Decimal (35,2))AS Freight, cast (SOH.TOTALDUE as Decimal (35,2))AS TotalDue, PD.Name AS ProductName, ST.NAME as TerritoryName,ST.CountryRegionCode,ST."Group" AS TerritoryGroup, ST.TerritoryIDFROM Sales.SalesOrderDetail SODINNER JOIN Sales.SalesOrderHeader SOHON SOD.SalesOrderID = SOH.SalesOrderIDINNER JOIN Production.Product PDON SOD.PRODUCTID = PD.PRODUCTIDINNER JOIN SALES.SalesTerritory STON SOH.TerritoryID = ST.TerritoryIDwhere CountryRegionCode = @Region_CodeORDER BY PD.NAME

select * from sysTRIGGERSWHERE TRIGNAME = 'TERRITORYRPT_GB' OR TRIGNAME ='T_Reports'

owner trigname tname event trigtime trigdefnDBA T_Reports Territory_ReportS INSERT BEFORE create trigger T_Reports before insert on

DBA.Territory_ReportSreferencing new as Nfor each rowbegin set self_recursion off; case when N.CountryRegionCode = 'GB' then insert into Territory_ReportGB ( SalesOrderNumber,AccountNumber,ORDERDATE,DUEDATE,SHIPDATE,PRODUCTID, CUSTOMERID,SUBTOTAL,TAXAMT,FREIGHT,TOTALDUE, PRODUCTNAME,DESCRIPTION,COLOR,SIZE,SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID,CategoryName,ProductCategoryID,TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID,REPORT_ID, REPORT_DATE ) values ( N.SalesOrderNumber,N.AccountNumber,N.ORDERDATE,N.DUEDATE,N.SHIPDATE,N.PRODUCTID, N.CUSTOMERID,N.SUBTOTAL,N.TAXAMT,N.FREIGHT,N.TOTALDUE, N.PRODUCTNAME,N.DESCRIPTION,N.COLOR,N.SIZE,N.SizeUnitMeasureCode,N.SubCatgoName, N.ProductSubcategoryID,N.CategoryName,N.ProductCategoryID,N.TerritoryName, N.CountryRegionCode,N.TerritoryGroup,N.TerritoryID,N.REPORT_ID, N.REPORT_DATE ) end caseend

DBA TERRITORYRPT_GB Territory_ReportGB INSERT AFTER create trigger TERRITORYRPT_GB after insert onDBA.Territory_ReportGBreferencing new as Nfor each rowbegin set self_recursion off; insert into REPORT_ADUIT ( SalesOrderNumber,AccountNumber,ORDERDATE,DUEDATE,SHIPDATE,PRODUCTID, CUSTOMERID,SUBTOTAL,TAXAMT,FREIGHT,TOTALDUE, PRODUCTNAME,DESCRIPTION,COLOR,SIZE,SizeUnitMeasureCode,SubCatgoName, ProductSubcategoryID,CategoryName,ProductCategoryID,TerritoryName, CountryRegionCode,TerritoryGroup,TerritoryID,REPORT_ID, REPORT_DATE ) values ( N.SalesOrderNumber,N.AccountNumber,N.ORDERDATE,N.DUEDATE,N.SHIPDATE,N.PRODUCTID, N.CUSTOMERID,N.SUBTOTAL,N.TAXAMT,N.FREIGHT,N.TOTALDUE, N.PRODUCTNAME,N.DESCRIPTION,N.COLOR,N.SIZE,N.SizeUnitMeasureCode,N.SubCatgoName, N.ProductSubcategoryID,N.CategoryName,N.ProductCategoryID,N.TerritoryName, N.CountryRegionCode,N.TerritoryGroup,N.TerritoryID,N.REPORT_ID, N.REPORT_DATE ) end

32

select * from sysprocedure where PROC_NAME ='ProdHistory' OR PROC_NAME ='eMPaGE'

proc_id

creator object_id

proc_name proc_defn remarks

replicate srvid source avg_num_rows avg_cost

stats

351 1 4388 EMPAGE create function DBA.EMPAGE( in @SNN integer ) returns integerbegin declare @EMPAGE1 integer; select DATEDIFF(yy,HRE.BirthDate,current timestamp) as EAGE into @EMPAGE1 from HumanResources.Employee as HRE where HRE.NationalIDNumber = @SNN; return(@EMPAGE1)end

N create FUNCTION EMPAGE( in @SNN integer)RETURNS INTEGER

BEGIN DECLARE @EMPAGE1 integer;

SELECT DATEDIFF(YY, HRE.BirthDate , CURRENT_TIMESTAMP) AS EAGEINTO @EMPAGE1FROM HumanResources.Employee HRE WHERE HRE.NationalIDNumber = @SNN;

RETURN (@EMPAGE1);END

Binary Data

Remote Server

CREATE SERVER "MYSQL4" CLASS 'MYSQLODBC' USING 'Mysql-Sakila';CREATE EXTERNLOGIN "DBA" TO "MYSQL4" REMOTE LOGIN 'root' IDENTIFIED BY '***';

CREATE SERVER "DB2" CLASS 'DB2ODBC' USING 'GSDB';CREATE EXTERNLOGIN "DBA" TO "DB2" REMOTE LOGIN 'GOC-PC' IDENTIFIED BY '***';

A proxy table is needed to access the data in a remote server.

33

SELECT * FROM "DBA"."customer"

customer_id

store_id first_name last_name email address_id active

create_date last_update

1 1 MARY SMITH [email protected] 5 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.0002 1 PATRICIA JOHNSON [email protected] 6 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.0003 1 LINDA WILLIAMS [email protected] 7 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.0004 2 BARBARA JONES [email protected] 8 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.0005 1 ELIZABETH BROWN [email protected]

g9 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.000

6 2 JENNIFER DAVIS [email protected] 10 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.0007 1 MARIA MILLER [email protected] 11 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.0008 2 SUSAN WILSON [email protected] 12 1 2006-02-14 22:04:36.000 2006-02-15 04:57:20.000

sybase sql anywhere12

Technology

database diagramcaptured

sybase infomaker

microsoft excel

bysunny okoro

user defined function

contentsdatabase platform