dbms lab 4

CREATE DATABASE dblab;
USE dblab;

CREATE TABLE STUDENTACCOUNT(
ID INT PRIMARY KEY,
Person_Name VARCHAR(20),
Department VARCHAR(15),
Birth DATE
);

SELECT * FROM STUDENTACCOUNT;

INSERT INTO STUDENTACCOUNT VALUES(1 , 'Ramesh' , 'Cs' , '2001/01/12');
INSERT INTO STUDENTACCOUNT VALUES(2 , 'suresh' , 'IT' , '2020/02/20');
INSERT INTO STUDENTACCOUNT VALUES(3 , 'Jomin' , 'CS' , '1996/02/29');
INSERT INTO STUDENTACCOUNT VALUES(4 , 'Shree' , 'IT' , '2012/12/18');
INSERT INTO STUDENTACCOUNT VALUES(5 , 'Hemanth' , 'CS' , '2022/02/07');

SELECT date_format(Birth , '%d/%m/%y') AS Birth FROM STUDENTACCOUNT; /* CHANGING DATE FORMAT */

/* 1. QUERY */

SELECT Person_Name , Birth , TIMESTAMPDIFF(YEAR , Birth , CURDATE()) AS AGE FROM STUDENTACCOUNT;

/* 2ND QUER */

SELECT * FROM STUDENTACCOUNT WHERE Person_Name LIKE '%S%';

/* 3RD QUERY */

SELECT * FROM STUDENTACCOUNT WHERE Person_Name LIKE '_____';

/* 4TH QUERY */

SELECT * FROM STUDENTACCOUNT GROUP BY ID HAVING MOD(ID , 2)=1;

/* 2ND QUESTION */

CREATE TABLE Employees(
Employee_id INT PRIMARY KEY,
First_name VARCHAR(20),
Last_name VARCHAR(20),
Salary INT(10),
Joining_date DATE,
Department VARCHAR(15)
);

INSERT INTO Employees VALUES(1 , 'Boby' , 'Rathod' , 1000000 , '2020/12/20' , 'Finance');
INSERT INTO Employees VALUES(2 , 'Jasmin' , 'Jose' , 6000000 , '2015/02/07' , 'IT');
INSERT INTO Employees VALUES(3 , 'Pratap' , 'Mathew' , 8900000 , '2014/03/09' , 'Banking');
INSERT INTO Employees VALUES(4 , 'John' , 'Michel' , 2000000 , '1999/03/17' , 'Insurance');
INSERT INTO Employees VALUES(5 , 'Alex' , 'Kinto' , 2200000 , '1987/02/25' , 'Finance');
INSERT INTO Employees VALUES(6 , 'Jaswanth' , 'Kumar' , 1230000 , '2021/07/23' , 'IT');

SELECT* FROM Employees;

/* 1ST QUERY */

SELECT * FROM Employees WHERE Salary BETWEEN 2000000 AND 5000000;

/* 2ND QUERY */

SELECT Last_name FROM Employees WHERE TIMESTAMPDIFF(YEAR , Joining_date , CURDATE())>3 AND First_name LIKE 'a%';

/* 3rd QUERY /
/
SELECT First_name , Last_name , Joining_date FROM Employees WHERE Salary > (SELECT avg(Salary) FROM Employees);

USING SUB QUERY
*/
SELECT avg(Salary)<Salary , Joining_date FROM Employees WHERE Department = 'IT';

/* 4th query /
/
SELECT * FROM Employees WHERE Department = 'Finance' AND Salary < (SELECT avg(Salary) FROM Employees WHERE Department = 'IT');
USING SUBQUERY
*/
SELECT * FROM Employees WHERE Department = 'Finance' AND Salary < 3615000;