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 (