Scribd
Upload
5 views4 pages

Database Fundamentals: SQL Exercises

The document outlines a basic SQL exercise involving the creation and population of a STUDENT_RECORD table, along with tasks to write SQL queries for data retrieval and manipulation. It also includes instructions for creating a school management system database with specified tables and requirements for screenshots of SQL code and ER diagrams. The final submission should be a zip file containing the screenshots formatted with the student's ID.

Uploaded by

nyarkosamuel198
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views4 pages

Database Fundamentals: SQL Exercises

The document outlines a basic SQL exercise involving the creation and population of a STUDENT_RECORD table, along with tasks to write SQL queries for data retrieval and manipulation. It also includes instructions for creating a school management system database with specified tables and requirements for screenshots of SQL code and ER diagrams. The final submission should be a zip file containing the screenshots formatted with the student's ID.

Uploaded by

nyarkosamuel198
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DCIT 305

INTRODUCTION TO DATABASE FUNDAMENTALS


Basic SQL Exercise

Task 1

1. Create the STUDENT_RECORD table below using the appropriate datatypes.

Populate the table with the given data.


Ensure the first row contains your details, excluding the “fees” column.

2. Take following screenshots

The SQL code.


The ER diagram in Dbeaver.

Note: ID is the Primary Key.


ID first_name last_name programme fees

your_id first_name last_name programme 5123.50

Computer
10255212 David Otoo 3145.30
Science

Political
11342355 Ama Atta 24534.0
Science

Computer
27352373 Lydia Eshun 1989.2
Science

Computer
14686247 Daniel Boateng 2323.45
Science

Political
43425333 Kofi Eshun 4113.56
Science

Information
12424526 Naana Odum 3456.67
Technology

Political
24427880 Evelyn Arhin 6452.66
Science

Task 2

Write an SQL query to

1. Display the first_name, last_name, and programme of each student


2. List students in descending order by their fees
3. Find all students who are studying Political Science.
4. Select students whose fees are between 2,000 and 5,000 (inclusive).
5. Calculate the total fees paid by students in Political Science

Take following screenshots

The SQL code.


The results of the queries from Dbeaver.

Task 3

1. Create a database for a school management system. The database should have the
following tables:

a. Students:

• student_id (Primary Key)

• first_name

• last_name

• birth_date

• gender

b. Courses:

• course_id (Primary Key)

• course_name

• credit_hours

• instructor_id (Foreign Key referencing the Instructors table)

c. Instructors:

• instructor_id (Primary Key)

• first_name

• last_name

• hire_date

• department

d. Enrollments:

• enrollment_id (Primary Key)

• student_id (Foreign Key referencing the Students table)

• course_id (Foreign Key referencing the Courses table)


• grade

2. Populate two rows of the tables with dummy data.

3. Ensure the first row of the Students table contains your information: ID, first_name, and
last_name.

Take following screenshots

The SQL code.


The ER diagram in Dbeaver.

Submission

Zip the screenshots and submit the zip file with the following format
student_record_<your_ID>.zip

You might also like