Use the code provided to create the tables. Turn in a hard copy of HW. The table

Question

Use the code provided to create the tables. Turn in a hard copy of HW. The table creation code must be at the top of the script, followed by the modifications and inserts. NOTE: you must insert the data in the correct order, which may not be the order given in the HW.

please run the codes on the sql manager to make sure it runs in order for me to give you the credit

i've included the code/file we need to do the homework.

SQL
SQL MANAGER
NOTEPAD ++

1 1 -Insert HW Start 2 3 DROP TABLE student; 4 DROP TABLE faculty; 5 6 CREATE TABLE student 7 (student_id INT PRIMARY KEY 8 sfirst VARCHAR (30), 9 s_mi CHAR (1), 10 s last VARCHAR (30), 11 s_class CHAR (2), 12 s_dob DATETIME 13 s_major VARCHAR (22), 14 s_gpa NUMERIC (3, 2)); 15 16 CREATE TABLE faculty 17 (f_id INT PRIMARY KEY 18 f_first VARCHAR (30), 19 f_last VARCHAR (30), 20 f_salary MONEY 21 year_hired INT, 22 phd BIT); 23 24

Explanation / Answer

Answer 2)

CREATE TABLE student

(

student_id INT PRIMARY KEY,

s_first VARCHAR (30),

s_mi CHAR(1),

s_last VARCHAR(30),

s_class CHAR(2),

s_dob DATETIME,

s_major VARCHAR(22),

s_gpa NUMERIC(3,2),

f_id INT,

FOREIGN KEY (f_id) REFERENCES faculty(f_id)

);

Added foreign key constraint in the table student for the relationship.

CREATE TABLE faculty(

f_id INT PRIMARY KEY,

f_first VARCHAR(30),

f_last VARCHAR(30),

f_salary MONEY,

year_hired INT,

phd BIT

);

Answer 3)

ALTER TABLE student

DROP COLUMN s_major;

Altering the table student to drop the column s_major.

Answer 4)

Insert data into faculty table:

insert into faculty values (14,'Ralph','A. Brooks',78000,1999,1);

insert into faculty values (22,'Jane','E. Seymour',null,2002,0);

insert into faculty values (29,'James','Bordy',56000,1996,1);

insert into faculty values (71,'Mary','Tessman',97000,2002,1);

insert into faculty values (44,'Bobby','Bell',69500,1999,0);

Insert data into student table:

insert into student values (111,'Maybelle','','Easton JR','','1984-07-01',null,29);

insert into student values (151,'Peter','','A Billings(SR)','','1986-11-03',3.3,44);

insert into student values (171,'John','','C. Briggs (FR)','','1987-05-18',null,71);

insert into student values (144,'Peggy','','Ming (JR)','',null,3.62,29);

insert into student values (166,'Abby','','M Lehmann JR','','1984-08-02',4.0,22);

select * from student;

Output:

student_id s_first s_mi s_last s_class s_dob s_gpa f_id

111 Maybelle Easton JR 1984-07-01T00:00:00Z (null) 29

144 Peggy Ming (JR) (null) 3.62 29

151 Peter A Billings(SR) 1986-11-03T00:00:00Z 3.3 44

166 Abby M Lehmann JR 1984-08-02T00:00:00Z 4 22

171 John C. Briggs (FR) 1987-05-18T00:00:00Z (null) 71

select * from faculty;

Output:

f_id f_first f_last f_salary year_hired phd

14 Ralph A. Brooks 78000 1999 true

22 Jane E. Seymour (null) 2002 false

29 James Bordy 56000 1996 true

44 Bobby Bell 69500 1999 false

71 Mary Tessman 97000 2002 true

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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