Database Concepts Assignment: Data Manipulation and Structure

This assignment solution covers fundamental database concepts, including creating tables, defining relationships, and performing data manipulation using SQL queries. The solution demonstrates how to use INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN to join tables, and how to implement primary and foreign keys. It also provides examples of INSERT, DELETE, UPDATE, and SELECT statements for managing data within the database. Furthermore, the solution explores how to alter the database structure by changing constraints and modifying field properties. The provided code snippets illustrate how to create, modify, and query the database effectively. This assignment is a practical guide for understanding and applying core database principles, providing a comprehensive overview of database operations.

Running head: BASIC DATABASE CONCEPTS
Basic Database Concepts
Name of the Student:
Name of the University:
Author note:

1
BASIC DATABASE CONCEPTS
Table of Contents
Joining Tables and Relationships....................................................................................................3
INNER JOIN...............................................................................................................................3
LEFT JOIN..................................................................................................................................3
RIGHT JOIN...............................................................................................................................4
FULL OUTER JOIN...................................................................................................................4
FOREIGN KEY...........................................................................................................................4
Data Manipulation...........................................................................................................................4
INSERT.......................................................................................................................................4
DELETE......................................................................................................................................5
UPDATE......................................................................................................................................5
RETRIEVE DATA......................................................................................................................5
Change Database Structure..............................................................................................................6
Changing Constraints...................................................................................................................6
Adding/Altering Field Properties................................................................................................6

2
BASIC DATABASE CONCEPTS
The tables that are being referred to are as follows:
1. Table Student
CREATE TABLE Student (
id int PRIMARY KEY,
name varchar(255)
);
2. Table Fee
CREATE TABLE Fee (
id int NOT NULL PRIMARY KEY,
slipNo int NOT NULL,
FOREIGN KEY (id) REFERENCES Student(id)
);
3. Table ProcessedFee
CREATE TABLE ProcessedFee (
proID int NOT NULL,
slipNo int NOT NULL,
employeeID int NOT NULL,

3
BASIC DATABASE CONCEPTS
Comment VARCHAR(25),
SubDate DATE,
PRIMARY KEY (proID),
FOREIGN KEY (slipNo) REFERENCES Fee(slipNo)
);
Answers to questions:
Joining Tables and Relationships
INNER JOIN
SELECT Student.id, Fee.slipNo
FROM Student
INNER JOIN Fee ON Student.id = Fee.id;
LEFT JOIN
SELECT Student.id, Fee.slipNo
FROM Student
LEFT JOIN Fee ON Student.id = Fee.id
ORDER BY Student.id;

4
BASIC DATABASE CONCEPTS
RIGHT JOIN
SELECT ProcessedFee.employeeID, Fee.slipNo
FROM ProcessedFee
RIGHT JOIN Fee ON ProcessedFee.slipNo = Fee.slipNo;
FULL OUTER JOIN
SELECT Student.id, Fee.slipNo
FROM Student
FULL OUTER JOIN Fee ON Student.id = Fee.id;
FOREIGN KEY
CREATE TABLE processedFee (
slipNo int NOT NULL,
FOREIGN KEY (slipNo) REFERENCES Fee(slipNo)
);
Data Manipulation
INSERT
INSERT INTO processedFee (proID, slipNo, employeeID, comment)
VALUES (2, 105, 2410, ‘Paid’);

5
BASIC DATABASE CONCEPTS
DELETE
DELETE FROM processedFee
WHERE slipNo = 105;
UPDATE
UPDATE processedFee
SET comment = ‘Not Paid’
WHERE slipNo = 202;
RETRIEVE DATA
1. Retrieve all data from a table:
SELECT * FROM Student;
2. Retrieve data on meeting condition:
SELECT name FROM Student WHERE id > 2;
3. Retrieve data from one table when condition is matched in another table as well:
SELECT name FROM Student WHERE id = ( SELECT id FROM Fee WHERE slipNo = 11 );
4. Retrieve count of data:
SELECT COUNT(*) FROM Fee;
5. Retrieve data in order:
SELECT name FROM Student ORDER BY name;

6
BASIC DATABASE CONCEPTS
Change Database Structure
Changing Constraints
 Adding Key to an already created table:
ALTER TABLE Student
ADD PRIMARY KEY (id);
 Dropping key constraint:
ALTER TABLE Student
DROP PRIMARY KEY;
Adding/Altering Field Properties
 Creating a new table with all field properties (Data types, Size, Field display names, Data
entry Priority, Constraints):
CREATE TABLE ProcessedFee (
proID int NOT NULL,
slipNo int NOT NULL,
employeeID int NOT NULL,
Comment AS Status VARCHAR(25),
SubDate DATE,
PRIMARY KEY (proID),
FOREIGN KEY (slipNo) REFERENCES Fee(slipNo)
);

7
BASIC DATABASE CONCEPTS
 Altering Field Properties:
ALTER TABLE ProcessedFee
ALTER COLUMN SubDate DATETIME;