daily pastebin goal
23%
SHARE
TWEET

Rashidul Hasan Hridoy | DBMS Lab Practice Question Solve 3

a guest Feb 19th, 2019 114 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 1.
  2. Customer(customerNo, name, email)
  3. Orders(orderNo, orderDate, handingCost, customerNo)
  4. Includes(unitPrice, quantity, orderNo, productNo)
  5. Products(productNo, productName, listPrice)
  6.  
  7. 2.
  8. SQL> create table Customer(
  9.   2  customerNo int not null primary key,
  10.   3  name varchar(255),
  11.   4  email varchar(255));
  12.  
  13. Table created.
  14.  
  15.  
  16.  
  17.  
  18. SQL> create table Orders(
  19.   2  orderNo int not null primary key,
  20.   3  orderDate date,
  21.   4  handingCost number(10,2),
  22.   5  customerNo int not null,
  23.   6  foreign key(customerNo) REFERENCES Customer(customerNo)
  24.   7  );
  25.  
  26. Table created.
  27.  
  28.  
  29.  
  30.  
  31. SQL> create table Product(
  32.   2  productNo int not null primary key,
  33.   3  productName varchar(255),
  34.   4  listPrice number(10, 2));
  35.  
  36. Table created.
  37.  
  38.  
  39.  
  40. SQL> create table Includes(
  41.   2  unitPrice number(10,2),
  42.   3  unitQuantity number(10,2),
  43.   4  orderNo int not null,
  44.   5  productNo int not null,
  45.   6  foreign key(orderNo) references orders(orderNo),
  46.   7  foreign key(productNo) references Product(productNo)
  47.   8  );
  48.  
  49. Table created.
  50.  
  51.  
  52.  
  53.  
  54. 3.
  55. SQL> insert all
  56.   2  into Customer(customerNo, name, email) values(1, 'Hridoy', 'Hridoy@mail.com')
  57.   3  into Customer(customerNo, name, email) values(2, 'Hasan', 'Hasan@mail.com')
  58.   4  into Customer(customerNo, name, email) values(3, 'Monisha', 'Monisha@mail.com')
  59.   5  into Customer(customerNo, name, email) values(4, 'Ahamed', 'Ahamed@mail.com')
  60.   6  into Customer(customerNo, name, email) values(5, 'Sajeeb', 'Sajeeb@mail.com')
  61.   7  select * from dual;
  62.  
  63. 5 rows created.
  64.  
  65.  
  66.  
  67. SQL> insert all
  68.   2  into Product(productNo, productName, listPrice)values(1, 'pen', 10)
  69.   3  into Product(productNo, productName, listPrice)values(2, 'pencil', 9)
  70.   4  into Product(productNo, productName, listPrice)values(3, 'paper', 2)
  71.   5  into Product(productNo, productName, listPrice)values(4, 'ball', 10)
  72.   6  into Product(productNo, productName, listPrice)values(5, 'marker', 25)
  73.   7  select * from dual;
  74.  
  75. 5 rows created.
  76.  
  77.  
  78.  
  79.  
  80.  
  81.  
  82. SQL> insert all
  83.   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)
  84.   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)
  85.   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)
  86.   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)
  87.   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)
  88.   7  select * from dual;
  89.  
  90. 5 rows created.
  91.  
  92.  
  93.  
  94.  
  95.  
  96. SQL> insert all
  97.   2  into Includes(unitPrice, unitQuantity, orderNo, productNo)values(100, 200, 1, 1)
  98.   3  into Includes(unitPrice, unitQuantity, orderNo, productNo)values(9, 300, 2, 2)
  99.   4  into Includes(unitPrice, unitQuantity, orderNo, productNo)values(2, 400, 3, 3)
  100.   5  into Includes(unitPrice, unitQuantity, orderNo, productNo)values(10, 100, 4, 4)
  101.   6  into Includes(unitPrice, unitQuantity, orderNo, productNo)values(25, 50, 5, 5)
  102.   7  select * from dual;
  103.  
  104. 5 rows created.
  105.  
  106.  
  107.  
  108.  
  109. 4.
  110.  
  111. SQL> select Product.productName, Includes.unitQuantity
  112.   2  from((Includes
  113.   3  INNER JOIN Product ON Includes.productNo = Product.productNo)
  114.   4  INNER JOIN Orders ON Includes.orderNo = Orders.orderNo AND orderDate >= TO_DATE('2018/01/01', 'yyyy/mm/dd')
  115.   5  AND orderDate <= TO_DATE('2018/12/31','yyyy/mm/dd'));
  116.  
  117.  
  118.  
  119. 5.
  120.  
  121.  
  122. SQL> select Customer.name from Customer
  123.   2  INNER JOIN Orders ON
  124.   3  Orders.customerNo = Customer.customerNo
  125.   4  order by orderDate asc;
  126.  
  127.  
  128.  
  129. 6.
  130. There are three joins.
  131. For question 4 and 5 , I have used inner join.
  132.  
  133. 7.
  134. My experience with sql query still now is,
  135. Create table, insert value, delete value, update value, join, select, where etc.
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top