borrower:
create table borrower(
姓名 char[20] not null,
系名 char[10],
班级 char[10])
create table loans(
借书证号 char[5],
图书登记号 char[6],
结束日期 DATE,
primary key(借书证号,图书登记号),
foreign key(借书证号) reference borrower(借书证号),
foreign key(图书登记号 reference books(图书登记号))
create table books(
索书号 char[10],
书名 char[20],
图书登记号 char[6] primary key,
出版社 char[20],
价格 smallint))
(1)
select borrower借书证号,姓名,系名,temptotal as 借书数量
from borrower,(select 借书证号,count(图书登记号) as total
from loans group by 借书证号
where tatal>5 as temp(借书证号,total))
(2)
select borrower姓名,系名,书名,结束日期
from borrower,loans,books
where borrower借书证号=loans借书证号
and books图书登记号=loans图书登记号
and 书名 in(selcet 书名
from borrower,loans,books
where borrower借书证号=loans借书证号
and books图书登记号=loans图书登记号
and 姓名='赵垒')
(3)
create view SB
as
select borrower借书证号,姓名,班级,books图书登记号,书名,出版社
,借书日期
from borrower,book,loans
where borrower借书证号=loans借书证号
and books图书登记号=loans图书登记号
and 系名='信息系'
1
create table student(
sno char[10] primary key,
sname char[20] not null,
ssex char[2],
sage smallint check( sage between 16 and 30),
sdept char[4])
create table course(
cno char[10] primary key,
cname char[20] not null,
cteacher char[20])
create table sc(
sno char[10],
cno char[10],
grade smallint check(grade is null or grade between 0 and
100),
primary key(sno,cno)
foreign key sno reference student(sno),
foreign key cno reference course(cno))
2
insert into student values('102','李四','男',16,'数学')
下同
insert into course values('203',' *** 作系统','程羽')
下同
insert into sc values('101','203',82)
下同
3
(1) select cname,grade
from sc,student,course
where studentsno=scsno and coursecno=sccno
and sname='张三'
(2) select sname from student
where sno in(select sno from sc x
where not exists(
select from sc y
where xsno=ysno
and ygrade<60))
(3) select cname,sname,grade
from student,course sc,(select cno,max(grade) from sc group by cno
as temp(cno,max))
where studentsno=scsno and coursecno=sccno
and grade=max and coursecno=tempcno
4
delete from sc where grade<60
5
update sc
set grade=(select avg(grade) from sc where cno='203')
where sno=105
仅供参考
---1) 创建一张学生表,包含以下信息,学号,姓名,年龄,性别,家庭住址,联系电话
CREATE TABLE student
(
[id] [int] IDENTITY(1,1) NOT NULL,
[student_id] [nvarchar](50) NULL,
[studen_name] [nvarchar](50) NULL,
[age] [int] NULL ,
[sex] [nvarchar](5) NULL,
[address] [nvarchar](200) NULL,
[tel] [nvarchar](20) NULL
)
--2) 修改学生表的结构,添加一列信息,学历 education
alter table student add education nvarchar(10) NULL
--3) 修改学生表的结构,删除一列信息,家庭住址
alter table student drop column address
--5) 修改学生表的数据,将电话号码以11开头的学员的学历改为“大专”
update student set education='大专' where tel like '11%'
--6) 删除学生表的数据,姓名以C开头,性别为‘男’的记录删除
delete student where studen_name like 'C%' and sex='男'
--7) 查询学生表的数据,将所有年龄小于22岁的,学历为“大专”的,学生的姓名和学号示出来
select studen_name,student_id from student
where age<12 and education='大专'
--8) 查询学生表的数据,查询所有信息,列出前25%的记录
select TOP 25 PERCENT from student
--9) 查询出所有学生的姓名,性别,年龄降序排列
select studen_name,sex,age from studen order by age desc
--10) 按照性别分组查询所有的平均年龄
select avg(age) as age from studen group by sex
13
23
33
43
51
62
73
82
92
101
114
121
133
142
154
162
174
183
192
204
以上就是关于跪解以下2道数据库系统原理上机试题全部的内容,包括:跪解以下2道数据库系统原理上机试题、sql数据库一道试题帮忙做做、急!!!~~~高手帮忙做下 数据库系统概论 的测试题等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)