SQL Fundamentals
This content delves into the SQL fundamentals with a focus on set operators such as UNION, INTERSECT, and EXCEPT within the Microsoft Enterprise Consortium. Learn about the principles, commands, and examples associated with these set operators to enhance your SQL skills and data manipulation techniques. Explore the functionalities and rules of each set operator to efficiently combine, find overlaps, and extract distinct data sets for optimized data querying and analysis in Microsoft SQL Server environments.
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 Set Operators: UNION, INTERSECT, EXCEPT 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 ll 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. If there is something besides the topics for this preentation that you re not familiar with, please review earlier lessons in this SQL Fundamental series. 2 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium SET OPERATORS There are 3 set operator commands: UNION INTERSECT EXCEPT UNION combines two or more data sets. + = A B A and B 3 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Set Operators: UNION UNION: If the data sets overlap (have common data), the duplicates are removed. + A = A and B B UNION compatible rule: This rule applies to all 3 set operators. The # of columns must be the same in both data sets. The column data types must be in the same sequence and compatible. Example: Column 2 in both data sets must be have the same data type. 4 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Set Operators: INTERSECT INTERSECT: If the data sets overlap, only the overlap is in the result. + A = B 5 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Set Operators: EXCEPT EXCEPT: If the data sets overlap, only the portion not in common with the second data set is in the result. + A = B 6 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium UNION example p1 List students who got 90 or higher on their CONTRIBUTE evaluation item (they were evaluatees). select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90; The output has 14 rows. 7 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium UNION example p2 List students who got 90 or higher on their RELIABLE evaluation item (they were evaluatees). select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; The output has 11 rows. 8 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium UNION example p3 UNION the lists to combine the output. select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 UNION select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; 9 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium UNION example p4 Remove the evaluation item ID column. This will reduce the list because some students were listed twice once for CONTRIBUTE and once for RELIABLE. The output drops to 20 rows. select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 UNION select stdid, stdfname, stdlname, eval_item_ID, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; 10 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium INTERSECT example Change UNION to INTERSECT. Now, we see which students got 90 or higher in both CONTRIBUTE and RELIABLE> select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 INTERSECT select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; 11 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium EXCEPT example Now, use EXCEPT. We see which students got 90 or higher for CONTRIBUTE but didn t for RELIABLE. select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'CONTRIBUTE' and score >= 90 EXCEPT select stdid, stdfname, stdlname, score from students, evaluations, eval_items_scores where stdid = evaluateeID and evaluations.eval_ID = eval_items_scores.eval_ID and eval_item_ID = 'RELIABLE' and score >= 90; 12 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium What was covered Set Operators UNION INTERSECT EXCEPT UNION compatible rule 13 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 14 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
