Read instructions carefullyBased on the feedback you have received with respect to your data model and DDL, now is your chance to implement the final changes. Make any needed changes to your ERD and P
Advanced SQL
Charles Jones
CS352 Unit 4 IP
Professor Jean Karsand
1/30/2019
Database Schema design for Sales Order System
The sales order system database is composed of three tables: Customer, Employee and SalesOrder. The attributes of these tables are shown as below:
SQL> create table Employee (Customer_No number(5) primary key, FName varchar2(15), LName varchar2(15), AddressLine1 varchar2(50), AddressLine2 varchar2(15), city varchar2(15),state varchar2(3), phone varchar2(15), email varchar2(15), zipcode varchar2(15));
Output: Table created.
SQL> desc Employee;
Output:
Name Null? Type
----------------------------------------- -------- -----------------------------------------------
CUSTOMER_NO NOT NULL NUMBER(5)
FNAME VARCHAR2(15)
LNAME VARCHAR2 (15)
ADDRESLINE1 VARCHAR2 (50)
ADDRESLINE2 VARCHAR2 (15)
CITY VARCHAR2 (15)
STATE VARCHAR2 (3)
PHONE VARCHAR2 (15)
ZIPCODE VARCHAR2 (15)
EMAILID VARCHAR2 (15)
SQL> create table Employee (Customer_No number(5) primary key, FName varchar2(15), LName varchar2(15), AddressLine1 varchar2(50), AddressLine2 varchar2(15), city varchar2(15),state varchar2(3), phone varchar2(15), email varchar2(15), zipcode varchar2(15));
Output: Table created.
SQL> desc Employee;
Output:
Name Null? Type
----------------------------------------- -------- -----------------------------------------------
CUSTOMER_NO NOT NULL NUMBER(5)
FNAME VARCHAR2(15)
LNAME VARCHAR2 (15)
ADDRESLINE1 VARCHAR2 (50)
ADDRESLINE2 VARCHAR2 (15)
CITY VARCHAR2 (15)
STATE VARCHAR2 (3)
PHONE VARCHAR2 (15)
ZIPCODE VARCHAR2 (15)
EMAILID VARCHAR2 (15)
Valid Test Data
b) SQL>
insert into Employee values(&customer_no,'&Fname,’&LName’ ,’&AddressLine1’ ,’&AddressLine2 ‘, ‘&city’ ,’&state ‘,’&phone’ ,’&zipcode’ ,’&email’);
SQL> select * from Employee;
Output:
CUSTOMER_NO FNAME LName ,AddressLine1 AddressLine2 city state phone zipcode email
---------- ---------------
100 ramu SS DDD 1222 MDDDD SSS 343434 12000 [email protected]
101 kamal MDDDD 12345 DDDSSS 343434 12000 SSS [email protected]
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SQL> create table customer (Employee_SSN_No number(5) primary key, Emp_FName varchar2(15), Emp_LName varchar2(15), AddressLine1 varchar2(50), AddressLine2 varchar2(15), city varchar2(15),state varchar2(3), phone varchar2(15), email varchar2(15), zipcode varchar2(15),DateofBirth Date,Hourlywages number(3),date _of_hire Date, status varchar2(1));
Output: Table created.
SQL> desc customer;
SQL>
Output: Table created.
SQL> desc Employee;
Output:
Name Null? Type
----------------------------------------- -------- -----------------------------------------------
Employee_SSN_NO NOT NULL NUMBER(5)
EMP_FNAME VARCHAR2(15)
EMP_LNAME VARCHAR2 (15)
ADDRESLINE1 VARCHAR2 (50)
ADDRESLINE2 VARCHAR2 (15)
CITY VARCHAR2 (15)
STATE VARCHAR2 (3)
PHONE VARCHAR2 (15)
ZIPCODE VARCHAR2 (15)
DATEOFBIRTH Date
EMAILID VARCHAR2 (15)
HOURLYWAGES NUMBER(3)
DATE_OF_HIRE Date
STATUS VARCHAR2(1)
SQL> create table total(Employee_SSNnumber(4) primary key,
total_name varchar2(15),price number(6,2));
SQL> dsec total
Output:
Name Null? Type
……………………………………………………………………………………………………
Customer_No NOT NULL NUMBER(4)
Total_name VARCHAR2(15)
PRICE NUMBER(6,2)
SQL>insert into total values(&total_id,’&total_name’,&price);
SQL> select * from total;
Output:
TOTAL_ID TOTAL_NAME PRICE
……………………………………………………………………………………..
2334 geera 6.25
4532 corn soup 34.65
2124 lays chips 20
4531 setwet 99.99
2319 duracell 45.5
SaleOrder_ID |
Date_Of_Sale |
Customer_No |
Employee_SSN |
Total |
Taxes |
SQL>create table sale(SalesOrder_IDnumber(5) primary key,Date_Of_Sales date, Customer_No number(5) references customer(Customer_No), Employee_SSNnumber(4) references total, Taxes number(4));
Out put: Table Created.
SQL>dsec sale
Output:
Name Null? Type
………………………………………………………………………………………..
SALESORDER_ID NOT NULL NUMBER(4)
DATE_OF_SALES DATE
CUSTOMER_NO NUMBER(5)
TOTAL_ID NUMBER(4)
TAXES NUMBER(4)
SQL>insert into Sale values(&bill_no, ’&Date_Of_Sales’,
&Customer_No, &total &Taxes);
SQL>select * from sale;
Output:
SALESORDER_ID DATE_OF_SALES CUSTOMER_NO TOTAL TAXES
………………………………………………………………………………………………………...
1450 04-JAN-06 100 2124 2
1451 04-JAN-06 101 2319 1
1452 04-JAN-06 103 4531 2
1453 04-JAN-06 102 2334 3
1454 04-JAN-06 104 4532 3
c) List all the bills for the current date with the customer names and total numbers
SQL> select c.custname, i.total, s. SALESORDER from customer c, total I, sale s
where c.customer_no=s.customer_no and
s.date_of_sale=to_char(sysdate);
CUSTNAME TOTAL SALESORDER
------------- --------- ---------
John 5001 332
SQL> select customer_no, counts(SALESORDER_ID) from sale group by customer_no;
CUSTOMER_NO COUNT(sales)
---------- ---------------------
1 2
3 1
4 1
5 1