（问题更新了）SQL： 稍微复杂一点的代码，where 语句的执行

[旭日阳刚]

放那里，有nerds来看吗？

斑竹来火，一刀给砍了，你哭都找不到坟头

 

[heureux]

放那里，有nerds来看吗？

不是建议你给 nerds 悄悄话喊一嗓子嘛

 

[旭日阳刚]

这上面的nerds就你们几个吗？包括闲得慌？
感觉那样很没礼貌，追着别人问问题。

不是建议你给 nerds 悄悄话喊一嗓子嘛

 

[旭日阳刚]

我吃饭去了，回来再完成问题。
何仙帮我移过去? 谢谢。

 

[heureux]

显摆一段俺以前的 SQL 笔记

01. WRITING SQL BASIC STATEMENT
02. RESTRICTING AND SORTING DATA
03. SINGLE-ROW FUNCTIONS
04. DISPLAY DATA FROM MUTIPLE TABLES
05. AGGREGATING DATA USING GROUP FUNCTIONS
06. SUBQUERIES
07. PRODUCING READABLE OUTPUT WITH ISQLPLUS
08. MANIPULATING DATA
09. CREATING AND MANAGING TABLES
10. INCLUDING CONSTRAINTS
11. CREATING VIEWS
12. OTHER DATABASE OBJECTS
13. CONTROLLING USER ACCESS

01. WRITING SQL BASIC STATEMENT
   
   0. Database general

      - Oracle9i can manage unstructured data: speadsheets, word document, 
        powerpoit, XML, mp3, multimedia such as graphics, video
      - Oracle9i supports tens of thousands concurrent users, 512 petabytes 
        (1000 terabytes = 1000x1000 GB) of data
      - 4 types of database: network, hierachical, relational, object
      - rdb conponents:
        > [composed of] collection of objects or relations that store the data
        > [managed by]  a set of operators that can act on the relations to produce other relations
        > [governed by] data integrity for accuracy and consistency
      - some explanations:      
        > relations = 2 dimention table
        > row = tuple
        > column = attribute
        > cardinality = degree
      - PK, FK
        > PK can be changed, but generally don't change it
        > FK must reference (match) PK, UK column or null      
      - 5 catagory of SQL statements 
        CAT1: SELECT
              select   -- data retrieval
        CAT2: DML
              MIDU (merge, insert, delete, update)
        CAT3: DDL
              CDART (create, drop, alter, rename, truncate)
        CAT4: TC (transaction control)
              CSR (commit, savepoint, rollback)
        CAT5: DCL (data control language)
              GR (grant, revoke)
      - delete is DML; while truncate is DDL
      - between a and b --> in [a,b]
   
   1. select statement can do:
      - projection:    'select * from' is also a projection 
      - selection:     where clause
      - joining:       
   
   2. case in-sensitive, keyword can't be abbreviated or split across line
   
   3. 'from' clause can't use '+,-,*,/' operator
       
   4. null
      - (true and null) = (false or null) = null
      - true or null   = true
        false and null = false
      - salary/0 ---> error
        salary/null = null
        in (1, 3, 5, null, ...) = unkown

   5. column alias
      AS is optional, "..." means

   6. literal      
      - character, number, date --> 'Heureux', 139, '12-OCT-04'
      - char, date literal must be enclosed with ' ';
   
   7. distinct
      - must immediately follows 'select'   

   8. desc
      - isql*plus command:  desc TVS
      
   STS-20
      - select * from a is also a projection
      - iSQL*PLUS use "-" for longstatement
      - SQL vs. iSQL*PLUS
        SQL is a command language used for communicating to the server,
        iSQL*PLUS is not although is contains its own command language
        and can be used to create scripts    
      - SQL is ANSI standard language

 

[旭日阳刚]

斑竹看了我的头像，会心软的。

斑竹来火，一刀给砍了，你哭都找不到坟头

 

[白米饭]

问题还没有写完，正在更新。
SELECT firtname, lastname
FROM managers
WHERE 'MA'=

(​

SELECT jobcategory​

FROM supervisors​

WHERE managers.id=supervisors.id​

)​

;

SELECT managers.firtname, managers.lastname
FROM managers, supervisors
WHERE manager.id=supervisors.id
AND supervisors.jobcategory='MA';

 

[苦逼热狗]

LZ那个query能用，不过我个人在数据库代码不是很喜欢这么拧麻花绳。

 

[旭日阳刚]

对，楼上两位。估计是书上想用它来做个例子，讲解什么叫关联子查询correlated subqueries
热狗的做法，变成了非关联的，更简单。
大米饭的做法，书上也提到了是比较好的一种。

 

[苦逼热狗]

运行结果

mickey=> select count(deviceid) from device where 1 in (select customerid from customer where device.customerid = customer.customerid);
count
-------
2
(1 row)

mickey=> select count(deviceid) from device where customerid = 1;
count
-------
2
(1 row)

mickey=> select count(deviceid) from device where customerid <> 1;
count
-------
1144
(1 row)

mickey=> select count(deviceid) from device where 1 not in (select customerid from customer where device.customerid = customer.customerid);
count
-------
1144
(1 row)

 

[苦逼热狗]

你可能会对这个更感兴趣一些，http://sqlserverplanet.com/sql-server/sql-server-interview-questions/

 

[旭日阳刚]

这个链接挺有用，感谢！

你可能会对这个更感兴趣一些，http://sqlserverplanet.com/sql-server/sql-server-interview-questions/

 

[Anakin]

哎。。。想灌都没法下手。。

 

[旭日阳刚]

咋地啦，被美女迷住了？
呵呵，哥男女通吃的策略算是用对了。

哎。。。想灌都没法下手。。

 

[旭日阳刚]

你网上网下都闲的慌。

显摆一段俺以前的 SQL 笔记

01. WRITING SQL BASIC STATEMENT
02. RESTRICTING AND SORTING DATA
03. SINGLE-ROW FUNCTIONS
04. DISPLAY DATA FROM MUTIPLE TABLES
05. AGGREGATING DATA USING GROUP FUNCTIONS
06. SUBQUERIES
07. PRODUCING READABLE OUTPUT WITH ISQLPLUS
08. MANIPULATING DATA
09. CREATING AND MANAGING TABLES
10. INCLUDING CONSTRAINTS
11. CREATING VIEWS
12. OTHER DATABASE OBJECTS
13. CONTROLLING USER ACCESS

01. WRITING SQL BASIC STATEMENT
   
   0. Database general

      - Oracle9i can manage unstructured data: speadsheets, word document, 
        powerpoit, XML, mp3, multimedia such as graphics, video
      - Oracle9i supports tens of thousands concurrent users, 512 petabytes 
        (1000 terabytes = 1000x1000 GB) of data
      - 4 types of database: network, hierachical, relational, object
      - rdb conponents:
        > [composed of] collection of objects or relations that store the data
        > [managed by]  a set of operators that can act on the relations to produce other relations
        > [governed by] data integrity for accuracy and consistency
      - some explanations:      
        > relations = 2 dimention table
        > row = tuple
        > column = attribute
        > cardinality = degree
      - PK, FK
        > PK can be changed, but generally don't change it
        > FK must reference (match) PK, UK column or null      
      - 5 catagory of SQL statements 
        CAT1: SELECT
              select   -- data retrieval
        CAT2: DML
              MIDU (merge, insert, delete, update)
        CAT3: DDL
              CDART (create, drop, alter, rename, truncate)
        CAT4: TC (transaction control)
              CSR (commit, savepoint, rollback)
        CAT5: DCL (data control language)
              GR (grant, revoke)
      - delete is DML; while truncate is DDL
      - between a and b --> in [a,b]
   
   1. select statement can do:
      - projection:    'select * from' is also a projection 
      - selection:     where clause
      - joining:       
   
   2. case in-sensitive, keyword can't be abbreviated or split across line
   
   3. 'from' clause can't use '+,-,*,/' operator
       
   4. null
      - (true and null) = (false or null) = null
      - true or null   = true
        false and null = false
      - salary/0 ---> error
        salary/null = null
        in (1, 3, 5, null, ...) = unkown

   5. column alias
      AS is optional, "..." means

   6. literal      
      - character, number, date --> 'Heureux', 139, '12-OCT-04'
      - char, date literal must be enclosed with ' ';
   
   7. distinct
      - must immediately follows 'select'   

   8. desc
      - isql*plus command:  desc TVS
      
   STS-20
      - select * from a is also a projection
      - iSQL*PLUS use "-" for longstatement
      - SQL vs. iSQL*PLUS
        SQL is a command language used for communicating to the server,
        iSQL*PLUS is not although is contains its own command language
        and can be used to create scripts    
      - SQL is ANSI standard language