Rashidul Hasan Hridoy | DBMS Lab Practice Question Solve 3

1.
Customer(customerNo, name, email)
Orders(orderNo, orderDate, handingCost, customerNo)
Includes(unitPrice, quantity, orderNo, productNo)
Products(productNo, productName, listPrice)

2.
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.




3.
SQL> insert all
  2  into Customer(customerNo, name, email) values(1, 'Hridoy', 'Hridoy@mail.com')
  3  into Customer(customerNo, name, email) values(2, 'Hasan', 'Hasan@mail.com')
  4  into Customer(customerNo, name, email) values(3, 'Monisha', 'Monisha@mail.com')
  5  into Customer(customerNo, name, email) values(4, 'Ahamed', 'Ahamed@mail.com')
  6  into Customer(customerNo, name, email) values(5, 'Sajeeb', 'Sajeeb@mail.com')
  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.




4.

SQL> select Product.productName, Includes.unitQuantity
  2  from((Includes
  3  INNER JOIN Product ON Includes.productNo = Product.productNo)
  4  INNER JOIN Orders ON Includes.orderNo = Orders.orderNo AND orderDate >= TO_DATE('2018/01/01', 'yyyy/mm/dd')
  5  AND orderDate <= TO_DATE('2018/12/31','yyyy/mm/dd'));



5.


SQL> select Customer.name from Customer
  2  INNER JOIN Orders ON
  3  Orders.customerNo = Customer.customerNo
  4  order by orderDate asc;



6.
There are three joins.
For question 4 and 5 , I have used inner join.

7.
My experience with sql query still now is,
Create table, insert value, delete value, update value, join, select, where etc.