- 博客(13)
- 收藏
- 关注
RSS订阅原创 SQL 每日一练(Lag () over (partition by))
SQL 每日一练(SQL每日一练)@ 题目和答案来源于 wechat：SQL数据库开发题目：Carrier_NameOrderNumberReSumCostA1JOY03170.00A2JOY03170.00A1JOY04196.50A2JOY04196.50A1JOY05458.80A2JOY05458.80OUTPUTCarrier_NameOrderNumberReSumCostNewReSumCo
2021-04-02 01:22:20
4387
原创 SQL Advanced Joins
导读：我们用FULL OUTER JOIN, LEFT JOIN, RIGHT JOIN可以看到每一个account有个sales rep 和每个sales rep有一个account我们也可以用他们看到每一个account没有一个sales_rep和每一个sales rep没有一个accountENGLISH:each account who has a sales rep and each sales rep that has an account (all of the columns in
2021-03-19 12:24:01
154
原创 SQL WINDOW FUNCTION II
Aliases for Multiple Window Functions(PARTITION BY account_id ORDER BY DATE_TRUNC(‘month’,occurred_at)) is replaced by main_window in the repeated window function query lines.SELECT id, account_id, DATE_TRUNC('year',occurred_at) AS year,
2021-03-19 10:50:50
181
原创 SQL Window Function
Window Function 1PARTITION BY ClauseA PARTITION BY clause is used to partition rows of table into groups.Syntax:Window_function ( expression ) Over ( partition by expr [order_clause] [frame_clause] ) order_clause and frame_clause are optional
2021-03-19 06:03:34
358
原创 2021-03-17
Definition and UsageThe COALESCE() function returns the first non-null value in a list.SyntaxCOALESCE(val1, val2, …, val_n)Run the query entered below in the SQL workspace to notice the row with missing data.SELECT *FROM accounts aLEFT JOIN orders
2021-03-18 09:56:01
98
原创 SQL cast
Definition and UsageThe CAST() function converts a value (of any type) into a specified datatype.SyntaxCAST(expression AS datatype(length))QuestionsWrite a query to look at the top 10 rows to understand the columns and the raw data in the dataset sf_
2021-03-18 09:40:22
84
原创 SQL concat
Definition and UsageThe CONCAT() function adds two or more expressions together.SyntaxCONCAT(expression1, expression2, expression3,…)QuestionEach company in the accounts table wants to create an email address for each primary_poc. The email address s
2021-03-18 09:31:03
297
原创 SQL 分名字
The POSITION() function returns the position of the first occurrence of a substring in a string.If the substring is not found within the original string, this function returns 0.This function performs a case-insensitive search.POSITION(substring IN stri
2021-03-18 02:05:12
118
原创 SQL LEFT and RIGHT
DefinitionThe LEFT() function extracts a number of characters from a string (starting from left).The RIGHT() function extracts a number of characters from a string (starting from right).In the accounts table, there is a column holding the website
2021-03-17 12:26:43
127
原创 SQL 习题总结 - with
How to useThe SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database.When creating multiple tables using WITH, you add a comma after every table except the last table leading to your final query.WITH table1 AS ( SEL
2021-03-17 10:24:37
107
原创 SQL 习题总结 - subqueries
Subquery 1What was the month/year combo for the first order placed?SELECT DATE_TRUNC('month', MIN(occurred_at)) FROM orders;The average amount of standard paper sold on the first month that any order was placed in the orders table (in terms of quan
2021-03-16 07:30:30
264
原创 SQL 习题总结 - case statement
Case StatementWrite a query to display for each order, the account ID, total amount of the order, and the level of the order - ‘Large’ or ’Small’ - depending on if the order is $3000 or more, or smaller than $3000.select account_id, total_amt_usd,case
2021-03-15 12:44:07
199
原创 2021-03-12
SQL 每日一练TOC@ 题目来源于：wechat：SQL数据库开发题目：姓名请假时间请假时间到张三2020-05-202020-05-25张三2020-05-302020-06-01李四2020-05-242020-05-25李四2020-05-242020-06-05王五2020-04-252020-06-05OUTPUT姓名月份总天数李四59李四65王五46王五531
2021-03-13 05:51:11
94
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人