CREATE SCHEMA [dtl]
GO

CREATE SCHEMA [Query]
GO

CREATE TABLE [dtl].[Departments]
(
  DepartmentId INT IDENTITY(1,1),
  DepartmentName NVARCHAR(100) NOT NULL,
  CONSTRAINT PK_Departments PRIMARY KEY (DepartmentID),
  CONSTRAINT FK_Department FOREIGN KEY(DepartmentId) REFERENCES  [dtl].[Departments](DepartmentId)
 
)
GO

CREATE TABLE [Query].[Students]
(
	StudentId INT IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Age INT NOT NULL,
    DOB DATE Not NULL,
    Email NVARCHAR(255) NOT NULL ,
    Mobile NVARCHAR(50) NOT NULL,
    DepartmentId INT NOT NULL,
  	CONSTRAINT PK_Students PRIMARY KEY (StudentId)
)
GO



INSERT INTO [Query].[Students] Values	('Karthi', 'Saravanan', 24, '2001-04-27', '[email protected]', '7837987369', 2),
							              ('MOUNIKHA', 'K', 22, '2002-02-05', '[email protected]', '9920044220', 3),
						              	('MADHU', 'M', 24, '2000-04-30', '[email protected]', '7993921990', 3),
						              	('DINESH', 'KAUMAR', 24, '2000-04-26', '[email protected]', '8817711003', 4),
						              	('Karthi', 'S', 23, '2001-06-29', '[email protected]', '8837492929', 4),
						              	('MOUNI', 'C', 24, '2000-08-12', '[email protected]', '6829949920', 5),
						              	('DEVI', 'S', 26, '1999-04-30', '[email protected]', '7932402948', 1),
						              	('RAVI', 'SHANKAR', 25, '1998-04-26', '[email protected]', '6328849202', 1),
						              	('HARISH', 'C', 24, '1998-07-12', '[email protected]', '9442737399', 3),
						              	('PANNER', 'K', 24, '2000-04-10', '[email protected]', '8849100492', 2),
						              	('WILFRED', 'FREDDY', 23, '2000-02-03', '[email protected]', '7738299722',4),
						              	('SUVETHA', 'S', 22, '1999-06-06', '[email protected]', '9493820049',1),
							              ('JEEVAN', 'KUMAR', 24, '1998-03-02', '[email protected]', '8932300402', 3),
						              	('PRIYANKA', 'P', 24, '1998-04-12', '[email protected]', '8932939942', 2),
						              	('DEVIAGI', 'J', 24, '2000-05-20', '[email protected]', '9976139117', 4),
						              	('VENKATESH', 'S', 25, '1998-04-26', '[email protected]', '8839950204', 5),
						              	('ARUN', 'S', 23, '2001-09-23', '[email protected]', '9908929949', 3),
						              	('SURESH', 'KANNAN', 22, '2002-03-25', '[email protected]', '9883992059', 2),
						               	('DINISH', 'M', 24, '2000-05-28', '[email protected]', '9330052994', 1),
						              	('DEENA', 'M', 24, '2000-06-02', '[email protected]', '9932495531', 1),
						              	('LAKSHMI', 'A', 23, '2001-04-19', '[email protected]', '9903024995', 1),
						              	('DRUAN', 'R', 24, '2000-03-21', '[email protected]', '9094000434', 3),
						              	('RAMYA', 'SRI', 26, '1999-03-30', '[email protected]', '9094002399', 5),
						              	('GAYATHRI', 'Y', 25, '1998-09-02', '[email protected]', '8846658924', 5),
						              	('JEGAN', 'S', 22, '2002-04-05', '[email protected]', '9002994768', 3),
						              	('AJAY', 'KUMAR', 23, '2001-05-22', '[email protected]', '8899482004', 5),
						              	('HARIHARAN', 'R', 24, '2000-12-12', '[email protected]', '7094992450', 4),
						              	('MOHAN', 'A', 25, '1998-05-06', '[email protected]', '8839030422', 3),
						              	('GOKUL', 'A', 23, '2001-05-16', '[email protected]', '9938290042', 2),
						              	('GOPI', 'S', 22, '2002-07-19', '[email protected]', '9020039950', 3),
						              	('MUTHU', 'KUMAR', 23, '2001-05-09', '[email protected]', '9932900432', 1),
						              	('ARJUN', 'KUMAR', 26, '1999-09-13', '[email protected]', '8848920452', 5),
							              ('MANIVANAN', 'M', 25, '1998-05-17', '[email protected]', '6629936772',3),
							              ('JEEVAN','KUMAR', 26, '1999-10-28', '[email protected]', '7738829942',6),
							              ('Ola', 'Hansen', 23 ,'2001-07-23', '[email protected]', '8832785984',5),
							              ('Tove', 'Svendson', 24 ,'2000-01-23', '[email protected]', '8932400504');

GO

INSERT INTO [dtl].[Departments] Values ('IT'),
                               ('CSE'),
                               ('ECE'),
                               ('EEE'),
                               ('MECH'),
                               ('BIO-MEDICAL'),
                               ('AGRICUTURE');
GO                               

--Alter TABLE Students
--DROP COLUMN DepartmentId

SELECT * FROM [Query].[Students]
GO

SELECT * FROM [dtl].[Departments]
GO

ALTER TABLE [Query].[Students] ALTER COLUMN  DepartmentId INT NULL;