1.Create table Project which has the following fields :
PNO number(3)
PNAME varchar2(60)
PMGR number(4)
PERSONS number(5)
BUDGET number(8,2)
PSTART date
PEND date
Also enforce the following constraints on the fields: PNO should be the primary key with the constraint name as prj_pk, PNAME should have the unique constraint,foreign key constraint on PMGR reference to the empno in the employee table, BUDGET should have the not null constraint, check constraint to check that the PSTART date is less than the PEND date.
ANS:
CREATE TABLE PROJECT(PNO NUMBER(3) CONSTRAINT PRJ_PK PRIMARY KEY,PNAME VARCHAR2(60) CONSTRAINT PNAME_CNSTRNT UNIQUE KEY,PMGR NUMBER(4) CONSTRAINT PMGR_CNSTRNT REFERENCES FOREIGN KEY(EMPLOYEE.EMPNO),PERSONS NUMBER(5),BUDGET NUMBER(8,2) NOT NULL,PEND DATE,PSTRAT DATE CONSTRAINT BUD_CNSTRNT CHECK CHECK PSTART<PEND);
2. Create table customer which has the following fields:
Customerid number(5)
Firstname varchar2(10)
Lastname varchar2(20)
City varchar2(10)
ANS:
CREATE TABLE CUSTOMER(CUSTOMERID NUMBER(5),FIRSTNAME VARCHAR2(10),LASTNAME VARCHAR2(20),CITY VARCHAR2(10));
3. Create table items_ordered which has the following fields:
Customerid number(5)
Order-Date date
Item varchar2(20)
Quantity number(4)
Price number(4,2)
ANS:
CREATE TABLE ITEMS_ORDERED(CUSTOMERID NUMBER(5),ORDER-DATE DATE,ITEM VARCHAR2(20),QUANTITY NUMBER(4),PRICE NUMBER(4,2));
4. Alter table customer add the column State varchar2(20) to it.
ANS:
ALTER TABLE CUSTOMER ADD COLUMN STATE VARCHAR2(20);
5. Alter table add primary key constraint to the Customerid of the customer table. Alter the
same table and modify the Firstname column and change varchar2(10) to varchar2(20).
ANS:
ALTER TABLE CUSTOMER MODIFY CUSTOMERID NUMBER(5) CONSTRAINT CID_CNSTRNT FOREIGN KEY;
ALTER TABLE CUSTOMER MODIFY FIRSTNAME VARCHAR2(20);
1. Alter table items_ordered and add the foreign key constraint to the Customerid .
2. Using the insert statement enter the following records in the PROJECT table.
| Pno | Pname | Pmgr | Persons | Budget | Pstart | Pend |
| 100 | Java | 7499 | 5 | 5000 | 23- Dec-90 | 21-Feb-91 |
| 101 | ChannelPoint | 7698 | 10 | 4000 | 20-Dec-90 | 03-Apr-91 |
| 102 | CRM | 7788 | 12 | 8000 | 19-Jun-91 | 20-Aug-92 |
| 103 | GE | 7876 | 30 | 9000 | 23-Mar-95 | 15-Dec-95 |
| 104 | GE Lighting | 7900 | 20 | 5000 | 16-Sep-93 | 20-Oct-94 |
| 105 | E-com | 7934 | 40 | 9000 | 09-Mar-98 | 28-Dec-99 |
ANS:
INSERT INTO PROJECT VALUES(100,’JAVA’,7499,5,5000,’23-DEC-90’,’21-FEB-91’);
INSERT INTO PROJECT VALUES(101,’CHANNELPOINT’,7698,10,4000,’20-DEC-90’,’03-FEB-91’);
INSERT INTO PROJECT VALUES(102,’CRM’,7788,12,4000,’20-DEC-90’,’03-FEB-91’);
8. Insert the following records in the Customer table.
| customerid | firstname | Lastname | City | state |
| | | | | |
| 10101 | John | Gray | Lynden | Washington |
| 10298 | Leroy | Brown | Pinetop | Arizona |
| 10299 | Elroy | Keller | Snoqualmie | Washington |
| 10315 | Lisa | Jones | Oshkosh | Wisconsin |
| 10325 | Ginger | Schultz | Pocatello | Idaho |
| 10329 | Kelly | Mendoza | Kailua | Hawaii |
| 10330 | Shawn | Dalton | Cannon Beach | Oregon |
| 10338 | Michael | Howell | Tillamook | Oregon |
| 10339 | Anthony | Sanchez | Winslow | Arizona |
9. Insert the following records in the Items_Ordered table.
| customerid | order_date | Item | quantity | price |
| | | | | |
| 10330 | 30-Jun-1999 | Pogo stick | 1 | 28.00 |
| 10101 | 30-Jun-1999 | Raft | 1 | 58.00 |
| 10298 | 01-Jul-1999 | Skateboard | 1 | 33.00 |
| 10101 | 01-Jul-1999 | Life Vest | 4 | 125.00 |
| 10299 | 06-Jul-1999 | Parachute | 1 | 1250.00 |
| 10339 | 27-Jul-1999 | Umbrella | 1 | 4.50 |
| 10101 | 18-Aug-1999 | Rain Coat | 1 | 18.30 |
| 10298 | 19-Sep-1999 | Lantern | 2 | 29.00 |
| 10298 | 01-Dec-1999 | Helmet | 1 | 22.00 |
| 10101 | 30-Dec-1999 | Hoola Hoop | 3 | 14.75 |
| 10330 | 01-Jan-2000 | Flashlight | 4 | 28.00 |
| 10101 | 02-Jan-2000 | Lantern | 1 | 16.00 |
| 10299 | 18-Jan-2000 | Inflatable Mattress | 1 | 38.00 |
| 10101 | 08-Mar-2000 | Sleeping Bag | 2 | 88.70 |
| 10298 | 18-Mar-2000 | Pocket Knife | 1 | 22.38 |
