Master SQL Fundamentals with IN and IS NULL Clauses - Microsoft Enterprise Consortium
Learn how to utilize the IN and IS NULL clauses in SQL fundamentals through Microsoft Enterprise Consortium's comprehensive lessons. Understand how to use these operators effectively for data querying tasks. Enhance your SQL skills and database management knowledge with practical examples and step-by-step instructions.
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 WHERE clause using IN and IS NULL 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 An alternative to using the OR operator In a previous lesson, we covered the use of the logical operators AND, OR and NOT. This lesson presents other operators you can use in the WHERE clause of a SELECT statement. IN ( , , ) An alternative to the OR operator. IS NULL Check for null values. 3 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Using IN instead of OR List students in these teams: ITPROS, SYSDES or TECHSO. /* List students who are in these teams: ITPROS, SYSDES or TECHSO. */ /* using OR */ select std_teamID, stdfname, stdlname from students where std_teamID = 'ITPROS' ORstd_teamID = 'SYSDES' ORstd_teamID = 'TECHSO'; /* using IN */ select std_teamID, stdfname, stdlname from students where std_teamID IN ('ITPROS', 'SYSDES', 'TECHSO'); 4 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium NOT IN List students who are NOT in these teams: ITPROS, SYSDES or TECHSO. /* List students who are NOT in these teams: ITPROS, SYSDES or TECHSO. */ select std_teamID, stdfname, stdlname from students where std_teamID NOT IN ('ITPROS', 'SYSDES', 'TECHSO'); 5 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Working with NULL (no data) List students who have not been assigned to a team. This is the case if the teamID field is null (no data). /* List students who have not been assigned to a team. */ select std_teamID, stdfname, stdlname from students where std_teamID IS NULL; /* List students who are assigned to a team. */ select std_teamID, stdfname, stdlname from students where std_teamID IS NOT NULL; 6 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Working with NULL - continued Count how many students there are, how many are not assigned to a team and how many are assigned to a team. /* How many students are there? */ select count(*) from students; /* How many students have not been assigned to a team. */ select count(*) from students where std_teamID IS NULL; /* How many students have been assigned to a team. */ select count(*) from students where std_teamID IS NOT NULL; 7 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium What was covered More operators to use in the WHERE clause. IN ( ) - an alternative to using OR. NOT IN IS NULL IS NOT NULL 8 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 9 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
