unsw|comp3311 辅导 week4 数据库|solution|assignment|sample|

对本文有疑问可以加微信 Tutor_0914联系。也可以访问我的个人辅导网站：
tutoryou

文章目录

- In operator may be wrong
- Division
- Partitions
- recursive queries
- plpgsql
- 学习一门语言要注意的地方

In operator may be wrong

SELECT * FROM suppliers WHERE supplier_name IN ('Microsoft', 'Oracle', 'Flowers Foods'); SELECT * FROM suppliers WHERE supplier_name = 'Microsoft' OR supplier_name = 'Oracle' OR supplier_name = 'Flowers Foods';

文章图片

Division division 是如何达到使用except代表的功能的？

文章图片

Division (cont)
Not all SQL implementations provide a division operator
But can be achieved by combination of existing operations
Example: Find bars that each sell all of the beers Justin likes.

SELECT DISTINCT a.bar FROMSells a WHERENOT EXISTS ( (SELECT beer FROM Likes WHERE drinker = 'Justin') EXCEPT (SELECT beer FROM Sells b WHERE bar = a.bar) );

Partitions 和group by的区别，就是partition原来有多少row，处理之后还有多少row，而且可使用的函数也更多。
partition 使用
recursive queries 难理解，在很多层级结构的数据库中使用。
plpgsql 一种嵌入在dbms中的过程式语言
这种语言的function和view很相似。
学习一门语言要注意的地方数据类型
if else
for
while
PLpgSQL constants and variables can be defined using:
【unsw|comp3311 辅导 week4】standard SQL data types (CHAR, DATE, NUMBER, …)
user-defined PostgreSQL data types (e.g. Point)
a special structured record type (RECORD)
table-row types (e.g. Branches%ROWTYPE or simply Branches)
types of existing variables (e.g. Branches.location%TYPE)
There is also a CURSOR type for interacting with SQL.
Variables can also be defined in terms of:
the type of an existing variable or table column
the type of an existing table row (implict RECORD type)
Examples:
quantity INTEGER;
start_qty quantity%TYPE;
employee Employees%ROWTYPE;
– or
employee Employees;
name Employees.name%TYPE;