1. create database Employees;
2. use Employees;
3. CREATE TABLE student (
4.     StudentId VARCHAR(20) PRIMARY KEY,
5.     Name VARCHAR(50),
6.     Age INT,
7.     Address VARCHAR(100)
8. );
9.  
10. CREATE TABLE competition (
11.     RegistrationDate DATE,
12.     StudentId VARCHAR(20),
13.     RegistrationFee DECIMAL(10, 2),
14.     CompetitionType VARCHAR(50),
15.     FOREIGN KEY (StudentId) REFERENCES student(StudentId)
16. );
17.  
18. INSERT INTO student (StudentId, Name, Age, Address) VALUES
19. ('181-35-2222', 'Raihan', 18, 'Mirpur'),
20. ('181-35-2224', 'Rakhi', 17, 'Ashulia'),
21. ('181-35-2225', 'Alif', 22, 'Mirpur'),
22. ('181-35-2226', 'Aman', 20, 'Ashulia'),
23. ('181-35-2228', 'Niloy', 23, 'Dhanmondi');
24.  
25. INSERT INTO competition (RegistrationDate, StudentId, RegistrationFee, CompetitionType) VALUES
26. ('2022-02-12', '181-35-2222', 200, 'Business Idea'),
27.  
28. ('2022-02-12', '181-35-2222', 300, 'Mobile App'),
29.    ('2022-02-14', '181-35-2224', 200, 'Business Idea'),
30. ('2022-02-15', '181-35-2225', 300, 'Mobile App'),
31.     ('2022-02-15', '181-35-2226', 300, 'Mobile app'),
32. ('2022-02-15', '181-35-2226', 250, 'Robotics Competition'),
33.  
34. ('2022-02-15', '181-35-2228', 200, 'Business Idea'),
35.  
36. ('2022-02-15', '181-35-2228', 300, 'Mobile App'),
37.  
38. ('2022-02-15', '181-35-2226', 250, 'Robotics Competition');
39.  
40.  
41. select * from competition;
42. select avg(RegistrationFee) AS AvgFee
43. from competition;
44.  
45. --1.Write sql statement to find the number of students who did register  in 15th February 2022.
46.  
47.  
48. select count( StudentId) AS NumofStudents
49. from competition
50. where RegistrationDate = '2022-02-15';
51.  
52. --2.Write sql statement to find student name of those students whose age more than the highest age. (using sub query)
53.  
54. select Name
55. from Student
56. where Age >(select MAX(Age) from Student);
57.  
58. --3.Write sql statement to find the number of students who have done their registration “Mobile app” competition.
59.  
60.  
61. select count(distinct StudentId) AS NumbofStu
62. from competition
63. where CompetitionType  = 'Mobile App' ;
64.  
65. --4. Write sql statement to find the total registration fee collected for each competition.
66.  
67. select sum(RegistrationFee) AS TotalFee , CompetitionType
68. from competition
69. GROUP BY CompetitionType;
70.  
71. --5.Write sql statement to find ID of those students who participated in competition.
72.  
73.  
74. select distinct StudentId from competition;
75.  
76. --6.Write sql statement to find the student ID whose name starts with ‘N’
77.  
78. select Studentid
79. from student
80. where Name LIKE 'N%';
81.  
82. --7.Write sql statement to find those students information who lives in Mirpur but age should be less than 22.
83.  
84.  
85. SELECT *
86. FROM student
87. WHERE  Age < 22 AND  Address = 'Mirpur';
88.  
89.  
90. --8. Write sql statement to find Address of those students who participate in robotics competition. (using sub query)
91.  
92. Select Address
93. from Student
94. where StudentId IN(
95.    select distinct StudentId
96.    from competition
97.     where Competiti Competition" );
98.    
99.    --9.Write sql statement to find those student name whose age is greater than any age of the student who lives in Mirpur.
100.    
101.    select Name
102.    from student
103.    where Age > ANY (Select Age from student where Address = "Mirpur" );
104.    
105.    --10. Delete those who lives in Ashulia
106.    
107.    delete from student
108.    where Address = "Ashulia" ;