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