Sql（PG SQL)常见面试题一

–1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

CREATE TABLE data(
name text,
kecheng text,
fenshu int);

INSERT INTO data values(‘张三’, ‘语文’, 81);
INSERT INTO data values(‘张三’, ‘数学’, 75);
INSERT INTO data values(‘李四’, ‘语文’, 76);
INSERT INTO data values(‘李四’, ‘数学’, 90);
INSERT INTO data values(‘王五’, ‘语文’, 81);
INSERT INTO data values(‘王五’,‘数学’, 100);
INSERT INTO data values(‘李四’,‘英语’, 90);

way1:SELECT name FROM data GROUP BY name HAVING (min(fenshu))>80;

way2:SELECT DISTINCT name FROM data WHERE name NOT IN(SELECT DISTINCT name FROM data WHERE fenshu<80);

–2. 学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息

CREATE TABLE studenttable(
num int,
学号 int,
姓名 text,
课程编号 int,
课程名称 text,
score numeric);
INSERT INTO studenttable VALUES(1,2005001,‘张三’,0001,‘数学’,69);
INSERT INTO studenttable VALUES(2,2005002,‘李四’,0001,‘数学’,89),(3,2005001,‘张三’,0001,‘数学’,69);

DELETE FROM studenttable WHERE num not in (SELECT min(num) FROM studenttable GROUP BY (学号,姓名,课程编号,课程名称,score));

–3. 面试题：怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

CREATE TABLE hehe(
year INT,
month INT,
amount numeric);

INSERT INTO hehe VALUES(1991,1,1.1),(1991,2,1.2),(1991,3,1.3),(1991,4,1.4),(1992,1,2.1),(1992,2,2.2),(1992,3,2.3),(1992,4,2.4);
SELECT year,
(SELECT amount FROM hehe m WHERE month=1 AND hehe.year=m.year) m1,
(SELECT amount FROM hehe m WHERE month=2 AND hehe.year=m.year) m2,
(SELECT amount FROM hehe m WHERE month=3 AND hehe.year=m.year) m3,
(SELECT amount FROM hehe m WHERE month=4 AND hehe.year=m.year) m4
FROM hehe GROUP BY year;

4. 说明：复制表( 只复制结构, 源表名：a新表名：b)
   CREATE TABLE haha2(LIKE hehe);

5.说明：拷贝表( 拷贝数据, 源表名：a目标表名：b)
CREATE TABLE b AS SELECT * FROM a;

6.有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
这道题的SQL 语句怎么写？
A
KEY VALUE
a 1
b 2
c 3
d 4

B
KEY VALUE
a 1
b 3
c 6
f 5

CREATE TABLE A(
key text,
value int);

CREATE TABLE B(
key text,
value int);

INSERT INTO A VALUES(‘a’,1),(‘b’,2),(‘c’,3),(‘d’,4);
INSERT INTO B values(‘a’,1),(‘b’,3),(‘c’,6),(‘f’,5);

key:
update b set value=a.value from a where a.key=b.key;