1. catalog
2. select from
Select companyname as company,cityname as city from sts.dimcustomer
Select * from sts.dimcustomer
or select *(drag the table to the screen.)
3. Where
select *
from STS.dimcustomer
where cityname = 'London'
where cityname like 'R%'
where cityname like '%a'
where customerid between 0 and 10
and countryname ='UK'
where countryname ='UK' or countryname ='France' or countryname='Spain'
where countryname in ('UK','France','Spain')
4. function
Select count(*)AS COUNT_OF_CUSTOMERS
from 'sts'.'dimcustomer'
where countryname = 'USA'
Select SUM(quantity) * .89 as total from 'sts'.'dimcustomer'
select sume(netsales) as total, sum(netsales)* 1.1 as added_tax from'sts'.'dimcustomer'
select min(netsales) from 'sts'.'dimcustomer'
5. group by
selectregionname, sum(netsales)as total_sales, sum(quantity)as total_quantity
from 'stsflat'.'stscustomerfaltfile'
group by regionname, countryname
result is classified by more detailed countryname
6. order by
select *
from 'stsflat'.'stscustomerfaltfile'
order by countryname asc(desc),
cityname
7. having
selectcountryname as country
sum(netsales) as TOTAL_SALES
from 'stsflat'.'stscustomerfaltfile'
group by countryname
having sum(netsales) >10000000
order by countryname
8 top
select top 5countryname as country
sum(netsales) as Total_sales
from 'stsflat'.'stscustomerfaltfile'
group by countryname
having sum(netsales)>10000000
order by sum(netsales) desc
9. Create
create columntable "XTRA"."DIMCUSTOMERV2"("SUPPLIERID"INTERGER CS_INT NOT NULL,
"CITYID" INTEGER CS_INT,
"COMPANYNAME" NVARCHAR(20),
PRIMARY KEY("SUPPLIERID"))
10. Insert
insert into"XTRA"."MYTESTTABLE" VALUES
(1,12345,'VWBEETLE')
11. update
update"XTRA"."MYTESTTABLE"
set CARREGISTRATION =54367
WHERE CARID = 2
(WHERE CARID >2)
13 join
13.1 go to schema-views- add two tables
select
T0."COMPANYNAME",
T1."NETSALES"
FROM
"STS"."DIMCUSTOMER" T0 INNER JOIN "STS"."FCTCUSTOMERORDER" T1
ON T0."CUSTOMERID" = T1."CUSTOMERID"
OR
SELECT COMPANYNAME AS COMPANY
SUM(NETSALES) AS TOTAL_SALES
FROM "STS"."DIMCUSTOMER" ,"STS"."FCTCUSTOMERORDER"
WHERE "STS"."DIMCUSTOMER"."CUSTOMERID" = "STS"."FCTCUSTOMERORDER"CUSTOMERID"
GROUP BY COMPANYNAME
ORDER BY COMPANYNAME
14 SUBSELECT
select companyname as company, round(avg(netsales),0) as average_sales
from "sts"."dimcustomer", "sts"."fctcustomerorder"
where "sts"."dimcustomer"."customerid" = "sts"."fctcustomerorder"."customerid"
group by companyname
having avg(netsales)>
(select round(avg(netsales),2) as average
from "sts"."fctcustomerorder"
)
order by companyname
15 union
(select companyname as company from "sts"."dimcustomer")
union
(select companyname as company from"sts"."fctcustomerorder")
will have 243 records
(select companyname as company from "sts"."dimcustomer")
union all
(select companyname as company from"sts"."fctcustomerorder")
will have 244 records
16. drop
create table STS.AAA as
(select * from "STS"."DIMCUSTOMER"
);
--copy a table
TRUNCATE TABLE "STS"."AAA"
--DELETE ALL DATA FROM table AAA
drop table STS.AAA restrict;
--only Delete table, other related objects will not be deleted
drop table STS.AAA cascade
- delete the table and related objects
17. Create view
create view STS.STS_VIEW AS
select regionname as region, sum(netsales) as total_sales
from "sts"."dimcustomer", "sts"."fctcustomerorder"
where "sts"."dimcustomer"."customerid" = "sts"."fctcustomerorder"."customerid"
group by regionname
having sum(netsales)>5000000
order by regionname
18. create schema
create schema STS owned by newuser
drop schema "STS"
19 Table types
create row table sts.rowstoretable
(column INT)
create column table sts.columnstoretable
(clumna INT)
alter table "sts"."columnstoretable" alter type column
create type sts.ststabletype_t as table ("item" varchar(100),"VALUE" INT);
--only used in store precedure.