Assignment: SQL Database Concepts & Querying
Class: 11 | Subject: Informatics Practices / CS Topic: MySQL Data Handling
You are the database administrator for a school. You need to manage the data of students who have
enrolled in various clubs. You will work with a table named CLUB_MEMBERS.
1. Write a SQL command to create a database named SCHOOL_DB.
2. Write a SQL command to create a table named CLUB_MEMBERS with the following structure:
Column Name Data Type Constraint Description
MemberID INT PRIMARY KEY Unique ID for every student
Name VARCHAR(30) NOT NULL Name of the student
Stream VARCHAR(20) Science, Commerce, or Arts
Club VARCHAR(20) Photography, Robotics, Music, Dance
Fee INT Membership fee paid
JoinDate DATE Date of joining
3. Alter Table: The school decided to track the city of each student. Write a query to add a new
column City (VARCHAR 20) to the table.
4. Drop Column: Write a query to remove the JoinDate column from the table.
5. Write a SQL query to insert the following row into the table:
MemberID: 101, Name: "Aarav", Stream: "Science", Club: "Robotics", Fee: 1500, City: "Jhalawar"
6. Write a query to increase the Fee by 200 for all students in the "Robotics" club.
Assume the table CLUB_MEMBERS now contains the following data for the questions below:
MemberID Name Stream Club Fee City
101 Aarav Science Robotics 1700 Jhalawar
102 Meera Commerce Music 1200 Kota
103 Rohan Science Robotics 1700 Jaipur
104 Ishita Arts Dance 1000 Jhalawar
105 Sameer Commerce Photography 1500 Kota
106 Nitya Science Music 1200 Jhalawar
107 Arjun Arts NULL 500 Jaipur
108 Kavya Science Robotics 1700 Bundi
Write SQL Queries for the following requirements:
7. Display the Name and Club of all students who live in "Jhalawar".
8. Display details of all students who have paid a Fee greater than 1400.
9. Display the Name and Stream of students who are in the "Science" stream AND are members of
the "Robotics" club.
10. Display the list of students whose Fee is between 1000 and 1500 (inclusive).
11. Display the Name of students who live in either "Kota" or "Bundi". (Use the IN operator).
12. Display all details of students whose Name starts with the letter 'R'.
13. Display the Name and Fee of all students, sorted by Fee in Descending order.
14. Display the records of students who have not been assigned a club yet (where Club is NULL).
15. Display the distinct (unique) Streams present in the table.
16. Challenge Question: Display the names of students whose name ends with 'a' and are paying less
than 1500.
Part A & B
1. CREATE DATABASE SCHOOL_DB;
2. CREATE TABLE CLUB_MEMBERS (MemberID INT PRIMARY KEY, Name VARCHAR(30) NOT NULL,
Stream VARCHAR(20), Club VARCHAR(20), Fee INT, JoinDate DATE);
3. ALTER TABLE CLUB_MEMBERS ADD City VARCHAR(20);
4. ALTER TABLE CLUB_MEMBERS DROP COLUMN JoinDate;
5. INSERT INTO CLUB_MEMBERS VALUES (101, 'Aarav', 'Science', 'Robotics', 1500, 'Jhalawar');
6. UPDATE CLUB_MEMBERS SET Fee = Fee + 200 WHERE Club = 'Robotics';
7. SELECT Name, Club FROM CLUB_MEMBERS WHERE City = 'Jhalawar';
8. SELECT * FROM CLUB_MEMBERS WHERE Fee > 1400;
9. SELECT Name, Stream FROM CLUB_MEMBERS WHERE Stream = 'Science' AND Club = 'Robotics';
10. SELECT * FROM CLUB_MEMBERS WHERE Fee BETWEEN 1000 AND 1500;
11. SELECT Name FROM CLUB_MEMBERS WHERE City IN ('Kota', 'Bundi');
12. SELECT * FROM CLUB_MEMBERS WHERE Name LIKE 'R%';
13. SELECT Name, Fee FROM CLUB_MEMBERS ORDER BY Fee DESC;
14. SELECT * FROM CLUB_MEMBERS WHERE Club IS NULL;
15. SELECT DISTINCT Stream FROM CLUB_MEMBERS;
16. SELECT Name FROM CLUB_MEMBERS WHERE Name LIKE '%a' AND Fee < 1500;