GRADE 12 : PRACTICAL 15

  

Create the below tables : 

Student:

Stream:

CREATE TABLE STREAM (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(20)
);
CREATE TABLE STUDENT (
    adno INT PRIMARY KEY,
    fname VARCHAR(20),
    lname VARCHAR(20),
    class VARCHAR(5),
    sec CHAR(1),
    fee INT,
    mobile BIGINT,
    area VARCHAR(30),
    s_id INT,
    FOREIGN KEY (s_id) REFERENCES STREAM(s_id)
);

INSERT INTO STREAM VALUES 

(10, 'PCMC'),

(20, 'PCMB'),

(30, 'PCBH'),

(40, 'HECA'),

(50, 'MECA'),

(60, 'Arts');

INSERT INTO STUDENT VALUES 

(1001, 'Ramesh', 'Babu', 'xii', 'a', 5000, 9988776655, 'Benson Town', 10),

(1002, 'Dipti', 'Rai', 'xii', 'b', 4500, 8877665599, 'Cooke Town', 20),

(1003, 'Rahul', 'Pai', 'xii', 'c', 4000, 7766559988, 'Fraser Town', 30),

(1004, 'Shruthi', 'Saravanan', 'xii', 'c', 4000, 6655998877, 'Benson Town', 30),

(1005, 'George', 'Davis', 'xi', 'a', 4500, 5599887766, 'Cooke town', 10),

(1006, 'Tinu', 'Anna', 'xi', 'b', 4200, 9988776611, 'Benson Town', 20),

(1007, 'Abrar', 'Sultan', 'xi', 'c', 4100, 9988776622, 'Cooke town', 40),

(1008, 'Iffath', 'Khan', 'xii', 'a', 5000, 9988776633, 'Fraser Town', 10),

(1009, 'Hansika', 'Singh', 'xii', 'b', 4500, 9988776644, 'Cleveland Town', 20),

(1010, 'Kailash', 'Dharyanani', 'xii', 'c', 4000, 9988776633, 'Richard Town', 50);

1)Display each student's full name along with their stream name.

SELECT CONCAT(fname,' ',lname) AS FullName, s_name

FROM STUDENT NATURAL JOIN STREAM;

2)List the names of streams and the number of students in each.

SELECT s_name, COUNT(*) AS Num_Students

FROM STREAM NATURAL JOIN STUDENT 

GROUP BY s_name;

3)Find the total fee collected from each class

SELECT class, SUM(fee) AS Total_Fee

FROM STUDENT

GROUP BY class;

4)List the average fee paid by students in each stream

SELECT s_name, AVG(fee) AS Avg_Fee

FROM STUDENT NATURAL JOIN STREAM 

GROUP BY s_name;

5)Display the student(s) who paid the highest fee.

SELECT fname, lname, fee

FROM STUDENT

WHERE fee = (SELECT MAX(fee) FROM STUDENT);

6)Display the stream name and the total fee collected (highest total first), where the fee collected is more than Rs 4000 

SELECT s_name,SUM(fee)

FROM Student NATURAL JOIN Stream

GROUP BY s_name

HAVING SUM(fee) > 4000

ORDER BY SUM(fee) desc;

7)Increase the fee by 500 for all students in the ‘PCMC’ stream.

UPDATE STUDENT

SET fee = fee + 500

WHERE s_id = (SELECT s_id FROM STREAM WHERE s_name = 'PCMC');

8)Update mobile number of student 'George Davis' to 9123456789.

UPDATE STUDENT

SET mobile = 9123456789

WHERE fname = 'George' AND lname = 'Davis';

9)Delete all students in the 'Arts' stream.

DELETE FROM STUDENT

WHERE s_id = (SELECT s_id FROM STREAM WHERE s_name = 'Arts');

10)Delete all students whose area starts with C and the first word has atleast 6 letters and ends with town.

DELETE FROM STUDENT

WHERE area LIKE 'C_____% Town';