SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HW Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

omairbang
Starting Member

3 Posts

Posted - 11/05/2013 :  02:18:16  Show Profile  Reply with Quote
Need to create following commands using these 2 tables:

Employee(empno,ename,job,mgr,hiredate,sal,comm.,deptno)
Dept(deptno,dname,loc)

h) Give an employee 8% salary increases if he or she is hired before 1981 and has salary less than 1500.

i) Find the minimum salary of each department from EMP.

j) Find the number of employees of all the departments located in DALLAS

k) Find the employees supervised by King and Blake

l) Find those employees who make more than BLAKE does

m) Find the maximum salary among the people who work in Dallas

n) Give everybody 10% salary increase in the departments whose maximum salaries are less than 2000

o) Find the employees who are making more than everybody in SALES department

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  02:32:05  Show Profile  Reply with Quote
this is an assignment question. unless we see an attempt from your end we wont offer any help for assignment questions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

omairbang
Starting Member

3 Posts

Posted - 11/05/2013 :  02:44:30  Show Profile  Reply with Quote

h) Give an employee 8% salary increases if he or she is hired before 1981 and has salary less than 1500.

UPDATE employee SET SAL * 1.08 WHERE e.HIREDATE<19830101’ AND e.SAL<1500;

i) Find the minimum salary of each department from EMP.

SELECT d.name, MIN(sal) as “Lowest Salary” FROM e.name GROUP BY d.name;

j) Find the number of employees of all the departments located in DALLAS

SELECT COUNT(e.EMPNO) FROM employee e, department d WHERE e.DEPTNO = d.DEPTNO AND d.LOCATION = 'Dallas';

k) Find the employees supervised by King and Blake

SELECT e.name WHERE mgr = empno;

l) Find those employees who make more than BLAKE does









m) Find the maximum salary among the people who work in Dallas







n) Give everybody 10% salary increase in the departments whose maximum salaries are less than 2000
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  03:02:58  Show Profile  Reply with Quote
quote:
Originally posted by omairbang


h) Give an employee 8% salary increases if he or she is hired before 1981 and has salary less than 1500.


UPDATE employee SET SAL = SAL * 1.08 WHERE e.HIREDATE< '19830101' AND e.SAL<1500;


i) Find the minimum salary of each department from EMP.



SELECT d.name, MIN(sal) as “Lowest Salary” FROM DepartmentTable GROUP BY d.name;



j) Find the number of employees of all the departments located in DALLAS

[]
SELECT COUNT(e.EMPNO) FROM employee e, department d WHERE e.DEPTNO = d.DEPTNO AND d.LOCATION = 'Dallas';


k) Find the employees supervised by King and Blake


SELECT e1.name 
FROM Employee e1
JOIN Employee e2
ON e2.empno = e1.mgr
WHERE e2.empname IN ('King','Blake')



l) Find those employees who make more than BLAKE does



SELECT *
FROM Employee
WHERE Salary > (SELECT Salary
FROM Employee
WHERE empname = 'Blake'
)







m) Find the maximum salary among the people who work in Dallas

same as j but use max over salary






n) Give everybody 10% salary increase in the departments whose maximum salaries are less than 2000


UPDATE e
SET Sal = Sal * 1.1
FROM Employee e
INNER JOIN (SELECT DeptNo
            FROM Employee e1
            GROUP BY DeptNo
            HAVING MAX(Salary) < 2000
           )d
ON d.DeptNo = e.DeptNo





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000