Objective: The purpose of this assignment is to demonstrate the proper writing f

Question

Objective:

The purpose of this assignment is to demonstrate the proper writing for SQL code, there is NO NEED TO USE ORACLE, ACCESS, OR ANY DBMS (JUST A CODE IN MS WORD)

Assignment Deliverables:

Write SQL code to create a STUDENT table that includes the following attributes:

Student ID

First Name

Last Name

Street Address

City

State

Zip Code

Phone

Email

In the code remember to include data type, data size, and if that field is required or not. The selected data types and data size should be logical. For example you cannot use Char (10) for street address; size 10 is very small compared to many long street addresses that we deal with every day. Having a short size data will create error messages.

Write SQL statement to :

Insert at least three rows of fake data (any names, any addresses,…) into the table above.

Add a column called StudentStatus to the table above.

Add a new constraint to limit the student zip code to be within the following group (32117, 32116, 32115, 32114)

Count the number of rows in the table

Show the first 3 records of data.

Delete the column StudentStatus.

Create a view that shows only First Name, Last Name, and Email

View records of students living in area code 32116.

Delete the table student

Note: Just write the code in a word document and there is no need for implementation in any DBMS at this point.

Explanation / Answer

Create table STUDENT

(

StudentID int not null,

FirstName varchar(30) not null,

LastName varchar(30) not null,

StreetAddress varchar(100) not null,

City varchar(30) not null,

State varchar(20) not null,

ZipCode int not null,

Phone varchar(20),

Email varchar(20),

Primary key (StudentID)

);

*

insert into Student values(2766,"Mathew","Buckner","Lane 2,DownStreet", "Hillside", "NJ", 124555, "75775-5757", "mathew@mail.com");

insert into Student values(2336,"Cathy","Jones","234", "Market Street", "CA", 156655, "72345-5757", "cathy@mail.com");

insert into Student values(2816,"Linda","Robinson","#675,DownStreet", "Sea side", "NY", 124555, "11222-5227", "linda@mail.com");

*

Alter table Student

Add StudentStatus varchar(20);

*

ALTER TABLE Student ADD CONSTRAINT CheckZipCodeConstraint CHECK(ZipCode IN(32117, 32116, 32115, 32114));

Select count(*) as rows from Student;

*

Select TOP 3 from Student;

*

Alter table Student

Drop column StudentStatus;

*

Create view view_name as Select FirstName,LastName,Email from Student;

*

Select * from Student where ZipCode = 32116;

*

Drop table Student;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.