我正在用SQL编写一个创建视图程序,上一条创建视图语句给我带来了一些麻烦。对于这个观点声明,我想检索来自哥伦比亚市所有大学的所有uid。然后,我想使用子查询来获取此人的名字和姓氏的信息。官方描述如下
Write a query that returns only the uid value for all universities in the city Columbia. Then use
that query with an IN sub-query expression to retrieve the first and last names for all people that
go to school in Columbias
现在我将发布我为createtable语句编写的代码,然后发布我将用于view语句的两个表。感谢您提前提供的帮助。
CREATE VIEW inn AS
SELECT a.uid
FROM letsdoit.university as a
WHERE b.fname, b.lname IN(SELECT b.fname, b.lname FROM letsdoit.person as b WHERE (a.city = "Columbia"));
表格为
Table "letsdoit.university"
Column | Type | Modifiers
-----------------+-----------------------+--------------------------------------
uid | integer | not null default nextval('university) uid_seq'::regclass)
university_name | character varying(50) |
city | character varying(50) |
Table "letsdoit.person"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------------------
pid | integer | not null default nextval('person_pid_seq'::reg class)
uid | integer |
fname | character varying(25) | not null
lname | character varying(25) | not null
根据问题陈述,您被指示创建的第一个查询应该是IN的子查询,并且应该用作查找该大学人员的名字和姓氏的标准(因为UID同时在PERSON和university上),因此您的视图如下:
create view inn as
select fname, lname
from letsdoit.person
where uid in (select uid from letsdoit.university where city = 'Columbia')