Question

Write SQL queries for:

1. To display name, fee, gender, joinyear about the applicants, who have joined before 2010.

2. To display names of applicants, who are playing fee more than 30000.

3. To display names of all applicants in ascending order of their joinyear.

4. To display the year and the total number of applicants joined in each YEAR from the table APPLICANTS.

5. To display the C_ID (i.e., CourselD) and the number of applicants registered in the course from the APPLICANTS and table.

6. To display the applicant’s name with their respective course’s name from the tables APPLICANTS and COURSES.

7. Give the output of following SQL statements:

• SELECT Name, Joinyear FROM APPLICANTS WHERE GENDER=’F’ and C_ID=’A02′;
• SELECT MIN (Joinyear) FROM APPLICANTS WHERE Gender=’m’;
• SELECT AVG (Fee) FROM APPLICANTS WHERE C_ID=’A0T OR C_ID=’A05′;
• SELECT SUM- (Fee), C_ID FROM C_ ID GROUP BY C_ID HAVING COUNT(*)=2;

Answer 1

1. SELECT NAME,FEE,GENDER,JOINYEAR 

FROM APPLICANTS 

WHERE J OINYE AR <2010

2. SELECT NAME FROM APPLICANTS WHERE FEE >30000

3. SELECT NAME FROM APPLICANTS ORDERBY JOINYEAR ASC

4. SELECT YEAR, COUNT]*) FROM 

APPLICANTS GROUP BY YEAR;

5. SELECT C_ID, COUNT]*) FROM 

APPLICANTS, COURSES GROUP BY ID 

WHERE APPLICANTS.C_ID=COURSES. C_ID

6. SELECT NAME,COURSE FROM APPLICANTS, COURSES 

WHERE APPLICANTS. C_ID=COURSES. C_ID

• Avisha 2009
• 2009
• 67
• 55000 A01