Description
Assignment:
Write the following queries in SQL, using the university schema.
1. Display the department name whose average salary in the year 2010 is greater than
50000.
2. (a) Insert a record in the table ‘Student’ with the following values
ID = ‘last 4 digits of your institute roll number’
Name = ‘Your first name’
Dept_name = ‘Comp. Sci.’
Total_credits = 12
(b) Show students between your credit and topper or least scorer’s credit (whichever
is farthest) arranged in descending order.
(c) Delete the record.
3. Display the total salary of all the advisors whose name starts with the letter K.
4. Show output as below using string queries :
5. Show output on cross joined section and course table having (ta,at,ka) in building
names,CS having course_id of section table and having department from ‘Comp. sci.’
6. Show name and salary of instructors in finance and biology in descending order of
their salary.
7. Show all the instructors with salary = Null and instructors with salary = NOT NULL.
(HINT: first set salary of 3-4 instructors as NULL).
8. Show name, three times repeated name from student table:
Eg. zhang. Zhangzhangzhang
9. Show name, length of name and grades of students with grades equal ‘A’,’B’,’A-’ .
10. Show courses where course name has ‘comp’ in it.
