1.    Delete the row in the STUDENT table and the two rows in the ZIPCODE table that you created. Be sure to issue a COMMIT command afterwards.

 

2.    Create a table called TEMP_STUDENT with the following columns and constraints: a column STUDID for student ID that is NOT NULL and is the primary key, a column FIRST_NAME for student first name; a column LAST_NAME for student last name, a column ZIP that is a foreign key to the ZIP column in the ZIPCODE table, a column REGISTRATION_DATE that is NOT NULL and has a CHECK constraint to restrict the registration date to dates after January 1, 2000. 

 

3.    Write an INSERT statement violating at least two of the constraints for the TEMP_STUDENT table that you just created.

 

4.    Write another INSERT statement that succeeds when executed, and commit your work.

 

5.    Alter the TEMP_STUDENT table to add two more columns called EMPLOYER and EMPLOYER_ZIP. The EMPLOYER_ZIP column should have a foreign key constraint referencing the ZIP column of the ZIPCODE table. Update the EMPLOYER column.

 

6.    Alter the table once again to make the EMPLOYER column NOT NULL.

- ******************************************************************-- File:CreateStudent.sql-- Description: Used for creating the objects and loading data--into the STUDENT schema for MySQL-- ******************************************************************--student Schema-- Creating TablesDROP TABLE IF EXISTSINSTRUCTOR;CREATE TABLE INSTRUCTOR(INSTRUCTOR_ID INT(8) NOT NULL,SALUTATION VARCHAR(5),FIRST_NAME VARCHAR(25),LAST_NAME VARCHAR(25),STREET_ADDRESS VARCHAR(50),ZIP VARCHAR(5),PHONE VARCHAR(15),CREATED_BY VARCHAR(30) NOT NULL,CREATED_DATE DATE NOT NULL,MODIFIED_BY VARCHAR(30) NOT NULL,MODIFIED_DATE DATE NOT NULL);-- COMMENT ON TABLE INSTRUCTOR IS 'Profile information for an instructor.'-- COMMENT ON COLUMN INSTRUCTOR.INSTRUCTOR_ID IS 'The unique ID for aninstructor.'-- COMMENT ON COLUMN INSTRUCTOR.SALUTATION IS 'This instructor''s title (Mr.,Ms., Dr., Rev., etc.)'-- COMMENT ON COLUMN INSTRUCTOR.FIRST_NAME IS 'This instructor''s first name.'-- COMMENT ON COLUMN INSTRUCTOR.LAST_NAME IS 'This instructor''s last name'-- COMMENT ON COLUMN INSTRUCTOR.STREET_ADDRESS IS 'This Instructor''s streetaddress.'-- COMMENT ON COLUMN INSTRUCTOR.ZIP IS 'The postal zip code for thisinstructor.'-- COMMENT ON COLUMN INSTRUCTOR.PHONE IS 'The phone number for this instructorincluding area code.'-- COMMENT ON COLUMN INSTRUCTOR.CREATED_BY IS 'Audit column - indicates user whoinserted data.'-- COMMENT ON COLUMN INSTRUCTOR.CREATED_DATE IS 'Audit column - indicates dateof insert.'-- COMMENT ON COLUMN INSTRUCTOR.MODIFIED_BY IS 'Audit column - indicates whomade last update.'-- COMMENT ON COLUMN INSTRUCTOR.MODIFIED_DATE IS 'Audit column - date of lastupdate.'-- Creating Table 'GRADE'DROP TABLE IF EXISTSGRADE;

CREATE TABLE GRADE(STUDENT_ID INT(8) NOT NULL,SECTION_ID INT(8) NOT NULL,GRADE_TYPE_CODE CHAR(2) NOT NULL,GRADE_CODE_OCCURRENCE INT(38) NOT NULL,NUMERIC_GRADE INT(3) DEFAULT 0,COMMENTS VARCHAR(2000),CREATED_BY VARCHAR(30) NOT NULL,CREATED_DATE DATE NOT NULL,MODIFIED_BY VARCHAR(30) NOT NULL,MODIFIED_DATE DATENOT NULL);-- COMMENT ON TABLE GRADE IS 'The individual GRADEs a student received for aparticular section(class).'-- COMMENT ON COLUMN GRADE.STUDENT_ID IS ' The unique ID for the student.'-- COMMENT ON COLUMN GRADE.SECTION_ID IS 'The unique ID for a section.'-- COMMENT ON COLUMN GRADE.GRADE_TYPE_CODE IS 'The code which identifies acategory of GRADE.'-- COMMENT ON COLUMN GRADE.GRADE_CODE_OCCURRENCE IS 'The sequence number of oneGRADE type for one section. For example, there could be multiple assignmentsnumbered 1, 2, 3, etc.'-- COMMENT ON COLUMN GRADE.NUMERIC_GRADE IS 'Numeric GRADE value, (e.g. 70,75.)'-- COMMENT ON COLUMN GRADE.COMMENTS IS 'Instructor''s comments on this GRADE.'-- COMMENT ON COLUMN GRADE.CREATED_BY IS 'Audit column - indicates user whoinserted data.'-- COMMENT ON COLUMN GRADE.CREATED_DATE IS 'Audit column - indicates date ofinsert.'-- COMMENT ON COLUMN GRADE.MODIFIED_BY IS 'Audit column - indicates who madelast update.'-- COMMENT ON COLUMN GRADE.MODIFIED_DATE IS 'Audit column - date of lastupdate.'-- Creating Table 'GRADE_TYPE'DROP TABLE IF EXISTSGRADE_TYPE;CREATE TABLE GRADE_TYPE(GRADE_TYPE_CODE CHAR(2)NOT NULL,DESCRIPTION VARCHAR(50) NOT NULL,CREATED_BY VARCHAR(30) NOT NULL,CREATED_DATE DATE NOT NULL,MODIFIED_BY VARCHAR(30) NOT NULL,MODIFIED_DATE DATE NOT NULL);-- COMMENT ON TABLE GRADE_TYPE IS 'Lookup table of a GRADE types (code) and itsdescription.'-- COMMENT ON COLUMN GRADE_TYPE.GRADE_TYPE_CODE IS 'The unique code which

SophiaPretty

(5)

Status NEW Posted 30 Nov 2017 11:11 AM My Price 10.00

-----------  ----------- H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly

Not Rated(0)

• Buy Answer