Scribd Logo
Upload

Welcome to Scribd!

  • 12 views

    DS Lab # 06

    Uploaded by

    ashbalfaheem
    1. The document provides instructions and examples for a database systems lab experiment on using aggregate functions in SQL queries. It includes sample tables of order and customer data. 2. Students are asked to write 10 SQL queries on the sample tables to calculate totals, averages, counts, maximums, minimums, products and limit results using aggregate functions like SUM, AVG, COUNT, MAX, MIN and LIMIT. 3. The objective is to practice using aggregate functions to operate on groups of rows and return single values from the tables. This helps students learn how to analyze and summarize data using common SQL functions.

    Copyright:

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

    DS Lab # 06

    Uploaded by

    ashbalfaheem
    12 views3 pages
    1. The document provides instructions and examples for a database systems lab experiment on using aggregate functions in SQL queries. It includes sample tables of order and customer data. 2. Students are asked to write 10 SQL queries on the sample tables to calculate totals, averages, counts, maximums, minimums, products and limit results using aggregate functions like SUM, AVG, COUNT, MAX, MIN and LIMIT. 3. The objective is to practice using aggregate functions to operate on groups of rows and return single values from the tables. This helps students learn how to analyze and summarize data using common SQL functions.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    1. The document provides instructions and examples for a database systems lab experiment on using aggregate functions in SQL queries. It includes sample tables of order and customer data. 2. Students are asked to write 10 SQL queries on the sample tables to calculate totals, averages, counts, maximums, minimums, products and limit results using aggregate functions like SUM, AVG, COUNT, MAX, MIN and LIMIT. 3. The objective is to practice using aggregate functions to operate on groups of rows and return single values from the tables. This helps students learn how to analyze and summarize data using common SQL functions.

    Copyright:

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

    DS Lab # 06

    Uploaded by

    ashbalfaheem
    1. The document provides instructions and examples for a database systems lab experiment on using aggregate functions in SQL queries. It includes sample tables of order and customer data. 2. Students are asked to write 10 SQL queries on the sample tables to calculate totals, averages, counts, maximums, minimums, products and limit results using aggregate functions like SUM, AVG, COUNT, MAX, MIN and LIMIT. 3. The objective is to practice using aggregate functions to operate on groups of rows and return single values from the tables. This helps students learn how to analyze and summarize data using common SQL functions.

    Copyright:

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

    DEPARTMENT OF TELECOMMUNICATION ENGINEERING

    BACHELOR OF SCIENCE IN CYBER SECURITY


    MEHRAN UNIVERSITY OF ENGINEERING & TECHNOLOGY, JAMSHORO
    DATABASE SYSTEMS
    (2nd SEMESTER, 1st Year) LAB EXPERIMENT # 6

    Name: _________________________________________________________ Roll No: ______________

    Date of Conduct: ___________ Submission Date: _________ Signature of the Lab Tutor: ____________
    ABILITY TO CONDUCT
    LAB PERFORMANCE INDICATOR SUBJECT KNOWLEDGE DATA ANALYSIS AND INTERPRETATION
    EXPERIMENT

    SCORE

    Objective: To use Aggregate functions in SQL queries


    Tools: ORACLE/MySQL/Workbench Duration: 3 Hours

    Aggregate Functions:
    Aggregate functions are built-in SQL functions that operate on groups of rows and return one value
    for the entire group.

    Types:
    • COUNT(): This function returns the number of rows in the table that satisfies the condition
    specified in the WHERE condition. If the WHERE condition is not specified, then the
    query returns the total number of rows in the table.
    • MAX(): This function is used to get the maximum value from a column.
    • MIN(): This function is used to get the minimum value from a column.
    • AVG(): This function is used to get the average value of a numeric column.
    • SUM(): This function is used to get the sum of a numeric column
    • ROUND(): This function rounds a number to a specified number of decimal places.

    Example:
    SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
    FROM employees;

    1|Page
    DEPARTMENT OF TELECOMMUNICATION ENGINEERING
    BACHELOR OF SCIENCE IN CYBER SECURITY
    MEHRAN UNIVERSITY OF ENGINEERING & TECHNOLOGY, JAMSHORO
    DATABASE SYSTEMS
    (2nd SEMESTER, 1st Year) LAB EXPERIMENT # 6

    Exercise
    Consider the following table Order table.

    ord_no purch_amt ord_date customer_id salesman_id


    ---------- ---------- ---------- ----------- -----------
    70001 150.5 2020-10-05 3005 5002
    70009 270.65 2023-09-10 3001 5005
    70002 65.26 2024-02-02 3002 5001
    70004 110.5 2015-08-17 3009 5003
    70007 110.5 2019-09-10 3005 5004
    70005 2400.6 2022-07-27 3007 5003
    70008 5760 2023-09-10 3002 5001

    1. From the following table, write a SQL query to calculate total purchase amount of all
    orders. Return total purchase amount.
    2. From the following table, write a SQL query to calculate the average purchase amount of
    all orders. Return average purchase amount.
    3. From the following table, write a SQL query that counts the number of unique salesman_id.
    Return number of salesman_id.
    Note: Use DISTINCT() function to find the unique values.
    4. From the following table, write a SQL query to find the maximum purchase amount.
    5. From the following table, write a SQL query to find the minimum purchase amount.

    Consider the following Customer Table.

    customer_id | cust_name | city | grade | salesman_id


    -------------+----------------+------------+-------+------------
    -
    3002 | Nick Rimando | New York | 100 | 5001
    3007 | Brad Davis | New York | 200 | 5001
    3005 | Graham Zusi | California | 200 | 5002
    3008 | Julian Green | London | 300 | 5002
    3004 | Fabian Johnson | Paris | 300 | 5006
    3009 | Geoff Cameron | Berlin | 100 | 5003
    3003 | Jozy Altidor | Moscow | 200 | 5007
    3001 | Brad Guzan | London | 300 | 5005

    2|Page
    DEPARTMENT OF TELECOMMUNICATION ENGINEERING
    BACHELOR OF SCIENCE IN CYBER SECURITY
    MEHRAN UNIVERSITY OF ENGINEERING & TECHNOLOGY, JAMSHORO
    DATABASE SYSTEMS
    (2nd SEMESTER, 1st Year) LAB EXPERIMENT # 6
    6. From the following table, write a SQL query to find the highest grade of the customers in
    each city. Return city and maximum grade.
    7. From the following table, write a SQL query to multiply grade with salesman_id. Return
    the product of grade and salesman_id.
    Note: Use (*) asterik to multiply the columns.
    8. From the following table, write a SQL query to find the maximum and minimum grade of
    Customer_id with its Name and City.
    9. From the following table, write a SQL query to display only first 5 customer_id.
    Note: Use LIMIT function().
    10. Summarize the whole lab in your own words.

    3|Page

    You might also like