Download to read offline
Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym
- 1. Practical No:2 Problem Statement:Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym ##Table *Create database mysql> create database prac2; Query OK, 1 row affected (0.00 sec) mysql> use prac2; Database changed mysql> create table stud(Roll int primary key,Name varchar(25)); Query OK, 0 rows affected (0.07 sec) *Create table mysql> create table stud(Roll int primary key,Name varchar(25)); Query OK, 0 rows affected (0.07 sec) mysql> desc stud; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Roll | int(11) | NO | PRI | NULL | | | Name | varchar(25) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) *alter table mysql> alter table stud add sub varchar(12); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stud; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Roll | int(11) | NO | PRI | NULL | | | Name | varchar(25) | YES | | NULL | | | Marks | int(10) | YES | | NULL | | | sub | varchar(12) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table stud rename seb; Query OK, 0 rows affected (0.06 sec) mysql> desc seb; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Roll | int(11) | NO | PRI | NULL | | | Name | varchar(25) | YES | | NULL | | | Marks | int(10) | YES | | NULL | | | sub | varchar(12) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table seb modify Roll int(20);
- 2. Query OK, 0rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc seb; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Roll | int(20) | NO | PRI | 0 | | | Name | varchar(25) | YES | | NULL | | | Marks | int(10) | YES | | NULL | | | sub | varchar(12) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table seb change sub Subject varchar(20); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc seb; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | Roll | int(20) | NO | PRI | 0 | | | Name | varchar(25) | YES | | NULL | | | Marks | int(10) | YES | | NULL | | | Subject | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table seb drop Subject; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc seb; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Roll | int(20) | NO | PRI | 0 | | | Name | varchar(25) | YES | | NULL | | | Marks | int(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) *Truncate truncate table stud; *Drop table Drop table stud; -------------------------------------------------------------------------------- -------------- ##View mysql> use prac2; Database changed mysql> select Name,RollNo from stud; +------+--------+ | Name | RollNo | +------+--------+ | xyz | 1 | | pqr | 2 |
- 3. | abc |3 | | a | 4 | +------+--------+ 4 rows in set (0.00 sec) mysql> select Name,RollNo from stud where Dept_no=1; +------+--------+ | Name | RollNo | +------+--------+ | xyz | 1 | | abc | 3 | +------+--------+ 2 rows in set (0.01 sec) mysql> create or replace view stud_CSE as select Name,RollNo from stud where Dept_no=1; Query OK, 0 rows affected (0.03 sec) mysql> select * from stud_CSE; +------+--------+ | Name | RollNo | +------+--------+ | xyz | 1 | | abc | 3 | +------+--------+ 2 rows in set (0.00 sec) mysql> create view v2 as select Name, RollNo, Dname from stud, Dept; Query OK, 0 rows affected (0.03 sec) mysql> select * from v2; +------+--------+-------+ | Name | RollNo | Dname | +------+--------+-------+ | xyz | 1 | CSE | | xyz | 1 | ENTC | | xyz | 1 | Civil | | pqr | 2 | CSE | | pqr | 2 | ENTC | | pqr | 2 | Civil | | abc | 3 | CSE | | abc | 3 | ENTC | | abc | 3 | Civil | | a | 4 | CSE | | a | 4 | ENTC | | a | 4 | Civil | +------+--------+-------+ 12 rows in set (0.00 sec) mysql> select * from stud, Dept; +--------+------+---------+---------+-------+ | RollNo | Name | dept_no | Dept_no | Dname | +--------+------+---------+---------+-------+ | 1 | xyz | 1 | 1 | CSE | | 1 | xyz | 1 | 2 | ENTC | | 1 | xyz | 1 | 3 | Civil | | 2 | pqr | 2 | 1 | CSE | | 2 | pqr | 2 | 2 | ENTC | | 2 | pqr | 2 | 3 | Civil | | 3 | abc | 1 | 1 | CSE | | 3 | abc | 1 | 2 | ENTC | | 3 | abc | 1 | 3 | Civil |
- 4. | 4 |a | 3 | 1 | CSE | | 4 | a | 3 | 2 | ENTC | | 4 | a | 3 | 3 | Civil | +--------+------+---------+---------+-------+ 12 rows in set (0.00 sec) mysql> select * from stud; +--------+------+---------+ | RollNo | Name | dept_no | +--------+------+---------+ | 1 | xyz | 1 | | 2 | pqr | 2 | | 3 | abc | 1 | | 4 | a | 3 | +--------+------+---------+ 4 rows in set (0.00 sec) mysql> select * from Dept; +---------+-------+ | Dept_no | Dname | +---------+-------+ | 1 | CSE | | 2 | ENTC | | 3 | Civil | +---------+-------+ 3 rows in set (0.00 sec) -------------------------------------------------------------------------------- --------------- ##Sequence mysql> desc stud1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | Rno | int(4) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | phone | int(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into stud1 values(NULL, 'MNO', 3278648723); Query OK, 1 row affected, 1 warning (0.04 sec) mysql> select * from stud1; +-----+------+------------+ | Rno | name | phone | +-----+------+------------+ | 1 | ABC | 123434671 | | 2 | DEF | 126252671 | | 3 | GHI | 1234765687 | | 4 | JKL | 8654855571 | | 5 | MNO | 2147343647 | | 6 | MNO | 2465333647 | +-----+------+------------+ 6 rows in set (0.00 sec) mysql> alter table stud1 auto_increment=10; Query OK, 6 rows affected (0.23 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into stud1 values(NULL, 'PQR', 2147483647); Query OK, 1 row affected, 1 warning (0.03 sec)
- 5. mysql> select *from stud1; +-----+------+------------+ | Rno | name | phone | +-----+------+------------+ | 1 | ABC | 123434671 | | 2 | DEF | 126252671 | | 3 | GHI | 1234765687 | | 4 | JKL | 8654855571 | | 5 | MNO | 2147343647 | | 6 | MNO | 2465333647 | | 10 | PQR | 2147483647 | +-----+------+------------+ 7 rows in set (0.00 sec) mysql> create table stud2(Rno int primary key auto_increment, name varchar(10), phone int(10)); Query OK, 0 rows affected (0.08 sec) mysql> alter table stud2 auto_increment=100; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into stud2 values(NULL, 'ABCDEF', 2235423523); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from stud2; +-----+--------+------------+ | Rno | name | phone | +-----+--------+------------+ | 100 | ABCDEF | 2147483647 | +-----+--------+------------+ 1 row in set (0.00 sec) mysql> insert into stud2 values(NULL, 'ABCDEF', 22354); Query OK, 1 row affected (0.05 sec) mysql> select * from stud2; +-----+--------+------------+ | Rno | name | phone | +-----+--------+------------+ | 100 | ABCDEF | 2147483647 | | 101 | ABCDEF | 22354 | +-----+--------+------------+ 2 rows in set (0.00 sec) -------------------------------------------------------------------------------- --------------------------- ##Index mysql> create index idx1 on stud2(name); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stud2; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | Rno | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | MUL | NULL | | | phone | int(10) | YES | | NULL | |
- 6. +-------+-------------+------+-----+---------+----------------+ 3 rows inset (0.00 sec) mysql> show index from stud2; +-------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+--------------- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+--------------- + | stud2 | 0 | PRIMARY | 1 | Rno | A | 2 | NULL | NULL | | BTREE | | | | stud2 | 1 | idx1 | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+--------------- + 2 rows in set (0.00 sec) mysql> drop index idx1 on stud2; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from stud2; +-------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+--------------- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+--------------- + | stud2 | 0 | PRIMARY | 1 | Rno | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+--------------- + 1 row in set (0.00 sec)