SQL Exercise: Neither New York residents nor with a grade above 100
SQL Boolean Operator Statement: Exercise-5 with Solution.
From the following table, write a SQL query to identify customers who are not from the city of 'New York' and do not have a grade value greater than 100. Return customer_id, cust_name, city, grade, and salesman_id..
Sample table: customer
customer_id | cust_name | city | grade | salesman_id -------------+----------------+------------+-------+------------- 3002 | Nick Rimando | New York | 100 | 5001 3007 | Brad Davis | New York | 200 | 5001 3005 | Graham Zusi | California | 200 | 5002 3008 | Julian Green | London | 300 | 5002 3004 | Fabian Johnson | Paris | 300 | 5006 3009 | Geoff Cameron | Berlin | 100 | 5003 3003 | Jozy Altidor | Moscow | 200 | 5007 3001 | Brad Guzan | London | | 5005
Sample Solution :
-- This query selects all columns from the 'customer' table.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'customer').
FROM customer
-- Filters the rows to only include those where neither the 'city' column has the value 'New York'
-- nor the 'grade' column has a value greater than 100.
WHERE NOT (city = 'New York' OR grade > 100);
Output of the Query:
customer_id cust_name city grade salesman_id 3009 Geoff Cameron Berlin 100 5003
Code Explanation:
The said SQL query that is selecting all columns (*) from the 'customer' table where NOT (the value of the "city" column is 'New York' OR the value of the "grade" column is greater than 100).
In this query, all rows from the "customer" table that do not match either of these conditions will be returned. It will return all the rows where city is not 'New York' and also the rows where the grade is less than or equal to 100.
Relational Algebra Expression:
Relational Algebra Tree:
Explanation :
Visual presentation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Customers from New York or not with a given grade.
Next SQL Exercise: Using AND, OR operator with a specified condition.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/sql-boolean-operator-exercise-5.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics