Scribd
Upload
21 views5 pages

SQL Student Marks Management

The document details SQL commands used to create, populate, modify and query a database table called 'student'. It creates the student table with columns like rollno, name etc. and inserts sample records. It then alters the table by adding and dropping columns, updates data, orders and groups records. Statistical functions like min, max, sum, count and average are applied on the 'telugu' column.

Uploaded by

Hiho Ha
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
21 views5 pages

SQL Student Marks Management

The document details SQL commands used to create, populate, modify and query a database table called 'student'. It creates the student table with columns like rollno, name etc. and inserts sample records. It then alters the table by adding and dropping columns, updates data, orders and groups records. Statistical functions like min, max, sum, count and average are applied on the 'telugu' column.

Uploaded by

Hiho Ha
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

#Switched to a database

mysql> USE GVKCV;


Database changed

#Creating table student


mysql> create table student
-> (ROLLNO INT NOT NULL PRIMARY KEY,
-> NAME CHAR(10),
-> TELUGU CHAR(10),
-> HINDI CHAR(10),
-> MATHS CHAR(10));
Query OK, 0 rows affected (1.38 sec)

#Inserting values into table


mysql> insert into student
-> values(101,"student1",50,51,52),
-> (102,"student2",60,61,62),
-> (103,"student3",70,71,72),
-> (104,"student4",80,81,82),
-> (105,"student5",90,91,92),
-> (106,"student6",40,41,42),
-> (107,"student7",63,64,65);
Query OK, 7 rows affected (0.24 sec)
Records: 7 Duplicates: 0 Warnings: 0

#Adding new attribute computers


mysql> alter table student
-> add (computers char(10));
Query OK, 0 rows affected (1.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

#Describing table
mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
| computers | char(10) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
6 rows in set (0.21 sec)

#Modifying the datatype


mysql> alter table student
-> modify column computers varchar(10);
Query OK, 7 rows affected (2.38 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> desc student;


+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
| computers | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.11 sec)

#Droping a attribute
mysql> alter table student
-> drop column computers;
Query OK, 0 rows affected (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 0

#Describing table
mysql> desc student;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.14 sec)

#UPDATE DATA TO MODIFY DATA


#ACTUAL DATA
mysql> select *from student;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 101 | student1 | 50 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)

#UPDATE THE MARKS FOR ATTRIBUTE TELUGU FOR THE STUDENT101


mysql> UPDATE STUDENT
-> SET TELUGU=99
-> WHERE ROLLNO=101;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#DATA IN THE TABLE AFTER UPDATING


mysql> SELECT *FROM STUDENT;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 101 | student1 | 99 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)

#ORDER BY DESCENDING ORDER


mysql> SELECT *FROM STUDENT
-> ORDER BY HINDI DESC;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 105 | student5 | 90 | 91 | 92 |
| 104 | student4 | 80 | 81 | 82 |
| 103 | student3 | 70 | 71 | 72 |
| 107 | student7 | 63 | 64 | 65 |
| 102 | student2 | 60 | 61 | 62 |
| 101 | student1 | 99 | 51 | 52 |
| 106 | student6 | 40 | 41 | 42 |
+--------+----------+--------+-------+-------+
7 rows in set (0.05 sec)

#ORDER BY ASCENDING ORDER


mysql> SELECT *FROM STUDENT
-> ORDER BY HINDI ASC;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 106 | student6 | 40 | 41 | 42 |
| 101 | student1 | 99 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 107 | student7 | 63 | 64 | 65 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)

#DELETING A TUPLE FROM THE TABLE


mysql> DELETE FROM STUDENT
-> WHERE ROLLNO=101;
Query OK, 1 row affected (0.14 sec)

mysql> SELECT *FROM STUDENT;


+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
6 rows in set (0.06 sec)

#ORDER BY BRANCH

#ACTUAL DATA
mysql> SELECT *FROM STUDENT;
+--------+--------+----------+--------+-------+-------+
| ROLLNO | BRANCH | NAME | TELUGU | HINDI | MATHS |
+--------+--------+----------+--------+-------+-------+
| 102 | MPC | student2 | 60 | 61 | 62 |
| 103 | BIPC | student3 | 70 | 71 | 72 |
| 104 | BIPC | student4 | 80 | 81 | 82 |
| 105 | BIPC | student5 | 90 | 91 | 92 |
| 106 | BIPC | student6 | 40 | 41 | 42 |
| 107 | MPC | student7 | 63 | 64 | 65 |
+--------+--------+----------+--------+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT BRANCH,COUNT(*)


-> FROM STUDENT
-> GROUP BY BRANCH;
+--------+----------+
| BRANCH | COUNT(*) |
+--------+----------+
| MPC | 2 |
| BIPC | 4 |
+--------+----------+
2 rows in set (0.01 sec)

#e min, max, sum, count and average


mysql> SELECT MIN(TELUGU) "TELUGU MIN MARKS"
-> FROM STUDENT;
+------------------+
| TELUGU MIN MARKS |
+------------------+
| 40 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(TELUGU) "TELUGU MAX MARKS"


-> FROM STUDENT;
+------------------+
| TELUGU MAX MARKS |
+------------------+
| 90 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(TELUGU) "TELUGU TOTAL MARKS"


-> FROM STUDENT;
+--------------------+
| TELUGU TOTAL MARKS |
+--------------------+
| 403 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(ROLLNO)


-> FROM STUDENT;
+---------------+
| COUNT(ROLLNO) |
+---------------+
| 6 |
+---------------+
1 row in set (0.01 sec)

mysql> SELECT AVG(TELUGU) "TELUGU AVG MARKS"


-> FROM STUDENT;
+-------------------+
| TELUGU AVG MARKS |
+-------------------+
| 67.16666666666667 |
+-------------------+

Common questions

Powered by AI

The operation "ORDER BY HINDI DESC" sorts students by their Hindi marks in descending order, facilitating easy identification of top performers in Hindi. This helps in quickly analyzing which students excel in Hindi compared to peers, aiding in targeted interventions or recognitions .

Aggregate functions provide critical insights into data. MIN and MAX reveal performance range, SUM indicates total class effort, COUNT confirms class size, and AVG offers a mean benchmark for individual performance evaluation. These metrics collectively facilitate comprehensive academic performance assessment .

The 'Computers' field was initially added as a CHAR(10) data type, affecting the schema by increasing the number of fields from 5 to 6 . This field was later modified to a VARCHAR(10) to allow variable-length entries . Eventually, the 'Computers' field was dropped, reverting the schema back to its original number of fields .

Deleting student1's record reduces the total number of entries, impacting operations reliant on complete data like AVG calculations. To maintain consistency, recalculating aggregates or adjusting any referenced analyses (e.g., recalculating the average Telugu marks without student1's 99 score) is necessary .

Changing from CHAR(10) to VARCHAR(10) improves storage efficiency by allocating space based on actual input length rather than a fixed size, saving storage for entries shorter than 10 characters. This leads to better performance in data retrieval by reducing unnecessary data overhead .

Grouping by 'BRANCH' offers structured insights for resource allocation or curriculum adjustments in academic management. It aids in monitoring branch-specific performance, allowing tailored academic strategies or resources based on branch-specific needs .

Executing this DELETE operation removes all data associated with ROLLNO=101, potentially causing data loss if not backed up. It impacts analytical outputs, requiring recalibration of aggregate functions and report updates to prevent inaccuracies .

Using CHAR and VARCHAR can complicate data management due to differences in storage and performance. CHAR reserves fixed space, potentially wasting storage for shorter values, while VARCHAR saves space but requires additional overhead for length storage. This can lead to inconsistent retrieval times and challenges in handling varying data lengths .

Updating student1's Telugu marks from 50 to 99 corrected a potential error or reflected new information, enhancing data accuracy. It ensures the data set now reflects the correct academic standing of student1 with a significant increase in Telugu marks, potentially affecting the student's overall performance analysis .

The calculated average Telugu mark of 67.17 indicates moderate overall student performance. Academic implications may include identifying students below this benchmark for support or revising teaching methods if the average reflects a lower understanding of the subject .

You might also like