数据库上机作业(英文)
mysql数据库实验上机作业
以下是一个MySQL数据库实验上机作业的示例：1. 创建一个名为“students”的数据库，并选择该数据库。
```sqlCREATE DATABASE students;USE students;```2. 创建一个名为“students_table”的表，包含以下列：id（主键，自动递增），name（文本），age（整数），gender（文本），major （文本）。
```sqlCREATE TABLE students_table (id INT AUTO_INCREMENT PRIMARY KEY,name V ARCHAR(100),age INT,gender V ARCHAR(10),major V ARCHAR(100));```3. 向“students_table”表中插入一些数据。
```sqlINSERT INTO students_table (name, age, gender, major) V ALUES ('John', 20, 'Male', 'Computer Science'),('Jane', 22, 'Female', 'Mathematics'),('Bob', 21, 'Male', 'Physics');```4. 查询所有学生的信息。
```sqlSELECT * FROM students_table;```5. 查询所有年龄大于等于20岁的学生信息。
```sqlSELECT * FROM students_table WHERE age >= 20;```6. 查询所有计算机科学专业的女学生信息。
```sqlSELECT * FROM students_table WHERE major = 'Computer Science' AND gender = 'Female';```7. 更新表中某个学生的信息。
实用数据库上机作业(第2次)
实用数据库上机作业
建立二张数据表，一张是员工信息表(Employee)，一张是部门信息表(Department)。
Employee包括：员工编号(EmployeeId, char(6)、员工姓名(EmployeeName, varchar(20))、员工性别(EmployeeSex, nchar(1))、雇佣时间(HireDate, smalldatetime)、基本工资(BaseWage, money)、部门编号(DepartmentId, char(3)等Department包括：部门编号(DepartmentId, char(3)、部门名称(DepartmentName, varchar(20))、负责人(Principal, varchar(20))等
注：以上表中字段名最好使用英文或拼音。
●选取所有部门信息表中的信息，返回前10行数据。
●查询所有员工的员工姓名、雇佣时间和基本工资。
●查询基本工资介于2000到3000之间的员工姓名，输出结果按照基本工资从
低到高排序。
●查询员工姓名中有“海”字且雇佣时间晚于2000年1月1日的所有员工的
基本工资。
●查询有多个员工基本工资不低于3000的部门编号。
●查询各个部门中平均的工资额（使用统计函数Avg）
●查询所有员工的姓名，同时列出员工所在部门名称，部门负责人。
（使用连
接查询）。
数据库上机(实验一)作业
在订单数据库OrderDB中，完成如下的查询：（1）查询员工的姓名、职务和薪水。
select employeeName 员工姓名,headShip 职务,salary 薪水from Employee（2）查询姓名中含有“有限”的客户名称和所在地。
select customerName 客户名称,address 所在地from customerwhere customerName like'%有限%'（3）查询出姓“张”并且姓名的最后一个字为“梅”的员工。
select employeeNo 编号,sex 性别,birthday 出生日期,address 地址,telephone 联系电话,employeeName 员工姓名,headShip 职务,salary 薪水from Employeewhere employeeName like'张%梅'（4）查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职务、住址、出生日期和性别。
如果出生日期为空，则显示“不祥”，否则按格式“yyyy—mm—dd”显示，性别用“男”和“女”显示。
select employeeName 员工姓名,department 所属部门,headShip 职务,address 地址,case sexwhen'F'then'女'when'M'then'男'end性别,isnull(convert(char(10),birthday,120),'不详')出生日期from Employeewhere sex like'F'and address like'上海%'or sex like'F'and address like'南昌%'（5）查询出职务为“职员”或职务为“科长”的女员工的信息。
上机练习题英文
上机练习题英文1. IntroductionIn today's digital age, computer literacy has become an essential skill for individuals of all ages. The ability to navigate and utilize computer systems efficiently is crucial in various aspects of life, from education to professional careers. As such, practicing computer exercises is a beneficial way to enhance one's computer skills. In this article, we will explore the importance of computer exercises and discuss how they can contribute to improving overall computer proficiency.2. Enhancing Technical SkillsComputer exercises provide an opportunity to enhance technical skills. By actively engaging in hands-on practice, individuals can become familiar with various software applications, operating systems, and tools. Through repeated exposure and experimentation, users can develop a deeper understanding of different computer concepts, such as file management, word processing, spreadsheet manipulation, and graphic design. With regular practice, individuals can gain proficiency in using these tools, potentially leading to increased efficiency and productivity in their personal and professional lives.3. Improving Problem-Solving AbilitiesComputer exercises often require individuals to solve problems through logical thinking and analytical skills. From debugging a programming code to troubleshooting software issues, these exercises challenge the mind and encourage critical thinking. By regularly engaging in problem-solvingactivities, individuals can sharpen their analytical abilities, develop strategic thinking, and cultivate a mindset that is adept at tackling complex computer-related challenges. These problem-solving skills can be applied not only in the field of technology but also in various domains of life, enhancing overall cognitive abilities.4. Promoting Creativity and InnovationComputer exercises can serve as a platform for individuals to unleash their creativity and foster innovation. Various software applications provide tools for graphic design, video editing, and programming, among others. Through active participation in computer exercises, individuals can explore different features, experiment with customization options, and create unique digital content. This creative process not only enhances technical skills but also encourages individuals to think outside the box, fostering a spirit of innovation and imagination.5. Enhancing Learning OpportunitiesComputer exercises can complement traditional learning methods and provide additional opportunities for knowledge acquisition. Various online platforms offer interactive tutorials, quizzes, and coding challenges that cater to learners of different levels. By engaging with these exercises, individuals can reinforce what they have learned through formal education or explore new areas of interest. Computer exercises can also facilitate self-paced learning, allowing individuals to tailor their learning experience to their specific needs and interests.6. Building Confidence and IndependenceRegular practice of computer exercises contributes to building confidence and independence in using computer systems. As individuals become more familiar with different applications and tools, their self-assurance in operating computers grows. This newfound confidence is not only relevant in personal endeavors but also in professional settings where computer literacy is often a prerequisite. Furthermore, by independently seeking solutions to problems encountered during computer exercises, individuals develop problem-solving skills and become resourceful in resolving technical issues on their own.7. ConclusionComputer exercises play a vital role in enhancing computer literacy and skills. Through hands-on practice, individuals can strengthen their technical abilities, improve problem-solving skills, foster creativity and innovation, enhance learning opportunities, and build confidence and independence in using computer systems. As technology continues to evolve, dedicating time to computer exercises becomes increasingly essential in keeping pace with the digital landscape and maximizing the advantages that technology offers.。
数据库上机作业
6.建表co，插入课程学分平均分在5分以上的系的系号和平均分。
答案：
/*1 select sum(grade),avg(grade)
from sc
where cno='e001'*/
/*2 select dno,count(*)
from class
8.将系表中的“数信”系删除。
/* selecBiblioteka * from dept delete from dept
where dname= 'ma'*/
9.建立一个新表，存放成绩在86分以上的学生的学号，课程号和成绩。
/*create table abc(Sno char(10),Cno char(10), grade int)
/*7 select sname,avg(grade) from sc,student
where grade>60 and sc.sno=student.sno
group by sname */
where clnum>2*/
/*5 select sno,sum(ccre)
from sc,couse
where o=o and grade>=0 or ccre<6
group by sno*/
/*6 create table co(cno char(10),avge int)
3.将学分统一降低1分。
/* select * from couse
update couse
set ccre=ccre-1*/
4.为每个班级增加5名同学。
大连理工大学软件学院数据库上机题目+答案(最新)
8. Display the IDs and names of all students who have never registered for a course
group by S.id
select course.course_id,title,AVG(salary) as avg_sal
from course,instructor as T,teaches
where course.dept_name='内功学院'
and T.dept_name='内功学院'
the current student.
4. Find students who have registered for some but not all courses(PART COURSE, for short)
taught by instructors of department '拳脚学院'. Furthermore, the registration of these
the result, information displayed should involve:
* Identifier of student(i.e. the primary key for student) * Name of student
* Identifier of section(i.e. the primary key for section) * Title of course.
数据库上机作业(学生学籍管理)
上机实验－1Access2003基本操作一、实验目的1．掌握Access2003数据库的构成；2．熟悉Access2003的用户界面。
二、实验内容在Access2003中打开罗斯文示例数据库，结合该数据库来了解Access 数据库的构成，并熟悉Access2003的用户界面。
1．单击“开始”按钮，指向“程序”，然后单击“Microsoft Access”，启动Access2003。
2．在启动对话框中，单击“打开已有文件”下的“罗斯文示例数据库”。
罗斯文示例数据库通常位于“Program Files\Microsoft Office\Office\Samples”文件夹中，文件名为“Northwind.mdb”。
3．在“数据库”窗口中，单击“对象”栏下的“表”，单击“产品”表对象，然后单击“数据库”窗口的工具栏上的“打开”按钮，在“数据表”视图中查看表中的数据记录。
4．从“视图”菜单中选择“设计视图”命令，以切换到设计视图下，查看表中各个字段的定义，例如字段名、数据类型、字段大小等，然后关闭设计视图窗口。
5．在“数据库”窗口中单击“对象”栏下的“查询”，单击“各类产品”查询对象，然后单击“数据库”窗口工具栏上的“打开”按钮，在“数据表”视图下查看运行查询所返回的记录集合。
6．从“视图”菜单中选择“设计视图”命令，以查看创建和修改查询时的用户界面。
7．从“视图”菜单中选择“SQL视图”命令，以查看创建查询时所生成的SQL语句，然后关闭SQL视图窗口。
8．在“数据库”窗口中单击“对象”栏下的“窗体”，单击“产品”窗体对象，然后单击“数据库”窗口工具栏上的“打开”按钮，在窗体视图下查看窗体的运行结果，并单击窗体下方的箭头按钮，在不同记录之间移动。
9．从“视图”菜单中选择“设计视图”命令，以查看设计窗体时的用户界面。
10．从“视图”菜单中选择“数据表视图”命令，以查看存储在基础表中的数据记录，然后关闭数据表视图窗口。
数据库上机作业一
实验一SQL语言及SQL Server数据库管理系统应用操作实验环境：Microsoft SQL Server 2000实验目的：◆熟练使用SQL定义子语言、操纵子语言命令语句；◆熟练SQL Server数据库管理系统图形化界面操作；实验要求：◆按要求完成所有上机题；认真填写实验报告，将相应SQL代码或界面截图填入实验报告；保存、备份相关实验数据。
1-1a 利用SQL语句创建数据库。
创建要求：数据库Employee中包含一个数据库文件Empdat1.mdf和一个日志文件Emplog.ldf。
其中，数据文件大小为10MB，最大为50MB，以5MB速度增长；日志文件大小为5MB，最大为25MB，以5%速度增长。
1-1b 利用SQL语句修改数据库修改要求：增加第二个数据库文件Empdat2.ndf，其中，数据文件大小为5MB，最大为25MB，以2MB速度增长。
1-1c 利用SQL语句删除数据库Employee1-1d 利用SQL Server企业管理器重新创建数据库Employee实验指导：（1）企业管理器——>Microsoft SQL Servers——>SQL Server组——>所在服务器——>数据库（2）在数据库文件夹上右键点击鼠标，选择“新建数据库”，此时打开了“数据库属性对话框”（3）在“名称”文本框中输入“Employee”，再在“数据文件”和“事务日志”选项卡中按要求添加数据文件和日志文件（4）按要求修改Employee数据库的属性1-2 利用SQL创建人员表person、月薪表salary；利用企业管理器创建部门表dept。
要求：按表1-1、表1-2及表1-3中的字段说明创建。
表1-1 person表结构字段名数据类型字段长度允许空否字段说明P_no Char 6 Not Null 工号，主键P_name Varchar 10 Not Null 姓名Sex Char 2 Not Null 性别BirthDate Datetime 8 Null 出生日期Prof Varchar 10 Null 职称Deptno Char 4 Not Null 部门代码，外键(参照dept表)表1-2 Salary表结构字段名数据类型字段长度允许空否字段说明P_no Char 6 Not Null 工号，主键，外键(参照person表) Base Dec 5 Null 基本工资Bonus Dec 5 Null 奖金，要求>50Fact 实发工资=基本工资+奖金Month Int Not Null 月份表1-3 dept表结构字段名数据类型字段长度允许空否字段说明Deptno Char 4 Not Null 部门代码，主键Dname Varchar 10 Not Null 部门名称1-3 利用SQL语句创建视图。
MySQL上机试题
MySQL上机试题1．创建数据库，名称为考生的学号；（10分）2．创建数据表customer（客户）、deposite（存款）、bank（银行），表结构如下：（10分）customer的表结构bank的表结构deposite的表结构3．录入数据如下：（10分）customer的数据如下，注意最后一条记录用你的学号和你的姓名代替bank的数据如下：deposite的数据如下：4．创建存储过程update_salary，更新customer表的salary属性，将salary低于5000的客户的salary 变为原来的2倍. （10分）5．创建视图bank_total，对deposite表进行统计，按银行统计存款总数，显示为b_id,total. （10分）6．创建视图show_deposite,对deposite、customer、bank进行查询，查询条件为location在广州、苏州、济南的客户，存款在300000至500000之间的存款记录，显示客户姓名name、银行名称bank_name、存款金额amount. （10分）7．为customer的name创建普通索引，名称为idx_name.（10分）8．新建表dep_log,数据结构如下：（5分）(1)创建一个触发器名称为insert_trigger, 在deposite插入记录时在dep_log中插入一条记录，将操作operation值为INSERT，并记录银行标识b_id;（5分）(2)在deposite中插入一条记录，客户标识c_id为你的学号，银行标识b_id为B0003,存款日期dep_date为当前系统日期，存款期限type为3，存款金额amount为50000，以验证触发器是否有效。
（5分）9．完成以下题目：(1)在bank中插入一条新记录B0005,交通银行；（5分）(2)创建存储过程copydata,将deposite表存款流水号d_id未在dep_log中记录的农业银行B0004的全部存款记录逐一复制到新建的交通银行，客户标识c_id不变，银行标识为B0005，存款期限type、存款金额amount不变，存款日期为当前系统日期。
MySQL查询上机作业和答案
MySQL查询上机作业和答案MySQL作业create table Student--3rd再次执行(Sno int primary key not null,--学号主键Sname varchar(50) not null,--学生姓名Ssex varchar(50) not null,--学生性别Sbirthday datetime,--出生年月Class int--班级)truncate table Student--清空表格insert into Student values(108,'曾华','男','1977-09-01',95033) insert into Student values(105,'匡明','男','1975-10-02',95031) insert into Student values(107,'王丽','女','1976-01-23',95033) insert into Student values(101,'李军','男','1976-02-20',95033) insert into Student values(109,'王芳','女','1975-02-10',95031) insert into Student values(103,'陆君','男','1974-06-03',95031) create table Course--2nd其次执行(Cno char(5) primary key not null,--课程号主键Cname Varchar(10)not null,--课程名称Tno Varchar(50) not null--教工编号（外码）foreign key(Tno)references Teacher (Tno),)insert into Course values('3-105','计算机导论','825')insert into Course values('3-245','操作系统','804')insert into Course values('6-166','数字电路','856')insert into Course values('9-888','高等数学','831')create table Score--4th最后执行(Sno int not null, --学号（外码）Cno Char(5) not null,--课程号（外码）Degree Decimal(4,1),--成绩foreign key(Sno)references Student(Sno),--学号（外码）foreign key(Cno)references Course(Cno),--课程号（外码）primary key(Sno,Cno))insert into Score values(103,'3-245',86)insert into Score values(105,'3-245',75)insert into Score values(109,'3-245',68)insert into Score values(103,'3-105',92)insert into Score values(105,'3-105',88)insert into Score values(109,'3-105',76)insert into Score values(101,'3-105',64)insert into Score values(107,'3-105',91)insert into Score values(108,'3-105',78)insert into Score values(101,'6-166',85)insert into Score values(107,'6-166',79)insert into Score values(108,'6-166',81)create table Teacher--1st首先执行(Tno varchar(50) primary key not null,--教工编号（主码）Tname varchar(50)not null,--教工姓名Tsex varchar(50)not null,--教工性别Tbirthday datetime,--教工出生年月Prof varchar(50),--职称Depart Varchar(10)not null--教工所在部门)insert into Teacher values(804,'李诚','男','1958-12-02','副教授','计算机系') insert into Teacher values(856,'张旭','男','1969-03-12','讲师','电子工程系') insert into Teacher values(825,'王萍','女','1972-05-05','助教','计算机系') insert into Teacher values(831,'刘冰','女','1977-08-14','助教','电子工程系') select *from Student;select *from Course;select *from Teacher;select *from Score;--1.查询Student表中的所有记录的Sname、Ssex和Class列。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Experiment
i) Identify the entities, relationships, and attributes required for constructing the entity-relationship models for the college database specified next.
ii)Construct for college database according the following entity-relationship model. The database keeps track of courses, textbooks, instructors, and students.
Fig.1 E-R model of college database
iii) Solve the following queries (which refer to the college database) in SQL.
(1)Find all students’ names of students who took cs210 and c s240.
(2)Find all students’ names of students who took cs210 but never took cs110.
(3)Find all pairs of student numbers such that both students studied with the same instructor.
(4)Find all triplets of instructors’ names of instructors who taught the same course.
(5)Find course numbers of courses taken by a student and taught by an instructor such that the student is advised by the instructor.
(6)Find all instructors who advise students who took a course from another instructor who is located in the same room.
(7)Find the names of instructors who taught every course taken by a student who lives in New Hampshire.
(8)Find all pairs of names of students and instructors such that the instructor advises the student but the student never took a course with the instructor.
(9)Find courses’ numbers such that two students are enrolled.
(10) Find courses’ numbers such that at least three students are enrolled.
(11) Find the names of instructors who taught every course taught by Prof. Evans.
(12) Find courses’ names of courses taught by every instructor who teaches cs240.
(13) Find students’ numbers, the lowest grade, the highest grade, and the average grade for students who took at least two courses, excluding cs110.
(14) Find instructors’ names for instructors who teach courses that enroll at least two students.
(15) List the names of instructors and the number of students advised by each instructor.。