MoreCompany companygovtid cname hqloc employeefname minit ln

MoreCompany

company(govt_id, cname, hq_loc)

employee(fname, minit, lname, ssn, bdate, supervisorssn, address, gender, salary, dno)

dept_locations(govt_id, dnumber, dlocation)

skillset(ssn, skill_no)

skills(skill_no, skill_desc)

NOTE: dno and dnumber both represent the department number.

Write SQL DML for More_Company

- Retrieve bdate and address of employee John S. Smith

- For each employee, retrieve full name of employee and full name of supervisor.

- For each department whose average salary is more than $50,000, retrieve the govt_id, department number and the number of employees working for that department.

- Create a view that has employee name, address and department number.

- Retrieve the name of each employee who has a manager whose social security number is 987654321. Make sure you use a nested query.

- Retrieve all skillsets and all skills matched on skill_no where possible.

Solution

1)select bdate,address from employee fname + \' \' +lname=\"John S. Smith\";
2)select supervisorssn,fname + \' \' +lname AS FullName from employee
3)select govt_id,dno, from dept_locations where dno=(select dnumber from employee where avg(salary)>50000
4)Create view emp_view(fname, lname,address,dno);