Chapter 2 Technology

Tzh's written test mainly involves T-SQL.

Original questions:

ID	Value
1	1
2	1
5	2
6	2
8	3
9	3

 

 

 

 

Difficulty: Calculate grouping statistics sorted by ID

ID	Value
1	2
2	4
3	6

 

 

Medium: grouping statistics by odd number

Low: Statistical Group

 

Extension 1:

There is a student table with the ID, CID, name, CID, score, and score of all students whose scores are less than 60.

/*
Navicat MySQL Data Transfer
Source Host     : localhost:3306
Source Database : tzh
Target Host     : localhost:3306
Target Database : tzh
Date: 2012-02-14 21:58:36
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `sid` int(4) default NULL,
  `name` varchar(200) default NULL,
  `cid` int(4) default NULL,
  `score` int(4) default NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '1', '1', '1', '50');
INSERT INTO `student` VALUES ('2', '1', '1', '2', '60');
INSERT INTO `student` VALUES ('3', '1', '1', '3', '70');
INSERT INTO `student` VALUES ('4', '1', '1', '4', '80');
INSERT INTO `student` VALUES ('5', '2', '2', '1', '50');
INSERT INTO `student` VALUES ('6', '2', '2', '2', '60');
INSERT INTO `student` VALUES ('7', '2', '2', '3', '55');
INSERT INTO `student` VALUES ('8', '2', '2', '4', '67');

Select CID, name, AVG (score) from student group by CID having score <60

When I answered the question, I didn't think about having, I was pointed out wrong with where, and I didn't think about it yet. I was told by the interviewer, sadly.

 

Extension 2:

There is a temporary table temp, the field is ID, value, there is a table A, the field is also ID, value. Update a temporary table to table.

Update a T1, temp T2 set t1.value = t2.value where t1.id = t2.id;

 

Extension 3:

There is a temporary table temp, the field is ID, value, there is a table A, the field is also ID, value. Insert a temporary table to table.

Insert into a (ID, value) Select ID, value from temp

Insert into a (ID, value) Select T. ID, T. value from temp as t where T. id not in (select a. ID as ID from a as );

 

Yfwl

1. What is the difference between heap and stack?

Stack is responsible for saving our code execution (or call) path, while stack is responsible for saving the object (or data, next we will talk about a lot of heap problems) path.

2. What is the solution to the deadlock?

A deadlock is a permanent waiting state in which all processes in a group of processes occupy resources that will not be released, but are waiting for resources that are not released because of mutual application by other processes. There are four necessary conditions for deadlock generation: mutex conditions, non-deprivation conditions, partial allocation, and cyclic waiting.

Measures:
1. Use static resource allocation policies to break the "partial allocation" condition;
2. allow the process to deprive it of the resources occupied by other processes, thereby undermining the "irrevocable" condition;
3. Use the resource ordered allocation method to break the "loop" condition.
Another way to solve the deadlock is the deadlock detection method, which does not limit the allocation of resources, that is, the occurrence of the deadlock is allowed. However, the system regularly runs a "Deadlock Detection" program to determine whether the system has encountered a deadlock. If the deadlock is detected, try to release it.
The following two methods are often used to remove deadlocks: 1. Resource deprivation; 2. withdrawal process.

3. Definitions and features of things and implementation in. net.

A transaction is a program execution unit that accesses and may update various data items in the database ).

Transactions must have ACID properties, I .e. atomicity, consistency, isolation, and continuity.

Asql transactions SQL transactions use SQL Server's own transactions: Begin Tran, rollback Tran, and commit Tran are directly used in the stored procedure to implement transactions. Advantages: optimal execution efficiency: transaction context is called only in the database, making it difficult to implement complicated business logic.

B ado.net transactions are common advantages: simplicity, efficiency and database transactions. Disadvantage: transactions cannot span databases and can only be connected to one database. This transaction cannot be used on two databases.

C transactionscope transaction class, which can make the code block a transactional code. And automatically upgraded to distributed transactions. Advantages: simple implementation and automatic upgrade to distributed transactions.

D COM + transactions in distributed applications usually need to operate multiple databases at the same time, and database transactions cannot meet the business requirements. Provides complete transaction processing services in COM +. It is easy to process transactions on multiple databases.

PS: http://www.cnblogs.com/jonescheng/archive/2008/07/22/1249043.html

Detailed steps for 4 socket synchronous communication

1. Use protocol and network address in applications and remote devices to initialize sockets

2. Create a listener by specifying the port and address in the Application

3. remote devices send connection requests
4. Applications accept connections to generate scoket communications
5. Communication starts between applications and remote devices (in communications, applications will be suspended until communication ends)
6. After the communication ends, close the socket of the application and remote device to recycle resources.

5. Fetch 20-40 records from the table.

Select top 20 * from a where id not in (select top 20 ID from a order by id asc) order by id asc;

PS1: retrieve the N to M records from the publish table: Select top M-n + 1 * From publish where (id not in (select top n-1 ID from publish ))

PS2: MySQL running on fengge's computer failed. MySQL had its own keyword: Select * From t_article order by ID limit 4, 2

6asp.net page lifecycle.

Page request	Page requests occur before the page lifecycle starts. ASP. net will determine whether to analyze and compile the page (to start the page lifecycle), or whether it can send the cached version of the page to respond without running the page.
Start	In the initial stage, page properties such as request and response are set. In this phase, the page also determines whether the request is a send-back request or a new request, and sets the ispostback attribute. In addition, the uiculture attribute of the page is set during the start phase.
Page Initialization	During page initialization, you can use controls on the page and set the uniqueid attribute for each control. In addition, any topic will be applied to the page. If the current request is a send-back request, the send-back data has not been loaded, and the control property value has not been restored to the value in the view State.
Load	During the loading, if the current request is a send-back request, the control properties will be loaded using information restored from the view status and control status.
Verify	During verification, the validate method for all validators controls is called, which sets the isvalid attribute for each validators control and page.
Event Processing	If the request is a send-back request, all event handlers are called.
Rendering	The view status is saved for the page and all controls before rendering. In the rendering phase, the page calls the render method for each control. It provides a text writer to write the control output toResponseAttribute in outputstream.
Uninstall	After the page is completely rendered and sent to the client, the call is uninstalled after the page is discarded. In this case, the page properties (suchResponseAndRequest) And perform cleanup.

 

Interview preparation:

SQL row/column Conversion

2000 method:

Select name,

Max (case [subject] When case 'China' then score else 0 end) as 'China ',

Max (case [subject] When case 'mate' then score else 0 end) as 'mate ',

Max (case [subject] When case 'physical 'Then score else 0 end) as 'physical'

From students

Group by name;

2005133 method:

Select name, max (Chinese)

From students as s

Max (score) for [subject] In (Chinese, Mathematics, Physics) as SS

Group by name

PS: http://www.cnblogs.com/zhangq723/archive/2011/04/22/2024776.html

 

Hctx

1. Differences between ASP and Asp.net

The main difference between ASP. NET and ASP is that the former is compiled for execution, while the latter is interpreted for execution, and the former is more efficient than the latter.

2. Differences between oledbconnection and sqlconnection and their usage

Oledbconnection connects to the ole db data source, and sqlconnection connects to SQL Server.

Using (oledbconnection connection = new oledbconnection (connectionstring ))
{
Connection. open ();
// Do work here.
}

Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Connection. open ();
// Do work here.
}

PS: http://teddywang.iteye.com/blog/490504

Connect to an instance:
Access
String constr = @ "provider = Microsoft. Jet. oledb.4.0; Data Source = D: \ accessdata. mdb; uid = sa; Pwd = dd ";
SQL Server
String constr = @ "Server = bwj; database = demo; uid = sa; Pwd = ";
Oracle
String constr = @ "provider = msdaora.1; Password = PWD; user id = user_name; Data Source = link_str"

3. Count "," -- "1234,1215, 1236,1237 ".

4. SQL questions

Customer

Custid custno custname

1 A01 mobile

2 A02 Unicom

3 A03 China Telecom

Price

Custid custpay

1 20000

3 40000

1 5000

Table search

Custno custname custpay

A01 mobile 25000

A02 Unicom 0

A03 telecommunications 40000

 

5. Write a stored procedure to obtain the outgoing value.

6. x = 30, y = 40, no variable exchange value is used.

7. "date and event" Start and close.