SQLize Online / PHPize Online  /  SQLtest Online

-- Drop the database 'dbEmployee' -- Connect to the 'master' database USE master GO -- Uncomment the ALTER DATABASE statement below to set the database to SINGLE_USER mode if the drop database command fails because the database is in use. -- ALTER DATABASE dbEmployee SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- Drop the database if it exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'dbEmployee' ) DROP DATABASE dbEmployee GO -- Create the new database if it does not exist already IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N'dbEmployee' ) CREATE DATABASE dbEmployee GO USE dbEmployee GO /* * Practice -1 Write a SQL statement to create a table named countries including columns CountryId, CountryName and RegionId and make sure that no countries except Italy, India and China will be entered in the table. and combination of columns CountryId and RegionId will be unique. */ -- Create a new table called 'Countries' in schema 'dbo' -- Drop the table if it already exists IF OBJECT_ID(N'dbo.Countries', N'U') IS NOT NULL DROP TABLE dbo.Countries GO -- Create the table in the specified schema CREATE TABLE dbo.Countries ( CountryId INT NOT NULL, CountryName VARCHAR(1) CONSTRAINT CK_CountryName CHECK (CountryName in ('Italy', 'India','China')), RegionId INT NOT NULL, CONSTRAINT PK_countryID_regionId Primary key (CountryId,RegionId) ); GO --Modify Data type ALTER TABLE Countries ALTER COLUMN CountryName VARCHAR(5) NOT NULL GO --DROP PK Constraint ALTER TABLE Countries DROP CONSTRAINT PK_countryID_regionId GO --Add PK Constriant ALTER TABLE Countries ADD CONSTRAINT PK_countryID_RegionId Primary key (CountryId,RegionId) GO /* Practice -2 Write a SQL statement to create a table named JobHistory including columns EmployeeId, StartDate, EndDate, Job_Id and Department_Id and make sure that the value against column EndDate will be entered at the time of insertion to the format like '–/–/—-' **/ -- Create a new table called 'JobHistory' in schema 'dbo' -- Drop the table if it already exists IF OBJECT_ID('dbo.JobHistory', 'U') IS NOT NULL DROP TABLE dbo.JobHistory GO -- Create the table in the specified schema CREATE TABLE dbo.JobHistory ( EmployeeId int NOT NULL PRIMARY KEY, JobId int NOT NULL, StartDate VARCHAR(10) NOT NULL, EndDate VARCHAR(10) NOT NULL CONSTRAINT CK_EndDate CHECK(EndDate Like '__/__/____'), DepartmentId int NOT NULL, ); GO /* Practice-3 Write a SQL statement to create a table named jobs including columns JobId, JobTitle, MinSalary and MaxSalary, and make sure that, the default value for JobTitle is blank and MinSalary is 8000 and MaxSalary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns. */ -- Create a new table called 'Jobs' in schema 'dbo' -- Drop the table if it already exists IF OBJECT_ID('dbo.Jobs', 'U') IS NOT NULL DROP TABLE dbo.Jobs GO -- Create the table in the specified schema CREATE TABLE dbo.Jobs ( JobId int NOT NULL PRIMARY KEY, JobTitle VARCHAR(25) NOT NULL DEFAULT '', MinSalary DECIMAL(18,2) NOT NULL DEFAULT 8000, MaxSalary DECIMAL(18,2) , ); GO /* Practice -4 Write a SQL statement to create a table employees including columns Employee_Id, FirstName, LastName, Email, PhoneNumber, Hire_Date, Job_Id, Salary, Commission, Manager_Id and Department_Id and make sure that, the Employee_Id column does not contain any duplicate value at the time of insertion, and the foreign key column DepartmentId, reference by the column DepartmentId of Departments table, can contain only those values which are exists in the Department table and another foreign key column JobId, referenced by the column JobId of jobs table, can contain only those values which are exists in the jobs table. */ -- Create a new table called 'Departments' in schema 'dbo' -- Drop the table if it already exists IF OBJECT_ID('dbo.Departments', 'U') IS NOT NULL DROP TABLE dbo.Departments GO -- Create the table in the specified schema CREATE TABLE dbo.Departments ( DepartmentId INT NOT NULL PRIMARY KEY, DepartmentName VARCHAR(25) NOT NULL ); GO -- Create a new table called 'Employees' in schema 'dbo' -- Drop the table if it already exists IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees GO -- Create the table in the specified schema CREATE TABLE dbo.Employees ( EmployeesId INT NOT NULL PRIMARY KEY, FirstName VARCHAR(25) NOT NULL, LastName VARCHAR(25) NOT NULL, Email VARCHAR(25) NOT NULL, PhoneNumber int NOT NULL, HireDate Date NOT NULL, Salary DECIMAL(18,2) NOT NULL, Commission DECIMAL(18,2), ManagerId int NOT NULL, JobId int NOT NULL, DepartmentId int NOT NULL, CONSTRAINT FK_JobId_EMP_JOBS FOREIGN KEY(JobId) REFERENCES jobs(JobId), CONSTRAINT FK_DepartmentId_EMP_Departments FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId) ); GO /* Practice-5 Alter statement Write a SQL statement to add a foreign key constraint named fk_job_id on JobId column of JobHistory table referencing to the primary key JobId of jobs table. Write a SQL statement to drop the existing foreign key fk_job_id from JobHistory table on JobId column which is referencing to the JobId of jobs table. Write a SQL statement to add a new column named location to the JobHistory table. */ -- ADD Constarint ALTER TABLE JobHistory ADD CONSTRAINT FK_JOB_ID FOREIGN KEY(JobId) REFERENCES Jobs(JobId) GO -- DROP Constarint ALTER TABLE JobHistory DROP CONSTRAINT FK_JOB_ID GO -- Add a 'location' Column ALTER TABLE JobHistory ADD location VARCHAR(50) GO