79.sql

来自「数据库电子书」· SQL 代码 · 共 103 行

create table customer
(
 customer_name char(20),
 customer_street char(30),
 customer_city  char(30),
 primary key (customer_name)
)

create table branch
(
 branch_name  char(15) primary key,
 branch_city  char(30),
 assets       numeric(16,2),

)

create table account
(
 account_number char(10) primary key,
 branch_name char(15),
 balance  numeric(12,2)
)

create table depositor 
(
 customer_name  char(20),
 account_number char (10),
 primary key(customer_name, account_number)
)


use pubs
select * from sales


select stor_id, sum(qty) sumqty
from sales
group by stor_id

select ord_num, sum(qty)
from sales
group by stor_id


select stor_id, sum(qty) as sumqty
from sales
group by stor_id
having sum(qty)>90

select stor_id, ord_num, sum(qty)
 from sales
group by stor_id, ord_num

在store关系增加一行 ，8099
同时修改sales关系，允许payterms为空

insert into sales
values('8099', '6871','1995-05-05',20,null, 'BU1032')


比较
select count(payterms) from sales
select count(*) from sales


some

select stor_id from sales
where qty > some (
    select qty from sales
    where year(ord_date)='1995')

all
select stor_id from sales
where qty > all (
    select qty from sales
    where year(ord_date)='1995')



USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
GO

-- Or, using the IN clause:

USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')
GO