Interview Questions & Answers

SQL Interview Questions and Answers for Manual Testing | 21 – 33

Continuing on our SQL Interview Questions and Answers series for Manual Testing,

Flashback: SQL Interview questions for Manual testing | 1 – 20

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”);SQL Interview questions and answers

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.

Select getdate();

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.

INSTR (String1,String2(n,(m));

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.

SELECT Name,

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

ELSE 99

END AS Category

FROM Employee;

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;

Subquery,

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;

Looking for a Job Change in QA/Testing technology? Get Regular Job notifications @ WhatsApp!

One thought on “SQL Interview Questions and Answers for Manual Testing | 21 – 33”

  1. Hi There,

    Fully agree on SQL Interview Questions and Answers for Manual Testing . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    Currently I’m trying to make a test using create job as a way to have multiple process on database starting in a loop.
    Basically I’m getting different behaviors when testing on different database machines. I’ll define the machine names are A and B.
    If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using enabled parameter, created the job and automatically start:
    DECLARE
    L_job_name VARCHAR2(100);
    L_comments VARCHAR2(240);
    L_pls_block VARCHAR2(32000);
    L_thread NUMBER := 1; — count of jobs
    L_max_threads NUMBER := 3; — max number of jobs to be started
    L_lot_id NUMBER := 1234; — add any number just to represent a lot
    BEGIN

    while L_thread L_job_name,
    job_type => ‘PLSQL_BLOCK’,
    job_action => L_pls_block,
    start_date => SYSTIMESTAMP,
    comments => L_comments,
    enabled => true);

    –DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    L_thread := L_thread + 1;
    end loop;
    END;
    /

    but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.

    Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?
    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,
    Preethi.

Leave a Reply

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