HAHA学院(6)- 结构化查询语言系列(SQL)

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.