Is it possible to join these tables a view and combined columns?

Question

I currently have a few tables that look like this:

create table Actors
(ActorID    varchar (20) primary key not null,
FirstName   varchar (35) null,
LastName    varchar(35) null,
"Address"   varchar (30) null,
Country varchar (30) null,
"State/Province"    varchar (30) null,
PhoneNumber varchar (15)  null
);

go

    insert into Actors
    values ('A-63','Tom','Hanks','4332 Whalibee Way','US','Washington','555-619-9905') ,
    ('A-42','Sandra','Bullock','63 Sharridon Ave.','US','Texas','329-556-3030');
go

create table Director
    (   DirectorID  varchar (20) primary key not null,
    FirstName   varchar (40) null,
    LastName    varchar(40) null,
    MovieID varchar(20) foreign key references Movies (MovieID) null,
    "Address" varchar ( 30) null,
    Country varchar (35) null,
    "State/Province"    varchar (35) null,
    PhoneNumber     varchar (15) null
    );
go

    insert into Director values
    ('D700','David','Speilberg','MO-57','9000 Cheque St','US','California','432-553-2267'),
    ('D-900','William','Bruckheimner','MO-31','68585 lava lane','US','California','519-242-2543');
go

create table Producer
    (ProducerID     varchar (20) primary key not null,  
    FirstName   varchar (40) null,
    LastName    varchar (40) null,
    MovieID varchar (20) foreign key references Movies(MovieID) null,
    "Address"   varchar (30) null,
    Country varchar (35) null,
    "State/Province"    varchar (35) null,
    PhoneNumber varchar (15) null
    );
    go

    insert into producer values 
    ('P-123','RJ','Abrams','MO-57','45 Canterbelly Cres.','US','California','556-876-4134'),
    ('P-82','Malcolm','Brooks','MO-62','678 Undertree rd','US','Arizona','897-332-6633');
go

Create table Movies 
( MovieID varchar(20) primary key not null,
    MovieName   varchar (40) null ,
    "Type"  varchar (20) null,
    Rating  varchar (20) null,
    Director    varchar (35) null,
    Producer    varchar (35) null,
    MainActor1  varchar (35) null,
    MainActor2  varchar (35) null,
    SupportingActor1    varchar (35) null,
    SupportingActor2    varchar (35) null,
    ReleaseDate Date null,
    Description varchar(500) null
    );
    go

    Insert into Movies (MovieID,MovieName,"Type",Rating,Director,Producer,MainActor1,MainActor2,SupportingActor1,SupportingActor2,ReleaseDate,"Description")
    values ( 'MO-150','The Legend Of Zelda','Fantasy','R','','','','','','','July 23 2018',''),
    (   'MO-216','The SQL Games:Replication','Science Fiction','PG','','','','','None','','March 20 2020','');
go

current Format: Robert Downey JR. FirstName| LastName Ben Affleck Christian Bale Will Smith Charlize Theron Angelina Jolie Jessica Alba Cameron Diaz Jennifer Anniston Bradley Cooper Sandra Bullock Leonardo DiCaprio Matt Damon Chris Hemsworth Tom Hanks Mila Kunis Scarlet Johansson Emma Stone Julia Roberts

Now I have a Table for Actors, Directors and Producers as well as a Movie table, all with their own ID columns and a MovieInfo table with foreign keys to link them.

I was wondering if there was a way to make a view that would take the first and last names and put them together in 1 column for the Actors,Producers,Directors and Movies column and have them joined.

So the table would look like this:

Actors(col1) Producers(col2) Directors (col3) Movies(col4) (Sandra Bullock) (RJ Abrams) (David Speilberg) (Late For Class)

Any help is greatly appreciated. I know if it was possible to do a view as such, it'd probably be very complicated.

Answer 1

You can join the values of two columns by using the || operator.

SELECT FirstName || ' ' || LastName as name FROM Actors

would return a single column called "name" that contains the Actors FirstName and LastName columns concatenated together. Just use that same logic for all of the tables, joined appropriately of course.