SQL Assignment

Table: student$\_$info

Table: course$\_$infoQuestions:

Create the student$\_$course$\_$enrollment table using

SQL$.$

$(1\mathrm{.}0$ Marks$)$

Insert the student$\_$course$\_$enrollment data from the above

picture.

$(1\mathrm{.}0$ Marks$)$

Retrieve the names and ages of students majoring in "Biology"

$(1\mathrm{.}0$ Marks $)$

Calculate the average age of students majoring in

"Physics"

$(1\mathrm{.}0$ Marks $)$

Retrieve the unique names of the courses from the course$\_$info

table

$(1\mathrm{.}0$ Marks $)$

Instructions:

Go to

https:$//$sqliteonline$.$com$/$ and connect MS SQL to start writing

queries.

File Format:

You must have to include the SQL Query and Output of that query

Like this format.

$>$ SQL$-$ASSIGNMENT$-$QUERY.SQL File with Question

Number, Query and Query Output

Screenshot of the Query and Output in Word Document

Table: student$\_$course$\_$enrollment

The creation of the student$\_$info and course$\_$info tables, along with the

data insertion queries, can be found in the attached SQL file named

student$-$and$-$course$-$table$-$create$-$and$-$data$-$insert.sql or in the code

snapshot.

$--$ Create the student$\_$info table

CREATE TABLE student$\_$info $($

StudentID INT PRIMARY KEY,

Name VARCHAR$(50),$

Age INT,

Major VARCHAR$(50)$

;

$--$ Insert data into the student$\_$info table

INSERT INTO student$\_$info $($StudentID$,$ Name, Age, Major$)$

VALUES

$(1,$ 'Alice', $20,$ 'Biology'$),$

$(2,$ 'Bob', $21,$ 'History'$),$

$(3,$ 'Carol', $19,$ 'Math'$),$

$(4,$ 'David', $23,$ 'Physics'$),$

$(5,$ 'Emily', $20,$ 'Biology'$),$

$(6,$ 'Frank', $21,$ 'Physics'$)$;

$--$ Create the course$\_$info table

CREATE TABLE course$\_$info

CourseID INT PRIMARY KEY,

CourseName VARCHAR$(100),$

Instructor VARCHAR$(100)$

;

$--$ Insert data into the course$\_$info table

INSERT INTO course$\_$info $($CourseID$,$ CourseName, Instructor$)$

VALUES

$(101,$ 'Biology $101\text{'},\text{'}$Dr$.$ Smith'$),$

$(102,$ 'History $101\text{'},$ 'Prof. White'$),$

$(103,$ 'Math $101\text{'},$ 'Prof. Green'$),$

$(104,$ 'Physics $101\text{'},\text{'}$Dr$.$ Brown'$)$;