(SQL Example for Citizen Data Scientist & Business Analyst)
SQL | UNIQUE Constraint
Unique constraint in SQL is used to check whether the sub query has duplicate tuples in it’s result. It returns a boolean value indicating the presence/absence of duplicate tuples. Unique construct returns true only if the sub query has no duplicate tuples, else it return false.
Important Points:
- Evaluates to true on an empty sub query.
- Returns true only if there are unique tuples present as the output of the sub query (two tuples are unique if the value of any attribute of the two tuples differ).
- Returns true if the sub query has two duplicate rows with at least one attribute as NULL.
Syntax:
SELECT table.ID FROM table WHERE UNIQUE (SELECT table2.ID FROM table2 WHERE table.ID = table2.ID);
Note: During the execution, first the outer query is evaluated to obtain table.ID. Following this, the inner sub query is processed which produces a new relation that contains the output of inner query such that table.ID == table2.ID. If every row in the new relation is unique then unique returns true and the corresponding table.ID is added as a tuple in the output relation produced. However, if every row in the new relation is not unique then unique evaluates to false and the corresponding table.ID is not add to the output relation.
Note: The SQL statement without UNIQUE clause can also be written as:
SELECT table.ID FROM table WHERE 1 <= (SELECT count(table2.ID) FROM table2 WHERE table.ID = table2.ID);
Queries
Example 1: Find all the instructors that taught at most one course in the year 2017.
Instructor relation:
EMPLOYEEID | NAME | COURSEID | YEAR |
---|---|---|---|
77505 | Alan | SC110 | 2017 |
77815 | Will | CSE774 | 2017 |
85019 | Smith | EE457 | 2017 |
92701 | Sam | PYS504 | 2017 |
60215 | Harold | HSS103 | 2016 |
77505 | Alan | BIO775 | 2017 |
92701 | Sam | ECO980 | 2017 |
- SQL Query:
SELECT I.EMPLOYEEID, I.NAME FROM Instructor as I WHERE UNIQUE (SELECT Inst.EMPLOYEEID FROM Instructor as Inst WHERE I.EMPLOYEEID = Inst.EMPLOYEEID and Inst.YEAR = 2017);
Output:
EMPLOYEEID | NAME |
---|---|
77815 | Will |
85019 | Smith |
Explanation: In the Instructor relation, only instructors Will and Smith teach a single course during the year 2017. The sub query corresponding to these instructors contains only a single tuple and therefore the unique clause corresponding to these instructors evaluates to true thereby producing these two instructors in the output relation.
Example 2: Find all the courses in Computer Science department that has only a single instructor allotted to that course.
Course relation:
COURSEID | NAME | DEPARTMENT | INSTRUCTORID |
---|---|---|---|
CSE505 | Computer Network | Computer Science | 11071 |
CSE245 | Operating System | Computer Science | 74505 |
CSE101 | Programming | Computer Science | 12715 |
HSS505 | Psychology | Social Science | 85017 |
EE475 | Signals & Systems | Electrical | 22150 |
CSE314 | DBMS | Computer Science | 44704 |
CSE505 | Computer Network | Computer Science | 11747 |
CSE314 | DBMS | Computer Science | 44715 |
- SQL Query:
SELECT C.COURSEID, C.NAME FROM Course as C WHERE UNIQUE (SELECT T.INSTRUCTORID FROM Course as T WHERE T.COURSEID = C.COURSEID and C.DEPARTMENT = 'Computer Science');
Output:
COURSEID | NAME |
---|---|
CSE245 | Operating System |
CSE101 | Programming |
MySQL Tutorials for Business Analyst: How to use Sub-Queries in MySQL
Learn to Code SQL Example – SQL | UNIQUE Constraint
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.