Skip to main content

Introduction to SQL - Part 10- SQL Sub Query

 SQL Sub Query

A Subquery is a query within another SQL query and embedded within the WHERE clause.

Important Rule:

  • A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause, HAVING clause.
  • You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
  • A subquery is a query within another query. The outer query is known as the main query, and the inner query is known as a subquery.
  • Subqueries are on the right side of the comparison operator.
  • A subquery is enclosed in parentheses.
  • In the Subquery, ORDER BY command cannot be used. But GROUP BY command can be used to perform the same function as ORDER BY command

Subqueries with the Select Statement

SQL subqueries are most frequently used with the Select statement.

Syntax:

    SELECT column_name
    FROM table_name
    WHERE column_name expression operator(SELECT column_name FROM table_name WHERE...);

Example:

    SELECT *
    FROM EMPLOYEE
    WHERE ID IN (
SELECT ID
FROM EMPLOYEE
WHERE SALARY > 4500
);

Subqueries with the INSERT Statement

  • SQL subquery can also be used with the Insert statement. In the insert statement, data returned from the subquery is used to insert into another table.
  • In the subquery, the selected data can be modified with any of the character, date functions.
Syntax:

    INSERT INTO table_name (column1, column2, column3....)
    SELECT * FROM table_name WHERE VALUE OPERATOR

Example:

    INSERT INTO EMPLOYEE_BKP
    SELECT * FROM EMPLOYEE
    WHERE ID IN (SELECT ID
    FROM EMPLOYEE);

Subqueries with the UPDATE Statement

The subquery of SQL can be used in conjunction with the Update statement. When a subquery is used with the Update statement, then either single or multiple columns in a table can be updated.

Syntax:

    UPDATE table SET column_name = new_value WHERE VALUE OPERATOR
    (SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);

Example:

Let's assume we have an EMPLOYEE_BKP table available which is backup of
EMPLOYEE table. The given example updates the SALARY by .25 times in the
EMPLOYEE table for all employee whose AGE is greater than or equal to 29.

    UPDATE EMPLOYEE
    SET SALARY = SALARY * 0.25
    WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
    WHERE AGE >= 29);

Subqueries with the DELETE Statement

The subquery of SQL can be used in conjunction with the Delete
statement just like any other statements mentioned above.

Syntax:

    DELETE FROM TABLE_NAME WHERE VALUE OPERATOR
    (SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);

Example:

Let's assume we have an EMPLOYEE_BKP table available which is backup of
EMPLOYEE table. The given example deletes the records from the EMPLOYEE
table for all EMPLOYEE whose AGE is greater than or equal to 29.

    DELETE FROM EMPLOYEE
    WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
    WHERE AGE >= 29 );


Comments

Post a Comment

Popular posts from this blog

Prompt Engineering Fundamentals

  Introduction Generative AI is a transformative technology capable of producing text, images, audio, and code in response to user prompts. This capability is powered by Large Language Models (LLMs) like OpenAI's GPT series, which are trained to understand and generate natural language. Interacting with these models via prompts allows users to harness their potential without needing technical expertise. This chapter explores the essentials of prompt engineering, a field dedicated to optimizing prompt design for consistent and high-quality responses. Learning Goals By the end of this lesson, you will be able to: Explain what prompt engineering is and why it matters. Describe the components of a prompt and how they are used. Learn best practices and techniques for prompt engineering. Apply learned techniques to real examples using an OpenAI endpoint. Learning Sandbox Prompt engineering is more art than science, requiring practice and iterative refinement. This lesson includes a Jupyt...
Post a Comment
Read more

AWS Cloud Containers

Amazon Web Services (AWS) offers a variety of services for deploying and managing applications in the cloud. One of these services is called Amazon Elastic Container Service (ECS), which allows you to run and manage Docker containers on AWS. Here is a brief overview of how Amazon ECS works: You package your application into a Docker container image and push it to a registry, such as Amazon Elastic Container Registry (ECR) or Docker Hub. You create an Amazon ECS task definition, which is a blueprint for your containerized application. The task definition specifies things like the Docker image to use, the CPU and memory requirements, and the environment variables to pass to the container. You create an Amazon ECS cluster, which is a group of Amazon EC2 instances that are running the Amazon ECS container agent. The cluster is where your tasks are placed and run. You create an Amazon ECS service, which is a long-running task that is hosted on your cluster. The service ensures that a speci...
Post a Comment
Read more

Identified COVID-19 in X-ray images with deep learning.

  Project structure :       Our coronavirus (COVID-19) chest X-ray data is in the dataset/ directory where our two classes of data are separated into covid/ and normal/   I have created train_covid19.py file to train the model.   Three command line arguments (parameters) required to run this file :   --dataset: The path to our input dataset of chest X-ray images. --plot: An optional path to an output training history plot. By default the plot is named plot.png unless otherwise specified via the command line. --model: The optional path to our output COVID-19 model; by default it will be named covid19.model.     To load our data, we grab all paths to images in in the --dataset directory. Then, for each imagePath, we:   ·           Extract the class label (either covid or normal) from the path. ·           Load the image, and preprocess it by convertin...
Post a Comment
Read more