Database test Questions

First, a brief question (5 points per question)

1, Tables: Table (Id,class,score), with the most efficient and simplest SQL to list the highest scores of classes, showing the class, the results of the two fields.

2, indicating: Student

Name	Course	Score
Zhang Qing	Chinese	72
Simon	Mathematical	72
Zhang hua	English	81
Zhang Qing	Physical	67
Zhang Li	Chemical	98
Zhang Yan	Physical	70
Zhang Qing	Chemical	76

Find out the student information of "Zhang" student whose average score is greater than 75 points.

3,    College Entrance Examination Information sheet: Student

valign= width= "Top" []

ID	A	B	C	D	E
2006001	108	119	98	127	136
2006002	149	"			129

Given the total score of the college entrance examination at more than 600, and did not fail (less than 60 points) of the student ticket number.

4, table name: table2

Shopping people	Product Name	Number
Name	Con_name	Number
A	A	2
B	B	4
C	C	1
A	Ding	2
B	C	5

Give all purchased items to two or more shopper records.

5, Table Table3

C1	C2
2005-01-01	1
2005-01-01	3
2005-01-02	5

Request result Data

2005-01-01	4
2005-01-02	5

Try a SQL statement to complete.

6, such as the following table (T2) for a certain class of a semester of mathematical results;

School Number	Results
199801001	85
199801002	76
199801003	98
199801004	54
199801005	39
199801006	61

Question: Please output the following two lines of data according to the contents of the T2 table, requiring the simplest SQL statement.

Number of categories

================

Pass 4

Fail 2

7. Avoid using "*" in the SELECT query statement.

8, what is a partitioned table? Describes the differences between range partitioning and list partitioning, and what are the main advantages of partitioned tables.

9, briefly describe the efficiency of exists and in.

10, compare Truncate,delete,drop commands.

11, briefly describe the understanding of the index, and the reasons for using the index.

12, List A few views you know.

Second, the analysis of the coding problem (40 points)

(1) Please use the SQL statement to create the following table Table6, which are structured as follows: (10 points)

Id	varchar (30)	Identification number	Set as primary key
Code	varchar (7)	Product Code	is not empty
Codename	varchar (30)	Product Name	is not empty
Spec	varchar (20)	Product Specifications	is not empty
Price	Number (10,2)	Price	is not empty
Sellnum	Int	Sales Quantity
Deptmo	varchar (3)	Branch code SOLD
Selldate	Date	Sales Time

(2) Insert an identification number of "01", the product is encoded as "010", the product name is "Telephone", the product specification is "CZ011", the price is 2300, the sales volume 2, sold the branch code is "12", the sales Time 2002-3-15 records. (5 points)

(3) Write the query sales period between 2002-2-15 to 2002-4-29, the sale of the branch code is 01 of all records. (10 points)

(4) Add the "no_id" field in the Table6 data table, the data type varchar (12) is not empty, and set it to a unique constraint, write out its command statement. (7 points)

(5) Delete the record with the identification number 01. (8 points)

Database test Questions