w3resource

SQL Exercises, Practice, Solution - Wildcard and Special operators

SQL [22 exercises with solution]

1. From the following table, write a SQL query to find the details of those salespeople who come from the 'Paris' City or 'Rome' City. Return salesman_id, name, city, commission.   Go to the editor

Sample table: salesman


Sample Output:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13

Click me to see the solution with pictorial presentation

2. From the following table, write a SQL query to find the details of those salespeople who come from any of the City 'Paris' or 'Rome'. Return salesman_id, name, city, commission.   Go to the editor

Sample table: salesman


Sample Output:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13

Click me to see the solution with pictorial presentation

3. From the following table, write a SQL query to find the details of those salespeople who live in cities apart from 'Paris' and 'Rome'. Return salesman_id, name, city, commission.   Go to the editor

Sample table: salesman


Sample Output:

salesman_id	name		city		commission
5001		James Hoog	New York	0.15
5005		Pit Alex	London		0.11
5003		Lauson Hen	San Jose	0.12

Click me to see the solution with pictorial presentation

4. From the following table, write a SQL query to find the details of the customers whose ID belongs to any of the values 3007, 3008 and 3009. Return customer_id, cust_name, city, grade, and salesman_id.    Go to the editor

Sample table: customer


Sample Output:

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

Click me to see the solution with pictorial presentation

5. From the following table, write a SQL query to find the details of salespeople who get the commission in the range from 0.12 to 0.14 (begin and end values are included). Return salesman_id, name, city, and commission.    Go to the editor

Sample table: salesman


Sample Output:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13
5003		Lauson Hen	San Jose0.12

Click me to see the solution with pictorial presentation

6. From the following table, write a SQL query to select orders value within a range 500, 400 (begin and end values are included). Exclude orders amount 948.50 and 1983.43. Return ord_no, purch_amt, ord_date, customer_id, and salesman_id.  Go to the editor

Sample table: orders


Sample Output:

ord_no	purch_amt	ord_date	customer_id	salesman_id
70005	2400.60		2012-07-27	3007		5001
70003	2480.40		2012-10-10	3009		5003
70013	3045.60		2012-04-25	3002		5001

Click me to see the solution with pictorial presentation

7. From the following table, write a SQL query to find the details of those salespeople whose name starts with any letter within 'A' and 'L' (not inclusive). Return salesman_id, name, city, commission.   Go to the editor

Sample table: salesman


Sample Output:

salesman_id	name		city		commission
5001		James Hoog	New York	0.15

Click me to see the solution with pictorial presentation

8. From the following table, write a SQL query to find the details of all salespeople except whose name starts with any letter within 'A' and 'L' (not inclusive). Return salesman_id, name, city, commission.    Go to the editor

Sample table: salesman


Sample Output:

salesman_id	name		city		commission
5002		Nail Knite	Paris		0.13
5005		Pit Alex	London		0.11
5006		Mc Lyon		Paris		0.14
5007		Paul Adam	Rome		0.13
5003		Lauson Hen	San Jose	0.12

Click me to see the solution with pictorial presentation

9. From the following table, write a SQL query to find the details of the customers whose name begins with the letter 'B'. Return customer_id, cust_name, city, grade, salesman_id.   Go to the editor

Sample table: customer


Sample Output:

customer_id	cust_name	city		grade	salesman_id
3007		Brad Davis	New York	200	5001
3001		Brad Guzan	London			5005

Click me to see the solution with pictorial presentation

10. From the following table, write a SQL query to find the details of the customers whose names end with the letter 'n'. Return customer_id, cust_name, city, grade, salesman_id.   Go to the editor

Sample table: customer


Sample Output:

customer_id	cust_name		city	grade	salesman_id
3008		Julian Green		London	300	5002
3004		Fabian Johnson		Paris	300	5006
3009		Geoff Cameron		Berlin	100	5003
3001		Brad Guzan		London		5005

Click me to see the solution with pictorial presentation

11. From the following table, write a SQL query to find the details of those salespeople whose name starts with ‘N’ and the fourth character is 'l'. Rests may be any character. Return salesman_id, name, city, commission.  Go to the editor

Sample table : salesman


Sample Output:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13

Click me to see the solution with pictorial presentation

12. From the following table, write a SQL query to find those rows where col1 contains the escape character underscore ( _ ). Return col1.  Go to the editor

Sample table: testtable


Sample Output:

col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300

Click me to see the solution with pictorial presentation

13. From the following table, write a SQL query to find those rows where col1 does not contain the escape character underscore ( _ ). Return col1.  Go to the editor

Sample table: testtable


Sample Output:

col1
A001/DJ-402%45\2015/200
A001/DJ-402\44

Click me to see the solution with pictorial presentation

14. From the following table, write a SQL query to find those rows where col1 contains the forward slash character ( / ). Return col1.   Go to the editor

Sample table: testtable


Sample Output:

col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
A001/DJ-402\44

Click me to see the solution with pictorial presentation

15. From the following table, write a SQL query to find those rows where col1 does not contain the forward slash character ( / ). Return col1.  Go to the editor

Sample table: testtable


Sample Output:

col1
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015

Click me to see the solution with pictorial presentation

16. From the following table, write a SQL query to find those rows where col1 contains the string ( _/ ). Return col1.   Go to the editor

Sample table: testtable


Sample Output:

col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015

Click me to see the solution with pictorial presentation

17. From the following table, write a SQL query to find those rows where col1 does not contain the string ( _/ ). Return col1.   Go to the editor

Sample table: testtable


Sample Output:

col1
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
A001/DJ-402\44

Click me to see the solution with pictorial presentation

18. From the following table, write a SQL query to find those rows where col1 contains the character percent ( % ). Return col1.   Go to the editor

Sample table: testtable


Sample Output:

col1
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300

Click me to see the solution with pictorial presentation

19. From the following table, write a SQL query to find those rows where col1 does not contain the character percent ( % ). Return col1.  Go to the editor

Sample table: testtable


Sample Output:

col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402\44

Click me to see the solution with pictorial presentation

20. From the following table, write a SQL query to find all those customers who does not have any grade. Return customer_id, cust_name, city, grade, salesman_id.   Go to the editor

Sample table: customer


Sample Output:

customer_id	cust_name	city	grade	salesman_id
3001		Brad Guzan	London		5005

Click me to see the solution with pictorial presentation

21. From the following table, write a SQL query to find all those customers whose grade value exists. Return customer_id, cust_name, city, grade, salesman_id.   Go to the editor

Sample table: customer


Sample Output:

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

Click me to see the solution with pictorial presentation

22. From the following table, write a SQL query to find the employees whose last name begins with the character 'D'. Return emp_idno, emp_fname, emp_lname and emp_dept. Go to the editor

Sample table: emp_details


Sample Output:

emp_idno	emp_fname	emp_lname	emp_dept
843795		Enric		Dosio		57
444527		Joseph		Dosni		47

Click me to see the solution with result

Practice Online


More to Come !

Query visualizations are generated using Postgres Explain Visualizer (pev).

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.