Question

Write SQL queries for the following question :
1. Display all the data from the EMP table in the ascending order of the Employee Name.
2. Display all the data from the EMP table in the descending order of the Employee Number.
3. Display the data of only those employees whose designation is “Manager”.
4. Display the data of only those employees who are working in the Department Number 20.
5. Display the data of only those employees who are from the department “Sales”.
6. Display the names of all those employees whose salary is above Rs. 25000.
7. Display the sum all of the salary of all the employees from Department No. 10 by using “Group By” Clause.
8. Display the sum all of the salary of all the employees from Department No. 30 by using “Group By” Clause.
9. Display Department No and Average of the salary of the employees who are working in that Department by using “Group By” Clause.
10. Display the minimum salary Department wise using “Group By” clause.
11. Count and display the number of employees working in each department (Use “Group By” Clause)
12. Delete only that row whose Employee Number is 205.
13. Delete all the rows from Department No. 40.
14. Write a SQL query to display the System Date.
15. Write a SQL query to display the square root of 100.
16. Write a SQL query to display the Absolute value of -50.
17. Write a SQL query to display the Lower Case of “NMD. COLLEGE,GONDIA”.
18. Write a SQL query to display the ASCII value of the character “R”.
19. Write a SQL query to display the length of the string “NMD. COLLEGE,GONDIA”.
20. Write a SQL query to display the character value of the ASCII number 97.

Answer 1

1. Select * from EMP order by Employee_Name;

2. Select * from EMP order by Employee_Number desc;

3. Select * from EMP where Designation = 'Manager';

4. Select * from EMP where Department_Number = 20;

5. Select * from EMP where Department = 'Sales';

6. Select Employee_Name from EMP where Salary > 25000;

7. Select sum(Salary), Department_Number from EMP group by Department_Number having Department_Number = 10;

8. Select sum(Salary), Department_Number from EMP group by Department_Number having Department_Number = 30;

9. Select Department_Number, avg(Salary) from EMP group by Department_Number;

10. Select Department, min(Salary) from EMP group by Department;

11. Select count(Employee_Name), Department from EMP group by Department;

12. Delete from EMP where Employee_Number = 205;

13. Delete from EMP where Department_Number = 40;

14. Select sysdate();

15. Select sqrt(100);

16. Select abs(-50);

17. Select lcase('NMD. COLLEGE, GONDIA');

18. Select As‎cii('R');

19. Select length('NMD. COLLEGE, GONDIA');

20. Select ch‎ar(97);

• Select is used to retrieve/display information from a table.
• Order by is used to sort the result according to the specified condition.
• Desc is used to specify that the results need to be set in descending order [order by sets the result in ascending order by default.]
• Where is a clause used to specify conditions.
• Sum() is an aggregate function used to display the sum of the data in the specified argument.
• Group by is a clause used to sort the data in clubs/groups.
• Having is a clause used to specify conditions after the group by clause has been used.
• Avg() is an aggregate function used to display the average value of the specified argument.
• Min() is an aggregate function used to display the lowest value of the specified argument.
• Count() is an aggregate function used to display the number of values present in the specified argument.
• Sysdate() is a scalar function used to display the current date and time.
• Sqrt() is a scalar function used to display the square root of the specified argument.
• Abs() is a scalar function used to display the absolute value of the specified argument.
• Lcase() is a scalar function used to display the specified argument in lowercase.
• As‎cii() is a function used to display the As‎cii value of the specified argument.
• Length() is a function used to display the length/number of characters present in the specified argument.
• Ch‎ar() is a function used to display the cha‎racter being held by the specified As‎cii value.