Multiple choice question:
1. According to the characteristics of relational model, which is the data model on which relational data is based, judge that the following correct items are: (_ _ _ _ _ _)
A, there is only one-to-many entity relationship, which is represented graphically.
B. Save data in a two-dimensional table structure, and duplicate rows are not allowed in the relational table.
C, can reflect the one-to-many and many-to-many relationship, but can not reflect the one-to-one relationship.
D. relational model database is the primary stage of database development.
2. There are two connection authentication methods in the Connection group. In (_ _ _) mode, when the client application needs to connect, it needs the user ID and password required for login.
A, Windows authentication B, SQL Server authentication
C. When logging in as superuser D. When logging in in other ways.
3. In the relational database, the primary key is (_ _ _), and there are (_ _ _) ways to create the primary key, which can be (_ _) when using Transact-SQL statements to create the primary key.
(1) a, which identifies the unique entity in the table.
Create a unique index and allow null values.
C, only the first field in the table can be created.
D. Allow multiple primary keys
A, B, C, D, D.
(3) a, create a table 1
(column 1 char( 13) is not empty,
column2 int not );
Modify table table 1
Constraint [PK_table 1] primary key is not clustered.
(column 1) is on the main server;
C, modify table table 1 column 1 primary key;
4. View is a common data object, and it is another way to provide (_ _ _) data, which can simplify database operation. When creating a view with multiple data tables, it is not allowed to include keywords such as (_ _ _) in this statement.
(1) A, inspection and storage b, inspection and concentration
C, insert and save d, and delete the update.
2 a, sorting basis, calculation
B, sorting basis, calculation, calculation basis
C, sorting basis, calculation basis, grouping basis
D, grouping basis, calculation basis
5. In Transact-SQL syntax, the complete syntax of the SELECT statement is very complicated, but it includes at least part (_ _). Keyword (_ _) can be used to mask duplicate rows, and the operator that returns multiple query results to a result set is (_ _ _). If the set function is used in the SELECT statement, it must be followed by (_ _).
(1) A, select, enter B, select, from
C, select, group d, select only
(2) a, difference b, joint
C, all c, top
(3) a, join b, joint
C, become c, LIKE
(4) A, grouping basis B, calculation basis
C, have d, calculate
1. Under WINDOWS98 operating system, 2. Only SQL Server2000 () can be installed.
A, Enterprise Edition B, Standard Edition C, Personal Edition D, Beta Edition
3. To connect to the SQL Server server, press 4. Server, 5. There are two connection authentication methods, 6. In (_ _ _) mode, 7. When the client application needs to connect, provide the user ID 8, ID and password required for login.
A, Windows authentication B, SQL Server authentication
C. When logging in as superuser D. When logging in in other ways.
9, use the query analyzer, 10, you can ().
A, directly execute SQL statement b, and submit the SQL statement to the server for execution.
C. use d as the enterprise manager and d as the service manager.
1 1, the default extension of the transaction log file 12, is ().
a、MDF B、NDF C、LDF D、DBF
13. By using file groups, 14. You can ().
A, improve the efficiency of accessing data. B, improve the efficiency of database backup and recovery
C, simplify the maintenance of the database D, ABC can be.
15, the main database of SQL Server is ().
A, master b, TEMPDB C, model d, MSDB
16, database capacity, 17, ().
A, only a fixed size can be specified. B, the lowest is 10M.
C, the maximum is 100m. D, which can be set as automatic growth.
18, the length of datetime data type is ().
a、2 B、4 C、8 D、 16
19, table is a very important data object in the database, 20, it is used to (_ _) various data contents, 2 1, tables can be created after the database is created, and 22, tables can be created by methods such as (_ _).
(1) A, display b, query c, store d and search.
(2) A, enterprise manager B, query analyzer
Enterprise manager and CREATE TABLE statement
23. The purpose of creating an index for a data table is (_ _). 24. When creating a table, you can use (_) to create a unique index.
(1) a, improve the retrieval performance of the query b, create a unique index.
C. create primary key d and classify it.
⑵ A, set primary key constraint and unique constraint b, create table and index.
C. set the primary key constraint, such as Create index D or higher.
25. In T-SQL syntax, 26. The command for inserting and updating data is (_ _ _).
A, insert, update b, update, insert
Delete, update d, create, insert
27. The 27.Primary key constraint is used to enforce the () integrity of data.
A, domain b, entity c, reference d and ABC are all acceptable.
28. On the server of SQL Server, 30. A stored procedure is a set of predefined (_ _) T-SQL statements.
A, save B, compile C, interpret D, and write.
3 1, instead of executing the trigger in the modify operation () that triggers the trigger.
A. after execution, B. when execution stops, C. at the same time, before D.
32, the return value of the aggregate function is ().
A, a scalar value b, a set of values c, expression d, table
Fill in the blanks:
33.Windows NT authorization authentication mode is only applicable to _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
36. The created rule must be _ _ _ _ _ _ _ to a column or a user-defined data type to take effect.
37. When using SELECT for fuzzy query, you can use _ _ _ _ _ _ _ operator, but you should use wildcards in the condition value, such as _ _ _ _ to match the query. Fuzzy queries can only be used for queries with field types similar to clause _ _ _ _ _ _ _.
4 1. The function to calculate the cumulative sum of fields is _ _ _ _ _ _, and the function to calculate the number of items is _ _ _ _ _ _.
43. The purpose of the server network utility is to create _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ and change the default clnp.
45. A view is a virtual table exported from one or more tables, and its contents are generated by the definition of _ _ _ _ _ _ _ _ _.
47. Through foreign key constraint, two related tables can be linked to realize the _ _ _ _ _ _ _ integrity of data.
49. The account number SA is _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
5 1 and SQL Server2000 are high-performance relational database management systems developed by Microsoft, which are closely integrated with _ _ _ _ _ _ _ _. This special combination enables SQL Server2000 to take full advantage of the operating system.
54. To access the SQL Server database, you must pass the security check. SQL Server2000 uses two layers of security mechanisms to confirm the legitimacy of users, including _ _ _ _ _ _ _ verification and _ _ _ _ _ _ verification.
57. The index types in SQL Server include unique index, primary key index _ _ _ _ _ _ _ and clustered index.
58. Service Manager can be used for _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ SQL Server 2000 server and other operations.
62.SQL Server2000 is a high-performance relational database management system developed by Microsoft. It is closely combined with _ _ _ _ _ _ _ _. This special combination enables SQL Server2000 to take full advantage of the operating system.
65. The physical files stored in the database disk include: _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
66. When creating a database, data files are stored in the main _ _ _ _ _ _ _ filegroup by default.
68. There are four types of plastic data: _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ and _ _ _ _ _ _.
69. Data integrity can be divided into entity integrity, domain integrity, referential integrity and user-defined integrity.
70. Through foreign key constraint, two related tables can be linked to realize the referential _ _ _ _ _ _ _ integrity of data.
72. A view is a virtual table derived from one or more tables, and its contents are defined and generated by selecting a query to _ _ _ _ _ _ _ _ _.
74. The command to delete a table is _ _ _ _ _ _ _ _.
76. Triggers can implement _ _ _ _ _ _ _ _ _ _ _ _ _ _ which is more complicated than checking constraints.
78. The three actions to trigger the trigger are: _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
79. If a batch contains syntax errors, the whole batch cannot be _ _ _ _ _ _ _ successful.
82. Transactions can be used to ensure that data can be modified correctly. Trading has four characteristics, collectively referred to as _ _ _ _ _ _ _.
True or false (10)
1, () In the CREATE INDEX statement, use CLUSTERED to create a clustered index.
2. () In Transact-SQL statements, identifiers that do not meet all the rules of identifiers must be separated.
3. () In SQL SERVER, the replacement trigger is executed before inserting, updating or deleting data.
4. () Each stored procedure returns an integer return code to the caller. This data represents the execution of the stored procedure.
5. () Because data can be inserted, modified or deleted through the view, the view is also a virtual and real table, and SQL SERVER saves it in the syscommens system table.
6. () The user-defined inline table-valued function is essentially a parameterized view.
7 () When a user who logs in to WINDOWS connects to SQL Server, the user does not need to provide an account of SQL Server. This authentication mode is a WINDOWS authentication mechanism.
8 () After connecting to the server with Enterprise Manager, all the objects you see are physical files stored on disk.
9 () When writing database objects, data can only be written to one file in the group.
10 () You can modify the name of the database in Enterprise Manager.
1 1 () IN the WHERE clause of a T-SQL statement, the in subquery can completely replace the OR logical expression.
12 () The wildcard "_" represents a single character.
13 () domain integrity is also called referential integrity.
After the default value 14 () is bound to a column, the data on the column will be fixed.
15 () Only one clustered index can be created on a table.
16( )*** Having a lock does not prevent others from modifying the data.
Operation coding problem
Now it is necessary to establish a database for the school. The school environment is as follows: a department has several majors, and each major only recruits one class a year, and each class has several students. Now it is necessary to establish a database about departments, students and classes. The relational model is:
Students (student number, name, gender, age, class number)
Class (class number, specialty name, department name, school year)
Department (department number, department name)
Try to complete the following functions in SQL language:
To build a table, you need to declare in the definition:
(1) Primary foreign key of each table.
(2) The students are between 15-40 years old.
(3) The student name cannot be empty.
(4) Only when there are no students in the class can the class be deleted.
2 Insert the following data
Category (
10 1, software, computer,1995; 102, microelectronics, computer,1996;
1 1 1, inorganic chemistry, chemistry,1995; 1 12, polymer chemistry, chemistry,1996;
12 1, statistical mathematics, mathematics,1995; 13 1, modern language, Chinese,1996;
14 1, international trade, economy,1997; 142, international finance and economy,1996;
)
Students (
8 10 1, Zhang San, 18,10/; 8 102, Si Qian, 16,121;
8 103, Wang Ling, 17,131; 8 105, Li Fei, 19,102;
8 109, Zhao Si, 18,141; 8 1 10, Li Ke, 20, 142
820 1, Zhang Fei, 18,11; 8302, Zhou Yu, 16,112;
8203, Wang Liang, 17,11; 8305, Dong Qing, 19,102;
8409, Zhao Long, 18,101; 85 10, Lili, 20, 142)
3 Complete the following query functions
(1) Find out all the students surnamed Li, and sort them in descending order of age.
(2) List the names of all departments offering more than two majors.
(3) List the numbers and names of departments with more than 2 students.
4. Create a trigger in the category table. When a class number record is deleted, the corresponding class number record in the student table will be automatically deleted.
5. Establish a function that can return the number of students in the class.