Scribd Logo
Upload

Welcome to Scribd!

  • 26 views

    DBMS Exercise 1.2

    Uploaded by

    Manish Kumar
    This document contains exercises from a Database Management Systems lab class. It includes: 1) Questions about writing SQL queries for a banking database including inserting data. 2) The creation of an EMP table with sample data and questions about retrieving salary data from the table. 3) Questions about finding the minimum, maximum, and average salaries as well as the difference between the minimum and maximum. It also asks to display names and salaries above the minimum for jobs starting with M. 4) A final question asks to display the total salary spent for each job category.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DBMS Exercise 1.2

    Uploaded by

    Manish Kumar
    26 views10 pages
    This document contains exercises from a Database Management Systems lab class. It includes: 1) Questions about writing SQL queries for a banking database including inserting data. 2) The creation of an EMP table with sample data and questions about retrieving salary data from the table. 3) Questions about finding the minimum, maximum, and average salaries as well as the difference between the minimum and maximum. It also asks to display names and salaries above the minimum for jobs starting with M. 4) A final question asks to display the total salary spent for each job category.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains exercises from a Database Management Systems lab class. It includes: 1) Questions about writing SQL queries for a banking database including inserting data. 2) The creation of an EMP table with sample data and questions about retrieving salary data from the table. 3) Questions about finding the minimum, maximum, and average salaries as well as the difference between the minimum and maximum. It also asks to display names and salaries above the minimum for jobs starting with M. 4) A final question asks to display the total salary spent for each job category.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    26 views10 pages

    DBMS Exercise 1.2

    Uploaded by

    Manish Kumar
    This document contains exercises from a Database Management Systems lab class. It includes: 1) Questions about writing SQL queries for a banking database including inserting data. 2) The creation of an EMP table with sample data and questions about retrieving salary data from the table. 3) Questions about finding the minimum, maximum, and average salaries as well as the difference between the minimum and maximum. It also asks to display names and salaries above the minimum for jobs starting with M. 4) A final question asks to display the total salary spent for each job category.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 10

    EXERCISE 1.

    2
    STUDENT’S NAME – MANISH KUMAR
    STUDENT’S UID – 20BCS7420
    CLASS AND GROUP – 20CSE33_A
    SEMESTER – 3rd

    Question 1
    Consider the database for a banking enterprise. Write the queries for the below
    questions.
    Insert at least 5 tuples in each table

    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215


    MANAGEMENT SYSTEM LAB
    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215
    MANAGEMENT SYSTEM LAB
    a. Display the branch details

    b. List the customers of ‘Mumbai’ city

    c. List the male customers of ‘Kolkata’ city

    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215


    MANAGEMENT SYSTEM LAB
    d. List the state having more than one branch.

    e. List the deposit schemes provided by the bank to the customers

    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215


    MANAGEMENT SYSTEM LAB
    Question 2
    Consider a relation Employee with following attributes:
    Name Null Type
    -------------------------------- ----------------------- -------------------------
    EMPNO NOT NULL NUMBER(4)
    ENAME VARCHAR2(10)
    JOB VARCHAR2(9)
    MGR NUMBER(4)
    HIREDATE DATE
    SAL NUMBER(7,2)
    COMM NUMBER(7,2)
    DEPTNO NUMBER(3)
    AGE NUMBER(3)
    ESAL NUMBER(10)

    ANSWER

    SQL>CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,


    ENAME VARCHAR2(10),
    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215
    MANAGEMENT SYSTEM LAB
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(3),
    AGE NUMBER(3),
    ESAL NUMBER);

    SQL > INSERT INTO EMP VALUES('10','HARSH','ANALYS','49', TO_DATE('13-9-


    1995','DD-MM-YYYY'),
    '60000','789','20','22','555');
    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215
    MANAGEMENT SYSTEM LAB
    INSERT INTO EMP VALUES('11','RAHUL','ANALYST','50', TO_DATE('5-1-1988','DD-MM-
    YYYY'),
    '60000','778','20','28','565');

    INSERT INTO EMP VALUES('12','RAM','CLERK','51', TO_DATE('22-4-2000','DD-MM-


    YYYY'),
    '44000','775','22','23','753');

    INSERT INTO EMP VALUES('13','SHAM','CLERK','52', TO_DATE('7-2-2000','DD-MM-


    YYYY'),
    '44000','','22','33','369');

    INSERT INTO EMP VALUES('14','ROHAN','SALESMAN','53', TO_DATE('29-12-1999','DD-


    MM-YYYY'),
    '35000','780','26','30','956');

    a. List minimum , maximum , average salaries of employee.


    SQL>
    SELECT MIN(SAL) FROM EMP;
    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215
    MANAGEMENT SYSTEM LAB
    SELECT MAX(SAL) FROM EMP;
    SELECT AVG(SAL) FROM EMP;

    b. What is the difference between maximum and minimum salaries of employees


    in the organization?

    c. Display all employee names and salary whose salary is greater than minimum
    salary of the company and job title starts with ‘M’.

    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215


    MANAGEMENT SYSTEM LAB
    d. Display total salary spent for each job category.

    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215


    MANAGEMENT SYSTEM LAB
    SUBJECT NAME- DATABASE SUBJECT CODE- 20CSP-215
    MANAGEMENT SYSTEM LAB

    You might also like