sql视图习题

建立city值为上海、北京的顾客视图

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

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

建立城市为上海的客户2016年的订单信息视图

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

其中客户表cutomer表,订单表order

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

建立一个视图

建立一个视图v_borrow，显示"力学1"班学生的借书信息，列出卡号，姓名和还书日期。

表结构如下：

card(借书卡) 表：cno 卡号，name 姓名，class 班级

borrow(借书记录)表 ：cno 借书卡号，bno 书号，rdate 还书日期

create view v_borrow
as
select card.cno,name,borrow.rdate from card,borrow
where card.cno=borrow.cno and card.class='力学1';

由视图删除数据

从视图v_student_c(sno,sname,sage)，删除学号为9521101的学生基本信息。
表名、视图名请用小写。

delete from v_student_c
where sno='9521101';

由视图更新数据

更新视图数据v_student_c(sno,sname,sage)，将学号为9521101的年龄改为21.
表名、视图名请用小写。

update v_student_c
set sage='21'
where sno='9521101';

由视图插入数据

向视图v_student_c(sno,sname,sage)添加数据行，数据如下：
sno:9521101
sname:王明
sage：20
表名、视图名请用小写。

insert 
into v_student_c(sno,sname,sage)
values('9521101','王明',20);

建立视图查看课程号cno，课程名cname，课程平均成绩avggrade

建立视图v_sc_avg，通过视图可以查看课程号cno，课程名cname，课程平均成绩avggrade。
基本表course，基本表sc

表名、视图名请用小写。

CREATE VIEW v_sc_avg AS 
SELECT c.cno, c.cname, AVG(sc.grade) AS avggrade
FROM course c
JOIN sc ON c.cno = sc.cno
GROUP BY c.cno, c.cname;

建立视图查看学号，姓名、课程号，课程名、成绩

建立视图v_sc，通过视图可以查询计算机系学生的成绩信息，包括学号，姓名、课程号，课程名、成绩。
基本表student,基本表course,基本表sc

表名、视图名请用小写。

CREATE VIEW v_sc AS
SELECT s.sno, s.sname, c.cno, c.cname, sc.grade
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON c.cno = sc.cno
WHERE s.sdept = '计算机系';

SQL视图：根据视图CJ_STUDENT创建视图CJ_TJ

已建有视图cj_student,视图中包含所有成绩不及格(含没有成绩)的学生的学号，姓名，课程名，成绩，按成绩降序排序。

根据视图cj_student创建视图cj_tj，包含课程名，不及格人数，不及格人姓名列表（按姓名升序，以 | 分割），按照课程名降序排列。

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

CREATE VIEW cj_tj AS
SELECT CNAME, COUNT(*), GROUP_CONCAT(SNAME ORDER BY SNAME ASC SEPARATOR '|')
FROM cj_student 
GROUP BY cname
ORDER BY cname DESC;

SQL视图：建立视图IS_STUDENT

#建立视图is_student,视图中包含信息系全体学生的基本信息。（要求限制更新）

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

create view is_student
as
select * from student where sdept='信息系'
with check option;

SQL视图：建立视图AVG_CJ

#建立视图avg_cj，视图包括学生的学号以及他们的平均成绩(取整)，按成绩降序排列。

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

create view avg_cj
as
select sno,round(avg(grade)) from sc 
group by sno order by round(avg(grade)) desc;

SQl视图：建立视图CJ_STUDENT

#建立视图cj_student,视图中包含所有成绩不及格(含没有成绩)的学生的学号，姓名，课程名，成绩，按成绩降序排序。

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

CREATE VIEW cj_student AS 
SELECT 
    sc.Sno,sname,
    cname,
    grade 
FROM 
  student,
  course,
  sc 
WHERE  sc.Sno=student.Sno
AND sc.Cno=course.Cno 
AND (grade<60 OR  grade IS NULL) 
ORDER BY grade DESC

建立视图查看学号、姓名、年龄

建立计算机系学生信息视图v_student_c，通过视图可以查看sno,sname,sage。
基本表student。
表名、视图名请用小写。

CREATE VIEW v_student_c AS
SELECT sno, sname, sage
FROM student
WHERE sdept = '计算机系';