数据库笔试题目

[数据结构类笔试题目]第一篇笔试题目Intel今年笔试题●第一道是一个编译器优化的题目。条件大致说在ZF为0或者不为0的情况下，分别有两条移位指令可以移进去。然后出了两个小题，要你优化。●第二道...+阅读

1.增加删除因为两个表的USERNAME字段是应该是设置了外键关联，这样不仅结构清晰，而且还确保了数据的完整性。看题目的意思是USERS跟PRODUCT的对应关系应该是1:n，一个USERS可以生产许多个PRODUCT。 INSERT INTO PRODUCT(PRODUCT.PRODUCTNAME,PRODUCT.PRODUCTCOUNT,PRODUCT.USERNAME) VALUES (char,int,char)；插入产品 INSERT INTO USERS (USERNAME) VALUES (char)；插入用户删除就省略了。至于如何确定是否存在，你直接在插入产品前搜索USERS表就好了啊 4.存储过程 CREATE PROC proc_AddUserAccount //定义一个过程名 pName nvarchar(200),pCount intAS,pUser nvarchar(200)//3个变量 BEGIN INSERT INTO PRODUCT(PRODUCT.PRODUCTNAME,PRODUCT.PRODUCTCOUNT,PRODUCT.USERNAME) VALUES (pName,pCount ,pUser)；//添加记录 RETURN ID //返回ID END...

有关数据库的笔试题

确实，这个问题得用点时间。我先给你开个头，有空给你补上：问题一：（需要说明下表和字段主键就OK了）需要建三个表：具体字段可以自己再加程序员信息表、部门表、上级部门表 ************************************** CREATE TABLE [Programmer] ( [ProgrammerCode] [int] NOT NULL , [ProgrammerName] [char] (10) COLLATE Japanese_CI_AS NULL , [ProgrammerSex] [int] NOT NULL , [BelongBranch] [int] NOT NULL , [other] [char] (10) COLLATE Japanese_CI_AS NULL , CONSTRAINT [PK_Programmer] PRIMARY KEY CLUSTERED ( [ProgrammerCode] ) ON [PRIMARY] ) ON [PRIMARY] GO ***************************************** CREATE TABLE [Branch] ( [BranchCode] [int] NOT NULL , [BranchName] [char] (10) COLLATE Japanese_CI_AS NOT NULL , [BelongTo] [int] NOT NULL , [OtherInformation] [char] (10) COLLATE Japanese_CI_AS NULL , CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchCode] ) ON [PRIMARY] ) ON [PRIMARY] GO *************************************** CREATE TABLE [LeaderBranch] ( [LeaderBranch] [int] NOT NULL , [LeaderBranchName] [char] (10) COLLATE Japanese_CI_AS NOT NULL , [OtherInformation] [char] (10) COLLATE Japanese_CI_AS NULL , CONSTRAINT [PK_LeaderBranch] PRIMARY KEY CLUSTERED ( [LeaderBranch] ) ON [PRIMARY] ) ON [PRIMARY] GO ****************************************************** 问题2：各部门人数查询： SELECT BranchName,count (*) AS 程序员人数 FROM Programmers p INNER JOIN Branch b ON p.BelongBranch = b.BranchCode GROUP BY BranchName 上级部门人数查询： SELECT LeaderBranchName,count (*) AS 程序员人数 FROM Programmers p INNER JOIN Branch b ON p.BelongBranch = b.BranchCode INNER JOIN LeaderBranch l ON b.BelongTo = l.LeaderBranch GROUP BY l.LeaderBranchName —————————————————————————————— 问题三有空再给你答，呵呵~~

数据库期末考试题求答案

--1 create database 学生选课系统 on primary (name = 学生选课系统_data,, size=8, maxsize=100, filegrowth=20%) log on (name =学生选课系统_log, filename = 'e:\ee\学生选课系统.ldf') --2 create table 学生（学号 char(10) primary key，姓名 char(20)，性别 char(2) check （性别 in（'男'，'女'）），年龄 int default 20，所在系 char(20)) --3 select 学号，年龄 from 学生 where 所在系='物理系' --4 select 学号，成绩 where 课程号='2' order by 成绩 asc，学号 desc --5 select 学生.学号学号，姓名，选修的课程名，成绩 from 学生，课程，选课 where 学生.学号=选课.学号 and 课程.课程号=选课.课程号 --6 insert into 学生（学号，姓名，所在系） values('96002'，'张三'，'英语系') --7 update 学生 set 年龄=年龄+3 --8 delete from 学生 where 所在系='化学系' --9 create view 英语系学生 as select 学号，性别，年龄 from 学生 where 所在系='英语系' --10 create unique index IX_课程号 on 课程（课程号） --11 create trigger trig1 on 学生 for delete as declare SID char(10) select SID=学号 from deleted begin delete from 选课 where 学号=SID end

SQL数据库试题求解

------------------------------------------------------ create table students(st_id varchar(20),st_name varchar(50),sex varchar(10)) insert into students(st_id,st_name,sex) select 'st001'，'张杰'， '男' union all select 'st002'， '公孙燕飞' ，'男' union all select 'st003'， '王楠'， '女' union all select 'st004'， '王伟'， '男' union all select 'st005'，'李燕纹'， '女' union all select 'st006'， '孙武' ，'男' select * from students create table teachers(t_id varchar(20),t_name varchar(50),t_lesson varchar(50)) insert into teachers select 't001'， '张老师' ，'数学' union all select 't002'， '李老师'， '英语' delete from results create table results(r_id varchar(20),r_fenshu int,r_stid varchar(50),r_tid varchar(50)) insert into results select 'r001','90', 'st001', 't002' union all select 'r002', '68', 'st005', 't001' union all select 'r003', '92', 'st003' ,'t001' union all select 'r004', '82', 'st006', 't002' union all select 'r005', '70', 'st002', 't002' union all select 'r006', '86', 'st002', 't001' union all select 'r007', '57', 'st003', 't002' union all select 'r008', '76', 'st006', 't001' union all select 'r009', '55', 'st001', 't001' union all select 'r010', '77', 'st004', 't002' union all select 'r011', '58', 'st005', 't002' ---------------------------------------------------------- 1. select st_id from students where st_name = '王伟' 2.select st_id,st_name from students where st_name like '__燕%' 3 select st_name,len(st_name) as 名字长度 from students where sex ='男' 4 select min(r_fenshu) as 最低分数 from teachers t inner join results r on t.t_id =r.r_tid where t_lesson ='数学' --这个是不考虑成绩中有null值的 5 select s.st_id as 学生编号，r_fenshu as分数，r_tid as 课目号 from students s inner join results r on s.st_id =r.r_stid where s.sex='女' --如果还要课目的名称的话请用下面的 select s.st_id as 学生编号，r.r_fenshu as 分数，r.r_tid as 课目号，t.t_lesson as 课目名称 from students s inner join results r on s.st_id =r.r_stid inner join teachers t on r.r_tid = t.t_id where s.sex='女' 6 select avg(r.r_fenshu) from results r inner join teachers t on r.r_tid = t.t_id where t.t_lesson='英语' 7.select * from students s inner join results r on s.st_id =r.r_stid inner join teachers t on r.r_tid = t.t_id where s.st_id in (select top 2 st_id from students order by st_id desc) order by s.st_id desc 8 select sum(r.r_fenshu) as 总分 from results r inner join students s on r.r_stid =s.st_id where s.st_name = '王楠' 9.select distinct s.st_id,s.st_name from students s inner join results r on s.st_id = r.r_stid where st_id not in (select r_stid from results where r_fenshu<60) and st_id not in (select r_stid from results where r_fenshu >=90) 10 update results set r_fenshu = r_fenshu + 10 --如果分数不可能大于100请用这句 set r_fenshu = case when r_fenshu + 10 <=100 then r_fenshu + 10 else 100 end where r_stid in (select st_id from students where) 1 进阶题 select t.t_name,count(*) from students s,teachers t,results r where r.r_tid = t.t_id and s.st_id =r.r_stid and r.r_fenshu >= 60 and t.t_id in (select t_id from teachers where t_lesson='数学' ) --and t_lesson='数学' group by t.t_name 2 select top 1 sum(r_fenshu) as 总分，t.t_lesson,t_id,t_name from results r,teachers t where r.r_tid = t.t_id group by t.t_lesson,t_id,t_name order by 总分 desc 3. delete from results where r_stid in (select r_stid from results group by r_stid having count(r_tid) = 1) 1 选做题 select d.name from sysobjects d where d.xtype='U' 2.select top 5 * from students order by newid()