API tools faq
paste
Advertisement
fsoc131y

DBC

fsoc131y
Oct 10th, 2023
1,073
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 10.50 KB | Help | 0 0
raw download clone embed print report
  1. EXP 5:
  2. 1. CREATE TABLE DEPOSIT WITH COLUMNS ACCNO NUMBER(3), NAME VARCHAR2(10), BANME CHAR(2), BALANCE NUMBER(6), LDATE DATE.
  3. SQL> CREATE TABLE deposit(accno NUMBER(3),name VARCHAR(10),bname CHAR(2),balance NUMBER(6),ldate DATE);
  4.  
  5. 2. CREATE TABLE LOAN WITH COLUMNS LOANNO NUMBER(3), NAME VARCHAR2(10), BNAME CHAR(2), AMOUNT NUMBER(3), LLDATE DATE
  6. SQL> CREATE TABLE loan(loanno NUMBER(3),name VARCHAR(10),bname CHAR(2),amount NUMBER(3),lldate DATE);
  7.  
  8. 3. INSERT DATA TO DEPOSIT AND LOAN TABLES
  9. SQL> INSERT INTO deposit VALUES(101,'varun','b1',25000,'01-Aug-23');
  10. SQL> INSERT INTO deposit VALUES(102,'gokul','b2',20000,'04-mar-13');
  11. SQL> INSERT INTO deposit VALUES(103,'rai','b1',30000,'05-dec-19');
  12. SQL> INSERT INTO deposit VALUES(104,'ramesh','b4',1500,'07-jan-22');
  13. SQL> INSERT INTO deposit VALUES(105,'vansh','b1',15000,'08-feb-22');
  14. SQL> INSERT INTO loan VALUES(101,'varun','b1',240,'06-mar-23');
  15. SQL> INSERT INTO loan VALUES(102,'vajid','b2',200,'23-mar-22');
  16. SQL> SELECT * FROM deposit;
  17. SQL> SELECT * FROM loan;
  18.  
  19. 4. UPDATE BALANCE OF DEPOSIT TABLE BY adding 10 % AS interest TO the BALANCE FOR LDATE(LAST DATE OF TRANSACTION) prior TO 1-Jan-14
  20. SQL> UPDATE deposit SET balance = (balance*(10/100))+balance WHERE ldate<'01-jan-14';
  21. SQL> SELECT * FROM deposit;
  22.  
  23. 5. UPDATE LAST DATE OF TRANSACTION FOR DEPOSIT AS ’24-may-15’ FOR ACCNO 104
  24. SQL> UPDATE deposit SET ldate = '24-may-15' WHERE accno = 104;
  25. SQL> SELECT * FROM deposit;
  26.  
  27. 6. CONVERT ALL FIRST characters alone IN NAME TO UPPER CASE IN DEPOSIT AND LOAN TABLES.
  28. SQL> SELECT initcap(name) FROM deposit;
  29. SQL> SELECT initcap(name) FROM loan;
  30.  
  31. 7. Display the ACCNO AND FIRST three characters OF NAME IN DEPOSIT TABLE.
  32. SQL> SELECT accno,substr(name,1,3) FROM deposit;
  33.  
  34. 8. Display ALL details OF depositors whose LAST DATE OF TRANSACTION exceeds 1 YEAR.
  35. SQL> SELECT * FROM deposit WHERE ldate<'02-aug-22';
  36.  
  37. 9. UPDATE LDATE FOR ACCNO 103 TO include TIME also.
  38. SQL> UPDATE deposit SET ldate = to_date('30-NOV-2013 15:30', 'DD-MON-YYYY HH24:MI') WHERE accno =103;
  39.  
  40. 10. Find the NUMBER OF account holders IN branch B1 HAVING BALANCE > 10000.
  41. SQL> SELECT COUNT(accno) FROM deposit WHERE balance > 10000 AND bname='b1';
  42.  
  43. 11. Find the average balance FOR each branch.
  44. SQL> SELECT bname,avg(balance) FROM deposit GROUP BY bname;
  45.  
  46. 12. Sort the DEPOSIT TABLE IN ascending ORDER OF BALANCE.
  47. SQL> SELECT * FROM deposit ORDER BY balance;
  48.  
  49. 13. Sort the LOAN TABLE IN descending ORDER OF NAME.
  50. SQL> SELECT * FROM loan ORDER BY name DESC;
  51.  
  52. 14. Display the average, maximum AND SUM OF balances OF depositors FOR branch B2.
  53. SQL> SELECT avg(balance),MAX(balance),SUM(balance) FROM deposit WHERE bname ='b2';
  54.  
  55. 15. INSERT a NEW ROW IN deposit TABLE FOR ACCNO 106.
  56. SQL> INSERT INTO deposit VALUES(106,'won','b3',10000,'22-apr-20');
  57.  
  58. 16. DELETE the tuple FOR ACCNO 106.
  59. SQL> DELETE deposit WHERE accno =106;
  60. SQL> SELECT * FROM deposit;
  61.  
  62. 17. CREATE a copy DEPOSIT1 OF DEPOSIT TABLE.
  63. SQL> CREATE TABLE deposit1 AS SELECT * FROM deposit;
  64.  
  65. 18. SELECT DISTINCT names FROM DEPOSIT AND LOAN (USE UNION)
  66. SQL> SELECT name FROM deposit UNION SELECT name FROM loan;
  67.  
  68. 19. SELECT ALL names FROM DEPOSIT AND LOAN (USE UNION ALL)
  69. SQL> SELECT name FROM deposit UNION ALL SELECT name FROM loan;
  70.  
  71. 20. SELECT names OF customers HAVING BOTH deposit AND loan (USE INTERSECT)
  72. SQL> SELECT name FROM deposit INTERSECT SELECT name FROM loan;
  73.  
  74. 21. SELECT names OF customers who have deposit but no loan taken (USE MINUS)
  75. SQL> SELECT name FROM deposit minus SELECT name FROM loan;
  76.  
  77. EXP 6:
  78. 1. WRITE SQL queries TO implement NATURAL JOIN, INNER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN AND FULL OUTER JOIN operations.
  79. SQL> CREATE TABLE orderr(orderid NUMBER(10),customerid NUMBER(3),orderdate DATE);
  80. SQL> CREATE TABLE customer(customerid NUMBER(3),customername VARCHAR(20),contactname VARCHAR(20),country VARCHAR(10));
  81. SQL> INSERT INTO orderr
  82.   2  SELECT 10308,2,'18-sep-1996' FROM dual
  83.   3  UNION ALL
  84.   4  SELECT 10309,37,'19-sep-1996' FROM dual
  85.   5  UNION ALL
  86.   6  SELECT 10310,77,'20-sep-1996' FROM dual;
  87. SQL> SELECT * FROM orderr;
  88.  
  89. SQL> INSERT INTO customer
  90.   2  SELECT 1,'Alfreds Futterkiste','Maria Anders','Germany' FROM dual
  91.   3  UNION ALL
  92.   4  SELECT 2,'Ana Trujillo','Ana Trujillo','Mexico' FROM dual
  93.   5  UNION ALL
  94.   6  SELECT 3,'Antonio','Antonio Moreno','Mexico' FROM dual;
  95.  
  96. SQL> SELECT * FROM orderr NATURAL JOIN customer;
  97.  
  98. CUSTOMERID    ORDERID ORDERDATE CUSTOMERNAME         CONTACTNAME          COUNTRY
  99. ---------- ---------- --------- -------------------- -------------------- ----------
  100.          2      10308 18-SEP-96 Ana Trujillo         Ana Trujillo         Mexico
  101.  
  102. SQL> SELECT orderr.orderid,customer.customername,orderr.orderdate FROM orderr INNER JOIN customer ON orderr.customerid=customer.customerid;
  103.  
  104. SQL> SELECT * FROM orderr RIGHT OUTER JOIN customer ON orderr.customerid=customer.customerid;
  105.  
  106. SQL> SELECT orderr.customerid,customer.contactname,orderr.orderid FROM orderr LEFT OUTER JOIN customer ON orderr.customerid=customer.customerid;
  107.  
  108. SQL> SELECT orderr.customerid,customer.contactname,orderr.orderid FROM orderr FULL OUTER JOIN customer ON orderr.customerid=customer.customerid;
  109.  
  110. 2. WRITE SQL queries TO implement Nested queries.
  111. SQL> SELECT customerid,orderid FROM orderr WHERE customerid IN (SELECT customerid FROM customer);
  112. SQL> SELECT customerid,orderid FROM orderr WHERE customerid NOT IN (SELECT customerid FROM customer);
  113. SQL> SELECT customerid,orderid FROM orderr WHERE orderid > ALL (SELECT customerid FROM customer );
  114. SQL> SELECT customerid,orderid FROM orderr WHERE orderid < any (SELECT customerid FROM customer );
  115.  
  116. 3. WRITE SQL queries TO implement HAVING clause.
  117. SQL> SELECT COUNTRY, COUNT() AS CUSTOMER_COUNT FROM customer GROUP BY COUNTRY HAVING COUNT() > 1;
  118. SQL> SELECT COUNTRY, COUNT() AS CUSTOMER_COUNT FROM customer GROUP BY COUNTRY HAVING COUNT() > 0 ORDER BY country DESC;
  119.  
  120.  
  121. EXP 7:
  122. a) CREATE the following TABLES
  123. SQL> CREATE TABLE empl(eno NUMBER(4),name VARCHAR(20),desgn VARCHAR(12),stree CHAR(2),city CHAR(2),mngrno NUMBER(5));
  124.  
  125. SQL> INSERT INTO empl
  126.   2  SELECT 100,'LEELA','SUPDT','S1','C1',100 FROM DUAL
  127.   3  UNION ALL
  128.   4  SELECT 101,'RAJAN','SALESMAN','S2','C1',101 FROM DUAL
  129.   5  UNION ALL
  130.   6  SELECT 102,'JOS','SALESMAN','S1','C2',101 FROM DUAL
  131.   7  UNION ALL
  132.   8  SELECT 103,'ANIL','SYSADM','S3','C1',103 FROM DUAL
  133.   9  UNION ALL
  134.  10  SELECT 104,'KUMAR','PROGRAMMER','S3','C1',103 FROM DUAL
  135.  11  UNION ALL
  136.  12  SELECT 105,'MEENA','CLERK','S2','C1',101 FROM DUAL
  137.  13  UNION ALL
  138.  14  SELECT 106,'ANUP','CLERK','S1','C2',100 FROM DUAL
  139.  15  UNION ALL
  140.  16  SELECT 107,'USHA','PROGRAMMER','S2','C2',103 FROM DUAL;
  141. SQL> SELECT * FROM EMPL;
  142.  
  143. SQL> CREATE TABLE works(eno NUMBER(4),cname VARCHAR(20),salary NUMBER(7));
  144.  
  145.  
  146. SQL> INSERT INTO works
  147.   2  SELECT 100,'ABC COMPANY',10000 FROM DUAL
  148.   3  UNION ALL
  149.   4  SELECT 101,'PQR ASSOCIATES',22000 FROM DUAL
  150.   5  UNION ALL
  151.   6  SELECT 102,'POR ASSOCIATES',19000 FROM DUAL;
  152. SQL> SELECT * FROM WORKS;
  153.  
  154. SQL> CREATE TABLE company(cname VARCHAR(20),city CHAR(4));
  155. SQL> INSERT INTO company
  156.   2  SELECT 'ABC COMPANY','C1' FROM dual
  157.   3  UNION ALL
  158.   4  SELECT 'PQR ASSOCIATES','C2' FROM DUAL;
  159. SQL> SELECT * FROM COMPANY;
  160.  
  161. b) Display the names OF employees who are managers.
  162. SQL> SELECT name FROM empl WHERE eno=mngrno;
  163.  
  164. c) How many persons WORK UNDER ANIL.
  165. SQL> SELECT COUNT(*) FROM EMPL WHERE MNGRNO = (SELECT ENO FROM EMPL WHERE NAME = 'ANIL');
  166.  
  167. d) Find the employee name, designation OF the employee getting maximum salary.
  168. SQL> SELECT empl.name,empl.desgn FROM empl WHERE eno =(SELECT eno FROM works WHERE salary=(SELECT MAX(salary) FROM works));
  169.  
  170. e) Find the company IN which Meena works.
  171. SQL> SELECT cname FROM works WHERE eno =(SELECT eno FROM empl WHERE name ='MEENA');
  172.  
  173. f) Display employees who are managers.
  174. SQL> SELECT name FROM empl WHERE eno IN(SELECT mngrno FROM empl);
  175.  
  176. g) Find ALL employees who earn more than the average salary OF ALL employees OF their company.
  177. SQL> SELECT name FROM empl WHERE(SELECT salary FROM works WHERE eno = empl.eno)>(SELECT avg(salary) FROM works WHERE cname=(SELECT cname FROM works WHERE eno=empl.eno));
  178.  
  179. h) Find the employees working IN ABC Company.
  180. SQL> SELECT name FROM empl WHERE eno IN(SELECT eno FROM works WHERE cname='ABC COMPANY');
  181.  
  182. i) Find ALL employees who earn more than each employee IN ABC Company.
  183. SQL> SELECT name FROM empl WHERE eno IN(SELECT eno FROM works WHERE salary >ALL(SELECT salary FROM works WHERE cname='ABC COMPANY'));
  184.  
  185. j) Find the names, street AND city OF ALL employees who WORK FOR PQR ASSOCIATES AND earn more than 10000.
  186. SQL> SELECT name,stree,city FROM empl WHERE eno IN(SELECT eno FROM works WHERE cname='PQR ASSOCIATES' AND salary>10000);
  187.  
  188. k) Find the company HAVING maximum NUMBER OF employees
  189. SQL> SELECT cname FROM (SELECT w.cname, COUNT(e.eno) AS emp_count FROM works w JOIN empl e ON w.eno = e.eno GROUP BY w.cname ORDER BY emp_count DESC)WHERE ROWNUM = 1;
  190.  
  191. l) Find ALL employees who live IN the same cities AS their mangers.
  192. SQL> SELECT NAME FROM EMPL WHERE CITY IN(SELECT CITY FROM EMPL WHERE ENO = MNGRNO);
  193.  
  194. m) Find ALL employees who live IN the same city AND same street AS do their managers.
  195. SQL> SELECT NAME FROM EMPL WHERE CITY IN(SELECT CITY FROM EMPL WHERE ENO = MNGRNO) AND STREE IN(SELECT STREE FROM EMPL WHERE ENO=MNGRNO);
  196.  
  197. EXP 8:
  198. SQL> CREATE TABLE orders (
  199.   2      order_id INT PRIMARY KEY,
  200.   3      customer_id INT,
  201.   4      order_date DATE,
  202.   5      total_amount DECIMAL(10, 2),
  203.   6      STATUS VARCHAR(20)
  204.   7  );
  205.  
  206. SQL> INSERT INTO orders (order_id, customer_id, order_date, total_amount, STATUS)
  207.   2  SELECT 1, 101, TO_DATE('2023-09-01', 'YYYY-MM-DD'), 125.50, 'Completed' FROM dual
  208.   3  UNION ALL
  209.   4  SELECT 2, 102, TO_DATE('2023-09-02', 'YYYY-MM-DD'), 75.25, 'Shipped' FROM dual
  210.   5  UNION ALL
  211.   6  SELECT 3, 103, TO_DATE('2023-09-03', 'YYYY-MM-DD'), 210.75, 'Pending' FROM dual
  212.   7  UNION ALL
  213.   8  SELECT 4, 104, TO_DATE('2023-09-04', 'YYYY-MM-DD'), 50.00, 'Completed' FROM dual
  214.   9  UNION ALL
  215.  10  SELECT 5, 105, TO_DATE('2023-09-05', 'YYYY-MM-DD'), 320.99, 'Shipped' FROM dual;
  216.  
  217. SQL> CREATE VIEW customer_orders AS
  218.   2  SELECT customer_id, order_date, total_amount
  219.   3  FROM orders
  220.   4  WHERE STATUS = 'Completed';
  221. SQL> SELECT * FROM customer_orders;
  222.  
  223. SQL> CREATE OR REPLACE VIEW customer_orders AS
  224.   2  SELECT customer_id, order_date, total_amount
  225.   3  FROM orders
  226.   4  WHERE STATUS = 'Shipped';
  227. SQL> SELECT * FROM customer_orders;
  228.  
  229. SQL> DROP VIEW customer_orders;
  230.  
  231. ***SQL> SELECT * FROM customer_orders;
  232. SELECT * FROM customer_orders
  233.               *
  234. ERROR at line 1:
  235. ORA-00942: TABLE OR VIEW does NOT exist***
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!