Question

sai veterent Course it ACCT 321 etro te Accounbsg 333 o Grade 168301458 A , wierna [204 し5432910) 41291073 | ACCT 131

Please help

Write the SQL statements necessary to complete the following tasks:

a) Use the ERD and provided data to create the four tables.

b) Insert the provided data into each of the four tables.

c) Update InstructorLoc for Instructor 111 to DA 206.

Answer 1

drop table student cascade constraints;
drop table instructor cascade constraints;
drop table course cascade constraints;
drop table courseGrade cascade constraints;

create table STUDENT(StudentID int primary key,
                       Firstname varchar(20),
                       lastname varchar(20),
                       CampusAddress varchar(20),
                       Major varchar(10));
                      
create table instructor(InstructorID int primary key,
                       Firstname varchar(20),
                       lastname varchar(20),
                       InstructorLoc varchar(20));
                      
                      
create table course (CourseId varchar(20) primary key,
                   CourseTItle varchar(30),
                   InstructorId int,
                   foreign key(InstructorId) references instructor(InstructorId));
                  
                  
create table CourseGrade(StudentId int,
                       CourseID varchar(20),
                       Grade Varchar(4),
                       Primary key(StudentId,CourseID),
                       foreign key(StudentId) references Student(StudentId),
                       foreign key(courseid) references Course(courseID));
                      
insert into student values(168300458,'Ean','William','208 Hillker','IS');
insert into student values(543291073,'Wan','Baker','112 Claurence','ACCT');

                      
insert into instructor values(111,'David','Lite','DA 230');
insert into instructor values(222,'Robert','Miller','DA 233');
insert into instructor values(333,'Ail','Ajma','PTC 205');
insert into instructor values(444,'Baria','Kerker','PTC 310');

                  
insert into Course values('IS 370','Bussiness Programming1',111);
insert into Course values('IS 372','System ANalysis',222);
insert into Course values('ACCT 121','Intro to Accounting',333);
insert into Course values('ACCT 230','Managirial Accounting',444);

                      
insert into CourseGrade values(168300458,'IS 370','A');                      
insert into CourseGrade values(168300458,'IS 372','B+');                      
insert into CourseGrade values(543291073,'IS 372','C');                      
insert into CourseGrade values(543291073,'ACCT 121','B');                      
insert into CourseGrade values(543291073,'ACCT 230','A');
          

select * from student;
select * from course;
select * from instructor;
select * from courseGrade;

update Instructor set InstructorLoc='DA 206' where InstructorId=111;
select * from instructor;

SQL> SQL> select*From student; STUDENTID FIRSTNAME LASTNAME CAMPUSADDRESS MAJOR William Baker 208 Hİİlker IS ACCT 168300458 Ean 112 Claurence 543291 973 Wan SQL> select *from course; INSTRUCTORID COURSEID COURSETITLE Bussiness Programming1 System ANalysis Intro to Accounting IS 370 IS 372 ACCT 121 ACCT 230 l^H* Managirial Accounting SQL> select *from instructor; INSTRUCTORID FIRSTNAME LASTNAME INSTRUCTORLOC 111 David Lite Miller Ajma DA 230 222 Robert 333 Ail 444 Baria DA 233 РТС 205 РТС 310 Kerker SQL> select * From courseGrade; GRAD STUDENTID COURSEID 1ό8300458 IS 370 168300458 IS 372 543291073 IS 372 543291073 ACCT 121 543291 973 ACCT 230

SQL> update Instructor set InstructorLoc="DA 206' Instructor|d=111; where 1 row updated. SQL> select from instructor; LASTNAME INSTRUCTORID FIRSTNAME INSTRUCTORLOC 111 David Lite Miller DA 206 222 Robert 333 Ail hh Baria DA 233 Ajma PTC 205 РТС 310 Kerker SQL> I