[go: up one dir, main page]
Scribd
Upload
86 views4 pages

DBMS Practical

The document contains examples of SQL queries and commands for creating tables, inserting data, joining tables, and retrieving data from databases. It includes examples of creating tables, inserting data, indexing, creating views, updating and deleting records, aggregating data using group by, and performing different types of joins.

Uploaded by

Vaibhav Dike
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
86 views4 pages

DBMS Practical

The document contains examples of SQL queries and commands for creating tables, inserting data, joining tables, and retrieving data from databases. It includes examples of creating tables, inserting data, indexing, creating views, updating and deleting records, aggregating data using group by, and performing different types of joins.

Uploaded by

Vaibhav Dike
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 4

dbms PRACTICAL 1

CREATE TABLE EMPLOYEE_MASTER(


EMP_ID INT,
FIRST_NAME VARCHAR(10),
MIDDLE_NAME VARCHAR(10),
LAST_NAME VARCHAR(10),
DEPARTMENT VARCHAR(10),
MANAGER_ID INT
);

INSERT INTO EMPLOYEE_MASTER VALUES(1,'VAIBHAV', 'KAILAS','DIKE','COMP',10);


INSERT INTO EMPLOYEE_MASTER VALUES(2,'VAIBHAV', 'KAILAS','DIKE','COMP',11);
INSERT INTO EMPLOYEE_MASTER VALUES(3,'VAIBHAV', 'KAILAS','DIKE','COMP',12);
INSERT INTO EMPLOYEE_MASTER VALUES(4,'VAIBHAV', 'KAILAS','DIKE','COMP',13);

SELECT * FROM EMPLOYEE_MASTER;

CREATE TABLE BRANCH_MASTER(BRANCH_ID INT,BRANCH_NAME VARCHAR(10));


INSERT INTO BRANCH_MASTER VALUES(1,'COMP');
INSERT INTO BRANCH_MASTER VALUES(2,'ELECTRIC');
INSERT INTO BRANCH_MASTER VALUES(1,'MECH');
INSERT INTO BRANCH_MASTER VALUES(1,'CIVIL');
SELECT * FROM BRANCH_MASTER;

CREATE INDEX EMP_NAME ON EMPLOYEE_MASTER (FIRST_NAME ,MIDDLE_NAME,LAST_NAME);


CREATE VIEW V1 AS
SELECT EMP_ID , FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPLOYEE_MASTER WHERE
EMP_ID > 0;
SELECT * FROM V1;

-----------------------------------------------------------------------------------
-------------------------------

DBMS PRACTICAL 2

CREATE TABLE STUDENT_INFO (


STUD_ID INT,
DEPART_NAME VARCHAR(20),
SEMESTER INT,
STUDENT_NAME VARCHAR(20),
YEAR DATE,
CREDIT INT
);

DROP TABLE STUDENT_INFO;


DROP TABLE TEACHER_INFO;

CREATE TABLE TEACHER_INFO (


TEACHER_ID INT,
TEACHER_NAME VARCHAR(20),
SALARY INT,
DEPART_NAME VARCHAR(20)
);

INSERT INTO STUDENT_INFO VALUES (1,'COMP','3','SAAD SAYYED',TO_DATE('12-11-


2023','DD-MM-YYYY'),10);
INSERT INTO STUDENT_INFO VALUES (2,'MECH','3','VAIBHAV DIKE',TO_DATE('12-11-
2023','DD-MM-YYYY'),6);
INSERT INTO STUDENT_INFO VALUES (3,'CIVIL','3','PRASAD BHOR',TO_DATE('12-11-
2023','DD-MM-YYYY'),7);
INSERT INTO STUDENT_INFO VALUES (4,'ENTC','3','AJAJ DALLE ',TO_DATE('12-11-
2023','DD-MM-YYYY'),9);
SELECT * FROM STUDENT_INFO;

INSERT INTO TEACHER_INFO VALUES(1,'JITU BAI',50000,'COMP');


INSERT INTO TEACHER_INFO VALUES(2,'RACHNA MAAM',60000,'MECH');
INSERT INTO TEACHER_INFO VALUES(3,'PRATIK SIR',350,'IT');
INSERT INTO TEACHER_INFO VALUES(4,'SHIVAJI BOSS',200,'CIVIL');

SELECT * FROM TEACHER_INFO;

UPDATE STUDENT_INFO
SET DEPART_NAME = 'IT'
WHERE DEPART_NAME = 'COMP';
SELECT * FROM TEACHER_INFO ORDER BY SALARY DESC;
SELECT DEPART_NAME FROM TEACHER_INFO WHERE SALARY = (SELECT MAX(SALARY) FROM
TEACHER_INFO);

DELETE FROM TEACHER_INFO WHERE SALARY < 2000;


SELECT * FROM TEACHER_INFO;

SELECT DEPART_NAME ,SUM(SALARY) FROM TEACHER_INFO GROUP BY DEPART_NAME;

-----------------------------------------------------------------------------------
----------------------------

DBMS PRACTICAL 4
-- Emp( empId int ,empName varchar(10),empSal int ,empDeptId int)
-- Dept(deptId int, deptName varchar(10))
-- 1. Insert few Record.
-- 2. List employees belonging to department 30, 40, or 10
-- 3. List the employee details whose salary is between 10000 to 30000.
-- 4. List total no of employee.
-- 5. List average sal of each deptID.
-- 6. List employee details in ascending order of salary.

CREATE TABLE EMP(EMPID INT,EMPNAME VARCHAR(20),EMPSAL INT ,EMPDEPTID INT);


CREATE TABLE DEPT(DEPTID INT,DEPTNAME VARCHAR(10));

INSERT INTO EMP VALUES(1,'VAIBHAV',2000,10);


INSERT INTO EMP VALUES(2,'SAAD',3000,20);
INSERT INTO EMP VALUES(3,'MAYUR',4000,30);
INSERT INTO EMP VALUES(4,'AJAJ',5000,40);
SELECT * FROM EMP;

INSERT INTO DEPT VALUES(10,'COMP');


INSERT INTO DEPT VALUES(20,'MECH');
INSERT INTO DEPT VALUES(30,'CIVIL');

INSERT INTO DEPT VALUES(40,'CHEM');


SELECT *FROM DEPT;
SELECT EMPNAME FROM EMP WHERE EMPDEPTID =30 OR EMPDEPTID =40 OR EMPDEPTID=10 ;

SELECT * FROM EMP WHERE EMPSAL>=1000 AND EMPSAL<=3000;


SELECT COUNT(EMPID) AS "TOTAL EMPLOYEE" FROM EMP ;
SELECT EMPDEPTID ,AVG(EMPSAL) AS "AVERAGE SALARY" FROM EMP GROUP BY EMPDEPTID;

SELECT * FROM EMP ORDER BY EMPSAL ASC;

-----------------------------------------------------------------------------------
--------------

DBMS PRACTICAL 5

Demonstrate all types of JOIN on following schema


customer(customer_id,first_name)
orders(order_id,amount,customer_id);

CREATE TABLE CUSTOMER (


CUSTOMER_ID INT,
FIRST_NAME VARCHAR(10)
);
INSERT INTO CUSTOMER VALUES(1,'SAAD');
INSERT INTO CUSTOMER VALUES(2,'AJAJ');

INSERT INTO CUSTOMER VALUES(3,'PRASAD');

INSERT INTO CUSTOMER VALUES(4,'VAIBHAV');

SELECT * FROM CUSTOMER;


CREATE TABLE C_ORDER (
ORDER_ID INT,
AMOUNT INT,
CUSTOMER_ID INT
);

INSERT INTO C_ORDER VALUES(1,20000,10);


INSERT INTO C_ORDER VALUES(2,30000,20);

INSERT INTO C_ORDER VALUES(3,40000,30);


INSERT INTO C_ORDER VALUES(4,50000,40);

SELECT * FROM C_ORDER;

SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.FIRST_NAME, C_ORDER.ORDER_ID, C_ORDER.AMOUNT,


C_ORDER.CUSTOMER_ID
FROM CUSTOMER
RIGHT JOIN C_ORDER
ON CUSTOMER.CUSTOMER_ID = C_ORDER.CUSTOMER_ID;

-------------------------------------------------------------------------------

You might also like