Logical Conjunctions

Introduction

So far, we have stated the conditions one at a time. This made their interpretation kind of easy. Sometimes, you will need to test one condition that depends on, or is related to, another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true.

Imagine you have a list of students and you want to study some statistics of your database. You already know that, to get a list of girls, you can filter the students based on the gender. You would execute a statement as:

SELECT StudentNumber [Student #],
       FirstName [First Name],
       LastName "Last Name",
       Gender,
       City,
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  Gender = N'Female';
GO

This would produce:

On the other hand, imagine you want to get a list of students who live in Silver Spring. You would execute a statement as:

SELECT StudentNumber [Student #],
       FirstName [First Name],
       LastName "Last Name",
       Gender,
       City,
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  City = N'silver spring';
GO

This would produce:

From these two results, notice that there is no relationship between the fact that one student is a female and the fact that a student lives in Silver Spring. Still, the SQL allows you to combine two condition and produce a result of what those two conditions have in common. This is referred to as a logical conjunction.

Practical Learning: Introducing Conjunctions and Disjunctions

1. Start the computer and log in
2. Launch Microsoft SQL Server and click Connect
3. In the Object Explorer, expand Databases
4. From the previous lessons, make sure you have the Department Store1 database.
   In the Object Explorer, expand DepartmentStore1
5. Right-click DepartmentStore1 and click New Query
6. Expand Tables
7. Right-click Inventory.StoreItems and click Edit Top 200 Rows
8. On the Query Designer toolbar, click the Show Diagram Pane button , the Show Criteria Pane button , and the Show SQL Pane button
9. In the SQL pane, change the statement as follows:

   SELECT ItemNumber AS [Item #],
          Manufacturer,
          Category,
          SubCategory AS [Sub-Category], 
          ItemName AS [Name/Description], 
          UnitPrice AS [Unit Price]
   FROM   Inventory.StoreItems

10. On the main menu, click Query Designer -> Execute SQL

Creating a Logical Conjunction

The simplest logical condition involves two columns of a table. You can create the conjunction visually or with code.

To visually create a logical conjunction:

• In the result of a Query Editor or in the Results section of the Query Designer
  • To select a range of records, click the row header of one of the records, press and hold Shift, click the row header on the other extreme, and then release Shift
  • To randomly select records, click the row header of one of the records, press and hold Ctrl, click each the row header of each of the records, and then release Ctrl. Here is an example:

    

• Open a table in the Query Designer and display the Criteria pane. In the Criteria section, click the Filter box that corresponds to the first desired field and type the condition. Click the Filter box that corresponds to the second desired field and type the other condition. Then execute the query.

To create a logical conjunction in SQL, you use the AND operator to combine two conditions. The formula to follow is:

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 AND Condition2

WhatColumn(s) and WhatObject are the same way we have used them so far. The AND keyword is the operator that joins the conditions. Each condition is written as a SQL operation using the formula:

Column operator Value

The Condition1 is the first that would be examined. If the first condition is false, the whole WHERE statement is false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined.

Based on this, suppose we want to get a list of female students who live in Silver Spring. The SQL statement used to get this list can be written as follows:

SELECT StudentNumber [Student #],
       FirstName [First Name],
       LastName "Last Name",
       Gender,
       City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  Gender = N'female' AND City = N'silver spring';
GO

We stated that each condition was separately evaluated. For this reason, to make the conjunction statement easier to read, each condition should be included in parentheses. Therefore, the above SQL statement can be written as follows:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name", 
       Gender, 
       City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (Gender = N'female') AND (City = N'silver spring')

This would produce:

Practical Learning: Creating a Logical Conjunction

1. In the Criteria pane, click the box at the intersection of Manufacturer and Filter
2. To see the list of items made by Calvin Klein, type Calvin Klein
3. To see the result, right-click the Diagram pane and click Execute SQL
4. To see items made by Calvin Klein but only those that cost 150 or less, click the box at the intersection of UnitPrice and Filter
5. Type <=150
6. To see the result, right-click the Diagram pane and click Execute SQL
    
   
7. To hide the columns, in the Criteria pane, click the check box at the intersection of Manufacturer and Output to remove the check mark
8. To see the result, on the Query Designer toolbar, click the Execute SQL button
   
9. To show the column again, in the Criteria pane, click the check box at the intersection of Size and Output
10. Click Filter header to select all filters and press Delete to remove them
11. Click the SQLQuery1.sql tab
12. Imagine a customer wants to rent a 1-bedroom apartment. Of course, the apartment has to be available. To check this, type the following:

    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           CASE aparts.Available
               WHEN 0 THEN N'No or Not Yet'
               WHEN 1 THEN N'Yes'
           END "Available"
    FROM Presentation.Units aparts
    WHERE (Bedrooms = 1) AND (Available = 1)
    ORDER BY [Monthly Rent], "Primary Deposit";
    GO

13. To execute, press F5

Logical Disjunctions

 

Introduction

The logical conjunction is used to check that two conditions are true, in which case both conditions must be valid. In a logical conjunction, if either condition is false, the whole statement is false. Sometimes you have two conditions that are equally important, in which the validity of either condition is good enough to make the whole statement true. This type of statement is referred to as logical disjunction.

Besides their names, the conjunction and the disjunction have many logical differences. A logical conjunction needs two different fields. Although you can (there is no rule against it), if you use the same field for both conditions of a conjunction, the statement would produce nothing:

This means that you should always use more than one column. On the other hand, a logical disjunction can be performed on one field. In this case, you would ask the interpreter to find out if the field matches this or that value. This means that, among the many values that the field has, you want to isolate two values and consider only either of them.

Like a conjunction, a disjunction can also be performed on more than one field.

Creating a Logical Disjunction

As mentioned already, a logical disjunction can be used on one or more fields. You can create the disjunction visually or using code.

To visually create a logical disjunction, open a table in the Query Designer and display the Criteria pane. You have various options. In the Criteria section:

• To use only one box, click it in the Filter column. Type the first condition, followed by OR, followed by the other condition. You can keep ORing the conditions in the same Filter box
• To use more than one box:
  • If you want to examine the value for only one column, click the Filter box that corresponds to one column and type the first condition. Click the box at the intersection of the field and the Or... column, then type the second condition
  • If you want to examine that value in more than one column, click the Filter box that corresponds to one of the columns and type the first condition. Click the Or... box that corresponds to the second field and type the other condition

After entering the conditions, execute the query.

To create a logical disjunction in SQL, you use the OR operator between two conditions. The formula to follow is:

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 OR Condition2

WhatColumn(s) and WhatObject are the same we have used so far. The OR keyword is the operator that joins the conditions. Each condition is written as a SQL operation using the formula:

Column operator Value

In this case:

• If you are examining the values of only one column, use it in both conditions but use the desired operator and the desired value in each condition. Here is an example that wants to get a list of students who live either in Bethesda or in Silver Spring:

  SELECT StudentNumber [Student #],
         FirstName [First Name], 
         LastName "Last Name",
         Gender, City, 
         ParentsNames [Parents Names]
  FROM   Registration.Students
  WHERE  (City = N'bethesda') OR (City = N'silver spring');
  GO

• If you are examining the values of different fields, use each on its own condition followed by the operator and the value of your choice. Here is an example that wants to get a list of students that either are female (regardless of where they live) or live in Silver Spring (regardless of their gender):

  SELECT StudentNumber [Student #],
         FirstName [First Name],
         LastName "Last Name", 
         Gender, City, 
         ParentsNames [Parents Names]
  FROM   Registration.Students
  WHERE  (Gender = N'female') OR (City = N'silver spring');
  GO

Logical conjunctions and disjunctions operator differently. As mentioned already, in a logical conjunctions, both conditions must be true for the whole statement to be true. In a logical disjuction, the interpreter first examines the first condition. If that condition is true, it concludes that the whole statement is true and there is no need to examine the second condition. If the first condition is false, then it examines the second condition. If the second condition is true, even if the first condition was false, the whole statement is true. Consider the above first statement:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name", 
       Gender, City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (City = N'bethesda') OR (City = N'silver spring');
GO

The interpreter would check the City value of a record:

1. If the record indicates that the student lives in Bethesda, that record would be included and the interpreter would move to the next record
2. If the record indicates that the student lives not in Bethesda (the value of the City column is not Bethesda), then the interpreter would apply the second condition, which consists of checking whether the student lives in Silver Spring instead:
   1. If the student lives in Silver Spring, the record is valid
   2. If the student doesn't live in Silver Spring, and the record has already indicated that the student doesn't live in Bethesda, then the record is rejected (it will not be included in the result)

The interpreter then moves to the next record.

Consider our second statement:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name", 
       Gender, City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (Gender = N'female') OR (City = N'silver spring');
GO

Once again, the interpreter would check each record:

1. The interpreter first checks the value of the Gender column. If a record indicates that the student is Female, that record is valid and the interpreter moves to the next record
2. If the record indicates that the student is not Female (the value of the Gender column is not Female), then the interpreter checks the value of the City column:
   1. If the record indicates that the student lives in Silver Spring, then the record is valid, even if the student is not Female
   2. If the record indicates that the student doesn't live in Silver Spring, and the record has already indicated that the student is not female, the whole record is rejected and the interpreter moves to the next record

Practical Learning: Creating a Logical Disjunction

1. Click the other tab
2. To see the items made by either Calvin Klein or Anne Klein, in the Criteria pane, click the box at the intersection of Manufacturer and Filter
3. Type N'Calvin Klein' OR N'Anne Klein'
4. To see the result, right-click the Diagram pane and click Execute SQL
    
   
5. In the Criteria pane, click Manufacturer, press Tab six times and type Calvin Klein
6. To see the items made by either Calvin Klein or Tommy Hilfiger, in the Criteria pane, click the box at the intersection of Manufacturer and the first Or...
7. Type Tommy Hilfiger
8. To see the result, right-click the Diagram pane and click Execute SQL
    
   
9. In the Criteria pane, click Manufacturer, press Tab six times and press Delete
10. Press Tab and press Delete

Topics on Logical Conjunctions and Disjunctions

Introduction

So far, for our introduction to conjunctions and disjunctions, we used only string-based columns. When it comes to string-based conditions, you can use the LIKE operator to either match a string exactly or specify an approximation (a character, a range of characters, or a sub-string). Here is an example:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name", 
       Gender, City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (Gender = N'male') AND (LastName LIKE N'%an');
GO

This LIKE condition is meant to find all students whose last name end with an:

Practical Learning: Using Conjunctions and Disjunctions in Strings

1. To see the items made either by a Klein (Calvin Klein or Anne Klein) or a Lauren (Ralph Lauren, Polo Ralph Lauren, or Lauren By Ralph Lauren), click the box at the intersection of Manufacturer and Filter
2. Type like %klein%
    
   
3. Press Tab and type like %lauren%
4. To see the result, right-click the Diagram pane and click Execute SQL
    
   

Conjunctions, Disjunctions, and NULL Fields

To include the ability to check whether a field is null, in its condition, apply IS NULL to the column's name.

Practical Learning: Checking for Nullity

1. In the Diagram pane, click the check box of DiscountRate
2. To see the items made by Ralph Lauren, in the Criteria pane, replace klein with lauren
3. Press Tab and press Delete
4. To see the result, right-click one of the panes and click Execute SQL
5. Notice that some items don't show a discount (the DiscountRate field shows NULL)
    
   
6. To see only items that qualify for a discount, click the box at the intersection of DiscountRate and Filter
7. Type is null
8. To see the result, right-click one of the panes and click Execute SQL
    
   
9. In the intersection of DiscountRate and Filter, delete IS NULL
10. To see the list of items made by Coach, in the Criteria pane, replace LIKE N'%lauren%' by Coach
    
11. Right-click any section of the window and click Execute SQL

Conjunctions, Disjunctions, and the Right Values

Besides string-based fields, any column can participate in a logical conjunction or disjunction. For types other than strings, follow their rules:

• If you are visually creating a condition, simply type the desired value in the Criteria pane and the studio would take care of identifying it
• If you are creating the condition using code:
  • If the field is string-based or date/time based, include its value in single-quotes
  • If the column is numeric based, provide desired number value
  • If the field is Boolean, use 0 for a false value and 1 for true

Practical Learning: Filtering for the Right Values

1. To see the items made by Coach and that cost at least 250, in the Criteria pane, click the box at the intersection of UnitPrice and Filter
2. Type  !< 250
3. To see the result, right-click one of the panes and click Execute SQL
4. In the Criteria pane, delete >= 250
5. In the Criteria pane, click Manufacturer, press Tab six times and press Delete
6. To get a list of items made by Ralph Lauren, Kenneth Cole, or a Klein, type LIKE N'%lauren%' OR LIKE N'%kenneth cole%' OR LIKE N'%klein%'
7. To see the result, right-click one of the panes and click Execute SQL
    
   
8. In the Criteria pane, click Manufacturer, press Tab six times and press Delete
9. To see the items made by Ralph Lauren again, type like %lauren%

Negating a Condition

As seen in previous lessons, there are many ways you can negate a Boolean condition. Remember that, to negate IS NULL, you can use IS NOT NULL.

Probably the easiest way to negate a condition is to precede it with the NOT operator. Here is an example:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name", 
       Gender, City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (Gender = N'female') AND NOT (City = N'silver spring');
GO

Or better, include NOT and its statement in parentheses. Here is an example:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name", 
       Gender, City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (Gender = N'female') AND (NOT (City = N'silver spring'));
GO

In the same way, you can negate the conditions individually. You can also negate the whole statement. In this case, after WHERE, type NOT() and, in the parentheses, include the logical conjunction or disjunction.

Practical Learning: Negating a Condition

1. To see Ralph Lauren's items that qualify for a discount, in the Criteria pane, click the box at the intersection of DiscountRate and Filter
2. Type is not null
3. To see the result, right-click one of the panes and click Execute SQL
    
   

Combining Conditions

The SQL allows you to create statements as complex as you want by combining the logical conjunctions and/or disjunctions.

To visually combine conditions, you have various options. If you are combining somewhat independent conditions, type each in the desired Filter or Or... box. If you are creating logical disjunctions that must be evaluated in groups, type each disjunctions in it own Filter box

Using SQL, you can create as many conditions as possible. Here is an example:

SELECT StudentNumber [Student #], 
       FirstName [First Name],
       LastName "Last Name",
       Gender, City,
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  (Gender = N'male') AND (City = N'silver spring')
                          AND (LastName LIKE N'%n');
GO

If the statement contains only conjunctions, the interpreter would examine the first condition:

• If the first condition is false, the whole statement is false. The record is rejected and the interpreter moves to the next record
• If the first condition is true, the interpreter puts a positive flag and moves to the next condition:
  • If the second condition is false, the whole statement is false and the interpreter moves to the next record
  • If the second condition is true, the interpreter puts a second positive flag and moves to the next condition:
    • If the next condition is false, the whole statement is false and the interpreter moves to the next record
    • If the next condition is true, and if the statement contains only three conditions, the record is valid and will be included in the result

The interpreter follows these steps for as many conjunctions as are included in the statement. If the statement contains only disjunctions, the approach is logically different. The interpreter would examine the first condition:

• If the first condition is true, the whole statement is true. The record is valid (the record will be included in the result) and the interpreter moves to the next record
• If the first condition is false, the interpreter moves to the second condition:
  • If the second condition is true, the whole statement is true and the record is valid (it will be included in the result). The interpreter moves to the next record
  • If the second condition is false, the interpreter moves to the next condition:
    • If the next condition is true, the whole statement is true. The record is valid and will be included in the result
    • If the next condition is false, and if the statement contains only three conditions, the whole record is rejected (it will not be included in the result)

The interpreter follows these steps for as many conjunctions as are included in the statement.

You can create a statement that is a combination of logical conjunctions and disjunctions. To make the statement easier to read and because of precedence rules, you should group the conditions in parentheses. Consider the following example:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName "Last Name",
       Gender, City, 
       ParentsNames [Parents Names]
FROM   Registration.Students
WHERE  ((Gender = N'female') AND (City = N'silver spring'))
	OR
       ((Gender = N'male') AND (City = N'bethesda'));
GO

This statement is essentially one main logical disjunction that checks two logical conjunctions. The first conjunction asks the interpreter to get a list of girls who live in Silver Spring. The second conjunction wants a list of boys who live in Bethesda. In reality, the clerk wants a list of girls who live in Silver Spring and boys who live in Bethesda:

In the same way, you can create complex conditions.

Practical Learning: Combining Conditions

1. To see the list of discounted items made either by Lauren or a Klein, click the box at the intersection of Manufacturer and the first Or...
2. Type like %lauren%
3. Press Tab and type like %klein%
4. Make sure the Filter box for DiscountRate displays IS NOT NULL.
   Click the box at the intersection of DiscountRate and the first Or... box
5. Type is not null
6. To see the result, right-click the Diagram pane and click Execute SQL
    
   
7. Click the SQLQuery1.sql table and type the following::

   SELECT  ItemNumber AS [Item #],
   	Manufacturer,
   	Category,
   	SubCategory AS [Sub-Category],
   	ItemName AS [Name/Description],
   	UnitPrice AS [Unit Price], 
           DiscountRate AS [%Discount]
   FROM    Inventory.StoreItems
   WHERE   (Manufacturer LIKE N'%lauren%') AND (DiscountRate IS NOT NULL)
           OR
           (Manufacturer LIKE N'%klein%') AND (DiscountRate IS NOT NULL)

8. To execute, press F5
9. To see the result, right-click the Diagram pane and click Execute SQL
    
   
10. Click the SQLQuery1.sql tab
11. Imagine a customer who wants to rent a one-bedroom apartment on the first floor (apartments of the first floor are numbered from 100 to 199). To check this, change the statement as follows:

    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           CASE aparts.Available
    	    WHEN 0 THEN N'No or Not Yet'
    	    WHEN 1 THEN N'Yes'
           END "Available"
    FROM Presentation.Units aparts
    WHERE (aparts.UnitNumber < 200) AND
          (aparts.Bedrooms = 1) AND
    	  (aparts.Available = 1)
    ORDER BY [Monthly Rent], "Primary Deposit";
    GO

12. To execute, press F5

    

13. Close Microsoft SQL Server
14. When asked whether you want to save something, click No

Previous	Copyright © 2008-2022, FunctionX, Inc.	Next