SQL Common face question 3[reprint]

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
Chacheng 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 AAA m where Month=1 and M.year=aaa.year) as M1,
(select amount from AAA m where month=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 from 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) over
(Partition by name order by year) B2, leads (m,2) over (partition by name order by year) B3,rank () over (
Partition by name, order by year, RK from T) where rk=1;

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

Sophisticated SQL Statement!
Subtle SQL statements
Unknown posting time: 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, and if B's key is also in a, the value of B is swapped to the corresponding value in a
How do you write the SQL statement for this problem?

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 70
2 Oracle 90
3 XML 40
4 JSP 30
5 Servlet 80
-------------------------------------
For readability, the results after querying this table are as follows (pass score 60):
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 (10)
Score Number

Sql> select * from Course_v;

CourseID Coursename Score
---------- ---------- ----------
1 Java 70
2 Oracle 90
3 XML 40
4 JSP 30
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 @ 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
0;
2 Z
Can I choose the following results with select?
1 AB
2 xyz
implemented with PL/SQL code, but requires that your combined length cannot exceed the limit of Oracle VARCHAR2 length.
Here is an example
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 (partition by D.deptno
5 ORDER by E.ename) | | ', ' | |
6 leads (e.ename,2) over (partition by D.deptno
7 order by E.ename) | | ', ' | |
8 Leads (e.ename,3) over (partition by D.deptno
9 ORDER by E.ename) | | ', ' | |
Ten leads (e.ename,4) over (partition by D.deptno
ORDER by E.ename) | | ', ' | |
e.ename,5 (partition by D.deptno
Order by E.ename), ', ') Emps,
Row_number () over (partition by D.deptno
Order by E.ename) x
From EMP E, Dept D
+ WHERE D.deptno = E.deptno
18)
where x = 1
20/

DEPTNO dname EMPS
------- ----------- ------------------------------------------
Ten ACCOUNTING CLARK, KING, MILLER
ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH
SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

Also
Create function GET_A2 first;
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 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
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 900
B 9 800
C 9 700
D 9 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 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 a table and insert test data: We require username from 1-100
CREATE TABLE [dbo]. [TABLE2] (
[Username] [varchar] () 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 ', 100)
Insert Table2 VALUES (CONVERT (varchar), @i, ' 2001-11-1 ', 50)
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. Combine query statements:
A. We must return a set of records from the first day to the 100 day:
For example: 2001-10-1 (this date is arbitrary) to 2002-1-8
Since the first day is any day, 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): There may be multiple records per user on the first day.
Results returned:
Outdate
------------------------------------------------------
2001-10-01 00:00:00.000
.........
2002-01-08 00:00:00.000

B. Returns a recordset of all user names:
Select DISTINCT username from table2
return Result:
Username
--------------------------------------------------
1
10
100
......
99

C. Returns a Cartesian collection 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)
Return result 100*100 record:
Outdate 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. Make a LEFT join of the Cartesian set returned in C and the records returned in D:
Select C.outdate,c.username,
D.cash
From
(
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
) as C
Left Join
(
Select Outdate,username,min (Cash) as cash from table2
GROUP 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 go back 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 Da Tediff (d,c.outdate,table2.outdate) <0
Order by Table2.cash
)
Else D.cash
End as cash

G. The final combination of the complete statement 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
(
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
) as C
Left Join
(
Select Outdate,username,min (Cash) as cash from table2
GROUP 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)

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 (800)
Set @m=40
Set @n=31
Set @sql = ' Select Top ' +str (@[email protected]+1) + ' * from Idetail where autoid not in (
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
L S
I 4
Have a master guide, I can only use PL/SQL to write out, ask to use 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
L S

9 rows have been selected.

SQL Common face question 3[reprint]