Database Practice __ Database

--3.1 creating a Student Performance Management database (STUDSCORE_DB1)

Create DATABASE STUDSCORE_DB1--DB creation statement

--3.2 Create a Student performance management database (STUDSCORE_DB2), note the requirements
Create DATABASE Studscore_db2
On
(Name=studscore_db2_data1,--The logical name of the database master file
Filename= ' D:\StudScore_DB2_Data1.mdf ',--database Master file physical filename
size=10,--Initial size of database master file
maxsize=unlimited,---Database main file maximum size unlimited
filegrowth=10%)--database Master file growth rate
LOG on
(Name= ' Studscore_db2_log1 ',--database log file logical name
Filename= ' D:\StudScore_DB2_log1.ldf ',--database log file physical file name
SIZE=5MB,--The initial size of the database log file
MAXSIZE=25MB,--Maximum size of database log file

FILEGROWTH=1MB)--database log file growth rate

--3.3 Modify the Student Performance Management database (STUDSCORE_DB2) to add 5MB secondary data files
ALTER DATABASE STUDSCORE_DB2
Add File (
NAME=STUDSCORE_DB2_DATA2,--Database secondary data file logical name
Filename= ' D:\StudScore_DB2_Data2.ndf ',--database secondary data file physical filename
SIZE=5MB,--Database secondary data file Initial size
MAXSIZE=100MB,--Database secondary data file maximum size
FILEGROWTH=5MB--Database secondary data file growth rate

)

--3.4 Modify the Student Performance Management database (STUDSCORE_DB2), add two log files of 5MB size
ALTER DATABASE STUDSCORE_DB2
Add log file
--Added the first 5MB log file
(Name= ' studscore_db2_log2 ',--database log file logical name
Filename= ' D:\StudScore_DB2_Log2.ldf ',--database log file physical file name
SIZE=5MB,--The initial size of the database log file
MAXSIZE=100MB,--Maximum size of database log file
FILEGROWTH=5MB),--database log file growth rate
--Added a second 5MB log file
(Name= ' Studscore_db2_log3 ',--database log file logical name
Filename= ' D:\StudScore_DB2_Log3.ldf ',--database log file physical file name
SIZE=5MB,--The initial size of the database log file
MAXSIZE=100MB,--Maximum size of database log file

FILEGROWTH=5MB)--database log file growth rate

--3.5 Delete Student Achievement Management database (STUDSCORE_DB2)

Drop Database studscore_db2--Delete student score Management DB statement

--3.6 Creating Student Information sheets (studinfo)
CREATE TABLE Studinfo
(
Studno varchar (primary) key,--set Studno as the primary key field
Studname varchar () NOT NULL,
Syudsex Char (2) Default ' male ' NOT null--defaults set in datasheet
Studbirthday datetime NULL,
ClassID varchar (TEN) NOT NULL

)

--3.7 Create class Information table (ClassInfo)
CREATE TABLE ClassInfo
(
ClassID varchar (TEN) primary key,--PRIMARY KEY constraint
ClassName varchar () NOT NULL,
Classdesc varchar (+) NULL

)

--3.8 Creating a Student information table with foreign key relationships (Studinfo)
DROP table Studinfo--Delete the previous student information sheet (studinfo)
CREATE TABLE Studinfo
(
Studno varchar (primary) key,--set Studno as the primary key field
Studname varchar () NOT NULL,
Syudsex Char (2) NOT NULL,
Studbirthday datetime NULL,
ClassID varchar (TEN) Constraint fk_classid Foreign key references ClassInfo (ClassID)
Not null-establishes a foreign key relationship

)

--3.9 creating a Student score information table with constraints (Studscoreinfo)
CREATE TABLE Studscoreinfo
(
Studno varchar (15),
CourseID varchar (10),
Studscore Numeric (4,1) default 0 Check (studscore>=0 and studscore<=100),
--use check to constrain student scores from 0 to 100
Constraint pk_s_c Primary Key (Studno,courseid)
--Creating a composite primary key

)

--3.10 Create a Student registration information form with identity column (identity) (Stuenro11info)
CREATE TABLE Stuenro11info
(
seq_id INT IDENTITY (100001,1),--Entry sequence number initial value is 100001, step is 1 (auto number)
Studno varchar (Primary) Key,
Studname varchar (+) NOT NULL

)

--3.11 Modify Student Score Information table (STUDSCOREINFO) Add auto-numbering new columns

ALTER TABLE studscoreinfo add seq_id int Identity (1001,1)

--3.12 Modify Student Score Information table (STUDSCOREINFO) Delete primary key (Pk_s_c)

ALTER TABLE Studscoreinfo DROP constraint Pk_s_c

--3.13 Modify Student Score Information table (Studscoreinfo) to set (Studno,courseid) to composite primary key (Pk_s_c)

ALTER TABLE STUDSCOREINFO ADD constraint Pk_t_c primary key (Studno,courseid)

--3.14 Modify Student scores information sheet (Studscoreinfo) Delete an AutoNumber column

ALTER TABLE studscoreinfo drop column seq_id

--3.15 Delete Student score Information Form (STUDSCOREINFO)

drop table Studscoreinfo

--3.16 adding a new record to the Class information table (ClassInfo) using the INSERT statement
INSERT INTO ClassInfo
(CLASSID,CLASSNAME,CLASSDESC)
Values

(' 20000704 ', ' Computer 2000 ', ' How's the computer ')

--3.17 adding a new record to the Class information table (ClassInfo) using the INSERT statement
INSERT INTO ClassInfo
(CLASSNAME,CLASSID)
Values

(' 20000704 ', ' Computer 2000 ')

--3.18 add a new record to the Student Score information table (STUDSCOREINFO) using the INSERT statement
INSERT INTO Studscoreinfo
(Studno,courseid,studscore)
Values
(' 20000704001 ', ' A0101 ', 80.5)
--3.19 Update class Number "20000704" class name is "ITCSC 2000", class description is empty value
Update ClassInfo
Set Classname= ' ITCSC level 2000 ', Classdesc=null

where classid= ' 20000704 '

--3.20 Delete class information for class number "20000704"
Delete from ClassInfo

where classid= ' 20000704 '

--3.21 Delete all records of Student score list (studscoreinfo)

TRUNCATE TABLE Studscoreinfo

--3.22 Inquiry Student Information Sheet (studinfo) all records
Select Studno, Studname, Studsex,studbirthday, ClassID from Studinfo
--also use the symbol * To select all columns of the table

Select *from studinfo

--3.23 Query Student Information Sheet (studinfo) section Records

Select Studno, Studname, ClassID from Studinfo

--3.24 using connection columns in the Query Student Information table (STUDINFO)
Select Studno+studname, Studname,studsex,studname,classid from Studinfo

--3.25 using computed columns in the Query Student Information table (STUDSCOREINFO)

Select studno,courseid,studscore+5,studscore*0.8 from Studscoreinfo
--3.26 query for non-duplicated gender records in student information sheets (studinfo)

Select distinct Studsex from Studinfo

--3.27 Querying Student Information Sheets (studinfo) for names and gender-not-duplicated records

Select distinct Studname,studsex from Studinfo

--3.28 Check the top 10 records in the Student Information sheet (studinfo)

Select Top 10* from Studinfo

--3.29 query number for the top 10 results in 20050319001 grades
Select Top 10* from Studscoreinfo

where studno= ' 20050319001 ' ORDER by studscore Desc

--3.30 Inquiry No. 20050319001 score of 20% Records

Select top percent * from Studscoreinfo where studno= ' 20050319001 '

--3.31 Query Student Information Form (studinfo) number, name, class number information, and display in Chinese text field name

Select Studno as study number, name =studname,classid class number from Studinfo

--3.32 aliases for string connections

Select Studno+studname as study number name, gender = ' student Gender: ' +studsex from Studinfo

--3.33 to store a new table for some of the field results queried in the Student Information table (STUDINFO)
Select Studno as study number, Studname name, date of birth =studbirthday
Into Studinfoback

From Studinfo

--3.34 Select Student Information table (STUDINFO) first 10 records insert in new table
Select top Studno as study number, Studname name, studsex as gender
Into Chinesestudinfo

From Studinfo

--3.35 using table aliases to query Student information sheets (STUDINFO) records
Select Studinfo.studno, studinfo.studname from Studinfo
Select S.studno,s.studname from Studinfo as S

Select S.studno number, s.studname as name, ' class number ' =classid from Studinfo s

--3.36 results information for students with a score greater than 70

SELECT * FROM Studscoreinfo where studscore>70

--3.37 query results of more than 90 of student performance information

SELECT * FROM Studscoreinfo where studscore>=90

--3.38 Student information for the "20050319001" study number

SELECT * from studinfo where Studno = ' 20050319001 '

--3.39 inquiry number is greater than "20050319001" Student information

SELECT * from Studinfo where Studno > ' 20050319001 '

--3.40 enquiries about students born after January 1, 1985

SELECT * from Studinfo where Studbirthday >= ' 1985/01/01 '

--3.41 information for students who do not have sex as "male"

SELECT * from Studinfo where studsex<> ' man '

--3.42 check all records from student grades 60 to 70

SELECT * from Studscoreinfo where studscore>=60 and studscore<=70

--3.43 Inquiry number 20050319002 or 99070405 student information

SELECT * from studinfo where Studno = ' 20050319002 ' or Studno = ' 99070405 '

--3.44 information for students with gender as "female"

SELECT * from Studinfo where not studsex= ' man '

--3.45 inquiry number is "20050319001", scores of 80 points above all record records

SELECT * from Studscoreinfo where studno= ' 20050319001 ' and studscore>=80

--3.46 inquiry number is "20050319001", scores between 90 to 100 of all records

SELECT * from Studscoreinfo where studno= ' 20050319001 ' and studscore>=90 and studscore<=100

--3.47 using the not and and operators to query all records from 80 to 90 for student scores

SELECT * from Studscoreinfo where not studscore>80 and studscore<=90

--3.48 using the not and or operators to query all records of the Student score table

SELECT * from Studscoreinfo where not studscore>80 or studscore<=90

--3.49 parentheses First, query result is empty

SELECT * from Studscoreinfo where not (studscore>80 or studscore<=90)

--3.50 track student scores from 70 to 80

SELECT * from Studscoreinfo where studscore between and 80

--3.51 track student scores not between 70 and 80

SELECT * from Studscoreinfo where studscore not between and 80

--3.52 inquiry number is 20050319001, scores in [90,100] all records between

SELECT * from Studscoreinfo where studno= ' 20050319001 ' and studscore between 100

--3.53 Check the record of the student score in [60,70] or the score in [80,90]

SELECT * from Studscoreinfo where studscore between and or studscore between and 90

--3.54 Inquiry number 20050319002 or 99070405 students basic information

SELECT * from Studinfo where Studno in (' 20050319002 ', ' 99070405 ')

--3.55 inquiry number not 20050319002 or 99070405 of students basic information

SELECT * from Studinfo where Studno not in (' 20050319002 ', ' 99070405 ')

--3.56 The basic information of students whose names begin with the word "Hu"

SELECT * from Studinfo where studname like ' Hu '

--3.57 Query the student's basic information that contains the word "text" in the name
SELECT * from Studinfo where studname like '%% '

--3.58 the second word of "Li" in the name of the student basic information

SELECT * from Studinfo where studname like ' _ Lai% '

--3.59 class information to query class description is empty

SELECT * from ClassInfo where Classdesc is NULL

--3.60 class information for class description not empty

SELECT * from ClassInfo where classdesc are not NULL

--3.61 statistics of all scores average

Select AVG (studscore) from Studscoreinfo

--3.62 statistics number is "20050319001" Student achievement average score

Select AVG (studscore) from studscoreinfo where Studno = ' 20050319001 '

--3.63 statistics number is "20050319001" student record number of records (i.e. course gate number)

Select COUNT (*) from studscoreinfo where Studno = ' 20050319001 '

--3.64 statistics number is "20050319001" Student score average, course gate number and assign Alias
Select AVG (Studscore) as Avgscore,coursecount=count (*)

From studscoreinfo where Studno = ' 20050319001 '

--3.65 statistics number is "20050319001" Student score score, highest score, lowest score, average score, course gate number
Select SUM (Studscore), MAX (Studscore), MIN (Studscore),
AVG (Studscore), COUNT (*), SUM (Studscore)/count (*) Avgscore

From studscoreinfo where Studno = ' 20050319001 '

--3.67 statistics of the average students

Select Studno,avg (Studscore) Avgscore from Studscoreinfo GROUP by Studno

--3.68 the average of each student, using the cast function to preserve the number of decimal digits
Select Studno,cast (AVG (Studscore) as numeric (4,1)) Avgscore

From Studscoreinfo GROUP by Studno

--3.69 statistics on the number of courses for each student

Select Studno,count (*) Coursecount from Studscoreinfo GROUP by Studno

--3.70 statistics on the average scores of students and the number of courses
Select Studno,count (*) Coursecount, CAST (AVG (Studscore) as numeric (4,1)) Avgscore

From Studscoreinfo GROUP by Studno

--3.71 statistics on the average scores of students, the number of courses, the average score and the average
Select Studno,sum (Studscore) as Sumscore,count (*) Coursecount,
CAST (AVG (Studscore) as numeric (4,1)) AvgScore1,
SUM (Studscore)/count (*) as AvgScore2

From Studscoreinfo GROUP by Studno

--3.72 query average of 80 or more student records, using the HAVING clause
Select Studno,sum (Studscore) as Sumscore,count (*) Coursecount,
CAST (AVG (Studscore) as numeric (4,1)) Avgscore
From Studscoreinfo GROUP by Studno

Having AVG (Studscore) >=80

--3.73 statistics students with a score of 80 or more are averaged, using the WHERE clause
Select Studno,sum (Studscore) as Sumscore,count (*) Coursecount,
CAST (AVG (Studscore) as numeric (4,1)) Avgscore
From Studscoreinfo where studscore>=80

GROUP BY Studno

--3.74 statistics The average score of 10 or more students, using the WHERE and having clauses
Select Studno,avg (studscore) as Avgscore
From Studscoreinfo where studscore<60
GROUP BY Studno

Having COUNT (*) >=10

--3.75 inquiry number for "20050319001" student record, and ranked by the high and low grades
Select *from studscoreinfo where Studno = ' 20050319001 '

ORDER BY Studscore DESC

--3.76 inquiry number for the "20050319001" student record, and ranked by the high and low grades, the same results by the course number in ascending order
SELECT * from studscoreinfo where Studno = ' 20050319001 '

ORDER BY Studscore Desc,courseid ASC

--3.77 The average score for each student, sorted by average and high and low,
Select Studno, avg (studscore) as average
from Studscoreinfo
Group by STUDNO&NB Sp
Order by average DESC