SQL From Zero to Practice

1. What is a database?

A database stores data in organized tables. A table is like a spreadsheet: columns define the type of information and rows store actual records.

The goal of SQL (Structured Query Language) is to create, insert, read, update, delete, summarize, and protect this data.

2. Keys in a database

Keys protect tables from confusion. They make sure each important record can be identified correctly.

Primary key example

CREATE TABLE Student (
  RollNo INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT,
  Course VARCHAR(50)
);

Composite primary key example

Use this when a single column is not enough. For example, RollNo can repeat across courses, but RollNo + Course should not repeat.

CREATE TABLE StudentCourse (
  RollNo INT,
  Name VARCHAR(50),
  Age INT,
  Course VARCHAR(50),
  PRIMARY KEY (RollNo, Course)
);

Candidate key example

CREATE TABLE Ticket (
  TicketNo VARCHAR(50) PRIMARY KEY,
  PNRNo VARCHAR(50) UNIQUE,
  PassengerName VARCHAR(50),
  DateOfJourney DATE,
  TrainNo VARCHAR(50),
  CoachNo VARCHAR(50),
  BerthNo INT,
  UNIQUE (DateOfJourney, TrainNo, CoachNo, BerthNo)
);

Foreign key example

CREATE TABLE Publications (
  PublicationName VARCHAR(100) PRIMARY KEY
);

CREATE TABLE Subscribers (
  PublicationName VARCHAR(100) REFERENCES Publications(PublicationName),
  CustomerId VARCHAR(100),
  PRIMARY KEY (PublicationName, CustomerId)
);

Important idea: a subscriber should not be allowed to subscribe to a publication that does not exist in the parent table.

3. Aggregate queries

Aggregate functions work on multiple rows and return a summary. The documents focus on five important aggregate functions.

Cricket scores table

CREATE TABLE Cricket_Scores (
  Batsman VARCHAR(100),
  InningsNo INT,
  MatchType VARCHAR(10),
  Score INT
);

INSERT INTO Cricket_Scores VALUES ('Amit', 1, 'Test', 120);
INSERT INTO Cricket_Scores VALUES ('Amit', 2, 'Test', 45);
INSERT INTO Cricket_Scores VALUES ('Ravi', 1, 'ODI', 80);
INSERT INTO Cricket_Scores VALUES ('Ravi', 2, 'ODI', 100);
INSERT INTO Cricket_Scores VALUES ('Mohan', 1, 'Test', 30);
INSERT INTO Cricket_Scores VALUES ('Mohan', 2, 'ODI', 60);

Basic aggregate examples

SELECT MAX(Score) AS HighestScore FROM Cricket_Scores;
SELECT MIN(Score) AS LowestScore FROM Cricket_Scores;
SELECT SUM(Score) AS TotalRuns FROM Cricket_Scores;
SELECT AVG(Score) AS AverageScore FROM Cricket_Scores;
SELECT COUNT(*) AS NumberOfInnings FROM Cricket_Scores;

GROUP BY

When you show a normal column along with an aggregate function, group the rows by that normal column.

SELECT Batsman, MAX(Score) AS HighestScore
FROM Cricket_Scores
GROUP BY Batsman;

Multiple GROUP BY columns

SELECT Batsman, MatchType, MAX(Score) AS HighestScore
FROM Cricket_Scores
GROUP BY Batsman, MatchType;

HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregate calculation.

SELECT Batsman, MAX(Score) AS HighestScore
FROM Cricket_Scores
GROUP BY Batsman
HAVING MAX(Score) >= 100;

4. Set operations: UNION, UNION ALL, INTERSECT, MINUS

The result of a SELECT query can be treated like a set. That is why SQL can combine two query results.

CREATE TABLE Cricketers (
  Name VARCHAR(100) PRIMARY KEY,
  Runs INT
);

CREATE TABLE Footballers (
  Name VARCHAR(100) PRIMARY KEY,
  Goals INT
);

INSERT INTO Cricketers VALUES ('A', 100);
INSERT INTO Cricketers VALUES ('C', 150);
INSERT INTO Footballers VALUES ('B', 15);
INSERT INTO Footballers VALUES ('C', 25);

SELECT Name FROM Cricketers
UNION
SELECT Name FROM Footballers;

SELECT Name FROM Cricketers
UNION ALL
SELECT Name FROM Footballers;

SELECT Name FROM Cricketers
INTERSECT
SELECT Name FROM Footballers;

SELECT Name FROM Cricketers
MINUS
SELECT Name FROM Footballers;

Some databases use EXCEPT instead of Oracle's MINUS.

5. Joins

A join combines columns from related tables. In the cricket-football example, player C appears in both tables.

Inner join

SELECT *
FROM Cricketers C
INNER JOIN Footballers F
ON C.Name = F.Name;

Shows only matching rows.

Left join

SELECT *
FROM Cricketers C
LEFT JOIN Footballers F
ON C.Name = F.Name;

Shows all cricketers and matching footballer data where available.

Right join

SELECT *
FROM Cricketers C
RIGHT JOIN Footballers F
ON C.Name = F.Name;

Shows all footballers and matching cricketer data where available.

Full outer join

SELECT *
FROM Cricketers C
FULL OUTER JOIN Footballers F
ON C.Name = F.Name;

Shows all records from both sides, matching where possible.

Self join idea: trains between two stations

A table can be joined with itself. The document uses this idea to find trains between two stations by joining Stops as source and destination.

CREATE TABLE Trains (
  TrainNo VARCHAR(10) PRIMARY KEY,
  TrainName VARCHAR(100),
  Source VARCHAR(100),
  Dest VARCHAR(100)
);

CREATE TABLE Stops (
  StopNo INT,
  TrainNo VARCHAR(10) REFERENCES Trains(TrainNo),
  Station VARCHAR(100),
  PRIMARY KEY (StopNo, TrainNo)
);

SELECT *
FROM Stops Source
INNER JOIN Stops Dest
ON Source.TrainNo = Dest.TrainNo
INNER JOIN Trains Train
ON Source.TrainNo = Train.TrainNo
WHERE Source.Station = 'Itarsi'
  AND Dest.Station = 'Jalgaon'
  AND Source.StopNo < Dest.StopNo;

6. Oracle triggers

A trigger is SQL/PLSQL code that runs automatically when a database event happens. The documents cover DML, database, and DDL triggers.

Backup trigger after insert

CREATE TABLE Table1 (
  Id INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Table2 (
  Id INT,
  Name VARCHAR(100)
);

CREATE OR REPLACE TRIGGER InsertTrigger
AFTER INSERT ON Table1
FOR EACH ROW
BEGIN
  INSERT INTO Table2 VALUES (:NEW.Id, :NEW.Name);
END;

:NEW gives the new value being inserted or updated. :OLD gives the old value during update or delete.

Logon trigger

CREATE TABLE LoginStats (
  Username VARCHAR(50),
  DateTimeOfLogin DATE
);

CREATE OR REPLACE TRIGGER LoginTrigger
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO LoginStats VALUES (ora_login_user, sysdate);
END;

DDL trigger to track created objects

CREATE TABLE CreatedObjects (
  CreatedByUser VARCHAR(100),
  ObjectName VARCHAR(100),
  ObjectType VARCHAR(100),
  DateOfCreation DATE
);

CREATE OR REPLACE TRIGGER DatabaseCreateObjectTrigger
AFTER CREATE ON DATABASE
BEGIN
  INSERT INTO CreatedObjects
  VALUES (ora_login_user, ora_dict_obj_name, ora_dict_obj_type, sysdate);
END;

Triggers are powerful. Use them carefully because hidden automatic actions can make debugging difficult.

7. Oracle connection idea

The supplied Oracle 10g connection document shows the classic Java/JSP connection flow: create a table, load the Oracle JDBC driver, connect using URL + username + password, then run SQL.

String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "system";
String password = "system";

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con = DriverManager.getConnection(url, username, password);
Statement statement = con.createStatement();

int n = statement.executeUpdate(
  "INSERT INTO Books VALUES ('The C Programming Language', 250)"
);

Teaching note: never hard-code real production passwords in source code. Use environment variables or a secure secrets system.