Office Tools PDF

# INDEX ## PARTICULARS OF THE EXPERIMENTS PERFORMED | Expt. No. | Name of Experiment | Page No. | Date of Experiment | Date of Submission | Remarks | |---|---|---|---|---|---| | | Practical No:-1 | 27/07/24 | | | | | | Practical No:-2 | 03/08/24 | | | | | | Practical No:-3 | 10/08/24 | |...

# INDEX ## PARTICULARS OF THE EXPERIMENTS PERFORMED | Expt. No. | Name of Experiment | Page No. | Date of Experiment | Date of Submission | Remarks | |---|---|---|---|---|---| | | Practical No:-1 | 27/07/24 | | | | | | Practical No:-2 | 03/08/24 | | | | | | Practical No:-3 | 10/08/24 | | | | | | Practical No:-4 | 17/08/24 | | | | | | Practical No:-5 | 24/08/24 | | | | | | Practical No:-6 | 07/09/24 | | | | | | Practical No:-7 | 07/09/24 | | | | | | Practical No:-8 | 14/09/24 | | | | # EXPERIMENT: Practical No:-1 ## Create emp table with design view. Add emp_id, name, contact, date of birth, date of joining, address, salary, pincode, gender on it. Apply input marks and validation on emp_id, contact, DOB, DOJ, salary and pincode. ### Validation: - Contact: Its allow only 10 digits. - Emp-id: Its allow only 9 digits. - DOB: It is less then current date. - DOJ: It is less then or equal to current date. - Salary: It is more then ₹ 9000. - Pin code: It allow only 6 digits. ### Queries: - Display the whole table. ```sql Select * from emp-table; ``` - Display the record of employee with address is mumbai. ```sql Select * from emp-table where address = 'Mumbai'; ``` - Select all the records where name is sachin singh. ```sql Select * from emp-table where name = 'Sachin Singh'; ``` - Select record where emp-id is 111-111-111. ```sql Select * from emp-table where emp-id = '111-111-111'; ``` - Display all the record where salary is greater then 20,000. ```sql Select * from emp-table where Salary > 20000; ``` ### Updation Queries - Update the address of Jay Upadhyay Banglore to Mumbai. ```sql Update emp-table set address = 'Mumbai' where address = 'Banglore'; ``` - Update the name of Jay Upadhyay to Rohit Sharma. ```sql Update emp-table set name = 'Rohit Sharma' where name = 'Jay Upadhyay'; ``` - Update the Salary of 10000 to 12000. ```sql Update emp - table set Salary = 12000 where Salary = 10000; ``` ### Deletation Queries - Delete all the record where name is Sameer Shaikh. ```sql Delete * from emp-table where name = 'Sameer Shaikh'; ``` - To delete all the record of the table. ```sql Delete * from emp-table; ``` - To delete all record where salary is 12000. ```sql Delete * from emp-table where salary = 12000; ``` # EXPERIMENT: Practical No:-2 ## Greate the book store database with necessary table. Database contains authors name, book name, publish date, book price. Add the book exploring microsoft 2000 by barber publish in 1995 by prantis hall selling for 45 dollers. ### Queries: - Change the price of mom day management for all of 29-95 doller. ```sql Updale book-library set price = 29-95 Where name = 'Life of Farmers'; ``` - Delete the presentation design book. ```sql Delete * from book-library where name = 'POB'; ``` # EXPERIMENT: Practical No:-3 ## Create a table employee with Id-no, Name, Job, Age and Salary. Use the form wizard to Create a form. Enter some record in the form. Add new field into existing table. Add combo-box into form for inheritence. Add button in the form & Shortcut buttons. Greale various query wizard & to display specific data. Creale various input query to insert. Add expression builder. ### Queries: - Create a query to display information of all manager or employee. ```sql Select * from empl; ``` - insert into empl values (1, 'Sachin', 'Developer', 22,100000); ```sql insert into empl values (1, 'Sachin', 'Developer', 22,100000); ``` - Create a query to display the name of employee where salary is greater then 15000 ```sql Select * from empl where salary > 15000; ``` - Greale query to display name, job and age. ```sql Select name, age, job from empl; ``` - Create a query to update employee where id = 3 ```sql Update empl set name= 'Surya' Where emp-Id = 3; ``` - Delete the record where employee name is Sachin'. ```sql Delete * from empi where name = 'Sachin'; ``` - Create query to display employee record where age is greater then 25. ```sql Select* from empl where age > 25; ``` # EXPERIMENT: Practical No:-4 ## Use the report wizard to create new report. Modify existing report by adding, deleting and modifying. Greale query containing Calculate control. Creale report based on that query using Sorting and grouping. Use 'action queries to modified that database. Create a Cross tab query to display summarize Valve from a table. # EXPERIMENT: Practical No:-5 ## Greale a database with multiple tables. Create a one-to-one relationship between the table. Creale student table which have id and student name enter the 5 record in the table. Creale marksheet table add id, hindi, english, marathi field. Go to database tool Select the required table. Now stablished the relationship to id to id. ## Create two tables with required field to stablished one-to-many relationship. Create a table of customer id and name insert atliest 5 records. Create another table of order which contains orders id, product name or customer id. insert record according to the customer. Save the table and go to database and stablished one-to-many relationship. ## Create a database with multiple tables Create many-to-many relationship between the table. Greate course table which have course id and course name. Create student table which have student id and student name. Now select the student id from the student table and must with course student table with the student. Note:- Course id and Student id from the course and student table should be primary key. # EXPERIMENT: Practical No:-6 ## We can use lookup function into excel file. For using lookup function we have to create database in excel. Enter 15 records of name, Salary and city. Enter records ascending or descending order of the alphabets according to the name. ### Lookup formulas/Queries: - Display the salary of any person from database ```excel =Lookup (Select the name of the person, Select whole file, Select whole salary) ``` - Display city of any person from database ```excel =Lookup (Select the name of the person, Select whole file, Select whole city) ``` - Display the name where salary is ≥ 60000/- ```excel =Lookup (60000, Select whole salary, Select whole name) ``` # EXPERIMENT: Practical No:-7 ## We can use lookup function in another sheet 2 of excel. For using lookup function in another sheet2 we have to create database in sheet 1. Enter 15 record of name , salary and city. Enter record ascending or descending order of the alphabets according to the name. ### Lookup formulas/Queries: - Display the salary of any people from database in sheet 2. ```excel = Lookup (Select the name of person, Select whole file from sheets, Select whole salary from sheet 1) ``` - Display the city of any people from database in sheet 2. ```excel = Lookup (Select the name of person, Select whole file from sheet, Select whole city from sheet 1) ``` # EXPERIMENT: Practical No:-8 ## Create database in excel which contain ID, Name, Age, Gender and save it. Export that Save file in ms-acess and give name to him. Create another product table in ms - acess which contains ID, Prod-name, Prod-price, and Prod-id. Go database fool and Select the required table. Now stablished one to many relation ship between both tables to ID to prod-id. ### Queries: - Create a query to display record which customer age is greater then 25. ```sql Select * from record I where age> 25; ``` - Create a query to display name, age, gender and product name. ```sql Select name, age, gender, prod- name from recordI; ``` - Create a query to display prod-name, prod-price and customer name. ```sql Select prod-name, prod- price, name from recordI; ``` - Create a query who retrive / display piyush age and which product he brought ```sql Select age, prod_name from record, where name = 'Piyush'; ``` - Create a query to display all the detail of Khushi. ```sql Select * from record, where name = 'Khushi'; ```