Turn: SQL face question

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      languages         Bayi
Zhang San       math       
John Doe        languages        John Doe
      Math        
Harry       languages        Bayi
Harry        Math       
Harry       English        

A:select distinct name from table where name is not in (select distinct name from T Able where fenshu<=80)
Select name from table group by name have min (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)

3. A table called team, 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 compete, 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

4. Use the SQL statement: from the TestDB data table, the occurrence of all the months is higher than the 101 accounts of the corresponding month. 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

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

5. Interview questions: 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

*******************************************************************************
6. Description: Copy table (copy structure only, source table name: A new table name: b)

Sql:select * to B from a where 1<>1 (where1=1, copy table structure and data contents)
Oracle:create Table B

As

Select * from a where 1=2

[<> (Not equal to) (SQL Server Compact)

Comparison of two expressions. When using this operator to compare non-empty expressions, the result is TRUE if the left operand is not equal to the right operand. Otherwise, the result is FALSE. ]

7. 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 A;

8. 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

9. 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 OUTER JOIN b on a.a = B.C

Oracle:select a.a, A.B, A.C, B.C, B.D, B.f from A, b

where a.a = B.C (+)

10. Description: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

11. Description: Two related tables, delete information in the primary table that is not already in the secondary table

Sql:
Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)

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

12. There are two tables A and B, both key and value two fields, and if B's key is also in a, change the value of B 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-side 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

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

SQL face question (1)

CREATE TABLE Testtable1
(
ID int IDENTITY,
Department varchar (12)
)

SELECT * FROM Testtable1
INSERT into testtable1 values (' design ')
INSERT into testtable1 values (' Market ')
INSERT into testtable1 values (' aftermarket ')
/*
Results
ID Department
1 design
2 Market
3 after-sales
*/
CREATE TABLE Testtable2
(
ID int IDENTITY,
Dptid int,
Name varchar (12)
)
INSERT into testtable2 values (1, ' Zhang San ')
INSERT into testtable2 values (1, ' John Doe ')
INSERT into Testtable2 values (2, ' Harry ')
INSERT into Testtable2 values (3, ' Peng VI ')
INSERT into Testtable2 values (4, ' Chen Qi ')
/*
With an SQL statement, how to display the following results
ID Dptid Department Name
1 1 design Zhang San
2 1 Design John Doe
3 2 Market Harry
4 3 after-sales Peng Liu
5 4 Black Chen Seven
*/

Answer:

SELECT testtable2.*, ISNULL (department, ' Black ')
From Testtable1 right Join testtable2 on testtable2.dptid = testtable1.id

It's a little more complicated than this method to make.

SQL Face question (2)

There is a table A, the structure is as follows:
a:p_id P_num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
Where: p_id for product id,p_num for product inventory, s_id for the warehouse ID. Use the SQL statement to merge the data from the table above, and the combined data is:
p_id s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
Where: s1_id for the warehouse 1 inventory, s2_id Warehouse 2 of inventory, s3_id Warehouse 3 inventory. If the product is not in stock in a warehouse, then it is 0 instead.

Results:

Select p_id,
SUM (case if S_id=1 then P_num else 0 end) as s1_id
, sum (case if s_id=2 then P_num else 0 end) as s2_id
, sum (case if s_id=3 then P_num else 0 end) as s3_id
From Mypro GROUP BY p_id

SQL Face question (3)

1. What does a trigger do?

A: A trigger is a special stored procedure that is executed primarily by triggering an event. It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes. Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

2. What is a stored procedure? Using what to invoke?

A: A stored procedure is a precompiled SQL statement, and the advantage is that it allows for a modular design, meaning that it is created once and can be called more than once in the program. If an operation needs to execute multiple times of SQL, using a stored procedure is faster than simply SQL statement execution. You can invoke a stored procedure with a command object.

3. The role of the index? And what are the pros and cons of it?

A: The index is a special query table, the database search engine can use it to speed up the retrieval of data. It is similar to the catalogue of real-life books, and you can find the data you want without having to query the entire contents of the book. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down data entry and also increases the size of the database.

3. What is a memory leak?

A: Generally what we call a memory leak refers to a leak in heap memory. Heap memory is a program that is allocated to it from the heap, arbitrarily sized, and then shown to free memory after it is exhausted. When an application creates an object with the keyword new and so on, it allocates a piece of memory from the heap, the program calls free or the delete frees the memory after use, or the memory is not used, we say that the memory is compromised.

4. Maintain database integrity and consistency, do you prefer to use triggers or self-write business logic? Why?

A: I do this, as far as possible to use constraints, such as check, primary key, foreign keys, non-empty fields to constrain, so that the most efficient and most convenient. The second is the use of triggers, which ensures that the data is intact and consistent regardless of the business system access to the database. The final consideration is self-writing business logic, but this is cumbersome, programming complex, inefficient.

5. What is a transaction? What is a lock?

A: A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails then the entire operation fails, and the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass acid testing, that is, atomicity, consistency, isolation, and persistence.

Lock: In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

6. What does a view mean? What is a cursor?

A: A view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is usually a subset of rows or columns that have a table or multiple tables. Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries.

Cursor: is a query out of the result set as a unit to effectively handle. A cursor can be set to a specific row in that cell, retrieving one or more rows from the current row of the result set. You can make modifications to the current row of the result set. Cursors are generally not used, but cursors are important when you need to process the data individually.

7. To manage business training information, create 3 tables:

S (S#,SN,SD,SA) S#,SN,SD,SA respectively representative student number, student name, affiliation unit, student age

C (C#,CN) C#,CN, respectively, representing the course number, course name

SC (S#,C#,G) S#,c#,g respectively represents the student number, the selected course number, the academic achievement

(1) Use standard SQL nested statements to query the student number and name of the elective course named ' Tax base '?

Answer: Select s#, SN from S where s# in (select s# from C,SC where c.c#=sc.c# and cn= ' tax base ')

(2) Use standard SQL nested statements to query the student's name and affiliation of the elective course number ' C2 '?

Answer: Select Sn,sd from S,SC where s.s#=sc.s# and sc.c#= ' C2 '

(3) Use standard SQL nested statements to query the name and affiliation of the student who does not take the course number ' C5 '?

A: Select Sn,sd from S where s# not in (select s# from SC where c#= ' c5 ')

(4) Number of students who have enrolled in the course

A: The number of select Trainees =count (DISTINCT s#) from SC

(5) The number of students enrolled in more than 5 elective courses and their respective units?

A: Select Sn,sd from S where s# in (select s# from SC GROUP by s# have count (Distinct C #) >5)

SQL Face question (4)

1. Query the 31st to 40th record in the A (id,name) table, the ID as the primary key may not be the continuous growth of the column, the complete query statement is as follows:

Select Top * from a Where ID > (select MAX (ID) from (select top with ID from a, order by a) T) order by a

2. Query table A has more than three duplicate ID records, the complete query is as follows:
SELECT * FROM (select COUNT (ID) as count from table Group by ID) T where t.count>3

SQL Face question (5)

I used a standard set of benchmark technical issues when interviewing SQL Server database developers. These are the questions I feel can really help to eliminate the unqualified candidates. They are arranged in order from easy to difficult. When you ask questions about primary keys and foreign keys, the questions are difficult, because the answers can be more difficult to explain and describe, especially in the case of interviews.

Can you give me a brief account of some of the database objects used in SQL Server 2000?

The answers you want to hear include objects such as tables, views, user-defined functions, and stored procedures, and it would be nice if they could also mention objects like triggers. If the candidate cannot answer the basic question, then this is not a good sign.

What does null mean?

Null (NULL) This value is a very difficult thing in the database world, so there are a lot of candidates who will fall on this issue and you should not be surprised.

Null this value represents unknown (unknown): It does not represent "" (an empty string). Suppose you have ansi_nulls in your SQL Server database, and of course, by default, any comparison to NULL will produce a null value. You cannot compare any value to a unknown value and logically want to get an answer. You must use the IS null operator.

What is an index? What types of indexes are in SQL Server 2000?

Any experienced database developer should be able to answer this question very easily. Some less experienced developers can answer this question, but in some places it's unclear.

Simply put, an index is a data structure that is used to quickly access data in a database table or view. In SQL Server, they come in two forms: clustered and nonclustered indexes. The clustered index holds the data at the leaf level of the index. This means that regardless of which (or what) fields in the table are in the clustered index, the fields are saved sequentially in the table. Because of this sort, there is only one clustered index per table. A nonclustered index has a row identifier at the leaf level of the index. This row identifier is a pointer to the data on disk. It allows more than one nonclustered index per table.

What is a primary key? What is a foreign key?

A primary key is a field (or fields) in a table that defines only the rows in the table, and the values in the primary key are always unique. A foreign key is a constraint that is used to establish a relationship between two tables. This relationship typically involves a series of connected fields in a table where the primary key field is associated with another table (although it may be the same table). Then these connected fields are foreign keys.

What is a trigger? What are the different types of triggers for SQL Server 2000?

It is beneficial for future database developers to know the types of triggers available and how to implement them.

A trigger is a private type of stored procedure that is bundled into a table or view of SQL Server 2000. In SQL Server 2000, there are instead-of and after two types of triggers. instead-of triggers are stored procedures that perform statements on a table in lieu of the data manipulation language (manipulation LANGUAGE,DML) statement. For example, if I had a instead-of-update trigger for TableA and an UPDATE statement for the table, the code in the Instead-of-update trigger would execute instead of the UPDATE statement I executed.

After triggers are not executed until the DML statement is used in the database. These types of triggers are useful for monitoring data changes that occur in database tables.

How do you make sure that a TableB table with the name Fld1 field has only those values in the Fld1 field, and that the values are in the Fld1 field of the table named TableA?

There are two possible answers to this relationship-related question. The first answer (and the answer you want to hear) is the use of foreign key restrictions. Foreign key restrictions are used to maintain referential integrity. It is used to ensure that the fields in the table only hold values that are defined in another field that is already in a different (or identical) table. This field is the candidate key (usually the primary key of the other table).

Another answer is a trigger. Triggers can be used to ensure that the same effect is achieved in another way, but it is very difficult to set up and maintain, and the performance is generally poor. For this reason, Microsoft recommends that developers use foreign key restrictions instead of triggers to maintain referential integrity.

What are the performance considerations for having too many indexes on an online transaction processing table that you put into use?

You are looking for candidates who are involved in data manipulation. The more indexes a table has, the more time it takes for the database engine to update, insert, or delete data because the index must be maintained when data manipulation occurs.

What can you use to make sure that the fields in the table only accept values in a specific range?

This question can be answered in many ways, but only one answer is "good". The answer you want to hear is a check limit, which is defined in a database table to restrict the value of the input column.

Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires that triggers be defined in the table, which may affect performance in some cases. Therefore, Microsoft recommends using check restrictions instead of other ways to limit domain integrity.

If the candidate is able to answer the question correctly, then his chances are great because it shows that they have experience using stored procedures.

The return parameter is always returned by the stored procedure, which is used to indicate whether the stored procedure succeeded or failed. The return parameter is always the int data type.

The output parameter is explicitly required by the developer to specify that it can return other types of data, such as character and numeric values. (There are some limitations to the data types that can be used as output parameters.) You can use more than one output parameter in a stored procedure and you can only use a return parameter.

What is a correlated subquery? How do I use these queries?

More experienced developers will be able to accurately describe this type of query.

A related subquery is a special type of query that contains subqueries. The subquery contained in the query will actually request the value of the external query, thus creating a loop-like condition.

SQL Face question (6)

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

Oracle:select CourseID, Coursename, score, decode (sign (score-60), -1, ' fail ', ' pass ') as Mark from course

(The Decode function is one of the most powerful functions of Oracle PL/SQL and is currently available only to Oracle Corporation.)

(Sql:select CourseID, Coursename, score, (case-score<60 then ' fail ' else ' pass ' end) as-mark from course)

Concept plus old problem http://blog.csdn.net/lgtttt/article/details/51610740

Turn: SQL face question