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
Relator Tables for many:many
Building the College Enrollment System:
We have now made a Data Model:
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;
SQL to generate the Schedule by doing predicate joins between students - enrollments, and enrollments - classes
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.
Each section contains a brief overview followed by practical exercises.
This hands-on approach helps reinforce what you've learned.
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
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
Retrieve all columns from the students table.
1.2 Sorting Data
Retrieve all students from the students table and sort them by first_name in ascending order.
ORDER BY first_name ASC;
Retrieve all students and sort them by birth_date in descending order.
ORDER BY birth_date DESC;
2. Filtering Data
Use the WHERE clause to filter the records that fulfill a specified condition.
Retrieve all students with the first name "John".
WHERE first_name = 'John';
Retrieve all students born after January 1, 2000.
WHERE birth_date > '2000-01-01';
3. Aggregation Functions
Aggregation functions perform a calculation on a set of values and return a single value.
Count the number of students in the students table.
Find the average grade of students from the grades table.
4. GROUP BY and Aggregation
The GROUP BY statement groups rows that have the same values in specified columns.
Count students in each course from the course_enrollments table.
5. Combining ORDER BY with Aggregations
Retrieve the total grades obtained by each student from the grades table and sort them in descending order.
SELECT student_id, SUM(grade) as total_grade
GROUP BY student_id
ORDER BY total_grade DESC;
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.