Posts

lab7

// Insert a document into a collection named 'users'  db.users.insertOne({ name: "John", age: 30, city: "New York" });  // Find all documents in the 'users' collection  db.users.find();  // Find documents with a specific condition  db.users.find({ age: { $gte: 25 } });  // Find users with age greater than or equal to 25  // Update a document in the 'users' collection  db.users.updateOne({ name: "John" }, { $set: { age: 35 } });  // Delete a document from the 'users' collection  db.users.deleteOne({ name: "John" });
Post a Comment
Read more

lab6

CREATE OR REPLACE PROCEDURE Merge_RollCall_Data AS  CURSOR N_RollCall_Cursor IS  SELECT *  FROM N_RollCall;  v_N_RollCall_Record N_RollCall%ROWTYPE;  BEGIN  FOR v_N_RollCall_Record IN N_RollCall_Cursor LOOP -- Check if data already exists in O_RollCall table  SELECT COUNT(*)  INTO v_Count  FROM O_RollCall  WHERE EMPNO = v_N_RollCall_Record.EMPNO  AND ROLL_DATE = v_N_RollCall_Record.ROLL_DATE; -- If data doesn't exist, insert into O_RollCall  IF v_Count = 0 THEN  INSERT INTO O_RollCall (EMPNO, ROLL_DATE, STATUS)  VALUES  (v_N_RollCall_Record.EMPNO,  v_N_RollCall_Record.STATUS);  END IF;  END LOOP;  COMMIT;  END Merge_RollCall_Data;  /
Post a Comment
Read more

lab5

 DECLARE  v_E_id Employee.E_id%TYPE;  v_E_name Employee.E_name%TYPE;  v_Age Employee.Age%TYPE;  v_Salary Employee.Salary%TYPE;  CURSOR emp_cursor IS  SELECT E_id, E_name, Age, Salary  FROM Employee; BEGIN  OPEN emp_cursor;  LOOP  FETCH emp_cursor INTO v_E_id, v_E_name, v_Age, v_Salary;  EXIT WHEN emp_cursor%NOTFOUND;  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_E_id || ', Employee Name: ' || v_E_name  || ', Age: ' || v_Age || ', Salary: ' || v_Salary);  END LOOP;  CLOSE emp_cursor; END;
Post a Comment
Read more

lab4

CREATE OR REPLACE TRIGGER SALARY_DIFF_TRIG BEFORE INSERT OR UPDATE OR DELETE ON CUSTOMER FOR EACH ROW DECLARE old_salary number; new_salary number; BEGIN IF INSERTING THEN DBMS.OUTPUT.PUT_LINE('NEW SALARY:'||:NEW.salary); ELSIF UPDATING THEN old_salary:=:OLD.salary; new_salary:=:NEW.salary; IF old_salary IS NULL THEN old_salary:=0; END IF; IF new_salary IS NULL THEN new_salary:=0; END IF; DBMS.OUTPUT.PUT_LINE('Old Salary'||old_salary); DBMS.OUTPUT.PUT_LINE('New Salary'||new_salary); DBMS.OUTPUT.PUT_LINE('SALARY DIFF'||(new_salary-old_salary)); ELSIF DELETING THEN DBMS.OUTPUT.PUT_LINE('Old Salary'||:OLD.salary); END IF; END; / first do set serveroutput ON then create table() then edit filename then a prompt will open write all program except insert and update operation then after saving close the program then write @filename then do insert and update operation
Post a Comment
Read more

lab3

1 CREATE TABLE Employee (  E_id INT,  E_name VARCHAR(50),  Age INT,  Salary DECIMAL(10,2) ); DBMS Manual Dept. of CSE, RNSIT Page 4 2 SELECT COUNT(E_name) AS Num_of_Employees FROM Employee; 3 SELECT MAX(Age) AS Max_Age FROM Employee; 4 SELECT MIN(Age) AS Min_Age FROM Employee; 5 SELECT E_name, Salary FROM Employee ORDER BY Salary ASC; 6 SELECT Salary, COUNT(*) AS Num_of_Employees FROM Employee GROUP BY Salary;
Post a Comment
Read more

lab2

 1-- Create the Employee table CREATE TABLE Employee (  EMPNO INT,  ENAME VARCHAR(50),  JOB VARCHAR(50),  MGR INT,  SAL DECIMAL(10,2) ); -- Add a new column 'commission' to the Employee table ALTER TABLE Employee ADD COMMISSION DECIMAL(10,2); DBMS Manual Dept. of CSE, RNSIT Page 3 2 -- Insert five records into the Employee table INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES  (101, 'John Doe', 'Manager', NULL, 50000.00, 2000.00),  (102, 'Jane Smith', 'Developer', 101, 40000.00, 1500.00),  (103, 'Michael Johnson', 'Salesperson', 101, 30000.00, NULL),  (104, 'Emily Brown', 'Analyst', 102, 45000.00, 2500.00),  (105, 'David Lee', 'Intern', 102, 25000.00, NULL); 3 -- Update the job details for a specific employee UPDATE Employee SET JOB = 'Senior Developer' WHERE EMPNO = 102; 4 ALTER TABLE Employee RENAME COLUMN Employ TO Employee; 5 -- Delete the employee whose Empno is 10...
Post a Comment
Read more

lab1

1-- Create the Employee table CREATE TABLE Employee (  EMPNO INT,  ENAME VARCHAR(50),  JOB VARCHAR(50),  MANAGER_NO INT,  SAL DECIMAL(10,2),  COMMISSION DECIMAL(10,2) ); -- Create a user CREATE USER theuser IDENTIFIED BY password; -- Grant all permissions to the user GRANT ALL PRIVILEGES ON Employee TO theuser; Note: This series of SQL commands should accomplish what you've asked for. Make sure to  replace 'password' with a secure password for the user 'theuser'. 2- Insert three records into the Employee table INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES  (1, 'John Doe', 'Manager', NULL, 50000.00, 5000.00),  (2, 'Jane Smith', 'Developer', 1, 40000.00, NULL),  (3, 'Michael Johnson', 'Salesperson', 1, 30000.00, 2000.00); -- Rollback to undo the inserts ROLLBACK; 3- Add Primary Key constraint to EMPNO column ALTER TABLE Employee DBMS Manual Dept. of CSE, RNSIT Page 2 ADD CONSTRAINT PK_Emplo...
Post a Comment
Read more