SQL Server Exam Questions

One, the choice question (1-25 no question 1 points 26-30 each question 2 points altogether 35 points)
1) Assume that there is a user table that contains fields: userid (int), username (varchar), password (varchar), etc., the table needs to set the primary key, the following is correct (). (select two items)
A) If there are no simultaneous username and password, then username and password can be grouped together as the primary key.
b) When designing the primary key for this table, it is best to use UserID as the primary key, depending on the minimum principle of the primary key selection.
c) When designing the primary key for this table, it is best to use username and password as key combinations, depending on the minimum principle of the primary key selection.
D) If the UserID is used as the primary key, then the value entered in the UserID column is allowed to be null.

2) The sample user database that Microsoft SQL Server 2000 created when it was installed includes (). (select two items)
a) Pubs
B) Master
C) Northwind
D) Msdb
3) The following () statement extracts the first 10 records from table table_name. (select an item)
a) Select from table_name where rowcount=10
B) Select top ten from table_name
C) SELECT top of 10 from table_name
D) The select from table_name where rowcount<=10
4) is queried from the Products table for a product with a price higher than the product name "disposable Paper cup", This SQL statement is (). (select an item)
a) Select from product where price > ' Disposable paper cup '
B) SELECT from product where price > (SELECT from product where product name > ' Disposable paper cup '
C) Select from product where EXISTS product name = ' Disposable paper cup '
D) select from product where price > (SELECT price from product where Product name = ' Disposable Paper cup '
5) find the first digit of all phone numbers (column name: Telephone) in the student table is 8 or 6, and the third digit is 0 phone number (). (select an item)
a) Select telephone from student where telephone like ' [8,6]%0 '
B] SELECT telephone from student WHERE Tel Ephone like ' (8,6) 0% '
C) Select telephone from student WHERE telephone like ' [8,6]_0% '
D] Select telephone from S Tudent WHERE telephone like ' [8,6]_0 '

6) Existing table book, field: ID (int), title (varchar), price (float); Where the ID field is set to identity and the INSERT statement inserts data into the Book table, the following statement is wrong (). (Select an item)
A) insert into book (Id,title,price) VALUES (1, ' Java ', 100)
b) insert into book (Title,price) VALUES (' Java ', 100)
c) insert into book values (' Java ', 100)
d) Insert book values (' Java ', 100)

7) Existing table employee, field: ID (int), firstname (varchar), lastname (varchar); The following SQL statement is wrong (). (Select an item)
A) Select Firstname+ '. ' +lastname as ' name ' from employee)
b) Select firstname+ '. ' +lastname= ' name ' from employee
c) Select ' Name ' =firstname+ '. ' +lastname from Employee
d) Select Firstname,lastname from employee

8) in SQL Server 2000, the error with respect to the database assertion is (). (Select an item)
A) The default storage location for the database on disk is: SQL Server installation path/microsoft SQL Server/mssql/data
b) A database should contain at least one database file (. mdf) and one transaction log file (. ldf)
c) The database can be shrunk only if no data exists in the database.
d) You can copy database files and transaction log files from a machine to B machine, and then implement the database from a machine to B machine by performing the corresponding additional database operation on the B machine.

9) To delete all the data in the Book table, the following statement is wrong (). (select two items)
A) TRUNCATE TABLE book
b) Delete * from book
c) DROP table book
d) Delete from book

10) There is a field score (float) in the Student score table grade, now to increase the score by 5 points for all points between 55 and 60, the following SQL statement is correct (). (select two items)
A) Update grade set score=score+5
b) Update grade set score=score+5 where score>=55 or score <=60
c) Update grade set score=score+5 where score between and 60
d) Update grade set score=score+5 where score >=55 and score <=60

11) Existing bibliography table book, containing fields: price (float); Now to query the details of a book's highest-priced bibliography, the following statement is correct (). (select two items)
A) Select top 1 from Book ORDER BY Price ASC
b) Select top 1 from book ORDER BY price DESC
c) Select top 1 from book where price= (select Max (price) from book)
d) Select top 1 from book where price= Max (price)

12) Existing bibliography table book, containing fields: price prices (float), category type (char); Now query the average price, category name for each category, and the following statement is correct (). (Select an item)
A) Select Avg, type from book Group by type
b) Select count (Price), type from book GROUP by Price
c) Select AVG (price), type from book GROUP by Price
d) Select count (Price), type from book Group by type

13) Query The student table for all non-empty email messages, the following statement is correct (). (Select an item)
A) Select email from student where email!=null
b) Select email from student where email isn't is null
c) Select email from student where email <> null
d) Select email from student where email was not null

14) score table Grade field score represents fractions, the following () statements return the lowest score in the score table. (select two items)
A) Select Max (score) from grade
b) Select top 1 score from grade order by score ASC
c) Select min (score) from grade
d) Select top 1 score from grade ORDER by score Desc

15) Existing order form orders, including user information UserID, product information ProductID, the following () statement can return at least two back to the ProductID? (Select an item)
A) Select ProductID from Orders where count (ProductID) >1
b) Select ProductID from Orders where Max (ProductID) >1
c) Select ProductID from Orders where have count (ProductID) >1 GROUP BY Productid_
d) Select ProductID from Orders GROUP by ProductID have Count (ProductID) >1

16) regarding the aggregation function, the following statement is wrong (). (Select an item)
A) sum returns all the sums in the expression, so it can be used only for columns of numeric types.
b) Avg Returns the average number of expressions in the expression, which can be used for both numeric and date-type columns.
c) Max and Min can be used for character-type columns.
d) count can be used for character-type columns.

17) Use the following () to not make a fuzzy query. (Select an item)
A) OR
b) Not between
c) Not in
D) Like

18) for multi-table join queries, the following () description is incorrect. (Select an item)
A) The number of result set rows returned by the outer join query may be greater than the number of result set rows that meet the join criteria.
b) Multiple table join queries must use the JOIN keyword
c) The result returned by the INNER join query is all data that conforms to the join condition.
d) Specify the join condition in the WHERE clause to implement an inner join query.

SQL statement: SELECT * from students where SNO like ' 010[^0]%[a,b,c]% ', the SNO that may be queried is (). (select two items)
A) 01053090A #Hm3?
b) 01003090a01
c) 01053090d09
D) 0101a01

20) About TRUNCATE TABLE, the following () description is incorrect. (select two items)
A) Truncate table can be deleted with the WHERE clause, depending on the condition.
b) Truncate table is used to delete all data in the table.
c) The trigger is not valid for TRUNCATE table.
d) Delete is faster than TRUNCATE table.

21) Create a new table named ' Customers ' and require that all records of table ' clients ' be included in the new table, and the SQL statement is (). (Select an item)
A) Select into customers from clients
b) Select into customers from clients
c) Insert into customers select from clients
d) Insert Customers select * from clients

22) for the primary key, the following () statement is wrong. (select two items)
A) The primary key can be used to ensure that no duplicate rows of data exist in the table.
b) A table must have a primary key.
c) A table can have only one primary key.
d) You can set the primary key only for integer columns.

23) Assuming the order form orders are used to store order information, the CID represents the customer number, money represents a single order amount, and now the order number of each customer and the total amount of each customer's subscription are to be queried, the following () SQL statement can return the correct result. (Select an item)
A) Select Cid,count (DISTINCT (CID)), SUM (money) from the orders group by CID
b) Select Cid,count (DISTINCT (CID)), SUM (money) from the orders order by CID
c) Select Cid,count (CID), SUM (money) from the orders order by CID
d) Select Cid,count (CID), SUM (money) from the orders group by CID

24) Existing Customer table customers (primary key: Customer number CID), contains 10 rows of data, order form orders (foreign key: customer number CID), contains 6 data. Execute SQL statement: SELECT * FROM Customers right outer join orders on CUSTOMERS.CID=ORDERS.CID. Returns a maximum of () records. (Select an item)
A) 10
b) 6
c) 4
d) 0

25) The following description is not correct
A) The stored procedure can achieve a faster execution speed.
b) An inline table-valued function is equivalent to a view with parameters.
c) When you do not specify an owner, an error occurs when you call a scalar function.
d) Any base table can be changed through the view

Your database stores telephone numbers. Each telephone number was stored as an integer. You must format, the telephone number to print on a, report in the following format:
(999) 999-9999
You are selected the phone number into a local variable as follows:
DECLARE @PhoneNumber int which statement would correctly format the number?
A) SELECT ' Phone number ' = ' (' + SUBSTRING (CONVERT (varchar), @PhoneNumber), 3,0) + ') ' + SUBSTRING (CONVERT (varchar (10), @ Phonenurnber), 3,3) + '-' + SUBSTRING (CONVERT (varchar), @PhoneNumber), 4,6)
b) SELECT ' Phone number ' = ' (' + SUBSTRING (CONVERT (varchar), @PhoneNuwber), 3,1) + ') ' + SUBSTRING (CONVERT (Varcher (10), @ PhoneNumber), 3,4) + '-' + SUBSTRING (CONVERT (varchar), @PhoneMumber), 4,7)
c) SELECT ' Phone number ' = ' (' + SUBSTRING (CONVERT (varchar), @PhoneNumber), 0,3) + ') ' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 3,3) + '-' + SUBSTRING (CONVERT (varchar), @PhoneNurtiber), 6,4)
d) SELECT ' Phone number ' = ' (' + SUBSTRING (CONVERT (varchar), @PhoneNumber), 1,3) + ') ' + SUBSTRING (CONVERT (varchar (10), @ PhoneNumber), 4,3) + '-' + SUBSTRING (CONVERT (varchar), @PhoneNumber), 7,4)

27) You is a database developer for Wide World Importers. You is creating a database that would store order information. Orders would be entered in a Client/server application. Each time a new order was entered, a unique order number must be assigned. Order numbers must is assigned in ascending order. An average of orders would be entered each day. You create a new table named Orders and add an ordernumber column to the This table. What's should do next?
a) Set the data type of the column to uniqueidentifier.
B) Set the data type of the column to Int., and set the IDENTITY property for the column.
C) Set the data type of the column to int. Create a user-defined function, selects the maximum order number in the table.
D) Set the data type of the column to int. Create a nextkey table, and add a nextorder column to the table. Set the data type of the Nextorder column to int. Create a stored procedure to retrieve and update the value held in the N Extkey.

Creating a script that would execute this stored procedure. If the stored procedure executes successfully, it should report the Year-to-date sales for the book title. If the stored procedure fails to execute, it should the following message:
"No Sales Found"
How should do you create the script?
a) DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ' Net etiquette ', @ytd
IF @retval < 0
PRINT ' No sales found '
ELSE
PRINT ' year to date sales: ' + STR (@ytd)
GO
b) DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ' Net etiquette ', @ytd OUTPUT
IF @retval < 0
PRINT ' No sales found '
ELSE
PRINT ' year to date sales: ' + STR (@ytd)
GO
c) DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ' Net etiquette ', @retval OUTPUT
IF @retval < 0
PRINT ' No sales found '
ELSE
PRINT ' year to date sales: ' + STR (@ytd)
GO
d) DECLARE @retval int
DECLARE @ytd int
EXEC @retval = Get_sales_for_title ' Net etiquette ', @ytd
OUTPUT
IF @retval < 0
PRINT ' No sales found '
ELSE
PRINT ' year to date sales: ' + STR (@ytd)
GO

You is a database developer for the insurance company. Information about the company's insurance policies is stored in a SQL Server database. You create a table named policy for this database by using the script shown below:
CREATE TABLE Policy
(
Policynumber int not NULL DEFAULT (0),
Insuredlastname CHAR (+) not NULL,
Insuredfirstname CHAR () not NULL,
Insuredbirthdate Dattime not NULL,
Policydate datetime not NULL,
Faceamount money is not NULL,
CONSTRAINT pk_policy PRIMARY KEY (policynumber)
)
Each time the company sells a new policy, the policy must is assigned a unique policy number. The database must assign a new policy number when a new policy is entered. What should?
A) Create an INSTEAD of inserts trigger to generate a new policy number, and include the policy number in the data INSTEAD I Nto the table.
b) Create an INSTEAD of UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into the table.
c) Create an after UPDATE trigger to generate a new policy number, and include the policy number in the data inserted into The table.
d) Replace the DEFAULT constraint with a after INSERT trigger that generates a new policy number and includes the policy nu Mber in the data inserted into the table.

A database developer for a marketing firm. You have designed a quarterly sales view. This view joins several tables and calculates aggregate information. You create a view. You want to provide a parameterised query to access the data contained in your view. The output would be used in other SELECT lists. How should accomplish this goal?
A) Use an ALTER VIEW statement to add the parameter value to the view definition.
b) Create A stored procedure that accepts the parameter as input and returns a rowset with the result set.
c) Create a scalar user-defined function that accepts the parameter as input.
D) Create An-inline user-defined function that accepts the parameter as input.

Two, fill in the blanks (each empty a total of 25 points)
1, the relationship R is 1NF, when and only if all the underlying fields contain only____。
2, the primary keyword should have____Of__Of__Of
3, the relationship R is 3NF, when and only if R is 2NF, and all non-PK attributes are__to PK.
4. The Transact-SQL language containslanguage,Language language.
5. The keyword to eliminate column duplication is UseThe _ operator creates a single result set from multiple queries, and the statement that submits a transaction is ____ .
6. Two licensing modes for SQL Server are ___ And ___.
7, there is a data table with a row of 5KB, there are 10000 rows of data, then we need at least __m of database space.
8, data integrity is divided into __,__,__.
9, each allowed to have _ Cluster index,_ index is the default choice for SQL Server.
10, you can use the __ keyword to display the unencrypted stored procedure information, the key to execute the stored procedure is _ , the aggregate function of the statistical column mean is____, modify the object's keyword to_, Delete object's keyword is ___。
11.____The database holds all temporary tables and temporary stored procedures.

Three, Jane Answer (15 points)
1, please briefly describe the second paradigm (3 points)
2. Please briefly describe how the update trigger works (3 points)
3. When referential integrity is enforced, describe the actions that SQL Server prohibits users (3 points)
4, briefly describe the relevant sub-query steps (3 points)
5. Briefly describe the reasons for using indexes and not using indexes (3 points)
On the problem (25 points)
One, (12 points) Description of the problem:
Known relational pattern:
S (sno,sname) student relationship. SNO for school number, SNAME for name
C (cno,cname,cteacher) course relationship. CNO as course number, CNAME as course name, Cteacher as Instructor
SC (sno,cno,scgrade) course selection relationship. Scgrade as a result

1. Find out the names of all students who have not been enrolled in the "Li Ming" teacher course (4 points)

2. Names of students with two or more (including two) failed courses and their average score (4 points)

3. List all students who have studied the course "1" and who have studied "2" (4 points)
   Second, define a decimal conversion to 2 binary scalar function. (4 points)
   In the current deposit, "depositors" are contacted by "Deposit and withdrawal form" and "Savings Bank". It is assumed that depositors include: Account number, name, telephone number, address, deposit amount; the "Savings Bank" includes: the savings fund, name, telephone, address (assuming a depositor can deposit and withdraw funds at a different savings bank)
   1, write the statement of the above table (4 points)
   2. Create a trigger TR1 complete the following:
   When inserting data into the "Deposit slip" table, if the access flag =1 you should change the depositors table so that the deposit amount plus the amount of access, if the access Mark =0 should change the depositor table so that the deposit amount minus the access amount, if the balance is insufficient to show the insufficient balance error. (5 points)

SQL Server Exam Questions