SQL question _ MySQL

SQL questions encountered in the interview questions bitsCN.com

1. suppose there is a sheet indicating cj table Name Subject Result Zhang San language 80 Zhang San mathematics 90 Zhang San Physics 85 Li Si language 85 Li Si Mathematics 92 Li Si physics 82 requirement query Result: name Chinese mathematics physics Zhang San 80 90 85 Li Si 85 92 82

1 -- CREATE cj TABLE sql2 create table 'CJ '(3 'id' int (11) not null AUTO_INCREMENT, 4 'name' varchar (20) default null, 5 'subobject' varchar (20) default null, 6 'result' int (11) default null, 7 primary key ('id') 8) ENGINE = InnoDB AUTO_INCREMENT = 7 default charset = utf8;

1 -- INSERT data SQL 2 INSERT INTO cj 3 ('id', 'name', 'subobject', 'result') 4 VALUES 5 (1, 'Zhang San ', 'China', 80); 6 7 insert into cj 8 ('id', 'name', 'subobject', 'result') 9 VALUES10 (2, 'Zhang San ', 'mat', 90); 11 12 insert into cj13 ('id', 'name', 'subobject', 'result') 14 VALUES15 (3, 'Zhang San ', 'Physical ', 85); 16 17 insert into cj18 ('id', 'name', 'subobject', 'result') 19 VALUES20 (4, 'Lily ', 'China', 85); 21 22 insert into cj23 ('id', 'name', 'subobject', 'result') 24 VALUES25 (5, 'Lily ', 'mat', 92); 26 27 insert into cj28 ('id', 'name', 'subobject', 'result') 29 VALUES30 (6, 'Lily ', 'Physical ', 89 );

1 -- query SQL2 select 3 distinct. name, 4 (select result from cj where name =. name and subject = 'China') language, 5 (select result from cj where name =. name and subject = 'mate') Mathematics, 6 (select result from cj where name =. name and subject = 'Physical ') physical 7 from cj;

BitsCN.com