-- Tmimata me meso mistho andrwn >20000, emfanise to plithos andrwn ypaliliwn kai to meso mistho SELECT count(ssn) plithos, d.dnumber, d.dname, avg(salary) FROM EMPLOYEE e join Department d ON e.dno=d.dnumber WHERE sex='M' GROUP BY d.dnumber, d.dname HAVING avg(salary)>=20000; -- breite to etos genissis kai tin imerominia gia antres prostateyomena meli poy to onoma arxizei apo M SELECT extract(year from bdate) birth_year, dependent_name, extract(year from SYSDATE)-extract(year from bdate) years_old FROM dependent WHERE sex='M' and dependent_name LIKE 'M%'; -- onoma fylo sxesi exartimenwn melwn twn ypallilwn toy tmimatos Research SELECT dp.dependent_name, dp.sex, dp.relationship FROM dependent dp join employee e ON dp.essn=e.ssn JOIN department d ON e.dno=d.dnumber WHERE dname='Research'; -- plithos ana typo sxesis kai fylo SELECT count(dp.dependent_name), dp.sex, dp.relationship FROM dependent dp join employee e ON dp.essn=e.ssn JOIN department d ON e.dno=d.dnumber WHERE dname='Research' GROUP BY dp.sex, dp.relationship ; -- meso mistho ana tmima CREATE view average_dep_salary AS SELECT d.dnumber, d.dname, avg(salary) mesos_misthos FROM EMPLOYEE e join Department d ON e.dno=d.dnumber GROUP BY d.dnumber, d.dname; SELECT max(mesos_misthos) FROM average_dep_salary; SELECT * FROM average_dep_salary WHERE mesos_misthos=(SELECT max(mesos_misthos) FROM average_dep_salary); select * from employee where bdate>='1-Jan-1966'; select * from employee where extract(year from bdate)>=1966; SELECT fname FROM employee WHERE extract(year from bdate)>=1965 UNION SELECT dependent_name FROM dependent WHERE extract(year from bdate)>=1965; SELECT e.ssn FROM employee e JOIN works_on w on e.ssn=w.essn WHERE e.sex='M' and w.pno=10; SELECT fname|| ' ' || lname onoma, e.address, d.dname,dl.dlocation FROM employee e JOIN department d ON e.dno=d.dnumber JOIN dept_locations dl ON d.dnumber=dl.dnumber WHERE dl.dlocation='Houston' AND e.address LIKE '%Houston%' and e.sex='M';