CODEWITHSUNDEEP
sqlcount
user21902195
user21902195

Reputation: 85

Count on a database using Count function SQL

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

Upvotes: 0

Views: 232

Answers (1)

Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

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

Upvotes: 4

Related Questions