Question

Consider the following tables Student and Stream in the Streams_of_Students database. The primary key of the Stream table is StCode (stream code) which is the foreign key in the Student table. The primary key of the Student table is AdmNo (admission number).

AdmNo	Name	StCode
211	Jay	NULL
241	Aditya	S03
290	Diksha	S01
333	Jasqueen	S02
356	Vedika	S01
380	Ashpreet	S03

 

StCode	Stream
S01	Science
S02	Commerce
S03	Humanities

Write SQL queries for the following:

1. Create the database Streams_Of_Students.
2. Create the table Student by choosing appropriate data types based on the data given in the table.
3. Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream.
4. Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.
5. Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order.
6. Display the names of students enrolled in Science and Humanities stream, ordered by student name in alphabetical order, then by admission number in ascending order (for duplicating names).
7. List the number of students in each stream having more than 1 student.
8. Display the names of students enrolled in different streams, where students are arranged in descending order of admission number.
9. Show the Cartesian product on the Student and Stream table. Also mention the degree and cardinality produced after applying the Cartesian product.
10. Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row.
11. List the names of teachers and students.
12. If Cartesian product is again applied on Student and Stream tables, what will be the degree and cardinality of this modified table?

Answer 1

a. 

CREATE DATABASE Streams_Of_Students;
USE Streams_Of_Students;

b. 

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

CREATE TABLE Student (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    StCode VARCHAR(5),
    FOREIGN KEY (StCode) REFERENCES Stream(StCode)
);

c) 

• Student Table Primary Key: AdmNo
• Stream Table Primary Key: StCode
• Student Table Foreign Key: StCode

d) 

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

e) 

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

f) 

SELECT Name 
FROM Student 
WHERE StCode IN ('S01', 'S03')
ORDER BY Name ASC, AdmNo ASC;

g) 

SELECT StCode, COUNT(*) 
FROM Student 
WHERE StCode IS NOT NULL
GROUP BY StCode 
HAVING COUNT(*) > 1;

h) 

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

i) SELECT * FROM Student CROSS JOIN Stream;

 

j) 

ALTER TABLE Stream ADD TeacherIncharge VARCHAR(30);
UPDATE Stream SET TeacherIncharge = 'Mr. Sharma' WHERE StCode = 'S01';
UPDATE Stream SET TeacherIncharge = 'Ms. Verma' WHERE StCode = 'S02';
UPDATE Stream SET TeacherIncharge = 'Dr. Gupta' WHERE StCode = 'S03';

k) 

SELECT Stream.TeacherIncharge, Student.Name 
FROM Student 
JOIN Stream ON Student.StCode = Stream.StCode;

l) 

• New Degree (Total Columns): 6 (3 from Student + 3 from Stream due to the new column)
• New Cardinality (Total Rows): 18 (Row count stays unchanged at 6 × 3)