Scribd
Upload
57 views4 pages

SQL Program 1

A MySQL database named 'SCHOOL' is created with a table 'marks' to store student marks across various subjects. Several records are inserted into the table, and additional fields for total marks and percentage are added and calculated. Queries are executed to retrieve students with percentages above 60 and to order the results by percentage.

Uploaded by

Vidyashree Vidya
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
57 views4 pages

SQL Program 1

A MySQL database named 'SCHOOL' is created with a table 'marks' to store student marks across various subjects. Several records are inserted into the table, and additional fields for total marks and percentage are added and calculated. Queries are executed to retrieve students with percentages above 60 and to order the results by percentage.

Uploaded by

Vidyashree Vidya
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
You are on page 1/ 4

mysql> CREATE DATABASE SCHOOL;

Query OK, 1 row affected (0.01 sec)

mysql> USE SCHOOL;


Database changed
mysql> CREATE TABLE marks ( Rollno INT(5), Sname VARCHAR(15) NOT NULL, Lang_mks
INT(3) CHECK (Lang_mks BETWEEN 0 AND 100), Eng_mks INT(3) CHECK (Eng_mks BETWEEN 0
AND 100), Sub1_mks INT(3) CHECK (Sub1_mks BETWEEN 0 AND 100), Sub2_mks INT(3) CHECK
(Sub2_mks BETWEEN 0 AND 100), Sub3_mks INT(3) CHECK (Sub3_mks BETWEEN 0 AND 100),
Sub4_mks INT(3) CHECK (Sub4_mks BETWEEN 0 AND 100));
Query OK, 0 rows affected, 7 warnings (0.04 sec)

mysql> INSERT INTO marks VALUES(1010, 'RAJ', 89, 97, 98, 99, 86, 95);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO marks VALUES (1026, 'KIRAN', 67, 62, 72, 86, 72, 62);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO marks VALUES (1042, 'ANAND', 78, 87, 92, 82, 72, 7);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO marks VALUES (1250, 'RAM', 72, 86, 72, 62, 87, 68);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO marks VALUES (5212, 'VIJAYA', 46, 58, 86, 92, 72, 62);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO marks VALUES (3622, 'MANOJ', 86, 56, 62, 86, 52, 64);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO marks VALUES (1948, 'REEHAN', 63, 68, 52, 56, 96, 76);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO marks VALUES (1482, 'KAJOL', 49, 54, 48, 76, 62, 55);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO marks VALUES (1947, 'KUMAR', 98, 98, 99, 100, 97, 99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from marks;


+--------+--------+----------+---------+----------+----------+----------+----------
+
| Rollno | Sname | Lang_mks | Eng_mks | Sub1_mks | Sub2_mks | Sub3_mks | Sub4_mks
|
+--------+--------+----------+---------+----------+----------+----------+----------
+
| 1010 | RAJ | 89 | 97 | 98 | 99 | 86 | 95
|
| 1026 | KIRAN | 67 | 62 | 72 | 86 | 72 | 62
|
| 1042 | ANAND | 78 | 87 | 92 | 82 | 72 | 7
|
| 1250 | RAM | 72 | 86 | 72 | 62 | 87 | 68
|
| 5212 | VIJAYA | 46 | 58 | 86 | 92 | 72 | 62
|
| 3622 | MANOJ | 86 | 56 | 62 | 86 | 52 | 64
|
| 1948 | REEHAN | 63 | 68 | 52 | 56 | 96 | 76
|
| 1482 | KAJOL | 49 | 54 | 48 | 76 | 62 | 55
|
| 1947 | KUMAR | 98 | 98 | 99 | 100 | 97 | 99
|
+--------+--------+----------+---------+----------+----------+----------+----------
+
9 rows in set (0.00 sec)

mysql> describe marks;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Rollno | int | YES | | NULL | |
| Sname | varchar(15) | NO | | NULL | |
| Lang_mks | int | YES | | NULL | |
| Eng_mks | int | YES | | NULL | |
| Sub1_mks | int | YES | | NULL | |
| Sub2_mks | int | YES | | NULL | |
| Sub3_mks | int | YES | | NULL | |
| Sub4_mks | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

mysql> alter table marks add(total int(3), percent float(5,3));


Query OK, 0 rows affected, 2 warnings (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 2

mysql> describe marks;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Rollno | int | YES | | NULL | |
| Sname | varchar(15) | NO | | NULL | |
| Lang_mks | int | YES | | NULL | |
| Eng_mks | int | YES | | NULL | |
| Sub1_mks | int | YES | | NULL | |
| Sub2_mks | int | YES | | NULL | |
| Sub3_mks | int | YES | | NULL | |
| Sub4_mks | int | YES | | NULL | |
| total | int | YES | | NULL | |
| percent | float(5,3) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

mysql> select * from marks;


+--------+--------+----------+---------+----------+----------+----------+----------
+-------+---------+
| Rollno | Sname | Lang_mks | Eng_mks | Sub1_mks | Sub2_mks | Sub3_mks | Sub4_mks
| total | percent |
+--------+--------+----------+---------+----------+----------+----------+----------
+-------+---------+
| 1010 | RAJ | 89 | 97 | 98 | 99 | 86 | 95
| NULL | NULL |
| 1026 | KIRAN | 67 | 62 | 72 | 86 | 72 | 62
| NULL | NULL |
| 1042 | ANAND | 78 | 87 | 92 | 82 | 72 | 7
| NULL | NULL |
| 1250 | RAM | 72 | 86 | 72 | 62 | 87 | 68
| NULL | NULL |
| 5212 | VIJAYA | 46 | 58 | 86 | 92 | 72 | 62
| NULL | NULL |
| 3622 | MANOJ | 86 | 56 | 62 | 86 | 52 | 64
| NULL | NULL |
| 1948 | REEHAN | 63 | 68 | 52 | 56 | 96 | 76
| NULL | NULL |
| 1482 | KAJOL | 49 | 54 | 48 | 76 | 62 | 55
| NULL | NULL |
| 1947 | KUMAR | 98 | 98 | 99 | 100 | 97 | 99
| NULL | NULL |
+--------+--------+----------+---------+----------+----------+----------+----------
+-------+---------+
9 rows in set (0.00 sec)

mysql> update marks set total =


lang_mks+eng_mks+sub1_mks+sub2_mks+sub3_mks+sub4_mks;
Query OK, 9 rows affected (0.01 sec)
Rows matched: 9 Changed: 9 Warnings: 0

mysql> update marks set percent = total/600*100;


Query OK, 9 rows affected (0.01 sec)
Rows matched: 9 Changed: 9 Warnings: 0

mysql> select sname, percent from marks where percent >=60;


+--------+---------+
| sname | percent |
+--------+---------+
| RAJ | 94.000 |
| KIRAN | 70.167 |
| ANAND | 69.667 |
| RAM | 74.500 |
| VIJAYA | 69.333 |
| MANOJ | 67.667 |
| REEHAN | 68.500 |
| KUMAR | 98.500 |
+--------+---------+
8 rows in set (0.00 sec)

mysql> select sname, percent from marks where percent between 60 and 85;
+--------+---------+
| sname | percent |
+--------+---------+
| KIRAN | 70.167 |
| ANAND | 69.667 |
| RAM | 74.500 |
| VIJAYA | 69.333 |
| MANOJ | 67.667 |
| REEHAN | 68.500 |
+--------+---------+
6 rows in set (0.00 sec)

mysql> select sname, percent from marks order by percent desc;


+--------+---------+
| sname | percent |
+--------+---------+
| KUMAR | 98.500 |
| RAJ | 94.000 |
| RAM | 74.500 |
| KIRAN | 70.167 |
| ANAND | 69.667 |
| VIJAYA | 69.333 |
| REEHAN | 68.500 |
| MANOJ | 67.667 |
| KAJOL | 57.333 |
+--------+---------+
9 rows in set (0.00 sec)

You might also like