create database Employees;
use Employees;
CREATE TABLE student (
    StudentId VARCHAR(20) PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Address VARCHAR(100)
);

CREATE TABLE competition (
    RegistrationDate DATE,
    StudentId VARCHAR(20),
    RegistrationFee DECIMAL(10, 2),
    CompetitionType VARCHAR(50),
    FOREIGN KEY (StudentId) REFERENCES student(StudentId)
);



INSERT INTO student (StudentId, Name, Age, Address) VALUES
('181-35-2222', 'Raihan', 18, 'Mirpur'),
('181-35-2224', 'Rakhi', 17, 'Ashulia'),
('181-35-2225', 'Alif', 22, 'Mirpur'),
('181-35-2226', 'Aman', 20, 'Ashulia'),
('181-35-2228', 'Niloy', 23, 'Dhanmondi');



INSERT INTO competition (RegistrationDate, StudentId, RegistrationFee, CompetitionType) VALUES
('2022-02-12', '181-35-2222', 200, 'Business Idea'),

('2022-02-12', '181-35-2222', 300, 'Mobile App'),
   ('2022-02-14', '181-35-2224', 200, 'Business Idea'),
('2022-02-15', '181-35-2225', 300, 'Mobile App'),
    ('2022-02-15', '181-35-2226', 300, 'Mobile app'),
('2022-02-15', '181-35-2226', 250, 'Robotics Competition'),

('2022-02-15', '181-35-2228', 200, 'Business Idea'),

('2022-02-15', '181-35-2228', 300, 'Mobile App'),

('2022-02-15', '181-35-2226', 250, 'Robotics Competition');


--1.Write sql statement to find the number of students who did register  in 15th February 2022.

select * from competition;
select avg(RegistrationFee) AS AvgFee 
from competition;


--2.Write sql statement to find student name of those students whose age more than the highest age. (using sub query)


select count( StudentId) AS NumofStudents
from competition
where RegistrationDate = '2022-02-15';




select Name 
from Student
where Age >(select MAX(Age) from Student);

--3.Write sql statement to find the number of students who have done their registration “Mobile app” competition.


select count(distinct StudentId) AS NumbofStu
from competition
where CompetitionType  = 'Mobile App' ;

--4. Write sql statement to find the total registration fee collected for each competition.


select sum(RegistrationFee) AS TotalFee , CompetitionType
from competition
GROUP BY CompetitionType;

--5.Write sql statement to find ID of those students who participated in competition.


select distinct StudentId from competition;

--6.Write sql statement to find the student ID whose name starts with ‘N’
select Studentid 
from student
where Name LIKE 'N%';


--7.Write sql statement to find those students information who lives in Mirpur but age should be less than 22.


SELECT *
FROM student
WHERE  Age < 22 AND  Address = 'Mirpur';

--8. Write sql statement to find Address of those students who participate in robotics competition. (using sub query)

Select Address
from Student
where StudentId IN(
   select distinct StudentId
   from competition
    where Competiti Competition" );
   
   --9.Write sql statement to find those student name whose age is greater than any age of the student who lives in Mirpur. (using subquery)

   
   select Name 
   from student
   where Age > ANY (Select Age from student where Address = "Mirpur" );
   
   --10. Delete those who lives in Ashulia
   
   delete from student
   where Address = "Ashulia" ;