Question

Subject :Introduction to Database

SQL

Q 1: List the emp who are working for the Deptno 10 or 20.

Q 2: List the Empno, Ename, Sal, Dname of all the ‘MGRS’ and ‘ANALYST’ working in New
York or Dallas with an salary 3000, 2450 or 2850.

Q 3. List the daily salaries (Salary per day) of employees?

Q 4: Display ename ,sal , and increment of in 100 in annual sal from employee Table?

Answer 1

Consider employee as table name.

Q 1: select * from employee where Deptno in (10,20); // This will select all employees present in department number 10 or 20

Q 2: select e.Empno,e.Ename,e.Sal,d.Dname from employee e, dept d where e.deptno=d.deptno and e.job in ('MGRS','ANALYST') and d.loc in ('New York','Dallas') and e.Sal in (3000,2450,2850);// let us consider dept table has deptno and loc as attributes... so the common attribute between employee and dept is depno... we will join two tables based on that condition and MGRS and ANALYST are the employee jobs

Q 3: select sal/30 from employee; // consider sal attribute has monthly salary ... so if we want to get daily salary we need to divide sal with no.of days (30)

Q 4: select ename ,sal ,(sal*12)+100 from employee; // annual salary means yearly salary so (sal*12) because year has 12 months and increment of 100 in annual salary means 100 is added to annual salary, therefore it becomes (sal*12)+100.