SQL Practices - SELECT

Practices set resources are referenced from w3resource.com

• SQL Exercises, Practice, Solution - Retrieve data from tables
• SQL Exercises, Practice, Solution - Using Boolean and Relational operators
• All practice set and answers are written in PostgreSQL

Sample Tables

salesman

orders

customer

nobel_win

item_mast

emp_details

Practice Questions

1. Simple Select

1.1. Write a SQL statement to display all the information of all salesmen.

SELECT * FROM salesman;

1.2. Write a SQL statement to display specific columns like name and commission for all the salesmen.

SELECT name, commission FROM salesman;

1.3. Write a query to display the columns in a specific order like order date, salesman id, order number and purchase amount from for all the orders.

SELECT ord_date, salesman_id, ord_no, purch_amt FROM orders;

1.4. Write a query which will retrieve the value of salesman id of all salesmen, getting orders from the customers in orders table without any repeats.

SELECT DISTINCT salesman_id FROM orders;

1.5. Write a SQL statement to display names and city of salesman, who belongs to the city of Paris.

SELECT name, city FROM salesman 
WHERE city = 'Paris';

1.6. Write a SQL statement to display all the information for those customers with a grade of 200.

SELECT * FROM Customer 
WHERE grade=200;

1.7. Write a SQL query to display the order number followed by order date and the purchase amount for each order which will be delivered by the salesman who is holding the ID 5001.

SELECT ord_date, ord_no, purch_amt FROM orders
WHERE salesman_id=5001;

1.8. Write a SQL query to display the Nobel prizes for 1970.

SELECT * FROM nobel_win WHERE YEAR=1970;

-- different interpretation of instruction
SELECT year,subject,winner FROM nobel_win 
WHERE year=1970; 

1.9. Write a SQL query to know the winner of the 1971 prize for Literature.

SELECT winner, country FROM nobel_win
WHERE year=1971 AND subject='Literature';

1.10. Write a SQL query to display the year and subject that won ‘Dennis Gabor’ his prize.

SELECT year, subject FROM nobel_win 
WHERE winner='Dennis Gabor';

1.11. Write a SQL query to give the name of the ‘Physics’ winners since the year 1950.

SELECT winner FROM nobel_win 
WHERE subject='Physics' AND year>=1950;

1.12. Write a SQL query to Show all the details (year, subject, winner, country ) of the Chemistry prize winners between the year 1965 to 1975 inclusive.

SELECT year, subject, winner, counter FROM nobel_win
WHERE subect='Chemistry' AND year>=1965 AND year<=1975;

1.13. Write a SQL query to show all details of the Prime Ministerial winners after 1972 of Menachem Begin and Yitzhak Rabin.

SELECT * FROM nobel_win
WHERE category='Prime Minister' AND year > 1972;

-- different interpretation of instruction
SELECT * FROM nobel_win
WHERE year >1972
AND winner IN ('Menachem Begin', 'Yitzhak Rabin');

1.14. Write a SQL query to show all the details of the winners with first name Louis.

SELECT * FROM nobel_win
WHERE winner LIKE 'Louse %';

1.15. Write a SQL query to show all the winners in Physics for 1970 together with the winner of Economics for 1971.

SELECT * FROM nobel_win
WHERE subject='Physics' AND year=1970
UNION 
SELECT * FROM nobel_win
WHERE subject='Economics' AND year=1971;

1.16. Write a SQL query to show all the winners of nobel prize in the year 1970 except the subject Physiology and Economics.

SELECT * FROM nobel_win
WHERE year=1970 AND subject NOT IN ('Physiology', 'Economics');

1.17. Write a SQL query to show the winners of a ‘Physiology’ prize in an early year before 1971 together with winners of a ‘Peace’ prize in a later year on and after the 1974.

SELECT * FROM nobel_win
WHERE subject='Pysiology' AND year<1971
UNION
SELECT * FROM nobel_win
WHERE subject='Peace' AND year>1974;

1.18. Write a SQL query to find all details of the prize won by Johannes Georg Bednorz.

SELECT * FROM nobel_win
WHERE winner='Johannes Georg Bednorz;

1.19. Write a SQL query to find all the details of the nobel winners for the subject not started with the letter ‘P’ and arranged the list as the most recent comes first, then by name in order.

SELECT * FROM nobel_win
WHERE subject NOT LIKE 'P%'
ORDER BY year DESC, winner;

1.20. Write a SQL query to find all the details of 1970 winners by the ordered to subject and winner name; but the list contain the subject Economics and Chemistry at last.

SELECT * FROM nobel_win
WHERE year=1970
ORDER BY 
	CASE WHEN subject IN ('Economics', 'Chemistry') THEN 1 ELSE 0 END ASC,
	subject,
	winnerl;

1.21. Write a SQL query to find all the products with a price between Rs.200 and Rs.600.

SELECT * FROM item_mast
WHERE pro_price>=200 AND pro_price<=600;

-- better approach
SELECT * FROM item_mast
WHERE pro_price BETWEEN 200 AND  600;

1.22. Write a SQL query to calculate the average price of all products of the manufacturer which code is 16.

SELECT AVG(pro_price) FROM item_mast
WHERE pro_com=16;

1.23. Write a SQL query to find the item name and price in Rs.

SELECT 
	pro_name AS 'item name', 
	pro_price AS 'price in Rs'
	FROM item_mast;

1.24. Write a SQL query to display the name and price of all the items with a price is equal or more than Rs.250, and the list contain the larger price first and then by name in ascending order.

SELECT pro_name, pro_price FOM item_mast
WHERE pro_price>=250
ORDER BY pro_price desc, pro_name;

1.25. Write a SQL query to display the average price of the items for each company, showing only the company code.

SELECT AVG(pro_price), pro_com FROM item_mast
GROUP BY pro_com;

1.26. Write a SQL query to find the name and price of the cheapest item(s).

SELECT pro_name, pro_price FROM item_mast
WHERE pro_price = (SELECT MIN(pro_price) from item_mast);

1.27. Write a query in SQL to find the last name of all employees, without duplicates.

SELECT DISTINCT emp_lname FROM emp_details;

1.28. Write a query in SQL to find the data of employees whose last name is ‘Snares’.

SELECT * FROM emp_details WHERE emp_lname='Snares';

1.29. Write a query in SQL to display all the data of employees that work in the department 57.

SELECT * FROM emp_details WHERE emp_dept=57;

2. Boolean & Relational Operators

2.1. Write a query to display all customers with a grade above 100.

SELECT * FROM customer
WHERE grade > 100;

2.2. Write a query statement to display all customers in New York who have a grade value above 100.

SELECT * FROM customer
WHERE city = 'New York' AND grade > 100;

2.3. Write a SQL statement to display all customers, who are either belongs to the city New York or had a grade above 100.

SELECT * FROM customers
WHERE city = 'New York' OR grade > 100;

2.4. Write a SQL statement to display all the customers, who are either belongs to the city New York or not had a grade above 100.

SELECT * FROM customer
WHERE city = 'New York' OR grade <= 100;

-- other approach
SELECT * FROM customer
WHERE city = 'New York' OR NOT grade > 100;

2.5. Write a SQL query to display those customers who are neither belongs to the city New York nor grade value is more than 100.

SELECT * FROM customer
WHERE city NOT 'New York' AND NOT grade > 100;

-- other approach
SELECT * FROM customer
WHERE NOT (city = 'New York' AND grade > 100);

2.6. Write a SQL statement to display either those orders which are not issued on date 2012-09-10 and issued by the salesman whose ID is 5005 and below or those orders which purchase amount is 1000.00 and below.

SELECT * FROM orders
WHERE NOT (
	(ord_date = '2012-09-10' AND salesman_id > 5005) OR 
	purch_amt > 1000.00
);

2.7. Write a SQL statement to display salesman_id, name, city and commission who gets the commission within the range more than 0.10% and less than 0.12%.

SELECT salesman_id, name, city, commission FROM salesman
WHERE commission > 0.10 AND commission < 0.12;

2.8. Write a SQL query to display all orders where purchase amount less than 200 or exclude those orders which order date is on or greater than 10th Feb,2012 and customer id is below 3009.

SELECT * FROM orders
WHERE (
	purch_amt < 200 OR 
	NOT(ord_date >= '2012-02-10' AND customer_id < 3009)
);

2.9. Write a SQL statement to exclude the rows which satisfy 1) order dates are 2012-08-17 and purchase amount is below 1000 2) customer id is greater than 3005 and purchase amount is below 1000.

SELECT * FROM orders
WHERE NOT (
	(ord_date = '2012-08-17' OR customer_id > 3005 ) AND
	purch_amt < 1000
);

2.10. Write a SQL query to display order number, purchase amount, achieved, the unachieved percentage for those order which exceeds the 50% of the target value of 6000.

SELECT 
	ord_no, 
	purch_amt, 
	(purch_amt / 6000 * 100) AS "Achieved%",
	((6000-purch_amt) / 6000 * 100) AS "Unachieved%"
FROM orders
WHERE (purch_amt / 6000 * 100) > 50;

2.11. Write a query in SQL to find the data of employees whose last name is Dosni or Mardy.

SELECT * FROM emp_details
WHERE emp_lname = 'Dosni' OR emp_lname = 'Mardy';

2.12. Write a query in SQL to display all the data of employees that work in department 47 or department 63.

SELECT * FROM emp_details
WHERE emp_dept = 47 OR emp_dept = 63;