in Databases recategorized by
5,192 views
17 votes
17 votes

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.)

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

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

in Databases recategorized by
5.2k views

3 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

1
1
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
0
0
GO PDF correction:

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

3 Answers

35 votes
35 votes
Best answer
  1. $π_{rollno}(σ_{cno.}=_{322}$(REGISTERED_FOR))
  2. 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). 

edited by

4 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

1
1
What would be the relational Algebra query for option (ii) would look like?????
0
0
0
0
4 votes
4 votes
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)
0 votes
0 votes

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.

3 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 ...
0
0
what is your answr.? @pujaa
0
0
Same as best answer .... hav u run that query ??
0
0

Related questions