Basic T-SQL Queries.



IF OBJECT_ID('dbo.Employees','U') IS NOT NULL
DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
EmpID INT NOT NULL,
FirstName varchar(30) not null,
LastName VARCHAR(30) NOT NULL,
HireDate DATE NOT NULL,
MgrID INT NULL,
ssn varchar(20) not null,
Salary MONEY not null
)

ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY(EmpID)
GO
ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn UNIQUE(ssn)
GO
IF OBJECT_ID('dbo.Orders','U')IS NOT NULL
DROP TABLE dbo.Orders
CREATE TABLE dbo.Orders
(
 OrderID INT NOT NULL,
 EmpID INT NOT NULL,
 CustID VARCHAR(10) NOT NULL,
 OrderTs DATETIME2 NOT NULL,
 Qty INT NOT NULL,
 CONSTRAINT Pk_Orders PRIMARY KEY(OrderID)
)
GO
ALTER TABLE dbo.Orders
ADD CONSTRAINT Fk_Orders_Employees FOREIGN KEY(EmpID)REFERENCES dbo.Employees(EmpID)
go
SP_HELP Employees
GO
ALTER TABLE dbo.Employees
ADD CONSTRAINT Fk_Employees_MGRID FOREIGN KEY(MgrID)REFERENCES dbo.Employees(EmpID)
GO
ALTER TABLE dbo.Employees
ADD CONSTRAINT chk_Employees_Salarty CHECK(Salary>0.00)
GO
ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_Orderts DEFAULT(SYSDATETIME()) FOR OrderTs

DROP TABLE dbo.Orders,dbo.Employees

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server