Can someone help me make these? Have code and some done. Table Data: CREATE TABL

Question

Can someone help me make these? Have code and some done.

Table Data:

CREATE TABLE STUDENT
(
Student_Number int Primary Key,
Student_FName varchar(25),
Student_LName varchar(25),
Student_Inital varchar (1),
Student_DOB date ,
Student_Join_date date,
Studnt_Address varchar(75)
);

CREATE TABLE INSTRUCTOR
(
Student_Number Int Primary Key,
Instrucor_Hire_date date,
Instructor_Rank Char(20),
FOREIGN KEY (Student_Number) REFERENCES STUDENT(Student_Number) Constraint STN FOREIGN KEY
);

CREATE TABLE CLASS
(
Class_ID Int Primary Key,
Class_Day Char(15),
Class_Time time,
Class_Dojo Char(25),
Student_Number Int ,
FOREIGN KEY (Student_Number) REFERENCES STUDENT(Student_Number)
);

CREATE TABLE MEETING
(
Meeting_Number Int Primary Key,
Meeting_Date date,
Class_ID Int --,
);   

CREATE TABLE ATTENDANCE
(
Meeting_Number Int ,
Student_Number Int ,
Attend_Role Char(60),
constraint MPK Primary key (Meeting_Number, Student_Number),constraint MPK
(Meeting_Number) REFERENCES MEETING(Meeting_Number), Constraint AMFK FOREIGN KEY
(Student_Number) REFERENCES STUDENT(Student_Number) Constraint ASFK
);

CREATE TABLE RANK
(
Rank_Number Int Primary Key,
Rank_Name Char(25) ,
Rank_Belt Char(25)
);

CREATE TABLE REQUIREMENT
(
Requirement_Number int Primary Key,
Requirement_Description varchar(75) ,
Rank_Number int ,
(Rank_Number) REFERENCES RANK(Rank_Number) Constraint RFK FOREIGN KEY
);

CREATE TABLE ATTAIN
(
Rank_Number int,
Student_Number int ,
Attain_Date date,
Constraint APK primary key (Rank_Number, Student_Number), Constraint APK
Constraint AFK1 FOREIGN KEY (Rank_Number) REFERENCES RANK(Rank_Number),Constraint AFK1 FOREIGN KEY
Constraint AFK2 FOREIGN KEY (Student_Number) REFERENCES STUDENT(Student_Number) Constraint AFK2 FOREIGN KEY
);

Questions so far:

-- This query is returning the full names of all the students who are instructors for class.

SELECT Student_FName , Student_LName
FROM STUDENT
INNER JOIN INSTRUCTOR
ON STUDENT.Student_Number=INSTRUCTOR.Student_Number;

-- This query returns the class id and class day based on the instructor who is teaching the class.

SELECT Class_ID , Class_Day
FROM CLASS
INNER JOIN STUDENT
ON STUDENT.Student_Number=Class.Student_Number;

-- This query returns the requirement number associated with the rank number(s).
SELECT Req_Num
FROM REQUIREMENT
INNER JOIN RANK
ON Rank.Rank_Number=Requirement.Rank_Number;

-- This query returns the date that a certain rank was attianed by a certain student.
SELECT Attain_Date , Student_Number
FROM ATTAIN
INNER JOIN RANK
ON ATTAIN.Rank_Number=Rank.Rank_Number;

-- This query returns the meeting number and meeting date based on class id.

SELECT Meeting_Number , Meeting_Date
FROM Meeting
INNER JOIN Class
ON Meeting.Class_ID=Class.Class_ID;

-- This query returns the Attendence Role of a person based off their student number.

Select Attend_Role from ATTENDANCE
join Student on Student.Student_Number = Attendance.Student_Number;

///

This query is returing all the student information ordered by Last name .
select * from STUDENT order by Student_LName;

This query is returing all the student information ordered by First name .
select * from STUDENT order by Student_FName;

This query is returning all the attendance information for the person associated with id student number 3.
select * from ATTENDANCE where Student_Number = 3;

DELIVERABLE III SQL Insert Statements Write a script or scripts to insert rows into each of the tables above to populate the tables. Be sure to insert at least 6 rows in each table. Again, be sure rows are entered into the tables in the correct order DELIVERABLE 25%) IS DUE NO LATER THAN EOD 12-6-2016 DELIVERABLE IV. SQL Queries. Write 12 SQL queries that will access the data in the database you have created. Write a sentence or two for each query to explain what the purpose of the query is and what it should retrieve. Be sure at least 6 of the queries join two or more tables.

Explanation / Answer

To insert values into the table we use insert statement in sql.

Syntax:- INSERT into <TABLE_NAME> values (<COLUMN1>,<COLUMN2>,.....);

TABLE_NAME ---> Give the specific table name which was created

<COLUMN>----> Column names which is given while creating the table

Inserting values to the STUDENT Table

1. insert into STUDENT values(1,'first1','lname1','K','01-01-1990','21-03-1995','Address with DNO');

2.insert into STUDENT values(2,'first2','lname2','L','01-01-1991','21-03-1996','Address with DNO');

3.insert into STUDENT values(3,'first3','lname3','M','01-01-1992','21-03-1997','Address with DNO');

4.insert into STUDENT values(4,'first4','lname4','N','01-01-1993','21-03-1998','Address with DNO');

5.insert into STUDENT values(5,'first5','lname5','O','01-01-1994','21-03-1999','Address with DNO');

6.insert into STUDENT values(6,'first6','lname6','M','01-01-1995','21-03-2000','Address with DNO');

Inserting values to the INSTRUCTOR Table

1. insert into INSTRUCTOR values(1,'10-02-1987','Proffesor');

2.insert into INSTRUCTOR values(2,'10-02-1987','Research Proffesor');

3.insert into INSTRUCTOR values(3,'10-02-1988','Associate Proffesor');

4.insert into INSTRUCTOR values(4,'10-02-1989','Assistent Proffesor');

5.insert into INSTRUCTOR values(5,'10-02-1990','Lecturer');

6.insert into INSTRUCTOR values(6,'10-02-1991','Lecturer');

Inserting values to the CLASS Table

1.insert into CLASS values(5,'Monday','08:30','21-04-1996',1);

2.insert into CLASS values(6,'Tuesday','08:30','23-04-1996',1);

3.insert into CLASS values(7,'Wednesday','08:30','28-04-1996',2);

4.insert into CLASS values(8,'Thursday','08:30','29-04-1996',3);

5.insert into CLASS values(9,'Friday','08:30','30-04-1996',4);

6.insert into CLASS values(10,'Saturday','08:30','31-04-1996',6);

Inserting values to the Meeting Table

1.insert into MEETING values(101,'20-02-1993',5);

2.insert into MEETING values(102,'20-03-1993',5);

3.insert into MEETING values(103,'20-04-1993',6);

4.insert into MEETING values(104,'20-05-1993',7);

5.insert into MEETING values(105,'20-06-1993',8);

6.insert into MEETING values(106,'20-07-1993',9);

Inserting values to the Attendence Table

1.insert into ATTENDANCE values (201 ,1 ,'Attended');

2.insert into ATTENDANCE values (202 ,2 ,'Attended');

3.insert into ATTENDANCE values (203 ,3 ,'Attended');

4.insert into ATTENDANCE values (204 ,4 ,'Attended');

5.insert into ATTENDANCE values (205 ,5 ,'Attended');

6.insert into ATTENDANCE values (206 ,6 ,'Attended');

Inserting values to the Rank Table

1.insert into RANK values(51,'FIRST GRADE' ,'BLACK');

2.insert into RANK values(52,'SECOND GRADE' ,'RED');

3.insert into RANK values(53,'THIRD GRADE' ,'GREEN');

4.insert into RANK values(54,'FOURTH GRADE' ,'NA');

5.insert into RANK values(55,'FIFTH GRADE' ,'NA');

6.insert into RANK values(56,'SIXTH GRADE' ,'NA');

Inserting values to the Requirement Table

1.insert into REQUIREMENT values(501,'Urgent Requirement' ,51);

2.insert into REQUIREMENT values(502,'Urgent Requirement' ,52);

3.insert into REQUIREMENT values(503,'Moderate Requirement' ,53);

4.insert into REQUIREMENT values(504,'Moderate Requirement' ,54);

5.insert into REQUIREMENT values(505,'Least Requirement' ,55);

6.insert into REQUIREMENT values(506,'LeastRequirement' ,56);

Inserting values to the Attain Table

1.insert into ATTAIN values (51,1 ,'04-06-2001');

2.insert into ATTAIN values (52,2 ,'04-06-2001');

3.insert into ATTAIN values (53,3 ,'04-06-2001');

4.insert into ATTAIN values (54,4 ,'04-06-2001');

5.insert into ATTAIN values (55,5 ,'04-06-2001');

6.insert into ATTAIN values (56,6 ,'04-06-2001');

The above are the insert statements which inserts 6 rows in every table which is specified. Please change the values with your required ones and execute.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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