Advanced sql
Advanced SQL Exercises
Exercise 1
Based on the staff and department file write SQL statements to select the following information.
a) Produce a list of casuals earning between 6 k and 8k showing their staffno, lname and
sposition
b) Produce a list of non-casuals earning between 6 k and 8k showing their staffno, lname and
sposition
c) Produce a list of casuals working in D1 showing their staffno, lname and sposition
d) Produce a list of all employees showing their staffno, lname, sposition and monthly pay
e) Produce a list of all employees showing their staffno, lname, sposition and the department
name and locations of their departments
f) Produce a list of all employees showing their staffno, lname, sposition and the department
name and locations of their departments
g) Produce a list of all employees showing their staffno, lname, sposition and the department
name for employees working in Venice
Exercise 2
The following tables represent information on a concert season, the performers who will be
playing at the concerts and details of which concerts each performer is performing in. Create the
following table
Write the SQL statements for the following queries.
a) List the names of all cello players who have participated in performances.
b) List the names of all violin players who have not participated in any performances.
c) List the names of all performers who are playing more than three pieces at any concert.
d) List the names of all concerts in which Matthews is performing.
e) List all the of all players and count the performances they have participated in
