Michelle️-CSDN博客

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

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

2021-04-02 01:22:20 3777

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

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

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

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

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

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

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

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

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

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

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

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

mimimi7777的博客