博客

1.

SQL更新：修改“高等数学”课程倒数三名成绩，在原来分数上减5分

UPDATE sc
SET sc.grade = sc.grade-5
where cno in(select cno from course where cname='高等数学') and grade is not null
order by grade
limit 3;

2.删除“数据库基础”的全部记录（包括课程信息，成绩信息）

DELETE sc
from sc,course
where cname='数据库基础' and sc.cno=course.cno;
DELETE from course
where cname='数据库基础';

3.修改“王大力”的“计算机导论”课程成绩为70。

update sc,(select sno from student where sname='王大力')as s1,(select cno from course where cname='计算机导论')as c1
set grade = 70
where s1.sno = sc.sno and c1.cno= sc.cno;

4.将所有平均分为75分以上的学生的各门课成绩在原来基础上加5分。

update sc,(select sno,avg(grade) from sc group by sno having avg(grade)>=75)as s2
set grade = grade+5
where sc.sno =s2.sno;

5.根据employee员工表中各部门的员工数，更新department部门表的各对应部门人数。

update department,
	(select dept_id,count(*) as snum from employee group by dept_id)as num1
set emp_num = snum
where num1.dept_id = department.dept_id;

6.建立city值为上海、北京的顾客视图v_customer，包括顾客号、顾客名、联系人姓名、电话和地址。

CREATE VIEW v_customer
AS
SELECT customer_id,customer_name,contact_name,phone,address
FROM customer
WHERE city in("上海","北京");

7.建立城市为上海的客户2016年的订单信息视图v_order，包括顾客号、顾客名、该顾客2016年的订单数及订单总额

CREATE VIEW v_order
AS
SELECT customer.customer_id,customer_name,count(order_id),sum(total_money)
from customer,`order`
where YEAR(order_date)=2016 and city="上海" and customer.customer_id = `order`.customer_id
GROUP BY customer.customer_id