Performance Queries

This page provides a hands-on walk-through of essential SQL operations using a sample student1 dataset. It covers everything from table creation to complex filtering, aggregations, and data modifications.

---

1. Database & Table Setup

First, we establish the database, define our table schema with appropriate data types and constraints, and populate it with initial mock data.

CREATE DATABASE college;
USE college;

CREATE TABLE student1 (
    rollno INT PRIMARY KEY,
    name VARCHAR (50),
    marks INT NOT NULL,
    grade VARCHAR(1),
    city VARCHAR(20)
);

INSERT INTO student1
(rollno, name, marks, grade, city)
VALUES
(101, "anil", 78, "C", "Pune"),
(102, "bhumika", 93, "A", "Mumbai"),
(103, "chetan", 85, "B", "Mumbai"),
(104, "dhruv", 96, "A", "Delhi"),
(105, "emanuel", 12, "F", "Delhi"),
(106, "farah", 82, "B", "Delhi");

2. Basic Data Retrieval (DQL)

These queries demonstrate how to select specific columns, fetch all records, and filter out duplicate entries.

SELECT name, marks FROM student1;
SELECT * FROM student1;
SELECT DISTINCT city FROM student1;

3. Filtering with Complex Conditions

Use the WHERE clause combined with comparison and logical operators to isolate specific data points.

-- Comparison Filters
SELECT * FROM student1 WHERE marks >= 80;
SELECT * FROM student1 WHERE marks + 10 > 100;
SELECT * FROM student1 WHERE marks != 96;

-- Logical AND / OR Operators
SELECT * FROM student1 WHERE marks >= 80 AND city = "mumbai";
SELECT * FROM student1 WHERE marks >= 80 OR city = "mumbai";

-- Range and List Filters
SELECT * FROM student1 WHERE marks BETWEEN 80 AND 90;
SELECT * FROM student1 WHERE city IN ("delhi", "gurgaon", "mumbai");
SELECT * FROM student1 WHERE city NOT IN ("delhi", "gurgaon", "mumbai");

4. Sorting and Limiting Results

Control the order of your output and limit the number of rows returned.

SELECT * FROM student1 LIMIT 2;

SELECT * FROM student1
ORDER BY marks DESC
LIMIT 3;

5. Aggregate Functions & Grouping

Perform calculations on multiple rows of data and group the results by specific categories.

• Basic Aggregations

  SELECT MIN(marks) FROM student1;
  SELECT MAX(marks) FROM student1;
  SELECT AVG(marks) FROM student1;
  SELECT COUNT(marks) FROM student1;
  

• Grouping and Sorting Aggregates

  SELECT city, count(marks)
  FROM student1 
  GROUP BY city;
  
  SELECT city, avg(marks)
  FROM student1 
  GROUP BY city
  ORDER BY avg(marks) DESC;
  
  SELECT grade, count(rollno)
  FROM student1
  GROUP BY grade
  ORDER BY grade;
  

• Filtering Groups with HAVING

  SELECT city, count(rollno)
  FROM student1
  GROUP BY city
  HAVING max(marks) > 90;
  
  SELECT city
  FROM student1
  WHERE grade = "A"
  GROUP BY city
  HAVING max(marks) > 90
  ORDER BY grade;
  

6. Modifying Data (DML)

These operations update existing records or remove them completely from the table.

• Tip: Toggle safe updates off to run bulk modifications without a unique ID filter key.

SET sql_safe_updates = 0;

-- Value Modifications
UPDATE student1 SET grade = "O" WHERE grade = "A";
UPDATE student1 SET marks = 82 WHERE rollno = 105;

-- Conditional Scaling
UPDATE student1 SET grade = "O" WHERE marks BETWEEN 90 AND 100;
UPDATE student1 SET grade = "A" WHERE marks BETWEEN 80 AND 90;
UPDATE student1 SET grade = "B" WHERE marks BETWEEN 70 AND 80;

-- Verification
SELECT * FROM student1;

-- Arithmetic Transformations
UPDATE student1 SET marks = marks + 1;
UPDATE student1 SET marks = 12 WHERE rollno = 105;

-- Data Purging
DELETE FROM student1 WHERE marks < 33;
DELETE FROM student1;