mysql> CREATE DATABASE college_db;
Query OK, 1 row affected (0.01 sec)
mysql> USE college_db;
Database changed
mysql>
mysql> CREATE TABLE Branch (
-> branch_id INT PRIMARY KEY,
-> branch_name VARCHAR(50) UNIQUE NOT NULL
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE TABLE Student (
-> student_id INT PRIMARY KEY,
-> student_name VARCHAR(50) NOT NULL,
-> cgpa DECIMAL(3,2),
-> branch_id INT,
-> FOREIGN KEY (branch_id) REFERENCES Branch(branch_id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE TABLE Course (
-> course_id INT PRIMARY KEY,
-> course_name VARCHAR(50) UNIQUE NOT NULL
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE TABLE Enrollment (
-> student_id INT,
-> course_id INT,
-> PRIMARY KEY (student_id, course_id),
-> FOREIGN KEY (student_id) REFERENCES Student(student_id),
-> FOREIGN KEY (course_id) REFERENCES Course(course_id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO Branch VALUES
-> (1, 'AI_DS'),
-> (2, 'CSE'),
-> (3, 'ECE');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Student VALUES
-> (101, 'Sahana', 8.20, 1),
-> (102, 'Sanjay', 9.20, 1),
-> (103, 'Kavya', 7.80, 1),
-> (104, 'Prasad', 8.60, 2),
-> (105, 'Shreya', 9.50, 2),
-> (106, 'Rohan', 7.20, 3),
-> (107, 'Sagar', 8.00, 1),
-> (108, 'Charan', 9.10, 2),
-> (109, 'Swathi', 8.40, 3);
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Course VALUES
-> (1, 'DBMS'),
-> (2, 'Java'),
-> (3, 'Python'),
-> (4, 'Operating Systems');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Enrollment VALUES
-> (101, 1),
-> (101, 2),
-> (102, 1),
-> (103, 3),
-> (104, 1),
-> (105, 2),
-> (106, 4),
-> (107, 1),
-> (108, 3),
-> (109, 1);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SELECT *
-> FROM Student
-> WHERE cgpa BETWEEN 7.5 AND 8.5;
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 101 | Sahana | 8.20 | 1 |
| 103 | Kavya | 7.80 | 1 |
| 107 | Sagar | 8.00 | 1 |
| 109 | Swathi | 8.40 | 3 |
+------------+--------------+------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT s.*
-> FROM Student s
-> JOIN Enrollment e ON s.student_id = e.student_id
-> JOIN Course c ON e.course_id = c.course_id
-> WHERE c.course_name = 'DBMS';
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 101 | Sahana | 8.20 | 1 |
| 102 | Sanjay | 9.20 | 1 |
| 104 | Prasad | 8.60 | 2 |
| 107 | Sagar | 8.00 | 1 |
| 109 | Swathi | 8.40 | 3 |
+------------+--------------+------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT s.*
-> FROM Student s
-> JOIN Branch b ON s.branch_id = b.branch_id
-> WHERE b.branch_name = 'AI_DS';
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 101 | Sahana | 8.20 | 1 |
| 102 | Sanjay | 9.20 | 1 |
| 103 | Kavya | 7.80 | 1 |
| 107 | Sagar | 8.00 | 1 |
+------------+--------------+------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT s.*
-> FROM Student s
-> JOIN Branch b ON s.branch_id = b.branch_id
-> WHERE b.branch_name = 'AI_DS'
-> AND s.cgpa > 9;
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 102 | Sanjay | 9.20 | 1 |
+------------+--------------+------+-----------+
1 row in set (0.00 sec)
mysql> SELECT *
-> FROM Student
-> WHERE student_name LIKE 'S%';
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 101 | Sahana | 8.20 | 1 |
| 102 | Sanjay | 9.20 | 1 |
| 105 | Shreya | 9.50 | 2 |
| 107 | Sagar | 8.00 | 1 |
| 109 | Swathi | 8.40 | 3 |
+------------+--------------+------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM Student
-> WHERE student_name LIKE '__a%';
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 104 | Prasad | 8.60 | 2 |
| 108 | Charan | 9.10 | 2 |
| 109 | Swathi | 8.40 | 3 |
+------------+--------------+------+-----------+
3 rows in set (0.00 sec)
mysql> SHOW TABLES;
+----------------------+
| Tables_in_college_db |
+----------------------+
| branch |
| course |
| enrollment |
| student |
+----------------------+
4 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM Student;
+------------+--------------+------+-----------+
| student_id | student_name | cgpa | branch_id |
+------------+--------------+------+-----------+
| 101 | Sahana | 8.20 | 1 |
| 102 | Sanjay | 9.20 | 1 |
| 103 | Kavya | 7.80 | 1 |
| 104 | Prasad | 8.60 | 2 |
| 105 | Shreya | 9.50 | 2 |
| 106 | Rohan | 7.20 | 3 |
| 107 | Sagar | 8.00 | 1 |
| 108 | Charan | 9.10 | 2 |
| 109 | Swathi | 8.40 | 3 |
+------------+--------------+------+-----------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM Branch;
+-----------+-------------+
| branch_id | branch_name |
+-----------+-------------+
| 1 | AI_DS |
| 2 | CSE |
| 3 | ECE |
+-----------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM Course;
+-----------+-------------------+
| course_id | course_name |
+-----------+-------------------+
| 1 | DBMS |
| 2 | Java |
| 4 | Operating Systems |
| 3 | Python |
+-----------+-------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM Enrollment;
+------------+-----------+
| student_id | course_id |
+------------+-----------+
| 101 | 1 |
| 102 | 1 |
| 104 | 1 |
| 107 | 1 |
| 109 | 1 |
| 101 | 2 |
| 105 | 2 |
| 103 | 3 |
| 108 | 3 |
| 106 | 4 |
+------------+-----------+
10 rows in set (0.00 sec)⚠️Content was pasted as plain text and auto-formatted as a code block. Use the Code Block button in the editor for proper formatting.