: : i have information and stuff on doing joins but anyone got any good examples? can't seem to get it to work
: : this is my code i need to show like a name of a student who their teacher is and what course it is for one table *shrugs*
:
:
: use master
: go
:
: drop database school
: go
:
: create database school
: go
:
: use school
: go
:
: create table students(
: SID int Primary key Identity (1,1),
: SFName varchar(20) not null unique,
: SLName varchar(20) not null unique,
: SPhone char(7) not null check (SPhone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or SPhone like ' '),
: Saddress varchar(20) not null
: );
: go
:
: insert into students values ('brian','Puettner','6994001','persianterrace');
: insert into students values ('chris','ollie','6980654','bear road');
: insert into students values ('dan','chapman','6687683','bladder');
: insert into students values ('eric','graber','7275568','snowshoe');
: insert into students values ('krystal','smith','7275548','berrystreet');
: go
:
:
: create table Faculty(
: FID int Primary key Identity (1,1),
: FFName varchar(20) not null ,
: FLName varchar(20) not null unique,
: FPhone char(7) not null check (Fphone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or FPhone like ' '),
: Faddress varchar(20) not null
: );
: go
:
: insert into faculty values ('Mr.','chan','4589000','buckleyroad');
: insert into faculty values ('Mr.','kohut','6991090','churchroad');
: insert into faculty values ('Mr.','sweeney','6871211','hamiltonroad');
: insert into faculty values ('Mr.','kamat','7276666','hamiltonroad');
: go
:
: create table Course(
: CID int primary key identity (1,1),
: CName varchar(20) not null
: );
: go
:
: insert into Course values ('java');
: insert into Course values ('c++');
: insert into Course values ('intro to pcs');
: insert into Course values ('linux');
: go
:
: create table teaching(
: FID int not null,
: CID int not null,
: Constraint Teaching_PK primary key(FID, CID),
: Constraint TEACHING_FID_FK FOREIGN KEY (FID)
: REFERENCES FACULTY(FID) ON DELETE CASCADE ON UPDATE CASCADE,
: Constraint TEACHING_CID_FK FOREIGN KEY (CID)
: REFERENCES COURSE(CID) ON DELETE CASCADE ON UPDATE CASCADE
: );
:
: insert into teaching values (1,1);
: insert into teaching values (2,2);
: insert into teaching values (3,3);
: insert into teaching values (4,4);
: go
:
: create table Classes(
: SID int not null,
: CID int not null,
: Constraint Classes_PK primary key (SID, CID),
: Constraint Classes_SID_FK FOREIGN KEY (SID)
: REFERENCES Students(SID) ON DELETE CASCADE ON UPDATE CASCADE,
: Constraint Classes_CID_FK FOREIGN KEY (CID)
: REFERENCES Course(CID) ON DELETE CASCADE ON UPDATE CASCADE
: );
:
: insert into classes values (1,1);
: insert into classes values (1,2);
: insert into classes values (2,2);
: insert into classes values (2,3);
: insert into classes values (3,3);
: insert into classes values (3,4);
: insert into classes values (4,3);
: insert into classes values (4,4);
: go
:
:
ok on this code my classes table and my other table where i use numerals as values how do i set that up to use items from other tables ? i know its prolly simple like adding a quote in between or something which i tried and it didn't do anything suggestions? so instead of like insert into classes values (4,4) the names and such would come up instead of 4,4 i can only read so much :(
