SQL Notes

Contains SQL practise problems and solutions.

Things to Keep in Mind:

• Single statement SQL were solved focusing on oracle database whereas PL/pgSQL were written focusing on postgreSQL.
• Most of the files use sample hr database of oracle sql. So before running them, your system must be connected to sample hr database.

This repository contain my solutions to the practise problems and sql written by me. Your solution may differ.

---

How to Use it:

• Open the oracle handbook from assets
• Read the topics and try to solve the practise problems
• If you are stuck you can see the solution from this repository. Use the index section.

---

Index:

Single Statement SQL

• Chapter 2: Retrieve Data From Database Tables
  • Selecting Data Using Basic SELECT Statement
  • SELECT Statement with WHERE Clause
  • Sorting Rows in the Output
• Chapter 3: Use of Oracle Single Row Functions
  • Character Functions
  • Number Functions
  • Date Functions
  • Functions for Manipulating NULL values
  • Data Type Conversion Functions
• Chapter 4: Aggregate Functions
  • Retrieve Aggregate Information: Simple GROUP BY queries
  • DISTINCT and HAVING in GROUP BY query
  • Advanced GROUP BY queries
• Chapter 5: Query Multiple Tables - Joins
  • Oracle Joins to Retrieve Data from Multiple Tables
• Chapter 6: Query Multiple Tables - Sub-query
  • Retrieving Records using Sub-query
• Chapter 7: Set Operations
  • Performing Set Operations
• Chapter 8: Data Manipulation Language(DML)
  • Inserting Data into Table
  • Changing Data in a Table
  • Deleting Rows from a Table
• Chapter 9: Data Definition Language(DDL)
  • Creating Tables
  • Specifying Constraints in Tables
  • Add or Remove Table Columns

---

PL/pgSQL

• Anonymous Block
• Variable
• Errors & Messages
• Control Statement
• Loop
• Function
• Trigger

---

Resources:

• postgreSQL documentation
• postgreSQL tutorial
• sql for generating hr schema