Objective: The purpose of this assignment is to demonstrate the proper writing f
ID: 663332 • Letter: O
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:
1.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
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.
2.Write SQL statement to :
Insert at least three rows of fake data (any names, any addresses,
Explanation / Answer
1)
CREATE TABLE STUDENT
(
Student_ID int NOT NULL PRIMARY KEY,
First_Name varchar(100) NOT NULL,
Last_Name varchar(100) ,
Street_Address varchar(255) NOT NULL,
City varchar(50) NOT NULL,
State varchar(50) NOT NULL,
Zip_Code int NOT NULL,
Phone varchar(20)
)
2)
Insert into Sudent(Student_ID,First_Name,Last_Name,Street_Address,City,State,Zip_Code,Phone,Email
)
Select 1,John,Oliver,202 Street number,New York,USA,423200,9821231132,abc@xyz.com
Insert into Sudent(Student_ID,First_Name,Last_Name,Street_Address,City,State,Zip_Code,Phone,Email
)
Select 2,Wayne,Oliver,203 Street number,New York,USA,423200,9821231133,abcd@xyz.com
Insert into Sudent(Student_ID,First_Name,Last_Name,Street_Address,City,State,Zip_Code,Phone,Email
)
Select 2,Frank,Oliver,204 Street number,New York,USA,423200,9821231135,abcde@xyz.com
ALTER TABLE STUDENT
ADD STUDENTSTATUS int NOT NULL
ALTER TABLE STUDENT ADD CONSTRAINT ZIP_CODE_CHECK
CHECK (ZIP_CODE>=32114 and ZIP_CODE<=32117)
Select count(*) as numberOfRows from Student
Select top 3 * from Student
ALTER TABLE STUDENT
DROP COLUMN STUDENTSTATUS
CREATE VIEW STUDENTDETAILS AS
Select First_Name,Last_Name,Email
From Student
Select * from Student
where ZipCode = 32116
DROP TABLE STUDENT
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.