Michelle️-优快云博客

原创 SQL 每日一练(Lag () over (partition by))

SQL 每日一练(SQL每日一练)@ 题目和答案来源于 wechat：SQL数据库开发题目：Carrier_NameOrderNumberReSumCostA1JOY03170.00A2JOY03170.00A1JOY04196.50A2JOY04196.50A1JOY05458.80A2JOY05458.80OUTPUTCarrier_NameOrderNumberReSumCostNewReSumCo

2021-04-02 01:22:20 4572

原创 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 184

原创 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 230

原创 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 395

原创 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 144

原创 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 107

原创 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 384

原创 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 147

原创 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 174

原创 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 154

原创 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 297

原创 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 227

原创 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 127

mimimi7777的博客