\c postgres公司DROP数据库练习；创建数据库练习；\c练习--练习1：基础知识。根据下面的Departments表，创建一个名为Employees的SQL表，用于存储员工编号、员工姓名、部门和薪资信息。“员工”表的主键应该是员工编号。在Employees表上创建一个外键，该外键基于department_id字段引用Departments表。创建表格部门(department_id INT不为空，部门名称文本不为空，约束部门_pk主键（部门_id）);创建表格员工(employee_number INT主键，employee_name文本不为空，部门INT NOT NULL REFERENCES部门（department_id），工资INT不为空);插入部门（department_id，department _name）值（0，'CSE'）；插入部门价值（1，“DIT”）；插入员工价值（0，“Matti”，0，1000000）；--练习2：--练习2：供应商。考虑以下关系模式：--供应商（*sid:integer*，sname:string，--城市：字符串，街道：字符串）--部件（*pid:integer*，pname:string，color:string）--目录（*sid:integer，pid:integer*，成本：real）--查找提供非蓝色零件的所有供应商的名称。创建表格供应商(sid INT主键，sname文本不为空，城市文本不为空，街道文本非空);输入供应商价值（0，“沃尔沃”，“哥德堡”，“海森根”）；输入供应商价值（1，‘英特尔’，‘西雅图’，‘第一大道’）；创建表格部件(pid INT主键，pname文本不为空，颜色文本非空检查（颜色IN（'red'，'green'，'blue'））);插入零件值（0，“X40”，“红色”）；插入零件值（1，“V44”，“绿色”）；插入零件值（2，‘i7-440’，‘蓝色’）；插入零件值（3，‘V50’，‘红色’）；--目录（*sid:integer，pid:integer*，成本：real）创建表格目录(pid INT NOT NULL引用部分（pid），sid INT NOT NULL参考供应商（sid），成本浮动不为空，主键（pid，sid）);插入目录值（0、0、5000.5）；插入目录值（1、0、10000.4）；插入目录值（2、1、100.3）；--查找提供非蓝色零件的所有供应商的名称。选择sname来自供应商何处输入sid（选择供应商.sidFROM目录、零件、供应商WHERE Parts.pid=目录.pidAND Suppliers.sid=目录.sid和颜色！='蓝色’）；--练习3：员工。考虑下表：--员工（*empId*，姓名，部门，工资）--empId和name列的类型为text，而department和salary为integer。DROP TABLE员工；创建表格员工(清空文本主键，name文本不为空，部门INT不为空，工资INT不为空）；插入员工价值观（0，‘Gloin’，15000）；纳入员工价值观（1，“Bifur”，2000年5月）；纳入员工价值观（2，‘Bofur’，21000）；引入员工价值观（3，‘Thrain’，12000年1月）；插入员工价值（4，‘巴林’，51500）；插入员工价值观（5，“JOE”，5，1500）；插入员工价值观（6，‘joe’，5，1500）；插入员工价值观（7，‘Joe’，51500）；--b）编写查询以查找每个部门的最高工资。选择部门，MAX（工资）来自员工部门分组；--a）编写一个查询，查找薪水高于第5部门任何人的员工（您可以假设第5部门有多个员工）。SELECT名称来自员工WHERE salary>（选择MAX（薪资）来自员工WHERE部门=5GROUP BY部门）；--c）查找包含单词“Joe”的所有员工记录，无论它是存储为Joe、Joe还是Joe。选择*来自员工上部（名称）如“%JOE%”；---练习4：--员工（*employeeId*，employeeName，街道，城市）--公司（*companyId*，companyName，city）--工作（*员工、公司*、工资）--经理（*经理、员工*）--员工工作的公司及其当前工资的信息存储在关系works中。假设所有人最多为一家公司工作。关于哪些员工具有经理角色以及他们管理谁的信息存储在关系“管理”中。--员工（*employeeId*，employerName，street，city）DROP TABLE员工；创建表格员工(员工身份信息主键，employeeName文本不为空，街道文本不为空，城市文本不为空);--公司（*companyId*，companyName，city）创建表格公司(companyId INT主键，公司名称文本不为空，companyCity文本不为空);--工作（*员工、公司*、工资）创建表格工程(员工INT NOT NULL参考员工（employeeId），公司INT NOT NULL参考公司（companyId），工资INT不为空，主键（员工、公司）);--经理（*经理、员工*）CREATE TABLE管理(经理INT NOT NULL参考员工（employeeId），员工INT NOT NULL参考员工（employeeId），主键（经理、员工）);输入员工价值（0，‘AAA’，‘1st Street’，‘New York’）；纳入员工价值观（1，‘BBB’，‘1st Street’，‘New York’）；插入员工价值观（2，“CCC”，“2nd Street”，“New York”）；纳入员工价值观（3，‘DDD’，‘1st Street’，‘Seattle’）；纳入员工价值观（4，“EEE”，“4th Street”，“Seattle”）；纳入员工价值观（5，‘FFF’，‘5th Avenue’，‘Palo Alto’）；纳入员工价值观（6，“GGG”，“6th Avenue”，“Palo Alto”）；引入员工价值观（7，‘HHH’，‘1st Avenue’，‘Mountain View’）；插入公司价值（0，“第一银行公司”，“纽约”）；引入公司价值（1，“亚马逊”，“西雅图”）；引入公司价值（2，“星巴克”，“西雅图”）；引入公司价值观（3，“脸书”、“帕洛阿尔托”）；引入公司价值（4，“小型银行公司”，“Palo Alto”）；引入公司价值（5，“谷歌”，“山景”）；插入工程价值（0、0、100000）；插入工程价值（1、1、10000）；插入工程价值（2,0,5000）；插入工程价值（3、2、5000）；计入工程价值（4、3、100000）；插入工程价值（5、4、500）；计入工程价值（6、5、100000）；插入工程价值（7、5、120000）；INSERT INTO管理值（0，2）；引入管理价值观（7、6）；引入管理价值观（4，4）；--为以下任务编写SQL查询：--a）找出为“第一银行公司”工作且收入超过10000美元的所有员工的姓名、街道地址和城市。选择员工姓名、街道、城市来自员工、工厂、公司WHERE employeeId=员工AND companyId=公司AND companyName='第一银行公司'工资>10000；--b）找出以下所有员工的姓名：--i）与他们工作的公司住在同一个城市。选择员工姓名来自员工、工厂、公司WHERE employeeId=员工AND companyId=公司AND city=公司城市；--ii）与经理住在同一城市的同一条街上。选择E.employeeNameFROM员工作为E，员工作为M，管理WHERE E.employeeId=员工AND M.employeeId=经理AND E.街道=M.街道和E.city=M.city；--iii）收入超过“小型银行公司”的每位员工选择员工姓名来自员工、工程WHERE employeeId=员工AND salary>（选择MAX（薪资）来自公司、工程WHERE company=公司IDAND companyName=“小型银行公司”）；--c）找出工资总额最小的公司的名称（即工资总额最小）。选择companyName、company、SUM（工资）作为总额来自工厂、公司WHERE company=公司IDGROUP BY（公司，公司名称）订单按金额ASC限制1；--d）假设Employee关系有一个值为NULL的email列，编写一个查询来更新email值。--更新员工设置电子邮件=“…@…”其中employeeId=5；--e）找到所有非管理人员的员工。从员工中选择employeeName其中employeeId NOT IN（从管理层中选择经理）；--练习5：--病房（数量、床位数）--患者（pid、姓名、年份、性别）--患者住院（pid，病房）--测试（患者、测试日期、测试小时、温度、心率）创建表病房(数字INT主键，numBeds INT NOT NULL CHECK（numBeds>0）);创建患者表格(pid VARCHAR（10）主键，name文本不为空，年份INT NOT NULL CHECK（年份>0），bsex VARCHAR（1）检查（bsex IN（'M'，'F'））);创建表PatientInWard(pid VARCHAR（10）参考患者（pid），病房内部参考病房（编号），主键（pid，病房）);--测试（患者、测试日期、测试时间、温度、心率）创建表格测试(患者VARCHAR（10）非空参考患者（pid），测试日期不为空，testHour时间不为空，温度浮动不为空，心率浮动不为空，主键（患者、测试日期、测试时间）);插入病房（数量、床位）值（1,10）；插入病房（数量、床位）值（2,10）；插入病房（数量、床位数）值（3,3）；插入病房（数量、床位数）值（4,3）；插入患者（pid、姓名、年份、bsex）值（“P1”，“Andy”，1970，“M”）；插入患者（pid、姓名、年份、bsex）值（“P2”、“Benny”，1970，“M”）；插入患者（pid、姓名、年份、bsex）值（“P3”、“Lisa”，1976年，“F”）；插入患者（pid、姓名、年份、bsex）值（“P4”、“Anne”，1990年，“F”）；插入患者（pid、姓名、年份、bsex）值（“P5”、“Lucy”，1980，“F”）；插入患者（pid、姓名、年份、bsex）值（“P6”、“Penny”，1975，“F”）；插入患者（pid、姓名、年份、bsex）值（“P7”、“Sheldon”，1971，“M”）；插入患者（pid、姓名、年份、bsex）值（'P8'、'Barry'、1940、'M'）；插入患者住院（pid，病房）值（'P1'，1）；插入患者住院（pid，病房）值（“P2”，1）；插入患者住院（pid，病房）值（“P3”，2）；插入患者住院（pid，病房）值（“P4”，2）；插入患者住院（pid，病房）值（'P5'，3）；插入患者的病房（pid，ward）值（'P6'，3）；插入患者住院（pid，病房）值（'P7'，4）；插入患者住院（pid，病房）值（'P8'，4）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P1”、“2018-11-01”、“10:00”、35.5、150）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P1”、“2018-11-01”、“11:00”、36、152）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P2”、“2018-11-01”、“10:10”、36.2、140）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P2”、“2018-11-01”、“15:00”、36、150）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P2”、“2018-11-01”、“17:00”、36、150）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P3”、“2018-11-02”、“10:00”、35.5、150）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P3”、“2018-11-02”、“16:00”、35、145）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P3”、“2018-11-02”、“18:00”、35.5、150）；插入测试（患者、测试日期、测试时间、温度、心率）值（“P8”、“2018-11-02”、“11:00”、39、150）；插入测试（患者、测试日期、测试时间、温度、心率）值（'P8'、'2018-11-02'、'18:00'、37150）；--病房是由它的号码来标识的。numBeds属性是该病房的床位数。患者通过其个人身份号码进行识别。每个患者的姓名、出生年份和生物性别（“M”或“F”）存储在“患者”关系中。每个患者分配到的病房存储在PatientInWard关系中。在住院期间，患者将接受常规检查。记录每次测试的日期、时间和测量值（温度和心率），并将其存储在数据库中。患者在住院期间通常会接受多项测试。--使用适当的约束和类型在SQL中实现模式，并执行以下操作：--a） 编写一个SQL查询，查找1950年之前出生的患者在每次测试中测得的温度和心率。选择温度，心率来自测试，患者其中pid=患者AND年份<1950；--b）创建一个视图，FreeBeds（ward，numBeds），其中ward是病房号，numBeds是该病房中可用的床位数。创建VIEW FreeBeds（ward，numBeds）AS(选择病房，（床位数-计数（pid））来自病房，患者住院WHERE ward=数字分组依据（病房，床位数）);--练习6：--行星（恒星、名称、距离、质量、大气层、氧气、水）--我们假设所有恒星都有不同的名字，行星的名字只有在它们的恒星系统中才是唯一的。为了简单起见，我们还假设每个恒星系统正好有一颗恒星，并且所有行星都以不同的距离围绕其恒星有圆形轨道。行星的位置表示其在恒星系统中的顺序，例如地球是继水星和金星之后绕太阳运行的第三颗行星。如果一颗行星有氧气或其他气体，那么它就有大气层。如果没有大气层，就没有气体。行星的表面要么全是水，要么全是陆地，要么介于两者之间。--执行以下操作：--a）在SQL表中实现关系，具有合理的类型和约束。存储距离以百万公里为单位，例如地球的值应为149.6。创建桌面行星(星号文本不为空，name文本不为空，distance FLOAT NOT NULL CHECK（距离浮动非空检查）（距离>0），质量浮动非空检查（质量>0），大气布尔值不为空，氧气输入不为空限制氧气百分比检查（氧气>=0且氧气<=100），限制氧气_大气检查（（大气和氧气>0）或（（非大气）和氧气=0）），水浮动不为空约束水检查（水>=0且水<=100），主钥匙（星形，名称），独特（星形，距离）);--b）假设该关系包含行星Duna“围绕一颗名为Kerbol的恒星运行”。编写一个SQL查询，找出有多少行星在其恒星周围的轨道上，这些行星比Duna围绕Kerbol的轨道大。插入行星值（‘Kerbol’，‘Jool’，380.4，100000，True，10，1）；插入行星值（‘Kerbol’，‘Duna’，170.4，20000，True，10，1）；插入行星值（“Kerbol”，“Kerbin”，149.4，50000，True，21，71）；插入行星值（'Kerbol'，'Eve'，100.2，10000，False，0，0）；插入行星值（“Kerbol”、“Moho”、45.4、5000、False、0、0）；--b）选择计数（*）FROM Planets WHERE distance>（选择距离FROM Planet WHERE star='Kerbol'AND name='Duna'）；--c）如果一颗行星满足以下条件，我们就说它是可居住的：--i.它的轨道距离其恒星1亿至2亿公里（含1亿公里）（即在，，Goldilocks区），--ii.它有大气层，含氧量在15%至25%（含）之间，--它的表面有水。--创建视图Habitable（名称、恒星、状态），如果恒星周围的行星名称适合居住，则状态为“宜居”，如果行星不适合居住，状态为“不适合居住”。CREATE VIEW习惯助手（姓名、星级）AS(选择姓名，星号来自行星其中距离>=100且距离<=200AND大气与氧>=15与氧<=25AND水>0);CREATE VIEW宜居（姓名、星级、状态）AS(（选择名称、星星、“宜居”来自HabitHelper）联合国（选择名称、星号、“不适合居住”来自行星WHERE（name，star）NOT IN（请从HabitHelper中选择name、star）);