Question

Problemi Slaterrier
Problem Statement
1 select from B. Display teacherid and firstname of the teacher(s) who have NOT been allocated to any
subject(s). For the given sample data, following record will feature as part of the output
along with other record(s).
Note: For the given requirement, display UNIQUE records wherever applicable.
Marks:2
Sample Output
TEACHERID
T305
Table Name : TEACHER
FIRSTNAME
Jecy
Column
Name
Data type and
Size
Constraints
teacherid
VARCHAR2(6)
PRIMARY
KEY.CHECK
NOT NULL
firstname VARCHAR2(30)
middlename VARCHAR2(30)
lastname VARCHAR2(30)
Description
Unique id of the teacher. Starts
with T
First name of the teacher
Middle name of the teacher
Last name of the teacher
Location where the teacher
belongs to
location
VARCHAR2(30)

Answer 1

Answer:

The carnage of World War II was unprecedented and brought the world closest to the term “total warfare.” On average 27,000 people were killed each day between September 1, 1939, until the formal surrender of Japan on September 2, 1945.

Answer 2

The following code is given below:

SQL as DML: Data Manipulation Language --Add test data to the tables.

INSERT INTO students (sid, name) VALUES(1, 'Simon');

INSERT INTO students (sid, name) VALUES(2, 'Alvin');

INSERT INTO students (sid, name) VALUES(3, 'Theo');

INSERT INTO students (sid, name) VALUES(4, 'Brittany');

INSERT INTO students (sid, name) VALUES(5, 'Jenette');

INSERT INTO students (sid, name) VALUES(6, 'Elenor');

INSERT INTO students (sid, name) VALUES(7, 'Stu');

select *

from Students;

INSERT INTO teachers (tid, name) VALUES (1, 'Washington');

INSERT INTO teachers (tid, name) VALUES (2, 'Adams');

INSERT INTO teachers (tid, name) VALUES (3, 'Jefferson');

INSERT INTO teachers (tid, name) VALUES (4, 'Lincoln');

select *

from teachers;

INSERT INTO subjects (subid, name) VALUES (1, 'History');

INSERT INTO subjects (subid, name) VALUES (2, 'Biology');

INSERT INTO subjects (subid, name) VALUES (3, 'SF');

select *

from subjects;

INSERT INTO grade (studentID, teacherID, subjectID, grade) VALUES (1, 2, 1, 'A');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (1, 2, 2, 'B');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (7, 4, 3, 'C+');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (7, 3, 2, 'F');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (6, 2, 1, 'B+');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (2, 4, 3, 'C');

INSERT INTO grades (studentID, teacherID, subjectID, grade) VALUES (3, 4, 3, 'B');

select *

from grades;

Students in order by name:

select *

from students

order by name ASC;

Names of students in any class taught by Adams:

select name

from students

where sid in

(select studentID

from grades

where teacherID in

(select tid

from teachers

where name = 'Adams')

);

Names of teachers who taught Biology:

select name

from teachers

where tid in

(select teacherID

from grades

where subjectID in

(select subid

from subjects

where name = 'Biology')

);

Names of students who have not yet taken any classes:

select name

from students

where sid not in

(select studentID

from grades);

Names of students in the same class:

select name

from students

where sid in

(SELECT studentID

FROM grades g1

WHERE

(SELECT COUNT(*)

FROM grades g2

WHERE g1.subjectID = g2.subjectID

AND g1.teacherID = g2.teacherID ) > 1

ORDER BY subjectID

);

select t.name as "Teacher",

sub.name as "Subject",

s.name as "Student"

from grades g1,

grades g2,

students s,

teachers t,

subjects sub

where g1.teacherID = g2.teacherID

and g1.subjectID = g2.subjectID

and g1.studentID = s.sid

and g1.teacherID = t.tid

and g1.subjectID = sub.subid

order by t.name, sub.name, s.name;

Hence, this the code of this problem.

#SPJ3