Provide SQL query for the questions below: 1. Write and execute the SQL command

Question

Provide SQL query for the questions below:

1. Write and execute the SQL command to add a column for a math score to the student table. You do not need to add data.

2. Write and execute the SQL command to add a column for a subject to the t u t o r table. The only values allowed in this column will be ‘Reading’, ‘Math’, and ‘ESL’. You do not need to add data.

3. Which students have read scores above 3.5?

4. List the t u t o r s who have certification dates after March 2016 and are listed as ‘Active’.

5. If t u t o r s are paid $10 per hour, what is the total amount paid to each t u t o r per month, using the data from the tutor report? Make sure to sort your output by month, then by t u t o r_id.

6. What are the average, max, and min read scores for students?

7. List the t u t o r_id and student id who have been working together the longest amount of time.

8. Which t u t o r s have not yet submitted a report for MAR 2017?

9. List the June activity for all currently active students, including the number of hours they have received "tutoring" and the number of lessons covered.

10. Which t u t o r s need to be reminded to turn in reports?

*NOTE* I have to spell t u t o r with spaces in between because chegg doesn't allow me to spell them as usual without spaces.

------------------------------------------------------------------------------------------------

The text file:

create table t u t o r

(t u t o r_id number(3),

cert_date date,

status varchar2(20),

constraint pk_tutor primary key (t u t o r_id));

create table student

(student_id number(4),

read number(2,1),

constraint pk_student primary key (student_id));

create table match_history

(match_id number(3),

t u t o r_id number(3),

student_id number(4),

start_date date,

end_date date,

constraint pk_match_history primary key (match_id),

constraint fk1_match_history foreign key (t u t o r_id) references

t u t o r(t u t o r_id),

constraint fk2_match_history foreign key (student_id) references

student(student_id));

create table t u t o r_report

(match_id number(3),

month date,

hours number(3),

lessons number(3),

constraint pk_t u t o r_report primary key (match_id, month),

constraint fk1_t u t o r_report foreign key (match_id) references

match_history(match_id));

insert into t u t o r values (100, '05-JAN-2017', 'Active');

insert into t u t o r values (101, '05-JAN-2017', 'Temp Stop');

insert into t u t o r values (102, '05-JAN-2017', 'Dropped');

insert into t u t o r values (103, '22-MAY-2017', 'Active');

insert into t u t o r values (104, '22-MAY-2017', 'Active');

insert into t u t o r values (105, '22-MAY-2017', 'Temp Stop');

insert into t u t o r values (106, '22-MAY-2017', 'Active');

insert into student values (3000, 2.3);

insert into student values (3001, 5.6);

insert into student values (3002, 1.3);

insert into student values (3003, 3.3);

insert into student values (3004, 2.7);

insert into student values (3005, 4.8);

insert into student values (3006, 7.8);

insert into student values (3007, 1.5);

insert into match_history values (1, 100, 3000, '10-JAN-2017', null);

insert into match_history values (2, 101, 3001, '15-JAN-2017', '15-MAY-

2017');

insert into match_history values (3, 102, 3002, '10-FEB-2017', '01-MAR-

2017');

insert into match_history values (4, 106, 3003, '28-MAY-2017', null);

insert into match_history values (5, 103, 3004, '01-JUN-2017', '15-JUN-

2017');

insert into match_history values (6, 104, 3005, '01-JUN-2017', '28-JUN-

2017');

insert into match_history values (7, 104, 3006, '01-JUN-2017', null);

insert into t u t o r_report values (1, '01-JUN-2017', 8, 4);

insert into t u t o r_report values (4, '01-JUN-2017', 8, 6);

insert into t u t o r_report values (5, '01-JUN-2017', 4, 4);

insert into t u t o r_report values (4, '01-JUL-2017', 10, 5);

insert into t u t o r_report values (1, '01-JUL-2017', 4, 2);

Explanation / Answer

Note- I am using tutorz instead of the actual word as chegg not allowing to enter this word any where.

1) ALTER table command is used to add one more column to the table student. Data type is declared as number(3).

ALTER TABLE student
ADD COLUMN math_score number(3);

2) ALTER table command is used to add a column to the tutorz table. Also check constraint is used to allow only three values to this column.

ALTER TABLE tutorz
ADD COLUMN subject VARCHAR(10) CHECK (subject IN ('Reading', 'Math', 'ESL'));

3) Selecting all the student whose read score is more than 3.5. Comparison operator is used in where clause.

SELECT * FROM student
WHERE read > 3.5;

4) Selecting tutorz whose certificate date is greater than March 216 and also the status is active. Both the condition is given in where clause to filter the result.

SELECT * FROM tutorz
WHERE cert_date >'31-03-2016' AND status = 'Active';

5) Sum of the hours is calulated and multiplied by 10 to get the total fee of taught hours of tutorz. Group by is used to group the result by tutorz_id and month. Order by clause is used to sort the result.

SELECT t.tutorz_id, tr.month, (SUM(hours) * 10) AS 'Total_fee'
FROM tutorz_report tr
INNER JOIN match_history mh
ON mh.match_id = tr.match_id
INNER JOIN tutorz t
ON t.tutorz_id = mh.tutorz_id
GROUP BY t.tutorz_id, tr.month
ORDER BY t.tutorz_id, tr.month;

6) Aggregate function AVG, MIN and MAX are used to get the average read, min read and max read of the students.
SELECT AVG(read), MIN(read), MAX(read)
FROM student;

7) Subquery is used in where clause to compare the difference of end and start date with the MAX duration of teaching .
SELECT tutorz_id, student_id
FROM match_history
WHERE (end_date - start_date) = (SELECT MAX(end_date - start_date) FROM match_history);

8) Subquery is used in WHERE clause to find all the tutorz_id who have submitted the report of month March 2017.

SELECT * FROM tutorz
WHERE tutorz_id NOT IN (SELECT tutorz_id FROM tutorz_report WHERE month BETWEEN '01-03-2017' AND '31-03-2017')

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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