Group By语句和having语句的用法

更多精彩请到 http://www.139ya.com

http://www.cnblogs.com/tangjian/archive/2009/02/13/1390027.html

(11)GROUP BY子句
GROUP BY子句首先讲讲GROUP BY 子句语法： SELECT column1, SUM(column2)FROM "list-of-tables"GROUP BY "column-list"; 这个GROUP BY子句将集中所有的行在一起，它包含了指定列的数据以及允许合计函数来计算一个或者多个列。当然最好解释的方法是给出一 个例子啦：假设我们将从employee表中搜索工资最高的列，可以使用以下的SQL语 句：SELECT max(salary), deptFROM employee GROUP BY dept; 这条语句将在每一个单独的部门中选择 工资最高的工资。结果他们的salary和dept将被返回。
(12)HAVING子句
HAVING子句下面先给出HAVING子句的语法：SELECT column1, SUM(column2)FROM "list-of-tables"GROUP BY "column-list"HAVING "condition"; 这个HAVING子句 允许你为每一个组指定条件，换句话说，可以根据你指定的条件来选择行。如果你想使用HAVING子句的话，它应该处再GROUP BY子句之后。下面将以 一个例子来解释HAVING子句。假设我们的employee表中包含雇员的name、departmen、salary和age。如果你想为每个部门中 每个雇员选择平均工资的话，你可以使用下面的SQL语 句：SELECT dept, avg(salary)FROM employeeGROUP BY dept; 当然，如果你还想只计算和显示 salary大于20000的平均工资的话，你还可以加上HAVING子 句：SELECT dept, avg(salary)FROM employeeGROUP BY deptHAVING avg(salary) > 20000;



where语句里不能含有聚合函数

Select SNAME FROM STUDENTS
Where AGE ＞ (Select AVG(AGE) FROM STUDENTS)

找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩。（ GROUP BY 与 HAVING
GROUP BY 子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。
GROUP BY 子句总是跟在 Where 子句后面，当 Where 子句缺省时，它跟在 FROM 子句后面。
HAVING 子句常用于在计算出聚集之后对行的查询进行控制。）

程序代码

Select CNO, AVG(GRADE), STUDENTS ＝ COUNT(*) FROM ENROLLS

GROUP BY CNO HAVING COUNT(*) >= 3

查询没有选任何课程的学生的学号和姓名。（当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（ Correlated Subquery) 。 相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。）

程序代码

Select SNO, SNAME FROM STUDENTS Where NOT EXISTS

(Select * FROM ENROLLS Where ENROLLS.SNO=STUDENTS.SNO)

例 37 查询没有选任何课程的学生的学号和姓名。（当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（ Correlated Subquery) 。 相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。）

程序代码

Select SNO, SNAME FROM STUDENTS Where NOT EXISTS
(Select * FROM ENROLLS Where ENROLLS.SNO=STUDENTS.SNO)

例 38 查询哪些课程只有男生选读。

程序代码

Select DISTINCT CNAME FROM COURSES C
Where ' 男 ' ＝ ALL
(Select SEX FROM ENROLLS ， STUDENTS Where ENROLLS.SNO=STUDENTS.SNO AND ENROLLS.CNO=C.CNO)

例 39 要求给出一张学生、籍贯列表，该表中的学生的籍贯省份，也是其他一些学生的籍贯省份。

程序代码

Select SNAME, BPLACE FROM STUDENTS A Where EXISTS
(Select * FROM STUDENTS B Where A.BPLACE=B.BPLACE AND A.SNO < > B.SNO)

SQL Interview Questions

更多精彩请到 http://www.139ya.com

SQL Interview Questions and Answers :

What is the difference between oracle,sql and sql server ?

* Oracle is based on RDBMS.
* SQL is Structured Query Language.
* SQL Server is another tool for RDBMS provided by MicroSoft.

why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view?

We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

Difference between Store Procedure and Trigger?

* we can call stored procedure explicitly.
* but trigger is automatically invoked when the action defined in trigger is done.
ex: create trigger after Insert on
* this trigger invoked after we insert something on that table.
* Stored procedure can't be inactive but trigger can be Inactive.
* Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.

What is the advantage to use trigger in your PL?

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

* Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
* Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
* Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
* Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.

What the difference between UNION and UNIONALL?

Union will remove the duplicate rows from the result set while Union all does'nt.

What is the difference between TRUNCATE and DELETE commands?

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.

Which system table contains information on constraints on all the tables created ?
yes,
USER_CONSTRAINTS,
system table contains information on constraints on all the tables created

Explain normalization ?
Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation :
first normal forms, second normal forms, third normal forms and fourth Normal forms.

How to find out the database name from SQL*PLUS command prompt?
Select * from global_name;
This will give the datbase name which u r currently connected to.....

What is the difference between SQL and SQL Server ?

SQLServer is an RDBMS just like oracle,DB2 from Microsoft
whereas
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.

What is diffrence between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to used with two attributes

1. % and

2. _ ( underscore )

% means matches zero or more characters and under score means mathing exactly one character

1)What is difference between Oracle and MS Access?
2) What are disadvantages in Oracle and MS Access?
3) What are feratures&advantages in Oracle and MS Access?
Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.

What is database?
A database is a collection of data that is organized so that itscontents can easily be accessed, managed and updated. open this url : http://www.webopedia.com/TERM/d/database.html

What is cluster.cluster index and non cluster index ?
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.

How can i hide a particular table name of our schema?
you can hide the table name by creating synonyms.

e.g) you can create a synonym y for table x

create synonym y for x;

What is difference between DBMS and RDBMS?
The main difference of DBMS & RDBMS is

RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization.
the DBMS hasn't normalization concept.

What are the advantages and disadvantages of primary key and foreign key in SQL?

Primary key

Advantages

1) It is a unique key on which all the other candidate keys are functionally dependent

Disadvantage

1) There can be more than one keys on which all the other attributes are dependent on.

Foreign Key

Advantage

1)It allows refrencing another table using the primary key for the other table

Which date function is used to find the difference between two dates?
datediff

for Eg: select datediff (dd,'2-06-2007','7-06-2007')

output is 5

SQL-92中DDL和DML的含义

更多精彩请到 http://www.139ya.com

SQL-92中DDL和DML的含义

SQL语言包含4个部分：
　　※ 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。
　　※ 数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。
　　※ 数据查询语言(DQL)，例如：SELECT语句。
　　※ 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。
　　SQL语言包括三种主要程序设计语言类别的语句：数据定义语言(DDL)，数据操作语言(DML)及数据控制语言(DCL)。
DDL 用于定义和管理对象，例如数据库、数据表以及视图。DDL 语句通常包括每个对象的CREATE、ALTER 以及 DROP 命令。举例来说，CREATE TABLE、ALTER TABLE 以及 DROP TABLE 这些语句便可以用来建立新数据表、修改其属性（如新增或删除资料行）、删除数据表等.
DML 利用 INSERT、SELECT、UPDATE 及 DELETE 等语句来操作数据库对象所包含的数据。