Write SQL statements for the following tasks using MySQL: 1. Create a database called lab2 2....

 

Transcribed Image Text:

Write SQL statements for the following tasks using MySQL: 1. Create a database called lab2 2. Create three tables as following in the database lab2 1. Students, three columns: first_name, last_name, student_id. The first two column should be text; the last one is an integer. Use student_id as the primary key. 2. Courses, two columns: course_title, course_id. They are both text. course_id should be the primary key. 3. Registration, three columns: student_id, a foreign key referencing student_id in the Students table; course_id, a foreign key referencing course_id in the Courses table; status, a text column that is meant to be 'registered', 'withdrawn', or 'pending permission', etc. You do not need to implement the constraint for the status column. The primary key should be the combination of the course_id and the student_id. 3. Add 5 students in the Students table. Choose any names as you like. 4. Add 2 courses in the Courses table. Choose any names as you like. 5. Register some students with both courses; some with each of the two course; and some with no course. 6. Find out the total number of students in each course. Be ready to do some other simple queries on this database. ER Diagram Student first name last name student id register status course course id course title Write SQL statements for the following tasks using MySQL: 1. Create a database called lab2 2. Create three tables as following in the database lab2 1. Students, three columns: first_name, last_name, student_id. The first two column should be text; the last one is an integer. Use student_id as the primary key. 2. Courses, two columns: course_title, course_id. They are both text. course_id should be the primary key. 3. Registration, three columns: student_id, a foreign key referencing student_id in the Students table; course_id, a foreign key referencing course_id in the Courses table; status, a text column that is meant to be 'registered', 'withdrawn', or 'pending permission', etc. You do not need to implement the constraint for the status column. The primary key should be the combination of the course_id and the student_id. 3. Add 5 students in the Students table. Choose any names as you like. 4. Add 2 courses in the Courses table. Choose any names as you like. 5. Register some students with both courses; some with each of the two course; and some with no course. 6. Find out the total number of students in each course. Be ready to do some other simple queries on this database. ER Diagram Student first name last name student id register status course course id course title

Answer rating: 100% (QA)

Certainly Here are the SQL statements for the tasks you mentioned 1 Create a database called lab2 sq... View the full answer