Every SQL Example, Presented as a Problem

Example 1Store customer records

Example 2Store product records

Example 3Store sales receipts

Example 4Store the items sold on each receipt

Example 1Identify the super key and minimum primary key in Students

The full rows are unique, but RollNo alone already provides uniqueness. The PDF therefore describes the primary key as the minimum super key.

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

INSERT INTO Student VALUES (1,'Himanshu',35,'MBBS');
INSERT INTO Student VALUES (1,'New Himanshu',34,'MD');
INSERT INTO Student VALUES (NULL,'New Himanshu',34,'MD');

The first insertion succeeds. Reusing RollNo 1 violates uniqueness; using NULL violates the not-null part of the primary key.

Example 2Allow the same roll number in different courses

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

The PDF shows RollNo 1 once in MD and once in MBBS. The pair is unique even though RollNo repeats. Another RollNo 1 in MBBS is rejected; NULL in either RollNo or Course is rejected.

Example 3Enforce three candidate keys in Ticket

The PDF identifies three unique choices: TicketNo; PNRNo; and the combination DateofJourney, TrainNo, CoachNo and BerthNo.

CopyCREATE 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)
);

The PDF tests the constraints with Ashish and New Ashish. Repeating TicketNo violates the primary key; repeating PNRNo violates its unique key; repeating the journey/train/coach/berth combination violates the composite unique key. Changing the coach from S1 to S2 allows the row.

Example 4Prevent subscriptions to nonexistent publications

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

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

The PDF contains publications Yuva Bharati and Vivekananda Kendra Patrika, with Ashish and Manish as subscribers. Without a foreign key, Mahesh can be subscribed to the nonexistent Vivekananda Patrika.

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

After the relationship is enforced, inserting or updating a subscriber to Vivekananda Patrika fails because the parent is absent. Updating or deleting Yuva Bharati in Publications fails while Ashish refers to it. Dropping Publications also fails while its primary key is referenced.

Example 1Create Marks with primary-key, NOT NULL and CHECK constraints

CopyCREATE TABLE Marks(
  RollNo INT PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Phy INT NOT NULL CHECK(Phy>=0 AND Phy<=100),
  Chem INT NOT NULL CHECK(Chem>=0 AND Chem<=100)
);

CREATE TABLE Marks(
  RollNo INT,
  Name VARCHAR(100) NOT NULL,
  Phy INT NOT NULL CHECK(Phy>=0 AND Phy<=100),
  Chem INT NOT NULL CHECK(Chem>=0 AND Chem<=100),
  PRIMARY KEY(RollNo)
);

DESC Marks;

Example 2Compare composite uniqueness with individual uniqueness

CopyCREATE TABLE Sample(F1 INT, F2 INT, PRIMARY KEY(F1,F2));

CREATE TABLE Sample(F1 INT PRIMARY KEY, F2 INT UNIQUE);

In the first form, the F1/F2 combination cannot repeat. In the second, each field is independently unique.

Example 3Add and remove a field from Sample

CopyALTER TABLE Sample ADD F3 INT;
ALTER TABLE Sample DROP COLUMN F3;

The PDF also demonstrates that a datatype change may fail when the column contains data; after deleting the data, the change succeeds.

Example 4Accept only existing train numbers in Tickets

CopyCREATE TABLE Trains(
  TrainNo INT PRIMARY KEY,
  TrainName VARCHAR(100)
);

CREATE TABLE Tickets(
  TicketNo INT PRIMARY KEY,
  TrainNo INT REFERENCES Trains(TrainNo),
  Passenger VARCHAR(100)
);

The parent table contains TrainNo 1 and 2. A ticket for TrainNo 5 fails because that train does not exist; a ticket for an existing train succeeds. Deleting a train with a child ticket fails, while deleting one with no child record succeeds. The PDF then drops the foreign-key constraint, after which nonexistent train numbers become acceptable.

SetupCreate and populate the two player tables

CopyCREATE 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');

A plays only cricket, B plays only football, and C plays both.

Example 1Find every player without duplicates

CopySELECT Name FROM Cricketers
UNION
SELECT Name FROM Footballers;

UNION combines both result sets and removes the repeated C. Use it when membership matters but duplicates do not.

Example 2Find every player and retain duplicates

CopySELECT Name FROM Cricketers
UNION ALL
SELECT Name FROM Footballers;

UNION ALL appends the second result without duplicate removal. C therefore appears twice because C occurs in both source tables.

Example 3Find players who play both games

CopySELECT Name FROM Cricketers
INTERSECT
SELECT Name FROM Footballers;

INTERSECT keeps only names common to both sets. C is the only name stored in both tables.

Example 4Find players who play cricket but not football

CopySELECT Name FROM Cricketers
MINUS
SELECT Name FROM Footballers;

MINUS starts with the cricket names and removes every name also found among footballers.

Example 5Find players who play exactly one game using combined sets

CopySELECT Name FROM Cricketers
UNION
SELECT Name FROM Footballers
MINUS
(SELECT Name FROM Cricketers INTERSECT SELECT Name FROM Footballers);

The union gives all players. The intersection gives C, the player common to both games. Subtracting the intersection leaves those who play exactly one game.

Example 6Find players who play exactly one game using two differences

CopySELECT Name FROM Cricketers
MINUS
SELECT Name FROM Footballers
UNION
(SELECT Name FROM Footballers MINUS SELECT Name FROM Cricketers);

The first difference finds cricket-only A. The second finds football-only B. Their union produces the same answer by a different set expression.

Example 7Produce every possible cricketer-footballer pair

CopySELECT Cricketers.Name, Footballers.Name
FROM Cricketers, Footballers;

This is a Cartesian product. Each of the two Cricketers rows pairs with each of the two Footballers rows, so 2 × 2 gives four rows.

Example 8Find matching people with an inner join

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

An inner join keeps only rows for which the join condition is true in both tables.

Example 9Keep every cricketer with a left join

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

The left table is preserved. C matches a football row, while A remains even though no football record exists.

Example 10Keep every footballer with a right join

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

The right table is preserved. C matches a cricket row, while B remains without one.

Example 11Keep every person with a full outer join

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

A full outer join preserves matches and unmatched rows from both tables. Empty columns identify the game that A or B does not play.

Example 1Create Trains and Stops

CopyCREATE 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)
);

Example 2Store the three trains

CopyINSERT INTO Trains VALUES(1,'Mahanagari','Varanasi','Mumbai');
INSERT INTO Trains VALUES(2,'Mahanagari','Mumbai','Varanasi');
INSERT INTO Trains VALUES(3,'Ratnagiri','Varanasi','Mumbai');

Example 3Store the stops on the route

CopyINSERT INTO Stops VALUES(1,1,'Varanasi');
INSERT INTO Stops VALUES(2,1,'Jabalpur');
INSERT INTO Stops VALUES(3,1,'Itarsi');
INSERT INTO Stops VALUES(4,1,'Jalgaon');
INSERT INTO Stops VALUES(5,1,'Mumbai');

The PDF then displays the complete Stops table and separately selects each train’s stops in StopNo order:

CopySELECT * FROM Stops WHERE TrainNo=1 ORDER BY StopNo;
SELECT * FROM Stops WHERE TrainNo=2 ORDER BY StopNo;
SELECT * FROM Stops WHERE TrainNo=3 ORDER BY StopNo;

Example 4Find trains between any two stops

CopySELECT *
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;

SetupCreate and populate Cricket_Scores

MAX and MIN work on orderable types; SUM and AVG work on numbers; COUNT counts records.

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

INSERT INTO Cricket_Scores VALUES('Champak',1,'Test',111);

The complete PDF data has ten rows: Champak scores 111 in Test and 112 and 113 in One Day; Gaurav scores 0 and 10 in One Day; Pappu scores 3, 2, 1, 0 and 0 in One Day.

Example 1Find the highest score

CopySELECT MAX(Score) FROM Cricket_Scores;

MAX compares all Score values and returns the greatest.

Example 2Find the lowest score

CopySELECT MIN(Score) FROM Cricket_Scores;

MIN returns the least Score value.

Example 3Find the total of all scores

CopySELECT SUM(Score) FROM Cricket_Scores;

SUM adds all ten scores.

Example 4Find the average score

CopySELECT AVG(Score) FROM Cricket_Scores;

AVG divides the total 352 by the ten recorded scores.

Example 5Count the recorded scores

CopySELECT COUNT(Score) FROM Cricket_Scores;

COUNT(Score) counts the non-null Score values.

Example 6Try to display a batsman beside an ungrouped maximum

CopySELECT Batsman, MAX(Score) FROM Cricket_Scores;

The table is reduced to one maximum, but SQL has no rule for choosing a single Batsman value. The non-aggregate column must be grouped.

Example 7Find the maximum score of every batsman

CopySELECT Batsman, MAX(Score)
FROM Cricket_Scores
GROUP BY Batsman;

GROUP BY Batsman creates one group per player, and MAX is calculated separately in each group.

Example 8Find complete statistics for every batsman

CopySELECT Batsman, MAX(Score), MIN(Score), AVG(Score), SUM(Score), COUNT(Score)
FROM Cricket_Scores
GROUP BY Batsman;

Each batsman produces one row containing five summaries: maximum, minimum, average, total and count.

Example 9Find statistics for every batsman and match type

CopySELECT Batsman, MatchType, MAX(Score), MIN(Score), AVG(Score), SUM(Score), COUNT(Score)
FROM Cricket_Scores
GROUP BY Batsman, MatchType;

Grouping by two columns creates a separate group for every batsman/match-type combination. Champak therefore receives separate Test and One Day summaries.

Example 10Reverse the grouping-column order

CopySELECT Batsman, MatchType, MAX(Score), MIN(Score), AVG(Score), SUM(Score), COUNT(Score)
FROM Cricket_Scores
GROUP BY MatchType, Batsman;

The same combinations are produced. The PDF notes that column order can affect processing time even when the result does not change.

Example 11Find batsmen whose maximum score is at least 100

CopySELECT Batsman, MAX(Score)
FROM Cricket_Scores
GROUP BY Batsman
HAVING MAX(Score)>=100;

HAVING filters groups after the maximum has been calculated. It is required because the condition uses an aggregate.

Example 12Find first-innings Test batsmen whose maximum score is at least 100

CopySELECT Batsman, MAX(Score)
FROM Cricket_Scores
WHERE InningsNo=1 AND MatchType='Test'
GROUP BY Batsman
HAVING MAX(Score)>=100;

WHERE first keeps first-innings Test rows. GROUP BY forms batsman groups, and HAVING then tests each group maximum.

Example 13Find the highest score in Test Matches and One Days

CopySELECT MatchType, MAX(Score)
FROM Cricket_Scores
GROUP BY MatchType;

One group is created for each MatchType, so the output contains one maximum for Test and one for One Day.

Example 14Find each batsman’s highest first-innings score

CopySELECT Batsman, InningsNo, MAX(Score)
FROM Cricket_Scores
WHERE InningsNo=1
GROUP BY Batsman, InningsNo;

The row filter keeps innings 1. Grouping by Batsman and InningsNo then returns each player’s first-innings maximum.

Example 15Find the overall highest first-innings score

CopySELECT InningsNo, MAX(Score)
FROM Cricket_Scores
WHERE InningsNo=1
GROUP BY InningsNo;

Removing Batsman from SELECT and GROUP BY combines all first-innings rows and returns one overall maximum.

Example 16Find the batsman with the highest average

CopySELECT Batsman, AVG(Score)
FROM Cricket_Scores
GROUP BY Batsman
HAVING AVG(Score) = (
  SELECT MAX(Average)
  FROM (
    SELECT Batsman, AVG(Score) AS Average
    FROM Cricket_Scores
    GROUP BY Batsman
  )
);

The inner grouped query calculates one average per batsman. The next query finds the maximum of those averages. HAVING retains the batsman whose average equals that maximum.

Example 1Create Result and insert the three students

CopyCREATE TABLE Result(
  RollNo INT PRIMARY KEY,
  Phy INT,
  Chem INT,
  Maths INT,
  Name VARCHAR(100),
  DOB DATE
);

INSERT INTO Result VALUES(1,40,40,40,'A','1-Jan-2013');
INSERT INTO Result VALUES(2,70,80,39,'B','2-Jan-2013');
INSERT INTO Result VALUES(3,99,98,100,'C','3-Jan-2013');

SELECT * FROM Result;

Example 2Display qualifying Physics results in descending roll-number order

CopySELECT MAX(Phy), RollNo
FROM Result
WHERE RollNo<=10
GROUP BY RollNo
HAVING AVG(Phy)>40
ORDER BY RollNo DESC;

FROM chooses Result. WHERE admits RollNo values up to 10. GROUP BY makes one group per RollNo. HAVING removes the group whose average Physics mark is not above 40. SELECT returns the maximum Physics mark and RollNo, and ORDER BY displays the remaining RollNo values in descending order.