A Comprehensive Guide to Practice SQL with Sample Tables
When learning SQL, working with realistic data sets is crucial for understanding how different statements interact with each other. This guide introduces three sample tables that you can use to practice SQL queries. By following the steps outlined below, you’ll be able to create and interact with these tables in your database.
Why Use Sample Tables for SQL Practice?
Sample tables provide a hands-on way to learn SQL, offering:
- Practical Learning: Apply SQL concepts in real-world scenarios.
- Enhanced Understanding: Understand relationships between tables through practical queries.
- Confidence Building: Develop skills that can be directly applied in professional settings.
Overview of the Tables
The provided SQL script creates three tables with the following relationships:
blog_students: Contains student information (e.g., student number, surname, forename).blog_modules: Contains information about modules (e.g., module code, module name).blog_marks: Captures the marks students achieve in specific modules.
Table Relationships
The relationships between the tables are as follows:
- A student can enroll in one or more modules.
- A module can be taken by one or more students.
- Each student-module combination must have a mark assigned.
This setup reflects a typical many-to-many relationship scenario, ideal for practicing SQL joins, constraints, and more.
Step-by-Step Guide to Creating the Tables
To create these tables, follow the steps below.
1. Dropping Existing Tables (if necessary)
Ensure the tables don’t already exist in your database.
2. Creating the Tables
- Student Table (
blog_students)
Contains primary details of students.sql CREATE TABLE blog_students ( stud_no NUMBER PRIMARY KEY, stud_surname VARCHAR2(200 CHAR) NOT NULL, stud_forename VARCHAR2(200 CHAR) NOT NULL );- Module Table (
blog_modules)
Contains primary details of modules.sql CREATE TABLE blog_modules ( modu_code VARCHAR2(200 CHAR) PRIMARY KEY, modu_name VARCHAR2(200 CHAR) NOT NULL );- Marks Table (
blog_marks)
Stores marks for each student-module combination.sql CREATE TABLE blog_marks ( mark_stud_no NUMBER NOT NULL, mark_modu_code VARCHAR2(200 CHAR) NOT NULL, mark_mark NUMBER NOT NULL, CONSTRAINT stud_modu_pk PRIMARY KEY (mark_stud_no, mark_modu_code) );
3. Inserting Data into the Tables
Populate the tables with the following sample data:
- Student Data
INSERT INTO blog_students VALUES (20060101, 'Dickens', 'Charles');INSERT INTO blog_students VALUES (20060102, 'ApGwilym', 'Dafydd');INSERT INTO blog_students VALUES (20060103, 'Zola', 'Emile');INSERT INTO blog_students VALUES (20060104, 'Mann', 'Thomas');INSERT INTO blog_students VALUES (20060105, 'Stevenson', 'Robert');- Module Data
INSERT INTO blog_modules VALUES ('CM0001', 'Databases'); INSERT INTO blog_modules VALUES ('CM0002', 'Programming Languages');INSERT INTO blog_modules VALUES ('CM0003', 'Operating Systems'); INSERT INTO blog_modules VALUES ('CM0004', 'Graphics');- Marks Data
INSERT INTO blog_marks VALUES (20060101, 'CM0001', 80);INSERT INTO blog_marks VALUES (20060101, 'CM0002', 65);INSERT INTO blog_marks VALUES (20060101, 'CM0003', 50);INSERT INTO blog_marks VALUES (20060102, 'CM0001', 75);INSERT INTO blog_marks VALUES (20060102, 'CM0003', 45);INSERT INTO blog_marks VALUES (20060102, 'CM0004', 70);INSERT INTO blog_marks VALUES (20060103, 'CM0001', 60);INSERT INTO blog_marks VALUES (20060103, 'CM0002', 75);INSERT INTO blog_marks VALUES (20060103, 'CM0004', 60);INSERT INTO blog_marks VALUES (20060104, 'CM0001', 55);INSERT INTO blog_marks VALUES (20060104, 'CM0002', 40);INSERT INTO blog_marks VALUES (20060104, 'CM0003', 45);INSERT INTO blog_marks VALUES (20060105, 'CM0001', 55);INSERT INTO blog_marks VALUES (20060105, 'CM0002', 50);INSERT INTO blog_marks VALUES (20060105, 'CM0004', 65);
4. Commit the Changes
Using the Sample Tables
Once the tables are created and populated, you can use them to practice:
- SQL Joins: Explore relationships between tables (e.g., retrieve student names and their module marks).
- Data Constraints: Test primary key and foreign key constraints.
- Query Optimization: Experiment with filtering and aggregating data.
Conclusion
These sample tables provide a robust foundation for practicing SQL statements. Whether you're learning basic queries or advanced concepts like joins and constraints, this setup is ideal for gaining hands-on experience. Start creating and querying your database today to elevate your SQL skills!
{fullWidth}