PRACTICAL_NO:2
Problem statement:
Design and develop SQL DDL statements which demonstrate use of sql objects
such as table,index,view,sequence,synonyms and different constraints.
Queries:
1. Create Database:
mysql> create database info2;
Query OK, 1 row affected (0.16 sec)
2. Using database:
mysql> use info2;
Database changed
3. Create table:
mysql> create table info2(Roll_no int, Name varchar(50), Age int);
Query OK, 0 rows affected (0.94 sec)
4. Description of the table:
mysql> DESC info2;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Roll_no | int | YES | | NULL | |
| Name | varchar(50) | YES | | NULL | |
| Age | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
5. Insert records to the table:
mysql> insert into info2 values(1,'Rishu Singh',21),(2,'Sandip Pawar',21),(3, 'Manasi
Shivarkar', 20);
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
6. Display the records:
7. Add new column:
mysql> alter table info2 add City varchar(50);
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0
8. Change the column name:
mysql> alter table info2 change Name Full_Name varchar(50);
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
9. Deleting particular record:
mysql> delete from info2 where Roll_no=4;
Query OK, 1 row affected (0.11 sec)
10. Deleting all records only:
mysql> truncate table info2;
Query OK, 0 rows affected (1.48 sec)
11. Deleting whole table:
mysql> drop table info2;
Query OK, 0 rows affected (0.39 sec)
mysql> select * from info2;
ERROR 1146 (42S02): Table 'info2.info2' doesn't exist
Different Constraints:
1. NOT NULL CONSTRAINTS:
mysql> create table info3(Roll_no int not null, Name varchar(50));
Query OK, 0 rows affected (0.63 sec)
2. UNIQUE Constraints:
1. For One Column:
mysql> alter table info3 add unique(Roll_no);
Query OK, 0 rows affected (1.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
2. For Multiple Column:
mysql> alter table info3 add constraint UC_info3 unique(Roll_no,Name);
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
3. PRIMARY KEY Constraints:
mysql> alter table info3 add primary key(Roll_no);
Query OK, 0 rows affected (2.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. FOREIGN KEY Constraints:
mysql> create table info4(Roll_no int, LastName varchar(50), Order_no int, primary
key(Order_no), constraint c1 foreign key(Roll_no) references info3(Roll_no));
Query OK, 0 rows affected (1.37 sec)
5. CHECK Constraints:
6. DEFAULT Constraints:
mysql> alter table info3 alter age set default 17;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
