SQL Assignment 1

we are  performing SQL operation for that used following table as :

Table Employee
Figure 1.0

 1) Display the following virtual columns.

Exam Fee 25% Discount Exam Fee After Discount 18% GST Final Exam Fees

If the Exam fee is Rs.9701 calculate the rest.

Ans : select 9701 as “Exam Fee”, (9701*(25/100)) as “25% Discount” , 9701-(9701*0.25) as “Exam Fee After Discount” , (9701-(9701*0.25))*0.18 as “18% GST”, ((9701-(9701*0.25))+((9701-(9701*0.25))*0.18)) as “Final Exam Fee” from dual;

2) Display the below sentence for the employees.  SMITH’S salary is 800 and his dream salary is 3 times i.e. = ________.

Ans: select ename||q'<‘s>’||’ salary is ‘|| sal ||’ and his dream salary is 3 times =’ || sal*3 from emp;

3) Calculate annual salary for all employees with a monthly deduction of 20%. Label the column as “Annual Salary after 20% Deduction”.

Ans: select ename as “Name”, sal, sal-(sal*0.20) as “Monthly Deduction of 20%”, sal-(sal*0.20))*12 as “Annual Salary After Deduction” from emp;

4) Display unique job in each department number.

Ans : select distinct(job), deptno from emp;

5) Calculate the below columns for all employees.

Employee Name Monthly Salary 20% Deduction Deducted Salary Annual Salary after Deduction

Ans: select ename as “Employee Name”, Sal as “Monthly Salary”,
sal-(sal*0.20) as “20% Deduction”, sal-(sal-(sal*0.20)) as “Deducted Salary”,(sal-(sal*0.20))*12 as “Annual Salary After Deduction” from emp;6) Display the below sentences for all employees. I can’t work with SMITH because he works in the department’s no. 20.

Ans : select ename || ‘ can’||q'<‘t>’ || ‘ work with SMITH ‘ || ‘because he works in department’  ||q'<‘s>’ || ‘ no ‘ ||deptno from emp where ename!=’SMITH’;

7) List all employee name and total salary, they are paid 500 rupees and balance salary. Provide proper heading to all 3 columns.

Ans : select ename as “Name”, sal as “Total Salary”, 500 as “Paid Rs 500”,  sal-500 as “Balance Salary” from emp;

Leave a Reply

Your email address will not be published. Required fields are marked *