Consider the following relations that represent a simple University DBMS:

Student(sid: integer, name: string, birthdate: date, sex: string, deptid: integer, startdate: date)

Course(cid: integer, name: string, prerequsiteid: integer) Department(deptid: integer, name: string)

StudentCourse(sid: integer, cid: integer, currentstatus: string, score: real)

DepartmentCourse(deptid: integer, cid: integer)

Note: Unlined attributes are the primary keys of each relations. The foreign keys are obvious, but if you need, please draw them by yourself.

Relational database schema shown above is already created and defined in a relational database management system, such as MySQL,

with all necessary constraints.

1. Write an SQL query which lists the number of students by department (department id) and sex. Assume that sex has two values as Female and Male.

2. Write an SQL query which lists each course (course name) with its department (department name), ordered by course name in descending order.

3. It is necessary to add course credits to the course table, such that default value of course credit is 4 and it cannot be null. Write an SQL query which adds course credit field to the existing course table.

4. The name of the Computer Engineering Department needs to be changed as Computer Science and Engineering. Write an SQL query which makes this change.

5. Create a view that lists current courses (course names) and the students (student names) in those courses.

Assume that for current courses, the currentstatus field has a value of current. (1 point) It is realized that users frequently run queries based on the course name.

To speed up such queries write an SQL query that builds an index on the course name field of the course table.