SQL - EXIST OR ALL?
sql where all
difference between any and all in sql
sql select all
sql case when exists
sql where = *
I have two different table student and grades; grade table has an attribute student_id which references student_id from student table. How do I find which student has every grade that exists?
If this is not clear,
Student ID Name 1 1 John 2 2 Paul 3 3 George 4 4 Mike 5 5 Lisa Grade Student_Id Course Grade 1 1 Math A 2 1 English B 3 1 Physics C 4 2 Math A 5 2 English A 6 2 Physics B 7 3 Economics A 8 4 Art C 9 5 Biology A Assume there is only grade a,b,c (no d, e or fail)
I want to find only John because He has grade a,b,c while other student like Paul(2) should not be selected because he does not have grade c. It does not matter which course he took, I just need to find if he has all the grades out there available.
Feel like I should something like exist or all function in sql but not sure. Please help. Thank you in advance.
I would use
GROUP BY and
HAVING, but like this:
SELECT s.Name FROM Student s JOIN Grade g ON s.ID = g.Student_Id GROUP BY s.id, s.Name HAVING COUNT(DISTINCT g.Grade) = (SELECT COUNT(DISTINCT g2.grade) FROM grade g2);
You say "all the grades out there", so the query should not use a constant for that.
You can use
HAVING COUNT(DISTINCT Grade) = 3 to check that the student has all 3 grades:
SELECT Name FROM Student S JOIN Grade G ON S.ID = G.Student_Id GROUP BY Name HAVING COUNT(DISTINCT Grade) = 3
S.Student on the join. Not sure what the difference is there.
SQL Server EXISTS By Practical Examples, The following example returns all rows from the customers table: SELECT customer_id, first_name, last_name FROM sales.customers WHERE EXISTS Search for Free Sql Databases on the New Internetcorkboard.com
By using exists
select * from student s where exists ( select 1 from grades g where g.Student_Id=s.ID group by g.Student_Id having count(distinct Grade)=3 )
with Student as ( select 1 as id,'John' as person union all select 2 as id,'Paul' as person union all select 3 as id,'jorge' ), Grades as ( select 1 as Graden, 1 as Student_Id, 'Math' as Course, 'A' as Grade union all select 2 as Graden, 1 as Student_Id, 'English' as Course, 'B' as Grade union all select 3 as Graden, 1 as Student_Id, 'Physics' as Course, 'C' as Grade union all select 4 as Graden, 2 as Student_Id, 'Math' as Course, 'A' as Grade union all select 5 as Graden, 2 as Student_Id, 'English' as Course, 'A' as Grade union all select 6 as Graden, 2 as Student_Id, 'Physics' as Course, 'B' as Grade ) select * from Student s where exists ( select 1 from Grades g where g.Student_Id=s.ID group by g.Student_Id having count(distinct Grade)=3 )
having count(distinct Grade)=3 i used this as in your sample data grade type is 3
SQL WHERE ANY | WHERE ALL, The SQL WHERE ANY and ALL syntax. The general ANY syntax is: SELECT column-names; FROM table-name SQL ALL Example. The ALL operator returns TRUE if all of the subquery values meet the condition. The following SQL statement returns TRUE and lists the product names if ALL the records in the OrderDetails table has quantity = 10 (so, this example will return FALSE, because not ALL records in the OrderDetails table has quantity = 10):
Before delving into the answer, here's a working SQL Fiddle Example so you can see this in action.
As Gordon Linoff points out in his excellent answer, you should use
Having Count(Distinct ... ) ... as an easy way to check.
However, I'd recommend changing your design to ensure that you have tables for each concern.
Grade table holds each student's grade per course. So it's more of a
StudentCourse table (i.e. it's the combination of student and course that's unique / gives you that table's natural key). You should have an actual
Grade table to give you the list of available grades; e.g.
create table Grade ( Code char(1) not null constraint PK_Grade primary key clustered ) insert Grade (Code) values ('A'),('B'),('C')
This then allows you to ensure that your query would still work if you decided to include grades D and E, without having to amend any code. It also ensures that you only have to query a small table to get the complete list of grades, rather than a potentially huge table; so will give better performance. Finally, it will also help you maintain good data; i.e. so you don't accidentally end up with students with grade X due to a typo; i.e. since the validation/constraints exist in the database.
select Name from Student s where s.Id in ( select sc.StudentId from StudentCourse sc group by sc.StudentId having count(distinct sc.Grade) = (select count(Code) from Grade) ) order by s.Name
Likewise, it's sensible to create a Course table. In this case holding Ids for each course; since holding the full course name in your StudentCourse table (as we're now calling it) uses up a lot more space and again lacks validation / constraints. As such, I'd propose amending your database schema to look like this:
create table Grade ( Code char(1) not null constraint PK_Grade primary key clustered ) insert Grade (Code) values ('A'),('B'),('C') create table Course ( Id bigint not null identity(1,1) constraint PK_Course primary key clustered , Name nvarchar(128) not null constraint UK_Course_Name unique ) insert Course (Name) values ('Math'),('English'),('Physics'),('Economics'),('Art'),('Biology') create table Student ( Id bigint not null identity(1,1) constraint PK_Student primary key clustered ,Name nvarchar(128) not null constraint UK_Student_Name unique ) set identity_insert Student on --inserting with IDs to ensure the ids of these students match data from your question insert Student (Id, Name) values (1, 'John') , (2, 'Paul') , (3, 'George') , (4, 'Mike') , (5, 'Lisa') set identity_insert Student off create table StudentCourse ( Id bigint not null identity(1,1) constraint PK_StudentCourse primary key , StudentId bigint not null constraint FK_StudentCourse_StudentId foreign key references Student(Id) , CourseId bigint not null constraint FK_StudentCourse_CourseId foreign key references Course(Id) , Grade char /* allow null in case we use this table for pre-results; otherwise make non-null */ constraint FK_StudentCourse_Grade foreign key references Grade(Code) , Constraint UK_StudentCourse_StudentAndCourse unique clustered (StudentId, CourseId) ) insert StudentCourse (StudentId, CourseId, Grade) select s.Id, c.Id, x.Grade from (values ('John', 'Math', 'A') ,('John', 'English', 'B') ,('John', 'Physics', 'C') ,('Paul', 'Math', 'A') ,('Paul', 'English', 'A') ,('Paul', 'Physics', 'B') ,('George', 'Economics','A') ,('Mike', 'Art', 'C') ,('Lisa', 'Biology', 'A') ) x(Student, Course, Grade) inner join Student s on s.Name = x.Student inner join Course c on c.Name = x.Course
SQL, ALL operator is used to select all tuples of SELECT STATEMENT. It is also used to compare a value to every value in another value set or result from a subquery. EXISTS (Transact-SQL) 03/15/2017; 5 minutes to read; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . Specifies a subquery to test for the existence of rows. Transact-SQL Syntax Conventions. Syntax EXISTS ( subquery ) Arguments. subquery Is a restricted SELECT statement.
SQL, Delete the record of all the customer from Order Table whose last name is 'Mehra'. DELETE FROM Orders WHERE EXISTS (SELECT * FROM customers WHERE I have two different table student and grades; grade table has an attribute student_id which references student_id from student table. How do I find which student has every grade that exists? If
SQL All operator, ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be ANY and ALL are all SQL logical operators used in conjunction with sub-query. For explanation let us consider a table customers customers(Name, Age, Salary) This
SQL: EXISTS Condition, This SQL tutorial explains how to use the SQL EXISTS condition with syntax and This website would not exist without the advertisements we display and your kind Now let's find all of the records from the customers table where there is at The EXISTS operator is a logical operator that allows you to check whether a subquery returns any row. The EXISTS operator returns TRUE if the subquery returns one or more row. The following shows the syntax of the SQL Server EXISTS operator: EXISTS ( subquery) In this syntax, the subquery is a SELECT statement only.
- Tag your question with the DBMS you are using (ie SQL Server, Oracle, MySQL, etc)
- Also, should the first column in the
GradeID? Right now there are two "Grade" columns...