Database Concepts and Operations

This assignment delves into fundamental database concepts, covering topics such as joining tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. It explores the use of FOREIGN KEY constraints to establish relationships between tables. The assignment also examines data manipulation operations like INSERT, DELETE, UPDATE, and RETRIEVE DATA with various query examples. Finally, it discusses changing database structure by modifying constraints and altering field properties.

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;