Skip to content

Latest commit

 

History

History
123 lines (90 loc) · 3.69 KB

README.md

File metadata and controls

123 lines (90 loc) · 3.69 KB

Database Script Project 📊

This repository contains Oracle SQL scripts designed to create, manage, and optimize databases. The scripts are tailored for efficient database operations and demonstrate advanced SQL techniques for various use cases.

Features

  • Schema Creation: Scripts to define tables, relationships, and constraints.
  • Data Manipulation: Includes examples of INSERT, UPDATE, and DELETE operations.
  • Data Querying: Complex SELECT queries showcasing joins, subqueries, and aggregations.
  • Performance Optimization: Use of indexes, triggers, and views to enhance database performance.
  • Transaction Management: Examples of transactions with COMMIT and ROLLBACK.

Developers & project founders

joseCalvo02 - Jose Calvo

sotoJ24 - Josué Soto

Marcodero2021 - Mario Cordero Ulate

Pablo735r - Pablo Amador

Requirements

To run these scripts, ensure the following are installed and configured:

  1. Oracle Database: Download and set up Oracle Database.
  2. SQL Developer: Use Oracle SQL Developer for an intuitive SQL scripting environment.
  3. Database Access: Ensure you have a user with sufficient privileges to execute the scripts.

Installation and Setup

Follow these steps to use the scripts:

  1. Clone the Repository

    git clone https://github.com/JoseCalvo02/Database_Script.git  
cd Database_Script

  2. Set Up Oracle Database

    • Log in to your Oracle SQL environment.
    • Create a new user (optional): 
      CREATE USER database_user IDENTIFIED BY password;  
GRANT ALL PRIVILEGES TO database_user;

  3. Run the Scripts

    • Open the SQL Developer tool.
    • Navigate to the scripts/ folder in this repository.
    • Execute the scripts in the following order (if applicable):
      1. schema.sql - Creates tables and relationships.
      2. data.sql - Inserts initial data into the tables.
      3. queries.sql - Contains complex queries and operations.
      4. triggers_and_views.sql - Adds triggers, views, or stored procedures.

Example Usage

Create a Table

CREATE TABLE employees (  
    employee_id NUMBER PRIMARY KEY,  
    first_name VARCHAR2(50),  
    last_name VARCHAR2(50),  
    department_id NUMBER,  
    salary NUMBER  
);

Insert Data

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)  
VALUES (1, 'John', 'Doe', 101, 50000);

Query Data

SELECT e.first_name, e.last_name, d.department_name  
FROM employees e  
JOIN departments d ON e.department_id = d.department_id  
WHERE e.salary > 40000;

Create a Trigger

CREATE OR REPLACE TRIGGER salary_check  
BEFORE INSERT OR UPDATE OF salary ON employees  
FOR EACH ROW  
BEGIN  
    IF :NEW.salary < 0 THEN  
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');  
    END IF;  
END;  
/

Contribution Guidelines

Contributions are welcome! Feel free to open issues or submit pull requests for improvements.

Resources

License

This project is licensed under the MIT License. See the LICENSE file for details.