Simple Queries and Aggregate Functions in MySQL

DBMS Practical: Simple Queries and Aggregate Functions in MySQL (FYBSc IT)

Learn how to execute simple SQL queries and aggregate function queries in MySQL with examples. This is a common FYBSc IT DBMS practical for students.

Aim

To perform simple SQL queries and queries using aggregate functions in MySQL.

Theory

In SQL, queries are used to retrieve and manipulate data stored in relational databases. Queries can be:

  • Simple Queries: Basic queries used to fetch data from tables using SELECT, WHERE, etc.
  • Aggregate Queries: Queries that perform calculations on data using aggregate functions like COUNT(), AVG(), MAX(), MIN(), SUM().

Steps

  1. Create or select a database using USE command.
  2. Create a table student with columns (student_id, name, course, marks, city).
  3. Insert records into the student table.
  4. Run simple queries to fetch specific data.
  5. Apply aggregate functions to summarize data.

Queries

1. Create and Use Database

USE fyit;

2. Insert Records

INSERT INTO student (student_id, name, course, marks, city) VALUES 
(2, 'Sara', 'IT', 75, 'Pune'),
(3, 'John', 'Computer Science', 92, 'Delhi'),
(4, 'Priya', 'IT', 68, 'Mumbai'),
(5, 'Rahul', 'Computer Science', 55, 'Chennai');

3. Simple Queries

-- Display all records
SELECT * FROM student;

-- Display only name and marks
SELECT name, marks FROM student;

-- Display students from Mumbai
SELECT * FROM student WHERE city='Mumbai';

-- Display students with marks greater than 70
SELECT name, marks FROM student WHERE marks>70;

4. Queries with Aggregate Functions

-- Count total students
SELECT COUNT(*) AS total_student FROM student;

-- Average marks of students
SELECT AVG(marks) AS average_marks FROM student;

-- Highest marks
SELECT MAX(marks) AS highest_marks FROM student;

-- Lowest marks
SELECT MIN(marks) AS lowest_marks FROM student;

-- Average marks course-wise
SELECT course, AVG(marks) AS avg_marks 
FROM student 
GROUP BY course;

Explanation

  • SELECT * retrieves all columns from the table.
  • WHERE is used to filter rows based on conditions.
  • COUNT() counts the number of rows.
  • AVG() calculates average value of a column.
  • MAX() finds the highest value, while MIN() finds the lowest.
  • GROUP BY groups rows to apply aggregate functions course-wise.

Conclusion

In this practical, we learned how to execute simple queries and queries using aggregate functions in MySQL. These queries are essential for analyzing and retrieving useful information from a database.

FYBSc IT DBMS Practical – Simple Queries and Aggregate Functions in MySQL
🔗