$16.$ DO the following: Build the $$Persons$16$ table with the same data fields and data

records where the $"$P$\_$Id$"$ column in the "Persons$16"$ table is the PRIMARY KEY in the

"Persons$16"$ table.

The "Persons$16"$ table:

P$\_$Id LastName FirstName Address City

$1$ Hansen Harry $12$ Smith St$.$ Edison

$2$ Svendson Tammy $34$ Apple Way Colonia

$3$ Pettersen Kimmie $56$ Patio Place Iselin

$17.$ To understand SQL FOREIGN KEY Constraint on CREATE TABLE, DO the following

SQL creates a FOREIGN KEY on the $"$P$\_$Id$"$ column when the "Orders$17"$ table is created:

CREATE TABLE Orders$17$

$($

O$\_$Id int NOT NULL PRIMARY KEY,

OrderNo int NOT NULL,

P$\_$Id int,

FOREIGN KEY $($P$\_$Id$)$ REFERENCES Persons$16($P$\_$Id$)$

$)$

$18.$ To understand how to DROP a PRIMARY KEY constraint, DO the following SQL:

ALTER TABLE Persons$16$

DROP CONSTRAINT pk$\_$PersonID

$19.$ To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY

constraint on multiple columns, DO the following SQL syntax:

CREATE TABLE Orders$19$

$($

O$\_$Id int NOT NULL,

OrderNo int NOT NULL,

P$\_$Id int,

PRIMARY KEY $($O$\_$Id$),$

CONSTRAINT fk$\_$PerOrders FOREIGN KEY $($P$\_$Id$)$

REFERENCES Persons$16($P$\_$Id$)$

$)$

$20.$ To understand how to DROP a FOREIGN KEY constraint, DO the following SQL:

ALTER TABLE Orders$19$

DROP CONSTRAINT fk$\_$PerOrders

$21.$ DO the following SQL to create a CHECK constraint on the $"$P$\_$Id$"$ column when the

"Persons" table is created. The CHECK constraint specifies that the column $"$P$\_$Id$"$ must

only include integers greater than $0.$

CREATE TABLE Persons$21$

$($

P$\_$Id int NOT NULL CHECK $($P$\_$Id$>0),$

LastName varchar$(255)$ NOT NULL,

FirstName varchar$(255),$

Address varchar$(255),$

City varchar$(255)$

$)$

$22.$ DO the following to allow naming of a CHECK constraint, and for defining a CHECK

constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons$22$

$($

P$\_$Id int NOT NULL,

LastName varchar$(255)$ NOT NULL,

FirstName varchar$(255),$

Address varchar$(255),$

City varchar$(255),$

CONSTRAINT chk$\_$Person CHECK $($P$\_$Id$>0$ AND City$=$'Sandnes'$)$

$)$

$23.$ To understand the SQL CHECK Constraint on ALTER TABLE, DO the following

to create a CHECK constraint on the $"$P$\_$Id$"$ column when the table is already created,

use the following SQL:

ALTER TABLE Persons$22$

ADD CHECK $($P$\_$Id$>0)$

$24.$ To understand how to DROP a CHECK constraint, DO the following SQL:

ALTER TABLE Persons$22$

DROP CONSTRAINT chk$\_$Person

$25.$ To use the SQL DEFAULT Constraint on CREATE TABLE, DO the following SQL

creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons$25$

$($

P$\_$Id int NOT NULL,

LastName varchar$(255)$ NOT NULL,

FirstName varchar$(255),$

Address varchar$(255),$

City varchar$(255)$ DEFAULT 'Sandnes'

$)$

$26.$ To understand how to use the DEFAULT constraint can also be used to insert system

values, by using functions like SYSDATE, DO the following:

CREATE TABLE Orders$26$

$($

O$\_$Id int NOT NULL,

OrderNo int NOT NULL,

P$\_$Id int,

OrderDate date DEFAULT SYSDATE

$)$

$27.$ To understand how to use the SQL DEFAULT constraint on ALTER TABLE, DO the following to create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE Persons$25$

MODIFY City DEFAULT 'SANDNES'

$28.$ To demonstrate the SQL ALTER TABLE, DO the following:

Build a "Persons$28"$ table with the following columns and insert the data into the table:

P$\_$Id LastName FirstName Address City

$1$ Hansen Harry $12$ Smith St$.$ Edison

$2$ Svendson Tammy $34$ Apple Way Colonia

$3$ Pettersen Kimmie $56$ Patio Place Iselin

$28.$ DO: Now add a column named "DateOfBirth" in the "Persons$28"$ table by doing the following SQL statement:

ALTER TABLE Persons$28$

ADD DateOfBirth date

Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold.

$29.$ DO: Use a Select command to demonstrate that the "Persons$28"$ table will now look like this:

P$\_$Id LastName FirstName Address City DateOfBirth

$1$ Hansen Harry $12$ Smith St$.$ Edison

$2$ Svendson Tammy $34$ Apple Way Colonia

$3$ Pettersen Kimmie $56$ Patio Place Iselin

$30.$ The "DateOfBirth" column is now of type year and is going to hold a year in a

two$-$digit or four$-$digit format. DO: Use a DESC statement on the $$Persons$28$ table to see this change.

$31.$ To delete the column named "DateOfBirth" in the "Persons$28"$ table, DO the following

SQL statement:

ALTER TABLE Persons$28$

DROP COLUMN DateOfBirth

$32.$ DO: Use a Select command to demonstrate that the "Persons$28"$ table will now look like this:

P$\_$Id LastName FirstName Address City

$1$ Hansen Harry $12$ Smith St$.$ Edison

$2$ Svendson Tammy $34$ Apple Way Colonia

$3$ Pettersen Kimmie $56$ Patio Place Iselin