GATE CSE 1994 | Question: 13

Question

Consider the following relational schema:

• COURSES (cno, cname)
• STUDENTS (rollno, sname, age, year)
• REGISTERED_FOR (cno, rollno)

The underlined attributes indicate the primary keys for the relations. The ‘year’ attribute for the STUDENTS relation indicates the year in which the student is currently studying (First year, Second year etc.)

• A. Write a relational algebra query to print the roll number of students who have registered for cno $322.$

• B. Write a SQL query to print the age and year of the youngest student in each year.

Comments

Proper underlines according to official question paper:

COURSES(cno, cname)

STUDENTS(rollno, sname, age, year)

REGISTERED_FOR(cno, rollno)

source : https://drive.google.com/file/d/0By-VZ5fZo_6iNHhYNW5QYXZFUVE/view

---

Is the following query correct?

SELECT age, year

FROM Students S1, Students S2

WHERE S1.age<=ALL(SELECT age FROM S2 where S1.rollno <> S2.rollno AND S1.year = S2.year)

GROUP BY year

---

GO PDF correction:

Underline are neither in PDF nor in GO-BOOK as well. (Which Highlights the primary key).

---

SELECT age,year

FROM students

GROUPBY age

HAVING MIN(age)

Answer 1

• A. $π_{rollno}(σ_{cno.}=_{322}$(REGISTERED_FOR))
• B. SELECT year, min(age) FROM STUDENTS GROUP BY year

In the second question we have to find the year and youngest student from that year. So, we have to apply MIN aggregate function on each year (group by year). 

Comments

@richadwivedi12 We can find the relational algebra query for (b). Below is my approach 

I assumed that we know the distinct year in advance.

We then calculate the minimum age for each year and then we union all of them to get the result.

If you something better please tell

---

What would be the relational Algebra query for option (ii) would look like?????

---

@SomeEarth .

Answer 2

Select  S.age,S.year

From  Student as S

Where  S.age <= all ( Select E.age

                                       From Student as E

                                       Where S.year=E.year)

Answer 3

1).  (a)π_{roll_no.}(σ_cno.=322(registered for))

2.) select age , year from student s1 where not exists(select s2.age from student s2 where s2.age > s1.age) group by year.

Comments

I think ur query is wrong ... it will select those students of every year who is smaller than the age of older student in their respective year ...

---

what is your answr.? @pujaa

---

Same as best answer .... hav u run that query ??