Question

SQL Homework Create a single script to do all of the following. Be sure to create in such a way that it can be re-run multiple times. Create a database named homework. In this database, create three tables: Student, Course, StudentCourse - Create the Student table with the following fields in this order: studentID as an integer; do not allow empty values firstName as a variable number of characters, max 30, with a null default value lastName as a variable number of characters, max 30, with a null default value address as a variable number of characters, max 30, with a null default value Use the studentID as the primary key for this table - populate this table with the following values, respectively: 1001, John, Brown, 123 Anystreet, OH 1002, Paul, Black, 23 Someplace Ct., IL 1003, George, Green, 1020 Langley Lane, TN 1004, Betty, Brown, 36250 Aims Ln, NY - Create the Course table with the following fields in this order: courseID as an integer; do not allow empty values courseName as a variable number of characters, max 30; do not allow empty values Use the courseID as the primary key for this table - populate this table with the following values, respectively: 1, Basket Weaving 2, The Writings of C. S. Lewis 3, Database Design 4, Computer Programming - Create the StudentCourse table with the following fields in this order: studentID as an integer; do not allow empty values courseID as an integer; do not allow empty values Use both fields as the primary key for this table Indicate each field as a foreign key, courseID as referencing the matching primary key in the Course table, and studentID as referencing the matching primary key in the Student table - populate this table with the following values, matched to courseID and studentID, respectively: 2, 1001 2, 1003 3, 1001 4, 1001 4, 1002 Execute the following queries: SQL Homework 1) Display all records for each of the three tables, Course, StSQL Homework 1) Display all records for each of the three tables, Course, Student, and StudentCourse. 2) Display all courses with enrollment and the enrolled students in each, ordered by course ID Display course name as "Course Name", student first name as "First Name", and last name as "Last Name" 3) Display all courses with enrollment and the enrolled students in each, ordered by course name Display course name as "Course Name", student first name as "First Name", and last name as "Last Name" 4) Display all enrolled students and the courses in which each is enrolled, ordered by student ID Display course name as "Course Name", student first name as "First Name", and last name as "Last Name" 5) Display all enrolled students and the courses in which each is enrolled, ordered by student last and first names Display course name as "Course Name", student first name as "First Name", and last name as "Last Name" Course P K courseID courseName Student P K studentID firstName address StudentEnroll

Answer 1

The above set of queries exactly satisfy your requirements.