我这里有一个跨国公司给我的SQL培训题，给大家分享一下（2）

 

Picture

Id	Name	Path
1	Google Logo	http://www.google.com/logo.png
2	Baidu Logo	http://www.baidu.com/logo.png
3	Yahoo Logo	http://www.yahoo.com/logo.png
4	Live Logo	http://www.live.com/logo.png

 

ViewLog

Id	Picture_Id	View_Date_Time
1	1	2000-01-01 00:00:00
2	1	2000-01-01 00:00:01
3	2	2000-01-01 00:00:01
4	2	2000-01-01 00:00:02
5	3	2000-01-01 00:00:02
6	3	2000-01-01 00:00:03
7	4	2000-01-01 00:00:03
8	4	2000-01-01 00:00:04

 

Question 1: Write a SQL to show the following stats information within the given period - 2000-01-01 00:00:00 to 2000-01-01 00:00:02.

Name	Access_Count
Google Logo	2
Baidu Logo	2
Yahoo Logo	1
Live Logo	0

If I change the period to 2000-01-01 00:00:01 to 2000-01-01 00:00:03, the shown result set should be:

Name	Access_Count
Google Logo	1
Baidu Logo	2
Yahoo Logo	2
Live Logo	1

 

Question 2: Write a SQL to show the following stats information within the given period - 2000-01-01 00:00:00 to 2000-01-01 00:00:02.

Name	Access_Count
Google Logo	2
Baidu Logo	2
Yahoo Logo	1

If I change the period to 2000-01-01 00:00:01 to 2000-01-01 00:00:03, the shown result set should be:

Name	Access_Count
Google Logo	1
Baidu Logo	2
Yahoo Logo	2
Live Logo	1

 

Question 3: Remove all the above data from database. Write a stored procedure to generate 10,000 records in the Picture table, and 26,784,010,000 records in the ViewLog table:

Id	Name	Path
1	Picture #1	http://www.site.com/1.png
2	Picture #2	http://www.site.com/2.png
${n}	Picture #${n}	http://www.site.com/${n}.png
10000	Picture #10000	http://www.site.com/10000.png

 

ViewLog

Id	Picture_Id	View_Date_Time
1	1	2000-01-01 00:00:00
2	2	2000-01-01 00:00:00
${n}	${n}	2000-01-01 00:00:00
10000	10000	2000-01-01 00:00:00
10000+1	1	2000-01-01 00:00:01
10000+2	2	2000-01-01 00:00:01
10000+${n}	${n}	2000-01-01 00:00:01
20000	10000	2000-01-01 00:00:01
${m}*10000+1	1	2000-01-01 00:00:00+${m}
${m}*10000+2	2	2000-01-01 00:00:00+${m}
${m}*10000+${n}	${n}	2000-01-01 00:00:00+${m}
${m}*10000+10000	10000	2000-01-01 00:00:00+${m}
3600*24*31*10000+1	1	2000-01-31 23:59:59
3600*24*31*10000+2	2	2000-01-31 23:59:59
3600*24*31*10000+${n}	${n}	2000-01-31 23:59:59
3600*24*31*10000+10000	10000	2000-01-31 23:59:59

So here should be (3600*24*31+1)*10000 = 26,784,010,000 recodes in the ViewLog table.

After the stored procedure executed, team leads must check the result of his team.

For example:

1.       ‘select count(*) from ViewLog’ should return 26,784,010,000.

2.       ‘select min(Id) from ViewLog’ should return 1.

3.       ‘select max(Id) from ViewLog’ should return 26,784,010,000.

4.       ‘select Picture_Id, count(*) from ViewLog group by Picture_Id’ should return 10,000 records, and all the values in the ‘count(*)’ column is 2,678,401.

You should try other ways to check the data, and make sure the data match the formula in the tables.

 

Question 4: Run your SQL of Q1 and Q2. See what happens. Improve the performance of SQL and table design.

 

Question 5: Run the SQL of Q1 and Q2 with the given period - 2000-01-01 00:00:00 to 2000-01-31 23:59:59, and see what happens. Improve the performance of SQL and table design.