SQL Exercises

Tables:

• course
• department
• empcourse
• employee
• jobhistory

Showing the contents of the tables

>> select * from course;

>> select * from department;

>> select * from empcourse;

>> select * from employee;

Simple selects

What is Robert Smith's address?

select address from employee where surname='Smith' and forenames='Robert';

What is Allan White's phone number?

select telno from employee where surname='White' and forenames='Allan';

Where is the engineering department?

??select location from department where dname='engineering';

What department is on the second floor?

??select dname from department where location='floor 2';

What are the phone numbers of people with last name Smith or Jones?

??select surname, forenames, telno from employee where surname='Jones' or surname='Smith';

Complex Joins

What department is Robert Smith in?

select dname from employee, department
where employee.depno=department.depno and surname='Smith' and forenames='Robert';

Who's in accounts?

select surname, forenames from employee, department
where employee.depno=department.depno and dname='accounts';

What floor does Gordon Reid work on?

??select location from employee, department
where employee.depno=department.depno and surname='Reid' and forenames='Gordon';

Give me the names and phone numbers of everyone who works on the first floor.

??select surname, forenames, telno from employee, department
where employee.depno=department.depno and location='floor 1';

Who's taking Ada?

??select surname, forenames from course, empcourse, employee
where empcourse.courseno=course.courseno and empcourse.empno=employee.empno and cname='Ada';

What courses are Robert Smith taking?

??select cname from course, empcourse, employee
where empcourse.courseno=course.courseno and empcourse.empno=employee.empno and surname='Smith' and forenames='Robert';

What courses are the people from the second floor taking?

??select surname, forenames, cname from course, empcourse, employee, department where department.depno=employee.depno and empcourse.courseno=course.courseno and empcourse.empno=employee.empno and location='floor 2';