吉林大学数据库系统概念SQL、关系代数习题汇总(持续更新)

奔腾 数据库系统原理考试（A卷）

// (1)
create table branch(
branch_name varchar(20),
branch_city varchar(20),
assets numeric(12, 2),
primary key (branch_name));

create table customer(
customer_name varchar(20),
customer_street varchar(20),
customer_city varchar(20),
primary key (customer_name));

create table loan(
loan_number varchar(5),
branch_name varchar(20),
amount numeric(12, 2),
primary key (loan_number),
foreign key (branch_name));

create table borrower(
customer_name varchar(20),
loan_number varchar(5),
primary key (customer_name, loan_number));

create table account(
account_name varchar(20),
branch_name varchar(20),
balance numeric(12, 2),
primary key (account_name),
foreign key (branch_name));

create table depositor(
customer_name varchar(20),
account_name varchar(20),
primary key (customer_name, account_name));

// （2）关系代数
Π Pi Πcustomer_name,account_number,balance( σ sigma σbranch_name=‘Brighton’(account Join depositor))

// (2) sql
select customer_name, account_number, balance 
from account natural join depositor 
where account.branch_name = 'Brighton'

// （3）关系代数
σ sigma σavg_balace<5000(branch_name G mathcal{G} G avg(balance) as avg_balance (account))
// 或者 标准答案写法
σ sigma σavg_balance<5000 ( ρ ho ρ branch_balance(branch_name,avg_balance)(branch_name G mathcal{G} G avg(balance)(account)))

// (3) sq1
select branch_name, avg(balance) 
from account 
group by branch_name 
having avg(balance) < 5000

// （4）关系代数
Π Pi Π customer_name (borrower) - Π Pi Π customer_name (despositor）

// (4) sq1
select distinct customer_name from borrower
where customer_name not in (
select distinct customer_name from depositor)

// (5) 关系代数
account ← leftarrow ← Π Pi Π account_number,branch_name,balance*1.03 ( σ sigma σ balance>avg(balance) (account)) igcup Π Pi Π account_number,branch_name,balance ( σ sigma σ balance<=avg(balance) (account))

// (5) sq1
update from account set balance = 
case
	when balance > avg(balance) then balance * 1.03
end
// 标准答案
update from account set balance = balance * 1.03 
where balance > 
(select avg(balance) from account)

奔腾 2018.6.15

// 关系代数 （1）
Π Pi Π Gname ( σ sigma σ Pname=‘wxy’ (GAME Join PERSON Join PG))

// (1) sql
select Gname from GAME 
natural join PG 
natural join PERSON 
where Pname = 'wxy'

// 关系代数 （2）
Π Pi ΠGname(GAME Join σ sigma σcnt>500 ( ρ ho ρGid_cnt(Gid,cnt)(Gid G mathcal{G} G count(Pid)(PG))))

// (2) sql
select Gname from GAME 
natural join PG 
group by Gid 
having count(Pid) > 500

// 关系代数 （3）
Π Pi Π Pname (PERSON Join ( Π Pi Π Pid ( σ sigma σ Gid=‘G01’(PG) - σ sigma σ Gid=‘G02’(PG))))

// (3) sql
select Pname from PERSON 
where Pid in 
(select Pid from PG where Gid = 'G01') 
and Pid not in 
(select Pid from PG where Gid = 'G02')

// 关系代数 （4）
Π Pi Π Gid,Pid(PG) ÷ div ÷ Π Pi Π Gid ( σ sigma σ type=‘益智类’(GAME))

// (5) sql
select distinct Gname from GAME
natural join PG
natural join PERSON
where PERSON.age between 15 and 25


// (6) sql
insert into PG (Pid, Gid)
values (P01, G02)


// (7) sql 做更新操作 假设新版本是2，GID = G01
update from GAME set version = 2
where Gid = 'G01'

奔腾 2016级

// (1)
create table books_rank (
BRID NUMBER(6),
BID CHAR(4),
BNAME VARCHAR(30) NOT NULL,
QTY_NO NUMBER(6)  NOT NULL,
QTY NUMBER(6,2) NOT NULL,
BUY_DATE DATE NOT NULL,
AVG_SCORE NUMBER(3,2),
PRIMARY KEY (BRID),
FOREIGN KEY (BID) REFERENCES books
); 


// (2)
 select distinct CID from orders where QTY between 1000 and 2000


// (3) 这个只能保证没买东西的顾客的购买金额为0
select c.CID, o.DOLLARS from customers as c 
left join orders as o 
on c.CID = o.CID 
// 如果要考虑一个顾客拥有多个订单，可以使用分组求和
select c.CID, sum(o.DOLLARS) as sumDollars 
from customers as c
left join orders as o
on c.CID = o.CID 
group by c.CID


// (4) 这个只能保证没买东西的顾客的购买金额为0
select max(salary) from orders
where orders < 
(select max(salary) from orders)


// (5) 权限的授予 grant <操作列表> on <关系> to <用户> 
grant insert, delete on book_ranks to Tom
// 如果只授予表中某几个属性的权限，可以这样写
grant insert(BNAME), delete on book_ranks to Tom
// 权限的收回 revoke <操作列表> on <关系> from <用户>
revoke insert on book_ranks from Tom

奔腾 2015级《数据库应用》期末考试试题

// (1)
create table users(
uid NUMBER(10) PRIMARY KEY,
uname CHAR(6) NOT NULL,
gender CHAR(1),
province VARCHAR2(10)
PRIMARY KEY (uid)
);


// (2) 这里不能distinct 因为有可能一个人对一个歌打分2次
select s.sname, l.score from users as u 
natural join listen_history as l
natural join songs as s
where uname = 'Smith' and YEAR(l.date_listen) = YEAR(NOW())
// 还有个问题是 数值为空时 要写成 0
// 一种方案是 但是貌似不能用 书上没写过这种
select s.sname, ifnull(l.score, 0) from users as u 
natural join listen_history as l
natural join songs as s
where uname = 'Smith' and YEAR(l.date_listen) = YEAR(NOW())
// 另外一种方案 case when 结构
select s.sname, l.score,
case 
	when l.score is null then 0
	else l.score
end
from users as u 
natural join listen_history as l
natural join songs as s
where uname = 'Smith' and YEAR(l.date_listen) = YEAR(NOW())


// (3)
select count(distinct u.uid) from users as u
natural join listen_history as l
natural join songs as s
where s.sname = 'XXX' and u.province = 'YYY'


// (4)
grant insert, delete on user_links to Tom


// (5)
select sid from songs order by avg_score desc

奔腾 2014级 《数据库应用》期末考试试题

// (1)这里没单独查名字是因为考虑到重名的客户没办法区分
select CID, CNAME from Customers where CNAME like 'A%'


// (2)
select * from Customers where ADDRESS is null order by CNAME desc


// (3)
select * from Sales where SALARY is not null and SALARY > 3000


// (4) 因为有多人重名，故使用all
select SID, SNAME from Sales 
where SALARY > all (
select SALARY from Sales
where SNAME = 'Smith')


// (5)
select CID from Orders where DATE_BUY 
between date(now())-interval 7 day and date(now())
group by CID
having count(*) >= 2


// (6)
select DOLLARS from Orders natural join 
(select PID, max(p1.QTY) from Products_rank as p1
where p1.QTY < (select max(p2.QTY) from Products_rank as p2))
// 或者
select DOLLARS from Orders natural join
(select PID, max(QTY) from 
(select PID, QTY from Products_rank
where QTY not in
(select max(QTY) from Products_rank)))

奔腾 2015级 《数据库原理》考试试题A

(1) 关系代数
Π Pi ΠMname,Maddress ( σ sigma σMRnum>=50(MD))

(2) 关系代数
Π Pi ΠMname,Maddress(MD Join σ sigma σMid<>256 (( Π Pi ΠMid,Pid(MP) ÷ div ÷ Π Pi ΠPid( σ sigma σMid=256(MD Join MP)))))

// (3)
select Pname, Mname from MD
natural join MP
natural join SP
where MP.Pnum < 10


// (4)
select MD.Mname, sum(MP.Pnum * SP.Price) as totalPrice
from MD natural join MP natural join SP
group by MD.Mid


// (5)
update from MP set Pnum = Pnum + 2000
where Pid in 
(select Pid from SP where Pname = '泉阳泉')
and Mid = 256