Hey there SQL MASTERS, here are some questions i was posed in an interview/assessment thing, dunno how well i did....Coud u answer these questions? i dont know how easy/difficut they are for u, all i know is that i had SERIOUS trouble with them! thanks alot!EMPLOYEEEmployee_id number NOT NULL (Primary Key)Employee_name VarChar(30) NOT NULL Dept_id number NOT NULL (Foreign Key to DEPARTMENT)Manager_id number NOT NULL (Foreign Key to EMPLOYMENT)Salary number NOT NULLDate_of_Birth date NOT NULLDEPARTMENTDept_id number NOT NULL (Primary Key)Dept_name (varchar30) NOT NULLAbove is the info given...Following are some of the questions posed....i.e we had to write the code using SELECT, UPDATE, INSERT etc...1. Update the salary to double its current value for every employee who worksin the COMPUTER department or has the string MC at the start of their name.2. Add your details (John Smith for example)to the EMPLOYEE table with an Employee_id one higher than the exsisting highest. Your Salary is $100,000 and you will work in the accounts department under \'Jed Teolut\' (Hint: Embed Subqueries in the values clause).3. List the name of each employee and the name of his/her department... I thought it was this.... am i right?SELECT Employee_name, Dept_idFROM EMPLOYEEORDER BY 2, 3;4. List names of employees in PERSONNEL dept whose salarie exceed $20,000a) using a joinb) sub-query (\'in\' or \'any\' clause)c) Using a correlated (exists clause)5. Update the salary of each manager to be double the average salary of the employees he/she manages (assume manager have a manager_id of NULL sub-query)6. List the highest salary, the lowest salry, the average salary and the total salaries of employees.

  • Interview Candidate
  • 7
  • 4797

Showing Answers 1 - 7 of 7 Answers

gomathi

  • Jan 6th, 2006
 

(1) Update the salary to double its current value for every employee who worksin the COMPUTER department or has the string MC at the start of their name. update employee set salary=salary * salary where dept_id=(select dept_id from department where dept_name='COMPUTER' ) and employee_name like 'mc%';(2) Add your details (John Smith for example)to the EMPLOYEE table with an Employee_id one higher than the exsisting highest. Your Salary is $100,000 and you will work in the accounts department under \'Jed Teolut\' (Hint: Embed Subqueries in the values clause).i think it may passible by using sequence(3) List the name of each employee and the name of his/her department... I thought it was this.... am i right?select employee_name,dept_name from employee a, department b where a.dept_id=b.dept_id;(4)list names of employees in PERSONNEL dept whose salarie exceed $20,000a) using a joinb) sub-query (\'in\' or \'any\' clause)c) Using a correlated (exists clause)by using join. (inner join)(5) Update the salary of each manager to be double the average salary of the employees he/she manages (assume manager have a manager_id of NULL sub-query)Sorry this question is not clear to me(6) List the highest salary, the lowest salry, the average salary and the total salaries of employees.select max(salary),min(salary),avg(salary),sum(salary) from employee

  Was this answer useful?  Yes

Nam

  • Feb 4th, 2006
 

for Q:-2 insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)select max(a.empno) + 1 empno,'Your Name', 'MANAGER',b.empno,sysdate,10000,d.deptno from emp A,emp b,dept d where b.ename ='Jed Teolut' and dname='ACCOUNTING'group by 'Your Name', 'MANAGER',b.empno,sysdate,100000,d.deptno

  Was this answer useful?  Yes

kn.2006@hotmail.com

  • Mar 22nd, 2006
 

1. Update the salary to double its current value for every employee who worksin the COMPUTER department or has the string MC at the start of their name.UPDATE EmployeeSET salary = salary*2WHERE Dept_id = (SELECT Dept_id FROM Department WHERE dept_name = "Computer") OR Employee_name like MC%; 2. Add your details (John Smith for example)to the EMPLOYEE table with an Employee_id one higher than the exsisting highest. Your Salary is $100,000 and you will work in the accounts department under \'Jed Teolut\' (Hint: Embed Subqueries in the values clause).INSERT INTO Employee(Employee_id,Employee_name, Dept_id, Manager_id, Salary, Date_of_birth) VALUES((SELECT MAX(Employee_id)+1 FROM Employee;), "John Smith", (SELECT Dept_id FROM Department WHERE Dept_name="accounts";), (SELECT Employee_id FROM Employee WHERE Employee_name="Jed Teolut";), $100,000, "1-1-1980"); 3. List the name of each employee and the name of his/her departmentSELECT Employee_name,Dept_nameFROM Employee, DepartmentWHERE Employee.Dept_id = Department.Dept_id;4. List names of employees in PERSONNEL dept whose salary exceed $20,000a) using a joinb) sub-query (\'in\' or \'any\' clause)c) Using a correlated (exists clause)SELECT Employee_nameFROM Employee,WHERE Dept_name = "PERSONNEL" and Salary > $20,000;6. List the highest salary, the lowest salry, the average salary and the total salaries of employees.SELECT MAX(salary), MIN(salary), AVG(salary), AVG(salary), SUM(salary)FROM Employee,

  Was this answer useful?  Yes

rb

  • Mar 23rd, 2006
 

hi ,

1)update employee set sal = sal*2 from employee as e join department as d on e.deptid = d.deptid and d.deptname ='computer' or e.empname like 'mc%'

2)insert into employee (empid,empname,deptid,managerid,sal,dob)

 select  (select max(empid)+1 from employee),'johnsmith', (select deptid from department where deptname='acc'), (select managerid from employment where managername='Jed Teolut'), 100000, 2006-6-12

3)select employee.empname, department.deptname from employee inner join department on employee.deptid=department.deptid

4)select empname from employee inner join department on employee.deptid=department.deptid where employee.sal>20000 and department.deptname='personal'

select empname from employee where deptid in (select deptid from department where deptname='personal') and sal>20000

6)select max(sal) as [highest salary],min(sal) as [lowest salary],avg(sal) as [average salary],sum(sal) as [total salaries] from employee

 

thx

rb

  Was this answer useful?  Yes

umagold

  • Aug 1st, 2006
 

1)update employee set sal = sal*2 from employee as e join department as d on e.deptid = d.deptid and d.deptname ='computer' or e.empname like 'mc%'

2)insert into employee (empid,empname,deptid,managerid,sal,dob)

 select  (select max(empid)+1 from employee),'johnsmith', (select deptid from department where deptname='acc'), (select managerid from employment where managername='Jed Teolut'), 100000, 2006-6-12

3)select employee.empname, department.deptname from employee inner join department on employee.deptid=department.deptid

4)select empname from employee inner join department on employee.deptid=department.deptid where employee.sal>20000 and department.deptname='personal'

select empname from employee where deptid in (select deptid from department where deptname='personal') and sal>20000

6)select max(sal) as [highest salary],min(sal) as [lowest salary],avg(sal) as [average salary],sum(sal) as [total salaries] from employee

 

  Was this answer useful?  Yes

diana

  • Oct 25th, 2007
 


#5 -

UPDATE x
SET salary = ( SELECT AVG(a.salary)*2
                      FROM employees a JOIN employees b ON
                      a.manager_id = b.employee_id )
FROM employees x LEFT OUTER JOIN employees y ON
x.manager_id = y.employees_id
WHERE y.manager_id IS NOT NULL

  Was this answer useful?  Yes

ieddy

  • Jan 6th, 2009
 

#5   Update x Set x.Salary = (Select  avg(e.salary)*2 'Salary' --, m.Employee_id --, m.Employee_Name,m.salary    From (employee m join employee e on m.employee_id = e.manager_id and m.manager_id <> e.employee_id)       Where m.Employee_id = x.Employee_id       Group By  m.Employee_id,m.Employee_Name,m.salary ) FROM employee xWhere x.Employee_id In       (Select m.Employee_id     From (employee m join employee e on m.employee_id = e.manager_id and m.manager_id <> e.employee_id)       Where m.Employee_id = x.Employee_id      Group By  m.Employee_id)  Select * from employee  

Department:

33PERSONNEL
44TOP_GUN
NULLNULL
 

Employee:

1Ed335500009/25/1953 12:00:00 AM
2Jamie335300008/1/1970 12:00:00 AM
3Tom335220003/22/1980 12:00:00 AM
4Bob446120001/1/1909 12:00:00 AM
5Jeff3371100006/5/1972 12:00:00 AM
6Jack4472000004/2/1939 12:00:00 AM
7MCBigs4473000006/6/1949 12:00:00 AM
8Jed Teolut4472000001/1/1900 12:00:00 AM
9John Smith3371000003/14/1905 12:00:00 AM
NULLNULLNULLNULLNULLNULL


This one works although note the query duplication


Hope it helps,

Ed

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions