SDET- QA Automation Techie

Full Stack QA Automation Testing Blog

  • Home
  • Training
    • Online
    • Self-Paced
  • Video Tutorials
  • Interview Skills
    • HR Interview Questions Videos
    • Domain Knowledge
  • Career Guidance
  • Home
  • Software Testing
    • Manual Testing Tutorials
    • Manual Testing Project
    • Manaul Testing FAQS
    • ISTQB
    • AGILE
  • Web Automation Testing
    • Java Programmng
    • Python Programmng
    • Selenium with Java
    • Selenium with Python
    • Robot Framework(Selenium with Python)
    • selenium with Cucumber
    • TestNG+IntelliJ
    • Mobile App Testing(Appium)
    • JMeter
  • API Automation Testing
    • Rest Assured API Testing (BDD)
    • Rest Assured API Testing (Java+ TestNG)
    • Robot Framework(Rest API Testing with Python)
    • Postman
    • SoapUI
    • API Testing(FAQ's)
  • SDET|DevOps
    • Continuos Integration
    • SDET Essentials
    • AWS For Testers
    • Docker
  • SQL
    • Oracle(SQL)
    • MySQL for Testers
    • NoSQL
  • Unix/Linux
    • UNIX TUTORIALS
    • Linux Shell Scripting
  • ETL Testing
    • ETL Data warehouse Tutorial
    • ETL Concepts Tools and Templates
    • ETL Testing FAQ's
    • ETL Testing Videos
  • Big Data Hadoop
  • Video Tutorials
  • ApachePOI Video Tutorials
  • Downloads
    • E-Books for Professionals
    • Resumes
  • Automation Essencials
    • Cloud Technologies
      • Docker For Testers
      • AWS For Testers
      • Sub Child Category 3
    • Java Collections
    • Selenium Locators
    • Frequently Asked Java Programs
    • Frequently Asked Python Programs
    • Protractor
    • Cypress Web Automation

SQL- Sub Queries

 ETL Testing, ORACLE, SQL, SQL SERVER   

A subquery is a type of SQL query, where a query is embedded within another query. Sub-queries are very powerful. To help you understand a subquery consider the following SELECT statement to retrieve the details of employees who belong to department 30.



SELECT * FROM EMPLOYEES



WHERE DEPARTMENT_ID=30;



In the above query, the department ID value has been provided, and is used on the right hand side of the WHERE condition. However, such constant values might not also be provided or known. For example, consider the query re-phrased as - retrieve the details of employees who belong to the same department as 'Alexander Khoo'. Here the department number has not been provided. Instead the name of an employee is given. Using this name, you would need to first find out - to which department does Alexander Khoo belong. Let say this is some value 'X'. You would have to proceed further to find out all the other employees who belong to the department X.



If you notice this is a 2-step process involving:



1) Which department does Alexander Khoo belong to.



2) Who are the others who belong to the department number returned by the first step.



Subquery Syntax:



SELECT select_list



FROM table_name



WHERE column_name operator (SELECT select_list



FROM table_name



…)



In the syntax, observe a second SELECT statement written in the WHERE clause, on the right hand side of the WHERE condition. This SELECT statement is enclosed in parantheses. This subquery is called the inner query and is executed once to return a value that is used by the main (outer) query. Subqueries can be different in different places in a SELECT statement, such as the WHERE clause, HAVING clause, FROM clause, SELECT column list etc.



The query to retrieve the details of employees who belong to the same department as Alexander Khoo is :



SELECT *



FROM EMPLOYEES



WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID



FROM EMPLOYEES



WHERE FIRST_NAME='Alexander' AND LAST_NAME='Khoo')






Some guidelines related to subqueries are:



• Enclose subqueries in parentheses.



• Place subqueries on the right side of the comparison condition.



• Use single-row operators with single-row subqueries.



• Use multiple-row operators with multiple-row subqueries.



A single-row subquery is one where the subquery returns only one value. In such a subquery you must use a single-row operator such as:





Operator


Description


=


Equal
To


<>


Not
Equal To


>


Greater
Than


>=


Greater
Than Equal To


<


Less
Than


<=


Less
Than Equal To



The single-row operators are used to write single-row subqueries. The table below demonstrates the use of the single-row operators in writing single-row subqueries.


Operator


Query


Example


=


Retreive the details of
employees who get the same salary as the employee whose ID is 101.


SELECT
* FROM EMPLOYEES

WHERE SALARY=(SELECT SALARY FROM EMPLOYEES

WHERE EMPLOYEE_ID=101);


<>


Retreive the details of
departments that are not located in the same location ID as department 10.


SELECT
*

FROM DEPARTMENTS

WHERE LOCATION_ID <>(SELECT  

       LOCATION_ID

       FROM DEPARTMENTS

       WHERE DEPARTMENT_ID=10);


>


Retrieve
the details of employees whose salary is greater than the minimum salary.


SELECT
*

FROM EMPLOYEES

WHERE SALARY > (SELECT 

       MIN(SALARY)

       FROM EMPLOYEES);


>=


Retrieve
the details of
employees who were hired on or after the
same date that employee 201 was
hired.


SELECT
* FROM EMPLOYEES

WHERE HIRE_DATE >=(SELECT

       HIRE_DATE

       FROM EMPLOYEES
       WHERE EMPLOYEE_ID=201);


<


Retrieve
the details of employees whose salary is less than the maximum salary of
employees in department 20.


SELECT
* FROM EMPLOYEES

WHERE SALARY < (SELECT

    MAX(SALARY)

    FROM EMPLOYEES

    WHERE DEPARTMENT_ID=20);


<=


Retrieve
the details of
employees who were hired on or before the
same date that employee 201 was
hired.


SELECT
* FROM EMPLOYEES

WHERE HIRE_DATE <=(SELECT

      HIRE_DATE

      FROM EMPLOYEES

      WHERE EMPLOYEE_ID=201);

A multiple row subquery is one where the subquery may return more than one value. In such type of subquery, it is necessary to use a multiple-row operator. If not you might get the ORA-01427 error: single-row subquery returns more than requested number of rows.




The table below describes the multiple-row operators that can be used when writing multiple-row subqueries:




Operator


Meaning


IN


Equal
to any value returned by the subquery


ANY


Compare
value to each value returned by the subquery


ALL


Compare
value to every value returned by the subquery


The multiple-row operators are used to write multiple-row subqueries. The table below demonstrates the use of the multiple-row operators in writing multiple-row subqueries.


Operator


Query


Example


IN


Retreive the department ID,
department name and location ID of departments that are located in the same
location ID as a location in the UK.


SELECT DEPARTMENT_ID, DEPARTMENT_NAME,
LOCATION_ID

FROM DEPARTMENTS

WHERE LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATIONS WHERE
COUNTRY_ID='UK')


 >ALL
(Greater
than the maximum returned by the subquery)


Retrieve
the first name of employees whose salary is greater than the all the salaries
of employees belonging to department 20.


SELECT
FIRST_NAME

FROM EMPLOYEES

WHERE SALARY > ALL

(SELECT SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID=20)


<ALL
(Less
than the least value returned by the subquery)


Retrieve
the first name of employees whose salary is less than all the salaries of
employees belonging to department 20.


SELECT
FIRST_NAME

FROM EMPLOYEES

WHERE SALARY < ALL

(SELECT SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID=20)


>ANY
(Greater
than the minimum value returned by the subquery)


Retrieve
the first name of employees whose salary is greater than the minimum salary
of employees in department 60.


SELECT
FIRST_NAME

FROM EMPLOYEES

WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=60)


 <ANY
(Less
than the maximum value returned by the subquery)


Retrieve
the first name of employees whose salary is less than the maximum salary of
employees in department 60.


SELECT
FIRST_NAME

FROM EMPLOYEES

WHERE SALARY < ANY

(SELECT SALARY

FROM EMPLOYEES WHERE DEPARTMENT_ID=10)








  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to TwitterShare to Facebook
Newer Post Older Post Home
popup

Popular Posts

  • How To Explain Project In Interview Freshers and Experienced
    “ Describe an important project you’ve worked on ” is one of the most common questions you can expect in an interview. The purpose of a...
  • API/Webservices Testing using RestAssured (Part 1)
    Rest Assured : Is an API designed for automating REST services/Rest API's Pre-Requisites Java Free videos: https://www.you...
  • MANUAL TESTING REAL TIME INTERVIEW QUESTIONS & ANSWERS
    1. How will you receive the project requirements? A. The finalized SRS will be placed in a project repository; we will access it fr...

Facebook Page

Pages

  • Home
  • Resumes
  • Job Websites India/UK/US
  • ISTQB
  • Selenium with Java
  • E-Books for Professionals
  • Manual Testing Tutorials
  • Agile Methodology
  • Manual Testing Projects

Live Traffic

YouTube


Blog Visitors

Copyright © SDET- QA Automation Techie | Powered by Blogger
Design by SDET | Blogger Theme by | Distributed By Gooyaabi Templates