dbm

1.
CREATE TABLE employee(empno int,ename VARCHAR(50),job VARCHAR(30),manager_no
int,sal int,commission FLOAT);

INSERT INTO employee1 VALUES(2,"shashi","coordinator",10043,80000,1500.00);
 INSERT INTO employee1 VALUES(3,"mamatha","academic head",10044,85000,2500.00);
 INSERT INTO employee1
VALUES(1,"gayathri","principal",10042,90000,2000.00);
COMMIT;
SELECT* FROM employee;

ALTER TABLE employee1 add constraint primary key(empno);
ALTER TABLE employee1 modify column empno int NOT NULL;

: INSERT INTO employee1 VALUES(7,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM employee1;
INSERT INTO employee1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL);


2.
CREATE TABLE employee
(empno NUMBER,
ename VARCHAR2(10),
job VARCHAR2(10),
mgr NUMBER,
sal NUMBER);

ALTER TABLE employee add(commission NUMBER);
OR
ALTER TABLE employee add commission int;

INSERT INTO employee VALUES(101,'abhi','manager',1234,10000,'70');
(x5)

SET linesize 10000;
SELECT *FROM emplyoee;

UPDATE employee SET job='trainee' WHERE empno=103;
SELECT * FROM employee;

ALTER TABLE employee rename column mgr TO manager_no;
DESC employee;

DELETE employee WHERE empno=105;
SELECT * FROM employee;


3.
CREATE TABLE employee
(
e_id int,
ename VARCHAR2(50),
Age int,
sal FLOAT);
DESC employee;

INSERT INTO...

SELECT COUNT(ename) AS employeenames FROM employee;
OR
SELECT COUNT(ename) AS total_employee FROM employee;

SELECT MAX(age) FROM employee;

SELECT MIN(age) FROM employee;

SELECT * FROM employee3 ORDER BY salary ASC;
OR
SELECT ename, salary FROM employee ORDER BY salary;

SELECT SUM(salary) FROM employee3 GROUP BY age;
OR
SELECT salary COUNT(*) AS emp_count FROM employee GROUP BY salary;


4.
CREATE TABLE customers (
 id int primary key,
 name VARCHAR(255),
 age int,
 address VARCHAR(255),
 salary DECIMAL(10, 2) );
CREATE OR REPLACE TRIGGER trg_customers_salary_diff
After INSERT OR UPDATE OR DELETE
ON customers
FOR each ROW
DECLARE
 old_salary NUMBER;
 new_salary NUMBER;
 salary_diff NUMBER;
 BEGIN
-- handle insert operation
 IF inserting THEN
 old_salary := NULL;
 new_salary := :NEW.salary;
 salary_diff := new_salary;
 DBMS_OUTPUT.put_line('inserted row: salary = ' || new_salary);

 -- handle delete operation
 ELSIF deleting THEN
 old_salary := :old.salary;
 new_salary := NULL;
 salary_diff := old_salary;
 DBMS_OUTPUT.put_line('deleted row: salary = ' || old_salary);

 -- handle update operation
 ELSIF updating THEN
 old_salary := :old.salary;
 new_salary := :NEW.salary;
 salary_diff := new_salary - old_salary;
 DBMS_OUTPUT.put_line('updated row: old salary = ' || old_salary || ', new salary = ' ||
new_salary || ', difference = ' || salary_diff);
 END IF;
END;/

SET SERVEROUTPUT ON;

INSERT INTO CUSTOMERS VALUES (1,'John Doe', 30, '123 Elm St', 50000.00);
Inserted ROW: Salary = 50000.00
UPDATE CUSTOMERS SET SALARY = 55000.00 WHERE NAME = 'John Doe';
DELETE FROM CUSTOMERS WHERE NAME = 'John Doe';


5.
CREATE TABLE Employee (
 E_id NUMBER PRIMARY KEY,
 E_name VARCHAR2(255),
 Age NUMBER,
 Salary NUMBER );
INSERT INTO Employee (E_id, E_name, Age, Salary) VALUES (1, 'Alice', 30, 50000);
 INSERT INTO Employee (E_id, E_name, Age, Salary) VALUES (2, 'Bob', 25,
45000);
 INSERT INTO Employee (E_id, E_name, Age, Salary) VALUES (3, 'Charlie', 35,
60000);
 COMMIT;
SET SERVEROUTPUT ON;
DECLARE
 -- Declare variables to hold the values from the Employee table.
 v_E_id Employee.E_id%TYPE;
 v_E_name Employee.E_name%TYPE;
 v_Age Employee.Age%TYPE;
DATABASE MANAGEMENT SYSTEM BCS403
Dept. OF CSE, Vemana IT 15
 v_Salary Employee.Salary%TYPE;
 -- Declare the cursor
 CURSOR emp_cursor IS
 SELECT E_id, E_name, Age, Salary
 FROM Employee;
 BEGIN
 -- Open the cursor
 OPEN emp_cursor;
 -- Loop through each row fetched by the curso
LOOP
 FETCH emp_cursor INTO v_E_id, v_E_name, v_Age, v_Salary;
 -- Exit the loop when no more rows are fetched
 EXIT WHEN emp_cursor%NOTFOUND;
 -- Here you can process the fetched values.
DBMS_OUTPUT.PUT_LINE('E_id: ' || v_E_id || ', E_name: ' || v_E_name || ', Age: ' ||
v_Age || ', Salary: ' || v_Salary);
 END LOOP;

 -- Close the cursor
 CLOSE emp_cursor;
END;
/


6.
CREATE TABLE N_RollCall (
 student_id NUMBER PRIMARY KEY,
 student_name VARCHAR2(100),
 birth_date DATE);
CREATE TABLE O_RollCall (
 student_id NUMBER PRIMARY KEY,
 student_name VARCHAR2(100),
 birth_date DATE);
Step 2:
INSERT test data INTO N_rollcall
INSERT INTO N_rollcall VALUES (1, 'John Doe', '20-JAN-1990');
INSERT INTO N_rollcall VALUES (2, 'Jane Smith', '21-MAR-1992');
INSERT INTO N_rollcall VALUES (3, 'Bob', '20-july-2003');
INSERT test data INTO O_rollcall
INSERT INTO O_rollcall VALUES (4, 'James', '20-JAN-1997');
INSERT INTO O_rollcall VALUES (2, 'Jane Smith', '21-MAR-1992');
Step 3: Enable DBMS_OUTPUTLINE
SET SERVEROUTPUT ON;
Step 4: EXECUTE the PL/SQL block
DECLARE
 V_count NUMBER;
 CURSOR c_new_rollcall IS
 SELECT student_id, student_name, birth_date
 FROM n_rollcall;
BEGIN
 FOR new_rec IN c_new_rollcall LOOP
 SELECT COUNT(*)
 INTO v_count
 FROM o_rollcall
 WHERE student_id = new_rec.student_id;
 IF v_count = 0 THEN
 INSERT INTO o_rollcall (student_id, student_name, birth_date)
 VALUES (new_rec.student_id, new_rec.student_name, new_rec.birth_date);
DBMS_OUTPUT.put_line('Record inserted: ' || new_rec.student_id);
 ELSE
 DBMS_OUTPUT.put_line('Record skipped: ' || new_rec.student_id);
 END IF;
 END LOOP;
 COMMIT;
END;
/


7.
1.CREATE collection student:
1. db.createCollection(‘student’)
2.INSERT VALUES IN TO student :
2. db.sales.insertMany([
{_id : 1, Name:’xyz’, Branch:’AIML’, USN:37, Age:19},
{_id : 2, Name:’sam’, Branch:’CSE’, USN:49, Age:20},
{_id : 3, Name:’Jack’, Branch:’Mech’, USN:48, Age:21},
{_id : 4, Name:’Viola’, Branch:’AIML’, USN:60, Age:22},
{_id : 5, Name:’Lucy’, Branch:’AIML’, USN:72, Age:23},
])
3. UPDATE
db.student.updateOne({_id:1},{$set:{age:20}})
4. REMOVE
db.student.updateOne({_id:2},{$unset:{usn:1}})
5. RENAME
db.student.updateOne({_id:3},{$rename:{name:’fullname’}})
6. ADD
db.student.updateOne({_id:1},{$set:{marks:600}})
7. DELETE
db.student.deleteOne({_id:4})
db.student.deleteMany({branch:’cse’})
8. PROJECTIONS
db.student.find({},{age:1,_id:0})
9. COMPARISION OPERATOR
db.student.find({age:{$gt:20}})
db.student.find({$in:[12-25]})
10. LOGICAL OPERATOR
a. AND
db.student.find({$and:[{age:{$eq:20}},{branch:{$eq:’aiml’}}]})
b. OR
db.student.find({$or:[{usn:{$eq:72}},{branch:{$eq:’civil’}}]})