More Practice Questions

download surveys SQLite database file

CREATE TABLE person(
    person_id text not null primary key,
    personal text not null,
    family text not null,
    supervisor_id text,
    foreign key(supervisor_id) references person(person_id)
);

CREATE TABLE survey(
    survey_id text not null primary key,
    person_id text not null,
    start_date text,
    end_date text,
    foreign key(person_id) references person(person_id)
);

CREATE TABLE machine(
    machine_id text not null primary key,
    machine_type text not null
);

CREATE TABLE rating(
    person_id text not null,
    machine_id text not null,
    level integer,
    foreign key(person_id) references person(person_id),
    foreign key(machine_id) references machine(machine_id)
);

1. Show each person's first and last name.

2. Count the number of people.

3. Show people with no supervisor.

4. Show each person's full name and their supervisor's full name.

5. Find surveys with no end date.

6. Show surveys with their owner's name.

7. Show distinct machine types.

8. Count machines by type.

9. Find ratings above level 1.

10. Show who has a rating for which machine.

11. Calculate each person's average rating on all machines.

12. Show people who have at least one machine rating.

13. Show machines that no one has a rating for.

14. Show all people who supervise someone else.

15. Show how many surveys each person is associated with.

16. Show people who do not have ratings for any machines.

17. Find machines that have not been rated by person P001.

18. Find supervisors who are not associated with any surveys.

19. Show people who have ratings for more than one type of machine.

20. Find the highest rating that anyone has on any kind of machine.

21. Show all of the people who have that highest rating for any kind of machine.

22. Show the length in days of each survey.

23. Show the total length of time that each person has spent doing surveys.

24. Find the longest time between surveys for each person.