Task1_Student.sql

create database Task1;
use Task1;

--1.Creates the table with name student with columns id,name,age,department,cgpa,city
create table students(
Id int,
Stdname varchar(255),
Age int,
Department varchar(255),
CGPA float,
City varchar(255),
);

--2.Inserts the different records in student table
insert into student(id,stdname,age,department,CGPA,city)  
values(1,'Nayyab',20,'Computer Science',4.0,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(2,'Laiba',21,'Computer Science',3.9,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(3,'Abeera',24,'Software Engineering',3.91,'Sialkot');

insert into student(id,stdname,age,department,CGPA,city)  
values(4,'Fatima',19,'Computer Science',3.81,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(5,'Rubab',21,'Computer Science',3.72,'Gujranwala');

insert into student(id,stdname,age,department,CGPA,city)  
values(6,'Orwa',20,'Software Engineering',4.0,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(7,'Eman',19,'Cyber Security',3.5,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(8,'Nabia',20,'Software Engineering',3.71,'Karachi');

insert into student(id,stdname,age,department,CGPA,city)  
values(9,'Ayesha',21,'Computer Science',3.7,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(10,'Mahnoor',19,'Computer Science',3.87,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(11,'Noor',22,'Data Science',3.5,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(12,'Maheen',20,'Data Science',3.8,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(13,'Arifa',21,'Computer Science',3.9,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(14,'Rafia',22,'Data Science',3.86,'Karachi');

insert into student(id,stdname,age,department,CGPA,city)  
values(15,'Dania',22,'Software Engineering',3.4,'Sialkot');

insert into student(id,stdname,age,department,CGPA,city)  
values(16,'Zarmina',20,'Computer Science',3.45,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(17,'Rameen',21,'Information Technology',3.52,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(18,'Hina',20,'Information Technology',3.98,'Karachi');

insert into student(id,stdname,age,department,CGPA,city)  
values(19,'Raimal',19,'Information Technology',3.3,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(20,'Sehar',21,'Data Science',3.1,'Islamabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(21,'Malaika',20,'Computer Science',3.2,'Lahore');

insert into student(id,stdname,age,department,CGPA,city)  
values(22,'Ajwa',19,'Cyber Security',3.53,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(23,'Tehreem',20,'Cyber Security',3.65,'Karachi');

insert into student(id,stdname,age,department,CGPA,city)  
values(24,'Maira',21,'Computer Science',3.0,'Faisalabad');

insert into student(id,stdname,age,department,CGPA,city)  
values(25,'Kainat',19,'Cyber Security',3.6,'Islamabad');

--3.Sql query to retrieve/select all columns from student table
select *from student;

--4.Sql query to fetch and display different departments
select distinct department from student;  

--5.Sql query to fetch and display all students who have CGPA greater than 3.5
select stdname, cgpa from student
where CGPA>3.5;

--6.Sql query to display students details sorted by Names in ascending order
select *from student
order by stdname asc;

--7.Sql query to retrieve students who are from either lahore or Faisalabad
--we use OR operator here 
select *from student
where city = 'lahore' OR city = 'Faisalabad';


--8.Sql query to find students aged bewtween 20 and 22
select *from student
where age between 18 AND 20;

--9.Sql query to fetch all students except those from Computer Science Department
select *from student
where department NOT IN ('computer science');

select *from student
where not department = 'Software engineering';