12.2 例1
CREATE SCHEMA SSB
CREATE TABLE part (p_partkey int, p_name varchar(22), p_category varchar(7))
CREATE VIEW part_view AS
SELECT p_name, p_category FROM part WHERE p_partkey <200;
CREATE SCHEMA SSB;
CREATE TABLE SSB.part (p_partkey int, p_name varchar(22), p_category varchar(7));
CREATE VIEW SSB.part_view AS
SELECT p_name, p_category FROM part WHERE p_partkey <200;
DROP TABLE SSB.part;
DROP VIEW SSB.part_view;
DROP SCHEMA SSB;
例2
先创建两个临时表part,supplier作为被参照表，然后创建带有参照完整性关系的表partsupp
CREATE TABLE part (p_partkey int primary key, p_name varchar(22));
CREATE TABLE supplier (S_SUPPKEY int primary key, s_name varchar(22));
CREATE TABLE PARTSUPP
(PS_PARTKEY integer,
PS_SUPPKEY integer,
PS_AVAILQTY integer,
PS_SUPPLYCOST Decimal,
PS_COMMENT varchar(199),
PRIMARY KEY(PS_PARTKEY, PS_SUPPKEY),/*使用表级约束定义复合主键*/
FOREIGN KEY (PS_PARTKEY) REFERENCES PART(p_partkey),
/*使用表级约束定义参照完整性约束*/
FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY));
例3
--1.创建part表
CREATE TABLE part (
p_partkey integer NOT NULL,
p_name varchar(22) NOT NULL,
p_mfgr varchar(6) NOT NULL,
p_category varchar(7) NOT NULL,
p_brand1 varchar(9) NOT NULL,
p_color varchar(11) NOT NULL,
p_type varchar(25) NOT NULL,
p_size integer NOT NULL,
p_container varchar(10) NOT NULL,
PRIMARY KEY (p_partkey)
);
--2.创建supplier表
CREATE TABLE supplier (
s_suppkey integer NOT NULL,
s_name varchar(25) NOT NULL,
s_address varchar(25) NOT NULL,
s_city varchar(10) NOT NULL,
s_nation varchar(15) NOT NULL,
s_region varchar(12) NOT NULL,
s_phone varchar(15) NOT NULL,
PRIMARY KEY (s_suppkey)
);
--3.创建customer表
CREATE TABLE customer (
c_custkey integer NOT NULL,
c_name varchar(25) NOT NULL,
c_address varchar(25) NOT NULL,
c_city varchar(10) NOT NULL,
c_nation varchar(15) NOT NULL,
c_region varchar(12) NOT NULL,
c_phone varchar(15) NOT NULL,
c_mktsegment varchar(10) NOT NULL,
PRIMARY KEY (c_custkey)
);
--4.创建date表
CREATE TABLE date (
d_datekey integer NOT NULL,
d_date varchar(19) NOT NULL,
d_dayofweek varchar(10) NOT NULL,
d_month varchar(10) NOT NULL,
d_year integer NOT NULL,
d_yearmonthnum integer NOT NULL,
d_yearmonth varchar(8) NOT NULL,
d_daynuminweek integer NOT NULL,
d_daynuminmonth integer NOT NULL,
d_daynuminyear integer NOT NULL,
d_monthnuminyear integer NOT NULL,
d_weeknuminyear integer NOT NULL,
d_sellingseason varchar(13) NOT NULL,
d_lastdayinweekfl varchar(1) NOT NULL,
d_lastdayinmonthfl varchar(1) NOT NULL,
d_holidayfl varchar(1) NOT NULL,
d_weekdayfl varchar(1) NOT NULL,
PRIMARY KEY (d_datekey)
);
--5.创建lineorder表
CREATE TABLE lineorder (
lo_orderkey integer NOT NULL,
lo_linenumber integer NOT NULL,
lo_custkey integer NOT NULL,
lo_partkey integer NOT NULL,
lo_suppkey integer NOT NULL,
lo_orderdate integer NOT NULL,
lo_orderpriority varchar(15) NOT NULL,
lo_shippriority varchar(1) NOT NULL,
lo_quantity integer NOT NULL,
lo_extendedprice integer NOT NULL,
lo_ordertotalprice integer NOT NULL,
lo_discount integer NOT NULL,
lo_revenue integer NOT NULL,
lo_supplycost integer NOT NULL,
lo_tax integer NOT NULL,
lo_commitdate integer NOT NULL,
lo_shipmode varchar(10) NOT NULL,
PRIMARY KEY (lo_orderkey,lo_linenumber),
FOREIGN KEY (lo_custkey) REFERENCES customer(c_custkey),
FOREIGN KEY (lo_partkey) REFERENCES part(p_partkey),
FOREIGN KEY (lo_suppkey) REFERENCES supplier(s_suppkey),
FOREIGN KEY(lo_orderdate) REFERENCES date(d_datekey)
);
例4
ALTER TABLE lineorder ADD lo_surrkey int;
--SQL命令解析：增加一个int类型的列lo_surrkey：
ALTER TABLE lineorder ALTER COLUMN lo_quantity SMALLINT;
--SQL命令解析：将lo_quantity列的数据类型修改为SMALLINT：
ALTER TABLE lineorder ALTER COLUMN lo_orderpriority varchar(15) NULL;
--SQL命令解析：将lo_orderpriority列的NOT NULL约束修改为NULL约束：
ALTER TABLE lineorder DROP COLUMN lo_shippriority;
--SQL命令解析：删除表中的列lo_shippriority：
ALTER TABLE lineorder ADD CONSTRAINT FK_S FOREIGN KEY (lo_suppkey) REFERENCES supplier(s_suppkey);
--SQL命令解析：在lineorder表中增加一个外键约束。CONSTRAINT关键字定义约束的名称FK_S，然后定义表级参照完整性约束条件。
例5
注：因为参照完整性约束关系不能直接删除。
DROP TABLE part;
例6
CREATE UNIQUE INDEX s_name_Inx ON supplier(s_name);
CREATE INDEX s_n_c_Inx ON supplier(s_nation ASC, s_city DESC);
例7
DROP INDEX supplier.s_n_c_Inx;
DROP INDEX s_name_Inx ON supplier;
例8
注：需要先按13.1节内容导入数据，然后执行查询。
SELECT * FROM part;
SELECT p_partkey, p_name, p_mfgr, p_category, p_brand1, p_color, p_type, p_size, p_container
FROM part;
例9
SELECT p_name, p_brand1, p_container FROM part;
例10
SELECT lname,fname,birthdate FROM customer;
--SQL查询解析：输出表中原始的列信息，其中姓名分别存储为lname和fname两列，存储有出生日期信息但没有年龄信息。
SELECT fname+' '+lname, YEAR(birthdate) FROM customer;
--SQL查询解析：将字符型的lname和fname列进行字符串连接操作，输出以空格间隔的姓名格式的表达式，并通过日期函数YEAR计算出用户出生年份。由于输出的是表达式派生列，因此输出默认的列名为空。
SELECT fname+' '+lname AS fullname, (2015-YEAR(birthdate)) AS age FROM customer;
--SQL查询解析：在列表达式后通过AS命令增加一个列别名，标识表达式派生列，将通过构造日期表达式计算出用户在2015年时的年龄。
SELECT fname+' '+lname AS fullname, (2015-YEAR(birthdate)) AS age, 2015 as updatetime FROM customer;
--SQL查询解析：增加一个常量输出列，标识当前年份2015。
例11
SELECT s_region FROM supplier;
--SQL命令解析：输出s_region列中全部的取值，包括了重复的取值。
SELECT DISTINCT s_region FROM supplier;
--SQL命令解析：通过DISTINCT短语指定列s_region只输出不同取值的成员，列中的每个取值只输出一次。
例12
SELECT total_children FROM customer WHERE total_children>2;
--SQL命令解析：输出customer表中total_children>2的记录的total_children列。
SELECT lname FROM customer WHERE lname>'t';
--SQL命令解析：输出customer表中lname>'t'的记录的lname列。Lname是字符型属性，表达式以字典序为标准对字符型数据进行比较。
SELECT birthdate FROM customer WHERE birthdate >'1950-01-01';
--SQL命令解析：输出customer表中birthdate >'1950-01-01'的记录的birthdate列。Birthdate为datetime数据类型，表达式中的日期常量需要满足数据库日期数据类型的格式，在SQL server中的日期常量表示为'1950-01-01'。
SELECT birthdate FROM customer WHERE 2015-YEAR(birthdate)>40;
--SQL命令解析：输出customer表中年龄超过40岁的用户的birthdate。用户年龄通过表达式2015-YEAR(birthdate)计算得到。
SELECT total_children,num_cars_owned FROM customer
WHERE total_children>num_cars_owned;
--SQL命令解析：输出customer表中孩子数量超过汽车数量的记录。total_children> num_cars_owned是两个列表达式之间的比较操作。
例13
SELECT * FROM customer WHERE total_children BETWEEN 2 AND 4;
--SQL命令解析：输出customer表中total_children数量介于2和4之间的记录。
SELECT * FROM customer WHERE total_children>=2 AND total_children<=4;
--SQL命令解析：total_children>=2 AND total_children<=4等价于BETWEEN 2 AND 4。
SELECT * FROM customer WHERE lname BETWEEN 'C' AND 'H';
--SQL命令解析：输出customer表中lname介于'C' 和 'H'之间的记录，支持字符型数据上的范围操作。
SELECT * FROM customer WHERE birthdate BETWEEN '1970-01-01' AND '1980-01-01';
--SQL命令解析：输出customer表中birthdate介于'1970-01-01'和'1980-01-01'之间的记录，支持日期型数据上的范围操作。
例14
SELECT * FROM customer WHERE total_children IN (1,4);
--SQL命令解析：输出total_children数量为1和4的记录。
SELECT * FROM customer WHERE lname IN ('Chin','Hill');
--SQL命令解析：输出lname为'Chin'和'Hill'的记录。
SELECT * FROM customer WHERE birthdate IN ('1944-10-25','1956-04-26','1970-09-21');
--SQL命令解析：输出birthdate为'1944-10-25'、'1956-04-26'和'1970-09-21'的记录。
例15
SELECT * FROM customer WHERE lname LIKE 'C%n';
--SQL命令解析：输出customer表中lname中以C开头，最后一个字母为n的记录。
SELECT * FROM customer WHERE lname LIKE '%i%n%';
--SQL命令解析：输出customer表中lname中包含字母i和n的记录且字母i在字母n之前。
SELECT * FROM customer WHERE lname LIKE 'Pa_';
--SQL命令解析：输出customer表中lname中以Pa开头，最后一个字母为任意字符的记录。
SELECT * FROM customer WHERE lname LIKE '_h%n_';
--SQL命令解析：输出customer表中lname中第二个字母为h，其后包含倒数第二个字母为n的记录。
SELECT * FROM customer WHERE lname LIKE 'Chow\_Wang' ESCAPE '\';
--SQL命令解析：输出customer表中lname中取值为Chow_Wang的记录，其中_为普通字符，不是通配符，由'\'表示其后的_为普通字符。
例16
SELECT * FROM customer WHERE mi IS NULL;
--SQL命令解析：输出customer表中mi列为空值为记录。
SELECT * FROM customer WHERE mi IS NOT NULL;
--SQL命令解析：输出customer表中mi列不是空值为记录。
例17
SELECT * FROM customer WHERE mi IS NULL AND gender='F' OR total_children>2;
--SQL命令解析：输出customer表中mi列为空值并且性别为女的客户记录或者孩子数量大于2个的客户记录。
SELECT * FROM customer WHERE mi IS NULL AND (gender='F' OR total_children>2);
--SQL命令解析：输出customer表中mi列为空值并且至少满足性别为女或者孩子数量大于2两个条件之一的客户记录。
例18
SELECT COUNT(*) FROM customer;
--SQL命令分析：统计customer表中记录的总数。
SELECT COUNT(mi) FROM customer;
--SQL命令分析：统计customer表中mi的元组数。当COUNT函数指定列时，列中的空值不进行计数，只统计非空值的记录数量而不是COUNT(*)所对应的表中记录数量。
SELECT COUNT(DISTINCT mi) FROM customer;
--SQL命令分析：统计customer表中mi列中不重复值的个数。
SELECT COUNT(DISTINCT mi) FROM customer WHERE mi!='';
--SQL命令分析：统计customer表中非空和非空白字符（''，长度为0的字符）的记录数量。
SELECT SUM(total_children) FROM customer;
--SQL命令分析：统计customer表中total_children的总和。
SELECT AVG(total_children) FROM customer;
--SQL命令分析：统计customer表中total_children的平均值。
SELECT MAX(total_children) FROM customer;
--SQL命令分析：统计customer表中total_children的最大值
SELECT MIN(total_children) FROM customer;
--SQL命令分析：统计customer表中total_children的最小值。
SELECT AVG(2015-YEAR(birthdate)) FROM customer;
--SQL命令分析：统计customer表中通过birthdate列计算出的平均年龄。
例19
SELECT COUNT(*) AS amount FROM customer;
--SQL命令解析：统计customer表中记录的总数。
SELECT gender,COUNT(*) AS amount FROM customer GROUP BY gender;
--SQL命令解析：按性别gender列分组统计customer表中客户记录的数量。
SELECT education,gender,COUNT(*) AS amount FROM customer GROUP BY education,gender ORDER BY education,gender;
--SQL命令解析：按教育程度education和性别gender列分组统计customer表中客户记录的数量。
例20
SELECT education,gender,COUNT(*) AS amount
FROM customer
GROUP BY education,gender
HAVING COUNT(*)>1000
ORDER BY education,gender;
--SQL命令解析：HAVING短语中的COUNT(*)>1000作为分组聚集计算结果的过滤条件，对分组聚集结果进行筛选。
SELECT education,gender,COUNT(*) AS amount,AVG(total_children) AS avg_children
FROM customer
GROUP BY education,gender
HAVING sum(num_cars_owned)>2000
ORDER BY education,gender;
--SQL命令解析：HAVING短语中可以使用输出目标列中没有的聚集函数表达式。如HAVING sum(num_cars_owned)>2000短语中sum(num_cars_owned)>2000并不是查询输出的聚集函数表达式，只用于对分组聚集计算结果进行筛选。
例21
SELECT education,gender,COUNT(*) AS amount
FROM customer
GROUP BY education,gender
ORDER BY education ASC,gender DESC;
--SQL命令解析：对查询结果按分组属性排序，第一排序属性为education升序，第二排序属性为gender降序。
SELECT education,gender,COUNT(*) AS amount
FROM customer
GROUP BY education,gender
ORDER BY COUNT(*);
--SQL命令解析：对分组聚集结果按聚集计算结果升序排列。
SELECT education,gender,COUNT(*) AS amount
FROM customer
GROUP BY education,gender
ORDER BY amount;
--SQL命令解析：当聚集表达式设置别名时，可以使用别名作为排序属性名，指代聚集表达式。
例22
SELECT customer_id,store_sales INTO sales_simple FROM sales_fact_1997;
SELECT customer_id,lname ,fname,gender,education INTO customer_simple FROM customer;
SELECT F.*,C.*
FROM sales_simple F,customer_simple C
WHERE F.customer_id=C.customer_id;
SELECT F.*,C.*
FROM sales_simple F INNER JOIN customer_simple C
ON F.customer_id=C.customer_id;
例23
SELECT F.customer_id,F.store_sales,C.lname,C.fname,C.gender,C.education
FROM sales_simple F,customer_simple C
WHERE F.customer_id=C.customer_id;
例24
SELECT F.customer_id,F.store_sales,C.lname,C.fname,C.gender,C.education
FROM sales_simple F,customer_simple C
WHERE F.customer_id=C.customer_id
AND F.store_sales BETWEEN 4 AND 8
AND C.education in ('Bachelors Degree','Graduate Degree');
例25
SELECT C.education,AVG(F.store_sales)
FROM sales_simple F,customer_simple C
WHERE F.customer_id=C.customer_id
AND C.gender='F'
GROUP BY C.education;
例26
SELECT COUNT(*) FROM customer c1,customer c2
WHERE c1.lname =c2.fname ;
SELECT c1.customer_id, c1.lname, c1.fname, c2.customer_id, c2.lname, c2.fname
FROM customer c1, customer c2
WHERE c1.lname =c2.fname ;
例27
SELECT F.*,C.* FROM sales_simple F LEFT OUTER JOIN customer_simple C
ON F.customer_id=C.customer_id;
SELECT F.*,C.* FROM sales_simple F RIGHT OUTER JOIN customer_simple C
ON F.customer_id=C.customer_id;
例28
注：前两个查询使用SSB数据库，后一个查询使用Foodmart数据库，注意sales_fact_1997表名称与自己的数据库名称一致
SELECT c_name,p_name,s_name,d_date, lo_quantity
FROM customer,part,supplier,date,lineorder
WHERE lo_custkey=c_custkey
AND lo_partkey=p_partkey
AND lo_suppkey=s_suppkey
AND lo_orderdate=d_datekey;
SELECT c_name,p_name,s_name,d_date, lo_quantity
FROM lineorder INNER JOIN customer ON lo_custkey=c_custkey
INNER JOIN part ON lo_partkey=p_partkey
INNER JOIN supplier ON lo_suppkey=s_suppkey
INNER JOIN date ON lo_orderdate=d_datekey;
SELECT r.sales_region,s.store_name,t.the_date,pr.media_type,
c.fname,p.product_name,pc.product_category,sf.store_sales
FROM region r,store s,time_by_day t,promotion pr,sales_fact_1997 sf,
customer c,product p, product_class pc
WHERE s.region_id=r.region_id
AND sf.store_id=s.store_id
AND sf.time_id=t.time_id
AND sf.promotion_id=pr.promotion_id
AND sf.customer_id=c.customer_id
AND sf.product_id=p.product_id
AND p.product_class_id=pc.product_class_id;
例29
SELECT SUM(store_sales) FROM sales_fact_1997 WHERE customer_id
IN (SELECT customer_id FROM customer WHERE gender='F');
SELECT SUM(store_sales) FROM sales_fact_1997 F,customer C
WHERE F.customer_id= C.customer_id AND C.gender='F';
例30
SELECT SUM(store_sales) FROM sales_fact_1997 WHERE product_id IN
(SELECT product_id FROM product WHERE product_class_id IN
(SELECT product_class_id FROM product_class
WHERE product_category='Seafood'));
SELECT SUM(store_sales)
FROM sales_fact_1997 F, product P, product_class PC
WHERE F.product_id=P.product_id AND P.product_class_id=PC.product_class_id
AND product_category='Seafood';
例31
SELECT COUNT(*) FROM sales_fact_1997_1997, customer
WHERE sales_fact_1997_1997.customer_id = customer.customer_id AND gender ='F'
AND store_sales >
(SELECT 1.5*AVG(store_sales) FROM sales_fact_1997_1997
WHERE sales_fact_1997_1997.customer_id = customer.customer_id);
SELECT COUNT(*) FROM sales_fact_1997, customer
WHERE sales_fact_1997.customer_id = customer.customer_id AND store_sales >
(SELECT 1.5*AVG(store_sales) FROM sales_fact_1997
WHERE sales_fact_1997.customer_id=customer.customer_id AND gender ='F');
SELECT COUNT(*) FROM sales_fact_1997 WHERE store_sales >
(SELECT 1.5*AVG(store_sales) FROM sales_fact_1997,customer
WHERE sales_fact_1997.customer_id=customer.customer_id AND gender ='F');
SELECT COUNT(*) FROM sales_fact_1997, customer
WHERE sales_fact_1997.customer_id = customer.customer_id
AND gender ='F'AND store_sales >
(SELECT 1.5*AVG(store_sales) FROM sales_fact_1997);
例32
SELECT COUNT(*) FROM sales_fact_1997 WHERE store_sales > ANY
(SELECT store_sales FROM sales_fact_1997,customer
WHERE sales_fact_1997.customer_id=customer.customer_id AND fname='Jeanne');
--SQL命令解析：内层查询返回用户Jeanne的所有store_sales值，外层查询判断store_sales是否满足大于内层查询store_sales值中任意一个的条件，并对满足条件的记录进行计数。
SELECT COUNT(*) FROM sales_fact_1997 WHERE store_sales >
(SELECT MIN(store_sales) FROM sales_fact_1997,customer
WHERE sales_fact_1997.customer_id=customer.customer_id AND fname='Jeanne');
--SQL命令解析：>ANY等价于大于多值结果集中的最小值，上述两个SQL命令等价。
例33
SELECT COUNT(*) FROM sales_fact_1997 WHERE store_sales > ALL
(SELECT store_sales FROM sales_fact_1997,customer
WHERE sales_fact_1997.customer_id=customer.customer_id AND fname='Jeanne');
--SQL命令解析：内层查询返回用户Jeanne的所有store_sales值，外层查询判断store_sales是否满足大于内层查询store_sales全部值的条件，并对满足条件的记录进行计数。
SELECT COUNT(*) FROM sales_fact_1997 WHERE store_sales >
(SELECT MAX(store_sales) FROM sales_fact_1997,customer
WHERE sales_fact_1997.customer_id=customer.customer_id AND fname='Jeanne');
--SQL命令解析：>ALL等价于大于多值结果集中的最大值，上述两个SQL命令等价。
例34
SELECT COUNT (*) FROM customer;
--SQL命令解析：统计客户总数。
SELECT COUNT(*) FROM customer WHERE EXISTS
(SELECT * FROM sales_fact_1997
WHERE sales_fact_1997.customer_id=customer.customer_id);
--SQL命令解析：判断外层查询的每一条记录的customer_id值是否在内层查询中存在满足sales_fact_1997.customer_id=customer.customer_id条件的记录，如果存在则外层查询进行计数。
SELECT COUNT (*) FROM customer WHERE NOT EXISTS
(SELECT * FROM sales_fact_1997
WHERE sales_fact_1997.customer_id=customer.customer_id);
--SQL命令解析：判断外层查询的每一条记录的customer_id值是否在内存查询中不存在满足sales_fact_1997.customer_id=customer.customer_id条件的记录，如果不存在则外层查询进行计数。后两个查询分别统计了存在和不存在销售记录的customer数量，统计结果之和与customer表中记录总数相等。
例35
SELECT customer_id FROM customer WHERE gender ='F' AND total_children>3
UNION
SELECT customer_id FROM customer WHERE member_card ='Silver' AND num_cars_owned =1;
--SQL命令解析：将UNION连接的两个子查询的结果集合并，两个结果集中的重复元组自动去掉。
SELECT customer_id FROM customer WHERE gender ='F' AND total_children>3
UNION ALL
SELECT customer_id FROM customer WHERE member_card ='Silver' AND num_cars_owned =1;
--SQL命令解析：将UNION连接的两个子查询的结果集合并，保留两个结果集中重复的元组。
SELECT customer_id FROM customer WHERE (gender ='F' AND total_children>3)
OR (member_card ='Silver' AND num_cars_owned =1);
--SQL命令解析：将两个子查询转换为用OR连接的复合谓词，查询结果集小于UNION ALL查询的元组数量，与UNION查询结果集相同。
例36
SELECT customer_id FROM customer WHERE gender ='F' AND total_children>3
INTERSECT
SELECT customer_id FROM customer WHERE member_card ='Silver' AND num_cars_owned =1;
--SQL命令解析：3个以上孩子的女性子查询结果集与有一辆车的银牌会员子查询的结果集执行交集运算，生成满足两个集合条件的查询结果集。
--交集子查询等价于子查询条件的与运算：
SELECT customer_id FROM customer WHERE (gender ='F' AND total_children>3) AND (member_card ='Silver' AND num_cars_owned =1);
例37
SELECT customer_id FROM customer WHERE gender ='F' AND total_children>3
INTERSECT
SELECT customer_id FROM customer WHERE member_card ='Silver' AND num_cars_owned =1;
--SQL命令解析：3个以上孩子的女性子查询结果集与有一辆车的银牌会员子查询的结果集执行交集运算，生成满足两个集合条件的查询结果集。
--交集子查询等价于子查询条件的与运算：
SELECT customer_id FROM customer WHERE (gender ='F' AND total_children>3) AND (member_card ='Silver' AND num_cars_owned =1);
例38
SELECT customer_id FROM customer WHERE member_card ='Silver' AND num_cars_owned =1
UNION
SELECT customer_id FROM sales_fact_1997 WHERE store_cost<0.2;
--SQL命令解析：集合操作连接的子查询可以使用相同的表，也可以使用不同的表，需要满足查询结果集具有相同的结构和语义。
例39
SELECT lname,C_sales.SUM_sales FROM customer C,
(SELECT customer_id, SUM(store_sales)
FROM sales_fact_1997 GROUP BY customer_id HAVING AVG(store_sales)>7)
AS C_sales(customer_id,SUM_sales)
WHERE C.customer_id=C_sales.customer_id;
WITH C_sales(customer_id,SUM_sales)
AS
(
SELECT customer_id, SUM(store_sales)
FROM sales_fact_1997
GROUP BY customer_id
HAVING AVG(store_sales)>7
)
SELECT lname,C_sales.SUM_sales
FROM customer C,C_sales
WHERE C.customer_id=C_sales.customer_id;
例40
INSERT INTO customer(customer_id,lname,fname,city)
VALUES (10282,'JACK','ROSE','New York');
例41
CREATE TABLE cust_test(city VARCHAR(50),cust_count INT);
INSERT INTO cust_test SELECT city,COUNT(*) FROM customer GROUP BY city ORDER BY city;
例42
SELECT customer_id,lname ,city INTO cust_test1 FROM customer;
例43
SELECT city,COUNT(*) AS cust_count INTO cust_test2
FROM customer GROUP BY city ORDER BY city;
例44
UPDATE customer SET gender='M' WHERE customer_id=10282;
例45
UPDATE customer SET member_card='Silver'
WHERE yearly_income ='$70K - $90K' AND num_children_at_home>2 AND member_card='Bronze';
例46
UPDATE customer SET member_card ='Golden'
FROM
(SELECT customer_id,SUM(store_sales) AS sum_cust
FROM sales_fact_1997
GROUP BY customer_id
HAVING SUM(store_sales)>800) AS cust_sum
WHERE customer.customer_id=cust_sum.customer_id;
例47
ALTER TABLE time_by_day ADD holiday_flag char(1);
--SQL命令解析：修改日期表time_by_day，增加一个日期标识列，由'T'或'F'标识是否假日。
UPDATE time_by_day SET holiday_flag='F';
--SQL命令解析：将holiday_flag初始化为'F'
UPDATE time_by_day SET holiday_flag='T'
WHERE the_date IN ('1997-01-01','1997-05-01','1997-06-01','1997-10-01');
--SQL命令解析：将1997年的1月1日，5月1日，6月1日，10月1日记录的holiday_flag标识为'T'。可以按日期逐年标识假日，也可以对各年假日按月-日批量修改。
UPDATE time_by_day SET holiday_flag='T'
WHERE month_of_year=10 AND day_of_month=1;
--SQL命令解析：将日期表中10月1日全部标识为假日。
UPDATE time_by_day SET holiday_flag='T'
WHERE month_of_year=5 AND day_of_month=1;
--SQL命令解析：将日期表中5月1日全部标识为假日。
例48
DELETE FROM cust_test ;
例49
DELETE FROM customer WHERE customer_id=10282;
例50
DELETE FROM sales_fact_1997
FROM customer ct INNER JOIN sales_fact_1997 sf ON ct.customer_id=sf.customer_id
WHERE ct.state_province='CA';
DELETE FROM sales_fact_1997
WHERE customer_id IN
(SELECT customer_id
FROM customer
WHERE state_province='CA');
例51
CREATE VIEW cust_sales(
customer_id,fullname,gender,education,store_sales,store_units,store_cost)
AS
SELECT C.customer_id,C.fullname,C.gender,C.education,F.store_sales,F.unit_sales,F.store_cost
FROM customer C,sales_fact_1997 F
WHERE C.customer_id=F.customer_id;
例52
CREATE VIEW gender_sales(gender,sum_sales,sum_units,sum_cost)
AS
SELECT gender,SUM(store_sales),SUM(unit_sales),SUM(store_cost)
FROM customer C,sales_fact_1997 F
WHERE C.customer_id=F.customer_id
GROUP BY gender;
CREATE VIEW gender_sales
AS
SELECT gender,SUM(store_sales) AS sum_sales,
SUM(unit_sales) AS sum_units,SUM(store_cost) AS sum_cost
FROM customer C,sales_fact_1997 F
WHERE C.customer_id=F.customer_id
GROUP BY gender;
例53
DROP VIEW gender_sales;
例54
CREATE VIEW SSB_tables
AS
SELECT customer.*,part.*,supplier.*,date.*,lineorder.*
FROM customer,part,supplier,date,lineorder
WHERE lo_custkey=c_custkey
AND lo_partkey=p_partkey
AND lo_suppkey=s_suppkey
AND lo_orderdate=d_datekey;
SELECT d_year, c_nation, SUM(CAST(lo_revenue - lo_supplycost AS bigint)) as profit
FROM date, customer, supplier, part, lineorder
WHERE lo_custkey = c_custkey
AND lo_suppkey = s_suppkey
AND lo_partkey = p_partkey
AND lo_orderdate = d_datekey
AND c_region = 'AMERICA'
AND s_region = 'AMERICA'
AND (p_mfgr = 'MFGR#1' OR p_mfgr = 'MFGR#2')
GROUP BY d_year, c_nation
ORDER BY d_year, c_nation;
--SQL命令改写为基于视图的单表查询。
SELECT d_year, c_nation, SUM(CAST(lo_revenue - lo_supplycost AS bigint)) as profit
FROM SSB_tables
WHERE c_region = 'AMERICA'
AND s_region = 'AMERICA'
AND (p_mfgr = 'MFGR#1' OR p_mfgr = 'MFGR#2')
GROUP BY d_year, c_nation
ORDER BY d_year, c_nation;
例55
SELECT LEN(lname) FROM customer;
--SQL命令解析：查询customer表lname列的字符串长度。
SELECT MAX(LEN(lname)),MIN(LEN(lname)),AVG(LEN(lname)) FROM customer;
--SQL命令解析：查看customer表lname列的最大长度、最小长度和平均长度。
SELECT CHARINDEX('sh', 'Washington'); --返回3
SELECT LEFT('李小明',1); --返回'李'
SELECT RIGHT ('李小明',2); --返回'小明'
SELECT SUBSTRING ('$30K - $50K',8,4); --返回'$50K'
例56
SELECT LEFT(address1,CHARINDEX(' ',address1)-1) AS addressNo,address1
FROM customer;
例57
SELECT DATEDIFF(YEAR, '10-01-1949', '10-01-2015'); --返回66
SELECT DATEDIFF(MONTH, '10-01-1949', '10-01-2015'); --返回792
SELECT DATEDIFF(DAY, '10-01-1949', '10-01-2015'); --返回24106
SELECT birthdate,
YEAR(GETDATE())-YEAR(birthdate) AS AGE0,
DATEDIFF(YEAR, birthdate, GETDATE()) AS AGE1,
DATEDIFF(DAY, birthdate, GETDATE())/365 AS AGE2
FROM customer;
例58
SELECT SUM(CAST(lo_revenue AS bigint)) FROM lineorder;
例59
SELECT AVG(CAST(lo_revenue AS bigint)) FROM lineorder;
--SQL命令解析：lo_revenue为bigint类型，AVG结果也为bigint类型。
SELECT CAST(AVG(CAST(lo_revenue AS real)) AS decimal(10,2)) FROM lineorder;
--SQL命令解析：使用CAST函数将AVG函数计算结果转换为带有两位小数位的decimal(10,2)类型。
例60
SELECT CONVERT(numeric(15,2),AVG(CONVERT(real,lo_extendedprice*lo_quantity))) as revenue FROM lineorder;
例61
SELECT lo_extendedprice,lo_quantity,lo_discount,1-CONVERT(decimal(4,2),lo_discount)/100 as discount FROM lineorder;
SELECT CONVERT(numeric(18,2),SUM(lo_extendedprice*lo_quantity* (1-CONVERT(decimal(4,2),lo_discount)/100)))
FROM lineorder;
例62
SELECT customer_id
,lname
,ROW_NUMBER()OVER
(ORDER BY lname) AS RowNum
FROM customer
ORDER BY lname;
SELECT customer_id
,lname
,RANK() OVER
(ORDER BY lname) AS RowNum
FROM customer
ORDER BY lname;
SELECT customer_id
,lname
,DENSE_RANK() OVER
(ORDER BY lname) AS RowNum
FROM customer
ORDER BY lname;
SELECT customer_id,
SUM(store_sales) AS sales
FROM sales_fact_1997
GROUP BY customer_id
ORDER BY sales;
SELECT customer_id,
SUM(store_sales)
AS sales,
RANK() OVER
(ORDER BY sales)
AS RowNum
FROM sales_fact_1997
GROUP BY customer_id
ORDER BY sales;
CREATE VIEW rank_sales(customer_id,sales)
AS
SELECT customer_id,
SUM(store_sales) AS sales
FROM sales_fact_1997
GROUP BY customer_id;
SELECT customer_id,sales, RANK() OVER (ORDER BY sales) AS rownum
FROM rank_sales
ORDER BY sales;
例63
SELECT
AVG(store_sales) AS avg_sales,
STDEV(store_sales) AS stedv_sales,
STDEVP(store_sales) AS stdevp_sales,
VAR(store_sales) AS var_sales,
VARP(store_sales) AS varp_sales
FROM sales_fact_1997;
例64
SELECT gender,marital_status,houseowner,
SUM(total_children) Num_children
FROM customer C,sales_fact_1997 F
WHERE C.customer_id=F.customer_id
GROUP BY gender,marital_status,houseowner;
SELECT gender,marital_status,houseowner,
SUM(total_children) Num_children
FROM customer C,sales_fact_1997 F
WHERE C.customer_id=F.customer_id
GROUP BY ROLLUP (gender,marital_status,houseowner);
SELECT gender,marital_status,houseowner,
SUM(total_children) Num_children
FROM customer C,sales_fact_1997 F
WHERE C.customer_id=F.customer_id
GROUP BY CUBE (gender,marital_status,houseowner);
案例实践
SSB 13个测试查询：
--Q1.1
select sum(CONVERT(real,lo_extendedprice*lo_quantity)) as
revenue
from lineorder, date
where lo_orderdate = d_datekey
and d_year = 1993
and lo_discount between 1 and 3
and lo_quantity < 25;
--Q1.2
select sum(CONVERT(real,lo_extendedprice*lo_quantity)) as
revenue
from lineorder, date
where lo_orderdate = d_datekey
and d_yearmonth = 'Jan1994'
and lo_discount between 4 and 6
and lo_quantity between 26 and 35;
--Q1.3
select sum(CONVERT(real,lo_extendedprice*lo_quantity)) as
revenue
from lineorder, date
where lo_orderdate = d_datekey
and d_weeknuminyear = 6
and d_year = 1994
and lo_discount between 5 and 7
and lo_quantity between 26 and 35;
--Q2.1
select sum(lo_revenue), d_year, p_brand1
from lineorder, date, part, supplier
where lo_orderdate = d_datekey
and lo_partkey = p_partkey
and lo_suppkey = s_suppkey
and p_category = 'MFGR#12'
and s_region = 'AMERICA'
group by d_year, p_brand1
order by d_year, p_brand1;
--Q2.2
select sum(lo_revenue), d_year, p_brand1
from lineorder, date, part, supplier
where lo_orderdate = d_datekey
and lo_partkey = p_partkey
and lo_suppkey = s_suppkey
and p_brand1 between 'MFGR#2221'
and 'MFGR#2228'
and s_region = 'ASIA'
group by d_year, p_brand1
order by d_year, p_brand1;
--Q2.3
select sum(lo_revenue), d_year, p_brand1
from lineorder, date, part, supplier
where lo_orderdate = d_datekey
and lo_partkey = p_partkey
and lo_suppkey = s_suppkey
and p_brand1= 'MFGR#2239'
and s_region = 'EUROPE'
group by d_year, p_brand1
order by d_year, p_brand1;
--Q3.1
select c_nation, s_nation, d_year,
sum(CONVERT(bigint,lo_revenue)) as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and c_region = 'ASIA'
and s_region = 'ASIA'
and d_year >= 1992 and d_year <= 1997
group by c_nation, s_nation, d_year
order by d_year asc, revenue desc;
--Q3.2
select c_city, s_city, d_year, sum(CONVERT(bigint,lo_revenue))
as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and c_nation = 'UNITED STATES'
and s_nation = 'UNITED STATES'
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
--Q3.3
select c_city, s_city, d_year, sum(CONVERT(bigint,lo_revenue))
as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and (c_city='UNITED KI1'
or c_city='UNITED KI5')
and (s_city='UNITED KI1'
or s_city='UNITED KI5')
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
--Q3.4
select c_city, s_city, d_year, sum(CONVERT(bigint,lo_revenue))
as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_orderdate = d_datekey
and (c_city='UNITED KI1'
or c_city='UNITED KI5')
and (s_city='UNITED KI1'
or s_city='UNITED KI5')
and d_yearmonth = 'Dec1997'
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
--Q4.1
select d_year, c_nation,
sum(CONVERT(bigint,lo_revenue - lo_supplycost)) as profit
from date, customer, supplier, part, lineorder
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_partkey = p_partkey
and lo_orderdate = d_datekey
and c_region = 'AMERICA'
and s_region = 'AMERICA'
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;
--Q4.2
select d_year, s_nation, p_category,
sum(CONVERT(bigint,lo_revenue - lo_supplycost)) as profit
from date, customer, supplier, part, lineorder
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_partkey = p_partkey
and lo_orderdate = d_datekey
and c_region = 'AMERICA'
and s_region = 'AMERICA'
and (d_year = 1997 or d_year = 1998)
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, s_nation, p_category
order by d_year, s_nation, p_category;
--Q4.3
select d_year, s_city, p_brand1,
sum(CONVERT(bigint,lo_revenue - lo_supplycost)) as profit
from date, customer, supplier, part, lineorder
where lo_custkey = c_custkey
and lo_suppkey = s_suppkey
and lo_partkey = p_partkey
and lo_orderdate = d_datekey
and s_nation = 'UNITED STATES'
and (d_year = 1997 or d_year = 1998)
and p_category = 'MFGR#14'
group by d_year, s_city, p_brand1
order by d_year, s_city, p_brand1;
扫一扫
1117
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
