Please turn in your SQL for these statements. [You can practice these in Oracle
ID: 3909040 • Letter: P
Question
Please turn in your SQL for these statements. [You can practice these in Oracle through SQL Developer or the command line. You can also use these in the SQL view in Access under the queries menu. –However, in Access some of the constraint commands do not work.]
Also note that Access provides a GUI interface so in practice, when using Access, you don’t type in the SQL commands. But, for this assignment, you will need to turn in the appropriate SQL commands.
Completing and referencing the W3schools tutorial will help you complete this assignment.
Please use this file and enter your answers following the questions.
Use the following three tables for questions 1- 12:
EMPLOYEE (EmpNumber, Name, Email)
PROJECT (ProjectName, Description, StartDate, EndDate)
ASSIGNMENT (EmpNumber, ProjectName, TotalHoursWorked)
Assume that the relationship from EMPLOYEE to ASSIGNMENT is 1:N, M-O and that the relationship from PROJECT to ASSIGNMENT is 1:N, M-O.
Note the DATATYPEs used in these examples are from Oracle. There may be slight differences if using SQL Server or Access (for instance in Oracle it is integer; in SQL Server and Access, it is int)
FINALLY – pay attention to your data type – THIS IS IMPORTANT – there are data types for numbers, text and dates. PAY SPECIAL attention to numbers – sometimes they are text and sometimes they are numbers.
A NUMBER IS ANYTHING THAT YOU CAN MANIPULATE WITH ARITHMETIC [add, subtract, multiply, divide]. Number data types differ between database systems but common data type names are integer, number, float, currency.
NUMBERS THAT YOU DO NOT NEED TO USE ARITHMETIC WITH ARE TEXT. Phone numbers and zip codes are good examples. Yes, they use numbers as characters but they are TEXT, not numbers.
1. Write a CREATE TABLE statement for the EMPLOYEE table. Assume that Name is required and an alternate key. Email is neither required nor an alternate key.
2. Write a CREATE TABLE statement for PROJECT. Assume that only ProjectName is required, and that no column is an alternate key.
3. Write a CREATE TABLE statement for ASSIGNMENT. Use the EMPLOYEE and PROJECT tables from your answers to questions 1 and 2. Set the default value of TotalHoursWorked to 3. Cascade deletions from PROJECT to ASSIGNMENT.
4. Write an ALTER statement to add the column Phone to EMPLOYEE. Assume that Phone is not required.
5. Write an ALTER statement to remove the column Email from EMPLOYEE.
6. Write an ALTER statement to make Phone an alternate key in EMPLOYEE (alternate keys are unique).
7. Write an ALTER statement to drop the constraint that Names be unique in EMPLOYEE.
In your answers to questions 8 through 12, assume that the EMPLOYEE table has its original format of EmpNumber, Name, Email. Assume that the relationship is 1:N from EMPLOYEE to Assignment.
8. Write an INSERT statement to add the row (12345, 'Jones', 'Jones@MyCorp.com') to EMPLOYEE.
9. Write an INSERT statement to add the row (12345, 'Jones@MyCorp.com', 'Jones') to EMPLOYEE.
10. Write an INSERT statement to add the row (12345, 'Jones') to EMPLOYEE.
11. Write an UPDATE statement to change the name of the employee named 'Jones' to 'Smith'.
12. Assume that ASSIGNMENT and PROJECT have valid data. Write an UPDATE statement to set the TotalHoursWorked to 15 for every row in ASSIGNMENT having the value 12345 for Employee.
13. Write the statement to delete employee 12345.
14. What command is used to delete a Table?
Explanation / Answer
If you have any doubts, please give me comment...
CREATE TABLE EMPLOYEE(
EmpNumber NUMBER(7) NOT NULL PRIMARY KEY,
Name NVARCHAR(100),
Email NVARCHAR(100)
);
CREATE TABLE PROJECT(
ProjectName NVARCHAR(100) NOT NULL PRIMARY KEY,
Description TEXT,
StartDate DATE,
EndDate DATE
);
CREATE TABLE ASSIGNMENT(
EmpNumber NUMBER(7),
ProjectName NVARCHAR(100),
TotalHoursWorked NUMBER(7) DEFAULT 3,
PRIMARY KEY(EmpNumber, ProjectName),
FOREIGN KEY(EmpNumber) REFERENCES EMPLOYEE(EmpNumber),
FOREIGN KEY(ProjectName) REFERENCES PROJECT(ProjectName) ON DELETE CASCADE
);
ALTER TABLE EMPLOYEE ADD Phone NCHAR(15);
ALTER TABLE EMPLOYEE DROP COLUMN Email;
ALTER TABLE EMPLOYEE ALTER COLUMN Phone NCHAR(15) UNIQUE KEY;
ALTER TABLE EMPLOYEE ALTER COLUMN Name NVARCHAR(100) UNIQUE KEY;
INSERT INTO EMPLOYEE VALUES(12345, 'Jones', 'Jones@MyCorp.com');
INSERT INTO EMPLOYEE(EmpNumber, Email, Name) VALUES (12345, 'Jones@MyCorp.com', 'Jones');
INSERT INTO EMPLOYEE(EmpNumber, Name) VALUES (12345, 'Jones');
UPDATE EMPLOYEE SET Name ='Smith' WHERE Name='Jones';
UPDATE ASSIGNMENT SET TotalHoursWorked = 15 WHERE EmpNumber = 12345;
DELETE FROM EMPLOYEE WHERE EmpNumber = 12345;
DROP TABLE tbl_name;