Lab 3 ABD

CREATE DATABASE LabMTest;
USE LabMTest;
CREATE TABLE employees (
E_ID VARCHAR(12) NOT NULL PRIMARY KEY,
E_Name VARCHAR(25) NOT NULL,
E_Address VARCHAR(55),
Salary INT NOT NULL,
Department VARCHAR(12)
)
INSERT INTO employees
(E_id, E_Name, E_Address, Salary, Department) VALUES
('E011','Rakib','Cumilla',64587,'HR'),
('E012','Rakib','Dhaka',24587,'IT'),
('E013','Rakiba','Dhaka',34587,'IT'),
('E014','Rasel','Chittagong',54587,'Management'),
('E015','Robin','Chadpur',24587,'Sale'),
('E016','Rubi','Dhaka',14587,'Sale'),
('E017','Kabir','Chadpur',28587,'Management'),
('E018','Rothi','Dhaka',28587,'HR');

CREATE TABLE Department (
D_id INT NOT NULL PRIMARY KEY,
D_Name VARCHAR(25) NOT NULL,
Location VARCHAR(55)
);
CREATE TABLE Student (
S_ID VARCHAR(25) NOT NULL PRIMARY KEY,
S_Name VARCHAR(55) NOT NULL,
S_address VARCHAR(25),
D_ID INT FOREIGN KEY REFERENCES Department(D_ID)
);

CREATE TABLE Course (
C_Code VARCHAR(25) NOT NULL PRIMARY KEY,
C_Name VARCHAR(25) NOT NULL,
Credit INT,
D_ID INT FOREIGN KEY REFERENCES Department(D_ID)
);

CREATE TABLE Taken_Course (
S_ID VARCHAR(25) NOT NULL FOREIGN KEY REFERENCES Student(S_ID),
C_Code VARCHAR(25) NOT NULL FOREIGN KEY REFERENCES Course(C_Code),
PRIMARY KEY (S_ID, C_Code)
);

INSERT INTO Department (D_ID, D_Name, Location) VALUES
(11, 'BBA','Shubanbag'),
(15, 'CSE', 'Shukrabad'),
(35, 'SWE', 'Shukrabad'),
(25,'EEE', 'Shubanbag');

INSERT INTO Student (S_ID, S_Name, S_address, D_ID) VALUES
('171-35-1257', 'Jamal', 'Dhaka', '35'),
('171-35-1258', 'Rakiba', 'Rajshahi', '35'),
('171-35-1259', 'Rakib', 'Dhaka', '35'),
('171-35-1260', 'Jamal', 'Rajshahi', '35'),
('171-11-1261', 'Jamal', 'Khulna', '11'),
('171-11-1262', 'Rakiba', 'Khulna', '11'),
('171-11-1263', 'Rakib', 'Barishal', '11'),
('171-11-1264', 'Jamal', 'Barishal', '11');

INSERT INTO Course(C_code, C_Name, Credit, D_ID) VALUES
('SWE423', 'Database', '4', '35'),
('SWE425', 'Telecom', '4', '35'),
('SWE332', 'Network', '4', '35'),
('SWE426', 'Distributive Computing', '4', '35'),
('CS452', 'Database', '4', '11'),
('CS258', 'Network', '4', '11'),
('CS845', 'Telecom', '4', '11');



 SELECT E_Name FROM employees WHERE Salary = (SELECT MAX(Salary) FROM employees)


 SELECT * FROM Student
 WHERE D_ID = (SELECT D_ID FROM Department WHERE D_Name = 'SWE') OR (D_ID IN (SELECT C_Code FROM Taken_Course WHERE C_Code = 'Network'))

 SELECT E_Name FROM employees
 WHERE Department LIKE 's%'


 DECLARE @MAX VARCHAR(20)
 DECLARE @MIN VARCHAR(20)
 DECLARE @AVG VARCHAR(20)

SELECT @MAX = CAST(MAX(Salary) AS VARCHAR(20)), @MIN = CAST(MIN(Salary) AS VARCHAR(20)),@AVG = CAST(AVG(Salary) AS VARCHAR(20)) FROM employees

 print('Maximum Salary: '+CAST(@MAX AS VARCHAR(10))+' Mimimum Salary: '+CAST(@MIN AS VARCHAR(10))+' Avg Salary: '+CAST(@AVG AS VARCHAR(10)))







 DECLARE @e_salary FLOAT = (SELECT salary FROM employees WHERE salary > 30000 AND E_ID = 'E013')
 SET @e_salary=(@e_salary + 500)
 print(@e_salary)

 DECLARE @e_salary2 FLOAT =(SELECT salary FROM employees WHERE salary > 30000 AND E_ID = 'E013')
 IF(@e_salary2> 30000)
 BEGIN
    UPDATE employees
    SET Salary = @e_salary2 - 5000
    WHERE E_ID = 'E013';
 END

 ELSE IF (@e_salary2 < 30000)
 BEGIN
    UPDATE employees
    SET Salary = @e_salary2 + 10000
    WHERE E_ID = 'E013';
 END

 SELECT salary FROM employees WHERE E_ID = 'E013'