cool hit counter SQL questions encountered in interview questions_Intefrankly

SQL questions encountered in interview questions


 1.Suppose there is a table representing cj 
Name Subject Result
 Zhang San Language 80
 Zhang San Mathematics 90
 Zhang San Physics 85
 Li IV Language 85
 Li IV Mathematics 92
 Li Si Physics 82

 Request for results.
 Name Language Maths Physics
 Zhang San 80 90 85
 Li Si 85 92 82
1 -- establishcj a table (listing information)sql
2 CREATE TABLE `cj` (
3   `id` int(11) NOT NULL AUTO_INCREMENT,
4   `name` varchar(20) DEFAULT NULL,
5   `subject` 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`, `subject`, `result`)
 4 VALUES
 5    (1, 'John Doe', ' language', 80);
 6 
 7 INSERT INTO cj
 8    (`id`, `name`, `subject`, `result`)
 9 VALUES
10    (2, 'John Doe', ' maths', 90);
11 
12 INSERT INTO cj
13    (`id`, `name`, `subject`, `result`)
14 VALUES
15    (3, 'John Doe', ' physical', 85);
16 
17 INSERT INTO cj
18    (`id`, `name`, `subject`, `result`)
19 VALUES
20    (4, ' the fact that the black sheep are in the wrong place', ' language', 85);
21 
22 INSERT INTO cj
23    (`id`, `name`, `subject`, `result`)
24 VALUES
25    (5, ' the fact that the black sheep are in the wrong place', ' maths', 92);
26 
27 INSERT INTO cj
28    (`id`, `name`, `subject`, `result`)
29 VALUES
30    (6, ' the fact that the black sheep are in the wrong place', ' physical', 89);
1 - Query SQL
2 select 
 3 distinct a.name Name,
4     (select result from cj where name = a.name and subject = ' language')  language,
5     (select result from cj where name = a.name and subject = ' maths')  maths,
6     (select result from cj where name = a.name and subject = ' physical')  physical   
7 from cj a;

E | hongtenzone@foxmail.com B |http://www.cnblogs.com/hongten


Recommended>>
1、
2、CCF Big Data Expert Committee Big Data Trends Predictions for 2018 full PPT
3、Is the app marketplace dying out Heres why
4、Paper Recommendation 5 Recent Papers Related to ImageCaption Generation Sentiment Attention Mechanisms Remote Sensing Images Sequence to Sequence Deep Neural Structures
5、Amazon Comprehend adds new feature to add NLP to apps without machine learning background

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号