--1.
NOT NULL CONSTARINT // ARE APPLIED ON FIELD
-- WILL NOT ALLOW NULL VALUES
CREATE TABLE TBL_NOTNULL
(
EMPID INT NOT NULL
,ENAME NVARCHAR(50) NOT NULL
,EMAIL NVARCHAR(50) NOT NULL
,DOB DATE NOT NULL
,DOJ DATETIME NOT NULL
,SAL MONEY NOT NULL
,COMM DECIMAL(18,2)
,[ADDRESS] NVARCHAR(50)
,ISACTIVE BIT NOT NULL
)
SELECT * FROM TBL_NOTNULL
INSERT INTO TBL_NOTNULL(EMPID,ENAME,EMAIL,DOB,DOJ,SAL,ADDRESS,ISACTIVE)
VALUES (3,'SAM','SAM@YAHOO.COM','05/29/1990',GETDATE(),15000,'HYD',0)
--UNIQUE NO DUPLICATES
CREATE TABLE TBL_NOTNULL_UNIQUE
(
EMPID INT UNIQUE NOT NULL
,ENAME NVARCHAR(50) UNIQUE NOT NULL
,EMAIL NVARCHAR(50) UNIQUE NOT NULL
,DOB DATE NOT NULL
,DOJ DATETIME NOT NULL
,SAL MONEY NOT NULL
,COMM DECIMAL(18,2)
,[ADDRESS] NVARCHAR(50)
,ISACTIVE BIT UNIQUE
)
SELECT * FROM TBL_NOTNULL_UNIQUE
INSERT INTO TBL_NOTNULL_UNIQUE(EMPID,ENAME,EMAIL,DOB,DOJ,SAL,COMM,ADDRESS,ISACTIVE)
VALUES (3,'KHAN','KHAN@YAHOO.COM','05/29/1990',GETDATE(),15000,500,'HYD',2)
--IDENTITY -- Auto Increment
CREATE TABLE TBL_NOTNULL_IDENTITY
(
EMPID INT IDENTITY(1,1)
,ENAME NVARCHAR(50)
,EMAIL NVARCHAR(50) UNIQUE NOT NULL
,DOB DATE NOT NULL
,DOJ DATETIME NOT NULL
,SAL MONEY NOT NULL
,COMM DECIMAL(18,2)
,[ADDRESS] NVARCHAR(50)
,ISACTIVE BIT
)
SELECT * FROM TBL_NOTNULL_IDENTITY
INSERT INTO TBL_NOTNULL_IDENTITY(ENAME,EMAIL,DOB,DOJ,SAL,COMM,ADDRESS,ISACTIVE)
VALUES ('KHAN','KHAN@YAHOO.COM','05/29/1990',GETDATE(),15000,500,'HYD',1)
--CHECK
CREATE TABLE TBL_CHECK
(
EMPID INT IDENTITY(1,1)
,ENAME NVARCHAR(50)
,EMAIL NVARCHAR(50) UNIQUE NOT NULL
,DOB DATE NOT NULL
,DOJ DATETIME NOT NULL
,SAL MONEY NOT NULL
,COMM DECIMAL(18,2)
,[ADDRESS] NVARCHAR(50)
,ISACTIVE BIT
--,AGE INT CHECK (AGE>=18)
,AGE INT CHECK (AGE BETWEEN 18 AND 50)
)
SELECT * FROM TBL_CHECK
INSERT INTO TBL_CHECK(ENAME,EMAIL,DOB,DOJ,SAL,COMM,ADDRESS,ISACTIVE,AGE)
VALUES ('KHAN','KHAN@YAHOO.COM','05/29/1990',GETDATE(),15000,500,'HYD',1,25)
--DEFAULT
CREATE TABLE TBL_DEFAULT1
(
EMPID INT IDENTITY(1,1)
,ENAME NVARCHAR(50)
,EMAIL NVARCHAR(50) UNIQUE NOT NULL
,DOB DATE NOT NULL
,DOJ DATETIME NOT NULL
,SAL MONEY NOT NULL
,COMM DECIMAL(18,2)
,[ADDRESS] NVARCHAR(50) DEFAULT 'HYD' UNIQUE
,ISACTIVE BIT
--,AGE INT CHECK (AGE>=18)
,AGE INT CHECK (AGE BETWEEN 18 AND 50)
)
SELECT * FROM TBL_DEFAULT1
INSERT INTO TBL_DEFAULT1(ENAME,EMAIL,DOB,DOJ,SAL,COMM,ISACTIVE,AGE,ADDRESS)
VALUES ('KHAN','KHAN@YAHOO.COM','05/29/1990',GETDATE(),15000,500,1,25,'SEC')
--PRIMARY KEY
CREATE TABLE TBL_PK
(
EMPID INT PRIMARY KEY
,ENAME NVARCHAR(50)
,EMAIL NVARCHAR(50) UNIQUE NOT NULL
,DOB DATE NOT NULL
,DOJ DATETIME NOT NULL
,SAL MONEY NOT NULL
,COMM DECIMAL(18,2)
,[ADDRESS] NVARCHAR(50) UNIQUE
,ISACTIVE BIT
,AGE INT CHECK (AGE BETWEEN 18 AND 50)
)
SELECT * FROM TBL_PK
INSERT INTO TBL_PK(EMPID,ENAME,EMAIL,DOB,DOJ,SAL,COMM,ISACTIVE,AGE,ADDRESS)
VALUES (3,'ANU','ANU@YAHOO.COM','05/29/1990',GETDATE(),15000,500,1,25,'DEDE')
--FK
--PK FK
-- FOR LINKING OR JOINNG TWO OR MORE TABLES WE USE PK AND FK
CREATE TABLE PARENT
(
PID INT PRIMARY KEY
,PNAME NVARCHAR(50)
,AGE INT
)
INSERT INTO PARENT VALUES (1,'SAVARAIH') -- ABHISHEK
INSERT INTO PARENT VALUES (2,'UTTAM') -- ANIL
SELECT * FROM PARENT
SELECT * FROM CHILD
CREATE TABLE CHILD
(
CID INT
,CNAME NVARCHAR(50)
,PARENT_REFID INT FOREIGN KEY REFERENCES PARENT(AGE)
)
INSERT INTO CHILD VALUES (101,'ANIL',2)
--GET CHILD NAME AND PARENT NAME IN SELECT FROM PARENT AND CHILD TABLE
--PK -- CAN HAVE MULTIPLR FK FK
--
NOT NULL
UNIQUE
DEFAULT
CHECK
IDENTITY
PK
FK
--CREATE TABLE WITH THESE CONSTRANTS
IDENTITY
1
AGAIN CAN I ADD 2 OR NOT
CREATE TABLE TEST
(
ID INT IDENTITY(1,1)
,NAME NVARCHAR(50)
)
SET IDENTITY_INSERT DBO.TEST OFF
INSERT INTO TEST(ID,NAME) VALUES (2,'ANUSHA')
SELECT * FROM TEST
DELETE FROM TEST WHERE ID =2
SELECT * FROM TEST
UPDATE TEST SET NAME ='XYZ'
DELETE FROM TEST
UPDATE -- UPDATING A FILED
DELETE -- DELETING
