你的位置:首页 > 数据库

[数据库]面试题整理:SQL(一)


1.横纵表转换

A表

Name

Course

Grade

Alex

English

80

Alex

Chinese

70

Alex

Japanese

85

Bob

English

75

Bob

Chinese

85

Bob

Japanese

80

 

 

 

 

 

 

 

 

 

 

 

B表

Name

English

Chinese

Japanese

Alex

80

70

85

Bob

75

85

80

Chris

90

70

60

 

 

 

 

 

 

 

A -> B

select Name, SUM(case Course when 'English' then Grade else 0 end) as English,SUM(case Course when 'Chinese' then Grade else 0 end) as Chinese,SUM(case Course when 'Japanese' then Grade else 0 end) as Japanesefrom TranscriptAgroup by Name

B -> A

select Name,'English' AS Course,English AS Grade from TranscriptBunion all select Name,'Chinese' AS Course,Chinese AS Grade from TranscriptB union all select Name,'Japanese' AS Course,Japanese AS Grade from TranscriptBorder by Name,Course desc;

 

2.假设有学习成绩表

用一条SQL查出每一门成绩都大于80的学生的姓名

select distinct name from table where name not in (select distinct name from table where chengji <= 80)select name from table group by name having min(chengji)>80

 

3.假设有Team表,只有一个Name字段,代表球队名字

用SQL查处各队之间互相比赛的组合

select a.name, b.name from team a, team b where a.name< b.name

 

4.统计工资累加

A表

Year

Salary
2010

1000

2011 

1500
2012

2000

2013

2500

 

 

 

 

 

 

 

 

B表

Year

Salary
2010

1000

2011 

2500
2012

4500

2013

7000

 

 

 

 

 

 

 

 

A -> B

select b.Year, sum(a.Salary) AccSalary from TA a, TA bwhere a.Year <= b.Yeargroup by b.Yearselect a.year, (select sum(b.Salary) from TA b where b.Year <= a.year) AccSalary from TA a

 

 

 

 

 

 

To be continue...