收集自Oracle公司的10次(60道)电话面试全部问答(英语)

Q:  What environment variables do I need to set on my machine in order to be able to run Java programs?
A:  CLASSPATH and PATH are the two variables.

Q:  Can an application have multiple classes having main method?
A:  Yes it is possible. While starting the application we mention the class name to be run. The JVM will look for the Main method only in the class whose name you have mentioned. Hence there is not conflict amongst the multiple classes having main method.

Q:  Can I have multiple main methods in the same class?
A:  No the program fails to compile. The compiler says that the main method is already defined in the class.

Q:  Do I need to import java.lang package any time? Why ?
A:  No. It is by default loaded internally by the JVM.

Q   Can I import same package/class twice? Will the JVM load the package twice at runtime?
A:  One can import the same package or same class multiple times. Neither compiler nor JVM complains abt it. And the JVM will internally load the class only once no matter how many times you import the same class.

Q:  What are Checked and UnChecked Exception?
A:  A checked exception is some subclass of Exception (or Exception itself), excluding class RuntimeException and its subclasses.
Making an exception checked forces client programmers to deal with the possibility that the exception will be thrown. eg, IOException thrown by java.io.FileInputStream's read() method?
Unchecked exceptions are RuntimeException and any of its subclasses. Class Error and its subclasses also are unchecked. With an unchecked exception, however, the compiler doesn't force client programmers either to catch the
exception or declare it in a throws clause. In fact, client programmers may not even know that the exception could be thrown. eg, StringIndexOutOfBoundsException thrown by String's charAt() method? Checked exceptions must be caught at compile time. Runtime exceptions do not need to be. Errors often cannot be.

Q:  What is Overriding?
A:  When a class defines a method using the same name, return type, and arguments as a method in its superclass, the method in the class overrides the method in the superclass.
When the method is invoked for an object of the class, it is the new definition of the method that is called, and not the method definition from superclass. Methods may be overridden to be more public, not more private.

Q:  What are different types of inner classes?
A:  Nested top-level classes, Member classes, Local classes, Anonymous classes

Nested top-level classes- If you declare a class within a class and specify the static modifier, the compiler treats the class just like any other top-level class.

Any class outside the declaring class accesses the nested class with the declaring class name acting similarly to a package. eg, outer.inner. Top-level inner classes implicitly have access only to static variables.There can also be inner interfaces. All of these are of the nested top-level variety.

Member classes - Member inner classes are just like other member methods and member variables and access to the member class is restricted, just like methods and variables. This means a public member class acts similarly to a nested top-level class. The primary difference between member classes and nested top-level classes is that member classes have access to the specific instance of the enclosing class.

Local classes - Local classes are like local variables, specific to a block of code. Their visibility is only within the block of their declaration. In order for the class to be useful beyond the declaration block, it would need to implement a more publicly available interface.Because local classes are not members, the modifiers public, protected, private, and static are not usable.

Anonymous classes - Anonymous inner classes extend local inner classes one level further. As anonymous classes have no name, you cannot provide a constructor.

Q:  Are the imports checked for validity at compile time? e.g. will the code containing an import such as java.lang.ABCD compile?
A:  Yes the imports are checked for the semantic validity at compile time. The code containing above line of import will not compile. It will throw an error saying,can not resolve symbol
symbol : class ABCD
location: package io
import java.io.ABCD;

Q:  Does importing a package imports the subpackages as well? e.g. Does importing com.MyTest.* also import com.MyTest.UnitTests.*?
A:  No you will have to import the subpackages explicitly. Importing com.MyTest.* will import classes in the package MyTest only. It will not import any class in any of it's subpackage.

Q:  What is the difference between declaring a variable and defining a variable?
A:  In declaration we just mention the type of the variable and it's name. We do not initialize it. But defining means declaration + initialization.
e.g String s; is just a declaration while String s = new String ("abcd"); Or String s = "abcd"; are both definitions.

Q:  What is the default value of an object reference declared as an instance variable?
A:  null unless we define it explicitly.

Q:  Can a top level class be private or protected?
A:  No. A top level class can not be private or protected. It can have either "public" or no modifier. If it does not have a modifier it is supposed to have a default access.If a top level class is declared as private the compiler will complain that the "modifier private is not allowed here". This means that a top level class can not be private. Same is the case with protected.

Q:  What type of parameter passing does Java support?
A:  In Java the arguments are always passed by value .

Q:  Primitive data types are passed by reference or pass by value?
A:  Primitive data types are passed by value.

Q:  Objects are passed by value or by reference?
A:  Java only supports pass by value. With objects, the object reference itself is passed by value and so both the original reference and parameter copy both refer to the same object .

Q:  What is serialization?
A:  Serialization is a mechanism by which you can save the state of an object by converting it to a byte stream.

Q:  How do I serialize an object to a file?
A:  The class whose instances are to be serialized should implement an interface Serializable. Then you pass the instance to the ObjectOutputStream which is connected to a fileoutputstream. This will save the object to a file.

Q:  Which methods of Serializable interface should I implement?
A:  The serializable interface is an empty interface, it does not contain any methods. So we do not implement any methods.

Q:  How can I customize the seralization process? i.e. how can one have a control over the serialization process?
A:  Yes it is possible to have control over serialization process. The class should implement Externalizable interface. This interface contains two methods namely readExternal and writeExternal. You should implement these methods and write the logic for customizing the serialization process.

Q:  What is the common usage of serialization?
A:  Whenever an object is to be sent over the network, objects need to be serialized. Moreover if the state of an object is to be saved, objects need to be serilazed.

Q:  What is Externalizable interface?
A:  Externalizable is an interface which contains two methods readExternal and writeExternal. These methods give you a control over the serialization mechanism. Thus if your class implements this interface, you can customize the serialization process by implementing these methods.

Q:  When you serialize an object, what happens to the object references included in the object?
A:  The serialization mechanism generates an object graph for serialization. Thus it determines whether the included object references are serializable or not. This is a recursive process. Thus when an object is serialized, all the included objects are also serialized alongwith the original obect.

Q:  What one should take care of while serializing the object?
A:  One should make sure that all the included objects are also serializable. If any of the objects is not serializable then it throws a NotSerializableException.

Q:  What happens to the static fields of a class during serialization?
A:  There are three exceptions in which serialization doesnot necessarily read and write to the stream. These are
1. Serialization ignores static fields, because they are not part of ay particular state state.
2. Base class fields are only hendled if the base class itself is serializable.
3. Transient fields.

Q:  Does Java provide any construct to find out the size of an object?
A:  No there is not sizeof operator in Java. So there is not direct way to determine the size of an object directly in Java.

Q:  Give a simplest way to find out the time a method takes for execution without using any profiling tool?
A:  Read the system time just before the method is invoked and immediately after method returns. Take the time difference, which will give you the time taken by a method for execution.

To put it in code...

long start = System.currentTimeMillis ();
method ();
long end = System.currentTimeMillis ();

System.out.println ("Time taken for execution is " + (end - start));

Remember that if the time taken for execution is too small, it might show that it is taking zero milliseconds for execution. Try it on a method which is big enough, in the sense the one which is doing considerable amout of processing.

Q:  What are wrapper classes?
A:  Java provides specialized classes corresponding to each of the primitive data types. These are called wrapper classes. They are e.g. Integer, Character, Double etc.

Q:  Why do we need wrapper classes?
A:  It is sometimes easier to deal with primitives as objects. Moreover most of the collection classes store objects and not primitive data types. And also the wrapper classes provide many utility methods also. Because of these resons we need wrapper classes. And since we create instances of these classes we can store them in any of the collection classes and pass them around as a collection. Also we can pass them around as method parameters where a method expects an object.

Q:  What are checked exceptions?
A:  Checked exception are those which the Java compiler forces you to catch. e.g. IOException are checked Exceptions.

Q:  What are runtime exceptions?
A:  Runtime exceptions are those exceptions that are thrown at runtime because of either wrong input data or because of wrong business logic etc. These are not checked by the compiler at compile time.

Q:  What is the difference between error and an exception?
A:  An error is an irrecoverable condition occurring at runtime. Such as OutOfMemory error. These JVM errors and you can not repair them at runtime. While exceptions are conditions that occur because of bad input etc. e.g. FileNotFoundException will be thrown if the specified file does not exist. Or a NullPointerException will take place if you try using a null reference. In most of the cases it is possible to recover from an exception (probably by giving user a feedback for entering proper values etc.).

Q:  How to create custom exceptions?
A:  Your class should extend class Exception, or some more specific type thereof.

Q:  If I want an object of my class to be thrown as an exception object, what should I do?
A:  The class should extend from Exception class. Or you can extend your class from some more precise exception type also.

Q:  If my class already extends from some other class what should I do if I want an instance of my class to be thrown as an exception object?
A:  One can not do anytihng in this scenarion. Because Java does not allow multiple inheritance and does not provide any exception interface as well.

Q:  How does an exception permeate through the code?
A:  An unhandled exception moves up the method stack in search of a matching When an exception is thrown from a code which is wrapped in a try block followed by one or more catch blocks, a search is made for matching catch block. If a matching type is found then that block will be invoked. If a matching type is not found then the exception moves up the method stack and reaches the caller method. Same procedure is repeated if the caller method is included in a try catch block. This process continues until a catch block handling the appropriate type of exception is found. If it does not find such a block then finally the program terminates.

Q:  What are the different ways to handle exceptions?
A:  There are two ways to handle exceptions,
1. By wrapping the desired code in a try block followed by a catch block to catch the exceptions. and
2. List the desired exceptions in the throws clause of the method and let the caller of the method hadle those exceptions.

Q: What is the basic difference between the 2 approaches to exception handling.
1> try catch block and
2> specifying the candidate exceptions in the throws clause?
When should you use which approach?

A:  In the first approach as a programmer of the method, you urself are dealing with the exception. This is fine if you are in a best position to decide should be done in case of an exception. Whereas if it is not the responsibility of the method to deal with it's own exceptions, then do not use this approach. In this case use the second approach. In the second approach we are forcing the caller of the method to catch the exceptions, that the method is likely to throw. This is often the approach library creators use. They list the exception in the throws clause and we must catch them. You will find the same approach throughout the java libraries we use.

Q:  Is it necessary that each try block must be followed by a catch block?
A:  It is not necessary that each try block must be followed by a catch block. It should be followed by either a catch block OR a finally block. And whatever exceptions are likely to be thrown should be declared in the throws clause of the method.

Q:  If I write return at the end of the try block, will the finally block still execute?
A:  Yes even if you write return as the last statement in the try block and no exception occurs, the finally block will execute. The finally block will execute and then the control return.

Q:  If I write System.exit (0); at the end of the try block, will the finally block still execute?
A:  No in this case the finally block will not execute because when you say System.exit (0); the control immediately goes out of the program, and thus finally never executes.

Q:  How are Observer and Observable used?
A:  Objects that subclass the Observable class maintain a list of observers. When an Observable object is updated it invokes the update() method of each of its observers to notify the observers that it has changed state. The Observer interface is implemented by objects that observe Observable objects.

Q:  What is synchronization and why is it important?
A:  With respect to multithreading, synchronization is the capability to control
the access of multiple threads to shared resources. Without synchronization, it is possible for one thread to modify a shared object while another thread is in the process of using or updating that object's value. This often leads to significant errors.

Q:  How does Java handle integer overflows and underflows?
A:  It uses those low order bytes of the result that can fit into the size of the type allowed by the operation.

Q:  Does garbage collection guarantee that a program will not run out of memory?
A:  Garbage collection does not guarantee that a program will not run out of memory. It is possible for programs to use up memory resources faster than they are garbage collected. It is also possible for programs to create objects that are not subject to garbage collection

Q:  What is the difference between preemptive scheduling and time slicing?
A:  Under preemptive scheduling, the highest priority task executes until it enters the waiting or dead states or a higher priority task comes into existence. Under time slicing, a task executes for a predefined slice of time and then reenters the pool of ready tasks. The scheduler then determines which task should execute next, based on priority and other factors.

Q:  When a thread is created and started, what is its initial state?
A:  A thread is in the ready state after it has been created and started.

Q:  What is the purpose of finalization?
A:  The purpose of finalization is to give an unreachable object the opportunity to perform any cleanup processing before the object is garbage collected.

Q:  What is the Locale class?
A:  The Locale class is used to tailor program output to the conventions of a particular geographic, political, or cultural region.

Q:  What is the difference between a while statement and a do statement?
A:  A while statement checks at the beginning of a loop to see whether the next loop iteration should occur. A do statement checks at the end of a loop to see whether the next iteration of a loop should occur. The do statement will always execute the body of a loop at least once.

Q:  What is the difference between static and non-static variables?
A:  A static variable is associated with the class as a whole rather than with specific instances of a class. Non-static variables take on unique values with each object instance.

Q:  How are this() and super() used with constructors?
A:  This() is used to invoke a constructor of the same class. super() is used to invoke a superclass constructor.

Q:  What are synchronized methods and synchronized statements?
A:  Synchronized methods are methods that are used to control access to an object. A thread only executes a synchronized method after it has acquired the lock for the method's object or class. Synchronized statements are similar to synchronized methods. A synchronized statement can only be executed after a thread has acquired the lock for the object or class referenced in the synchronized statement.

Q:  What is daemon thread and which method is used to create the daemon thread?
A:  Daemon thread is a low priority thread which runs intermittently in the back ground doing the garbage collection operation for the java runtime system. setDaemon method is used to create a daemon thread.

Q:  Can applets communicate with each other?
A:  At this point in time applets may communicate with other applets running in the same virtual machine. If the applets are of the same class, they can communicate via shared static variables. If the applets are of different classes, then each will need a reference to the same class with static variables. In any case the basic idea is to pass the information back and forth through a static variable.

An applet can also get references to all other applets on the same page using the getApplets() method of java.applet.AppletContext. Once you get the reference to an applet, you can communicate with it by using its public members.

It is conceivable to have applets in different virtual machines that talk to a server somewhere on the Internet and store any data that needs to be serialized there. Then, when another applet needs this data, it could connect to this same server. Implementing this is non-trivial.

Q:  What are the steps in the JDBC connection?
A:    While making a JDBC connection we go through the following steps :

Step 1 : Register the database driver by using :

Class.forName(\" driver classs for that specific database\" );

Step 2 : Now create a database connection using :

Connection con = DriverManager.getConnection(url,username,password);

Step 3: Now Create a query using :

Statement stmt = Connection.Statement(\"select * from TABLE NAME\");

Step 4 : Exceute the query :

stmt.exceuteUpdate();

Q:  How does a try statement determine which catch clause should be used to handle an exception?
A:  When an exception is thrown within the body of a try statement, the catch clauses of the try statement are examined in the order in which they appear. The first catch clause that is capable of handling the exceptionis executed. The remaining catch clauses are ignored.

Q:  Can an unreachable object become reachable again?
A:  An unreachable object may become reachable again. This can happen when the object's finalize() method is invoked and the object performs an operation which causes it to become accessible to reachable objects.

Q:  What method must be implemented by all threads?
A:  All tasks must implement the run() method, whether they are a subclass of Thread or implement the Runnable interface.

Q:  What are synchronized methods and synchronized statements?
A:  Synchronized methods are methods that are used to control access to an object. A thread only executes a synchronized method after it has acquired the lock for the method's object or class. Synchronized statements are similar to synchronized methods. A synchronized statement can only be executed after a thread has acquired the lock for the object or class referenced in the synchronized statement.

Q:  What is Externalizable?
A:  Externalizable is an Interface that extends Serializable Interface. And sends data into Streams in Compressed Format. It has two methods, writeExternal(ObjectOuput out) and readExternal(ObjectInput in)

Q:  What modifiers are allowed for methods in an Interface?
A:  Only public and abstract modifiers are allowed for methods in interfaces.

Q:  What are some alternatives to inheritance?
A:  Delegation is an alternative to inheritance. Delegation means that you include an instance of another class as an instance variable, and forward messages to the instance. It is often safer than inheritance because it forces you to think about each message you forward, because the instance is of a known class, rather than a new class, and because it doesn't force you to accept all the methods of the super class: you can provide only the methods that really make sense. On the other hand, it makes you write more code, and it is harder to re-use (because it is not a subclass).

Q:  What does it mean that a method or field is "static"?
A:  Static variables and methods are instantiated only once per class. In other words they are class variables, not instance variables. If you change the value of a static variable in a particular object, the value of that variable changes for all instances of that class.
Static methods can be referenced with the name of the class rather than the name of a particular object of the class (though that works too). That's how library methods like System.out.println() work out is a static field in the

Q:  What is the difference between preemptive scheduling and time slicing?
A:  Under preemptive scheduling, the highest priority task executes until it enters the waiting or dead states or a higher priority task comes into existence. Under time slicing, a task executes for a predefined slice of time and then reenters the pool of ready tasks. The scheduler then determines which task should execute next, based on priority and other factors.

Q:  What is the catch or declare rule for method declarations?
A:  If a checked exception may be thrown within the body of a method, the method must either catch the exception or declare it in its throws clause.


 

 

 

 

 

 

写了这么多JAVA基础,来点SQL吧!

 

一般面试时考SQL,主要就是考你“统计分析”这一块,下面我们来看面试官经常采用的手段。

由4张简单的不能再简单的表,演变出50道SQL

哈哈哈哈,够这个面试官面个15,20个人,不带重复的了,而且每个SQL你真的不动动脑子还写不出呢,你别不服气,下面开始。

表结构:

 

 

表Student

(S#,Sname,Sage,Ssex) 学生表

 

S#
student_no
Sage student_age
Ssex student_sex

 

 

 

表Course

(C#,Cname,T#) 课程表

 

C#
course_no
Cname course_name
T# teacher_no

 

 

 

表SC(学生与课程的分数mapping 表)

(S#,C#,score) 成绩表

 

S# student_no
C# course_no
score 分数啦

 

 

 

表Teacher

(T#,Tname) 教师表

 

T# teacher_no
Tname teacher_name

 

 

50道问题开始

 

1、查询“001”课程比“002”课程成绩高的所有学生的学号;

 

select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

from SC where C#='002')

 

where a.score>b.score and a.s#=b.s#;

 

2、查询平均成绩大于60分的同学的学号和平均成绩;

select S#,avg(score)

from sc

group by S# having avg(score) >60;

 

3、查询所有同学的学号、姓名、选课数、总成绩;

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname

 

4、查询姓“李”的老师的个数;

select count(distinct(Tname))

from Teacher

where Tname like '李%';

 

5、查询没学过“叶平”老师课的同学的学号、姓名;

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) fromSC,Course,Teacher where  SC.C#=Course.C#and Teacher.T#=Course.T# andTeacher.Tname='叶平');

 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

select Student.S#,Student.Sname fromStudent,SC where Student.S#=SC.S# andSC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# andSC_2.C#='002');

 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

select S#,Sname

from Student

where S# in (select S# from SC,Course ,Teacher where SC.C#=Course.C# andTeacher.T#=Course.T# and Teacher.Tname='叶平'group by S# havingcount(SC.C#)=(select count(C#) fromCourse,Teacher  whereTeacher.T#=Course.T# and Tname='叶平'));

 

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S#and SC_2.C#='002') score2

from Student,SC where Student.S#=SC.S# andC#='001') S_2 where score2 <score;


9、查询所有课程成绩小于60分的同学的学号、姓名;

select S#,Sname

from Student

where S# not in (select Student.S# fromStudent,SC where S.S#=SC.S# andscore>60);

 

10、查询没有学全所有课的同学的学号、姓名;

select Student.S#,Student.Sname

from Student,SC

whereStudent.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

 

 

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

 

select S#,Sname from Student,SC whereStudent.S#=SC.S# and C# in select C# from SC where S#='1001';

 

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in(select C# from SC where S#='001');

 

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

update SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) fromCourse,Teacher where Course.C#=SC.C# andCourse.T#=Teacher.T# and Teacher.Tname='叶平');

 

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

select S# from SC where C# in(select C# from SC where S#='1002')

group by S# having count(*)=(select count(*) from SC where S#='1002');

 

15、删除学习“叶平”老师课的SC表记录;

DelectSC

from course ,Teacher

where Course.C#=SC.C# and Course.T#=Teacher.T# and Tname='叶平';

 

16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学

号、2号课的平均成绩;

Insert SC select S#,'002',(Select avg(score)

from SC where C#='002') from Student where S# notin (Select S# from SC where C#='002');

 

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按

如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT S# as 学生ID

,(SELECT score FROM SC WHERE SC.S#=t.S#AND C#='004') AS 数据库

,(SELECT score FROM SC WHERE SC.S#=t.S#AND C#='001') AS 企业管理

,(SELECT score FROM SC WHERE SC.S#=t.S#AND C#='006') AS 英语

,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score)

 

18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

FROM SC L ,SC AS R

WHERE L.C# = R.C# and

L.score = (SELECT MAX(IL.score)

FROM SC ASIL,Student AS IM

WHERE L.C# =IL.C# and IM.S#=IL.S#

GROUP BYIL.C#)

AND

R.Score= (SELECT MIN(IR.score)

FROM SC ASIR

WHERE R.C# =IR.C#

GROUP BY IR.C#

);

 

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS平均成绩

,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

FROM SC T,Course

where t.C#=course.C#

GROUP BY t.C#

ORDER BY 100* SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC


20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):


企业管理(001),马克思(002),OO&UML (003),数据库(004)

 

 

SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分

,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数

,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分

,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数

,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分

,100* SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数

,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分

,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数

FROM SC

 

 

21、查询不同老师所教不同课程平均分从高到低显示

  SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 
    FROM SC AS T,Course AS C ,Teacher AS Z 
    where T.C#=C.C# and C.T#=Z.T# 
  GROUP BY C.C# 
  ORDER BY AVG(Score) DESC

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:

企业管理(001),马克思(002),UML (003),数据库(004

[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

    SELECT  DISTINCT top 3 
      SC.S# As 学生学号, 
        Student.Sname AS 学生姓名 , 
      T1.score AS 企业管理, 
      T2.score AS 马克思, 
      T3.score AS UML, 
      T4.score AS 数据库, 
      ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 
      FROM Student,SC  LEFT JOIN SC AS T1 
                      ON SC.S# = T1.S# AND T1.C# = '001' 
            LEFT JOIN SC AS T2 
                      ON SC.S# = T2.S# AND T2.C# = '002' 
            LEFT JOIN SC AS T3 
                      ON SC.S# = T3.S# AND T3.C# = '003' 
            LEFT JOIN SC AS T4 
                      ON SC.S# = T4.S# AND T4.C# = '004' 
      WHERE student.S#=SC.S# and 
      ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 
      NOT IN 
      (SELECT 
            DISTINCT 
            TOP 15 WITH TIES 
            ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 
      FROM sc 
            LEFT JOIN sc AS T1 
                      ON sc.S# = T1.S# AND T1.C# = 'k1' 
            LEFT JOIN sc AS T2 
                      ON sc.S# = T2.S# AND T2.C# = 'k2' 
            LEFT JOIN sc AS T3 
                      ON sc.S# = T3.S# AND T3.C# = 'k3' 
            LEFT JOIN sc AS T4 
                      ON sc.S# = T4.S# AND T4.C# = 'k4' 
      ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

    SELECT SC.C# as 课程ID, Cname as 课程名称 
        ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] 
        ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70] 
        ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60] 
        ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 
    FROM SC,Course 
    where SC.C#=Course.C# 
    GROUP BY SC.C#,Cname;

24、查询学生平均成绩及其名次

      SELECT 1+(SELECT COUNT( distinct 平均成绩) 
              FROM (SELECT S#,AVG(score) AS 平均成绩 
                      FROM SC 
                  GROUP BY S# 
                  ) AS T1 
            WHERE 平均成绩 > T2.平均成绩) as 名次, 
      S# as 学生学号,平均成绩 
    FROM (SELECT S#,AVG(score) 平均成绩 
            FROM SC 
        GROUP BY S# 
        ) AS T2 
    ORDER BY 平均成绩 desc;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

      SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 
      FROM SC t1 
      WHERE score IN (SELECT TOP 3 score 
              FROM SC 
              WHERE t1.C#= C# 
            ORDER BY score DESC 
              ) 
      ORDER BY t1.C#;

26、查询每门课程被选修的学生数

  select c#,count(S#) from sc group by C#;

27、查询出只选修了一门课程的全部学生的学号和姓名

  select SC.S#,Student.Sname,count(C#) AS 选课数 
  from SC ,Student 
  where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

28、查询男生、女生人数

    Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男'; 
    Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女';

29、查询姓“张”的学生名单

    SELECT Sname FROM Student WHERE Sname like '张%';

30、查询同名同性学生名单,并统计同名人数

  select Sname,count(*) from Student group by Sname having  count(*)>1;

31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)

    select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age 
    from student 
    where  CONVERT(char(11),DATEPART(year,Sage))='1981';

32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

    Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;


33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

    select Sname,SC.S# ,avg(score) 
    from Student,SC 
    where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;

34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数

    Select Sname,isnull(score,0) 
    from Student,SC,Course 
    where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score <60;

35、查询所有学生的选课情况;

    SELECT SC.S#,SC.C#,Sname,Cname 
    FROM SC,Student,Course 
    where SC.S#=Student.S# and SC.C#=Course.C# ;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

    SELECT  distinct student.S#,student.Sname,SC.C#,SC.score 
    FROM student,Sc 
    WHERE SC.score>=70 AND SC.S#=student.S#;

37、查询不及格的课程,并按课程号从大到小排列

    select c# from sc where scor e <60 order by C# ;

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

    select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';

39、求选了课程的学生人数

    select count(*) from sc;

40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩

    select Student.Sname,score 
    from Student,SC,Course C,Teacher 
    where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );

41、查询各个课程及相应的选修人数

    select count(*) from sc group by C#;

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

  select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ;

43、查询每门功成绩最好的前两名

    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 
      FROM SC t1 
      WHERE score IN (SELECT TOP 2 score 
              FROM SC 
              WHERE t1.C#= C# 
            ORDER BY score DESC 
              ) 
      ORDER BY t1.C#;

44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列

    select  C# as 课程号,count(*) as 人数 
    from  sc  
    group  by  C# 
    order  by  count(*) desc,c#

45、检索至少选修两门课程的学生学号

    select  S#  
    from  sc  
    group  by  s# 
    having  count(*)  >  =  2

46、查询全部学生都选修的课程的课程号和课程名

    select  C#,Cname  
    from  Course  
    where  C#  in  (select  c#  from  sc group  by  c#)

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

    select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');

48、查询两门以上不及格课程的同学的学号及其平均成绩

    select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;

49、检索“004”课程分数小于60,按分数降序排列的同学学号

    select S# from SC where C#='004'and score <60 order by score desc;

50、删除“002”同学的“001”课程的成绩

delete from Sc where S#='001'and C#='001';