Use the ER diagram below and perform following SQL statements: 1. Write SQL statements to create tables:
Question:
Use the ER diagram below and perform following SQL statements:
1. Write SQL statements to create tables: EMPLOYEE, DEPARTMENT, JOB. Add primary and foreign keys (dept_no & mgr_code in table creation scripts for EMPLOYEE and DEPARTMENT. Prefix table name with your initial. For ex. My initial is MAA. EMPLOYEE table => MAA_EMPLOYEE
2. Write a SQL statement to create job table without creating primary key.
3. Write a SQL statement to create primary key for JOB table (i.e ALTER TABLE statement)
4. Write a SQL statement to create foreign key on EMPLOYEE table (job_code) referencing JOB table (job_code)
5. Write a SQL statement to create a check constraint (salary_ck) on salary column of EMPLOYEE table so that the valid salary is >0 and
6. Write a SQL statement to add a column ‘address varchar (100) to EMPLOYEE table
7. Write a SQL statement to create an index ‘emp_name_idx1’ on (lname, fname) to EMPLOYEE table.
8. Write SQL statements to write INSERT statements to insert 3 rows to each table.
9. Write SQL statements to display all columns and all rows from each of the tables.
10. Write SQL statements to write an UPDATE statements to modify salary to increase salary by 5000 for all employees11. Write SQL statements to write a DELETE statements to delete highest paid employee12. Implement these SQL statements in Mysql database.