扫码加入数据分析学习群

#测试题一：
create database cdaxuexiao; 
use cdaxuexiao; 
create table student (sno varchar(20), sname varchar(20), ssex char(10), sage int, sdept varchar(20)); 
insert into student values
("9512101","李勇","男",19,"计算机系"),
("9512102","刘晨","男",20,"计算机系"), 
("9512103","王敏","女",20,"计算机系"),
("9521101","张立","男",22,"信息系"),
("9521102","吴宾","女",21,"信息系"), 
("9521103","张海","男",20,"信息系"),
("9531101","钱小力","女",18,"数学系"), 
("9531102","王大力","男",19,"数学系"); 
create  table course (cno varchar(20), cname varchar(20), hours int); 
insert into course values
("c01","计算机文化学",70),("c02","VB",90), 
("c03","计算机网络",80),("c04","数据库基础",108), 
("c05","高等数学",180),("c06","数据结构",72); 
create table sc (sno varchar(20), cno varchar(20), grade int); 
insert into      sc    values
("9512101","c01",90),
("9512101","c02",86), 
("9512101","c06",null),
("9512102","c02",78), 
("9512102","c04",66),
("9521102","c01",82), 
("9521102","c02",75),
("9521102","c04",92), 
("9521102","c05",50),
("9521103","c02",68), 
("9521103","c06",null),
("9531101","c01",80), 
("9531101","c05",95);

select * from student;
#（1）查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。（4分）
#（2）分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩。 （6分）

#测试题二：
create database  cda113 ;
use cda113;
create table customer(
  c_id char(6) primary key not null,
  name varchar(30) not null,
  location varchar(30),
  salary decimal(8,2)
);

create table bank(
  b_id char(5) primary key not null,
  bank_name char(30) not null
);


create table deposite(
	d_id int primary key not null auto_increment,   #存款流水号
    c_id char(6),                                   #客户ID 
    b_id char(5),                                   #银行ID
    dep_date date,                                  #存款日期
    dep_type char(1),                               #存款类型
    amount decimal(8,2),                            #存款金额
    constraint fk_cid foreign key(c_id) references customer(c_id), 
    constraint fk_bid foreign key(b_id) references bank(b_id)
);

insert into customer values
('101001','孙杨','广州',1234),
('101002','郭海','南京',3526),
('101003','卢江','苏州',6892),
('101004','郭惠','济南',3492),
('101005','徐昊','北京',5200);

insert into bank values
('B0001','工商银行'),
('B0002','建设银行'),
('B0003','中国银行'),
('B0004','农业银行');

insert into deposite values
(null,'101001','B0001','2011-04-05','3',42526),
(null,'101002','B0003','2012-07-15','5',66500),
(null,'101003','B0002','2010-11-24','1',42366),
(null,'101004','B0004','2008-03-31','1',62362),
(null,'101001','B0003','2002-02-07','3',56346),
(null,'101002','B0001','2004-09-23','3',353626),
(null,'101003','B0004','2003-12-14','5',36236),
(null,'101004','B0002','2007-04-21','5',26267),
(null,'101001','B0002','2011-02-11','1',435456),
(null,'101002','B0004','2012-05-13','1',234626),
(null,'101003','B0003','2001-01-24','5',26243),
(null,'101004','B0001','2009-08-23','3',45671);

#问题1 对deposite表进行统计，按银行统计存款总数，显示为b_id,total. 


#问题2 对deposite、customer、bank进行查询，查询条件为location在广州、苏州、济南的客户，
#存款在300000至500000之间的存款记录，显示客户姓名name、银行名称bank_name、存款金额amount.

学习更多数据分析知识欢迎加入CDA网校会员 https://www.cda.cn/member.html?utm_source=weitao