Complete SQL Solution: Streams_of_Students Database

This is a multi-part SQL task requiring database creation, table manipulation, queries with joins, aggregation, and Cartesian products. Each sub-part is answered below in the printed order.

(a) Create the database Streams_Of_Students

CREATE DATABASE Streams_Of_Students;
USE Streams_Of_Students;

The CREATE DATABASE statement initializes the database; USE switches the context to it for subsequent operations.

(b) Create the table Student

CREATE TABLE Student (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(50),
    StCode CHAR(3),
    FOREIGN KEY (StCode) REFERENCES Stream(StCode)
);

Key choices:

• AdmNo is INT (numeric admission numbers) and the primary key.
• Name is VARCHAR(50) to accommodate variable-length text.
• StCode is CHAR(3) (fixed 3-character codes like 'S01') and a foreign key referencing Stream(StCode).
• The foreign key constraint enforces referential integrity.

(c) Identify Primary and Foreign Keys

Primary Keys:

• Student table: AdmNo
• Stream table: StCode

Foreign Key:

• In Student table: StCode references Stream(StCode)

The question asks for the foreign key "from the table Stream," but there is no foreign key in the Stream table. The foreign key relationship is defined in the Student table, where StCode points to Stream(StCode). If the question intends to ask which column in Stream is referenced, that is StCode (the primary key of Stream).

(d) Update Jay's stream to Humanities

UPDATE Student
SET StCode = 'S03'
WHERE Name = 'Jay';

Jay's current StCode is NULL. Humanities corresponds to StCode = 'S03' in the Stream table. The WHERE clause targets Jay's record by name (or equivalently WHERE AdmNo = 211).

(e) Display names ending with 'a', alphabetically

SELECT Name
FROM Student
WHERE Name LIKE '%a'
ORDER BY Name;

Output:

The LIKE '%a' pattern matches names ending with 'a' — that's Aditya, Diksha and Vedika (Jay, Jasqueen and Ashpreet don't end in 'a'). ORDER BY Name sorts them alphabetically (ascending by default).

(f) Students in Science and Humanities, ordered by name then AdmNo

SELECT s.Name
FROM Student s
JOIN Stream st ON s.StCode = st.StCode
WHERE st.Stream IN ('Science', 'Humanities')
ORDER BY s.Name, s.AdmNo;

Output:

The JOIN links students to their streams. WHERE st.Stream IN (...) filters for Science (S01) and Humanities (S03). After Jay's update in part (d), he is now in Humanities. The ORDER BY clause first sorts by name alphabetically, then by AdmNo for any duplicate names (though none exist here).

(g) Number of students per stream (streams with >1 student)

SELECT st.Stream, COUNT(s.AdmNo) AS StudentCount
FROM Stream st
JOIN Student s ON st.StCode = s.StCode
GROUP BY st.Stream
HAVING COUNT(s.AdmNo) > 1;

After Jay's update (Jay → S03), the students per stream are:

• Science (S01): Diksha, Vedika → 2
• Commerce (S02): Jasqueen → 1
• Humanities (S03): Jay, Aditya, Ashpreet → 3

Output (streams with count > 1):

The GROUP BY aggregates by stream; HAVING filters groups with count >1.

(h) Students and streams, descending by AdmNo

SELECT s.Name, st.Stream
FROM Student s
JOIN Stream st ON s.StCode = st.StCode
ORDER BY s.AdmNo DESC;

Output:

The JOIN pairs each student with their stream. ORDER BY s.AdmNo DESC sorts from highest (380) to lowest (211).

(i) Cartesian product: degree and cardinality

SELECT *
FROM Student, Stream;

The Cartesian product SELECT * FROM Student, Stream pairs every row of one table with every row of the other and combines all their columns.

Degree (number of columns): Student has 3 columns (AdmNo, Name, StCode) and Stream has 2 columns (StCode, Stream), so the result has $3 + 2 = 5$ columns. Both StCode columns are retained, distinguished by their table prefix.

Cardinality: $6 \times 3 = 18$ rows (Student has 6 rows, Stream has 3 rows).

Sample output (first 3 rows):

(Each of the 6 students paired with each of the 3 streams.)

Degree = 5, Cardinality = 18.

--- …

Understanding the Schema

The database models students enrolled in different academic streams. The Stream table holds the master list of streams (Science, Commerce, Humanities), each identified by StCode. The Student table references this through a foreign key StCode, establishing a many-to-one relationship: many students can belong to one stream, and a student may have no stream assigned (NULL).

Primary keys uniquely identify rows. Foreign keys enforce referential integrity — every non-NULL StCode in Student must exist in Stream. This design prevents orphaned records and maintains data consistency.

(a) Create the database

CREATE DATABASE Streams_Of_Students;
USE Streams_Of_Students;

The USE statement switches the active database context so subsequent commands operate on Streams_Of_Students.

(b) Create the Student table

CREATE TABLE Student (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    StCode CHAR(3),
    FOREIGN KEY (StCode) REFERENCES Stream(StCode)
);

Key design choices:

• AdmNo INT: Admission numbers are numeric identifiers; INT is appropriate.
• Name VARCHAR(50): Variable-length string for names; 50 characters accommodate most names.
• StCode CHAR(3): Fixed 3-character codes (S01, S02, S03) — CHAR is more efficient than VARCHAR for fixed-width data.
• StCode allows NULL (no NOT NULL constraint) because Jay's record shows a student can exist without an assigned stream.
• The foreign key constraint ensures StCode values (when not NULL) must exist in the Stream table.

Stream table creation (prerequisite):

CREATE TABLE Stream (
    StCode CHAR(3) PRIMARY KEY,
    Stream VARCHAR(20) NOT NULL
);

(c) Identify Primary and Foreign Keys

Primary Keys:

• Student table: AdmNo — uniquely identifies each student.
• Stream table: StCode — uniquely identifies each stream.

Foreign Key:

• Student table: StCode references Stream(StCode).

(d) Update Jay's stream to Humanities

Jay currently has StCode = NULL. Humanities corresponds to StCode = 'S03'.

UPDATE Student
SET StCode = 'S03'
WHERE Name = 'Jay';

Why this works:

• UPDATE modifies existing rows.
• WHERE Name = 'Jay' targets Jay's record specifically.
• SET StCode = 'S03' assigns the Humanities stream code.

After this update, Jay's row becomes:

(e) Display students whose names end with 'a', alphabetically

SELECT Name
FROM Student
WHERE Name LIKE '%a'
ORDER BY Name;

Explanation:

• LIKE '%a': The % wildcard matches any sequence of characters; %a matches names ending with 'a'.
• ORDER BY Name: Sorts results alphabetically (ascending by default).

Output:

Checking every student's name against the %a pattern: Jay (no), Aditya (yes — ends in 'a'), Diksha (yes), Jasqueen (no), Vedika (yes), Ashpreet (no). Three names match, sorted alphabetically.

(f) Display students in Science and Humanities, ordered by name then admission number

SELECT s.Name
FROM Student s
JOIN Stream st ON s.StCode = st.StCode
WHERE st.Stream IN ('Science', 'Humanities')
ORDER BY s.Name, s.AdmNo;

Key points:

• JOIN combines Student and Stream on matching StCode values.
• WHERE st.Stream IN ('Science', 'Humanities') filters to the two specified streams.
• ORDER BY s.Name, s.AdmNo: Primary sort by name; if names tie, secondary sort by admission number.

Output (after Jay's update in part d):

(g) List streams with more than 1 student

SELECT st.Stream, COUNT(*) AS StudentCount
FROM Student s
JOIN Stream st ON s.StCode = st.StCode
GROUP BY st.Stream
HAVING COUNT(*) > 1;

Why HAVING not WHERE:

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.
• COUNT(*) > 1 is an aggregate condition, so HAVING is required.

Output (after Jay's update in part d):

Science has Diksha and Vedika (2). Humanities has Aditya, Ashpreet, and Jay — updated in part (d) — for 3. Commerce has only Jasqueen (1 student), so it's excluded by the HAVING COUNT(*) > 1 filter.

(h) Display students and their streams, ordered by admission number descending

SELECT s.Name, st.Stream
FROM Student s
JOIN Stream st ON s.StCode = st.StCode
ORDER BY s.AdmNo DESC;

Output (after Jay's update):

Jay (211) appears last because 211 is the smallest admission number.

(i) Cartesian product and its degree/cardinality

SELECT *
FROM Student, Stream;

Or equivalently:

SELECT *
FROM Student CROSS JOIN Stream;

What is a Cartesian product?

Every row in Student pairs with every row in Stream, producing all possible combinations.

Degree (number of columns):

• Student has 3 columns (AdmNo, Name, StCode).
• Stream has 2 columns (StCode, Stream).
• Cartesian product: $3 + 2 = 5$ columns.

Cardinality (number of rows):

• Student has 6 rows.
• Stream has 3 rows.
• Cartesian product: $6 \times 3 = 18$ rows.

Sample output (first few rows):

Notice the column name collision: both tables have StCode, so the result has two StCode columns (the DBMS may rename them as StCode and StCode_1 or require table prefixes in practice).

--- …