SQLTeam.com | Weblogs | Forums

Request for solution for below queries, m fresher

I need solutions for below queries. M a fresher and finding it difficult to solve

Below are the queries
6. Every teacher taking a class will interact with all the students in that class. Write a query to identify the standard wise number of student each teacher interacts with

  1. Every question paper for an exam will have contribution from all the teachers who are teaching the corresponding standard and subject. Write a query to identify the number of question papers each teacher contributed to
  2. The principal of the school wants to recognize the teacher by ranking them based on their contribution (number of classes taught). Write a query to assign ranks (continuous) to teachers, who are teaching more than 2 classes, based on their contributions.
  3. There are multiple exams that each student must take throughout the year. There is a chance that a student might miss few exams. Write a query to identify the students who missed any exam in ‘Annual Exam and number of exams missed.
  4. For every exam class teacher must assign grades to their students based on the % of the marks they have obtained in the exam. Write a query to allocate “Annual” exam grade to each student based on the below criteria
    Grade A >= 90%
    Grade B >= 75% and <90
    grade C >= 60% and < 40
    Grade F <40
  5. Class teacher must keep track of the cumulative marks obtained (in exam) over the year for each student in their class. Our teachers forgot to keep track of this data and need your help. Write a query to provide the cumulative marks obtained till each exam (including that exam) for every student
    Note: Exams are created in order Ex: Quarterly followed by Half-yearly, followed by Annual
  6. Every class must be assigned teachers for all the subject present in the curriculum of the standard. There is a chance that no teacher is assigned for a subject in the curriculum. Write a query that identifies the class and subject for which teacher is not assigned.

Welcome to forum

Provide sample data in the form of ddl dml

CREATE table teachers with all columns

Insert into teachers

Looks like homework to me. What have you tried?

1 Like

I am new to this site and m still learning sql. :slight_smile:

I am new to this site. I dint have option to attach the file. so had to attach the word file. I was only allowed to attach 1 file

We need DDL, not a word file. Like Create table A (col1, col2), etc... Then insert into A values (....). Real data we can work with. This is a volunteer site, we don't have time to create all of this for you

so we need sample data and for this we ask that you please provide your data in the following format. This takes a lot of time if we had to do it and folks are busy.

so provide your data in the following format

--the following is called DDL  data definition language
create table #student_table(student_id int, student_name varchar(50), 
class_id int, gender char(1), date_of_birth date)

--this part is called DML - data manipulation language 
insert into #student_table
select 1, 'Charan Tej', 2, 'M, '1995-03-05' union
select 2, 'Danush Koti', 1, 'M, '1995-04-00' 

you will need to do this for all of the tables. As you can see this will take us a long time hence you need to do the hard work and it will help expedite the answer you need.

Thanks I will be attaching that soon
Thanks for quick reply

By the way, your use of "m" as a separate word threw me at first. This is not valid English. I'm guessing you're using it as it's used in the contraction "I'm", thinking it would be understood alone. Not wanting to nitpick but just to help you achieve clear communication.