pgsql数据库作业练习4-5

感觉这些题大部分都是比较基础的，所以以后就放一些做的时候有卡壳的，和有多种解法的一些题~
建表语句都放在最后了，可供练习~

练习4

框架

题目及解答

-- 4.5 Select all data from all movie theaters 
    -- and, additionally, the data from the movie that is being shown in the theater (if one is being shown).
SELECT * FROM movies JOIN movietheaters ON movies.code = movietheaters.movie;
--------
SELECT *
   FROM MovieTheaters LEFT JOIN Movies
   ON MovieTheaters.Movie = Movies.Code;
   
-- 4.7 Show the titles of movies not currently being shown in any theaters.
SELECT title FROM movies left JOIN movietheaters ON movies.code = movietheaters.movie
WHERE movietheaters.movie is NULL;

----------
select title 
from movies
where code not in ( 
select movie from movietheaters
);
/* With subquery */
 SELECT Title FROM Movies
   WHERE Code NOT IN
   (
     SELECT Movie FROM MovieTheaters
     WHERE Movie IS NOT NULL
   );

笔记

left join 和 right join 区别
一、left join，“左连接”，表1左连接表2，以左为主，表示以表1为主，关联上表2的数据，查出来的结果显示左边的所有数据，然后右边显示的是和左边有交集部分的数据。
二、right join，“右连接”，表1右连接表2，以右为主，表示以表2为主，关联查询表1的数据，查出表2所有数据以及表1和表2有交集的数据。
三、join，其实就是“inner join”，为了简写才写成join，两个是表示一个的，内连接，表示以两个表的交集为主，查出来是两个表有交集的部分，其余没有关联就不额外显示出来

练习5

框架

题目及解答

-- 5.4  Obtain the names of all providers who supply piece 1.
SELECT name FROM providers JOIN provides ON providers.code = provides.provider
WHERE piece = 1;
-----------我做复杂了
select Name 
from Providers
where Code in (
select  Provider from provides where Piece = 1
); 

select Providers.Name 
from Providers join Provides
on Providers.Code = Provides.Provider
where Provides.Piece = 1;

-- 5.5 Select the name of pieces provided by provider with code "HAL".
SELECT name FROM pieces JOIN provides ON pieces.code = provides.piece
WHERE provides.provider = 'HAL';
----------
select Name from Pieces
where Code in (