Continuing on our SQL Interview Questions and Answers series for Manual Testing,
SQL Interview Questions and Answers
Q21. How do you add record to a table?
Records are added to a table using the ‘Insert’ command.
INSERT into TestTable VALUES (“STS”,33,”M”);
Q22. How do you add a column to a table?
To add a column we have to ‘Alter’ the table and provide the column name along with its data type.
ALTER TABLE Department ADD (AGE, NUMBER);
Q23. How do you change value of the field?
To update any value in the table, simply use the ‘Update’ command along with the ‘Where’ clause.
UPDATE EMPTABLE set number = 200 where itemvalue = “D”;
Q24. How do you find unique city (no duplicate) from Students table?
SELECT DISTINCT City FROM Students;
— The Distinct keyword automatically sorts all data in ascending order. However, if you want the data sorted in descending order, you have to use an ORDER BY clause.
Q25. Write an SQL query to find names of employees that start with ‘A’?
Using the ‘Like’ keyword and % wildcard,
SELECT * FROM Employees WHERE EmpName like ‘A%’;
Q26. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers using mod operation.
Even rows: Select studentId from (Select rowno, studentId from student where mod(rowno,2)=0);
Q27. What is the SQL query to display current date?
There is a built in function in SQL called GetDate() which is used to return current timestamp.
Q28. What is the difference between SUBSTR and INSTR?
SUBSTR returns a character string of size m in string1, starting from nth position of string1.
E.g. SUBSTR (“SoftwareTestingStudio”,9,7) will return “Testing”.
INSTR returns the position of the mth occurrence of the string 2 in string1. The search begins from nth position of string1.
Q29. How to select random rows from a table?
Using SAMPLE clause we can select random rows.
SELECT * FROM Employee SAMPLE(10);
Q30. What is Case Function?
Case facilitates conditional inquires just like an if-then-else statement. In SQL case works with either the select or update clauses.
CASE WHEN Sal > 0 AND Sal <= 100000 THEN 1
WHEN Sal > 100000 AND Sal < 250000 THEN 2
WHEN Sal > 250000 AND Sal < 5000000 THEN 3
END AS Category
Q31. What will be the result of the query below?
Select case when null = null then ‘Yup’ else ‘Nope’ end as Result;
This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the ‘IS’ operator, not with =. Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows:
Select case when null is null then ‘Yup’ else ‘Nope’ end as Result;
Q32. How to find second highest salary of an Employee?
There are many ways to find second highest salary of Employees in SQL. Here is SQL query using Sub-query:
Select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee);
Q33. How to find third highest salary of an Employee? Or nth highest salary!
Select TOP (1) salary from
(Select DISTINCT TOP (3) salary from Employee ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
Select DISTINCT TOP (3) salary from Employee ORDER BY salary DESC
will select the top 3 salaried employees in the table listed in descending order.
Now picking the top 1 from that list will give you the highest salary not the 3rd highest salary. Therefore, the second query reorders the 3 records in ascending order and then selects the top record (which will now be the lowest of those 3 salaries).
Not all databases support the TOP keyword. For example, MySQL and PSQL use the LIMIT keyword, as follows,
Select Salary from
(Select DISTINCT Salary from Employee ORDER BY Salary DESC LIMIT 10) AS Emp
ORDER BY Salary LIMIT 1;