Results of 1 - 10 of about 18 for City (0.002 sec.)
- SQL Introduction 12285
- This page provides an overview of how to perform simple operations in SQL. This tutorial is only intended to give you an introduction and is in no way a complete tutorial on SQL. This tutorial is adapted from the PostgreSQL tutorial . DuckD
...u can create a new table by specifying the table name, along with all column names and their types: CREATE TABLE weather ( city VARCHAR, temp_lo INTEGER, -- minimum temperature on a day temp_hi INTEGER, -- maximum temperature on a day prcp FLOAT, dat...the parameters for the command. First, the table name, weather , is given. Then the column names and column types follow. city VARCHAR specifies that the table has a column called city that is of type VARCHAR . VARCHAR specifies a data type that can...o remember the order of the columns. An alternative syntax allows you to list the columns explicitly: INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'); You can list the columns in a differen... - https://man.mtool.work/duckdb/2.0/sql/introduction.html
- GROUP BY Clause 10792
- The GROUP BY clause specifies which grouping columns should be used to perform any aggregations in the SELECT clause. If the GROUP BY clause is specified, the query is always an aggregate query, even if no aggregations are present in the SE
...ORDER BY tag; tag n a 1 b 3 c 1 Examples Count the number of entries in the addresses table that belong to each different city: SELECT city, count(*) FROM addresses GROUP BY city; Compute the average income per city per street_name : SELECT city, st...reet_name, avg(income) FROM addresses GROUP BY city, street_name; GROUP BY ALL Examples Group by city and street_name to remove any duplicate values: SELECT city, street_name...FROM addresses GROUP BY ALL; Compute the average income per city per street_name. Since income is wrapped in an aggregate function, do not include it in the GROUP BY : SELECT city, street... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/groupby.html
- ORDER BY Clause 10483
- ORDER BY is an output modifier. Logically it is applied near the very end of the query (just prior to LIMIT or OFFSET , if present). The ORDER BY clause sorts the rows on the sorting criteria in either ascending or descending order. In addi
...ll examples use this example table: CREATE OR REPLACE TABLE addresses AS SELECT '123 Quack Blvd' AS address, 'DuckTown' AS city, '11111' AS zip UNION ALL SELECT '111 Duck Duck Goose Ln', 'DuckTown', '11111' UNION ALL SELECT '111 Duck Duck Goose Ln',...Duck Town', '11111' UNION ALL SELECT '111 Duck Duck Goose Ln', 'Duck Town', '11111-0001'; Select the addresses, ordered by city name using the default NULL order and default order: SELECT * FROM addresses ORDER BY city; Select the addresses, ordered...by city name in descending order with nulls at the end: SELECT * FROM addresses ORDER BY city DESC NULLS LAST; Order by city and t... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/orderby.html
- GROUPING SETS 10294
- GROUPING SETS , ROLLUP and CUBE can be used in the GROUP BY clause to perform a grouping over multiple dimensions within the same query. Note that this syntax is not compatible with GROUP BY ALL . Examples Compute the average income along t
...provided four different dimensions: -- the syntax () denotes the empty set (i.e., computing an ungrouped aggregate) SELECT city, street_name, avg(income) FROM addresses GROUP BY GROUPING SETS ((city, street_name), (city), (street_name), ()); Compute...the average income along the same dimensions: SELECT city, street_name, avg(income) FROM addresses GROUP BY CUBE (city, street_name); Compute the average income along the dimension...s (city, street_name) , (city) and () : SELECT city, street_name, avg(income) FROM addresses GROUP BY ROLLUP (city, street_name);... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/grouping_sets.html
- SELECT Clause 10106
- The SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expressions here are executed only at the end. The SELECT clause can contain arbitrary expressions that
...es: SELECT res: col1 + col2, root: sqrt(col1) FROM tbl; Select all unique cities from the addresses table: SELECT DISTINCT city FROM addresses; Return the total number of rows in the addresses table: SELECT count(*) FROM addresses; Select all columns...except the city column from the addresses table: SELECT * EXCLUDE (city) FROM addresses; Select all columns from the addresses table, but...replace city with lower(city) : SELECT * REPLACE (lower(city) AS city) FROM addresses; Select all columns matching the given regular ex... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/select.html
- HAVING Clause 9900
- The HAVING clause can be used after the GROUP BY clause to provide filter criteria after the grouping has been completed. In terms of syntax the HAVING clause is identical to the WHERE clause, but while the WHERE clause occurs before the gr
...lause occurs after the grouping. Examples Count the number of entries in the addresses table that belong to each different city , filtering out cities with a count below 50: SELECT city, count(*) FROM addresses GROUP BY city HAVING count(*) >= 50; Co...mpute the average income per city per street_name , filtering out cities with an average income bigger than twice the median income : SELECT city, street_na...me, avg(income) FROM addresses GROUP BY city, street_name HAVING avg(income) > 2 * median(income); Syntax... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/having.html
- PIVOT Statement 9128
- The PIVOT statement allows distinct values within a column to be separated into their own columns. The values within those new columns are calculated using an aggregate function on the subset of rows that match each distinct value. DuckDB i
...', 2020, 1158), ('US', 'Seattle', 2000, 564), ('US', 'Seattle', 2010, 608), ('US', 'Seattle', 2020, 738), ('US', 'New York City', 2000, 8015), ('US', 'New York City', 2010, 8175), ('US', 'New York City', 2020, 8772); SELECT * FROM cities; country nam...1005 NL Amsterdam 2010 1065 NL Amsterdam 2020 1158 US Seattle 2000 564 US Seattle 2010 608 US Seattle 2020 738 US New York City 2000 8015 US New York City 2010 8175 US New York City 2020 8772 PIVOT ON and USING Use the PIVOT statement below to create...ON year USING sum(population); country name 2000 2010 2020 NL Amsterdam 1005 1065 1158 US Seattle 564 608 738 US New York City 8015 8175 8772 In the above example, the sum aggregate is always operating on a single value. If we only want to change th... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- FROM and JOIN Clauses 8973
- The FROM clause specifies the source of the data on which the remainder of the query should operate. Logically, the FROM clause is where the query starts execution. The FROM clause can contain a single table, a combination of multiple table
...ote that both tables are intentionally incomplete, i.e., they do not have a matching pair in the other table. CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR); CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR); INSERT INTO ci...ue Airport'), ('MST', 'Maastricht Aachen Airport'); To join the tables on their shared IATA attributes, run: SELECT * FROM city_airport NATURAL JOIN airport_names; This produces the following result: city_name iata airport_name Amsterdam AMS Amsterda...included in the result. We can also express this query using the vanilla JOIN clause with the USING keyword: SELECT * FROM city_airport JOIN airport_names USING (iata); Semi and Anti Joins Semi joins return rows from the left table that have at least... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/from.html
- LIMIT and OFFSET Clauses 8733
- LIMIT is an output modifier. Logically it is applied at the very end of the query. The LIMIT clause restricts the amount of rows fetched. The OFFSET clause indicates at which position to start reading the values, i.e., the first OFFSET valu
...g the first 5 rows): SELECT * FROM addresses LIMIT 5 OFFSET 5; Select the top 5 cities with the highest population: SELECT city, count(*) AS population FROM addresses GROUP BY city ORDER BY population DESC LIMIT 5; Select 10% of the rows from the add...ROWS ONLY syntax. It can be combined with OFFSET , in which case FETCH NEXT reads better: SELECT * FROM addresses ORDER BY city FETCH FIRST 5 ROWS ONLY; SELECT * FROM addresses ORDER BY city OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY; FIRST and NEXT are i...nterchangeable, as are ROW and ROWS . Omitting the row count fetches a single row: SELECT * FROM addresses ORDER BY city FETCH FIRST ROW ONLY; Syntax... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/limit.html
- UPDATE Statement 8270
- The UPDATE statement modifies the values of rows in a table. Examples For every row where i is NULL , set the value to 0 instead: UPDATE tbl SET i = 0 WHERE i IS NULL; Set all values of i to 1 and all values of j to 2: UPDATE tbl SET i = 1,
...ows to update, UPDATE statements can use the FROM clause and express joins via the WHERE clause. For example: CREATE TABLE city (name VARCHAR, revenue BIGINT, country_code VARCHAR); CREATE TABLE country (code VARCHAR, name VARCHAR); INSERT INTO city...INSERT INTO country VALUES ('FR', 'France'), ('BE', 'Belgium'); To increase the revenue of all cities in France, join the city and the country tables, and filter on the latter: UPDATE city SET revenue = revenue + 100 FROM country WHERE city.country_...code = country.code AND country.name = 'France'; SELECT * FROM city; name revenue country_code Paris 800 FR Lyon 300 FR Brussels 400 BE Upsert (Insert or Update) See the Insert documentation... - https://man.mtool.work/duckdb/2.0/sql/statements/update.html
DuckDB 2.0 Documentation / man.mtool.work