36-Blog Site Database-Blog Comment Information Data Operation
Item Description
Nowadays, micro-blog and blog publishing information have become the main information publishing and dissemination system. How to manage these data, this project mainly operates on the blog information table and commentary table in the blog website.
Blog site database operation requirements are as follows:
1) Database BlogDB.
2) Comments information table comments, the table structure as shown in table J2-36-1.
Table J2-36-1 comments table
| Field name | Field Description | data type | Allow blank | Remarks |
|---|---|---|---|---|
| CommentID | Comment ID | integer | no | Primary key, self-increasing (increment 1) |
| ArticleID | Article ID | integer | no | foreign key |
| Conntent | Comments | Characters (500) | no | |
| GuestName | Commentator Name | Characters (20) | no | |
| GuestEmail | Commentator E-mail | Characters (50) | no | |
| GuestHomepage | Reviewer Site | Characters (100) | yes |
3) Blog Booking Information Table article, the table structure as shown in Table J2-36-2.
Table J2-36-2 article table
| Field name | Field Description | data type | Allow blank | Remarks |
|---|---|---|---|---|
| ID | Article ID | integer | no | Primary key, self-increasing (increment 1) |
| Title | Article Title | Characters (100) | no | |
| Content | content | text Type | yes | |
| CommentsNum | Number of comments | integer | yes | |
| Postdate | Date of publication | Date Time | no |
4) Establish an association between the two tables, and the ArticleID is associated with the ArticleID of comments.
5) Table comments basic data as shown in table J2-36-3.
Table J2-36-3 comments Table Base Data
| CommentID | ArticleID | Conntent | GuestName | GuestEmail | GuestHomepage |
|---|---|---|---|---|---|
| 1 | 1 | Comment One | Wang Yuyan | Zhang3@qq.com | http://www.zhang3.com |
| 2 | 2 | Comment 2 | Li Qiushui | Lee4@163.com | http://www.lee4.com |
| 3 | 1 | Comment 3 | Witch Line Cloud | Wang2@sina.com | |
| 4 | 1 | Comment IV | Xiaoyao Zi | mazi@sohu.com |
6) Table article basic data is shown in table J2-36-4.
Table J2-36-4 article table base data
| ID | Title | Content | CommentsNum | Postdate |
|---|---|---|---|---|
| 1 | Universities don't aspire to be "top universities" | The California Polytechnic Institute ranked first among the best universities in the world from 2012 to 2013, according to a report published by the British Times Higher Education Special 3. Harvard University, which ranked second last year, retired to fourth place.Peking University rose from 49 to 46 last year, Tsinghua University rose from 19 to 52, Hong Kong University of Science and Technology and Hong Kong University of Science and Technology fell from last year to 35 and 65 respectively. (China News Network, October 5)According to this ranking, the mainland's Peking University and Tsinghua finally breathed heavily in front of the Hong Kong schools. At least two schools have already surpassed HKUST in ranking. However, this ranking does not seem to explain much to many mainland netizens, and even a considerable number of them think it is funny.This shows that if the Mainland Colleges and universities are running schools around the ranking data, they will not be recognized by the public even if the ranking is improved. | 3 | 2011-7-2 |
| 2 | A letter of admission from a foreign university is nothing | Recently, a resignation letter from Dayle Smith, vice president of the Business School of the University of San Francisco, poured cold water on China's study abroad. According to the San Francisco Chronicle on September 23, Weber, director of the Business School, said in a letter to employees that Smith's resignation was due to a "substantial increase" in foreign students."Given the large number of students with poor English proficiency and unbalanced student-origin ratios, we will face some unique educational and cultural challenges." However, students with poor English proficiency are enrolled due to the "Conditional Enrollment" policy. | 1 | 2011-8-3 |
(1) Task description
Task 1: Create blog site database BlogDB in SQL language
1) The primary database file has an initial value of 10MB and a maximum of 20MB, increasing by 1MB.
2) The initial value of the log file is 5MB, up to 10MB, increasing by 10%.
IF DB_ID('BlogDB') IS NOT NULL DROP DATABASE BlogDB
GO
CREATE DATABASE BlogDB
ON PRIMARY
(
NAME=BlogDB,
FILENAME='D:\xxxx\BlogDB.mdf',
SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=1MB
)
LOG ON
(
NAME=BlogDB_log,
FILENAME='D:\xxxx\BlogDB_log.ldf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=10%
)
Task 2: Create comment information table, blog information table article in SQL language
1) Create database tables according to the provided table J2-36-1, J2-36-2 structure, with primary and foreign keys.
CREATE TABLE article ( ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), Title NVARCHAR(100) NOT NULL, Content TEXT, CommentsNum INT, Postdate DATETIME NOT NULL, ) CREATE TABLE comments ( CommentID INT NOT NULL PRIMARY KEY IDENTITY(1,1), ArticleID INT NOT NULL, Conntent NVARCHAR(500) NOT NULL, GuestName NVARCHAR(20) NOT NULL, GuestEmail NVARCHAR(50) NOT NULL, GuestHomepage NVARCHAR(100), FOREIGN KEY(ArticleID) REFERENCES article(ID) )
Task 3: Operate comment and blog information table article s in SQL
1) Add data to the table according to the data provided in tables J2-36-3 and J2-36-4.
2) Find out the list of people who commented on the article "Don't pursue being a university in the leaderboard".
3) Delete the blog related information of Li Qiushui's comments.
INSERT INTO article VALUES
('Universities don't aspire to be "top universities"','UK Times Higher Education Special Issue released on March 3, 2012-2013 California Institute of Technology ranked first in the world, while Harvard University ranked second last year dropped to fourth. Peking University rose from 49 to 46, Tsinghua University rose 19, from 71 to 52, and Hong Kong University of Science and Technology fell from last year to 35 and 65, respectively. (China News Network October 5)',1,'2011-8-3'),
('A letter of admission from a foreign university is nothing','Recently, Dell, associate dean of the University of San Francisco Business School·Smith(Dayle Smith)According to the San Francisco Chronicle on September 23, Weber, director of Business School, said in a letter to employees that Smith's resignation was due to a "substantial increase" in foreign students. Smith wrote:"Given the large number of students with poor English proficiency and unbalanced student-origin ratios, we will face some unique educational and cultural challenges." However, students with poor English proficiency are enrolled due to the "Conditional Enrollment" policy.',3,'2011-7-2')
INSERT INTO comments VALUES
(1,'Comment One','Wang Yuyan','Zhang3@qq.com','http://www.zhang3.com'),
(2,'Comment 2','Li Qiushui','Lee4@163.com','http://www.lee4.com'),
(1,'Comment 3','Witch Line Cloud','Wang2@sina.com',NULL),
(1,'Comment IV','Xiaoyao Zi','mazi@sohu.com',NULL)
SELECT GuestName FROM comments WHERE ArticleID=(SELECT ID FROM article WHERE Title='Universities don't aspire to be "top universities"')
DECLARE @VAR1 INT
SET @VAR1 = (SELECT ArticleID FROM comments WHERE GuestName='Li Qiushui')
DELETE FROM comments WHERE GuestName='Li Qiushui'
DELETE FROM article WHERE ID = @VAR1