Learn to Code SQL Example – SQL | EXISTS

Hits: 6

(SQL Example for Citizen Data Scientist & Business Analyst)

 

SQL | EXISTS

The EXISTS condition in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not. The result of EXISTS is a boolean value True or False. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.

Syntax:

SELECT column_name(s) 
FROM table_name
WHERE EXISTS 
  (SELECT column_name(s) 
   FROM table_name
   WHERE condition);

Examples:
Consider the following two relation “Customers” and “Orders”.


 

Queries

  1. Using EXISTS condition with SELECT statement
    To fetch the first and last name of the customers who placed at least one order.

    SELECT fname, lname 
    FROM Customers 
    WHERE EXISTS (SELECT * 
                  FROM Orders 
                  WHERE Customers.customer_id = Orders.c_id);

    Output:

  2. Using NOT with EXISTS
    Fetch last and first name of the customers who has not placed any order.

    SELECT lname, fname
    FROM Customer
    WHERE NOT EXISTS (SELECT * 
                      FROM Orders 
                      WHERE Customers.customer_id = Orders.c_id);

    Output:

  3. Using EXISTS condition with DELETE statement
    Delete the record of all the customer from Order Table whose last name is ‘Mehra’.

    DELETE 
    FROM Orders
    WHERE EXISTS (SELECT *
                  FROM customers
                  WHERE Customers.customer_id = Orders.cid
                  AND Customers.lname = 'Mehra');
    SELECT * FROM Orders;

    Output:

  4. Using EXISTS condition with UPDATE statement
    Update the lname as ‘Kumari’ of customer in Customer Table whose customer_id is 401.

    UPDATE Customers
    SET lname = 'Kumari'
    WHERE EXISTS (SELECT *
                  FROM Customers
                  WHERE customer_id = 401);
    SELECT * FROM Customers;

    Output:

 

MySQL Tutorials for Business Analyst: MySQL Create Database, Tables, Data Types

 

Learn to Code SQL Example – SQL | EXISTS

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!