Mastering SQL Logical Operators for Efficient Data Filtering
Learn how to utilize logical operators in the WHERE clause of SQL SELECT statements to filter data effectively. Explore examples of using AND and OR operators to retrieve specific information based on multiple criteria. Enhance your SQL skills with this comprehensive guide prepared by Jennifer Kreie from New Mexico State University and hosted by the University of Arkansas.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Microsoft Enterprise Consortium SQL Fundamentals Logical operators in the WHERE clause Microsoft Enterprise Consortium: http://enterprise.waltoncollege.uark.edu Microsoft Faculty Connection/Faculty Resource Center http://www.facultyresourcecenter.com 1 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium What you need Log in to MEC for this lesson and into MSSMS (Microsoft SQL Server Management Studio). Be sure to select your account ID under Database in the Object Explorer pane, similar to the example shown here. You need to be familiar with some of the concepts presented in earlier lessons in this SQL Fundamentals series. 2 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Logical Operators: AND The WHERE clause in the SELECT statement allows you to filter output based on a single criterion or several criteria. To use more than one criterion in the WHERE clause you need to learn about logical operators. AND The AND operator specifies that two conditions be met. For example: List employees in marketing who were hired before 2005. Department = marketing AND Hire date < 01/01/2005 3 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Logical Operators: OR OR The OR operator specifies that at least one condition be met, though both may be. For example: List employees who work in the Southwest or Northwest regional office. Office location = Southwest OR office location = Northwest 4 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Logical Operators: Output for AND Assume you have a SELECT statement with the AND operator in the WHERE clause. When is data displayed? The table below shows the possible combinations when evaluating two conditions using the AND operator. Condition 1 AND NO YES NO YES Condition 2 NO NO YES YES Display Data? Don t display. Don t display. Don t display. Display. YES means the condition is met. 5 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Logical Operators: Output for OR Assume you have a SELECT statement with the OR operator in the WHERE clause. When is data displayed? The table below shows the possible combinations when evaluating two conditions using the OR operator. Condition 1 OR NO YES NO YES Condition 2 NO NO YES YES Display Data? Don t display. Display. Display. Display. YES means the condition is met. 6 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Query S-T database using AND operator Show students in the ITPROS team with an ISYS major. /* Show students in the ITPROS team AND with an ISYS major. */ select std_teamID, stdmajor, stdfname, stdlname from students where std_teamID = 'ITPROS' AND stdmajor = 'ISYS'; 7 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Query S-T database using OR operator Show students majoring in computer science or accounting. /* Show students who are majoring computer science (COMPSC) OR accounting (ACCT). */ select std_teamID, stdmajor, stdfname, stdlname from students where stdmajor = 'COMPSC' OR stdmajor = 'ACCT'; 8 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Using operator OR with AND Show students majoring in accounting or marketing that are in team ITPROS or SYSDES. /* Show students majoring in accounting OR marketing AND that are in the ITPROS team OR the SYSDES team.*/ select std_teamID, stdmajor, stdfname, stdlname from students where stdmajor = 'ACCT' OR stdmajor = 'MKTG' AND std_teamID = 'ITPROS' OR std_teamID = 'SYSDES'; Is there a problem with this output? 9 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Using operator OR with AND Show students majoring in accounting or marketing that are in team ITPROS or SYSDES. /* Show students majoring in accounting OR marketing AND that are in the ITPROS team OR the SYSDES team.*/ select std_teamID, stdmajor, stdfname, stdlname from students where (stdmajor = 'ACCT' OR stdmajor = 'MKTG') AND(std_teamID = 'ITPROS' OR std_teamID = 'SYSDES'); 10 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Logical Operators: NOT You can use NOT to negate an evaluation. Show students who are NOT in the ITPROS and SYSDES teams. /* Show students who are NOT in ITPROS and NOT in SYSDES. */ select std_teamID, stdmajor, stdfname, stdlname from students where NOT std_teamID = 'ITPROS' AND NOT std_teamID = 'SYSDES'; Can you think of a way to write the SQL without using NOT? 11 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium What was covered Logical operators in the WHERE clause. AND OR It is best to use parentheses around OR conditions. NOT 12 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Resources http://enterprise.waltoncollege.uark.edu/mec.asp Microsoft Faculty Connection Faculty Resource Center http://www.facultyresourcecenter.com/ Microsoft Transact-SQL Reference http://msdn.microsoft.com/en-us/library/aa299742(v=SQL.80).aspx AdventureWorks Sample Database http://msdn.microsoft.com/en-us/library/ms124659%28v=sql.100%29.aspx 13 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
