23个简单的sql基础语句练习+关于sql语句一些知识

这个为SQL语句练习的数据库的ERD(entity relationship diagram)图

1:
Write a query that displays the order ID, account ID, and total dollar amount for all the orders, sorted first by the account ID (in ascending order), and then by the total dollar amount (in descending order).

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY account_id, total_amt_usd DESC;

2:
Now write a query that again displays order ID, account ID, and total dollar amount for each order, but this time sorted first by total dollar amount (in descending order), and then by account ID (in ascending order).

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC, account_id;

ps:
在查询#1中，每个帐户ID的所有订单都分组在一起，然后在每个分组中，订单从最大到最小。
在查询#2中，由于您首先按总金额排序，因此订单从最大到最小依次显示，而与来自哪个帐户ID无关。
然后，它们接下来按帐户ID排序。
(在这里很难看到按帐户ID进行的二次排序，因为只有当两个订单的总金额相等时，才需要按帐户ID进行排序。)

ps:
ORDER BY 解释：

ORDER BY one,two;

这条语句是先对one属性进行第一排序，当One中有重复的内容，再通过two进行排序。

3:
Pulls the first 5 rows and all columns from the orders table that have a dollar amount of gloss_amt_usd greater than or equal to 1000.

SELECT *
FROM orders
WHERE gloss_amt_usd >=1000
limit 5;

4:
Pulls the first 10 rows and all columns from the orders table that have a total_amt_usd less than 500.

SELECT *
FROM orders
WHERE total_amt_usd <= 500
limit 10;

ps:WHERE语句中常用的符号包括:

(greater than)
< (less than)
= (greater than or equal to)
<= (less than or equal to)
= (equal to)
!= (not equal to)
WHERE字句位于FROM字句后面，但在ORDER BY 或LIMIT字句前面

WHERE语句也可以用于非数值数据。
我们可以使用=和!=运算符。
您需要确保在文本数据中使用单引号(只是在原始文本中使用引号时要小心)，而不是双引号。

5:
Filter the accounts table to include the company name, website, and the primary point of contact (primary_poc) just for the Exxon Mobil company in the accounts table.

SELECT name, website, primary_poc
FROM accounts
WHERE name = 'Exxon Mobil';

6:
Create a column that divides the standard_amt_usd by the standard_qty to find the unit price for standard paper for each order. Limit the results to the first 10 orders, and include the id and account_id fields.

SELECT id, account_id, standard_amt_usd/standard_qty AS unit_price
FROM orders
LIMIT 10;

7:
Write a query that finds the percentage of revenue that comes from poster paper for each order. You will need to use only the columns that end with _usd. (Try to do this without using the total column.) Display the id and account_id fields also.

SELECT id, account_id, 
   poster_amt_usd/(standard_amt_usd + gloss_amt_usd + poster_amt_usd) AS post_per
FROM orders
LIMIT 10;

ps:
The LIKE operator is extremely useful for working with text. You will use LIKE within a WHERE clause. The LIKE operator is frequently used with %. The % tells us that we might want any number of characters leading up to a particular set of characters or following a certain set of characters, as we saw with the google syntax above. Remember you will need to use single quotes for the text you pass to the LIKE operator, because of this lower and uppercase letters are not the same within the string. Searching for ‘T’ is not the same as searching for ‘t’. In other SQL environments , you can use either single or double quotes.

8:
Use the accounts table to find all the companies whose names start with ‘C’.

SELECT name
FROM accounts
WHERE name LIKE 'C%';

9:
Use the accounts table to find all companies whose names contain the string ‘one’ somewhere in the name.

SELECT name
FROM accounts
WHERE name LIKE '%one%';

10:
Use the accounts table to find all companies whose names end with ‘s’.

SELECT name
FROM accounts
WHERE name LIKE '%s';

11：
Use the accounts table to find the account name, primary_poc, and sales_rep_id for Walmart, Target, and Nordstrom.

SELECT name,primary_poc,sales_rep_id 
FROM accounts
WHERE name IN ('Walmart','Target','Nordstrom');

12：
Use the web_events table to find all information regarding individuals who were contacted via the channel of organic or adwords.

SELECT *
FROM web_events
WHERE channel IN ('organic', 'adwords');

13:
Use the accounts table to find the account name, primary poc, and sales rep id for all stores except Walmart, Target, and Nordstrom.

SELECT  name,primary_poc,sales_rep_id
FROM accounts
WHERE name NOT IN ('Walmart', 'Target','Nordstrom');

ps:The NOT operator is an extremely useful operator for working with the previous two operators we introduced: IN and LIKE. By specifying NOT LIKE or NOT IN, we can grab all of the rows that do not meet a particular criteria.

14:
Use the web_events table to find all information regarding individuals who were contacted via any method except using organic or adwords methods.

SELECT *
FROM web_events
WHERE channel NOT IN ('organic', 'adwords');

15:
Use the accounts table to find all the companies whose names do not start with ‘C’.

SELECT *
FROM accounts
WHERE name NOT LIKE 'c%';

16:

Write a query that returns all the orders where the standard_qty is over 1000, the poster_qty is 0, and the gloss_qty is 0.

SELECT *
FROM orders
WHERE standard_qty >=1000 AND poster_qty=0 AND gloss_qty=0;

ps:
BETWEEN Operator
Sometimes we can make a cleaner statement using BETWEEN than we can using AND. Particularly this is true when we are using the same column for different parts of our AND statement.

for example:

WHERE column >= 6 AND column <= 10

equal to

WHERE column BETWEEN 6 AND 10

ps:

WHERE column >= 6 AND <= 10 这是错误语句

17：
Using the accounts table, find all the companies whose names do not start with ‘C’ and end with ‘s’.

SELECT name
FROM accounts
WHERE name NOT LIKE 'C%' AND name LIKE '%s';

ps:不要忘记写第二个name，不然会报错哦

18：
When you use the BETWEEN operator in SQL, the results include the values of your endpoints。 write a query that displays the order date and gloss_qty data for all orders where gloss_qty is between 24 and 29.

SELECT occurred_at, gloss_qty 
FROM orders
WHERE gloss_qty BETWEEN 24 AND 29;

19：
Use the web_events table to find all information regarding individuals who were contacted via the organic or adwords channels, and started their account at any point in 2016, sorted from newest to oldest.

SELECT *
FROM web_events
WHERE channel IN ('organic', 'adwords') AND occurred_at BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY occurred_at DESC;

20：
Write a query that returns all the orders where the standard_qty is over 1000, the poster_qty is 0, and the gloss_qty is 0.

SELECT *
FROM orders
WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;

21：
Find list of orders ids where either gloss_qty or poster_qty is greater than 4000. Only include the id field in the resulting table.

SELECT id
FROM orders
WHERE gloss_qty > 4000 OR poster_qty > 4000; ；

22：
Write a query that returns a list of orders where the standard_qty is zero and either the gloss_qty or poster_qty is over 1000.

SELECT *
FROM orders
WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);

23：
Find all the company names that start with a ‘C’ or ‘W’, and the primary contact contains ‘ana’ or ‘Ana’, but it doesn’t contain ‘eana’.

SELECT *
FROM accounts
WHERE (name LIKE 'C%' OR name LIKE 'W%') 
           AND ((primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%') 
           AND primary_poc NOT LIKE '%eana%');

Commands
You have already learned a lot about writing code in SQL! Let’s take a moment to recap all that we have covered before moving on:

Other Tips
Though SQL is not case sensitive (it doesn’t care if you write your statements as all uppercase or lowercase), we discussed some best practices. The order of the key words does matter! Using what you know so far, you will want to write your statements as:

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;

Notice, you can retrieve different columns than those being used in the ORDER BY and WHERE statements. Assuming all of these column names existed in this way (col1, col2, col3, col4, col5) within a table called table1, this query would run just fine.

Some of the most popular databases include:
MySQL
Access
Oracle
Microsoft SQL Server
Postgres
You can also write SQL within other programming frameworks like Python, Scala, and HaDoop.

Each of these SQL databases may have subtle differences in syntax and available functions – for example, MySQL doesn’t have some of the functions for modifying dates as Postgres. Most of what you see with Postgres will be directly applicable to using SQL in other frameworks and database environments. For the differences that do exist, you should check the documentation. Most SQL environments have great documentation online that you can easily access with a quick Google search.