USE MyDB; GO CREATE TABLE [Employee] ( [EID] int NOT NULL PRIMARY KEY, [Name] nvarchar(128) NOT NULL, [Gender] char(1) COLLATE SQL_Latin1_General_CP1_CS_AS --Make it Case Sensitive CONSTRAINT CK_Employees_Gender CHECK (Gender IN ('M', 'F', 'O')) --Constraint valid value , [Department] char(2), [Race] char(15), [Supervisor] int, [Age] int, [Basic Salary] money ); GO INSERT INTO [Employee] VALUES(1000,'Tong Sam Pah', 'M','IT','Chinese', 1005,23,5000); INSERT INTO [Employee] VALUES(1002,'Yong Tau Foo', 'M','FN','Chinese', 1015,25,4800); INSERT INTO [Employee] VALUES(1005,'Low Mee', 'F','IT','Chinese', 1015,26,5100); INSERT INTO [Employee] VALUES(1008,'Low Shi Fun', 'F','IT','Chinese', 1005,24,4300); INSERT INTO [Employee] VALUES(1010,'Ali', 'M','HR','Malay', 1015,29,4700); INSERT INTO [Employee] VALUES(1012,'Abu', 'M','FN','Malay', 1002,35,5340); INSERT INTO [Employee] VALUES(1015,'Ahmad', 'M','IT','Malay', NULL,40,6500); INSERT INTO [Employee] VALUES(1017,'Aaron', 'M','OP', NULL, 1015,32,5500); INSERT INTO [Employee] VALUES(1020,'Ah Chong', 'M','SA','Chinese', 1028,28,5600); INSERT INTO [Employee] VALUES(1022,'Azizi', 'M','RD','Malay', 1015,30,5780); INSERT INTO [Employee] VALUES(1028,'Shila Hamzah', 'F','SA','Malay', 1015,25,4325); INSERT INTO [Employee] VALUES(1030,'Narayanan', 'M','FN','Indian', 1002,27,4340); INSERT INTO [Employee] VALUES(1032,'Fatimah', 'F','SA','Malay', 1015,26,5345); SELECT * FROM [Employee]; DELETE FROM [Employee]; DROP TABLE [Employee]; ALTER TABLE [Employee] ADD [Weight in KG] float CONSTRAINT CK_Employees_Weight CHECK ([Weight in KG]>0); ALTER TABLE [Employee] DROP CONSTRAINT CK_Employees_Weight; ALTER TABLE [Employee] DROP COLUMN [Weight in KG]; CREATE TABLE [Department] ( [DID] char(2) NOT NULL PRIMARY KEY, [Department Name] nvarchar(128) NOT NULL, [HID] int ); GO INSERT INTO [Department] VALUES('FN','Finance', 1028); INSERT INTO [Department] VALUES('HR','Human Resource', 1022); INSERT INTO [Department] VALUES('IT','Information Technology', 1010); INSERT INTO [Department] VALUES('OP','Operation', 1015); INSERT INTO [Department] VALUES('QA','Quality Assurance', 1017); INSERT INTO [Department] VALUES('RD','Research & Development', 1020); INSERT INTO [Department] VALUES('SA','Sales', 1012); SELECT * FROM [Department]; DELETE FROM [Department]; DROP TABLE [Department]; --NULL Handling SELECT * FROM [Employee] WHERE [Race] IS NULL; SELECT * FROM [Employee] WHERE [Race] IS NOT NULL; SELECT [EID],[Name], [Race] FROM Employee; SELECT [EID],[Name], ISNULL([Race], '') AS [Race] FROM Employee; SELECT [EID],[Name], COALESCE([Race], '') AS [Race] FROM Employee; CREATE TABLE [Wages] ( [Name] nvarchar(32) NOT NULL, [Basic Salary] money, [Sales Amount] money, [Hour Rate] money, [Hours] int ); GO INSERT INTO [Wages] VALUES('Ali',5500.15,NULL,NULL,NULL); INSERT INTO [Wages] VALUES('Abu',NULL,100000.00,NULL,NULL); INSERT INTO [Wages] VALUES('Aminah',NULL,NULL,32.5,20); INSERT INTO [Wages] VALUES('Ah Chong',NULL,NULL,NULL,NULL); SELECT * FROM [Wages]; SELECT [Name], CAST(COALESCE([Basic Salary],0.1*[Sales Amount],[Hour Rate]*[Hours],0.0) AS DECIMAL(16,2)) [Salary] FROM [Wages]; DECLARE @EPFEmployee float = 0.11;--Declare a Variable SELECT [Name], [Basic Salary], [Age], CAST(((1.0-@EPFEmployee)*[Basic Salary]) AS money) [Monthly Salary], --Custom Column CAST(((2*@EPFEmployee+0.01)*[Basic Salary]) AS money) [EPF], --Custom Column CAST(IIF([Age]<25,10,IIF([Age]>=35,30,20)) AS money) [Socso] --Custom Column FROM [Employee]; --How Many Employees? --How Many Male Employees? --How Many IT Male Employees? --What is the oldest Employee Age? --What is the oldest Female Employee Age? --Who are the 3 Highest Basic Salary employees? --What is the Average Basic Salary for each department? --How many Employees for each Race? --Show total Basic Salary group by Department followed by Race SELECT [Department],[Race],SUM([Basic Salary]) [Total] FROM [Employee] GROUP BY [Department],[Race] ORDER BY [Department];