public List <EMPLOYEE> getAll()
{
using (DB2Entities context = new DB2Entities())
{
return(context.EMPLOYEE.ToList());
}
}
public List <DEPARTMENT> query02()
{
using (DB2Entities context = new DB2Entities())
{
return(context.DEPARTMENT.Take(5).ToList());
}
}
public dynamic query51()
{
using (DB2Entities context = new DB2Entities())
{
DateTime date = Convert.ToDateTime("15/10/1982");
var qry = (from ea in context.EMP_ACT
join p in context.PROJECT
on ea.PROJNO equals(p.PROJNO)
join e in context.EMPLOYEE on
ea.EMPNO equals(e.EMPNO)
join d in context.DEPARTMENT on
e.WORKDEPT equals(d.DEPTNO)
where ea.EMSTDATE >= date
orderby ea.ACTNO, ea.EMSTDATE
select new
{
ea.ACTNO,
d.MGRNO,
ea.EMSTDATE,
p.PROJNO,
e.LASTNAME,
}).ToList();
return(qry);
}
}
public dynamic query20()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
orderby d.EDLEVEL descending
select new
{
d.EDLEVEL
}).Distinct().ToList();
return(qry);
}
}
public dynamic query17()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.LASTNAME.Contains("G")
select new
{
d.LASTNAME
}).ToList();
return(qry);
}
}
public dynamic query05()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.DEPARTMENT
orderby d.ADMRDEPT ascending, d.DEPTNAME descending select new
{
d.DEPTNO,
d.DEPTNAME,
d.ADMRDEPT
}).ToList();
return(qry);
}
}
public dynamic query09()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.EDLEVEL >= 19
select new
{
d.LASTNAME,
d.EDLEVEL
}).ToList();
return(qry);
}
}
public dynamic query07()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
orderby d.WORKDEPT, d.JOB
select new
{
d.WORKDEPT,
d.JOB
}).Distinct().ToList();
return(qry);
}
}
public dynamic query08()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.DEPARTMENT
where d.ADMRDEPT == "A00"
select new
{
d.DEPTNO,
d.ADMRDEPT
}).ToList();
return(qry);
}
}
public dynamic query31()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.DEPARTMENT
where d.DEPTNO.Contains("11")
orderby d.DEPTNO
select new
{
d.DEPTNO,
d.DEPTNAME
}).ToList();
return(qry);
}
}
public dynamic query22()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.SEX == "M"
select new
{
d.LASTNAME,
d.SALARY,
d.BONUS
}).ToList();
return(qry);
}
}
public dynamic query19()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
orderby d.WORKDEPT descending, d.LASTNAME descending
select new
{
d.LASTNAME,
d.FIRSTNME,
d.WORKDEPT
}).ToList();
return(qry);
}
}
public dynamic query11()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.JOB == "ANALYST" || d.EDLEVEL == 16
select new
{
d.EMPNO,
d.JOB,
d.EDLEVEL
}).ToList();
return(qry);
}
}
public dynamic query16()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.DEPARTMENT
where d.MGRNO == null
select new
{
d.DEPTNO,
d.DEPTNAME,
d.MGRNO
}).ToList();
return(qry);
}
}
public dynamic query30()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from p in context.PROJECT
where p.PROJNAME.Contains("SUPPORT")
orderby p.PROJNO
select new
{
p.PROJNO,
p.PROJNAME
}).ToList();
return(qry);
}
}
public dynamic query21()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from ea in context.EMP_ACT
join e in context.EMPLOYEE
on ea.EMPNO equals(e.EMPNO)
where e.EMPNO.CompareTo("0001000") <= 0
select new
{
ea.EMPNO,
ea.PROJNO
}).Distinct().ToList();
return(qry);
}
}
public dynamic query23()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.SALARY > 20000 &&
d.HIREDATE.Value.Year > 1979
select new
{
d.LASTNAME,
d.SALARY,
d.COMM
}).ToList();
return(qry);
}
}
public dynamic query39()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from e in context.EMPLOYEE
join a in context.EMP_ACT
on e.EMPNO equals(a.EMPNO)
select new
{
e.EMPNO,
e.LASTNAME,
a.ACTNO
}).Take(10).ToList();
return(qry);
}
}
public dynamic query36()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from e in context.EMPLOYEE
from a in context.EMP_ACT
where e.EMPNO == a.EMPNO
select new
{
e.EMPNO,
e.LASTNAME,
a.ACTNO
}).Take(10).ToList();
return(qry);
}
}
public dynamic query18()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.SALARY > 30000
orderby d.SALARY descending
select new
{
d.EMPNO,
d.LASTNAME,
d.BIRTHDATE,
d.SALARY
}).ToList();
return(qry);
}
}
public dynamic query38()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from e in context.EMPLOYEE
join d in context.DEPARTMENT
on e.WORKDEPT equals(d.DEPTNO)
select new
{
e.EMPNO,
e.LASTNAME,
e.WORKDEPT,
d.DEPTNAME
}).Take(10).ToList();
return(qry);
}
}
public dynamic query32()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.JOB != "PRES" && d.JOB != "MANAGER"
orderby d.SALARY descending
select new
{
d.LASTNAME,
d.FIRSTNME,
d.MIDINIT,
d.SALARY
}).Take(5).ToList();
return(qry);
}
}
public dynamic query28()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where d.BONUS >= 800 && d.BONUS <= 1000
orderby d.EMPNO, d.BONUS ascending
select new
{
d.EMPNO,
d.LASTNAME,
d.SALARY,
d.BONUS
}).ToList();
return(qry);
}
}
public dynamic query35()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from e in context.EMPLOYEE
from d in context.DEPARTMENT
where e.WORKDEPT == d.DEPTNO
select new
{
e.EMPNO,
e.LASTNAME,
e.WORKDEPT,
d.DEPTNAME
}).Take(10).ToList();
return(qry);
}
}
public dynamic query45()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.DEPARTMENT
from e in context.EMPLOYEE
where e.WORKDEPT == d.DEPTNO
orderby d.DEPTNAME
select new
{
d.DEPTNAME,
e.LASTNAME,
e.FIRSTNME
}).Take(20).ToList();
return(qry);
}
}
public dynamic query46()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from p in context.PROJECT
join ea in context.EMP_ACT on
p.PROJNO equals(ea.PROJNO)
where p.PROJNO.Contains("AD")
orderby p.PROJNO, ea.ACTNO
select new
{
p.PROJNO,
p.PROJNAME,
ea.ACTNO
}).Take(15).ToList();
return(qry);
}
}
public dynamic query25()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.EMPLOYEE
where (d.SALARY > 22000 && (d.BONUS == 400 || d.BONUS == 500)) &&
(d.COMM < 1900)
orderby d.LASTNAME
select new
{
d.LASTNAME,
d.SALARY,
d.BONUS,
d.COMM
}).ToList();
return(qry);
}
}
public dynamic query40()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from e in context.EMPLOYEE
join d in context.DEPARTMENT
on e.WORKDEPT equals(d.DEPTNO)
where e.JOB == "MANAGER"
select new
{
e.EMPNO,
e.FIRSTNME,
e.LASTNAME,
d.DEPTNAME
}).Take(15).ToList();
return(qry);
}
}
public dynamic query42()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from e in context.EMPLOYEE
join d in context.DEPARTMENT
on e.WORKDEPT equals(d.DEPTNO)
orderby e.FIRSTNME, e.LASTNAME
orderby d.DEPTNAME
select new
{
e.LASTNAME,
e.FIRSTNME,
d.DEPTNAME
}).Take(15).ToList();
return(qry);
}
}
public dynamic query33()
{
using (DB2Entities context = new DB2Entities())
{
var qry = (from d in context.DEPARTMENT
join p in context.PROJECT
on d.DEPTNO equals(p.DEPTNO)
orderby p.PROJNO
select new
{
p.PROJNO,
p.PROJNAME,
d.DEPTNO,
d.DEPTNAME
}).Take(8).ToList();
return(qry);
}
}
