fork(35) download 
  1. 							4.1: Database Objects
  2.  
  3.  
  4. 1.	Create the Customer table with the following columns. 
  5. 	CustomerId	Number(5)
  6. 	Cust_Name	varchar2(20)
  7. 	Address1	Varchar2(30)
  8. 	Address2 	Varchar2(30)
  9. 	   ==>create table customer
  10. 	(
  11. 	customerid number(5),
  12. 	cust_name varchar2(20),
  13. 	Address1 varchar2(30),
  14. 	Address2 varchar2(30)
  15. 	);
  16.  
  17. 2.	Modify the Customer table Cust_Name column of datatype with Varchar2(30), rename the column to CustomerName and it should not 	accept Nulls.
  18. 	   ==>Alter table customer rename column cust_name to customername;
  19. 	   ==>Alter table customer modify customername  varchar2(30) Not Null;
  20.  
  21. 3.	a) Add the following Columns to the Customer table. 
  22. 	Gender	Varchar2(1)
  23. 	Age Number(3)
  24. 	PhoneNo	Number(10)
  25. 	   ==>Alter table customer add Gender varchar2(1);
  26. 	   ==>Alter table customer add Age Number(3);
  27. 	   ==>Alter table customer add phoneNo(10);
  28. 	 b) Rename the Customer table to Cust_Table
  29. 	   ==>Rename customer to cust_table;
  30.  
  31. 4.	Insert rows with the following data in to the Customer table.
  32. 	   ==>insert into cust_table(&Customerid,'&cust_Name','&Address1','&Address2','&Gender',&Age,&phoneNo);
  33. 	   ==>1000, ‘Allen’, ‘#115 Chicago’, ‘#115 Chicago’, ‘M’, ‘25, 7878776’
  34. 	   ==>1001, George, #116 France, #116 France, M, 25, 434524
  35. 	   ==>1002, Becker, #114 New York, #114 New York, M, 45, 431525
  36.  
  37. 5.	Add the Primary key constraint for Customerld with the name Custld_Prim. 
  38.  
  39. 	   ==>Alter table cust_table add constraints Custid_prim PRIMARY KEY (customerid);
  40.  
  41. 6.	Insert the row given below in the Customer table and see the message generated by the Oracle server. 
  42. 	1002, John, #114 Chicago, #114 Chicago, M, 45, 439525
  43.  
  44. 	   ==>
  45.  
  46. 7.	Disable the constraint on CustomerId, and insert the 	following data:
  47. 	1002, Becker, #114 New York, #114 New york , M, 45, 431525
  48. 	1003, Nanapatekar, #115 India, #115 India , M, 45, 431525
  49.  
  50. 	   ==>Alter table cust_table drop PRIMARY KEY custid_prim;
  51.  
  52. 8.	Enable the constraint on CustomerId of the Customer table, and see the message generated by the Oracle server. 
  53.  
  54. 	   ==>Alter table cust_table add constraints Custid_prim PRIMARY KEY (customerid);
  55. 	   ==>
  56.  
  57. 9.	Drop the constraint Custld_Prim on CustomerId and insert the following Data. Alter Customer table, drop constraint 	Custid_Prim.
  58. 	1002, Becker, #114 New York, #114 New york , M, 45, 431525, 15000.50
  59. 	1003, Nanapatekar, #115 India, #115 India , M, 45, 431525, 20000.50	
  60.  
  61. 	   ==>Alter table cust_table drop PRIMARY KEY custid_prim;
  62. 	   ==>Insert into cust_table(1002, Becker, #114 New York, #114 New york , M, 45,431525, 15000.50);
  63. 	   ==>Insert into cust_table(1003, Nanapatekar, #115 India, #115 India , M, 45, 431525,20000.50);
  64.  
  65. 10.	Delete all the existing rows from Customer table, and let the structure remain itself using TRUNCATE statement. 
  66. 	   ==>TRUNCATE table cust_table;
  67.  
  68.  
  69. 11.	In the Customer table, add a column E_mail. 
  70. 	   ==>Alter table add e_mail varchar2(30);
  71.  
  72.  
  73. 12.	Drop the E_mail column from Customer table. 
  74. 	   ==>Alter table cust_table DROP e_mail;
  75.  
  76. 13.	Create the Suppliers table based on the structure of the Customer table. Include only the CustomerId, CustomerName, 		Address1, Address2, and phoneno columns. 
  77. 	Name the columns in the new table as SuppID, SName, Addr1, 	Addr2, and Contactno respectively. 
  78. 	   ==>create table Suppliers as select(customerid as suppid,customername as sname,adddress1 as addr1,address2 as 	addr2,phoneno as contactno) from cust_table;
  79.  
  80. 14.	Drop the above table and recreate the following table with the name CustomerMaster. 
  81. 	Customerid	Number(5) Primary key(Name of constraint is CustId_PK)
  82. 	CustomerName	Varchar2(30) Not Null
  83. 	Addressl	Varchar2(30) Not Null
  84. 	Address2	Varchar2(30)
  85. 	Gender		Varchar2(l)
  86. 	Age		Number(3)
  87. 	PhoneNo	Number(10)
  88. 	   ==>Drop table Suppliers;
  89. 	   ==>create table customermaster(customerid(10) primary key(custid_pk),customername varchar2(30),Address1 	varchar2	(30),Address2 varchar2(30),Gender varchar2(1),Age number(3),phoneno number(10));
  90.  
  91. 15.	Create the AccountsMaster table with the following Columns. Use sequence to generate Account number
  92. 	Customerid Number(5)
  93. 	AccountNumber Number(10,2) Primary key(Name of constraint is Acc_PK)
  94. 	AccountType Char(3)
  95. 	LedgerBalance Number(10,2) Not Null
  96. 	   ==>Create table Accoutnmaster(customerid number(5),Accountnumber number(10) primary key(acno),accounttype char	(3),ledgerbalance number(10) Not Null);
  97. 	   ==>Create sequence seq_ano
  98. 		MINVALUE 101
  99. 		MAXVALUE 10000
  100. 		START WITH 101
  101. 		INCREMENT BY 1
  102. 		CACHE 101;
  103.  
  104. 16.	Relate AccountsMaster table and CustomerMaster table through Customerld column with the constraint name Cust_acc.
  105. 	   ==>Alter table Accountmaster ADD constraint ass_fk FOREIGN KEY(customerid) REFERENCES customermaster(customerid);
  106.  
  107. 17.	Insert the following rows to the CustomerMaster table:
  108. 	1000, Allen, #115 Chicago, #115 Chicago, M, 25, 7878776
  109. 	1001, George, #116 France, #116 France, M, 25, 434524
  110. 	1002, Becker, #114 New York, #114 New York, M, 45, 431525
  111. 	   ==>Insert into customermaster values(1000, Allen, #115 Chicago, #115 Chicago, M, 25, 7878776);
  112. 	   ==>Insert into customermaster values(1001, George, #116 France, #116 France, M, 25, 4345240;
  113. 	   ==>Insert into customermaster values(1002, Becker, #114 New York, #114 New York, M, 45, 4315250;
  114.  
  115. 18.	Modify the AccountMaster table with the Check constraint to ensure AccountType should be either NRI or IND.
  116. 	   ==>alter table Accountmaster add constraint ck_ac check(accountype='NRI' or accountype='IND');
  117.  
  118. 19.	Modify the AccountsMaster table keeping a Check constraint with the name Balance_Check for the Minimum Balance which 			should be greater than 5000. 
  119. 	   ==>alter table Accountmaster add constraint 	Balance_check(ledger balance > 5000);
  120.  
  121. 20.	Modify the AccountsMaster table such that if Customer is deleted from Customer table then all his details should be 			deleted from AccountsMaster table. 
  122. 	   ==>Delete from Accountmaster,customertable where customerid = 1001
  123.  
  124.  
  125. 21.	Create Backup copy for the AccountsMaster table with the name ‘AccountDetails’. 
  126.  
  127. 	   ==>Create table accountdetails as select * from Accountmaster;
  128.  
  129. 22.	Create a view ‘Acc_view’ with columns Customerld, CustomerName, AccountNumber, AccountType, and LedgerBalance from 		AccountsMaster. In the view Acc_view, 	the column names should be CustomerCode, AccountHolderName, AccountNumber, Type, and 		Balance for the respective columns from AccountsMaster table. 
  130. 	   ==>CREATE VIEW Acc_view AS SELECT(Customerid,Customername,Accountnumber,AccountType,ledgerBalance)
  131. 	from AccountMaster;
  132.  
  133. 23.	Create a view on AccountsMaster table with name vAccs_Dtls. This view should list all customers whose AccountType is ‘IND’ 		and their balance amount should not be less than 10000. Using this view any DML operation should not violate the view 		conditions. 
  134. 	   ==>CREATE VIEW vAccs_Dtls AS SELECT 	Accounttype,ledgerbalance from Accountmaster where 	accounttype = 'IND' and 		ledgerbalance < 10000;
  135.  
  136. 24.	Create a view accsvw10 which will not allow DML statement against it.
  137. 	   ==>
  138.  
  139. 25.	Create a Sequence with the name Seq_Dept on Deptno column of Department_Masters table. It should start from 40 and stop at 		200. Increment parameter for the sequence Seq_Dept should be in step of 10.
  140. 	   ==>CREATE sequence SEQ_DEPTt minvalue 40 start with 40
  141. 	increment by 10 cache 40;
  142.  
  143. 26.	Insert three sample rows by using the above sequence in Department_Masters table.
  144. 	   ==>create table departmentmaster(deptno number(50),Dname varchar2(25),location varchar2(25));
  145. 	   ==>insert into departmentmaster  values(seq_dept.NEXTVAL,'MARKETING','NEW DELHI');
  146. 	   ==>==>insert into departmentmaster  values(seq_dept.NEXTVAL,'SALES','chennai');
  147. 	   ==>==>insert into departmentmaster  values(seq_dept.NEXTVAL,'RESEARCH','BOSTON');
  148.  
  149. 27.	Drop the Seq_Dept sequence. 
  150.  
  151. 	   ==>DROP sequence seq_dept;
  152.  
  153. 28.	Get information on the index No_Name from the Data Dictionary. 
  154. 	   ==>CREATE INDEX no_name on emp(empno);
  155. 	   ==>select * from emp;
  156. 29.	Create synonym synEmp for the EMP table. 
  157. 	   ==>create SYNONYM synemp for emp;
  158.  
  159. 30.	Get Information on synonym synEmp from the Data Dictionary. 
  160. 	   ==>select * from synemp;
Runtime error #stdin #stdout #stderr 0s 28112KB
comments (?)
stdin 
Standard input is empty
stdout 
Standard output is empty
stderr 
invalid command name "4.1:"
    while executing
"4.1: Database Objects"
    (file "prog" line 1)
https://ideone.com/T8eOwX
language:
TCL (tcl 8.6)
created:
7 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!