#測試題一：
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、customer、bank進行查詢，查詢條件為location在廣州、蘇州、濟南的客戶，
#存款在300000至500000之間的存款記錄，顯示客戶姓名name、銀行名稱bank_name、存款金額amount.(5分)

#問題2, 對 deposite表進行統計，按銀行統計存款總數，顯示為 b_id 銀行ID, bank_name 銀行名稱,total 存款總額.（5分）

學習更多數據分析知識歡迎加入CDA網校會員 http://www.ruiqisteel.com/member.html?utm_source=weitao