Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 34 views

    SQL AND, OR and NOT Operators

    Uploaded by

    zainab alhussainy
    The document describes various SQL operators such as WHERE, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL. It provides examples of SQL queries using each operator to filter records based on column values and conditions. Key operators covered include AND to filter records where all conditions are true, OR to filter records where any condition is true, NOT to filter records where conditions are not true, BETWEEN to filter numeric values within a range, IN to specify multiple possible values, LIKE to use wildcard patterns to match strings, and IS NULL/IS NOT NULL to test for empty versus non-empty values.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    SQL AND, OR and NOT Operators

    Uploaded by

    zainab alhussainy
    34 views5 pages
    The document describes various SQL operators such as WHERE, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL. It provides examples of SQL queries using each operator to filter records based on column values and conditions. Key operators covered include AND to filter records where all conditions are true, OR to filter records where any condition is true, NOT to filter records where conditions are not true, BETWEEN to filter numeric values within a range, IN to specify multiple possible values, LIKE to use wildcard patterns to match strings, and IS NULL/IS NOT NULL to test for empty versus non-empty values.

    Original Title

    db4 p

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes various SQL operators such as WHERE, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL. It provides examples of SQL queries using each operator to filter records based on column values and conditions. Key operators covered include AND to filter records where all conditions are true, OR to filter records where any condition is true, NOT to filter records where conditions are not true, BETWEEN to filter numeric values within a range, IN to specify multiple possible values, LIKE to use wildcard patterns to match strings, and IS NULL/IS NOT NULL to test for empty versus non-empty values.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    34 views5 pages

    SQL AND, OR and NOT Operators

    Uploaded by

    zainab alhussainy
    The document describes various SQL operators such as WHERE, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL. It provides examples of SQL queries using each operator to filter records based on column values and conditions. Key operators covered include AND to filter records where all conditions are true, OR to filter records where any condition is true, NOT to filter records where conditions are not true, BETWEEN to filter numeric values within a range, IN to specify multiple possible values, LIKE to use wildcard patterns to match strings, and IS NULL/IS NOT NULL to test for empty versus non-empty values.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 5

    SELECT * FROM Products

    WHERE Price = 18;


    SELECT * FROM Products
    WHERE Price > 30;
    SELECT * FROM Products
    WHERE Price < 30;
    SELECT * FROM Products
    WHERE Price >= 30;
    SELECT * FROM Products
    WHERE Price <= 30;
    SELECT * FROM Products
    WHERE Price <> 18;
    SELECT * FROM Products
    WHERE Price BETWEEN 50 AND 60;
    SELECT * FROM Customers
    WHERE City LIKE 's%';
    SELECT * FROM Customers
    WHERE City IN ('Paris','London');

    SQL AND, OR and NOT Operators


    he SQL AND, OR and NOT Operators
    The WHERE clause can be combined with AND, OR, and NOT operators.

    The AND and OR operators are used to filter records based on more than one condition:

     The AND operator displays a record if all the conditions separated by AND are
    TRUE.
     The OR operator displays a record if any of the conditions separated by OR is
    TRUE.

    The NOT operator displays a record if the condition(s) is NOT TRUE.


    AND Syntax
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition1 AND condition2 AND condition3 ...;

    OR Syntax
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition1 OR condition2 OR condition3 ...;

    NOT Syntax
    SELECT column1, column2, ...
    FROM table_name
    WHERE NOT condition;

    OR Example
    SELECT * FROM Customers
    WHERE City='Berlin' OR City='München';

    SELECT * FROM Customers


    WHERE Country='Germany' OR Country='Spain';

    NOT Example
    SELECT * FROM Customers
    WHERE NOT Country='Germany';

    Combining AND, OR and NOT


    You can also combine the AND, OR and NOT operators.

    The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be
    "Berlin" OR "München" (use parenthesis to form complex expressions):

    Example
    SELECT * FROM Customers
    WHERE Country='Germany' AND (City='Berlin' OR City='München');
    Example
    SELECT * FROM Customers
    WHERE NOT Country='Germany' AND NOT Country='USA';

    If we have some condition in same field we should be using IN

    SELECT * FROM Customers


    WHERE NOT Country='Germany' or Country='USA' or Country='Iraq';

    SQL IN Operator
    The IN operator allows you to specify multiple values in a WHERE clause.

    The IN operator is a shorthand for multiple OR conditions.

    IN Syntax

    SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (value1, value2, ...);

    Example
    SELECT * FROM Customers
    WHERE Country IN ('Germany', 'France', 'UK');

    The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

    Example
    SELECT * FROM Customers
    WHERE Country NOT IN ('Germany', 'France', 'UK');

    Q/return products with items equal to 20 30 40

    Q/ From film table ,get the items from (col1) equal 6


    Where col2*col3 greater than 40
    LIKE Operator Description

    WHERE Finds any values that start with "a"


    CustomerName
    LIKE 'a%'

    WHERE Finds any values that end with "a"


    CustomerName
    LIKE '%a'

    WHERE Finds any values that have "or" in any position WHERE CustomerName LIKE '%b%'
    CustomerName B in any position
    LIKE '%or%'

    WHERE Finds any values that have "r" in the second WHERE CustomerName LIKE '_r%'
    CustomerName position Should be 2 char length in this exmple
    LIKE '_r%'

    WHERE Finds any values that start with "a" and are at
    CustomerName least 2 characters in length
    LIKE 'a_%'

    WHERE Finds any values that start with "a" and are at
    CustomerName least 3 characters in length
    LIKE 'a__%'

    WHERE Finds any values that start with "a" and ends
    ContactName LIKE with "o"
    'a%o'

    % for any numbers of character


    _ For single of character

    The SQL LIKE Operator


    LIKE Syntax
    SELECT column1, column2, ...
    FROM table_name
    WHERE columnN LIKE pattern;

    Here are some examples showing different LIKE operators with '%' and '_' wildcards:

    Q/Get the customers


    1-whose col1 contain ali or ahmad
    2-phone number end with 0

    The IS NULL Operator


    It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

    We will have to use the IS NULL and IS NOT NULL operators instead.

    IS NULL Syntax
    SELECT column_names
    FROM table_name
    WHERE column_name IS NULL;

    IS NOT NULL Syntax

    SELECT column_names
    FROM table_name
    WHERE column_name IS NOT NULL;

    The IS NULL Operator


    The IS NULL operator is used to test for empty values (NULL values).

    The following SQL lists all customers with a NULL value in the "Address" field:

    Example

    SELECT CustomerName, ContactName, Address


    FROM Customers
    WHERE Address IS NULL;

    The IS NOT NULL Operator


    The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

    The following SQL lists all customers with a value in the "Address" field:

    Example

    SELECT CustomerName, ContactName, Address


    FROM Customers
    WHERE Address IS NOT NULL;

    You might also like