Interview Questions of an enterprise

I. multiple choice questions:
1. In ms SQL Server, the stored procedure used to display database information is ()
A sp _ dbhelp
B sp _ db
C sp _ help
D sp _ helpdb

2. In SQL, the command to delete a table is ()
A DELETE
B DROP
C CLEAR
D REMORE

3. In relational databases, the primary key is (__)
A. It is A unique entity in the identification table.
B. Create a unique index and allow null values
C. Only the first field in the table can be created.
D. If multiple primary keys are allowed

4. In the Transact-SQL syntax, the complete syntax of the SELECT statement is more complex, but at least the part included (1 ___) can be shielded by the keyword (2, the operator that returns multiple query results to a result set is (3 ___). If an aggregate function is used in a SELECT statement, it must be followed by (4 ___).
(1) A, SELECT, into B, SELECT, FROM
C. SELECT, group d, SELECT only
(2) A, distinct B, UNION
C, all c, TOP
(3) A, join B, UNION
C, into c, LIKE
(4) A, group by B, COMPUTE
C, having d, COMPUTE

5. The 25 in the statement dbcc shrinkdatabase (Sample, 25) indicates
A, 25 M
B. The remaining space accounts for 25% of the total space
C. Occupied Space accounts for 25% of the total space
D. None of the above

6. You are a database developer of an insurance company. The company's Policy information is stored in the SQL Server 2000 database. You have created a table named Policy using the following script:
Create table Policy
(
PolicyNumber int not null default (0 ),
InsuredLastName char (30) not null,
InsuredFirstName char (20) not null,
InsuredBirthDate datetime not null,
PolicyDate datetime not null,
FaceAmount money not null,
CONSTRAINT PK_Policy primary key (PolicyNumber)
)
Every time a company sells a Policy, a new record is added to the Policy table and a new Policy number is assigned to it. What do you do?

A. Create an instead of insert trigger to generate a new policy number and INSERT the policy number into the data table.
B. Create an INSTEAD OF UPDATE trigger to generate a new policy number and insert it into the data table.
C. Create an after update trigger to generate a new policy number and insert the policy number into the data table.
D. Use the after update trigger to replace the DEFAULT constraint to generate a new policy number and insert the policy number into the data table.

7. In SQL, if you want to create a payroll containing employee ID, name, and title. Salary and other fields. To ensure that the value of the salary field is no less than 800 yuan, the most appropriate implementation method is:
A. Create a default value for the "salary" field when creating a payroll table
B. Create check constraints for the "salary" field when creating a payroll
C. Create a trigger in the payroll
D. Write a program for payroll data input for control

8. In the Select statement, the character used to connect strings is ______.
A. "+" B. "&" C. "|" D. "|"

9. You are a database developer at a publishing company and have created the following storage process for the daily sales of specific titles:
Create procedure get_sales_for_title
Title varchar (80), @ ytd_sales int OUTPUT
AS
SELECT @ ytd_sales = ytd_sales
FROM titles
WHERE title = @ title
IF @ ROWCOUNT = 0
RETURN (-1)
ELSE
RETURN (0)
In addition, a script is created to execute this stored procedure. If the execution is successful, a report corresponding to the name of the book is returned for the daily Sales status. If the execution fails, "No Sales Found" is returned ", how to create this 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 etid', @ ytd OUTPUT
IF @ retval <0
PRINT 'no sales Found'
ELSE
PRINT 'year to date sales: '+ STR (@ ytd)
GO

10. You are a database developer for a container manufacturing company. the containers produced by your company are a number of different sizes and shapes. the tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:
Size
SizeID
SizeName
Height
Container
ContainerID
ShapeID
SizeID
Shape
ShapeID
ShapeName
Measurements

A sample of the data stored in the tables is shown below:
Size Table
SizeID SizeName Height
1 Small 40
2 Medium 60
3 Large 80
4 Jumbo 100
Shape Table
ShapeID ShapeName Measurement
1 Triangle 10
2 Triangle 20
3 Triangle 30
4 Square 20
5 Square 30
6 Square 40
7 Circle 15
8 Circle 25
9 Circle 35
Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables.
You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?
A. Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.
B. Create a stored procedure that requires ContainerID as an argument and returns the volume of the container.
C. Add a column named volume to the container table. Create a trigger that calculates and stores volume in this column when a new container is inserted into the table.
D. Add a computed column to the container table that calculates the volume of the container.

2. Fill in blank questions
1. The three types of backup policies are __________ backup, _______________ backup, and ___________________ backup.
2. The statement used to start an explicit transaction is __________, the statement used to submit the transaction is __________, and the statement used to roll back the transaction is __________
Iii. Short answer
1. The existing sales table 1 (Table named SellDetail) has the following structure: (4 points)
Field Name field type description
Id int (id)
Codno char (7) (product code)
Codname varchar (30) (product name)
Spec varchar (20) (item type)
Price numeric (10, 2) (price)
Sellnum int (sales quantity)
Deptno char (3) (sold branch code)
Selldate datetime (sales time)
Requirement: Write the query sales time range from-2-15 to-4-29, and the branch code is all records of 01.
2. To write a stored procedure, you must input a table name and return the number of records of the table (assuming that the input table exists in the Database)

Address: http://www.javaeye.com/topic/137688