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 the salespeople who come from either '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 other than 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 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.    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 salespeople who receive commissions between 0.12 and 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 between 500 and 4000 (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 retrieve the details of the salespeople whose names begin with any letter between '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 those whose names begin with any letter between '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 retrieve the details of the customers whose names 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 names begin 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 identify 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 rows in which 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 identify 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 locate all customers with a grade value. 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 locate the employees whose last name begins with the letter '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.