Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 68 views

    A2119680103 23509 25 2020 Ca2int322 PDF

    Uploaded by

    rahil
    This document contains instructions for a computing systems and technologies assignment for Lovely Professional University. It provides two sets of questions for students with odd and even roll numbers. Set A contains questions about SQL clauses like GROUP BY and HAVING and creating and inserting data into a table. Set B contains similar SQL questions about clauses like ORDER BY and DML statements, and creating and populating a table. Both sets ask students to write SQL queries to retrieve information from the EMP table based on various conditions. The document outlines expectations for submitting the assignment and policies on plagiarism.

    Copyright:

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

    A2119680103 23509 25 2020 Ca2int322 PDF

    Uploaded by

    rahil
    68 views4 pages
    This document contains instructions for a computing systems and technologies assignment for Lovely Professional University. It provides two sets of questions for students with odd and even roll numbers. Set A contains questions about SQL clauses like GROUP BY and HAVING and creating and inserting data into a table. Set B contains similar SQL questions about clauses like ORDER BY and DML statements, and creating and populating a table. Both sets ask students to write SQL queries to retrieve information from the EMP table based on various conditions. The document outlines expectations for submitting the assignment and policies on plagiarism.

    Original Description:

    Original Title

    A2119680103_23509_25_2020_CA2INT322.pdf

    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 instructions for a computing systems and technologies assignment for Lovely Professional University. It provides two sets of questions for students with odd and even roll numbers. Set A contains questions about SQL clauses like GROUP BY and HAVING and creating and inserting data into a table. Set B contains similar SQL questions about clauses like ORDER BY and DML statements, and creating and populating a table. Both sets ask students to write SQL queries to retrieve information from the EMP table based on various conditions. The document outlines expectations for submitting the assignment and policies on plagiarism.

    Copyright:

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

    A2119680103 23509 25 2020 Ca2int322 PDF

    Uploaded by

    rahil
    This document contains instructions for a computing systems and technologies assignment for Lovely Professional University. It provides two sets of questions for students with odd and even roll numbers. Set A contains questions about SQL clauses like GROUP BY and HAVING and creating and inserting data into a table. Set B contains similar SQL questions about clauses like ORDER BY and DML statements, and creating and populating a table. Both sets ask students to write SQL queries to retrieve information from the EMP table based on various conditions. The document outlines expectations for submitting the assignment and policies on plagiarism.

    Copyright:

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

    Lovely Professional University

    School of Civil Engineering


    Department of Computer Science & Engineering
    Computing System and Technologies(INT 322)

    CA-2 SET A( ODD Roll Nos)


    1. What is the use of GROUP BY clause ? Explain with Examples. (2marks)
    2. Explain the use of HAVING clause ? Explain with Examples. ( 2 marks)
    3. A) Write the oracle query to create the table EMP with the attribute, EMPNO,
    ENAME,JOB MGR, HIREDATE, SAL, COMM, DEPTNO . ( 2 marks)
    B) Write the sql queries to Insert the data as per the table created below(4 Marks)
    EMP Table
    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    7369 SMITH CLERK 7902 17-DEC-80 800 20
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    7566 JONES MANAGER 7839 02-APR-81 2975 20
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    7788 SCOTT ANALYST 7566 19-APR-87 3000 20
    7839 KING PRESIDENT 17-NOV-81 5000 10
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    7876 ADAMS CLERK 7788 23-MAY-87 1100 20
    7900 JAMES CLERK 7698 03-DEC-81 950 30
    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    7934 MILLER CLERK 7782 23-JAN-82 1300 10

    4. Write the SQL statement for the following queries (20 Marks)

    (a) List hire date's of each employee in the format `DD/MM/yyyy,


    (b) List the number of months between the system's date and date of joining of the employee.
    (c) List the current system time (Hint : SQL> SELECT TO_CHAR (SYSDATE , ' HH :MI :
    SS' ) FROM DUAL;)
    (d) List the maximum, minimum and average of Salary.
    (f) List the sum of SAL and COMM of each of the employee.
    (g) Count the number of JOB titles in EMP tables.
    (h) Calculate the total number of employees in each department except DEPTN0=30.
    (i) Find all the department number where total number of employees equal to 4
    (j) List all the employees who joined in 1981, department wise.
    (k) List all the employees who joined in 1987.( HINT : LIKE '%87)
    (q) List employees who are not clerks. List all the employees who do not have a manager.
    (r) List all employees who are not working in any department.
    (s) List all employees who have two L's in their name.

    Note
    • Assignment Should be Submitted online Latest By Monday (30-03-2020)
    • Assignment Solution must be hand written will upload as pdf file. Each page of the
    assignment contains Name, Rollno, GroupNo and registration of the student.
    • Copied assignments straightway get 0 mark .
    Lovely Professional University
    School of Civil Engineering
    Department of Computer Science & Engineering
    Computing System and Technologies(INT 322)

    CA-2 SET B ( EVEN Roll Nos)


    1. What is the use of ORDER BY clause ? Explain with Examples. (2marks)
    2. Explain DML Statements ? Explain with Examples. ( 4 marks)
    3. A) Write the oracle query to create the table EMP with the attribute, EMPNO,
    ENAME,JOB MGR, HIREDATE, SAL, COMM, DEPTNO . ( 2 marks)
    B) Write the sql queries to Insert the data as per the table created below(4 Marks)
    EMP Table
    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    7369 SMITH CLERK 7902 17-DEC-80 800 20
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    7566 JONES MANAGER 7839 02-APR-81 2975 20
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    7788 SCOTT ANALYST 7566 19-APR-87 3000 20
    7839 KING PRESIDENT 17-NOV-81 5000 10
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    7876 ADAMS CLERK 7788 23-MAY-87 1100 20
    7900 JAMES CLERK 7698 03-DEC-81 950 30
    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    7934 MILLER CLERK 7782 23-JAN-82 1300 10

    4. Write the SQL statement for the following queries (18 Marks)
    (a) List the ENAME of employee whose salary is greater than 2000 from EMP
    (b) List the ENAME, JOB, SAL of an Employee whose DEPTNO=10 and SAL>2000 from EMP table.
    (c) List all the columns of EMP table whose department number is other than 10
    (d) List all employees of EMP table whose ENAME start with 'A' and end with ‘N’
    (e) List all employees whose JOB post is 'Clerk' or 'Manager'.
    (f) List all the employees whose SAL lies between 1250 and 3000.
    (g) List all employees who is getting a commission. ( HINT : COMMIS NOT NULL)
    (h) List all the employee's according to the date of joining.
    (i) List all the names and EMPNO's as `<EMPNO> name is <ENAME>'.
    Note
    • Assignment Should be Submitted online Latest By Monday (30-03-2020)
    • Assignment Solution must be hand written will upload as pdf file. Each page of the
    assignment contains Name, Rollno, GroupNo and registration of the student.
    Copied assignments straightway get 0 mark

    You might also like