SQL-retrieve data from tables

1. Write a SQL statement to display all the information of all salesmen.
   SELECT * FROM salesman;
2. Write a SQL statement to display a string “This is SQL Exercise, Practice and Solution”.
   SELECT 'This is SQL Exercise, Practice and Solution;'
3. Write a query to display three numbers in three columns.
   SELECT 2, 3, 4;
4. Write a query to display the sum of two numbers 10 and 15 from RDMS sever.
   SELECT 10+15;
5. Write a query to display the result of an arithmetic expression.
   SELECT 2*3;
6. Write a SQL statement to display specific columns like name and commission for all the salesmen.

   SELECT name, commission
   FROM salesman;
   

7. 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;
   

8. 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;
   

9. 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';
   

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

    SELECT *
    FROM customer
    WHERE grade=200;
    

11. 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;
    

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

    SELECT *
    FROM nobel_win
    WHERE YEAR=1970;
    

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

    SELECT WINNER
    FROM nobel_win
    WHERE YEAR=1971
    AND SUBJECT='Literature';
    

14. 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';
    

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

    SELECT WINNER
    FROM nobel_win
    WHERE YEAR>=1950
    AND SUBJECT='Physics';
    

16. 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 *
    FROM nobel_win
    WHERE SUBJECT='Chemistry'
    AND YEAR BETWEEN 1965 AND 1975;
    --------
    SELECT *
    FROM nobel_win
    WHERE subject = 'Chemistry'
    AND year>=1965 AND year<=1975;
    

17. 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 YEAR>1972
    AND WINNER in ('Menachem Begin', 'Yitzhak Rabin');
    

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

    SELECT *
    FROM nobel_win
    WHERE WINNER LIKE 'Louis%';
    

19. 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)
    OR (SUBJECT='Economics' AND year=1971);
    ------
    SELECT * FROM nobel_win  
    WHERE (subject ='Physics' AND year=1970) 
    UNION 
    SELECT * FROM nobel_win  
    WHERE (subject ='Economics' AND year=1971);
    

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

    SELECT WINNER
    FROM nobel_win
    WHERE YEAR=1970
    AND SUBJECT NOT IN ('Physiology', 'Economics');
    

21. 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='Physiology' AND YEAR<1971)
    OR (SUBJECT='Peace' AND YEAR>=1974);
    ------
    SELECT *
    FROM nobel_win 
    WHERE (subject ='Physiology' AND year<1971)
    UNION
    SELECT *
    FROM nobel_win 
    WHERE (subject ='Peace' AND year>=1974);
    

22. 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';
    

23. 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;
    

24. 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.

• 思路：SUBJECT in (‘Economics’, ‘Chemistry’)返回一系列0/1值：如果subject是Economics或Chemistry，则返回1，其余则返回0。使用这一列进行升序排序，即可将 Economics和Chemistry种类的行放在表尾(0<1).

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

25. 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 BETWEEN 200 AND 600;
    

26. 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;
    

27. 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;
    

28. 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
    FROM item_mast
    WHERE PRO_PRICE>=250
    ORDER BY PRO_PRICE DESC, PRO_NAME;
    

29. 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_NAME;
    

30. 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);
    

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

    SELECT DISTINCT EMP_LNAME
    FROM emp_details;
    

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

    SELECT *
    FROM emp_details
    WHERE EMP_LNAME='Snares';
    

33. 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;
    

来源：w3resource