Notes

CREATE TABLE Member
(
RollNo INT,
Fname VARCHAR(25),
Mname VARCHAR(25),
Sname VARCHAR(25),
DeptID INT,
Semester INT,
ContactNo INT,
Gender CHAR(1),
PRIMARY KEY (RollNo),
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

CREATE TABLE Department
(
DeptID INT,
DeptName VARCHAR(25),
PRIMARY KEY (DeptID)
);

INSERT INTO `NIKHIL4007`.`Department` (`DeptID`, `DeptName`) VALUES('1','Information Technology')('2','Electrical') ('3', 'Civil'), ('4', 'Mechanical'),('5', 'Chemical');

INSERT INTO Member VALUES (1,'Ankur','Samir','Kahar',1,1,272121,'M');
INSERT INTO Member VALUES (2,'Dhaval','Dhiren','Joshi',1,1,232122,'M');
INSERT INTO Member VALUES (3,'Ankita','Biren','Shah',1,1,112121,'F');
INSERT INTO Member VALUES (10,'Komal','Maheshkumar','Pandya',2,3,123123,'F');
INSERT INTO Member VALUES (13,'Amit','Jitenkumar','Mehta',3,3,453667,'M');
INSERT INTO Member VALUES (23,'Jinal','Ashish','Gandhi',2,1,323232,'M');
INSERT INTO Member VALUES (22,'Ganesh','Asha','Patel',2,3,124244,'M');
INSERT INTO Member VALUES (4,'Shweta','Mihir','Patel',3,1,646342,'F');
INSERT INTO Member VALUES (7,'Pooja','Mayank','Desai',3,3,328656,'F');
INSERT INTO Member VALUES (8,'Komal','Krishnaraj','Bhartia',2,3,257422,'F');
INSERT INTO Member VALUES (43,'Kiran','Viraj','Shah',1,1,754124,'F');


---> SELECT Fname,Mname,Sname,ContactNo FROM Member;

---> SELECT Fname,Mname,Sname,RollNo FROM Member
JOIN Department ON Member.DeptID = Department.DeptID
WHERE Member.DeptID = Department.DeptID
AND Department.DeptName='Information Technology' ;

---> SELECT DISTINCT DeptName FROM Department
JOIN Member ON Member.DeptID = Department.DeptID
WHERE Member.DeptID = Department.DeptID;

---> SELECT DISTINCT DeptName FROM Department WHERE Department.DeptName
NOT IN (SELECT DISTINCT DeptName FROM Department
JOIN Member ON Member.DeptID = Department.DeptID
WHERE Member.DeptID = Department.DeptID);

---> SELECT DeptName FROM Department;

---> SELECT COUNT(RollNo) FROM Member WHERE DeptID = 2;

---> SELECT * FROM Member WHERE Fname LIKE 'A%';

---> SELECT * FROM Member WHERE Gender='M';

---> SELECT * FROM Member WHERE Semester = 3;

---> SELECT * FROM Member WHERE Gender = 'F' ORDER BY Fname ASC;