oracle SYS_EXTRACT_UTC(SYSTIMESTAMP), mssql GETUTCDATE(), mysql CURRENT_TIMESTAMP

inORACLE

DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (  
"ID" VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL,
"NAME" NVARCHAR2(150),
"RCDATE" TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

insert into "BOROO"."CMS_TEMP" (NAME)
values ('new name 1');

select TO_CHAR(RCDATE, 'YYYY-MM-DD HH24:MI:SS.FF') from CMS_TEMP;


inMSSQL


DROP TABLE [dbo].[table1]
GO
/****** Object:  Table [dbo].[table1]    Script Date: 3/21/2017 5:50:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
insert into table1(name)
values ('name 1');
GO
select rcdate from table1;


inMySQL

CREATE TABLE `table1` (
  `id` char(36) DEFAULT NULL,
  `name` varchar(250) NOT NULL,
  `rcdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

oracle sys_guid(), mssql newid(), mysql trigger all for default uniqueidentifier ID

inORACLE

DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (   
"ID" VARCHAR2(36) NOT NULL,
"NAME" NVARCHAR2(150),
"RCTIME" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

create or replace FUNCTION NEWID RETURN VARCHAR2 IS guid VARCHAR2(36);
BEGIN
    SELECT SYS_GUID() INTO guid FROM DUAL;
guid := regexp_replace(rawtohex(sys_guid())
       , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
       , '\1-\2-\3-\4-\5');
--OR
guid := SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;

create or replace TRIGGER SetGUIDforCMS_TEMP BEFORE INSERT ON CMS_TEMP
FOR EACH ROW
BEGIN
    :new.ID := NEWID();
END;

insert into "CMS_TEMP" (NAME)
values ('new name 1');

select * from cms_temp;

DECLARE
  v_Return VARCHAR2(36);
BEGIN
  v_Return := NEWID;
  DBMS_OUTPUT.PUT_LINE(v_Return);
END;

select NEWID from dual;

inMSSQL

Data Type: uniqueidentifier
Default Value or Binding: (newid())

CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO

inMYSQL

CREATE TRIGGER `before_insert_table1` BEFORE INSERT ON `table1`
FOR EACH ROW BEGIN
    SET new.id = UPPER(uuid());
END

maybe backup entity framework migration history manual

SELECT * into [backup_db].[dbo].[migrations]
  FROM original_db.dbo.__MigrationHistory

after error

  insert into original.dbo.__MigrationHistory
  select * from [backup_db].[dbo].[migrations]

mssql try catch with transaction commit or rollback using XACT_STATE

USE AdventureWorks2008R2;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_books', N'U') IS NOT NULL
    DROP TABLE my_books;
GO

-- Create table my_books.
CREATE TABLE my_books
    (
    Isbn        int PRIMARY KEY,
    Title       NVARCHAR(100)
    );
GO

BEGIN TRY
    BEGIN TRANSACTION;
        -- This statement will generate an error because the 
        -- column author does not exist in the table.
        ALTER TABLE my_books
            DROP COLUMN author;
    -- If the DDL statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.
    -- XACT_STATE = 0 means there is no transaction and
    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. ' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO

mssql select data insert into memory temporary table

declare @temp table(ProductID int, SalesOrderID int, SalesOrderDetailID int, OrderQty smallint,    
                    primary key clustered (SalesOrderID,SalesOrderDetailID),
                    unique nonclustered (ProductID,SalesOrderID,SalesOrderDetailID))
insert into @temp (ProductID, SalesOrderID, SalesOrderDetailID, OrderQty)
select ProductID, SalesOrderID, SalesOrderDetailID, OrderQty
from Sales.SalesOrderDetail
select temp.SalesOrderID, temp.SalesOrderDetailID, temp.ProductID, temp.OrderQty
from @temp as temp
where temp.SalesOrderID = 43661
go

--------------------------------------------------------------------------------

--set nocount off
--set nocount on
drop table #tmp
SELECT * into #tmp from dbo.suragch
insert #tmp SELECT name, age from dbo.suragch where name like N'%жар%'
insert #tmp SELECT name, age from dbo.suragch where name like N'%сүх%'
SELECT * from #tmp order by id asc
print @@IDENTITY

CREATE PROCEDURE for insert for c# nonexecutequery

CREATE PROCEDURE sp_insertemployee 
        @FirstName nvarchar(10),
        @LastName nvarchar(20),
        @Title nvarchar(30),
        @Notes nvarchar(200),
        @PK_New int OUTPUT
      AS
        INSERT INTO Employees(FirstName,LastName,Title,Notes) 

VALUES (@FirstName,@LastName,@Title,@Notes)

        SELECT @PK_New = @@IDENTITY
        RETURN (1)    
      GO

---------------------------------------------------------------

IF ( OBJECT_ID('dbo.sp_Students_INS_byPK') IS NOT NULL ) 
   DROP PROCEDURE dbo.sp_Students_INS_byPK
GO

CREATE PROCEDURE dbo.sp_Students_INS_byPK
       @student_id                     INT                      , 
       @password                       VARCHAR(15)      = NULL  , 
       @active_flg                     TINYINT                  , 
       @lastname                       VARCHAR(30)      = NULL  , 
       @birth_dttm                     DATETIME         = NULL  , 
       @gpa                            INT              = NULL  , 
       @is_on_staff                    TINYINT                   
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.Students
          ( 
            student_id                   ,
            password                     ,
            active_flg                   ,
            lastname                     ,
            birth_dttm                   ,
            gpa                          ,
            is_on_staff                  
          ) 
     VALUES 
          ( 
            @student_id                   ,
            @password                     ,
            @active_flg                   ,
            @lastname                     ,
            @birth_dttm                   ,
            @gpa                          ,
            @is_on_staff                  
          ) 

END 

GO

----------------------------------------------------------------------

EXECUTE [dbo].[spINSERT_dbo_Customer] 
   @FirstName = 'Tommy'
  ,@LastName = 'Crabber'
  ,@PhoneNumber = '333-333-3333'
  ,@EmailAddress = 'tommy@KingCrabber.com'
  ,@Priority = 1
  ,@CreateDate = '2011-09-15'
GO

mysql select field as json text using concat, group_concat, distinct

select a.id, a.title, a.subtitle, a.createddate, a.user_id,
u.username, u.displayname, u.thumburl,
CONCAT(
    '[',
    GROUP_CONCAT(DISTINCT(
            
        CONCAT(
            CONCAT('{"id":"',m.id,'"'),
            CONCAT(', "typecode":"',m.typecode,'"'),
            CONCAT(', "thumburl":"',ifnull(m.thumburl,''),'"}')
        )
       
    ) ORDER BY m.typecode, am.ordering ASC SEPARATOR ',' ),
    ']'
) as medias
from (
    select id, title, subtitle, createddate, user_id from article
    where user_id in (select user_id from follow where follow_user_id = 33 OR user_id = 33)
    ) as a
left join article_media as am on a.id = am.article_id
left join media as m on am.media_id = m.id
left join (
    select u.id, u.username, u.displayname, u.picture_id, m.thumburl
    from (
        select id, username, displayname, picture_id from user
        where id in (select user_id from follow where follow_user_id = 33 OR user_id = 33)
    ) as u
    left join media as m on u.picture_id = m.id
) as u on a.user_id = u.id
where u.username != 'root'
group by a.id

SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure.
The questions are
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?

I really like the follow up questions of my posts/articles. Answer is xp_cmdshell can execute shell/system command, which includes batch file.

1) Example of running system command using xp_cmdshell is SQL SERVER – Script to find SQL Server on Network

EXEC master..xp_CMDShell 'ISQL -L'

2) Example of running batch file using T-SQL
i) Running standalone batch file (without passed parameters)
EXEC master..xp_CMDShell 'c:findword.bat'

ii) Running parameterized batch file

DECLARE @PassedVariable VARCHAR(100)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = 'SqlAuthority.com'
SET @CMDSQL = 'c:findword.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL

Book Online has additional examples of xp_cmdshell
A. Returning a list of executable files
B. Using Windows net commands
C. Returning no output
D. Using return status
E. Writing variable contents to a file
F. Capturing the result of a command to a file

article full select query using mysql concat, group_concat as json field

article full select with foreign table by one query

SELECT a.*, mediatype.code as mediatypecode, (SELECT CONCAT('[', GROUP_CONCAT(DISTINCT(CONCAT(CONCAT( CONCAT('{"id":"', attribute.id, '"'), CONCAT(', "filterable":"', attribute.filterable, '"') ), CONCAT(', "title":"', REPLACE(attribute.title, '"', '"'), '"'), CONCAT(', "value":"', REPLACE(article_attribute.value, '"', '"'), '"}'))) ORDER BY attribute.ordering ASC SEPARATOR ', ' ), ']') as attributes FROM (article) INNER JOIN article_attribute ON article.id = article_attribute.article_id INNER JOIN attribute ON article_attribute.attribute_id = attribute.id INNER JOIN attribute_item ON attribute.id = attribute_item.attribute_id WHERE article.id = a.id) as attributes, CONCAT('[', GROUP_CONCAT(DISTINCT(CONCAT(CONCAT( CONCAT('{"id":"', media.id, '"'), CONCAT(', "typecode":"', media.typecode, '"'), CONCAT(', "extension":"', media.extension, '"') ), CONCAT(', "path":"', REPLACE(media.path, '"', '"'), '"'), CONCAT(', "filename":"', REPLACE(media.filename, '"', '"'), '"}'))) ORDER BY media.typecode, article_media.ordering ASC SEPARATOR ', ' ), ']') as medias, seourl.url FROM (`article` as a) LEFT JOIN `seourl` ON `a`.`seourl_id` = `seourl`.`id` LEFT JOIN `mediatype` ON `a`.`mediatype_id` = `mediatype`.`id` LEFT JOIN `article_attribute` ON `a`.`id` = `article_attribute`.`article_id` LEFT JOIN `attribute` ON `article_attribute`.`attribute_id` = `attribute`.`id` LEFT JOIN `article_media` ON `a`.`id` = `article_media`.`article_id` LEFT JOIN `media` ON `article_media`.`media_id` = `media`.`id` WHERE `a`.`id` = '1' AND `mediatype`.`code` = 'zar' AND (a.approved = 1) GROUP BY `a`.`id` ORDER BY `a`.`createddate` desc

mysql data to json object select using concat , group_concat

MySQL to JSON Januari 23, 2007 I confess - I used to loop through my MySQL queries, in my server side language of choice, to build JSON. But there is a far better way that will save you some coding, add to simplicity and might even save some valuable server time. If you're running MySQL 4.1 or later you can use the nifty function GROUP_CONCAT() together with the normal CONCAT() function to build all your JSON straight from your SQL query.

username	email
mike	mike@mikesplace.com
jane	jane@bigcompany.com
stan	stan@stanford.com

Our SQL table.

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

A MySQL-query that returns JSON.

[
     {username:'mike',email:'mike@mikesplace.com'},
     {username:'jane',email:'jane@bigcompany.com'},
     {username:'stan',email:'stan@stanford.com'}
]

The returned JSON structure.

Combine multiple rows into one MySQL field using GROUP_CONCAT

SELECT GROUP_CONCAT(DISTINCT category.id) as ids FROM (`category`) LEFT JOIN `seourl` ON `category`.`seourl_id` = `seourl`.`id` WHERE (category.langcode IS NULL OR length(category.langcode) = 0 OR category.langcode='mn') AND `parent_id` IN ('10', '19', '20', '21', '22') AND (parent_id != 0) AND (deleted = 0)

result

array ( 'ids' => 35, 31);



same syntaxs

GROUP_CONCAT(cast(concat(c.id,\': \',c.name) AS char)SEPARATOR \', \') AS categorie_names

mysql group_concat,concat with join

select 
  a.id, 
  a.name,  
  group_concat(b.id) ids, 
  group_concat(b.title) titles,
  group_concat(concat("<a href='index.php?id=", b.id, "'>", b.title, "</a>")) titlelist
from participants a
inner join
  posts b on b.parentid = a.id
group by a.id,a.name

sample

id       Name       Value
1          A          4
1          A          5
1          B          8
2          C          9

result

id          Column
1          A:4,5,B:8
2          C:9  

select id, group_concat(`Name` separator ',') as `Column`
from
(
  select id, concat(`Name`, ':',
  group_concat(`Value` separator ',')) as `Name`
  from mytbl
  group by id, `Name`
) tbl
group by id; 

MSSQL GROUP BY Clause WITH ROLLUP

Бүлэглэгдсэн мэдээллийн нэгдсэн дүнг доор нь бодон харуулахад хэрэглэгдэх функц

USE test
go
create table Orders
(
    OrderID int primary key,
    Customer varchar(10),
    OrderDate datetime,
    ShippingCost money
)

create table OrderDetails
(
    DetailID int primary key,
    OrderID int references Orders(OrderID),
    Item varchar(10),
    Amount money
)

go

insert into Orders
select 1,'ABC', '2007-01-01', 40 union all
select 2,'ABC', '2007-01-02', 30 union all
select 3,'ABC', '2007-01-03', 25 union all
select 4,'DEF', '2007-01-02', 10

insert into OrderDetails
select 1, 1, 'Item A', 100 union all
select 2, 1, 'Item B', 150 union all
select 3, 2, 'Item C', 125 union all
select 4, 2, 'Item B', 50 union all
select 5, 2, 'Item H', 200 union all
select 6, 3, 'Item X', 100 union all
select 7, 4, 'Item Y', 50 union all
select 8, 4, 'Item Z', 300

select * from Orders
select Customer, MAX(OrderDate) as OrderDate, SUM(ShippingCost) as ShippingCost
from Orders GROUP BY Customer WITH ROLLUP

MSSQL data how to PIVOT row content to column name

Энэ нь бүртгэгдсэн мөрүүдийг аль нэг сонгосон ( код: Month гэх мэт ) талбарын хувьд бүлэглэн гарсан үр дүнг хэвтээ чиглэлд ( талбаруудад ) хөрвүүлэх үйл ажиллагаа. Ихэнхдээ энэ нь хязгаарлагдмал утга агуулдаг талбаруудад хэрэгжинэ. Жишээ нь 24 цаг, сарын хоногууд, Төлөвүүд гэх мэт өгөгдлийг бүртгэсэн мөрүүдийг баганын дагуу болгон хөрвүүлэхэд ашиглана.

CREATE TABLE Sales (Human nvarchar(50), [Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('boroo', 'January', 100)
INSERT INTO Sales VALUES ('bataa', 'January', 200)
INSERT INTO Sales VALUES ('boroo', 'February', 300)
INSERT INTO Sales VALUES ('boroo', 'February', 400)

SELECT    Human
      , [January]
      , [February]
      , [March]
FROM    ( SELECT  Human
                  , [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month]
                      IN ([January],[February],[March])
                  ) AS pvt

MSSQL table create with foreign key

USE [MyDb]
GO

CREATE TABLE [dbo].[MachineWorkInfoes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[WorkDate] [datetime] NOT NULL,
[FieldEngineerId] [int] NULL,
[MachineId] [int] NOT NULL,
[TransportCount] [int] NOT NULL,
[TimeStart] [float] NOT NULL,
[TimeEnd] [float] NOT NULL,
[Kilometer] [float] NOT NULL,
[WorkTime] [float] NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](150) NOT NULL,
[Description] [nvarchar](500) NULL,
[Ordering] [int] NULL,
[IsActive] [bit] NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedName] [nvarchar](50) NULL,
[Modified] [datetime] NOT NULL,
[ModifiedName] [nvarchar](50) NULL,
[ParentId] [int] NULL,
[SiteId] [int] NOT NULL,
[SectionId] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MachineWorkInfoes]  WITH CHECK ADD  CONSTRAINT [MachineWorkInfo_FieldEngineer] FOREIGN KEY([FieldEngineerId])
REFERENCES [dbo].[EmployeeInfoes] ([Id])
GO

ALTER TABLE [dbo].[MachineWorkInfoes] CHECK CONSTRAINT [MachineWorkInfo_FieldEngineer]
GO

ALTER TABLE [dbo].[MachineWorkInfoes]  WITH CHECK ADD  CONSTRAINT [MachineWorkInfo_Machine] FOREIGN KEY([MachineId])
REFERENCES [dbo].[EmployeeWithCars] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MachineWorkInfoes] CHECK CONSTRAINT [MachineWorkInfo_Machine]
GO

ALTER TABLE [dbo].[MachineWorkInfoes]  WITH CHECK ADD  CONSTRAINT [MachineWorkInfo_Section] FOREIGN KEY([SectionId])
REFERENCES [dbo].[SectionInfoes] ([Id])
GO

ALTER TABLE [dbo].[MachineWorkInfoes] CHECK CONSTRAINT [MachineWorkInfo_Section]
GO


энэ жишээгээр бол relationship class ийг дураараа үүсгэж загварчилж болно.

Жишээ нь: ASP.NET entity framework дээр entity Class нь нэг иймэрхүү бичиглэлтэй болно
ямар ажилчин ямар тушаал дээр хэзээ ажилласанг бүртгэх кодын жишээ


public class MachineWorkInfo : NamedMetaData
    {
        [DisplayName("Ажил хийсэн өдөр")]
        [Required(ErrorMessage = "Ажил хийсэн өдөр заавал байх ёстой!")]
        public DateTime WorkDate { get; set; }
        [DisplayName("Хариуцсан ажилтан")]
        [Required(ErrorMessage = "Хариуцсан ажилтан заавал байх ёстой!")]
        public Nullable<int> FieldEngineerId { get; set; }
        public virtual EmployeeInfo FieldEngineer { get; set; }
        [DisplayName("Амжил хийсэн машин")]
        [Required(ErrorMessage = "Амжил хийсэн машин заавал байх ёстой!")]
        public int MachineId { get; set; }
        public virtual EmployeeWithCar Machine { get; set; }
        [DisplayName("Рэйсийн тоо")]
        [Required(ErrorMessage = "Рэйсийн тоо заавал байх ёстой!")]
        [RegularExpression(@"^\$?\d+(\.(\d{2}))?$", ErrorMessage = "Тоон утга байх ёстой!")]
        public int TransportCount { get; set; }
        [DisplayName("Спидометр явахад")]
        [Required(ErrorMessage = "Спидометр явахад заавал байх ёстой!")]
        [RegularExpression(@"^(-{0,1})([0-9]+)(\.{0,1})([0-9]*)$", ErrorMessage = "Тоон утга байх ёстой!")]
        public double TimeStart { get; set; }
        [DisplayName("Спидометр ирэхэд")]
        [Required(ErrorMessage = "Спидометр ирэхэд заавал байх ёстой!")]
        [RegularExpression(@"^(-{0,1})([0-9]+)(\.{0,1})([0-9]*)$", ErrorMessage = "Тоон утга байх ёстой!")]
        public double TimeEnd { get; set; }
        [DisplayName("Явсан километр")]
        [Required(ErrorMessage = "Явсан километр заавал байх ёстой!")]
        [RegularExpression(@"^(-{0,1})([0-9]+)(\.{0,1})([0-9]*)$", ErrorMessage = "Тоон утга байх ёстой!")]
        public double Kilometer { get; set; }
        [DisplayName("Ажилласан цаг")]
        [Required(ErrorMessage = "Ажилласан цаг заавал байх ёстой!")]
        [RegularExpression(@"^(-{0,1})([0-9]+)(\.{0,1})([0-9]*)$", ErrorMessage = "Тоон утга байх ёстой!")]
        public double WorkTime { get; set; }
    }


how to delete constrainted column from table

ALTER TABLE [dbo].[LanguageInfoes] DROP CONSTRAINT [FK_LanguageInfoes_SectionInfoes_SectionId]
GO
Exec ('DROP INDEX [' + @indexName + '] ON [' + @tableName + ']')
GO

MSSQL Database Creation & RelationShip simple

Жишээ 1.

CREATE TABLE [dbo].[EmployeeTerritory](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [RowVersion] [int] NOT NULL,
 CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY NONCLUSTERED
(
    [EmployeeID] ASC, [TerritoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[EmployeeTerritory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeTerritory_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[EmployeeTerritory] CHECK CONSTRAINT [FK_EmployeeTerritory_Employee]
GO

ALTER TABLE [dbo].[EmployeeTerritory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeTerritory_Territory] FOREIGN KEY([TerritoryID])
REFERENCES [dbo].[Territory] ([TerritoryID])
GO

ALTER TABLE [dbo].[EmployeeTerritory] CHECK CONSTRAINT [FK_EmployeeTerritory_Territory]
GO

ALTER TABLE [dbo].[EmployeeTerritory] ADD  CONSTRAINT [DF_EmployeeTerritory_RowVersion]  DEFAULT ((0)) FOR [RowVersion]
GO


Жишээ 2.

USE [MyDatabaseName]
GO
CREATE TABLE [dbo].[UserRole](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NOT NULL,
    [RoleId] [bigint] NOT NULL,
 CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([Id])
GO

ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_Role]
GO

ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([Id])
GO

ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_User]
GO

MSSQL SERVER vs MYSQL

MSSQL SERVER

CREATE TABLE [dbo].[table1](
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](10) primary key NOT NULL,
    [col] [nvarchar](20) NOT NULL
)

exec sp_RENAME 'table1.col', 'age', 'COLUMN'

ALTER TABLE tableName
ALTER COLUMN age int NOT NULL


MYSQL SERVER

mysql> create table table1(
    -> id int(10) unsigned primary key auto_increment,
    -> name varchar(100) not null,
    -> col smallint);
Query OK, 0 rows affected (0.38 sec)

mysql> alter table table1
    -> change column col age int;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc table1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)     | NO   |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.13 sec)

MSSQL бүтээгдэхүүний ӨРТӨГ ОРЛОГО ТООЛЛОГО ЗАРЛАГА АШИГ тооцох

Санхүү эсвэл бүтээгдэхүүн үйлчилгээний програм хийхийг хүсдэг залуусд зориулж нэг ийм жишээ гаргалаа. MSSQL Editor дээр хуулж тавиад ажиллуулаад үзээрэй энэ их хэрэгтэй жишээ болсон байх гэж бодож байна. Бүтээгдэхүүн үйлчилгээний өртөг зардал орлого ашиг тооцоход энэ жишээ хэрэг болно. Миний зүгээс зориулж байна

USE [test]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 06/21/2011 19:28:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
    [ProductPkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Size] [int] NOT NULL,
    [Price] [money] NOT NULL
) ON [PRIMARY]

GO


USE [test]
GO

/****** Object:  Table [dbo].[Income]    Script Date: 06/21/2011 19:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Income](
    [IncomePkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ProductPkID] [numeric](18, 0) NOT NULL,
    [IncomeSize] [int] NOT NULL,
    [IncomeAmount] [money] NOT NULL,
    [IncomeDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


USE [test]
GO

/****** Object:  Table [dbo].[Outcome]    Script Date: 06/21/2011 19:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Outcome](
    [OutcomePkID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ProductPkID] [numeric](18, 0) NOT NULL,
    [OutcomeSize] [int] NOT NULL,
    [OutcomeAmount] [money] NOT NULL,
    [OutcomeDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


Өгөгдөлөөр дүүргие

insert Product values('Talh',1,950)
insert Product values('Tamhi',20,2000)
insert Product values('Juus',1,850)
insert Product values('Haraa',750,7500)

insert Income values(1,10,7500,'2011-06-21')--10sh 750
insert Income values(2,100,8000,'2011-06-21')--5 bottle 1600
insert Income values(3,16,11200,'2011-06-21')--16sh 700
insert Income values(3,8,5600,'2011-06-21')--8sh 700
insert Income values(4,3000,22000,'2011-06-21')--4 bottle 5500

insert Outcome values(1,8,950,'2011-06-21')--8sh 950
insert Outcome values(2,20,2000,'2011-06-21')--1 bottle 2000
insert Outcome values(2,20,2000,'2011-06-21')--1 bottle 2000
insert Outcome values(2,10,1000,'2011-06-21')--10sh 1000
insert Outcome values(3,10,8500,'2011-06-21')--10sh 850
insert Outcome values(3,2,1700,'2011-06-21')--2sh 850
insert Outcome values(3,1,850,'2011-06-21')--1sh 850
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,50,500,'2011-06-21')--50ml 500
insert Outcome values(4,750,7500,'2011-06-21')--1 bottle 7500
insert Outcome values(4,300,3000,'2011-06-21')--50ml 500


Ашиг орлого тооцие


FirstAmount зарагдсан барааг анх худалдаж авсан дүн
GetAmount зарагдсан бараанаас олсон ашиг

select I.*, O.OutcomeSize, O.OutcomeAmount
, (I.IncomeSize-O.OutcomeSize) as ChangeSize
, ((I.IncomeAmount / I.IncomeSize) * O.OutcomeSize) as FirstAmount
, (O.OutcomeAmount -((I.IncomeAmount / I.IncomeSize) * O.OutcomeSize)) as GetAmount
from (select ProductPkID, sum(IncomeSize) as IncomeSize, sum(IncomeAmount) as IncomeAmount, IncomeDate
    from Income group by ProductPkID, IncomeDate) I
inner join (select ProductPkID, sum(OutcomeSize) as OutcomeSize, sum(OutcomeAmount) as OutcomeAmount, OutcomeDate
    from Outcome group by ProductPkID, OutcomeDate) O
on I.ProductPkID=O.ProductPkID
order by ProductPkID

select * from Income order by ProductPkID
select * from Outcome order by ProductPkID