July 30, 2011 at 11:11 pm
use master
CREATE DATABASE [DIPANJALI1DB]
ON PRIMARY
(
NAME=N'DIPANJALI1DBPRI1',
FILENAME=N'D:\DIPANJALI\DATAFILES\PDIPANJALI1DBPRI1.MDF',
FILEGROWTH=10%,
SIZE=10MB,
MAXSIZE=100MB
)
LOG ON
(
NAME=N'DIPANJALI1DBLOG1',
FILENAME=N'D:\DIPANJALI\DATAFILES\PDIPANJALI1DBLOG1.LDF',
FILEGROWTH=10%,
SIZE=10MB,
MAXSIZE=100MB
)
CREATE LOGIN [DIPANJALI1LOGIN]
WITH PASSWORD=N'123456',
DEFAULT_DATABASE=[DIPANJALI1DB],
DEFAULT_LANGUAGE=[US_ENGLISH],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember
@LOGINAME=N'DIPANJALI1LOGIN',
@ROLENAME=N'SYSADMIN'
CREATE USER [DIPANJALI1USER]
FOR LOGIN [DIPANJALI1LOGIN]
CREATE SCHEMA [DIPANJALI1SCHEMA]
AUTHORIZATION [DIPANJALI1USER]
CREATE TABLE [DIPANJALI1SCHEMA].[DEPT]
(
DEPTNO INT IDENTITY(100,10)
CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR(20),
LOCATION NVARCHAR(20)
CONSTRAINT CK_DEPT_LOCATION
CHECK(LOCATION IN
('NEW DELHI','CHENNAI','KOLKATA','MUMBAI'))
)
INSERT INTO [DIPANJALI1SCHEMA].[DEPT]
(DNAME,LOCATION)
SELECT 'ADMIN','KOLKATA'
UNION
SELECT 'ACCOUNTS','KOLKATA'
UNION
SELECT 'PRODUCTION','KOLKATA'
UNION
SELECT 'SALES','CHENNAI'
UNION
SELECT 'MARKETING','CHENNAI'
UNION
SELECT 'IT','MUMBAI'
UNION
SELECT 'QUALITY','NEW DELHI'
CREATE TABLE [DIPANJALI1SCHEMA].[EMP]
(
EMPNO BIGINT CONSTRAINT PK_EMP PRIMARY KEY,
ENAME CHAR(30),
GENDER BIT,
DOJ SMALLDATETIME DEFAULT GETDATE(),
PHOTO IMAGE,
BIODATA VARBINARY(MAX),
DEPTNO INT CONSTRAINT FK_EMP_DEPT
FOREIGN KEY
REFERENCES [DIPANJALI1SCHEMA].[DEPT](DEPTNO)
)
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
ADD DESIGNATION VARCHAR(10)
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
ADD CONSTRAINT CK_EMP_DESIGNATION
CHECK(DESIGNATION IN
('PRESIDENT','CEO','MANAGER','PROGRAMMER'))
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
DROP PK_EMP
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
DROP COLUMN EMPNO
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
ADD EMPNO BIGINT IDENTITY(1000,1)
CONSTRAINT PK_EMP PRIMARY KEY
INSERT INTO [DIPANJALI1SCHEMA].[EMP]
(ENAME,DESIGNATION,GENDER,DEPTNO)
SELECT 'AMAL','PRESIDENT','True','100'
UNION
SELECT 'RAJ','CEO','True','110'
UNION
SELECT 'GOPA','MANAGER','False','100'
UNION
SELECT 'PRITHA','PROGRAMMER','False','120'
UNION
SELECT 'DEVIKA','PROGRAMMER','False','100'
UNION
SELECT 'SAM','MANAGER','True',NULL
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
ADD SALARY MONEY
CONSTRAINT CK_EMP_SALARY
CHECK(SALARY BETWEEN 10000 AND 30000)
ALTER TABLE [DIPANJALI1SCHEMA].[EMP]
ADD EMAIL NVARCHAR(30)
July 31, 2011 at 2:33 am
There's a Create Database DIPANJALI1DB, but there's no USE DIPANJALI1DB, so all of your objects got created in master.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply