Explore

Professor Data's SQL Workbook IN3193

⁠

September 27 Class Plan

Do Data Reporting Analytics with SQL

⁠

Building the College Enrollment Database:

⁠

https://share.mindmanager.com/#publish/ELalSU5YcqobSgUmxru24bq0PtLL4_fyxcjE0kPP⁠

⁠

Downloads - DB Browser for SQLite Free code signing provided by SignPath.io, certificate by SignPath Foundation. sqlitebrowser.org⁠

⁠

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:

⁠

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.

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.