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');


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



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


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



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


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

select distinct StudentId from competition;

select Studentid 
from student
where Name LIKE 'N%';

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


Select Address
from Student
where StudentId IN(
   select distinct StudentId
   from competition
    where Competiti Competition" );
   
   select Name 
   from student
   where Age > ANY (Select Age from student where Address = "Mirpur" );
   
   
   delete from student
   where Address = "Ashulia" ;