Scribd Logo
Upload

Welcome to Scribd!

  • 34 views

    VL2023240101049 Ast02 3

    Uploaded by

    dsa dsa
    This document discusses different types of functions in SQL including substring, date, and group functions. It provides 35 examples of SQL queries using various functions on sample employee, department, and project tables. The examples demonstrate how to use functions to select, manipulate, and analyze data from the tables.

    Copyright:

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

    VL2023240101049 Ast02 3

    Uploaded by

    dsa dsa
    34 views3 pages
    This document discusses different types of functions in SQL including substring, date, and group functions. It provides 35 examples of SQL queries using various functions on sample employee, department, and project tables. The examples demonstrate how to use functions to select, manipulate, and analyze data from the tables.

    Original Title

    VL2023240101049_AST02_3

    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 discusses different types of functions in SQL including substring, date, and group functions. It provides 35 examples of SQL queries using various functions on sample employee, department, and project tables. The examples demonstrate how to use functions to select, manipulate, and analyze data from the tables.

    Copyright:

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

    VL2023240101049 Ast02 3

    Uploaded by

    dsa dsa
    This document discusses different types of functions in SQL including substring, date, and group functions. It provides 35 examples of SQL queries using various functions on sample employee, department, and project tables. The examples demonstrate how to use functions to select, manipulate, and analyze data from the tables.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    BCSE302P- DATABASE SYSTEMS

    FUNCTIONS IN SQL
    Date: 02.06.2023

    Aim: To understand different types of function in SQL


    Consider the following schema (used in EX2):
    Table Name: Employee

    Attribute Data Type


    First Name VARCHAR(15)
    Mid Name CHAR(2)
    Last Name VARCHAR(15)
    SSN Number CHAR(9)
    Birthday DATE
    Address VARCHAR(50)
    Sex CHAR(1)
    Salary NUMBER (7)
    Supervisor SSN CHAR(9)
    Department Number NUMBER (5)

    Table Name: Department

    Attribute Data Type


    Department Name Varchar(15)
    Department Number Number(5)
    ManagerSSN CHAR(9)
    ManageStartDate DATE

    Table Name: Project


    Attribute Data Type
    Project Name VARCHAR(15)
    Project Number NUMBER(5)
    Project Location VARCHAR(15)
    Department Number NUMBER(5)

    1
    Data For Employee Table
    Mini
    FName LName SSN BDate Address Sex Salary SuperSSN DepNo
    t
    Doug E Gilbert 123 09-JUN-1968 Chennai M 80000 NULL 1
    Joyce PAN 124 07-FEB-1973 Vellore F 70000 NULL 1
    Frankin T Wong 125 08-DEC-1972 Delhi M 40000 123 2
    Jennifer S Wallace 564 20-JUN-1983 Chennai F 43000 123 2
    John B Smith 678 09-JAN-1987 Madurai M 30000 124 1
    Ramesh K Narayan 234 15-SEP-1985 Bangalore M 38000 124 3
    Rahul S Ram 555 10-OCT-1973 Vellore M 50000 533 3

    Data For Department Table

    DName DepNo MgrSSN MgrStartDate


    Administration 2 564 03-Jan-2012
    Headquarter 1 678 16-Dec-2014
    Finance 3 234 18-May-2013
    IT 4 123 12-Jun-2015

    Data For Project Table

    PName PNumber Plocation DepNo


    ProjectA 3388 Delhi 1
    ProjectB 1945 Hyderabad 1
    ProjectC 6688 Chennai 2
    ProjectD 2423 Chennai 2
    ProjectE 7745 Bangalore 3

    1. Display the employee names whose bdate is on or before 1978.


    2. Display all the department names in upper case and lower case.
    3. Display the first four characters and last four of the department names using substring
    function.
    4. Display the substring of the Address (starting from 5th position to 11 th position) of all
    employees.
    5. Display the Mgrstartdate on adding three months to it.
    6. Display the age of all the employees rounded to two digits.
    7. Display the bdate of all employees in the format ‘DDthMonthYYYY’.

    2
    8. Find the last day and next day of the month in which each manager has joined.
    9. Print a substring from the string ‘Harini’.
    10. Replace the string ‘ni’ from ‘Harini’ by ‘sh’.
    11. Print the length of all the department names.
    12. Display the date after 10 months from current date.
    13. Display the next occurrence of Friday in this month.
    14. Display the project location padded with **** on left side.
    15. Display the day of next Saturday.
    16. Display the last date of Feb 2022
    17. Count the number of days present between today and Sunday.
    18. Display all the employees whose experience is greater than 14 yrs.

    Group Functions

    19. Find the total salary of all employees


    20. Display the min, max salary given to employees
    21. Display the avg salary of all employees
    22. Display the total number of employees
    23. How many different departments are there in the ‘employee’ table
    24. For each department display the minimum and maximum employee salaries
    25. Print the average annual salary.
    26. Count the number of employees over 30 age.
    27. Print the Department name and average salary of each department.
    28. Display the department name which contains more than 2 employees.
    29. Calculate the average salary of employees by department and age
    30. Count separately the number of employees in the finance and administration department.
    31. List out the employees based on their seniority.
    32. Display the total number of employees who are assigned to a department.
    33. Display only department numbers that are paying a total salary of more than 30000 to its
    employees
    34. Display all the employees working under the department 1.
    35. Display employees whose salary is between 5000 and 10000 in department no 1.
    3

    You might also like