Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 3

Practical – 3

 To Perform various data manipulation commands, aggregate functions and sorting concept on all created tables.

(1)  List total deposit from deposit.

SELECT SUM(AMOUNT) FROM DEPOSIT

(2)  List total loan from karolbagh branch

SELECT SUM(AMOUNT) FROM  BRROW WHERE BNAME=’karolbagh’

(3)  Give maximum loan from branch vrce.

SELECT MAX(AMOUNT) From BRROW WHERE BNAME=’vrce’

(4)  Count total number of customers

SELECT COUNT(CNAME) FROM CUSTOMERS

(5)  Count total number of customer’s cities.

SELECT COUNT (CITY) FROM CUSTOMERS

(6)  Create table supplier from employee with all the columns.

CREATE TABLE SUPPLIER (sup_no, sup_name, sup_sal, sup_comm, dept_no )  AS SELECT emp_no, emp_name, emp_sal, emp_comm, dept_no FROM EMPLOYEE;

(7)  Create table sup1 from employee with first two columns.

CREATE TABLE SUP1 (sup_no, sup_name)  AS SELECT emp_no, emp_name FROM EMPLOYEE;

(8)  Create table sup2 from employee with no data

CREATE TABLE SUPPLIER (sup_no, sup_name, sup_sal, sup_comm, dept_no)  AS SELECT emp_no, emp_name, emp_sal, emp_comm, dept_no FROM EMPLOYEE WHERE EMP_NO=1;

(9)  Insert the data into sup2 from employee whose second character should be ‘n’ and string should be 5 characters long in employee name field.

INSERT INTO SUP2 SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE ‘_n___’

(10)                    Delete all the rows from sup1.

DELETE FROM SUP1

(11)                    Delete the detail of supplier whose sup_no is 103.

DELETE FROM SUP1 WHERE SUP_NO=103

(12)                    Rename the table sup2.

RENAME SUP2 TO SUPPLIER2;

(13)                    Destroy table sup1 with all the data.

 DROP TABLE SUP1;

(14)                    Update the value dept_no to 10 where second character of emp. name is ‘m’.

UPDATE EMPLOYEE SET DEPT_NO =10 WHERE EMP_NAME LIKE ‘_m%’

(15)                    Update the value of employee name whose employee number is 103.

UPDATE EMPLOYEE SET EMP_NAME=’ABC’ WHERE DEPT_NO=103;