KEMBAR78
SQL | PDF | Table (Database) | Databases
Scribd
Upload
8 views4 pages

SQL

The document outlines the creation and modification of a MySQL database named 'school', including the creation of tables for 'guardian', 'student', and 'attendance'. It details the structure of these tables, including fields, data types, and constraints, as well as various alterations made to the tables throughout the process. The document concludes with the dropping of the 'attendance' table and the 'school' database itself.

Uploaded by

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

SQL

The document outlines the creation and modification of a MySQL database named 'school', including the creation of tables for 'guardian', 'student', and 'attendance'. It details the structure of these tables, including fields, data types, and constraints, as well as various alterations made to the tables throughout the process. The document concludes with the dropping of the 'attendance' table and the 'school' database itself.

Uploaded by

longstoryshortisurvivedts
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

ASSIGNMENT-1

DIVANSHI ARORA XII-G


mysql> create database school ;
Query OK, 1 row affected (0.00 sec)

mysql> use school ;


Database changed
mysql> create table guardian
-> (
-> GUID CHAR(12) primary key not null ,
-> GNAME VARCHAR(20) not null ,
-> GPHONE CHAR(10) unique,
-> GADDRESS Varchar(25) not null
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc guardian ;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| GUID | char(12) | NO | PRI | NULL | |
| GNAME | varchar(20) | NO | | NULL | |
| GPHONE | char(10) | YES | UNI | NULL | |
| GADDRESS | varchar(25) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table guardian


-> add(GINCOME float);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc guardian ;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| GUID | char(12) | NO | PRI | NULL | |
| GNAME | varchar(20) | NO | | NULL | |
| GPHONE | char(10) | YES | UNI | NULL | |
| GADDRESS | varchar(25) | NO | | NULL | |
| GINCOME | float | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table guardian


-> add( Gmail varchar(20) ,Gpin int);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc guardian ;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| GUID | char(12) | NO | PRI | NULL | |
| GNAME | varchar(20) | NO | | NULL | |
| GPHONE | char(10) | YES | UNI | NULL | |
| GADDRESS | varchar(25) | NO | | NULL | |
| GINCOME | float | YES | | NULL | |
| Gmail | varchar(20) | YES | | NULL | |
| Gpin | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table guardian


-> drop gincome;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc guardian;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| GUID | char(12) | NO | PRI | NULL | |
| GNAME | varchar(20) | NO | | NULL | |
| GPHONE | char(10) | YES | UNI | NULL | |
| GADDRESS | varchar(25) | NO | | NULL | |
| Gmail | varchar(20) | YES | | NULL | |
| Gpin | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table guardian


-> drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc guardian ;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| GUID | char(12) | NO | | NULL | |
| GNAME | varchar(20) | NO | | NULL | |
| GPHONE | char(10) | YES | UNI | NULL | |
| GADDRESS | varchar(25) | NO | | NULL | |
| Gmail | varchar(20) | YES | | NULL | |
| Gpin | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table guardian


-> modify Gaddress varchar(30);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc guardian;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| GUID | char(12) | NO | | NULL | |
| GNAME | varchar(20) | NO | | NULL | |
| GPHONE | char(10) | YES | UNI | NULL | |
| Gaddress | varchar(30) | YES | | NULL | |
| Gmail | varchar(20) | YES | | NULL | |
| Gpin | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> create table student


-> (
-> rollnumber int,
-> sname varchar(20) ,
-> sdateofbirth date not null,
-> guid char(12) references guardian (guid)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc student ;


+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| rollnumber | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sdateofbirth | date | NO | | NULL | |
| guid | char(12) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student


-> add primary key(rollnumber);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student ;


+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| rollnumber | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| sdateofbirth | date | NO | | NULL | |
| guid | char(12) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student


-> modify sname varchar(20) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student ;


+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| rollnumber | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | NULL | |
| sdateofbirth | date | NO | | NULL | |
| guid | char(12) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student


-> modify sdateofbirth date default '2022-05-15';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student ;


+--------------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+------------+-------+
| rollnumber | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | NULL | |
| sdateofbirth | date | YES | | 2022-05-15 | |
| guid | char(12) | YES | | NULL | |
+--------------+-------------+------+-----+------------+-------+
4 rows in set (0.00 sec)

mysql> create table attendance


-> (
-> attendancedate date,
-> rollnumber int references student (rollnumber),
-> attendancestatus char(1),
-> primary key (attendancedate , rollnumber)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc attendance;


+------------------+---------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+------------+-------+
| attendancedate | date | NO | PRI | 0000-00-00 | |
| rollnumber | int(11) | NO | PRI | 0 | |
| attendancestatus | char(1) | YES | | NULL | |
+------------------+---------+------+-----+------------+-------+
3 rows in set (0.00 sec)

mysql> drop table attendance;


Query OK, 0 rows affected (0.01 sec)
mysql> desc attendance;
ERROR 1146 (42S02): Table 'school.attendance' doesn't exist
mysql> drop database school ;
Query OK, 2 rows affected (0.03 sec)

mysql> desc school ;


ERROR 1046 (3D000): No database selected

You might also like