recategorized by
6,269 views
18 votes
18 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.

recategorized by

3 Answers

Best answer
37 votes
37 votes
  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
5 votes
5 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.

Related questions

26 votes
26 votes
5 answers
1
Kathleen asked Oct 5, 2014
6,875 views
Give a relational algebra expression using only the minimum number of operators from $(∪, −)$ which is equivalent to $R$ $∩$ $S.$
5 votes
5 votes
5 answers
2
gatecse asked May 2, 2021
3,415 views
For a $B^+$ - tree of order $d$ with $n$ leaf nodes, the number of nodes accessed during a search is $O(\_)$.
29 votes
29 votes
1 answer
3
Kathleen asked Oct 5, 2014
7,338 views
Consider $B^+$ - tree of order $d$ shown in figure. (A $B^+$ - tree of order $d$ contains between $d$ and $2d$ keys in each node)Draw the resulting $B^+$ - tree after $10...