数据库试验_SQL语句.doc
文本预览下载声明
非绿色文字为语句，绿色为结果。
(2课时）
select * from authors;
select TITLE_ID,TITLE from test.titles;
select TITLE,price from test.titles where price=15 and price=10;
select TITLE_ID,TITLE from test.titles where TITLE LIKE
T%;
select TITLE,price from test.titles where TITLE LIKE
T% and price 16;
select TITLE_ID,TITLE,price from test.titles where TITLE not LIKE
T% and price 16;
select TITLE,YTD_SALES from test.titles where YTD_SALES is not null;
select count(distinct type ) from test.titles;
select count(distinct price) from titles;
select max(price) from titles;
select sum(ytd_sales) from titles where ytd_sales is not null;
select max(price),min(price),count(price)/count(title_id) average from titles where ytd_sales is not null;
select sum(ytd_sales) from titles where ytd_sales is not null group by pub_id ;
select count(distinct TITLE) from titles;
select au_fname,au_lname,phone from authors order by au_fname , au_lname asc;
select title, price from titles order by price desc;
select type,sum(price)/count(title_id) from titles where type like %cook% group by type;
select publishers.pub_name,titles.title from publishers,titles where publishers.pub_id=titles.pub_id;
select authors.au_fname,authors.au_lname,title from authors,titleauthor,titles where titles.title_id=titleauthor.title_id and titleauthor.au_id=authors.au_id;
select titles.title,titles.price,sales.qty from titles,sales where sales.title_id=titles.title_id and qty in(select max(qty) from sales)
实验三 建表(2课时）
CREATE TABLE Tp(
tname char(30),
tauthor char (30),
tid char (30),
tprice float,
tpub char(30)
) ;
alter table Tp
add tpubtime int;
select tname,tprice into STp
from Tp
create index ITpon Tptid)
create view VTpas select tid,tname,tprice from Tpwhere Tptprice20;
-- 创建一个视图，查询价格大于的书籍名字书籍号
select * from VTp--测试视图是否建立
/*
S3092 计算机原理 20.8
D1007 数
显示全部