数据操作语句-2

1.     查计算机系的学生学号和姓名

2.     查选修了课程的学生学号

3.     查选修数据库课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列

4.     查选修课程数据库且成绩在80－90之间的学生学号和成绩，并将成绩乘以系数0.7输出

5.查计算机系和数学系的姓张的学生的信息

6.查缺少了成绩的学生的学号和课程号

7.     查询Student表与SC表基于学号Sno的自然连接（使用JOIN）

8.     查询计算机系（CS）选修了不少于两门课的学生的学号

 

9.     查询学生及其课程、成绩的情况（不论是否选课，均需列出学生信息）

10.  查询同系、同年龄和同性别的学生

11.  查询选修了课程名为“数据库系统”的学生学号、姓名和所在系

 

 

12.  查询至少选修了学生08311001号选修的全部课程的学生学号

13.  查询至少选修一门课的女生的姓名

 

14.  查询全部学生都选修的课程的课程号和课程名

15.  统计每门课的学生平均成绩

16.  查询选修4门课（不统计不及格的课程）以上的学生的总成绩，按总成绩降序排列

1.Produce a list of salaries for all staff,showing only the staff number, the first and last names, and the salarydetails.

 – Use staff number, First name and Last nameand Salary as

 column names for the viewing table.

2.List all staff with a salary greater than10,000

 –Use staff number, First name and Last name and Salary as

 column names for the viewing table.

3.List all staff with a salary between 20000 and 30 000.

4.Produce a list of salaries for all staff,arranged in descending order of salary.

 

5.     List the address of all branch offices in London or Glasgow.

6.     List all managers and supervisors.

7.     How many properties cost more than 350 per month to rent?

8.     How many different properties were viewed in May 2001?

 

9.     List the names of clients who have viewed a property along with anycomments.

10.  For each branch office, list the numbers and names of staff whomanage properties and the properties that they manage.

 

11.  For each branch, list the numbers and names of staff who manageproperties, including the city in which the branch is located and theproperties that the staff manage.

12.  From each branch with more than 1 member of staff, find number ofstaff in each branch and sum of their salaries.

 

13List all staff whose salary is greaterthan the average salary, and show how much.

13.  List Staff's names and property they manage.

 

1. 将你的年龄改为23岁

2. 将你选修的3号课程的成绩改为该课的平均成绩

3. 学生王林2号课程成绩改为零分

4. 备份学生选课表SC到TSC中

       

5. 删除计算机系所有学生的选课记录

6. Give all staff a 3% pay increase.

7. Give all Managers a 5% pay increase.

 

8.Delete therecord of a staff member whose number is

'SA9'.

能成功删除，但后半部分添加约束有错