SQL

download the script database_university sql file from here:

https://ufile.io/w7eu2

Section 1: Database Update Create a new database named as "YourUserName_University", and run the script database_university.sql posted on Canvas. Use "YourUserName_university" for the following exercises. Write the following queries in sQL statements. Include SQL statements in your submission. 1. (1 point) Increase the salary of each instructor in the Comp. Sci. department by 10% 2. (1 point) Delete all courses that have never been offered (i.e., do not occur in the section relation). (1 point) Insert every student whose tot_cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000. 3. (1 point) Create a new course ("CS-001", "Computer Basics", "Comp. Sci.", 2), then create a new section of this course ("CS-001", 1, "Winter", 2018). Enroll every student in Comp. Sci. department into this course section. 4. (1 point) Delete enrollments in CS-001 course, Section 1 of Winter 2018 where the student's name is Zhang. 5. 6. (1 point) Delete all takes tuples corresponding to any section of any course with the worod "database" as a part of the title; ignore case when matching the word with the title. 7. (2 points) Update Tot_Cred in student relation. When you look at the student relation you will find that the tot_cred field provides incorrect information. Now you are going to update this field with the real total credits the students received. Note that if a student got an For the grade is null, he/she got 0 credits for that course. Show the student table after the update Section 2: DDL with Constraints (1 point for each question) Create a new database named as "YourUsername_Constraints", and use it for the following exercises. Write the following queries in SQL statements. Include SQL statements in your submission. 8. Modify the following SQL command so that the Rep ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column. (The Comm column indicates whether the sales representative earns commission.) CREATE TABLE STORE_REPS (Rep ID INT(5), Last VARCHAR(15), First VARCHAR(10), Comm CHAR(1)); 9. Change the STORE_REPS table so that NULL values CANNOT be entered in the name columns (First and Last) 10. Create a table named BOOK_STORES to include the columns listed in the following chart: Column Name Datatype Store ID Name Contact Rep ID Constraint Comments INT(8 VARCHAR(30) Should be UNIQUE and NOT NULL VARCHAR(20) INT(5 PRIMARY KEY column 11. Add a constraint to make sure the Rep ID value entered in the BOOK_STORES table is a valid value contained In the STORE_REPS table, and the associated rows of the BOOK_STORES table are deleted automatically if a row in the STORE_REPS table is deleted 12. Create a table named REP_CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep_ID and Store_ID columns Column Name DataType Store ID Name Quarter Rep ID INT(8) INT(5 CHAR(3) INT(5 Section 1: Database Update Create a new database named as "YourUserName_University", and run the script database_university.sql posted on Canvas. Use "YourUserName_university" for the following exercises. Write the following queries in sQL statements. Include SQL statements in your submission. 1. (1 point) Increase the salary of each instructor in the Comp. Sci. department by 10% 2. (1 point) Delete all courses that have never been offered (i.e., do not occur in the section relation). (1 point) Insert every student whose tot_cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000. 3. (1 point) Create a new course ("CS-001", "Computer Basics", "Comp. Sci.", 2), then create a new section of this course ("CS-001", 1, "Winter", 2018). Enroll every student in Comp. Sci. department into this course section. 4. (1 point) Delete enrollments in CS-001 course, Section 1 of Winter 2018 where the student's name is Zhang. 5. 6. (1 point) Delete all takes tuples corresponding to any section of any course with the worod "database" as a part of the title; ignore case when matching the word with the title. 7. (2 points) Update Tot_Cred in student relation. When you look at the student relation you will find that the tot_cred field provides incorrect information. Now you are going to update this field with the real total credits the students received. Note that if a student got an For the grade is null, he/she got 0 credits for that course. Show the student table after the update Section 2: DDL with Constraints (1 point for each question) Create a new database named as "YourUsername_Constraints", and use it for the following exercises. Write the following queries in SQL statements. Include SQL statements in your submission. 8. Modify the following SQL command so that the Rep ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column. (The Comm column indicates whether the sales representative earns commission.) CREATE TABLE STORE_REPS (Rep ID INT(5), Last VARCHAR(15), First VARCHAR(10), Comm CHAR(1)); 9. Change the STORE_REPS table so that NULL values CANNOT be entered in the name columns (First and Last) 10. Create a table named BOOK_STORES to include the columns listed in the following chart: Column Name Datatype Store ID Name Contact Rep ID Constraint Comments INT(8 VARCHAR(30) Should be UNIQUE and NOT NULL VARCHAR(20) INT(5 PRIMARY KEY column 11. Add a constraint to make sure the Rep ID value entered in the BOOK_STORES table is a valid value contained In the STORE_REPS table, and the associated rows of the BOOK_STORES table are deleted automatically if a row in the STORE_REPS table is deleted 12. Create a table named REP_CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep_ID and Store_ID columns Column Name DataType Store ID Name Quarter Rep ID INT(8) INT(5 CHAR(3) INT(5