图书销售系统

  1. 根据图书销售系统关系模式设计，创建表 A-1 至表 A-6

    表A-1 CUSTOMERS
    字段名数据类型长度约束说明
    customer_idNUMBER4PRIMARY KEY客户编号
    nameCHAR20NOT NULL客户名称
    phoneVARCHAR250NOT NULL电话
    emailVARCHAR250Email
    addressVARCHAR2200地址
    codeVARCHAR210邮政编码
    表A-2 PUBLISHERS
    字段名数据类型长度约束说明
    publisher_idNUMBER2PRIMARY KEY出版社号
    nameVARCHAR250出版社名
    contactCHAR10联系人
    phoneVARCHAR250电话
    表A-3 BOOKS
    字段名数据类型长度约束说明
    ISBNVARCHAR250PRIMARY KEY图书号
    titleVARCHAR250图书名
    authorVARCHAR250作者
    pubdateDATE出版日期
    publisher_idNUMBER2FOREIGN KEY出版社 ID
    costNUMBER6,2批发（大于 10 本）价格
    retailNUMBER6,2零售价格
    categoryVARCHAR250图书类型
    表A-4 ORDERS
    字段名数据类型长度约束说明
    order_idNUMBER4PRIMARY KEY订单号
    customer_idNUMBER4FOREIGN KEY顾客号
    orderdateDATENOT NULL订货日期
    shipdateDATE发货日期
    shipaddressVARCHAR2200发货地址
    shipcodeVARCHAR210发货邮政编码
    表A-5 ORDERITEM
    字段名数据类型长度约束说明
    order_idNUMBER4FOREIGN KEY订单号
    item_idNUMBER4订单明细号
    ISBNVARCHAR250NOT NULL图书编号
    quantityNUMBER4图书数量
    表A-6 PROMOTION
    字段名数据类型长度约束说明
    gift_idNUMBER2礼品编号
    nameCHAR20PRIMARY KEY礼品名称
    minretailNUMBER5,2NOT NULL图书最低价
    maxretialNUMBER5,2图书最高价
    点我看答案
    create table customers
(
    customer_id number(4) primary key,
    name        char(20)     not null,
    phone       varchar2(50) not null,
    email       varchar2(50),
    address     varchar2(200),
    code        varchar2(10)
);

create table publishers
(
    publisher_id number(2) primary key,
    name         varchar2(50),
    contact      char(10),
    phone        varchar2(50)
);

create table books
(
    isbn         varchar2(50) primary key,
    title        varchar2(50),
    author       varchar2(50),
    pubdate      date,
    publisher_id number(2),
    cost         number(6, 2),
    retail       number(6, 2),
    category     varchar2(50)
);
alter table books
    add constraint fk_books_publisher_id foreign key (publisher_id)
        references publishers (publisher_id);

create table orders
(
    order_id    number(4) primary key,
    customer_id number(4),
    orderdate   date not null,
    shipdate    date,
    shipaddress varchar2(200),
    shipcode    varchar2(10)
);
alter table orders
    add constraint fk_orders_customer_id foreign key (customer_id)
        references customers (customer_id);

create table orderitem
(
    order_id number(4),
    item_id  number(4),
    isbn     varchar2(50) not null,
    quantity number(4)
);
alter table orderitem
    add constraint fk_orderitem_order_id foreign key (order_id)
        references orders (order_id);

create table promotion
(
    gift_id   number(2),
    name      char(20) primary key,
    minretail number(5, 2) not null,
    maxretial number(5, 2)
);

  2. 将表 A-7 至表 A-12 中的数据插入数据库的相应表中。

    表A-7 CUSTOMERS
    customer_idnamephoneemailaddresscode
    1王牧83823422Wangmu@sina.com北京110010
    2李青83824566Liqing@sina.com大连116023
    表A-8 PUBLISHERS
    publisher_idnamecontactPhone
    1电子工业出版社张芳56231234
    2机械工业出版社孙翔89673456
    表A-9 BOOKS
    ISBNtitleauthorpubdatepublisher_idcostretailcategory
    978-7-121-18619-8文件化基础王澜2010-1-123528管理
    978-7-122-18619-8Oracle孙风栋2011-2-114032计算机
    表A-10 ORDERS
    order_idcustomer_idorderdateshipdateshipaddressshipcode
    100012013-2-12013-2-5大连116023
    100122013-3-12013-3-10大连116023
    表A-11 ORDERITEM
    order_iditem_idISBNquantity
    10001978-7-121-18619-85
    10002978-7-122-18619-820
    10011978-7-121-18619-815
    表A-12 PROMOTION
    gift_idnameminretailmaxretial
    1签字笔100150
    2笔记本150300
    3保温杯300500
    点我看答案
    insert into customers
values (1, '王牧', '83823422', 'Wangmu@sina.com', '北京', '110010');
insert into customers
values (2, '李青', '83824566', 'Liqing@sina.com', '大连', '116023');

insert into publishers
values (1, '电子工业出版社', '张芳', '56231234');
insert into publishers
values (2, '机械工业出版社', '孙翔', '89673456');

insert into books
values ('978-7-121-18619-8', '文件化基础', '王澜', to_date('2010-1-1', 'YYYY-MM-DD'), 2, 35, 28, '管理');
insert into books
values ('978-7-122-18619-8', 'Oracle', '孙风栋', to_date('2011-2-1', 'YYYY-MM-DD'), 1, 40, 32, '计算机');

insert into orders
values (1000, 1, to_date('2013-2-1', 'YYYY-MM-DD'), to_date('2013-2-5', 'YYYY-MM-DD'), '大连', '116023');
insert into orders
values (1001, 2, to_date('2013-3-1', 'YYYY-MM-DD'), to_date('2013-3-10', 'YYYY-MM-DD'), '大连', '116023');

insert into orderitem
values (1000, 1, '978-7-121-18619-8', 5);
insert into orderitem
values (1000, 2, '978-7-122-18619-8', 20);
insert into orderitem
values (1001, 1, '978-7-121-18619-8', 15);

insert into promotion
values (1, '签字笔', 100, 150);
insert into promotion
values (2, '笔记本', 150, 300);
insert into promotion
values (3, '保温杯', 300, 500);

  3. 将 ISBN 为 978-7-121-18619-8 的图书的零售价格（retail）修改为 30。

    点我看答案
    update books
set retail = 30
where isbn = '978-7-121-18619-8';

  4. 将订单号为 1000 的订单的发货日期修改为“2013-2-2”。

    点我看答案
    update orders
set shipdate = to_date('2013-2-2', 'YYYY-MM-DD')
where order_id = 1000;

  5. 查询 BOOKS 表中包含的所有图书列表。

    点我看答案
    select *
from books;

  6. 列出 BOOKS 表中有图书类型非空的图书书名。

    点我看答案
    select title
from books
where category is not null;

  7. 列出 BOOKS 表中每本书的书名和出版日期，对 pubdate 字段使用 Publication Date 列标题。

    点我看答案
    select title, pubdate "Publication Date"
from books;

  8. 列出 CUSTOMERS 表中每一个客户的客户号及他们所在的地址。

    点我看答案
    select customer_id, address
from customers;

  9. 创建一个包含各个出版社的名称、联系人及出版社电话号码的列表。其中，联系人的列在显示的结果中重命名为 Contact Person。

    点我看答案
    select name, contact "Contact Person", phone
from publishers;

  10. 查询下达了订单的每一个客户的客户号。

    点我看答案
    select distinct customer_id
from orders;

  11. 查询 2013 年 3 月 1 日之后发货的订单。

    点我看答案
    select *
from orders
where shipdate > to_date('2013-3-1', 'YYYY-MM-DD');

  12. 查询居住在北京或大连的客户，将结果按姓名的升序排列。

    点我看答案
    select *
from customers
where address in ('北京', '大连')
order by name asc;

  13. 列出姓“王”的作者编写的所有图书信息，并将结果按姓名降序排序。

    点我看答案
    select *
from books
where author like '王%'
order by author desc;

  14. 查询“儿童”类和“烹饪”类的所有图书。

    点我看答案
    select *
from books
where category in ('儿童', '烹饪');

  15. 查询书名的第二个字母是“A”、第四个字母是“N”的图书信息。

    点我看答案
    select *
from books
where title like '_A_N%';

  16. 查询电子工业出版社在 2012 年出版的所有“计算机”类图书的名称。

    点我看答案
    select *
from books b
join publishers p
    on to_char(b.pubdate, 'YYYY') = 2012
        and b.category = '计算机';
        and p.publisher_id = b.publisher_id
        and p.name = '电子工业出版社'

  17. 查询图书名称、出版社名称、出版社联系人的名称和电话号码。

    点我看答案
    select b.title, p.name, p.contact, p.phone
from books b
    join publishers p
        on p.publisher_id = b.publisher_id;

  18. 查询当前还没有发货的订单信息及下达订单的用户名，查询结果按下达订单日期排序。

    点我看答案
    select o.*, c.name
from orders o
join customers c
    on c.customer_id = o.customer_id
        and o.shipdate is null
order by o.orderdate;

  19. 查询已经购买了“计算机”类图书的所有人的客户号和姓名。

    点我看答案
    select c.customer_id, c.name
from orders o
join customers c
    on c.customer_id = o.customer_id
join orderitem o1
    on o1.order_id = o.order_id
join books b
    on o1.isbn = b.isbn
where b.category = '计算机';

  20. 查询“王牧”购买的图书的 ISBN 以及书名。

    点我看答案
    select b.isbn, b.title
from orders o
join customers c
    on c.customer_id = o.customer_id
join orderitem o1
    on o1.order_id = o.order_id
join books b
    on o1.isbn = b.isbn
where c.name = '王牧';

  21. 查询订购图书“Oracle 数据库基础”的客户将收到什么样的礼品。

    点我看答案
    select c.name, p.name
from (
    select t1.order_id, sum(t1.总价) 订单总价
    from (
        select o1.order_id, sum(o1.quantity) * (case when sum(o1.quantity) > 10 then b.cost else b.retail end) 总价
        from orderitem o1
        join books b
            on o1.isbn = b.isbn
        group by o1.order_id, b.cost, b.retail
    ) t1
    group by t1.order_id
) t2,
promotion p,
orders o1,
customers c,
orderitem o2,
books b2
where t2.订单总价 between p.minretail and p.maxretial
    and t2.order_id = o1.order_id
    and o1.customer_id = c.customer_id
    and o1.order_id = o2.order_id
    and o2.isbn = b2.isbn
    and b2.title = 'Oracle';

  22. 确定客户“张扬”订购的图书的作者。

    点我看答案
    select b.author
from customers c1
join orders o1
    on c1.customer_id = o1.customer_id
join orderitem o2
    on o1.order_id = o2.order_id
join books b
    on o2.isbn = b.isbn
where c1.name = '张扬';

  23. 查询 CUSTOMERS 表中的每一个客户所下达的订单数量。

    点我看答案
    select c1.name, count(*)
from customers c1
join orders o1
    on c1.customer_id = o1.customer_id
group by c1.customer_id, c1.name;

  24. 查询价格低于同一种类图书的平均价格的图书的信息。

    点我看答案
    select *
from books b1
where retail < (
    select avg(retail)
    from books b2
    where b1.category = b2.category
);

  25. 查询每个出版社出版图书的平均价格、最高价格、最低价格。

    点我看答案
    select p.name, avg(b.retail), max(b.retail), min(b.retail)
from books b, publishers p
where b.publisher_id = p.publisher_id
group by b.publisher_id,p.name;

  26. 统计每个客户购买图书的数量及总价钱。

    点我看答案
    select customer_id, name, sum(总数量), sum(订单总价)
from (
    select c2.customer_id, c2.name, t2.*
    from (
        select t1.order_id, sum(t1.数量) 总数量, sum(t1.总价) 订单总价
        from (
            select o1.order_id,
                sum(o1.quantity) 数量,
                sum(o1.quantity) * (case when sum(o1.quantity) > 10 then b.cost else b.retail end) 总价
            from orderitem o1
            join books b
                on o1.isbn = b.isbn
            group by o1.order_id, b.cost, b.retail
        ) t1
        group by t1.order_id
    ) t2,
    orders o,
    customers c2
    where t2.order_id = o.order_id
        and o.customer_id = c2.customer_id
)
group by customer_id, name;
© 2022 刘士. All rights reserved.

结果匹配 ""

    没有匹配的结果 ""