w3resource

SQL Exercises: Sort records using in operator

SQL Wildcard & Special Operator: Exercise-4 with Solution

From the following table, write a SQL query to retrieve the details of all customers whose ID belongs to any of the values 3007, 3008 or 3009. 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 the 'customer_id' column has one of the values 3007, 3008, or 3009.
WHERE customer_id IN (3007, 3008, 3009);

Output of the Query:

customer_id	cust_name	 city		grade	salesman_id
3007		Brad Davis	 New York	200		5001
3008		Julian Green   London		300		5002
3009		Geoff Cameron	Berlin		100		5003

Code Explanation:

The said query in SQL that selects all columns (*) from a table called 'customer' where the "customer_id" is in the list of values (3007, 3008, 3009).
It will return a list of all the rows in the 'customer' table in which the 'customer_id' matches any of the specified values in the query.

Relational Algebra Expression:

Relational Algebra Expression: Sort records using in operator.

Relational Algebra Tree:

Relational Algebra Tree: Sort records using in operator.

Explanation:

Syntax of sort records using in operator

Visual presentation:

Result of sort records using in operator

Query Visualization:

Duration:

Query visualization of Sort records using in operator - Duration

Rows:

Query visualization of Sort records using in operator - Rows

Cost:

Query visualization of Sort records using in operator - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filter records using not in operator.
Next SQL Exercise: Using between operator to get a value from a range.

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.