Calculate the quantity of the product:
choose numbering(* ) fromproduct;
Count the number of products with a non-zero value:
choose numbering(price ) fromproduct;
Count the number of unique ones
choose numbering( clearlycategory ) fromproduct;
Get minimum and maximum product prices:
choose Min. (price ), upper limit (price ) fromproduct;
Find the total product price for each category:
choosecategory, and(price ) fromproduct group fromcategory;
Find the average price of each product category whose average price is above 3.0:
choosecategory, average value (price ) fromproduct group fromcategory I have average value (price )> 3,0
Get product names sorted by order
priceColumns sorted in ascending order by default:
chooseName fromproduct Order byprice [ ASC ];
Get product names sorted by order
priceColumns in descending order:
chooseName fromproduct Order byprice DESC;
/Do basic math. To get the seconds of the week:
choose60*60*24*7; -- Result: 604800
Round the numbers to the nearest whole number:
chooseround (1234.56789); -- Result: 1235
Round numbers to two decimal places:
chooseround ( avg ( value ), 2 ) fromproduct Whereclass_id = 21; -- Result: 124.56
In PostgreSQL and SQL Server,
/The operator performs integer division on integer arguments. If you don't see the expected number of decimal places, it's because you're dividing between two integers. Convert one to decimal:
-- Result: 61 throw (123 AS Decimal )/ 2 -- Result: 61.5
divide by 0
To avoid this error, make sure the denominator is not
0. you can use it
NULLIF()function to replace
invalid, which leads to
invalidFor the whole expression:
NULLIF(all counts, 0 )
JOIN is used to get data from multiple tables. To get the names of the products purchased in each order, use:
chooseOrder.OrderDate, Product Name asQuantity fromSeries take partproduct existsproduct.id = order.product_id;
Learn more about JOIN in our interactionSQL connectioncourse.
To insert data into the table, use
entercategory values(1, "Home and Kitchen"), (2, "Clothes and Clothing");
You can specify the columns to add data to. The remaining columns are filled with predefined default values or
entercategory name) values(“electronic product”);
To update data in a table, use
renewcategory I putis_active = true, name = 'office' Wherename = 'office';
To delete data from a table, use
deleted bycategory WhereName Vacuum;
Check out our interactive lessonsHow to insert, update and delete data in SQL.
date and time
There are 3 main types related to time:
. Times are represented using a 24-hour clock and can be as vague as hours and minutes (for example,
15:30– 3:30 PM) or to the nearest microsecond and time zone (see below):
14:39:53.662522-05It's almost 2:40pm. CDT (eg in Chicago, 7:40 PM UTC). The letters in the example above represent:
In the date part:
YYYY– 4 digit year.
mm– Months with zero investment (
DD– Day with zero investment.
part of time:
fearful– Zero hours on a 24-hour clock.
SS- The number of seconds.can be omitted.
ssssssss– Small fraction of a second – can be represented using 1 to 6 digits.can be omitted.
±TZ- Time zone. It must start with any of the following
-and use two digits relative to UTC.can be omitted.
current date and time
Find out what time it is:
choose current time;
Get today's date:
choose Today's Date;
In SQL Server:
Get the timestamp of the current date and time:
Creation date and time value
To create a date, time, or timestamp, write the value to a string and convert it to the correct type.
choose throw ('31-12-2021' asdate ); choose throw ('15:31' asyear ); choose throw ('2021-12-31 23:59:29+02' astime stamp ); choose throw ('15:31.124769' asyear );
Note the last example - it interprets as 15 minutes 31 seconds 124769 microseconds! It's always a good idea to explicitly write 00 for the time:
Order bySort rows chronologically from oldest to newest in datetime columns:
chooseOrder Date, Product, Quantity fromsales volume Order bydate of order;
|date of order||product||quantity|
Sort from newest to oldest using descending order:
chooseOrder Date, Product, Quantity fromsales volume Order bydate of order DESC;
Compare date and time values
You can use comparison operators
=Compare date and time values. Earlier dates are less than later dates. For example,
Find sales for July 2023:
chooseOrder date, product name, quantity fromsales volume WhereOrder date >= '07-01-2023' andorder_date < '08-01-2023';
Find customers signed up for July 2023:
chooseRegistration timestamp, email fromcustomer WhereRecord Timestamp >= '07-01-2023' andRecord Timestamp < '2023-08-01';
notes:Note the due date in the query. upper limit
'01-08-2023'Not included. time stamp
'01-08-2023'it is actually a timestamp
'01-08-2023 00:00:00.0'. comparison operator
<Used to ensure that the selection is made for all timestamps less than
'01-08-2023 00:00:00.0', meaning all timestamps in July 2023, even those near midnight on August 1, 2023.
Intervals measure the difference between two points in time. For example, the space between
06-07-2023It's 2 days.
To set a range in SQL, use the following syntax:
The pension consists of three elements:
spacekeywords, quoted values, and time segment keywords. You can use the following time parts:
minute, thin, and
Add spaces to date and time values
you can use it
-The interval to add or subtract from a date or timestamp value.
minus one year
choose throw ('05-07-2023' astime stamp )- space"1" Year; -- Result: 2022-07-05 00:00:00
Find customers who placed their first order within a month of signing up:
chooseID card fromcustomer WhereDate of first order > Date of registration + space"1" moon;
Filter events within the last 7 days
To find scheduled deliveries in the last 7 days, use:
choosedelivery date, address fromsales volume Wheredelivery date <= Today's Date anddelivery date >= Today's Date- space"7" heaven;
notes:In SQL Server, intervals are not implemented - use
Filter events within the last 7 days in SQL Server
To find discounts for the last 7 days, use:
choosedelivery date, address fromsales volume Wheredelivery date <= getDate() anddelivery date >= date added (day,-7, getDate() );
extract partial date
The standard SQL syntax for getting a part of a date is
choose refining (Year fromdate of order ) fromSales volume;
You can export the following fields:
minute, thin, and
Standard syntax does not work in SQL Server. use
DATEPART (part, date)operation instead.
choose date part (year, date of order ) fromSales volume;
group by year and month
Find the number of sales per month:
choose refining (Year fromdate of order ) asYear, refining (moon fromdate of order ) asmoon, numbering(* ) asnumbering fromsales volume group fromyears Order byyears;
Note that you must also group by year and month.
Delivery (months from order date)Output only the month digits (1, 2, ..., 12). To distinguish months from different years, you must also group by year.
For more information about using date and time values in our interactiveStandard SQL functionscourse.
case whenallows you to pass conditions like
Whereclause), evaluate them in order and return the value that satisfies the first condition.
chooseName, case neverPrice > 150 afterward'High quality' nevervalue > 100 afterward"middle class" but'role model' end of ASprice_category fromproduct;
Here, all products with prices above 150 getHigh qualitytag, users with a value above 100 (and below 150) will receive.middle classlabel, the rest receiverole modelInscription.
Cases and group v
you can combine
group fromCalculates object statistics in categories you specify.
choose case neverPrice > 150 afterward'High quality' nevervalue > 100 afterward"middle class" but'role model' end of ASprice_category, numbering(* ) asproduct fromproduct group fromprice_category;
Calculate the bulk order quantity per customer using
chooseCustomer's code, and( case whenQuantity > 10 afterward1 but0 end ) asLarge Orders fromsales volume group fromCustomer's code;
chooseCustomer's code, numbering( case whenQuantity > 10 afterwardorder number end ) asLarge Orders fromsales volume group fromCustomer's code;
Learn more in our interactionCreate basic SQL reportscourse.
group by extension
grouping setAllows you to specify multiple sets of columns to group by in a query.
choosearea, product, numbering(order number ) fromsales volume group from grouping set( (region, product), ()）
cubeCreate groupings for all possible subsets
choosearea, product, numbering(order number ) fromsales volume group per cube(region, product);
wrappedAdded new grouping levels for subsets and aggregates.
choosearea, product, numbering(order number ) fromsales volume group with summary(region, product);
mergereplace the first one
invalidArgument with given value. Usually used to display labels
choosearea, merge(product, "all" ), numbering(order number ) fromsales volume group with summary(region, product);
Check out our practical lessonsGROUP BY extension.
common array expression
A common array expression (CTE) is a named temporary result set that can be referenced in a larger query. They are especially useful for complex assemblies and for breaking large queries into more manageable parts.
andTotal product sales as( chooseproduct, and(profit ) asTotal profit fromsales volume group fromproduct) choose average value (Total profit ) fromtotal product sales;
Check out our practical lessonscommon array expression.
Window functions calculate their results based on a sliding window frame (a set of rows relative to the current row). Unlike aggregate functions, window functions do not collapse rows.
Calculate the percentage of the total within the group
chooseProduct, Brand, Profit, (100.0 * Profit / and(profit ) Exceed( Partition basebrand )) asperchlorethylene fromSales volume;
To sort products by price:
choose rank() over ( Order byprice ), Name fromproduct;
class– Give equal rank to associated values, leaving spaces.
DENSE_RANK– Give the same rank to bound values without spaces.
ROW_NUMBER– Give consecutive numbers without spaces.
|Name||class||Intensive level||Row number|
A running total is the cumulative sum of a given value and all previous values in the column.
choosedate, amount, and(quantity ) Exceed( Order bydate ) asrunning total fromSales volume;
moving average (akaThe running average is a technique for analyzing trends in time series data. It is the average of the current price and a specified number of previous prices.
choosedate, price, average value (price ) Exceed( Order bydate between the lines2 forward and the current row ) asmoving average fromshare price?
Difference (Delta) between two series
chooseAnnual income, back(income ) Exceed(sorted by year ) asRevenue_previous_year, Revenue - back(income ) Exceed(sorted by year ) asdifference annually fromannual indicators;
Learn about SQL window functions in our interactivewindow modecourse.