SQL-retrieve data from tables

最新推荐文章于 2023-11-10 03:58:55 发布

snistty

最新推荐文章于 2023-11-10 03:58:55 发布

阅读量8k

点赞数

分类专栏： SQL-w3resource练习文章标签： SQL

本文链接：https://blog.youkuaiyun.com/Intro21/article/details/84853992

版权

SQL-w3resource练习专栏收录该内容

6 篇文章

订阅专栏

本文提供了一系列SQL查询示例，涵盖了从基础的数据检索到复杂的联接操作，包括显示销售员所有信息、数学运算、特定列筛选、聚合函数、条件过滤等。通过这些练习，帮助读者加深对SQL查询的理解和运用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Retrieve data from tables

Write a SQL statement to display all the information of all salesmen.
SELECT * FROM salesman;
Write a SQL statement to display a string “This is SQL Exercise, Practice and Solution”.
SELECT 'This is SQL Exercise, Practice and Solution;'
Write a query to display three numbers in three columns.
SELECT 2, 3, 4;
Write a query to display the sum of two numbers 10 and 15 from RDMS sever.
SELECT 10+15;
Write a query to display the result of an arithmetic expression.
SELECT 2*3;
Write a SQL statement to display specific columns like name and commission for all the salesmen.
```
SELECT name, commission
FROM salesman;
```
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;
```
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;
```
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';
```
Write a SQL statement to display all the information for those customers with a grade of 200.
```
SELECT *
FROM customer
WHERE grade=200;
```
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;
```
Write a SQL query to display the Nobel prizes for 1970.
```
SELECT *
FROM nobel_win
WHERE YEAR=1970;
```

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

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';
```
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';
```

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;

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');
```
Write a SQL query to show all the details of the winners with first name Louis.
```
SELECT *
FROM nobel_win
WHERE WINNER LIKE 'Louis%';
```

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

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');
```

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

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';
```
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;
```
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;

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

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;

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

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

Write a query in SQL to find the last name of all employees, without duplicates.
```
SELECT DISTINCT EMP_LNAME
FROM emp_details;
```
Write a query in SQL to find the data of employees whose last name is ‘Snares’.
```
SELECT *
FROM emp_details
WHERE EMP_LNAME='Snares';
```
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