use exam1; /* Query 1. Write one DDL statement to perform the following two modi
ID: 3734949 • Letter: U
Question
use exam1;
/* Query 1.
Write one DDL statement to perform the following two modifications.
(1) Change the data type of GPA in Student table to allow a GPA value of
three
decimal digits to be stored.
(2) Allow no NULL values in GPA column.
*/
/* Query 2.
Write one DDL statement to add a new column AssignDate of date type in
ProjAssignment
table, which should allow no null values. Because there are already
existing rows in
the table which have no AssignDate data, your DDL statement should define
a default
value of current date in this column. When this statement is executed, it
will therefore
update the AssignDate of every existing row with a current date.
*/
/* Query 3.
Write one DDL statement to add a new table Project into exam1. It contains
the following
three columns:
-ProjNum of char(3) type, which is the primary key of Project.
-Pname of varchar(30) type, which could be null. If it has a value,
its first three
characters must be 'UH2' or 'UB5'.
-Budget of smallmoney type, which cannot be null and must have a
minimum of $1,500.
*/
/* Query 4.
Write one DML statement to add data of the following six projects into
Project table.
ProjNum Pname Budget
------- ----- --------
p1 UH28K 1890.00
p2 UB555 1500.00
p3 UB5AA 2009.00
p4 UH200 11000.00
p5 UH2XG 9000.00
p6 UB5J6 4400.00
*/
/* Query 5.
When Query 4 is successfully executed, write one DDL statement to add a
foreign
key constraint called 'FK_ProjAss_Proj' in ProjAssignment table. It should
define
Pno as a foreign key which references the ProjNum of Project.
After executing this statement, updating a ProjNum value in Project should
automatically update the Pno to the same in ProjAssignment. However, with
the
constraint enforced, a project cannot be deleted if its ProjNum value is
referenced
by Pno of one or more rows of ProjAssignment.
Hint: p.350~1
*/
here is the database info:
E EEE dbo.ProjAssignment Columns o Pno (PK, char(3), not null) StulD (PK, FK, char(3), not null) E EEE dbo.Student Columns StulD (PK, char(3), not null) GPA (decimal(3,2), nul)Explanation / Answer
Solution:
Query 1 and 2 is done as per Chegg guidelines, please repost others.
1)
ALTER TABLE Student
ALTER COLUMN GPA int(3);
Explanation:
Alter keyword is used here to change the column datatype.
2)
ALTER TABLE Student
ALTER COLUMN GPA int(3) NOT NULL;
Explanation:
NOT NULL is used here to fulfill the requirement.
Query 2)
ALTER TABLE ProjAssignment
ADD COLUMN AssignDate date;
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.