DISTINCT queries in SQL allow us to retrieve unique values from a column or a combination of columns in a table. By eliminating duplicates, we can obtain a clean and accurate representation of the data. DISTINCT queries are commonly used in scenarios where duplicate values may distort analysis or reporting.
--Distinct Queries
CREATE TABLE grades (name TEXT, subject TEXT, grade INTEGER);
INSERT INTO grades (name, subject, grade) VALUES
("John", "CompSci", 97), ("Eric", "CompSci", 88), ("Carol", "Arts", 99),
("John", "History", 93), ("Andrew", "History", 82), ("Eric", "History", 87),
("Steve", "Physics", 91), ("John", "Physics", 84), ("Barney", "Physics", 97);
SELECT "all names", COUNT(name) FROM grades;
SELECT "unique names", COUNT(DISTINCT name) FROM grades;
SELECT DISTINCT name FROM grades;
The DISTINCT keyword is used in conjunction with the SELECT statement to specify the columns for which we want unique values. It retrieves distinct records from the specified columns, filtering out duplicates. DISTINCT queries can be applied to single or multiple columns.
DISTINCT queries are particularly useful when working with large datasets that may contain redundant or repetitive information. By using DISTINCT, we can streamline our results and focus on unique values, providing a clearer understanding of the underlying data.
Distinct Queries Exercise Solution
--Code Completed
CREATE TABLE grades (name TEXT, subject TEXT, grade INTEGER);
INSERT INTO grades (name, subject, grade) VALUES
("John", "CompSci", 97), ("Eric", "CompSci", 88), ("Carol", "Arts", 99),
("John", "History", 93), ("Andrew", "History", 82), ("Eric", "History", 87),
("Steve", "Physics", 91), ("John", "Physics", 84), ("Barney", "Physics", 97);
-- enter code here
SELECT DISTINCT subject FROM grades;
DISTINCT queries find application in various scenarios, including data analysis, generating reports, and ensuring data integrity. They help us identify unique values and eliminate redundancy, leading to more reliable insights.
