Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

use exam1; /* Query 1. Write one DDL statement to perform the following two modifications. (1) Change the data type of GPA in Student table

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:

image text in transcribed

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)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Visual C# And Databases

Authors: Philip Conrod, Lou Tylee

16th Edition

1951077083, 978-1951077082

More Books

Students also viewed these Databases questions

Question

Of what significance is inventory turnover to a retail store?

Answered: 1 week ago