Count on a database using Count function SQL

Question

I have the database schema like this

Flights(flno,from,to,distance,departs,arrives,price)
Aircraft(aid,aname,cruisingRange)
Certified(employee,aircraft)
Employees(eid,ename,salary)

Where Flno is primary key and Each route corresponds to a "flno".

So I have this question to answer for the Schema

For each pilot, list their employee ID, name, and the number of routes he can pilot.

I have this SQL, is this correct? ( I can test, as I dont have data for the database).

 select eid, ename, count(flno) 
 from employees, flights 
 groupby flno

No, it's not quite right because you don't have any JOIN criteria. This will give you the Cartesian product of all employees and flights. You would discover this if you tested it. You can test it by inserting data into the tables and then analyzing the results of the query. — Brandon
– Brandon, Commented Apr 27, 2015 at 12:26
Your schema is missing a relation between the flights and the aircrafts — juergen d
– juergen d, Commented Apr 27, 2015 at 12:26
What are your foreign keys between the tables? Is Certified.employee related to Employees.eid or Employees.ename? Is Certified.aircraft related to Aircraft.aid or Aircraft.aname? I'm guessing the id columns? — Eilidh
– Eilidh, Commented Apr 27, 2015 at 12:29
Also, the column names are a bit hard to read. It's clear that aid means aircraft_id when the same line above shows it's on the aircraft table, but when you have a query joining several tables, aid is going to get confusing to anyone with poor short-term memory (like me). Couldn't these be named aircraft_id, employee_id, etc? — Brandon
– Brandon, Commented Apr 27, 2015 at 12:29
There is nothing in Flight that links to Employee (nor aircraft). How can you expect to do what you are asking? — paparazzo
– paparazzo, Commented Apr 27, 2015 at 12:31

Ryan-Neal Mes · Accepted Answer · 2015-04-30 10:42:45Z

4

This is a simple questioin, but as everyone is mentioning you don't have any link between employee and flights. The relationships stop at certified.

You obviously have or will create some relationship. I have written a query that will give you the count taking into account that you will have a many to many relationship between employee and flights. Meaning an employee can have many flights and a single flight can be made by many employees.

Flights(flno,from,to,distance,departs,arrives,price) Aircraft(aid,aname,cruisingRange) Certified(employee,aircraft) Employees(eid,ename,salary)

select
  e.eid employee_id,
  e.ename employee_name,
  count(*)
from
  employees e 

  inner join certified c on
    c.employee = e.eid

  inner join aircraft a on
    a.aid = c.aircraft

  inner join aircraft_flights af on -- new table that you would need to create
    af.aircraft = a.aid and

  inner join flights f on
    f.flno = af.flno -- not I made up a relationship here which needs to exist in some for or another
group by
  e.eid,
  e.ename

I hope this at least shows you how to write a count statement correctly, but you should probably brush up on your understanding of joins.

Hope that helps.

EDIT

Without the relationships and working in your comments you could get the count as below.

select
  e.eid employee_id,
  e.ename employee_name,
  count(*)
from
  employees e 

  inner join certified c on
    c.employee = e.eid

  inner join aircraft a on
    a.aid = c.aircraft

  inner join flights f on
    f.distance <= a.cruisingRange
group by
  e.eid,
  e.ename

edited Apr 30, 2015 at 10:42

answered Apr 27, 2015 at 12:42

Ryan-Neal Mes

6,28311 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user21902195 Over a year ago

hey, Thanks for your answer. But I m thinking like this Link employees.eid with certified.employee then Link certified.aircraft with aircraft.aid Then find fights where flights.distance <= aircraft.cruisingRange. Because i ve been told A ploit is ok to pilot a route if the cruisingRange >= Flights.distance. Any advice on this?

Ryan-Neal Mes Over a year ago

Sure will just adjust answer quick.

Collectives™ on Stack Overflow

Count on a database using Count function SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related