CREATE TABLE IF NOT EXISTS `project.dataset.table_name`
(
someName STRING,
dateTime TIMESTAMP NOT NULL, -- REQUIRED or non-null column
index INT64, -- INT64 for INTEGER column
longitude FLOAT64 -- FLOAT64 for FLOAT column
);
CREATE TABLE IF NOT EXISTS `united-strategy-279207.Example.proverka_02`
(
ADDRESS_ID STRING,
INDIVIDUAL_ID STRING,
FIRST_NAME STRING,
LAST_NAME STRING,
sum_purchase INT64
);
INSERT INTO `united-strategy-279207.Example.proverka_02`
(ADDRESS_ID,INDIVIDUAL_ID,FIRST_NAME,LAST_NAME,sum_purchase)
VALUES
('1','1','Petya','Sssss',1000),
('2','2','Ivan','Qqqqqq',2000),
('3','3','Pasha','Rrrrrr',5000),
('4','4','Dima','Vvvvvv',777)
INSERT INTO `united-strategy-279207.Example.proverka_02`
VALUES
('5','5','Sliva','Ddddd',300),
('6','6','Darya','OOoooo',5000)
UPDATE `united-strategy-279207.Example.proverka_02`
SET ADDRESS_ID = 'bbbbbbb', INDIVIDUAL_ID = '222222', FIRST_NAME = 'Kapustka', LAST_NAME = 'jirniy', sum_purchase = 202002
WHERE ADDRESS_ID = 'f43j231';
DELETE FROM `united-strategy-279207.Example.proverka_02`
WHERE ADDRESS_ID = 'fghj231';
MERGE `united-strategy-279207.Example.manager_2` as target
USING `united-strategy-279207.Example.manager_1` as source
ON target.managerID = source.managerID
WHEN MATCHED
THEN UPDATE SET managerID = source.managerID
WHEN NOT MATCHED
THEN INSERT
VALUES ( source.managerID, source.manager_name, source.sum_sales)
WHEN NOT MATCHED BY SOURCE
THEN
DELETE
select * from `united-strategy-279207.Example.manager_2`
select * from `united-strategy-279207.Example.manager_2`
order by managerID asc
select * from `united-strategy-279207.Example.manager_2`
where sum_sales < 2000
order by managerID asc
select distinct * from `united-strategy-279207.Example.manager_2`
order by managerID asc
select sum (sum_sales) as count_sales, manager_name
from `united-strategy-279207.Example.manager_2`
group by manager_name
select sum (sum_sales) as count_sales, manager_name
from `united-strategy-279207.Example.manager_2`
group by manager_name
having sum (sum_sales) > 2000
select manager_name, sum_sales from `united-strategy-279207.Example.manager_2`
where sum_sales between 1000 and 3000
select manager_name, sum_sales from `united-strategy-279207.Example.manager_2`
where manager_name like '%A%'
select manager_name, sum_sales from `united-strategy-279207.Example.manager_2`
where manager_name like '%a%'
select manager_name, sum_sales from `united-strategy-279207.Example.manager_2`
where manager_name like '____ __'
select manager_name, sum_sales from `united-strategy-279207.Example.manager_2`
where sum_sales in (1000,2000)
select count(sum_sales) as count_sales,
sum(sum_sales) as sum_sum_sales,
avg(sum_sales) as avg_sales,
min (sum_sales) as min_sales,
max (sum_sales) as max_sales,
count (distinct sum_sales) as distinct_count_sum_sales
from `united-strategy-279207.Example.manager_2`
select avg(sum_sales) as avg_sales from `united-strategy-279207.Example.manager_2`
select * from `united-strategy-279207.Example.manager_2`
where sum_sales >
(select avg(sum_sales) from `united-strategy-279207.Example.manager_2`
)
SELECT p.product, p.price, m.model FROM price AS p
LEFT JOIN model AS m ON p.id = m.id
SELECT p.product, p.price, m.model FROM price AS p
RIGHT JOIN model AS m ON p.id = m.id
SELECT p.product, p.price, m.model FROM price AS p
RIGHT JOIN model AS m ON p.id = m.id
SELECT p.product, p.price, m.model FROM price AS p
FULL JOIN model AS m ON p.id = m.id
CREATE VIEW all_managers_projects AS
SELECT m.name, p.name AS projects_name
FROM managers AS m JOIN managers_projects AS mp
ON m.id = mpmanager_id JOIN projects AS p
ON p.id = mp.project_id
SELECT * from `united-strategy-279207.Example.all_managers_projects`
SELECT name, count(projects_name) as count_projects from `united-strategy-279207.Example.all_managers_projects`
group by name
WITH all_managers_projects AS (
SELECT m.name, p.name as projects_name
FROM `united-strategy-279207.Example.managers` AS m JOIN `united-strategy-279207.Example.managers_projects` AS mp
ON m.id = mp.manager_id JOIN `united-strategy-279207.Example.projects` AS p
ON p.id = mp.project_id
)
SELECT name, count(projects_name) as count_projects from all_managers_projects
group by name