Share
Explore

Professor Data's SQL Workbook IN3193

image.png

September 27 Class Plan


Do Data Reporting Analytics with SQL


Building the College Enrollment Database:

SQL Student Lab Workbook


Tables and Rows

We make a Database to data processing on the data in our Business by:
We make a SQL table for each ENTITY (thing) that exists in our Business Domain.
We think about “dimensions” describe the rowsets in our Table.
Once we have figured our (1) and (2): We are now set to use a Database Tool like DBBrowser for SQLite to make and populate with data our Tables.

Rowsets: Are collections of “dimensions” or fields that describe the details of the entity which we are modelling about the Entity (table).
DDL : Building the Database
CRUD : Putting Rowsets into your Tables

Predicate Joins

Relator Tables for many:many


Building the College Enrollment System:
image.png
image.png

We have now made a Data Model:

image.png
insert into students (studentid, slastname, sfirstname) values ('C119', 'Hammer', 'Mike'); insert into students (studentid, slastname, sfirstname) values ('C432', 'Helm', 'Matt'); insert into students (studentid, slastname, sfirstname) values ('C134', 'Bond', 'James'); insert into students (studentid, slastname, sfirstname) values ('C224', 'Reacher', 'Jack');
select * from students;

image.png

image.png
SQL to generate the Schedule by doing predicate joins between students - enrollments, and enrollments - classes
image.png

Sorting and Filtering Data in One Table


Structured Query Language (SQL) is the standard language for relational database management systems. Through SQL, you can manage, query, and manipulate data.
In this lab workbook, we will focus on key SQL operations: searching and sorting, filtering, and the use of aggregation functions with GROUP BY and ORDER BY clauses.

Lab Overview

Each section contains a brief overview followed by practical exercises.
This hands-on approach helps reinforce what you've learned.

Prerequisites

A basic understanding of relational databases and their structures.
Access to an SQL environment or platform (e.g., MySQL, PostgreSQL, SQLite, SQL Server).

1. Searching and Sorting Data

Overview

To retrieve data from a database, you use the SELECT statement. To sort the result set, the ORDER BY clause is used.

1.1 Basic Data Retrieval

Exercise 1.1.1:
Retrieve all columns from the students table.
sqlCopy code
SELECT *
FROM students;

1.2 Sorting Data

Exercise 1.2.1:
Retrieve all students from the students table and sort them by first_name in ascending order.

SELECT *
FROM students
ORDER BY first_name ASC;

Exercise 1.2.2:
Retrieve all students and sort them by birth_date in descending order.
sqlCopy code
SELECT *
FROM students
ORDER BY birth_date DESC;

2. Filtering Data

Overview

Use the WHERE clause to filter the records that fulfill a specified condition.

Exercise 2.1:

Retrieve all students with the first name "John".
sqlCopy code
SELECT *
FROM students
WHERE first_name = 'John';

Exercise 2.2:

Retrieve all students born after January 1, 2000.
sqlCopy code
SELECT *
FROM students
WHERE birth_date > '2000-01-01';

3. Aggregation Functions

Overview

Aggregation functions perform a calculation on a set of values and return a single value.

Exercise 3.1:

Count the number of students in the students table.
sqlCopy code
SELECT COUNT(*)
FROM students;

Exercise 3.2:

Find the average grade of students from the grades table.
sqlCopy code
SELECT AVG(grade)
FROM grades;

4. GROUP BY and Aggregation

Overview

The GROUP BY statement groups rows that have the same values in specified columns.

Exercise 4.1:

Count students in each course from the course_enrollments table.

5. Combining ORDER BY with Aggregations

Exercise 5.1:

Retrieve the total grades obtained by each student from the grades table and sort them in descending order.
sqlCopy code
SELECT student_id, SUM(grade) as total_grade
FROM grades
GROUP BY student_id
ORDER BY total_grade DESC;

Conclusion

By now, you should have a fundamental understanding of how to search, sort, and filter data in SQL. Additionally, the combination of aggregation functions with the GROUP BY and ORDER BY clauses provides powerful data analysis capabilities. Practice these exercises, and with time, you'll gain proficiency in SQL querying!
Remember, databases and tables used in the exercises above are fictional, so you'll need to have a suitable environment set up to execute these queries. Adjust the table names and columns as necessary based on your own setup.

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.