ASP. NET SQL Summary (2)

SQL Common face question (summary)

1. Use an SQL statement to find out the names of students who have more than 80 points per course

Name Kecheng Fenshu

Zhang San language 81

Zhang San Mathematics 75

John Doe Language 76

John Doe Mathematics 90

Harry Language 81

Harry Mathematics 100

Harry English 90
A:select distinct name from table where name is not in (select DISTINCT name from table where fenshu<=80)
2. The student table is as follows:

AutoNumber Number name Course number Course name score

1 2005001 Zhang 30,001 Math 69

2 2005002 li 40,001 Math 89

3 2005001 Zhang 30,001 Math 69

Delete the same student redundancy information except for the automatic numbering
A:delete tablename where auto number not in (select min (autonumber) from TableName Group by number, name, course number, course name, score)

A table called department, there is only one field name, a total of 4 records, respectively, is a,b,c,d, corresponding to four ball pairs, now four ball pairs to play, with an SQL statement to show all possible match combinations. Would you like to do it your own way and see if the result is as simple as mine?

Answer: Select A.name, B.name from Team A, Team B where A.name < b.name

Use the SQL statement to: Query from the TESTDB data table that the occurrence of all months is higher than the corresponding month of the 101 account. Please note: There are many subjects in TestDB and there are 1-December occurrences. Accid: Account code, Occmonth: The amount of the month, Debitoccur: The amount of the occurrence. Database name: Jcyaudit, Data set: Select * from TestDB

Answer: Select A.* from TestDB A, (select Occmonth,max (debitoccur) debit101ccur from TestDB where accid= ' 101 ' GROUP by Occmonth) b where A.occmonth=b.occmonth and a.debitoccur>b.debit101ccur

************************************************************************************

Interview question: How to put such a table year month amount 1991 1 1.1 1991 2 1.2 1991 3 1.3 1991 4 1.4 1992 1 2.1 1992 2 2.2 1992 3 2.3 1992 4 2.4 to find such a result year M1 M2 M3 M4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4
Answer one, select year, (select amount from the AAA m where Month=1 and M.year=aaa.year) as M1, (select amount from AAA m where m Onth=2 and M.year=aaa.year) as M2, (select amount from AAA m where month=3 and m.year=aaa.year) as M3, (select Amount F Rom AAA m where month=4 and M.year=aaa.year) as M4 from AAA group by year

This is done in Oracle: SELECT * FROM (select name, Year B1, leads (year) through (partition by name order by year) B2, leads (m,2) over ( Partition by Name: B3,rank () over (partition by name, order by year) RK from T) where rk=1;

************************************************************************************

sophisticated SQL Statement! Subtle SQL statement not published: 2003.05.29 10:55:05
Description: Copy table (copy structure only, source table name: A new table name: b)
Sql:select * to B from a where 1<>1
Description: Copy table (copy data, source table name: A target table name: b)
Sql:insert to B (A, B, c) select d,e,f from B;
Description: Displays the article, the author, and the last reply time
Sql:select a.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b
Description: Outer join query (table name 1:a table name 2:b)
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
Description: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
Description: Two associated tables that remove information that is not already in the secondary table in the main table
Sql:
Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)
Description:--
Sql:
SELECT A.num, A.name, B.upd_date, b.prev_upd_date
From TABLE1,
(SELECT x.num, x.upd_date, Y.upd_date prev_upd_date
From (SELECT NUM, Upd_date, Inbound_qty, Stock_onhand
From TABLE2
WHERE to_char (upd_date, ' yyyy/mm ') = To_char (sysdate, ' yyyy/mm ')) X,
(SELECT NUM, Upd_date, Stock_onhand
From TABLE2
WHERE to_char (upd_date, ' yyyy/mm ') =
To_char (To_date (To_char (sysdate, ' yyyy/mm ') ¦¦ '/01 ', ' Yyyy/mm/dd ')-1, ' yyyy/mm ')) Y,
WHERE X.num = y.num (+)
and X.inbound_qty + NVL (y.stock_onhand,0) <> X.stock_onhand) B
WHERE A.num = B.num
Description:--
Sql:
SELECT * from Studentinfo where isn't exists (SELECT * from student where studentinfo.id=student.id) and system name = ' "&strdepart mentname& "' and professional name = '" &strprofessionname& "' order by gender, origin, overall exam
Description
From the database to a year of units telephone fee statistics (telephone charges fixed telegram of fertilizer List two table source)
Sql:
SELECT A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ') as Telyear,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as JAN,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as FRI,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as MAR,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' "A.factration") as APR,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a.factration ") as May,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ", A.factration)" as Jue,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' "," a.factration ") as JUL,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' AGU ', a.factration)
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a.factration ') as SEP,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ten ', a.factration)) as OCT,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' one ', a.factration)) as NOV,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' m ', a.factration)) as DEC
From (SELECT a.userper, A.tel, A.standfee, B.telfeedate, b.factration
From Telfeestand A, Telfee b
WHERE A.tel = B.telfax) A
GROUP by A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ')
Description: Four table linked questions:
Sql:select * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...
Description: Get the smallest unused ID number in the table
Sql:
Select (Case if EXISTS (SELECT * from Handle b WHERE b.handleid = 1) then MIN (Handleid) + 1 ELSE 1 END) as Handleid
From Handle
WHERE not Handleid in (SELECT a.handleid-1 from Handle a)

*******************************************************************************

There are two tables A and B, both key and value two fields, if B's key in a also, the value of B to the corresponding value in a SQL statement how to write?

Update b Set b.value= (select A.value from a where a.key=b.key) where b.id in (select b.ID from B,a where B.key=a.key);

***************************************************************************

Advanced SQL Face Questions

Original table: CourseID coursename score-------------------------------------1 Java 2 Oracle 3 XML 4 JSP 5 servlet 80- ------------------------------------for readability, the results after querying this table are as follows (pass score): CourseID Coursename score Mark------------------ ---------------------------------1 Java Pass 2 Oracle Pass 3 XML fail 4 JSP-fail 5 servlet pass---------- -----------------------------------------write out this query statement

No Oracle installed, never tried select CourseID, coursename, score, decode (sign (score-60), -1, ' fail ', ' pass ') as Mark from course

Absolutely right
sql> desc course_v Name Null? Type-----------------------------------------------------------------------------CourseID number Coursename VARCHAR2 (Ten) score number
Sql> select * from Course_v;
CourseID coursename score------------------------------1 Java 2 Oracle 3 XML 4 JSP 5 servlet 80
Sql> Select CourseID, Coursename, score, decode (sign (score-60), -1, ' fail ', ' pass ') as Mark from Course_v;
CourseID coursename score MARK----------------------------------1 Java pass 2 Oracle Pass 3 XML fail 4 JSP 3 0 Fail 5 servlet Pass

*******************************************************************************

Original table:
ID proid proname 1 1 M 1 2 F 2 1 N 2 2 G 3 1 B 3 2 A table after query:
ID Pro1 Pro2 1 M F 2 N G 3 B A write A query statement

Solution Solutions
SQL Solver Table A column A1 A2 record 1 a 1 B 2 x 2 y 2 z can I choose the following results with select? 1 AB 2 xyz is implemented using PL/SQL code, but requires that your combined length cannot exceed the limit of Oracle VARCHAR2 length. Here is an example of Create or replace type strings_table is Table of VARCHAR2 (20); /Create or Replace function merge (PV in strings_table) return varchar2 is LS varchar2 (4000); Begin for I in 1..pv.count loop ls: = ls | | PV (i); End Loop; return LS; End /CREATE TABLE T (ID number,name varchar2 (10)); INSERT into t values (1, ' Joan '); INSERT into t values (1, ' Jack '); INSERT into t values (1, ' Tom '); INSERT into t values (2, ' Rose '); INSERT into t values (2, ' Jenny ');
column names format A80; Select T0.id,merge (CAST (multiset (select name from t where t.id = t0.id) as strings_table), names from (SELECT DISTINCT ID from T) t0;
Drop type strings_table; drop function Merge; drop table t;

With sql:
Well if you had a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employee s in the the-describe then yes. But the hence a hundred emps a hundred LAGs and so kind of bulky in the needs to use the LAG function for each employee.
This example uses a max of 6, and would need the more cut n pasting to does more than that.
Sql> Select Deptno, Dname, Emps 2 from (3 select D.deptno, D.dname, RTrim (e.ename | |, ' | | 4 leads (e.ename,1) over (par Tition by D.deptno 5 order by e.ename) | | ', ' | | 6 lead (e.ename,2) through (partition by D.deptno 7 order by e.ename) | | ', ' | | 8 Lead (e.ename,3) through (partition by D.deptno 9 order by e.ename) | | ', ' | | e.ename,4 (partition by D.deptno one ORDER by E.ename) | | ', ' | | e.ename,5 (partition by D.deptno order by E.ename), ', ') Emps, + row_number () over (partition by d.dept No (e.ename) x from EMP E, Dept d, where D.deptno = E.deptno), where x = 1 20/
DEPTNO dname EMPS------------------------------------------------------------ACCOUNTING CLARK, KING, MILLER Rese ARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH, SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
Also first create function get_a2; Create or Replace function get_a2 (tmp_a1 number) return varchar2 is COL_A2 varchar2 (4000); Begin col_a2:= '; For cur in (select A2 from unite_a where a1=tmp_a1) Loop col_a2=col_a2| | CUR.A2; End Loop; return COL_A2; End GET_A2;
Select DISTINCT A1, GET_A2 (A1) from Unite_a 1 ABC 2 EFG 3 KMN

*******************************************************************************

A SQL face question

Last year to apply for a position did not result in the course of a seemingly simple question, but I did not find a good case. I wonder if you have any good solution for prawns?
Entitled: There are two tables, T1, t2, table T1:
SELLER | Non_seller----------
A b A C a D B a B C b d C a C b c D D A D b d C
Table T2:
SELLER | COUPON | BAL-----------------------a 9 B 9 C 9 D 9 a 9.5 b 9.5 a 10 80

A SELECT statement is required to list the following results:------such as sum of A (BAL) is b,c,d, sum of B (BAL) is a,c,d and .... and use the method do not increase the burden of the database, such as the use of temporary tables.
non-seller| COUPON | SUM (BAL)---------------a 9 B 9 0 C 9 9.5 d 9 a C 9.5 d 9.5-a B-c 10 80

About the SQL Microsoft face question on the forum

Problem:
100 accounts each have 100$, an account one day if there is an expense to add a new record, record their balance. After 100 days, please export the balance information of all accounts per day
The difficulty with this problem is that each user may have more than one record at a given day, and may not have a record (excluding the first day).
The recordset returned is a record set of 100 days * 100 users
Here's my idea:
1. Create the table and insert the test data: We require username from 1-100 CREATE TABLE [dbo]. [TABLE2] ([username] [varchar] (a) NOT NULL,--user name [Outdate] [datetime] NOT NULL,--date [cash] [float] NOT null--balance) On [PRIMARY
DECLARE @i int set @i=1 while @i<=100 begin insert Table2 VALUES (CONVERT (varchar), @i), ' 2001-10-1 ', [+] Ins ERT Table2 VALUES (CONVERT (varchar), @i, ' 2001-11-1 ', ') set @[email protected]+1 end insert Table2 values (CONVERT ( varchar (+), @i), ' 2001-10-1 ', 90
SELECT * FROM Table2 ORDER by Outdate,convert (Int,username)
2. Combining query statements: A. We must return a recordset from the first day to the 100 day: for example: 2001-10-1 (this date is arbitrary) to 2002-1-8 because the first day is any day, so we need the following SQL statement: Select top DateAdd (D,convert (Int,username) -1,min (outdate)) as outdate from Table2 Group by username ORDER by convert (Int,username) The secret here is: Convert (Int,username)-1 (remember we specify user name from 1-100:-)) group by Username,min (outdate): On the first day there may be multiple records per user. Results returned: Outdate------------------------------------------------------2001- 10-01 00:00:00.000 ..... 2002-01-08 00:00:00.000
B. Returning a recordset for all user names: SELECT DISTINCT username from table2 return result: Username------------ --------------------------------------1 10 100 ... 99
C. Returning a Cartesian set of 100-day recordsets and 100 user recordsets: SELECT * FROM (select top DateAdd (D,convert (int,username) -1,min (outdate)) as Outdate From Table2 GROUP by username ORDER by convert (Int,username)

As A Cross join (SELECT distinct username from table2) as-B order by Outdate,convert (int,username) Returns the result 100*100 record: Out Date username 2001-10-01 00:00:00.000 1 ... 2002-01-08 00:00:00.000 100
D. Return all current user records in the database: Select Outdate,username,min (Cash) as cash from Table2 GROUP by Outdate,username
Order by Outdate,convert (Int,username) return record: Outdate username cash 2001-10-01 00:00:00.000 1 90 ... 2002-01-08 00:00:00.000 100 50
E. The Cartesian set returned in C and the records returned in D are left Join:select C.outdate,c.username, D.cash from (SELECT * FROM (select top DateAdd (d,conver T (int,username) -1,min (outdate)) as outdate from Table2 Group by username ORDER by convert (Int,username)) as A Cross join (select DISTINCT username from table2) as B) as C left join (select Outdate,username,min (Cash) as cash from Table2 Gro Up by Outdate,username) as D on (C.username=d.username and DateDiff (d,c.outdate,d.outdate) =0)
Order by C.outdate,convert (int,c.username) Note: If the user does not have a record on that day, the cash field returns NULL, otherwise cash returns the balance of the day for each user outdate Username Cash 2001-10-01 00:00:00.000 1 90 2001-10-01 00:00:00.000 2 100 .... .. 2001-10-02 00:00:00.000 1 90

2001-10-02 00:00:00.000 2 NULL <--Note here ...
2002-01-08 00:00:00.000 100 50
F. Okay, now all we have to do is, if cash is null, we're going to return the first user balance that's less than the current record date (since we're using order by cash, so we'll return to the top 1 record, and the min should be OK), the balance is the current balance: case IsNull (d.cash,0) when 0 then (select top 1 cash from table2 where Table2.username=c.username and DateDiff (d,c.outdate,tab Le2.outdate) <0 ORDER by Table2.cash) Else D.cash end as cash
G. The complete statement of the last combination is select C.outdate,c.username, Case isnull (d.cash,0) when 0 then (select top 1 cash from table2 where table2. Username=c.username and DateDiff (d,c.outdate,table2.outdate) <0 ORDER by Table2.cash) Else D.cash end as cash from (SE Lect * FROM (select top DateAdd (D,convert (int,username) -1,min (outdate)) as outdate from Table2 Group by username Orde R by convert (Int,username)) as A Cross join (SELECT distinct username to table2) as B) as C left join (select OUTDA Te,username,min (Cash) as cash from Table2 Group by Outdate,username) as D on (C.username=d.username and DateDiff (D,C.OUTDA Te,d.outdate) =0)
Order by C.outdate,convert (Int,c.username)
Return Result: Outdate username cash 2001-10-01 00:00:00.000 1 90 2001 -10-01 00:00:00.000 2 100 ... 2002-01-08 00:00:00.000 100 50

***********************************************************************************

Remove the 31st to 40th record in the SQL table (with the auto-Grow ID as the primary key)

* Remove the record from the data table of article N to article M * *
DECLARE @m int DECLARE @n int DECLARE @sql varchar () set @m=40 set @n=31 set @sql = ' Select Top ' +str (@[email protected]+ 1) + ' * from Idetail where autoid (select top ' + str (@n-1) + ' autoid from Idetail) ' EXEC (@sql)

Select top * from T where ID not in (select top with ID from T order by ID) Orde by ID

--------------------------------------------------------------------------------

Select top * from T where ID in (select top all IDs from T order by ID) Order BY id DESC

*******************************************************************************

An interview question, write SQL statement
There is a node in table A that stores the binary tree, and a SQL statement is used to isolate all nodes and the layer where the nodes are located. Table A C1 c2 a----------1--------//a b b C--------2 a C///B D D N e------3 C E///D f F k I---4 E I D k C N
The results to be obtained are as follows
JD CS---------a 1 B 2 C 2 D 3 N 3 E 3 F 4 K 4 I 4 There is a master guide, I can only be written in PL/SQL, ask for the wording of a statement

Sql> Select C2, Level + 1 LV 2 from test start 3 with C1 = ' A ' 4 connect by C1 = Prior C2 5 Union 6 Select ' A ', 1 from Dual 7 order by LV;
C2 LV------------A 1 B 2 C 2 D 3 E 3 N 3 F 4 I 4 K 4
9 rows have been selected.

ASP. NET SQL Summary (2)