Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    S_Function_A_Function_SQL_Assignment_4

    Uploaded by

    Alieu Keita

    Copyright:

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

    S_Function_A_Function_SQL_Assignment_4

    Uploaded by

    Alieu Keita
    4 views4 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    S_Function_A_Function_SQL_Assignment_4

    Uploaded by

    Alieu Keita

    Copyright:

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

    Scalar Functions and Aggregate Functions in SQL

    1. Scalar Functions
    • Definition: Scalar functions return a single value based on the input value(s). They operate
    on individual values and return one result per row.
    • Key Characteristics:
    o Work on each row independently.
    o Often used in the SELECT statement.
    Syntax:
    SELECT scalar_function(column_name)
    FROM table_name
    [WHERE condition];

    Common Scalar Functions and Examples:


    Function Description Example
    UPPER()/LOWER() Converts text to SELECT UPPER(emp_name)
    uppercase/lowercase. FROM Employees;

    LENGTH() Returns the length of a string. SELECT


    LENGTH(emp_name) FROM
    Employees;

    ROUND() Rounds a number to a SELECT ROUND(salary, 2)


    specified number of decimal FROM Employees;
    places.

    CONCAT() Concatenates two or more SELECT


    strings. CONCAT(emp_name, ' - ',
    dept_id) FROM Employees;

    SUBSTRING() Extracts a part of a string. SELECT


    SUBSTRING(emp_name, 1,
    3) FROM Employees;

    NOW() Returns the current date and SELECT NOW();


    time.
    Some Example:
    I. Convert to Uppercase:

    SELECT UPPER(emp_name) AS uppercase_name


    FROM Employees;

    II. Extract Substring:

    SELECT SUBSTRING(emp_name, 1, 3) AS short_name


    FROM Employees;

    2. Aggregate Functions
    • Definition: Aggregate functions perform calculations on a set of rows and return a single
    value. They are commonly used with GROUP BY to summarize data.
    • Key Characteristics:
    o Work on groups of rows.
    o Often used in SELECT statements with group-based operations.

    Syntax:
    SELECT aggregate_function(column_name) FROM table_name [WHERE condition] [GROUP
    BY column_name];

    Common Aggregate Functions and Examples:


    Function Description Example
    COUNT() Counts the number of rows or SELECT COUNT(emp_id)
    non-NULL values. FROM Employees;

    SUM() Calculates the total of numeric SELECT SUM(salary) FROM


    values. Employees;

    AVG() Calculates the average of SELECT AVG(salary) FROM


    numeric values. Employees;

    MAX() Returns the maximum value in SELECT MAX(salary)


    a column. FROM Employees;

    MIN() Returns the minimum value in SELECT MIN(salary) FROM


    a column. Employees;
    Some Example:
    i. Count Employees:

    SELECT COUNT(emp_id) AS total_employees


    FROM Employees;

    ii. Find Maximum Salary:

    SELECT MAX(salary) AS highest_salary


    FROM Employees;

    iii. Group by Department and Sum Salaries:

    SELECT dept_id, SUM(salary) AS total_salary


    FROM Employees
    GROUP BY dept_id;

    Assignment No. – 4

    Q1. Create the following table “Employees”


    Column Name Datatype Size

    emp_id (PRIMARY KEY) NUMBER 10


    emp_name VARCHAR2 20
    dept_id VARCHAR2 10
    salary DECIMAL 10,2
    joining_date DATE
    Q2. Insert the following data in the Employees table
    emp_id emp_name dept_id salary joining_date
    1 Alice 10 5000 2020-01-10
    2 Bob 20 6000 2019-03-15
    3 Charlie 30 7000 2021-07-01
    4 David NULL 4000 2022-05-20
    5 Emma 10 8000 2018-08-22
    6 Frank 20 5500 2020-09-30
    7 Grace NULL 3000 2023-01-01

    Q3. Create the following table “Departments”


    Column Name Datatype Size
    dept_id (PRIMARY KEY) NUMBER 10
    dept_name VARCHAR2 20

    Q4. Insert the following data in the Departments table


    emp_id emp_name
    10 HR
    20 IT
    30 Finance
    40 Marketing

    Q5. Based on above two tables answer the following Questionaries: -


    i. Display all employee names in uppercase along with their salaries.
    ii. Retrieve the total number of employees.
    iii. Calculate the average salary of all employees.
    iv. Count the number of employees in each department.
    v. Calculate the total salary paid in each department.
    vi. Display the length of each employee's name.
    vii. Display the first three characters of each employee's name.
    viii. List all employees who joined after January 1, 2020.
    ix. Find the department name and total salary paid in that department using LEFT
    JOIN.
    x. Calculate the total salary paid to employees, rounded to two decimal places.

    You might also like