Teacher (ID, name, dept_ name, salary) student (ID, name, dept_ name, tot_ cred)

Question

Teacher (ID, name, dept_ name, salary) student (ID, name, dept_ name, tot_ cred) teaches(ID, course id, sec id, semester, year) course(course id, title, dept_ name, credits) section (course id, sec id, semester, year, building, room_ number, time_ slot_ id) Using above schemas, provide the SQL Statements for the following questions: Find all teachers earning the highest salary. Find the IDs and names of all students who were taught by a teacher named "Mujo". Make sure there are no duplicate results. Increase the salary of each instructor in the "Computer Science" department by 15%. List all the courses with the details that have never been offered (that is, do not occur in the section relation). Student (nr, town, birth date, sex, name, surname) (Sex is stored as '0' for female and '1' for male) Course(cnr, name, Inr) Enrolled in(nr, cnr) Lecturer (Inr, name, surname) Exam (nr, cnr, nr, score) Give an expression in SQL for each of the following queries based on the given schemas above: Get the name and surname of all students who were born before 1985. Get the course name, name, surname and note of students who take courses offered by Prof. Mehmed Can.. Get the nr of students who have the same sex as and live in the same town as student nr 20. Get the print all course names which are taken by students who come from Sarajevo. Print all student names, if they are taking some courses also print courses name. Get the names of students whose first letter of their name is A, B, C.

Explanation / Answer

1. For the first database,

1. SELECT id,name,dept_name salary FROM teacher WHERE salary = (SELECT MAX(salary) FROM teacher);

2.How do we know which student took which courses? i couldnt understand from the db, its not clear.

3. update teacher set salary=salary+0.15*salary where dept_name='Computer Science';

4. select * from course where course_id not in(select distinct course_id from section);

2. For second database,

5.SELECT name,surname FROM student WHERE birthdate < '1985-01-01';

6.select name,surname from Student a,Enrolled b in where cnr in (select cnr from course where lnr in (select lnr from lecturer where name='Can' and surname='Mehmed') and a.nr=b.nr;

7.select nr from student where sex=(select sexfrom student where nr=20) and town=(select town from student where nr=20);

8.select name from course where cnr in (select cnr from Enrolledin where nr in (select nr from Student where town'Sarajevo') );

9.select * from Student where nr in (select nr from Enrolledin) ;

10.select name from Student where name like 'A%' or name like '%B' or name like '%C';

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.