Use the sampleSQL database to do the following. Please show your code works. Cre

Question

Use the sampleSQL database to do the following. Please show your code works.

Create basic queries, at least 8 ones.

Create stored procedure and stored functions. (At least 4 stored procedures and 3 functions are required)

usage of cursor

Provide a description about the function of each stored procedure (function).

Here is the sample database SQL which is a student information system that provides information about students, and what courses they are enrolled in, their teachers, and address of each student.

create table student( student_num varchar2(10) PRIMARY KEY, Fname varchar2(20), Lname varchar2(20),
age decimal(5,2), GPA REAL, CHECK (GPA>=0.00), CHECK (GPA<=4.00), gender varchar2(10),
major varchar2(30), year varchar2(20));

create table address( s_number varchar2(10) references student(student_num), street varchar2(20),
city varchar2(20), state varchar2(20), zipcode number(10), country varchar2(20));

create table instructor ( I_id varchar2(10) PRIMARY KEY, name varchar2(20),
dept varchar2(20));

create table section( SectionNumber varchar2(10) PRIMARY KEY, term varchar2(10), year number(10),
location varchar2(20), s_id varchar2(10) references student(student_num),
instructor_id varchar2(10) references instructor(I_id));

create table course( course_num varchar2(10) PRIMARY KEY, name varchar2(20),
creditHours number(5), section_id varchar2(10) references section(SectionNumber));

insert into student values('11111111', 'Mike', 'Smith', '19', '3.2', 'M', 'Computer Science',
'junior');
insert into student values('22222222', 'Lisa', 'Brown', '20', '3.4', 'F', 'Computer Science',
'senior');
insert into student values('33333333', 'Jake', 'White', '18', '3.6', 'M', 'Computer Science',
'freshman');
insert into student values('44444444', 'Brian', 'Andrews', '21', '2.9', 'M', 'Computer Science',
'senior');

insert into address values('11111111', '8 Market Street', 'Oneonta', 'NY', '13820', 'US');
insert into address values('22222222', '14 Grove Lane', 'Florence', 'MA', '14215', 'US');
insert into address values('33333333', '21 Blue Drive', 'Oneonta', 'NY', '13820', 'US');
insert into address values('44444444', '21 Orange Way', 'Oneonta', 'NY', '13820', 'US');

insert into instructor values('1212', 'Dr. Brown Walter', 'Mathematics');
insert into instructor values('3232', 'Dr. Algo Rhythm', 'Computer Science');

insert into section values( '001', 'fall', '2017', 'Fitzelle', '11111111', '1212');
insert into section values( '002', 'fall', '2017', 'IRC', '22222222', '1212');
insert into section values( '003', 'fall', '2017', 'Science Building', '33333333', '3232');
insert into section values( '004', 'fall', '2017', 'Science Building', '44444444', '3232');

insert into course values( '9494', 'Intro to databases', '3', '001');
insert into course values( '8765', 'Computer Arch', '4', '002');
insert into course values( '2323', 'Intro to Business', '3', '002');
insert into course values( '4786', 'Intro to Biology', '4', '003');

Explanation / Answer

1. Create basic queries, at least 8 ones

The above query retrieves the student information along with his address.

Select I_id,name,SectionNumber,course_num from instructor,section,course where instructor.I_id=section.instructor_id and section.SectionNumber=course.section_id;

This query retrieves the I_id and his section_number and the course he teaches.

Alter table student modify column gender varchar2(6);

The above query modifies the column gender in the table structure.

Update student set gender = ‘Female’ where student_num= 11111111;

The above query sets gender data to female whose id= 11111111;

The above query deletes the course having id=4786

This query deletes the table named address

This query add's the check constrainst to the column gender.

This query creates the table students_dup with the given students only.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.