DBMS Lab 4 by Hridoy

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. ALL rights reserved.

C:\Windows\System32>sqlplus/nolog

SQL*Plus: Release 10.2.0.1.0 - Production ON Mon Feb 25 08:53:15 2019

Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

SQL> conn AS / sysdba
USER altered.

SQL> conn hr
Enter password:
Connected.
SQL> Customer(customer_no, name, email)
SP2-0734: UNKNOWN command beginning "Customer(c..." - rest OF line ignored.
SQL> Orders (order_no, order_date, handing_cost,  customer_no)
SP2-0734: UNKNOWN command beginning "Orders (or..." - rest OF line ignored.
SQL> Product(product_no, product_name, list_price)
SP2-0734: UNKNOWN command beginning "Product(pr..." - rest OF line ignored.
SQL>
SQL>
SQL>
SQL>
SQL> Customer(customerNo, name, email)
SP2-0734: UNKNOWN command beginning "Customer(c..." - rest OF line ignored.
SP2-0044: FOR a list OF known commands enter HELP
AND TO leave enter EXIT.
SQL> Orders(orderNo, orderDate, handingCost, customerNo)
SP2-0734: UNKNOWN command beginning "Orders(ord..." - rest OF line ignored.
SQL> Includes(unitPrice, quantity, orderNo, productNo)
SP2-0734: UNKNOWN command beginning "Includes(u..." - rest OF line ignored.
SQL> Products(productNo, productName, listPrice)
SP2-0734: UNKNOWN command beginning "Products(p..." - rest OF line ignored.
SQL> DESC Customer;
ERROR:
ORA-04043: object Customer does NOT exist


SQL> DESC orders
ERROR:
ORA-04043: object orders does NOT exist


SQL> CREATE TABLE Customer(
  2  customerNo INT NOT NULL PRIMARY KEY,
  3  name VARCHAR(255),
  4  email VARCHAR(255));

TABLE created.

SQL> CREATE TABLE Orders(
  2  orderNo INT NOT NULL PRIMARY KEY,
  3  orderDate DATE,
  4  handingCost NUMBER(10,2),
  5  customerNo INT NOT NULL,
  6  FOREIGN KEY(customerNo) REFERENCES Customer(customerNo)
  7  );

TABLE created.

SQL>  CREATE TABLE Product(
  2  productNo INT NOT NULL PRIMARY KEY,
  3  productName VARCHAR(255),
  4  listPrice NUMBER(10, 2));

TABLE created.

SQL> CREATE TABLE Includes(
  2  unitPrice NUMBER(10,2),
  3  unitQuantity NUMBER(10,2),
  4  orderNo INT NOT NULL,
  5  productNo INT NOT NULL,
  6  FOREIGN KEY(orderNo) REFERENCES orders(orderNo),
  7  FOREIGN KEY(productNo) REFERENCES Product(productNo)
  8  );

TABLE created.

SQL> INSERT ALL
  2  INTO Customer(customerNo, name, email) VALUES(1, 'Hridoy', '[email protected]
')
  3  INTO Customer(customerNo, name, email) VALUES(2, 'Hasan', '[email protected]')

  4  INTO Customer(customerNo, name, email) VALUES(3, 'Monisha', '[email protected]
om')
  5  INTO Customer(customerNo, name, email) VALUES(4, 'Ahamed', '[email protected]
')
  6  INTO Customer(customerNo, name, email) VALUES(5, 'Sajeeb', '[email protected]
')
  7  SELECT * FROM dual;

5 ROWS created.

SQL> INSERT ALL
  2  INTO Product(productNo, productName, listPrice)VALUES(1, 'pen', 10)
  3  INTO Product(productNo, productName, listPrice)VALUES(2, 'pencil', 9)
  4  INTO Product(productNo, productName, listPrice)VALUES(3, 'paper', 2)
  5  INTO Product(productNo, productName, listPrice)VALUES(4, 'ball', 10)
  6  INTO Product(productNo, productName, listPrice)VALUES(5, 'marker', 25)
  7  SELECT * FROM dual;

5 ROWS created.

SQL> INSERT ALL
  2  INTO Orders(orderNo, orderDate, handingCost, customerNo) VALUES(1,  TO_DATE
('2018/12/16 01:00:00', 'yyyy/mm/dd hh:mi:ss'), 100, 1)
  3  INTO Orders(orderNo, orderDate, handingCost, customerNo) VALUES(2,  TO_DATE
('2018/12/15 02:00:00', 'yyyy/mm/dd hh:mi:ss'), 120, 2)
  4  INTO Orders(orderNo, orderDate, handingCost, customerNo) VALUES(3,  TO_DATE
('2018/12/17 03:00:00', 'yyyy/mm/dd hh:mi:ss'), 130, 3)
  5  INTO Orders(orderNo, orderDate, handingCost, customerNo) VALUES(4,  TO_DATE
('2018/12/11 04:00:00', 'yyyy/mm/dd hh:mi:ss'), 400, 4)
  6  INTO Orders(orderNo, orderDate, handingCost, customerNo) VALUES(5,  TO_DATE
('2018/12/14 05:00:00', 'yyyy/mm/dd hh:mi:ss'), 800, 5)
  7  SELECT * FROM dual;

5 ROWS created.

SQL> INSERT ALL
  2  INTO Includes(unitPrice, unitQuantity, orderNo, productNo)VALUES(100, 200,
1, 1)
  3  INTO Includes(unitPrice, unitQuantity, orderNo, productNo)VALUES(9, 300, 2,
 2)
  4  INTO Includes(unitPrice, unitQuantity, orderNo, productNo)VALUES(2, 400, 3,
 3)
  5  INTO Includes(unitPrice, unitQuantity, orderNo, productNo)VALUES(10, 100, 4
, 4)
  6  INTO Includes(unitPrice, unitQuantity, orderNo, productNo)VALUES(25, 50, 5,
 5)
  7  SELECT * FROM dual;

5 ROWS created.

SQL> SELECT avg(handingCost) FROM Orders;

AVG(HANDINGCOST)
----------------
             310

SQL> SELECT COUNT(customerNo) FROM Customer;

COUNT(CUSTOMERNO)
-----------------
                5

SQL> SELECT SUM(handingCost), MAX(handingCost), MIN(handingCost) FROM Orders;

SUM(HANDINGCOST) MAX(HANDINGCOST) MIN(HANDINGCOST)
---------------- ---------------- ----------------
            1550              800              100

SQL> SELECT * FROM Customer UNION ALL SELECT * FROM Orders;
SELECT * FROM Customer UNION ALL SELECT * FROM Orders
*
ERROR at line 1:
ORA-01789: query block has incorrect NUMBER OF RESULT COLUMNS


SQL> SELECT customerNo FROM Customer
  2  UNION ALL
  3  SELECT customerNo FROM Orders;

CUSTOMERNO
----------
         1
         2
         3
         4
         5
         1
         2
         3
         4
         5

10 ROWS selected.