ACCP7.0 optimization myschool Database Design test Paper Summary

In SQL Server, indexing a database table can be (C). Index: is an internal method of SQL Server orchestration data, which is a direct channel for retrieving data in a table

The role of indexing: greatly improve the database retrieval speed, improve database performance

A. Preventing illegal deletion operations

B. Preventing illegal insertion operations

C. Improve query performance

D. Saving disk space on the database

2) in SQL Server, create a table using the (C) statement.

A. INSERT add Data

B. Drop delete database, table, constraint, view, index, stored procedure

C. CREATE DATABASE, tables, views, indexes, stored procedures

D. ALERT create constraints or modify stored procedures

3) in SQL Server, the following statement about the view is wrong (C).

A. Most operations performed on a query can also be performed on a view

B. Using views to increase the security of a database

C. Cannot use views to increase, delete, modify data in the database in order to ensure the security of the database, try not to use the view for the pruning operation

D. View gives users more flexibility to access the data they need

4) in the database design, use the E-r graph to describe the information structure but not the representation of the information in the computer, it is the (B) stage of the database design.

A. Demand Analysis

B. Overview design

C. Logical design

D. Physical Design

5) in a SQL Server database, if you force a learner in the grades table to be a learner that exists in the students table, the following description is correct (A).

A. Creating a foreign KEY constraint in the grades table creates a connection between the grades table and the students table, creating a FOREIGN KEY constraint

B. Creating a Check constraint in the grades table

C. Creating a unique constraint in the grades table

D. Creating a DEFAULT constraint in the grades table

6) in the SQL Server database, the following T-SQL statement segment executes the third sentence when an error occurs, and the content in the database is modified (C).

BEGIN TRANSACTION

Update account set balance=7800 where id=1 the third sentence has an error, execution rollback tran rollback of the transaction, then all update data after the BEGIN TRAN statement is rolled back to the initial state

Update account set balance=9000 where id=1

IF @ @error <> 0

ROLLBACK TRANSACTION

ELSE

COMMIT TRANSACTION

A. The value of the Balance field in the record with ID 1 in the Account table is modified to 7800

B. The value of the Balance field in the record with ID 1 in the Account table is modified to 9000

C. The value of the Balance field in the record with ID 1 in the Account table does not change

D. This cannot happen

7) in the SQL Server database, the function of the following T-SQL statements is correct (C).

DECLARE @i int, @J int

SET @i=0

while (@i < 2) specify loop two times

BEGIN

SELECT @j=count (*) from Stumarks WHERE writtenexam<60 Statistics The number of students who failed the exam

IF (@j>0)

UPDATE stumarks SET writtenexam=writtenexam+5 WHERE writtenexam<60 add 5 points to a failed student

ELSE

Break

SET @i = @i + 1 Change the loop variable, @i to 1 to continue the cycle again!

END

SELECT Stuno, score =case

When writtenexam<60 then ' failed '

ELSE ' Pass '

END

From Stumarks

A. Repeat 5 points to the failing student until it is passed, and finally show the status of all the students passing.

B. Give 10 points to the failing student, and finally show the information of the students ' passing.

C. 5 points for failing trainees, 5 points for those who failed after the points were made, and a final demonstration of the status of the students ' passing.

D. Give 5 points to the failing student, and finally show the information of all the students ' passing

8) in the SQL Server database, the following statement that calls the stored procedure is wrong (B).

A. EXEC Proc_stu @sum output,64,78

B. EXEC proc_stu @sum OUTPUT, @total output,78

C. EXEC Proc_stu @sum output,604

D. EXEC Calculate @sum output, @total output

9) in SQL Server, the following statement about subqueries you think is correct (C).

A. SQL statements that apply simple subqueries are less efficient than implementations with SQL variables

B. The query execution order of the tape query is to execute the parent query before executing the subquery

C. Table joins can generally be replaced with subqueries, but some subqueries cannot be replaced with table joins all table joins can be replaced as subqueries, but not all subqueries can be replaced as table joins

D. If a subquery returns a value of two fields at a time, the NOT EXISTS keyword should be used in the WHERE clause of the parent query

10) in SQL Server, the correct SQL statement to create the learner table as per the following requirements is (D).

The Learner's table (stutable) requires a 5-digit number, an automatic number, a maximum of 4 characters, and a maximum of 18 digits for the ID number.

A. CREATE TABLE stutable (identity : self-incrementing identity (identity seed, representing increment) numberic (18,0) refers to length 18, decimal point There's no one in the back.

ID NUMERIC (6,0) is not NULL,

Name VARCHAR (4),

Card INT

)

B. CREATE TABLE stutable

(

ID INT IDENTITY (10000,1),

Name VARCHAR (4),

Card DECIMAL (18,0)

)

C. IF EXISTS (SELECT * from sysobjects WHERE name= ' stutable ')

DROP TABLE stutable

GO

CREATE TABLE stutable

(

ID NUMERIC (4,0) is not NULL,

Name VARCHAR (4),

Card INT

)

D. IF EXISTS (SELECT * from sysobjects WHERE name= ' stutable ')

DROP TABLE stutable

GO

CREATE TABLE stutable

(

ID INT IDENTITY (10000,1),

Name VARCHAR (8),

Card NUMERIC (18,0)

)

11) In SQL Server, declare and create the following stored procedure, the statement that correctly calls the stored procedure is (BC).

CREATE PROCEDURE PRO

@passNum int OUTPUT,

@passPoint int=60

As

Select @passNum =count (*) from stutable, Where point > @passPoint

GO

A. Declare @sum int

EXEC PRO @passNum, 70

B. Declare @sum int input parameter has a default value, so you can choose the input parameter when you call the stored procedure, or you can use the default value of the input parameter.

EXEC PRO @sum output,70

C. Declare @sum int

EXEC PRO @sum Output

D. EXEC PRO 70

12) The four phases described below are not the stages that are experienced during the database design process (D).

A. Phase of the demand analysis

B. Outline design phase

C. Detailed design phase

D. Code design phase

13) in SQL Server, the in statement is reversed by adding (B) in front of.

A. NONE

B. Not

C.!

D. NO

14) in the SQL Server database, the following definition of the variable is wrong (C).

A. DECLARE @username varchar defines the temporary variable as the @ variable name, and the global variable has two @@ before it

B. DECLARE @RowCount varchar (30)

C. DECLARE @ @username varchar (10)

D. DECLARE @ @RowCount varchar (30)

15) In SQL Server, you typically use (B) to represent the attributes of an entity when drawing a e-r diagram.

A. Rectangle rectangle : solid ellipse: Attribute Diamond: Relationship

B. Ellipse

C. Diamond

D. Triangles

16) in SQL Server, run the following statement, and the output is (B).

Create proc Proc_search

@myStuno varchar (8) is null compile error here, should be changed to "="

As

If @myStuno = null

Begin

print ' You forgot to pass the school number '

Return

End

Select * from Stuinfo where [email protected]

Go

EXEC Proc_search

A. You forgot to pass the school number

B. Compilation errors

C. Displaying an empty record

D. Calling a stored procedure Proc_search error

17) The following actions about variables in SQL Server are correct (AD).

A. DECLARE @name varchar (8)

SET @name = ' LKL '

print ' name is ' [email protected]

B. DECLARE name varchar (8)

SET name= ' LKL '

print ' name is ' +name

C. Print @ @VERSION as ' version ', @ @SERVERNAME as ' server '

D. SELECT @ @VERSION as ' version ', @ @SERVERNAME as ' server '

18) The statement in the following option about the logical control statement used in the SQL statement is correct (A).

A. In the If-else condition statement, if is required, and ELSE is optional

B. In the If-else condition statement, the statement block uses {} to use Begin and end, and in the case branch statement the End,while loop condition is true, repeating the loop

C. The Else branch is not available in the case multi-branch statement

D. Repeating the loop statement when the condition is false in the while loop statement

19) Known Dept table has the department number field Deptno, the Department name Field Dname, the Employee table EMP has the Employee Number field empno, the employee last name segment ename, the Phone field phone and the department number field Deptno, This field refers to the Deptno field of the Dept table, and to query the number and name information for all employees in the "Research and Development department" using SQL statements, the correct one in the following option is (B).

A. SELECT Empno,ename from EMP

where empno= (select Empno from dept WHERE dname= "Development Department")

B. SELECT empno,ename from EMP use subqueries do not confuse tables

where deptno= (SELECT deptno from dept WHERE Dname= ' Development Department ')

C. SELECT Empno,ename from EMP

where deptno= (SELECT * from dept WHERE dname= ' Development Department ')

D. SELECT Empno,ename from Dept

where deptno= (select Deptno from emp WHERE dname= "Development Department")

20) in SQL Server, the following is a description of the view, which is said to be correct (AD).

A. Use a view to filter data from the original physical table, and to increase the security view of data access in order to ensure that the integrity of the data as far as possible do not increase the operation of views, you can multi-table check!

B. A view is a virtual table, and the data can only come from one original physical table

C. CREATE View statement can contain the UPDATE statement

D. For security purposes, the query operation is generally only performed on the view, and it is not recommended to perform modifications on the views

21) in SQL Server, create the following stored procedure:

Create proc Stu_exam

@writtenExam Int=null

As

If @writtenExam is null

Begin

print ' Please enter the written test pass line '

Return

End

SELECT * FROM student where stu_id in

(Select stu_id from Stu_marks

where writtenexam> @writtenExam)

GO

The following options are correct (C). If the condition is filtered, the output will be indicated if the written result is not output.

A. Executing the EXEC stu_exam statement, the console displays all student information recordsets that have passed the written exam

B. Stored procedure (stu_exam) code has a syntax error

C. Execute the EXEC stu_exam statement, the console displays "Please enter the written test score pass Line"

D. Execute EXEC Stu_exam 75 statement, console display "Please enter the written test score pass Line"

22) Logical control statements in the following options that are not part of SQL Server are (B).

A. If-else statement

B. For Loop statement

C. Case clause

D. While loop statement

23) in SQL Server, the description for clustered and nonclustered indexes is wrong (B).

A. A table can have multiple nonclustered indexes, but only one clustered index

B. The value order of the nonclustered index is exactly the same as the physical order of the records in the data table the nonclustered index causes the physical order of the rows in the table to be stored in a logical order that does not match the key values!

C. Duplicate values are not allowed on a column that is set up for a clustered index

D. Using a clustered index query faster than a nonclustered index

24) in SQL Server, the following statement about system stored procedures is wrong (D).

A. System stored procedures provide a mechanism for managing databases and updating tables

B. All system stored procedures start with sp_

C. All system stored procedures are kept in the master database

D. Users cannot update system tables with system stored procedures, only query system tables

25) in the SQL Server database, the query statement for the product that has a price higher than pname (the product name) for the "Disposable paper cup" in all records from the product table is (D).

A. SELECT * FROM Product WHERE max (price) > ' Disposable paper cups '

B. Select * FROM product where price> (SELECT Max (*) from product where pname= ' disposable paper cups ') aggregate function sum is sum, must contain a column in the packet

C. SELECT * FROM product WHERE EXISTS pname= ' Disposable paper cups '

D. Select * FROM product where price> (SELECT max (price) from product where pname= ' disposable paper cups ')

ACCP7.0 optimization myschool Database Design test Paper Summary