SQL-Boolean and Relational Operators

最新推荐文章于 2024-06-13 16:35:31 发布

snistty

最新推荐文章于 2024-06-13 16:35:31 发布

阅读量272

点赞数

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

本文链接：https://blog.csdn.net/Intro21/article/details/84858819

版权

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

6 篇文章 0 订阅

订阅专栏

Write a query to display all customers with a grade above 100.
SELECT * FROM customer WHERE grade>100;
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;
```
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 customer
WHERE city='New York'
OR grade>100;
```

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;
------
SELECT *
FROM customer
WHERE city='New York'
OR NOT grade>100

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 NOT (city='New York' OR grade>100)
```
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 505 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>505)
OR purch_amt<=1000.00);
```
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 *
FROM salesman
WHERE commission BETWEEN 0.10 AND 0.12;
```
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_no>='2012-02-10' AND customer_id<3009);
```
Write a SQL statement where i) order dates are anything but 2012-08-17, or customer id is not greater than 3005 ii) and purchase amount is not below 1000.
~~？题目理解存在问题~~
SELECT *
FROM orders
WHERE (ord_date!=‘2012-08-17’ OR customer_id<=3005)
AND purch_amt>=1000;
```
------ 标准答案
SELECT * 
FROM  orders 
WHERE NOT((ord_date ='2012-08-17' 
OR customer_id>3005) 
AND purch_amt<1000);
```
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.

在使用as设立别名时，需要使用双引号

SELECT ord_no, purch_amt,
purch_amt/6000*100 as "achieved %",
(6000-purch_amt)/6000*100 as "unachieved %"
FROM orders
WHERE purch_amt>0.5*6000

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 IN ('Dosni', 'Mardy');
```
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 IN (47, 63);
```

来源：w3resource