Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Oracle Lab programs for B.Com CA for academic year 2024-25
Typology: Lab Reports
1 / 30
This page cannot be seen from the preview
Don't miss anything!
An Associate of Ramakrishna group of Institutions (AFFILIATED TO BHARATHIAR UNIVERSITY) (ACCREDITED BY NAAC) DEPARTMENT OF COMMERCE WITH COMPUTER APPLICATIONS DATABASE MANAGEMENT SYSTEM PRACTICAL RECORD 2023-
An Associate of Ramakrishna group of Institutions (AFFILIATED TO BHARATHIAR UNIVERSITY) (ACCREDITED BY NAAC) DEPARTMENT OF COMMERCE WITH COMPUTER APPLICATIONS II- SEMESTER BONAFIDE CERTIFICATE This is to certify that DATABASE MANAGEMENT system is a Bonafide Work Done by NAME : REG.NO : BATCH : 2023- SUBMITTED FOR THE PRACTICAL EXAMINATION HELD ON STAFF INCHARGE HEAD OF THE DEPARTMENT INTERNAL EXAMINER EXTERNAL EXAMINER INDEX
Aim: To create and manage Project and Employee tables using SQL, insert records into the tables, and perform various queries such as updating salary, retrieving employee details based on conditions, and applying aggregate functions. ALGORITHM: Step 1: Create Project Table
create table proj(projno number(5) primary key,location char(20),custname char(20),year char(4)); desc proj; insert into proj values('1','Chennai','Vimal','2005'); insert into proj values('2','Coimbatore','Vijay','2006'); insert into proj values('3','salem','ramesh','2005'); insert into proj values('4','Chennai','ravi','2005'); insert into proj values('5','Chennai','suresh','2006'); insert into proj values('6','salem','murali','2005'); select * from proj; create table emplo(empno number(5) primary key,empname char(20),deptno number(5),deptname char(20),projno number(5),salary number(8,2)); desc emplo; insert into emplo values('101','richard','10','quality','1','15000'); insert into emplo values('102','krishnan','20','sales','3','20000'); insert into emplo values('103','ravi','10','production','2','16000'); insert into emplo values('104','rahul','30','hr','1','24000'); insert into emplo values('105','prakash','40','manager','2','18000'); insert into emplo values('106','prem','20','quality','4','250000'); insert into emplo values('107','vijay','10','production','5','30000'); insert into emplo values('108','vikram','10','pm','6','35000'); insert into emplo values('109','venu','40','gm','1','35000');
insert into emplo values('110','sathish','20','quality','3','28000'); select * from emplo; a) Increase the salary of the employees working in department number 10 by 15%. UPDATE Emplo SET salary = salary+(salary15/100) WHERE deptno = 10; select * from emplo; b) Find the employee number and names of employees who get the salary in the range 20000 and 30000. SELECT * FROM emplo WHERE salary BETWEEN 20000 AND 30000; c) Find the information about the employees whose name starts with the letter ‘r’. select * from emplo where empname like 'r%'; d) Find the employee names and salary for employees who work in ‘production’ department select empname,salary from emplo where deptname like'production%'; e) Find the details of the employee who gets the maximum salary. select MAX(SALARY) from emplo; f) Find the department number of all departments which has more than 2 employees working in it. select deptname from emplo group by deptname having count ()>2;
create table warehouse(code char(15) primary key,location char(15),capacity varchar(15)); desc warehouse; insert into warehouse values('1','cbe','4'); insert into warehouse values('2','cbe','5'); insert into warehouse values('3','pldm','2'); insert into warehouse values('4','sulur','3'); insert into warehouse values('5','pldm','2'); insert into warehouse values('6','newyork','3'); select * from warehouse; create table boxes(bcode char(15),contents char(15),value varchar(10),warehouse char(10));
desc boxes; insert into boxes values('s4hi','cup','380','5'); insert into boxes values('d7yt','pen','280','4'); insert into boxes values('n9ok','note','130','5'); insert into boxes values('e2ep','gum','110','2'); insert into boxes values('h2aa','doll','170','3'); insert into boxes values('c4kj','lamp','130','3'); insert into boxes values('r9oi','pen','110','2'); insert into boxes values('b3uh','note','140','3'); insert into boxes values('d9og','lamp','280','5'); insert into boxes values('h5rt','papers','280','2'); select * from boxes; a) Modify B Code as a Not null in “BOXES” table ALTER TABLE boxes MODIFY bcode char(15) NOT NULL; b) In Boxes table Code must be greater than 100 how to evaluate the condition? select bcode from boxes group by bcode having count (*)>100; d. Boxes table “contents” contains many duplicate values within it. Retrieve the value without any duplication. SELECT contents, COUNT(value) AS Duplicatevalues
e. Find the values between 3 and 8 and show the results and Use all Aggregate function in boxes table (Use Value field) SELECT * FROM boxes WHERE warehouse BETWEEN 3 and 8; Aggregate function: SELECT COUNT(bcode) FROM boxes; SELECT AVG(warehouse) FROM boxes; SELECT sum(value) FROM boxes;
create table piece(code number(6) primary key,name char(25),color char(10)); desc piece; insert into piece values('1','sprocket','white'); insert into piece values('2','screw','black'); insert into piece values('3','nut','white'); insert into piece values('4','bolt','black'); select * from piece; CODE NAME COLOR 1 sprocket white 2 screw black 3 nut white 4 bolt black create table providers(code char(8) primary key,name char(25) not null,state char(15),phoneno number(10)); desc providers; insert into providers values('HAL','Clarke Enterprises','Tamilnadu','6543980987'); insert into providers values('RBT','Susan Calvin corp ','Tamilnadu','9756437206'); insert into providers values('TNBC','Skellington supplies','Tamilnadu','9875438790'); select * from providers; CODE NAME STATE PHONENO HAL Clarke Enterprises Tamilnadu 6543980987 RBT Susan Calvin corp Tamilnadu 9756437206 TNBC Skellington supplies Tamilnadu 9875438790 create table provides1(piece number(6) primary key,provider char(6),price number(8,2),qty number(5)); desc provides1; insert into provides1 values('1','hal','10','5'); insert into provides1 values('2','rbt','15','7'); insert into provides1 values('3','hal','20','5');
Program executed successfully
To create the tables Manufacturers and Products with appropriate primary and foreign key constraints, insert given records, and execute SQL queries to retrieve product details, perform calculations, and display results based on specified conditions. ALGORITHM: Step 1: Start the Program
Open SQL environment (Oracle / MySQL / SQL Server). Step 2: Create Table – Manufacturers
insert into products values('2','memory','120','6'); insert into products values('3','zip drive','150','4'); insert into products values('4','floppy disk','5','6'); insert into products values('5','monitor','240','1'); insert into products values('6','dvd drive','180','2'); insert into products values('7','cd drive','90','2'); insert into products values('8','printer','270','3'); insert into products values('9','toner cartridge','66','3'); insert into products values('10','dvd burner','180','2'); select * from products; CODE NAME PRICE MANUFACTURER 1 hard drive 240 5 2 memory 120 6 3 zip drive 150 4 4 floppy disk 5 6 5 monitor 240 1 6 dvd drive 180 2 7 cd drive 90 2 8 printer 270 3 9 toner cartridge 66 3 10 dvd burner 180 2 a. Select the name and price in cents (i.e., the price must be multiplied by 100). SELECT Name, Price * 100 AS PriceCents FROM Products; NAME PRICECENTS hard drive 24000 memory 12000 zip drive 15000 floppy disk 500 monitor 24000 dvd drive 18000 cd drive 9000 printer 27000 toner cartridge 6600 dvd burner 18000 b. Select the name and price of all products with a price larger than or equal to $180, and sort first by price (in descending order), and then by name (in ascending order). SELECT Name, Price FROM Products WHERE Price >= ORDER BY Price DESC, Name; NAME PRICE printer 270
hard drive 240 monitor 240 dvd burner 180 dvd drive 180 c. Select all the data from the products, including all the data for each product's manufacturer. SELECT * FROM Products, Manufacturers WHERE Products.Manufacturer = Manufacturers.Code; CODE NAME PRICE MANUFACTURER CODE NAME 1 hard drive 240 5 5 fujisu 2 memory 120 6 6 winchester 3 zip drive 150 4 4 lonega 4 floppy disk 5 6 6 winchester 5 monitor 240 1 1 sony 6 dvd drive 180 2 2 creative labs 7 cd drive 90 2 2 creative labs 8 printer 270 3 3 havelet packard 9 toner cartridge 66 3 3 havelet packard 10 dvd burner 180 2 2 creative labs d. Select the average price of each manufacturer's products, showing the manufacturer's name SELECT AVG(Price), Manufacturers.Name FROM Products, Manufacturers WHERE Products.Manufacturer = Manufacturers.Code GROUP BY Manufacturers.Name; AVG(PRICE) NAME 240 fujisu 62.5 winchester 240 sony 168 havelet packard 150 lonega 150 creative labs e. Select the names of manufacturer whose products have an average price larger than or equal to $150. SELECT AVG(Price), Manufacturers.Name FROM Products, Manufacturers WHERE Products.Manufacturer = Manufacturers.Code GROUP BY Manufacturers.Name HAVING AVG(Price) >= 150; AVG(PRICE) NAME 240 fujisu 240 sony 168 havelet packard 150 lonega 150 creative labs
