a. How many programmers, who are at least 30 years old, work at Cognos? b.Which employees, who are working in New York, have a salary less than $70000. c. Return True if there are programmers working in Ottawa making over $50000 & False otherwise (hint: what type of operators return Boolean values?) d. How many programmers work in Ottawa? e. Return true if there are more managers in Chicago than in Ottawa and False

laurelsandwichSoftware and s/w Development

Nov 25, 2013 (3 years and 6 months ago)

72 views



employee

=
{"dave","steve","John","mary","diane","nadia","bill","hillary","jimi"}

company


= {"ibm","ford","cognos","cibc","dow jones","nortel"}

occupation

= {"engineer","programmer","analyst","manager"}

age


= { n | 16 < n < 100 }

location


= {"ottaw
a","windsor","detroit","toronto","chicago","new york"}

salary



= { n | 20000 < n < 200000 }

























a. How many programmers, who are at least 30 years old, work at Cognos?

b.Which employees, who are working in New York, have a salary le
ss than
$70000.

c. Return True if there are programmers working in Ottawa making over
$50000 & False otherwise

(hint: what type of operators return Boolean values?)

d. How many programmers work in Ottawa?

e. Return true if there are more managers in Chic
ago than in Ottawa and False
otherwise.

f. What is the highest salary of an engineer?

g. Return True if the average salary of analysts is higher than that of managers
and False otherwise

h. At which company does the employee with the highest salary work?



employee

occupation


age


company


"dave"

"engineer"

32

"ford"

"mary"

"programmer
"

27

"cognos"

"bill"

"programmer
"

36

"ibm"

"nadia"

"analyst"

27

"cibc"

"steve"

"analyst"

40

"cognos"

"jimi"

"programmer
"

31

"cognos"

"john"

"manager"

42

"dow jones"

"diane"

"engineer"

27

"nortel"

"hillary"

"analyst"

27

"dow jones"

employee_rel



company


location


"ibm"

"chicago"

"ford"

"detroit"

"cognos"

"ottawa"

"cibc"

"windsor"

"dow jone
s"

"new
york"

"nortel"

"toronto"

company_rel


employee salary


"dave"

45000

"mary"

52000

"bill"

36000

"nadia"

61000

"jimi"

75000

"steve"

43000

"john"

52000

"diane"

50000

"hillary"

83000


employee_salary


a. #(select_on_2
nd
_of_4 "programmer"

(select_on_4
th
_of_4 "Cognos"

(select_on_3
rd
_of_4 (> 30) employee_rel)))


b.
project_unique_on_1
st
_of_6



(join_on_1
st
_of_5_with_1
st
_of_2




(join_on_4
th
_of_4_with_2
nd
_of_2 employee_rel





(select_on_2
nd
_of_2
"new york" company_rel))


(select_on_2
nd
_of_ (> 70000) employee_salary))


c. #(join_on_1
st
_of_5_with_1
st
_of_2



(join_on_4
th
_of_4_with_1st_of_2

(select_on_2
nd
_of_4 "programmer" employee_rel)





(select_on_2
nd
_of_2 "ottawa" company_rel))




(select_
on_2
nd
_of_ (> 50000) employee_salary))



> 0


d. #(join_on_4
th
_of_4_with_2
nd
_of_2

(select_on_2
nd
_of_4 "programmer" employee_rel)




(select_on_2
nd
_of_2 "ottawa" company_rel))

e. managers =

(select_on_2
nd
_of_5 "manager"




(join_on4th_of_4_with_1
st
_of_2

employee_rel company_rel))


#(select_on_5
th
_of_5 "chicago" managers) > #(select_on_5
th
_of_5 "ottawa" managers)


f. engineers = select_on_2
nd
_of_4 "engineer"


engineering_salaries =


project_on_5
th
_of_5

(join_on_1
st
_of_4_with_1
st
_of_2 engineers emplo
yee_salary)


max (engineering_salaries)


g. manager_salaries =


project_on_5
th
_of_5

(join_on_1
st
_of_4_with_1
st
_of_2 managers employee_salary)


analyst_salaries =


project_on_5
th
_of_5

(join_on_1
st
_of_4_with_1
st
_of_2 analysts employee_salary)


manager
s = select_on_2
nd
_of_4 "manager" employee_rel

analysts = select_on_2
nd
_of_4 "analyst" employee_rel


(sumlist analyst_salaries/#analyst_salaries)



(sumlist manager_salaries/#manager_salaries)


Bonus:


max2 [(a,b)] = (a,b)

max2 ((a,b):rest) = (a,b), if b >
second (max2 rest)





= max2 rest, otherwise


project_unique_on_4
th
_of_5


(join_on_1
st
_of_4_with_1
st
_of_2 employee_rel (max2 employee_salary))