sql

六：Databases Questions & Answers
1.        What are two methods of retrieving SQL?　
2.        What cursor type do you use to retrieve multiple recordsets?
3.        What action do you have to perform before retrieving data from the next result set of a stored procedure?
Move the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row. Before you can get to the first row, you would need to Move the cursor down by one row ( For ex: in java the first call to next makes the first row the current row; the second call makes the second row the current row, and so on).

4.        What is the basic form of a SQL statement to read data out of a table?
SELECT * FROM table_name;

5.        What structure can you have the database make to speed up table reads?
The question is not correct. "What structure can you have the database make to speed up table reads?" It is not clear what exactly the term "structure" means in this case. Follow the rules of DB tuning we have to:

1) properly use indexes ( different types of indexes)

2) properly locate different DB objects across different tablespaces, files and so on.

3) Create a special space (tablespace) to locate some of the data with special datatypes( for example CLOB, LOB and ...)

6.        What is a "join"?
Joins merge the data of two related tables into a single result set, presenting a denormalized view of the data.

7.        What is a "constraint"?
　　A constraint allows you to apply simple referential integrity checks to a table. There are 5 primary types of constraints that are currently supported by SQL Server:

　　PRIMARY/UNIQUE - enforces uniqueness of a particular table column.

　　DEFAULT - specifies a default value for a column in case an insert operation does not provide one.

　　FOREIGN KEY - validates that every value in a column exists in a column of another table.

　　CHECK - checks that every value stored in a column is in some specified list

　　NOT NULL - is a constraint which does not allow values in the specific column to be null. And also it is the only constraint which is not a table level constraint.

8.        What is a "primary key"?
Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.

9.        What is a "functional dependency"? How does it relate to database table design?
What functional dependence in the context of a database means is that: Assume that a table exists in the database called TABLE with a composite primary key (A, B) and other non-key attributes (C, D, E). Functional dependency in general, would mean that any non-key attribute - C D or E being dependent on the primary key (A and B) in our table here.

　　Partial functional dependency, on the other hand, is another corollary of the above, which states that all non-key attributes - C D or E - if dependent on the subset of the primary key (A and B) and not on it as a whole.

　　Example :

　　----------

　　Fully Functional Dependent : C D E --> A B

　　Partial Functional dependency : C --> A, D E --> B

　　Hope that helps!

10.    What is a "trigger"?
A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert, update, delete and query statements. Basically, trigger is a set of SQL statements that execute in response to a data modification/retrieval event on a table.

Other than table triggers there are also schema and database triggers. These can be made to fire when new objects are created, when a user logs in, when the database shutdown etc. Table level triggers can be classified into row and statement level triggers and those can be further broken down into before and after triggers. Before triggers can modify data.

11.    What is "index covering" of a query?
A nonclustered index that includes (or covers) all columns used in a query is called a covering index. When SQL server can use a nonclustered index to resolve the query, it will prefer to scan the index rather than the table, which typically takes fewer data pages. If your query uses only columns included in the index, then SQL server may scan this index to produce the desired output.

12.    What is a SQL view?
View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn't physically take any space. View is a good way to present data in a particular format if you use that query quite often.

View can also be used to restrict users from accessing the tables directly.

A view otherwise known as a virtual table is a mere window over the base tables in the database. This helps us gain a couple of advantages:

1) Inherent security exposing only the data that is needed to be shown to the end user

2) Views are updateable based on certain conditions. For example, updates can only be directed to one underlying table of the view. After modification if the rows or columns don't comply with the conditions that the view was created with, those rows disappear from the view. You could use the CHECK OPTION with the view definition, to make sure that any updates to make the rows invalid will not be permitted to run.

3) Views are not materialized (given a physical structure) in a database. Each time a view is queried the definition stored in the database is run against the base tables to retrieve the data. One exception to this is to create a clustered index on the view to make it persistent in the database. Once you create a clustered index on the view, you can create any number of non-clustered indexes on the view.

13.    存储过程和函数的区别
存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

14.    事务是什么?
事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID(原子性、一致性、隔离性和持久性)属性，只有这样才能成为一个事务:

原子性：事务必须是原子工作单元;对于其数据修改，要么全都执行，要么全都不执行。

一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构(如 B 树索引或双向链表)都必须是正确的。

隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

15.    游标的作用?如何知道游标已经到了最后?
游标用于定位结果集的行，通过判断全局变量@@FETCH_STATUS可以判断是否到了最后，通常此变量不等于0表示出错或到了最后。

16.    触发器分为事前触发和事后触发，这两种触发有和区别。语句级触发和行级触发有何区别。
事前触发器运行于触发事件发生之前，而事后触发器运行于触发事件发生之后。通常事前触发器可以获取事件之前和新的字段值。

语句级触发器可以在语句执行前或后执行，而行级触发在触发器所影响的每一行触发一次。

17.    SQL Server常用测试题(1)
问题描述:

为管理岗位业务培训信息，建立3个表:

S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

C (C#,CN ) C#,CN 分别代表课程编号、课程名称

SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

　　--实现代码:

SELECT SN,SD FROM S

WHERE [S#] IN(SELECT [S#] FROM C,SC WHERE C.[C#]=SC.[C#] AND CN=N'税收基础')

2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

　　--实现代码:

SELECT S.SN,S.SD FROM S,SC

WHERE S.[S#]=SC.[S#] AND SC.[C#]='C2'

3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

　　--实现代码:

SELECT SN,SD FROM S

WHERE [S#] NOT IN(SELECT [S#] FROM SC WHERE [C#]='C5')

4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

　　--实现代码:

SELECT SN,SD FROM S

WHERE [S#] IN( SELECT [S#] FROM SC RIGHT JOIN

　　C ON SC.[C#]=C.[C#] GROUP BY [S#]

HAVING COUNT(*)=COUNT([S#]))

5. 查询选修了课程的学员人数

　　--实现代码:

　　SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

6. 查询选修课程超过5门的学员学号和所属单位

　　--实现代码:

　　SELECT SN,SD FROM S

　　WHERE [S#] IN(

　　SELECT [S#] FROM SC

　　GROUP BY [S#]

　　HAVING COUNT(DISTINCT [C#])>5)

　　

18.    SQL Server常用测试题(2)
问题描述:

已知关系模式:

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

--实现代码:

SELECT SNAME FROM S

WHERE NOT EXISTS(

SELECT * FROM SC,C WHERE SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

--实现代码:

SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)

FROM S,SC,(

SELECT SNO FROM SC WHERE SCGRADE<60 GROUP BY SNO

HAVING COUNT(DISTINCT CNO)>=2)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO

GROUP BY S.SNO,S.SNAME

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

　　--实现代码:

　　SELECT S.SNO,S.SNAME

　　FROM S,(SELECT SC.SNO FROM SC,C

　　WHERE SC.CNO=C.CNO AND C.CNAME IN('1','2')

　　GROUP BY SNO

　　HAVING COUNT(DISTINCT CNO)=2

　　)SC WHERE S.SNO=SC.SNO

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

　　--实现代码:

　　SELECT S.SNO,S.SNAME

　　FROM S,(

　　SELECT SC1.SNO

　　FROM SC SC1,C C1,SC SC2,C C2

　　WHERE SC1.CNO=C1.CNO AND C1.NAME='1'

　　AND SC2.CNO=C2.CNO AND C2.NAME='2'

　　AND SC1.SCGRADE>SC2.SCGRADE

　　)SC WHERE S.SNO=SC.SNO

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

　　--实现代码:

　　SELECT S.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩]

　　FROM S,(

　　SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE

　　FROM SC SC1,C C1,SC SC2,C C2

　　WHERE SC1.CNO=C1.CNO AND C1.NAME='1'

　　AND SC2.CNO=C2.CNO AND C2.NAME='2'

　　AND SC1.SCGRADE>SC2.SCGRADE

　　)SC WHERE S.SNO=SC.SNO

19.    Question 1：Can you use a batch SQL or store procedure to calculating the Number of Days in a Month
找出当月的天数
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

20.    Question2：Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?

select bookid,bookname,price=case when price is null then 'unknown'

when    price between 10 and 20 then '10 to 20' else price end
from books

21.      Question3：Can you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname                                   number_dups
---------------------------------------- -----------
Ringer                                     2
(1 row(s) affected)
Answer 3
select au_lname,number_dups=count(1) from authors group by au_lname
22.    Question4：Can you create a cross-tab report in my SQL Server!
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale qulity. Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name                                  Total         Qtr1          Qtr2          Qtr3          Qtr4        
---------------------------------------- ----------- ----------- ----------- ----------- -----------
Barnum's                                   50            0             50            0             0
Bookbeat                                   55            25            30            0             0
Doc-U-Mat: Quality Laundry and Books       85            0             85            0             0
Fricative Bookshop                         60            35            0             0             25
Total                                      250           60            165           0             25

Answer 4：用动态SQL实现

23.    Question5: The Fastest Way to Recompile All Stored Procedures
I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?
Tips: sp_recompile can recomplie a store procedure each time
Answer 5：在执行存储过程时,使用 with recompile 选项强制编译新的计划；使用sp_recompile系统存储过程强制在下次运行时进行重新编译

24.    Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:
line-no       title_id
----------- --------
1             BU1032
2             BU1111
3             BU2075
4             BU7832
5             MC2222
6             MC3021
7             MC3026
8             PC1035
9             PC8888
10            PC9999
11            PS1372
12            PS2091
13            PS2106
14            PS3333
15            PS7777
16            TC3218
17            TC4203
18            TC7777

Answer 6：
--SQL 2005的写法
select row_number() as line_no ,title_id from titles
--SQL 2000的写法
select line_no identity(int,1,1),title_id into #t from titles
select * from #t
drop table #t

25.    Question 7: Can you tell me what the difference of two SQL statements at performance of execution?

Statement 1:
if NOT EXISTS ( select * from publishers where state = 'NY')
begin
SELECT 'Sales force needs to penetrate New York market'
end
else
begin
SELECT 'We have publishers in New York'
end
Statement 2:
if EXISTS ( select * from publishers where state = 'NY')
begin
SELECT 'We have publishers in New York'
end
else
begin
SELECT 'Sales force needs to penetrate New York market'
end
Answer 7：不同点:执行时的事务数,处理时间,从客户端到服务器端传送的数据量大小

26.    Question8: How can I list all California authors regardless of whether they have written a book?
In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written.
CA behalf of california in table authors.
Answer 8：
select * from    authors where state='CA'
27.    Question9: How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
In database pubs, use three table stores,sales and titles to implement this requestment. Now I want to get the result as below:
stor_id stor_name                                
------- ----------------------------------------
...
7896      Fricative Bookshop
...
...
...
Answer 9：
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')    

28.    Question10: How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below
create table test
( id int primary key )
go

insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go

Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before
------------- --------------
6               8
9               11
...

Answer 10：
select id from test t where not exists(select 1 from test where id=t.id+1)
or not exists(select 1 from test where id=t.id-1)

29.    Question11: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:
type           title                                                                              price                 
------------ -------------------------------------------------------------------------------- ---------------------
business       The Busy Executive's Database Guide                                                19.9900
...
...
...
...

Answer 11：
select a.type,a.title,a.price from titles a,
(select type,price=avg(price) from titles group by type)b
where a.type=b.type and a.price>b.price

        试题点评：通览整个试题，我们不难发现，这份试题是针对SQL Server数据库人员的。而从难度分析上来看，这份试题也属于同类试题中比较难的了。之所以说它难，首先是限定时间的全英文试题；其次，尽管这份试题主要是考核开发能力，但却涉及到了算法的选择和性能的调优；最后，这份试题还夹进了SQL Server数据库的升级问题。因此，综上所述，我们估计这是一家从事程序外包工作的外企招聘后台开发或与后台开发相关的SQL Server高级程序员的试题。