1) Data Definition Language Table: Student Regno number (5) primary key Studname varchar2 (15) Gender char (6) Deptname char (15) Address char (25) Percentage number (4, 2) Queries: a) To create a table b) To describe a table c) To alter a table d) To drop a table e) To truncate a table 2) Data Manipulation Language Table: Student Regno number (5) primary key Studname varchar2 (15) Gender char (6) Deptname char (15) Address char (25) Percentage number (4, 2) Queries: a. To insert values b. To retrieve records c. To update records d. To delete records 3) Create an Employee table with following field. Eno number (5) primary key Ename varchar2 (20) not null Deptno number (2) not null Desig char (10) not null Sal number (9, 2) not null Comm. Number (7, 2) null Queries: a) Insert values and display the records b) Display sum, maximum amount of basic pay c) List the name of the clerks working in the department 20 d) Display name that begins with ‘G’ e) List the names having ‘I’ as the second character f) List the names of employees whose designation are ‘Analyst’ and ‘Salesman’ g) List the different designation available in the Employee table without duplication (distinct) 4) Create a table "Company" with the following fields and insert the values for 10 employees. Compid number (6) primary key Compname varchar2 (15) not null Proprietor varchar2 (15) not null Address varchar2 (25) not null Supname varchar2 (15) Noofempl number (4) GPPercent number (6, 2) Queries: a) Display all the records of the company which are in the ascending order of GP percent. b) Display the name of the company whose supplier name is "Telco". c) Display the details of the company whose GP percent is greater than 20 and Order by GP Percent. d) Display the detail of the company having the employee ranging from 300 to 1000. e) Display the name of the company whose supplier is same as the Tata's. 5) Create a student table with the following fields Stuno number (5) primary key Stunm Varchar2 (20) Age number (2) Mark1 number (3) Mark2 number (3) Mark3 number (3) Queries: a) Insert values and display the records b) List the names and age of the student whose age is more than 12 c) Display total and average of marks d) Display the names of the maximum total & minimum total student e) List the names of the student that ends with ‘A’ f) List the names of student whose names have exactly 5 characters (Programs 1 to 5 are to be worked out and executed in both Oracle SQL and Microsoft SQL) 6) Create a table "Product" with the following fields and insert the values: Prodno number (6) Prodname varchar2 (15) Unitofmeasure varchar2 (15) Qty number (6, 2) Totamt number (8, 2) Queries: a) Using update statements calculate the total amount and then select the record. b) Select the records whose unit of measure is "Kg". c) Select the records whose quantity is greater than 10 and less than or equal to 20. d) Calculate the entire total amount by using sum operation. e) Calculate the number of records whose unit price is greater than 50 with count operation. 7) Create the table Payroll with the following fields and insert the values: Emplno number (8) Emplname varchar2 (8) Dept varchar2 (10) Baspay number (8, 2) HRA number (6, 2) DA number (6, 2) Pf number (6, 2) Netpay number (8, 2) Queries: a) Update the records to calculate the net pay. b) Arrange the records of the employees in ascending order of their net pay. c) Display the details of the employees whose department is "Sales". d) Select the details of employees whose HRA>= 1000 and DA<=900. e) Select the records in descending order. 8) Create a table Deposit and loan with the following fields: Table: Deposit Accno number (3) Account varchar2 (6) Branch Name varchar2 (15) Custname varchar2 (20) Balanceamt varchar2 (10) Table: Loan Loanno number (5) Branchnm varchar2 (15) Custnm varchar2 (30) Loanamt number (10) Queries: a) Insert the records into the table. b) Describe the structure of the table. c) Display the records of Deposit and Loan. d) Find the number of loans with amount between 10000 and 50000. e) List in the alphabetical order the names of all customers who have a loan at the Coimbatore branch. f) Find the average account balance at the Coimbatore branch. g) Update deposits to add interest at 5% to the balance. h) Arrange the records in descending order of the loan amount. i) Find the total amount of deposit in 'Erode' branch. II. Ledgers, Zoho books the open source software will be taken up for the recording of 1. Purchase 2. Sales 3. Stock position 4. Total Payable 5. Total Receivable 6. Cash and Bank Books

-

-

-

-