w3resource

SQL Exercise: Customers from New York or not with a given grade

SQL Boolean Operator Statement: Exercise-4 with Solution

From the following table, write a SQL query to find customers who are either from the city 'New York' or who do not have a grade 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 either the 'city' column has the value 'New York'
-- or the 'grade' column does not have a value greater than 100.
WHERE city = 'New York' OR NOT grade > 100;

Output of the Query:

customer_id	cust_name	city		grade   salesman_id
3002		Nick Rimando	New York	100	5001
3007		Brad Davis	New York	200	5001
3009		Geoff Cameron	Berlin		100	5003

Code Explanation:

The said SQL query that is selecting all columns (*) from the 'customer' table where the value of the "city" column is 'New York' OR NOT the value of the "grade" column is greater than 100.
The query will return all rows from the 'customer' table that meet either of these conditions. As a result, all the rows where city is 'New York' and where grade is less than or equal to 100 will be returned as well.

Relational Algebra Expression:

Relational Algebra Expression: Display all information using OR, NOT operator with specified conditions.

Relational Algebra Tree:

Relational Algebra Tree: Display all information using OR, NOT operator with specified conditions.

Explanation:

Syntax of display all customers, Display all information using OR, NOT operator with specified conditions

Visual presentation:

Result of display all the customers, who are either belongs to the city New York or not had a grade above 100

Practice Online


Query Visualization:

Duration:

Query visualization of Display all information using OR, NOT operator with specified conditions - Duration

Rows:

Query visualization of Display all information using OR, NOT operator with specified conditions - Rows

Cost:

Query visualization of Display all information using OR, NOT operator with specified conditions - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Information using OR operator with given conditions.
Next SQL Exercise: Neither New York residents nor with a grade above 100.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.