有一个“学生－课程”数据库，数据库中包括三个表： 有一个[学生课程]数据库，数据库中包括三个表：
\u6709\u4e00\u4e2a\u201c\u5b66\u751f\uff0d\u8bfe\u7a0b\u201d\u6570\u636e\u5e93\uff0c\u6570\u636e\u5e93\u4e2d\u5305\u62ec\u4e09\u4e2a\u8868\uff1a\u4ee5oracle \u4e3a\u4f8b\uff1a1\u3001create table student( Sno number(10) primary key, Sname varchar2(20), Ssex varchar2(2), Sage number(10), Sdept varchar2(20));2\u3001SELECT * FROM student FROM Sdept='\u8ba1\u7b97\u673a' ORDER BY Sno DESC3\u3001SELECT DISTINCT student.Sno,student.Sname,student.Ssex FROM student,Course,SC where student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Course.Ccredit=5 AND SC.Grade > 60 4\u3001create or replace trigger my_trig after delete on student for each row begin delete from SC where Sno=:student.Sno; end;
1.\u81ea\u5df1\u624b\u52a8\u5efa\u5427\uff01
2.select DISTINCT(Sno) from SG where Grade<60
3.update Student set Sage='22' where Sno='05001'
4.select cast(Grade) from Gourse where Cno=1
5.
CREATE PROCEDURE getDetailByName
@Sname NVARCHAR(10),
@intCount int ouput
AS
BEGIN
SELECT @intTotalCount=count(*) FROM Student WHERE Sname=@Sname
if @intCount =1
BEGIN
select * from Student where Sname=@Sname
END
ELSE
BEGIN
print '\u4e0d\u5b58\u5728\u6b64\u5b66\u751f'
END
END
GO
6.select Sname,Ssex,Sage from where Ssex=N'\u7537' and Sdept =N'\u8ba1\u7b97\u673a\u7cfb' and Sname like '\u8d75%'
7.
8.INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('05020','\u4e01\u8389','\u5973','17\u5c81','\u8ba1\u7b97\u673a')
create database MyDB
on(
name='Student－SC',
filename='d:\自己学号\Student－SC.mdf',
size=1,filegrowth=10%)
log on
(name='Student－SClog',
filename='d:\自己学号\Student－SClog.ldf',
size=1,filegrowth=10%)
go
2、在“学生－课程”数据库创建“学生”表，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号设为主键约束，性别设置检查性约束。
use Student－SC
create table Student
(Sno char(5) primary key,
Sname varchar(20),
Ssex varchar(2),
Sage tinyint,
Sdept varchar(30),
check(Ssex in('男','女')))
go
3、查询“学生”表中全体学生的学号与姓名
select Sno,Sname from Student
4、查询年龄在20至23岁之间的学生的姓名、所在系和年龄
select Ssex,Sdept,Sage from Student where Sage between 20 and 23
5、 查所有姓刘的学生的姓名、学号和性别
select Sname,Sno,Ssex from Student where Sname like '刘%'
6、 查询“学生选课”表中成绩最高和成绩最低的记录，要求显示学号（Sno）、课程号（Cno）、成绩（Grade）三个属性
select Sno,Cno,Grade from SC group by Sno,Cno having max(Grade) or min(Grade)
7、使用内部联接查询并显示所有选修课程的同学的学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）、课程号（Cno）、成绩（Grade）属性
select SC.Sno,Student.Sname,Student.Ssex,Student.Sage,Student.Sdept,SC.Cno,SC.Grade from SC inner join Student on SC.Sno=Student.Sno
8、向“学生”表中插入如下记录：学号：’04160’、姓名：’王燕’、性别 :’女’、年龄:22、所在系: ’计算机科学系’
insert into Student values('04160','王燕','女',22,'计算机科学系')
9、将计算机科学系全体学生的成绩置零
update SC set Grade=0 where exists(select Sno,Sdept from Student where Student.Sno=SC.Sno and Student.Sdept='计算机科学系')
10、在“学生”表中，删除学号为’04160’同学的记录
delete from Student where Sno='04160'
以oracle 为例：
1、create table student( Sno number(10) primary key,
Sname varchar2(20),
Ssex varchar2(2),
Sage number(10),
Sdept varchar2(20)
);
2、SELECT * FROM student FROM Sdept='计算机' ORDER BY Sno DESC
3、SELECT DISTINCT student.Sno,student.Sname,student.Ssex
FROM student,Course,SC
where student.Sno=SC.Sno AND Course.Cno=SC.Cno
AND Course.Ccredit=5 AND SC.Grade > 60
4、create or replace trigger my_trig
after delete on student
for each row
begin
delete from SC where Sno=:student.Sno;
end;
CREATE TABLE [课程]
(
Cno CHAR(4) PRIMARY KEY,
Cname VARCHAR(20),
Cpno VARCHAR(20),
Ccredit INT
)
CREATE TABLE [学生选课]
(
Sno CHAR(5),
Cno CHAR(4),
Grade INT
CONSTRAINT PK_Selectcourse PRIMARY KEY(Sno, Cno)
)
1、
CREATE DATABASE [学生-课程]
ON
( NAME = stud_course_dat,
FILENAME = 'D:\200101001\stud_course_dat.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10% )
LOG ON
( NAME = stud_course_log,
FILENAME = 'D:\200101001\stud_course_log.ldf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
2、
CREATE TABLE [学生]
(
Sno CHAR(5),
Sname VARCHAR(20),
Ssex CHAR(2),
Sage INT,
Sdept VARCHAR(20)
CONSTRAINT PK_STUDENT PRIMARY KEY(Sno),
CONSTRAINT CHK_SEX CHECK(Ssex IN ('男','女'))
)
3、
SELECT Sno, Sname
FROM 学生
4、
SELECT Sno, Sdept, Sage
FROM 学生
WHERE Sage BETWEEN 20 AND 23
5、
SELECT Sname, Sno, Ssex
FROM 学生
WHERE Sname LIKE '刘%'
6、
SELECT Sno, Cno, MAX(Grade)
FROM 学生选课
GROUP BY Sno, Cno
UNION
SELECT Sno, Cno, MIN(Grade)
FROM 学生选课
GROUP BY Sno, Cno
7、
SELECT Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM 学生 S
JOIN 学生选课 SC
ON S.Sno = SC.Sno
8、
INSERT INTO 学生 VALUES('04160', '王燕', '女', 22, '计算机科学系')
9、
UPDATE 学生选课
SET Grade = 0
WHERE Sno = (SELECT Sno FROM 学生 WHERE Sdept = '计算机科学系')
10、
DELETE 学生
WHERE Sno = '04160'
1.自己手动建吧！
2.select DISTINCT(Sno) from SG where Grade<60
3.update Student set Sage='22' where Sno='05001'
4.select cast(Grade) from Gourse where Cno=1
5.
CREATE PROCEDURE getDetailByName
@Sname NVARCHAR(10),
@intCount int ouput
AS
BEGIN
SELECT @intTotalCount=count(*) FROM Student WHERE Sname=@Sname
if @intCount =1
BEGIN
select * from Student where Sname=@Sname
END
ELSE
BEGIN
print '不存在此学生'
END
END
GO
6.select Sname,Ssex,Sage from where Ssex=N'男' and Sdept =N'计算机系' and Sname like '赵%'
7.
8.INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('05020','丁莉','女','17岁','计算机')
用ACCESS处理
绛旓細select 瀛﹀彿锛屽鍚 from 瀛︾敓 where 濮撳悕 in( select 濮撳悕 from 瀛︾敓 group by 濮撳悕 having count(*)>1 )update 鎴愮哗 set 鎴愮哗=0 where CNO=1 濡傛灉/涓嶆槸闄ゆ硶鐨勮瘽 鍙槸瀛楃涓 锛堥櫎娉曟殏鏃舵湁闂锛変笅鐝簡 鏄庡ぉ鍐嶆潵鍐 select C.璇剧▼鍙,D.璇剧▼鍚,D.鎴愮哗 from COURSE C,(select CONVERT(VARCHAR(...
绛旓細10.鏌ヨ濮撳悕涓浜屼釜瀛椾负'闃'瀛楃殑瀛︾敓鐨勫鍚 select sname from student where sname like '_闃%'11.鏌ヨ淇℃伅宸ョ▼绯诲勾榫勫湪20宀佷互涓嬬殑瀛︾敓鐨勫鍚 select sname from student where sage<20 and sdept='淇℃伅宸ョ▼绯'12.鏌ヨ閫変慨浜3鍙璇剧▼鐨勫鐢熺殑瀛﹀彿鍙婂叾鎴愮哗锛屾煡璇㈢粨鏋滄寜鍒嗘暟鐨勯檷搴忔帓鍒 select sno,...
绛旓細鐢 SC 瀛﹀彿 璇惧彿 鎴愮哗 9801 201 89 9801 202 67 9801 203 77 9802 201 82 9802 202 32 9803 201 85 9803 201 98 9803 203 45 鈹娿 鈹娿 鈹 9850 203 69 C 璇惧彿 璇惧悕 浠昏鑰佸笀 201 鏁版嵁缁撴瀯 鐜嬪崕 202 璁＄畻鏂规硶 鍒樻収 203 鏁版嵁搴 鐜嬪崕 鐒跺悗瀵煎叆access 鍐嶈缃甧r-win妯″紡銆傚氨琛屼簡 ...
绛旓細鍥1 瀛︾敓-璇剧▼鏁版嵁搴鍐欏嚭瀹屾垚涓嬪垪瑕佹眰鐨凷QL璇彞:1)鍐欏嚭鍒涘缓閫夎SC琛ㄧ殑SQL璇彞(鎻愮ず:鍚勫睘鎬у垪鐨勬暟鎹被鍨嬩笌瀹藉害銆佸叧閿瓧鍒楃殑閫夊彇瑕佸悎鐞,寤虹珛鐩稿簲鐨勭害鏉)2)鏌ヨ鎵鏈夊勾榫勫湪20宀佷互涓嬬殑瀛︾敓濮撳悕涓庡勾榫勩3)缁熻鐢风敓鍜屽コ鐢熺殑浜烘暟銆4)鏌ヨ姣忎釜瀛︾敓鍙婂叾閫変慨璇剧▼鐨勮绋嬪彿,璇剧▼鍚嶃5)鍒犻櫎瀛﹀彿涓衡95001鈥滅殑瀛︾敓鐨勯夎璁板綍銆
绛旓細鈶 create table Student( Sno int not null PRIMARY KEY ,Sname string Unique, Ssex string ,Sage integer, Sdept string )鈶 SELECT SG.Sno, SG.Grade FROM SG WHERE (((SG.Cno)=2))鈶 INSERT INTO sg(Sno,Cno,Cname,Grade锛塿alves(鈥2012314鈥,鈥2鈥, 鈥鏁版嵁搴绠＄悊鈥,90)鈶 UPDATE...
绛旓細SNO where SC.GRADE >95 5>select Student.sname ,SC.GRADE from Student inner join SC on Stuent.SNO=SC.SNO where SC.CNO=(select CNO from C where CNAME='ACCESS') order by SC.GREDE dec 6> select sno ,sname ,sdept from Student where sname like '鐜%'鏈夐敊鐨勫湴鏂硅鎸囩偣锛
绛旓細涓 瀛︾敓 鈥 璇剧▼鏁版嵁搴1 鏌ヨ 7鍙疯绋嬫病鏈夎冭瘯鎴愮哗鐨勫鐢熷鍙穝elect sno from sc where cno=鈥7鈥 and grade is not null2 鏌ヨ 7鍙疯绋嬫垚缁╁湪90鍒嗕互涓婃垨60鍒嗕互涓嬬殑瀛︾敓瀛﹀彿select sno from sc where grade>90 or grade<603 鏌ヨ璇剧▼鍚嶄互鈥滄暟鎹濅袱涓瓧寮澶寸殑鎵鏈夎绋嬬殑璇剧▼鍙峰拰璇剧▼鍚嶃係elect cno,...
绛旓細寤3涓〃锛氥愬鐢熻〃銆戝鐢烮D,濮撳悕...銆愯绋嬭〃銆戣绋婭D,璇剧▼鍚嶇О...銆愰夎琛ㄣ戝鐢烮D,璇剧▼ID 瀛︾敓閫夎锛屽疄闄呬笂灏辨槸鍦ㄣ愰夎琛ㄣ戜腑娣诲姞璁板綍 缁熻鐨勬椂鍊欒繖鏍锋潵鍐 select [璇剧▼鍚嶇О],count(*) as [閫夎瀛︾敓鏁]from [璇剧▼琛╙ inner join [閫夎琛╙ on [璇剧▼琛.璇剧▼ID]=[閫夎琛.璇剧▼ID]group by...
绛旓細鎵撳紑鏌ヨ鍒嗘瀽鍣紝杈撳叆浠ヤ笅浠ｇ爜锛歝reate database student on (name=student,filename='d:\stu_data.mdf',size=3,maxsize=500,filegrowth=10 )log on (name=stu_log,filename='d:\stu_log.ldf',size=3,maxsize=unlimited,filegrowth=1 )杩欐牱锛屾暟鎹簱灏卞缓濂戒簡锛屼笅鍥 ...
绛旓細鏈変竴涓鐢熸暟鎹簱,鏈変互涓嬪叧绯绘ā寮忔瀯鎴:瀛︾敓(瀛﹀彿,濮撳悕,鎬у埆,骞撮緞,鎵鍦ㄧ郴)閫変慨(瀛﹀彿,璇剧▼鍙,鎴愮哗)璇剧▼(璇剧▼鍙,璇剧▼鍚,瀛﹀垎)璇曠敤sql璇█瀹屾垚浠ヤ笅鎿嶄綔:鏌ヨ閫変慨浜3鍙疯绋嬬殑瀛︾敓瀛﹀彿鍙婂叾鎴愮哗,缁撴灉鎸夊垎鏁伴檷搴忔帓鍒椼傛煡璇㈠悇涓绋嬪彿涓... 灞曞紑 22585146 | 娴忚3120 娆 |涓炬姤 鎴戞湁鏇村ソ鐨勭瓟妗堟帹鑽...
