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-2224', 'Rakhi',
('181-35-2224', 'Rakhi', 17,
('181-35-2225', 'Alif',
('181-35-2225', 'Alif', 22,
('181-35-2226', 'Aman',
('181-35-2226', 'Aman', 20,
('181-35-2228', 'Niloy',
('181-35-2228', 'Niloy', 23,
INSERT INTO competition (RegistrationDate, StudentId, RegistrationFee, CompetitionType) VALUES
('2022-02-12', '181-35-2222',
('2022-02-12', '181-35-2222', 300,
('2022-02-14', '181-35-2224',
('2022-02-14', '181-35-2224', 200,
('2022-02-15', '181-35-2225',
('2022-02-15', '181-35-2225', 300,
('2022-02-15', '181-35-2226',
('2022-02-15', '181-35-2226', 300,
('2022-02-15', '181-35-2226',
('2022-02-15', '181-35-2226', 250,
('2022-02-15', '181-35-2228',
('2022-02-15', '181-35-2228', 200,
('2022-02-15', '181-35-2228',
('2022-02-15', '181-35-2228', 300,
('2022-02-15', '181-35-2226',
('2022-02-15', '181-35-2226', 250,
--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
--2.
--1.Write sql statement to find
select count( StudentId) AS NumofStudents
from competition
where RegistrationDate =
--2.Write sql statement to find student name of those students whose age more than the highest age. (using sub query)
select Name
from Student
where Age
--3.Write sql statement to find the number of students who have done their registration “Mobile app”
select count(distinct StudentId) AS NumbofStu
from competition
where CompetitionType =
--4. Write sql statement to find the total registration fee collected for each
select sum(RegistrationFee) AS TotalFee , CompetitionType
from competition
GROUP BY CompetitionType;
--5.Write sql statement to find ID of those students who participated in
select distinct StudentId from competition;
--6.Write sql statement to find the student ID whose name starts with
select Studentid
from student
where Name LIKE
--7.Write sql statement to find those students information who lives in Mirpur but age should be less than
SELECT *
FROM student
WHERE Age
--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
--9.Write sql statement to find those student name whose age is greater than any age of the student who lives in
select Name
from student
where Age
--10. Delete those who lives in Ashulia
delete from student
where Address =