CS5481 Data Engineering
Basic use of SQL (that you should have learnt)
Tutorial 1
A few 1.
2.
3.
4.
5.
6.
7.
examples of using SQL to retrieve data from the university: Find the names of all instructors.
select name from instructor;
Find the instructor names and the ID of courses they taught for all instructors who have
taught some courses.
select name, course_id from instructor, teaches
where instructor.ID=teaches.ID;
Find the instructor names and the ID of courses they taught for all instructors in the
Computer Science department who have taught some courses. select name, course_id from instructor, teaches
where instructor.ID=teaches.ID and dept_name=¡¯Computer Science¡¯; Find the ID of all courses taught in Semester A 2015 but not in Semester B 2014.
(select course_id from section where semester=¡¯A¡¯ and year=2015) except
(select course_id from section where semester=¡¯B¡¯ and year=2014);
Find the average salary of instructors in the Computer Science department.
select avg(salary) from instructor where dept_name=¡¯Computer Science¡¯;
Find the average salary of instructors in each department.
select dept_name, avg(salary) from instructor group by dept_name;
Display a list of all students, with their ID, name, dept_name and tot_cred, along with the courses that they have taken.
select * from student natural join takes;
How to include those students who have not taken any courses in the list? select * from student natural left outer join takes;