SQL Fundamentals Course Documentation
Table of Contents
- Oracle Cloud Account Setup
- Provisioning Oracle Autonomous Database
- Connecting to Oracle Autonomous Database
- SQL Development Tools Installation
- Lab Exercise 1: Setting Up SQL Environment
- Lab Exercise 2: Querying Data
- Lab Exercise 3: Exploring Joins
- Lab Exercise 4: Aggregating Data
- Lab Exercise 5: Modifying Data and Transactions
- Lab Exercise 6: Building a Blood Donation Database
- Final Project: Building a Blood Donation Database
1. Oracle Cloud Account Setup
Sign Up for an Oracle Cloud Account:
Go to Oracle Cloud.
2. Provisioning Oracle Autonomous Database
Access Oracle Cloud Console:
Log in to the Oracle Cloud Console.
Create an Autonomous Database:
Navigate to the "Autonomous Database" section.
Click "Create Autonomous Database" and follow the setup wizard.
Provide details such as database name, username, and password.
Obtain Connection Details:
Once the Autonomous Database is provisioned, note down the connection details (hostname, port, service name, username, password).
3. Connecting to Oracle Autonomous Database
Download SQL Developer or Toad for Oracle:
Download and install Oracle SQL Developer or Toad for Oracle on your local machine.
Connect SQL Developer or Toad to Autonomous Database:
Open SQL Developer or Toad and create a new connection.
Use the connection details obtained earlier (hostname, port, service name, username, password) to connect to the Autonomous Database.
4. SQL Development Tools Installation
Install SQL Developer:
Download SQL Developer from the official website.
Follow the installation wizard to install it on your machine.
Install Toad for Oracle:
Download Toad for Oracle from the official website.
Follow the installation wizard to install Toad on your machine.
5. Lab Exercise 1: Setting Up SQL Environment
1. Install Toad for Oracle:
Download Toad for Oracle from the official website.
Follow the installation wizard to install Toad on your machine.
-- SQL Command: None, as it involves setting up Toad.
2. Connect to a Database:
Open Toad and click on "New Connection."
Enter your connection details, including username, password, and database connection details (hostname, port), and click "Connect."
-- SQL Command: None, as it involves setting up Toad.
3. Create a Sample Database and Table:
In the SQL Editor within Toad, execute the CREATE TABLE statement to create a table named users with columns id, name, and age.
CREATE TABLE users ( id NUMBER PRIMARY KEY, name VARCHAR2(50), age NUMBER );
ALTER TABLE users
MODIFY id int NOT NULL;
CREATE SEQUENCE users_sequence
START WITH 1
INCREMENT BY 1;
CREATE OR REPLACE TRIGGER users_trigger
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SELECT users_sequence.nextval INTO :new.id FROM dual;
END;
4. Insert Sample Data:
Use the INSERT INTO statements to add sample data to the users table.
INSERT INTO users (name, age) VALUES ('John Doe', 25); INSERT INTO users (name, age) VALUES ('Jane Smith', 30);
5. Execute Basic Queries:
In the SQL Editor, run a SELECT * FROM users; query to retrieve all data from the users table.
SELECT * FROM users;
6. Lab Exercise 2: Querying Data
1. Basic SELECT Statement:
Retrieve all columns from the users table:
SELECT * FROM users;
2. Filtering Data:
Retrieve users older than 25:
SELECT * FROM users WHERE age > 25;
3. Sorting Data:
Retrieve users sorted by age in descending order:
SELECT * FROM users ORDER BY age DESC;
4. Limiting Results:
Retrieve the first 5 users:
SELECT * FROM users WHERE ROWNUM <= 5;
7. Lab Exercise 3: Exploring Joins
1. Inner Join:
Retrieve information from two tables where there is a match:
SELECT users.id, users.name, orders.order_number FROM users INNER JOIN orders ON users.id = orders.user_id;
2. Left Join:
Retrieve all records from the left table and the matched records from the right table:
SELECT users.id, users.name, orders.order_number FROM users LEFT JOIN orders ON users.id = orders.user_id;
3. Right Join:
Retrieve all records from the right table and the matched records from the left table:
SELECT users.id, users.name, orders.order_number FROM users RIGHT JOIN orders ON users.id = orders.user_id;
4. Full Outer Join:
Retrieve all records when there is a match in either the left or right table:
SELECT users.id, users.name, orders.order_number FROM users FULL OUTER JOIN orders ON users.id = orders.user_id;
8. Lab Exercise 4: Aggregating Data
1. Counting Records:
Count the number of users in the users table:
SELECT COUNT(*) FROM users;
2. Grouping Data:
Group users by age and display the count in each group:
SELECT age, COUNT(*) FROM users GROUP BY age;
9. Lab Exercise 5: Modifying Data and Transactions
1. Updating Records:
Update the age of a user in the users table:
UPDATE users SET age = 28 WHERE name = 'John Doe';
2. Deleting Records:
Delete a user from the users table:
DELETE FROM users WHERE name = 'Jane Smith';
3. Transactions:
Use transactions to ensure atomicity for a series of SQL statements:
BEGIN; -- SQL statements within the transaction COMMIT;
10. Lab Exercise 6: Building a Blood Donation Database
1. Create Tables:
Create tables for donors, donations, and recipients:
CREATE TABLE donors ( donor_id NUMBER PRIMARY KEY, donor_name VARCHAR2(50), blood_type VARCHAR2(5) ); CREATE TABLE donations ( donation_id NUMBER PRIMARY KEY, donor_id NUMBER, donation_date DATE, volume_ml NUMBER, FOREIGN KEY (donor_id) REFERENCES donors(donor_id) ); CREATE TABLE recipients ( recipient_id NUMBER PRIMARY KEY, recipient_name VARCHAR2(50), blood_type VARCHAR2(5) );
2. Insert Sample Data:
Insert sample data into each table:
INSERT INTO donors (donor_id, donor_name, blood_type) VALUES (1, 'John Smith', 'O+'); INSERT INTO donors (donor_id, donor_name, blood_type) VALUES (2, 'Jane Doe', 'A-'); INSERT INTO donations (donation_id, donor_id, donation_date, volume_ml) VALUES (1, 1, TO_DATE('2023-01-01', 'YYYY-MM-DD'), 500); INSERT INTO donations (donation_id, donor_id, donation_date, volume_ml) VALUES (2, 2, TO_DATE('2023-02-15', 'YYYY-MM-DD'), 750); INSERT INTO recipients (recipient_id, recipient_name, blood_type) VALUES (1, 'Alice Johnson', 'AB+'); INSERT INTO recipients (recipient_id, recipient_name, blood_type) VALUES (2, 'Bob Williams', 'B-');
3. Write Queries:
Write queries to retrieve information about donors, donations, and recipients.
-- Example queries SELECT * FROM donors; SELECT * FROM donations; SELECT * FROM recipients;
11. Final Project: Building a Blood Donation Database
Project Overview:
For the final project, you will build a Blood Donation Database to manage information about blood donors, donations, and recipients.
Project Tasks:
- Create tables for donors, donations, and recipients.
- Insert sample data into each table.
- Write queries to retrieve information about donors, donations, and recipients.
- Implement basic CRUD operations (Create, Read, Update, Delete) for the database.
Project Submission:
Submit your SQL script containing all the queries and commands used to create and populate the Blood Donation Database.
