Given the following schemas for a student record: STUDENTS (student_id, student_name, region) COURSES (course_id, course_name, subject_type, lecturer_name) RESULTS (student_id, course_id, score, result_status) STUDENTS relation shows the information of the students. The COURSE relation specifies the course details. The RESULTS relation lists the scores that students obtain for each course taken. *Please execute this SQL codes for creating the table schemas mentioned above, Then execute the tasks in the table accordingly using proper SQL queries and Write your answer into the Answer Template CREATE TABLE STUDENTS (student_id VARCHAR(10) PRIMARY KEY, student_name VARCHAR(150), region VARCHAR(50)); CREATE TABLE COURSES (course_id VARCHAR(10) PRIMARY KEY, course_name VARCHAR(150), subject_type VARCHAR(50), lecturer_name VARCHAR(150)); CREATE TABLE RESULTS (student_id VARCHAR(10), course_id VARCHAR(10), score NUMBER, result_status VARCHAR(20), CONSTRAINT pk_results PRIMARY KEY (student_id, course_id), CONSTRAINT fk_results_student FOREIGN KEY (student_id) references STUDENTS(student_id), CONSTRAINT fk_results_courses FOREIGN KEY (course_id) references COURSES(course_id)); Tasks: ++Insert the data into the three relations STUDENTS, COURSES, and RESULTSaccording to the data given below: Table 1: STUDENTS relation student_id student_name region 67540 Anas Forrest Florida 87543 Edie Johnston Washington 98560 Haley Hibbert Arizona 54870 Lorenzo Whitney Washington 76590 Kate Wood Washington Table 2: COURSES relation course_id course_name subject_type lecturer_name 786 Science Math Maggie 764 Education Drawing Maggie 579 Engineering Digital Mary 787 Engineering Math Jude Hoffman Table 3: RESULTS relation student_id course_id score result_status 76590 579 80 Good 54870 786 76 Good 87543 787 69 Good 76590 787 60 Average 67540 764 90 Good 67540 786 79 Good 87543 764 50 Failed Question SQL Statement ++Insert the data into the three relations STUDENTS, COURSES, and RESULTS according to the data given List the course_id and the score In RESUTLS relation, list only the result_status, student id, and the score for those who get “good” and “average” results. Show the student id who score more than 75 marks. A new joint staff Gary will take over the Drawing subject in Education course (course ID: 764). Update the information of the course accordingly List the lecturer names that teach Engineering course Show the list of students who originate from Washignton List the course_id and score from the RESULTS List the students who get the score between 50 – 70 List the lecturer names that consists of the letter ‘r’ in the third letter List the information (student_id and course_id) for the students who take the course of Science Math (course_id: 786) and Engineering Math (course_id: 787)
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Course:
Given the following schemas for a student record:
STUDENTS (student_id, student_name, region)
COURSES (course_id, course_name, subject_type, lecturer_name)
RESULTS (student_id, course_id, score, result_status)
STUDENTS relation shows the information of the students. The COURSE relation specifies the course details. The RESULTS relation lists the scores that students obtain for each course taken.
*Please execute this SQL codes for creating the table schemas mentioned above, Then execute the tasks in the table accordingly using proper SQL queries and Write your answer into the Answer Template
CREATE TABLE STUDENTS (student_id VARCHAR(10) PRIMARY KEY, student_name VARCHAR(150), region VARCHAR(50));
CREATE TABLE COURSES (course_id VARCHAR(10) PRIMARY KEY, course_name VARCHAR(150), subject_type VARCHAR(50), lecturer_name VARCHAR(150));
CREATE TABLE RESULTS (student_id VARCHAR(10), course_id VARCHAR(10), score NUMBER, result_status VARCHAR(20), CONSTRAINT pk_results PRIMARY KEY (student_id, course_id), CONSTRAINT fk_results_student FOREIGN KEY (student_id) references STUDENTS(student_id), CONSTRAINT fk_results_courses FOREIGN KEY (course_id) references COURSES(course_id));
Tasks:
++Insert the data into the three relations STUDENTS, COURSES, and RESULTSaccording to the data given below:
Table 1: STUDENTS relation
| student_id | student_name | region |
| 67540 | Anas Forrest | Florida |
| 87543 | Edie Johnston | Washington |
| 98560 | Haley Hibbert | Arizona |
| 54870 | Lorenzo Whitney | Washington |
| 76590 | Kate Wood | Washington |
Table 2: COURSES relation
| course_id | course_name | subject_type | lecturer_name |
| 786 | Science | Math | Maggie |
| 764 | Education | Drawing | Maggie |
| 579 | Engineering | Digital | Mary |
| 787 | Engineering | Math | Jude Hoffman |
Table 3: RESULTS relation
| student_id | course_id | score | result_status |
| 76590 | 579 | 80 | Good |
| 54870 | 786 | 76 | Good |
| 87543 | 787 | 69 | Good |
| 76590 | 787 | 60 | Average |
| 67540 | 764 | 90 | Good |
| 67540 | 786 | 79 | Good |
| 87543 | 764 | 50 | Failed |
|
Question |
SQL Statement |
|
++Insert the data into the three relations STUDENTS, COURSES, and RESULTS |
|
|
List the course_id and the score |
|
| In RESUTLS relation, list only the result_status, student id, and the score for those who get “good” and “average” results. |
|
|
Show the student id who score more than 75 marks. |
|
| A new joint staff Gary will take over the Drawing subject in Education course (course ID: 764). Update the information of the course accordingly |
|
| List the lecturer names that teach Engineering course |
|
| Show the list of students who originate from Washignton |
|
| List the course_id and score from the RESULTS |
|
| List the students who get the score between 50 – 70 |
|
| List the lecturer names that consists of the letter ‘r’ in the third letter |
|
|
List the information (student_id and course_id) for the students who take the course of Science Math (course_id: 786) and Engineering Math (course_id: 787) |
|
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
