Due today
Submit a text file (extension .txt).
1- Write a statement that creates a table named Part, with an Id field as an int idendity PK, a SupplierId int field, a Description string field of size 25, a Count int field, a Class string field of size 5, and an inspection small date field. All fields cannot have a value of null, and the SupplierId field should be an FK that references the PK of a Supplier parent table.
2- Write a satement to add a check constraint to the above defined table, which guarantees that values entered in the inspection small date field are not older than today, meaning from now on. According to new requirements from your business analyst, an inspection can only be scheduled in the future.
3- Write a statement to add a check constraint to the above defined table, which guarantees that values entered in the Count field are never less than zero.
4- Write a statement to add a check constraint to the above defined table, which guarantees that values entered in the Class field can only be limited to the following strings: Open, Spec, and Priv.
5- Write a statement to create a view that selects all fields from the above table, except for both Id fields, in addition to the supplier name field of the Supplier table referenced in question 1. Note that the user is only interested in Parts that are not of class Priv. In other words, parts of class Priv should not be returned. Hint: A JOIN must be used to only return common rows.
What kind of HELP do you need? You need to be specific when asking questions here.
If all you do is post your entire assignment, nothing will happen since no one here will do your work for you. But if you are specific about what you don't understand about the assignment or exactly what help you need, someone might be able to assist you.
1- Try:
SELECT Id, SupplierID, Description, Count, Class, inspecton
INTO Part FROM Supplier;
You will need to describe the table structure ahead of time.
For the rest, give it a try and post what you have, and what the problem is, if any.
To complete the given tasks, you can use SQL queries to create a table, add check constraints, and create a view. Here is the step-by-step explanation of how to achieve each task:
Task 1:
To create a table named Part with the specified fields and constraints, you can use the following SQL statement:
```
CREATE TABLE Part (
Id int IDENTITY(1,1) PRIMARY KEY,
SupplierId int NOT NULL,
Description varchar(25) NOT NULL,
Count int NOT NULL,
Class varchar(5) NOT NULL,
Inspection smallDateTime NOT NULL,
FOREIGN KEY (SupplierId) REFERENCES Supplier(PK)
);
```
Note: Replace `Supplier` with the actual name of the Supplier parent table.
Task 2:
To add a check constraint to ensure values in the Inspection field are not older than today's date, you can use the following SQL statement:
```
ALTER TABLE Part
ADD CONSTRAINT CHK_InspectionDate
CHECK (Inspection >= CONVERT(smalldatetime, GETDATE()));
```
Task 3:
To add a check constraint to ensure values in the Count field are never less than zero, you can use the following SQL statement:
```
ALTER TABLE Part
ADD CONSTRAINT CHK_Count
CHECK (Count >= 0);
```
Task 4:
To add a check constraint to limit values in the Class field to "Open," "Spec," and "Priv," you can use the following SQL statement:
```
ALTER TABLE Part
ADD CONSTRAINT CHK_Class
CHECK (Class IN ('Open', 'Spec', 'Priv'));
```
Task 5:
To create a view that selects fields from the Part table and includes the supplier name from the Supplier table while excluding Priv parts, you can use the following SQL statement:
```
CREATE VIEW PartView
AS
SELECT p.SupplierId, p.Description, p.Count, p.Class, p.Inspection, s.SupplierName
FROM Part p
JOIN Supplier s ON p.SupplierId = s.PK
WHERE p.Class <> 'Priv';
```
Note: Replace `SupplierName` with the actual name of the supplier name column in the Supplier table.
After completing these steps, you can save the SQL statements in a text file with the extension .txt and submit it as required.