Database Management System - Practical Programmes

Practical No-1       Study of Open Source Relational Databases : MySQL

siem@siem-OptiPlex-390:~$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 37

Server version: 5.5.54-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| SIEM               |

| akshay             |

| as7                |

mysql> create database shubham;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| SIEM               |

| akshay             |

| as7                |

| shubham            |

mysql> use shubham;

Database changed

mysql> create table info(id int(10),name varchar(10),address varchar(20));

Query OK, 0 rows affected (0.06 sec)

mysql> show tables;

+-------------------+

| Tables_in_shubham |

+-------------------+

| info              |

+-------------------+

1 row in set (0.00 sec)

mysql> desc info;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| id      | int(10)     | YES  |     | NULL    |       |

| name    | varchar(10) | YES  |     | NULL    |       |

| address | varchar(20) | YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into info values ('1',"shubh","nashik");

Query OK, 1 row affected (0.04 sec)

mysql> insert into info values ('2',"tushar","trambak");

Query OK, 1 row affected (0.04 sec)

mysql> insert into info values('3',"hardik","trambak"),('4',"rahul","nashik"),('5',"rushi","pune"),('6',"jefin","nashik");

Query OK, 4 rows affected (0.04 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into info values('7',"lol","trambak"),('8',"papu","nashik");

Query OK, 2 rows affected (0.04 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;

+------+--------+---------+

| id   | name   | address |

+------+--------+---------+

|    1 | shubh  | nashik  |

|    2 | tushar | trambak |

|    3 | hardik | trambak |

|    4 | rahul  | nashik  |

|    5 | rushi  | pune    |

|    6 | jefin  | nashik  |

|    7 | lol    | trambak |

|    8 | papu   | nashik  |

+------+--------+---------+

8 rows in set (0.00 sec)

mysql> select jefin from info;

ERROR 1054 (42S22): Unknown column 'jefin' in 'field list'

mysql> select name from info;

+--------+

| name   |

+--------+

| shubh  |

| tushar |

| hardik |

| rahul  |

| rushi  |

| jefin  |

| lol    |

| papu   |

+--------+

8 rows in set (0.00 sec)

mysql> select id,name from info;

+------+--------+

| id   | name   |

+------+--------+

|    1 | shubh  |

|    2 | tushar |

|    3 | hardik |

|    4 | rahul  |

|    5 | rushi  |

|    6 | jefin  |

|    7 | lol    |

|    8 | papu   |

+------+--------+

8 rows in set (0.00 sec)

mysql> alter table info add column ph int(10);

Query OK, 8 rows affected (0.17 sec)

Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from info;

+------+--------+---------+------+

| id   | name   | address | ph   |

+------+--------+---------+------+

|    1 | shubh  | nashik  | NULL |

|    2 | tushar | trambak | NULL |

|    3 | hardik | trambak | NULL |

|    4 | rahul  | nashik  | NULL |

|    5 | rushi  | pune    | NULL |

|    6 | jefin  | nashik  | NULL |

|    7 | lol    | trambak | NULL |

|    8 | papu   | nashik  | NULL |

+------+--------+---------+------+

8 rows in set (0.00 sec)

mysql> select id where name='hardik';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where name='hardik'' at line 1

mysql> select id from info  where name='hardik';

+------+

| id   |

+------+

|    3 |

+------+

1 row in set (0.00 sec)

mysql> select id from info  where name='hardik' and address='nashik';

Empty set (0.00 sec)

mysql> select id from info  where name='shubh' and address='nashik';

+------+

| id   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

mysql> update info set ph=1234567890 where id=1;

Query OK, 1 row affected (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info

    -> ;

+------+--------+---------+------------+

| id   | name   | address | ph         |

+------+--------+---------+------------+

|    1 | shubh  | nashik  | 1234567890 |

|    2 | tushar | trambak |       NULL |

|    3 | hardik | trambak |       NULL |

|    4 | rahul  | nashik  |       NULL |

|    5 | rushi  | pune    |       NULL |

|    6 | jefin  | nashik  |       NULL |

|    7 | lol    | trambak |       NULL |

|    8 | papu   | nashik  |       NULL |

+------+--------+---------+------------+

8 rows in set (0.00 sec)

mysql> update info set ph=9833846241 where id=2;

Query OK, 1 row affected, 1 warning (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 1

mysql> update info set ph=5421367890 where id=3;

Query OK, 1 row affected, 1 warning (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 1

mysql> update info set ph=7748596120 where id=4;

Query OK, 1 row affected, 1 warning (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 1

mysql> update info set ph=6541237084 where id=5;

Query OK, 1 row affected, 1 warning (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 1

mysql> update info set ph=8574961234 where id=6;

Query OK, 1 row affected, 1 warning (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from info

    -> ;

+------+--------+---------+------------+

| id   | name   | address | ph         |

+------+--------+---------+------------+

|    1 | shubh  | nashik  | 1234567890 |

|    2 | tushar | trambak | 2147483647 |

|    3 | hardik | trambak | 2147483647 |

|    4 | rahul  | nashik  | 2147483647 |

|    5 | rushi  | pune    | 2147483647 |

|    6 | jefin  | nashik  | 2147483647 |

|    7 | lol    | trambak |       NULL |

|    8 | papu   | nashik  |       NULL |

+------+--------+---------+------------+

8 rows in set (0.00 sec)

mysql> update info set ph=986541237 where id=7;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update info set ph=124578963 where id=8;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;

+------+--------+---------+------------+

| id   | name   | address | ph         |

+------+--------+---------+------------+

|    1 | shubh  | nashik  | 1234567890 |

|    2 | tushar | trambak | 2147483647 |

|    3 | hardik | trambak | 2147483647 |

|    4 | rahul  | nashik  | 2147483647 |

|    5 | rushi  | pune    | 2147483647 |

|    6 | jefin  | nashik  | 2147483647 |

|    7 | lol    | trambak |  986541237 |

|    8 | papu   | nashik  |  124578963 |

+------+--------+---------+------------+

8 rows in set (0.00 sec)

mysql> update info set ph=123456789 where id=1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update info set ph=312457801 where id=2;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update info set ph=654879123 where id=3;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update info set ph=741852963 where id=4;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;

+------+--------+---------+-----------+

| id   | name   | address | ph        |

+------+--------+---------+-----------+

|    1 | shubh  | nashik  | 123456789 |

|    2 | tushar | trambak | 312457801 |

|    3 | hardik | trambak | 654879123 |

|    4 | rahul  | nashik  | 741852963 |

|    5 | rushi  | pune    | 341852963 |

|    6 | jefin  | nashik  | 415263789 |

|    7 | lol    | trambak | 986541237 |

|    8 | papu   | nashik  | 124578963 |

+------+--------+---------+-----------+

8 rows in set (0.00 sec)

mysql> rename table info to studinfo;

Query OK, 0 rows affected (0.04 sec)

mysql> select * from studinfo;

+------+--------+---------+-----------+

| id   | name   | address | ph        |

+------+--------+---------+-----------+

|    1 | shubh  | nashik  | 123456789 |

|    2 | tushar | trambak | 312457801 |

|    3 | hardik | trambak | 654879123 |

|    4 | rahul  | nashik  | 741852963 |

|    5 | rushi  | pune    | 341852963 |

|    6 | jefin  | nashik  | 415263789 |

|    7 | lol    | trambak | 986541237 |

|    8 | papu   | nashik  | 124578963 |

+------+--------+---------+-----------+

8 rows in set (0.00 sec)

mysql> delete from studinfo where name='lol';

Query OK, 1 row affected (0.05 sec)

mysql> select * from studinfo;

+------+--------+---------+-----------+

| id   | name   | address | ph        |

+------+--------+---------+-----------+

|    1 | shubh  | nashik  | 123456789 |

|    2 | tushar | trambak | 312457801 |

|    3 | hardik | trambak | 654879123 |

|    4 | rahul  | nashik  | 741852963 |

|    5 | rushi  | pune    | 341852963 |

|    6 | jefin  | nashik  | 415263789 |

|    8 | papu   | nashik  | 124578963 |

+------+--------+---------+-----------+

7 rows in set (0.00 sec)

mysql> alter table studinfo change address address1 varchar(10);

Query OK, 7 rows affected (0.19 sec)

Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from studinfo;

+------+--------+----------+-----------+

| id   | name   | address1 | ph        |

+------+--------+----------+-----------+

|    1 | shubh  | nashik   | 123456789 |

|    2 | tushar | trambak  | 312457801 |

|    3 | hardik | trambak  | 654879123 |

|    4 | rahul  | nashik   | 741852963 |

|    5 | rushi  | pune     | 341852963 |

|    6 | jefin  | nashik   | 415263789 |

|    8 | papu   | nashik   | 124578963 |

+------+--------+----------+-----------+

7 rows in set (0.00 sec)

mysql> alter table studinfo drop column ph;

Query OK, 7 rows affected (0.20 sec)

Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from studinfo;

+------+--------+----------+

| id   | name   | address1 |

+------+--------+----------+

|    1 | shubh  | nashik   |

|    2 | tushar | trambak  |

|    3 | hardik | trambak  |

|    4 | rahul  | nashik   |

|    5 | rushi  | pune     |

|    6 | jefin  | nashik   |

|    8 | papu   | nashik   |

+------+--------+----------+

7 rows in set (0.00 sec)

siem@siem-OptiPlex-390:~$ exit

Practical No-2 Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym

siem@siem-OptiPlex-390:~$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 39

Server version: 5.5.54-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| shubham |

+--------------------+

2 rows in set (0.15 sec)

mysql> use shubham;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-------------------+

| Tables_in_shubham |

+-------------------+

| studinfo          |

+-------------------+

1 row in set (0.00 sec)

mysql> select * from studinfo;

+------+--------+----------+

| id   | name   | address1 |

+------+--------+----------+

|    1 | shubh  | nashik   |

|    2 | tushar | trambak  |

|    3 | hardik | trambak  |

|    4 | rahul  | nashik   |

|    5 | rushi  | pune     |

|    6 | jefin  | nashik   |

|    8 | papu   | nashik   |

+------+--------+----------+

7 rows in set (0.02 sec)

mysql> create table nstud(acc_no int(100) AUTO_INCREMENT,PRIMARY KEY (acc_no),name varchar(20));

Query OK, 0 rows affected (0.11 sec)

mysql> select * from nstud;

Empty set (0.00 sec)

mysql> insert into nstud values("shubham");

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into nstud values('1',"shubham");

Query OK, 1 row affected (0.05 sec)

mysql> insert into nstud values("Roshan");

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into nstud values('',"Roshan");

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into nstud values('',"Rohit");

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select* from nstud

    -> ;

+--------+---------+

| acc_no | name    |

+--------+---------+

|      1 | shubham |

|      2 | Roshan  |

|      3 | Rohit   |

+--------+---------+

3 rows in set (0.00 sec)

mysql> insert into nstud values('',"mahajan");

Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into nstud values('',"rushi");

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into nstud values('',"pushkaraj");

Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select* from nstud;

+--------+-----------+

| acc_no | name      |

+--------+-----------+

|      1 | shubham   |

|      2 | Roshan    |

|      3 | Rohit     |

|      4 | mahajan   |

|      5 | rushi     |

|      6 | pushkaraj |

+--------+-----------+

6 rows in set (0.00 sec)

mysql> insert into nstud values('',"ashish");

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into nstud values('',"nisha");

Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> insert into nstud values('',"mayur");

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into nstud values('',"poonam");

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from nstud;

+--------+-----------+

| acc_no | name      |

+--------+-----------+

|      1 | shubham   |

|      2 | Roshan    |

|      3 | Rohit     |

|      4 | mahajan   |

|      5 | rushi     |

|      6 | pushkaraj |

|      7 | ashish    |

|      8 | nisha     |

|      9 | mayur     |

|     10 | poonam    |

+--------+-----------+

10 rows in set (0.00 sec)

mysql> select * from nstud where name like '%a';

+--------+-------+

| acc_no | name  |

+--------+-------+

|      8 | nisha |

+--------+-------+

1 row in set (0.00 sec)

mysql> select * from nstud where name like 'a%';

+--------+--------+

| acc_no | name   |

+--------+--------+

|      7 | ashish |

+--------+--------+

1 row in set (0.00 sec)

mysql> select * from nstud where name like '_a%';

+--------+---------+

| acc_no | name    |

+--------+---------+

|      4 | mahajan |

|      9 | mayur   |

+--------+---------+

2 rows in set (0.00 sec)

mysql> select * from nstud where name like '%a%';

+--------+-----------+

| acc_no | name      |

+--------+-----------+

|      1 | shubham   |

|      2 | Roshan    |

|      4 | mahajan   |

|      6 | pushkaraj |

|      7 | ashish    |

|      8 | nisha     |

|      9 | mayur     |

|     10 | poonam    |

+--------+-----------+

8 rows in set (0.00 sec)

mysql>  select * from nstud order by name asc;

+--------+-----------+

| acc_no | name      |

+--------+-----------+

|      7 | ashish    |

|      4 | mahajan   |

|      9 | mayur     |

|      8 | nisha     |

|     10 | poonam    |

|      6 | pushkaraj |

|      3 | Rohit     |

|      2 | Roshan    |

|      5 | rushi     |

|      1 | shubham   |

+--------+-----------+

10 rows in set (0.00 sec)

mysql>  select * from nstud order by name desc;

+--------+-----------+

| acc_no | name      |

+--------+-----------+

|      1 | shubham   |

|      5 | rushi     |

|      2 | Roshan    |

|      3 | Rohit     |

|      6 | pushkaraj |

|     10 | poonam    |

|      8 | nisha     |

|      9 | mayur     |

|      4 | mahajan   |

|      7 | ashish    |

+--------+-----------+

10 rows in set (0.00 sec)

mysql> select min(acc_no) from nstud;

+-------------+

| min(acc_no) |

+-------------+

|           1 |

+-------------+

1 row in set (0.00 sec)

mysql> select max(acc_no) from nstud;

+-------------+

| max(acc_no) |

+-------------+

|          10 |

+-------------+

1 row in set (0.00 sec)

mysql> select avg(acc_no) from nstud;

+-------------+

| avg(acc_no) |

+-------------+

|      5.5000 |

+-------------+

1 row in set (0.00 sec)

mysql> select sum(acc_no) from nstud;

+-------------+

| sum(acc_no) |

+-------------+

|          55 |

+-------------+

1 row in set (0.00 sec)

PRACTICAL NUMBER 3

mysql> create database assign1;

Query OK, 1 row affected (0.00 sec)

mysql> use assign1;

Database changed

mysql> create table emp(Id int(20),Name char(40),DOB char(40),Designation char(40),Join_date char(40),Salary int(20),Department char

    -> (40));

Query OK, 0 rows affected (0.19 sec)

mysql> desc emp;

+————-+———-+——+—–+———+——-+

| Field       | Type     | Null | Key | Default | Extra |

+————-+———-+——+—–+———+——-+

| Id          | int(20)  | YES  |     | NULL    |       |

| Name        | char(40) | YES  |     | NULL    |       |

| DOB         | char(40) | YES  |     | NULL    |       |

| Designation | char(40) | YES  |     | NULL    |       |

| Join_date   | char(40) | YES  |     | NULL    |       |

| Salary      | int(20)  | YES  |     | NULL    |       |

| Department  | char(40) | YES  |     | NULL    |       |

+————-+———-+——+—–+———+——-+

7 rows in set (0.04 sec)

mysql> alter table emp add primary key (Id);

Query OK, 0 rows affected (0.26 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;

+————-+———-+——+—–+———+——-+

| Field       | Type     | Null | Key | Default | Extra |

+————-+———-+——+—–+———+——-+

| Id          | int(20)  | NO   | PRI | 0       |       |

| Name        | char(40) | YES  |     | NULL    |       |

| DOB         | char(40) | YES  |     | NULL    |       |

| Designation | char(40) | YES  |     | NULL    |       |

| Join_date   | char(40) | YES  |     | NULL    |       |

| Salary      | int(20)  | YES  |     | NULL    |       |

| Department  | char(40) | YES  |     | NULL    |       |

+————-+———-+——+—–+———+——-+

7 rows in set (0.00 sec)

mysql> insert into emp values(101,”Arbaaz”,”25-09-1998″,”Manager”,”15-02-2014″,35000,”Computer”);

Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(102,”Navin”,”1-06-1998″,”Developer”,”28-07-2016″,5000,”Electronics”);

Query OK, 1 row affected (0.33 sec)

mysql> insert into emp values(103,”Iram”,”12-09-1998″,”Tester”,”01-11-2015″,25000,”Electronics”);

Query OK, 1 row affected (0.04 sec)

mysql> insert into emp values(104,”Nikul”,”20-12-1998″,”Developer”,”25-03-2014″,15000,”Computer”);

Query OK, 1 row affected (0.34 sec)

mysql> select * from emp;

+—–+——–+————+————-+————+——–+————-+

| Id  | Name   | DOB        | Designation | Join_date  | Salary | Department  |

+—–+——–+————+————-+————+——–+————-+

| 101 | Arbaaz | 25-09-1998 | Manager     | 15-02-2014 |  35000 | Computer    |

| 102 | Navin  | 1-06-1998  | Developer   | 28-07-2016 |   5000 | Electronics |

| 103 | Iram   | 12-09-1998 | Tester      | 01-11-2015 |  25000 | Electronics |

| 104 | Nikul  | 20-12-1998 | Developer   | 25-03-2014 |  15000 | Computer    |

+—–+——–+————+————-+————+——–+————-+

4 rows in set (0.00 sec)

mysql> select * from emp where Department=”Computer”;

+—–+——–+————+————-+————+——–+————+

| Id  | Name   | DOB        | Designation | Join_date  | Salary | Department |

+—–+——–+————+————-+————+——–+————+

| 101 | Arbaaz | 25-09-1998 | Manager     | 15-02-2014 |  35000 | Computer   |

| 104 | Nikul  | 20-12-1998 | Developer   | 25-03-2014 |  15000 | Computer   |

+—–+——–+————+————-+————+——–+————+

2 rows in set (0.00 sec)

mysql> select * from emp where Salary>20000;

+—–+——–+————+————-+————+——–+————-+

| Id  | Name   | DOB        | Designation | Join_date  | Salary | Department  |

+—–+——–+————+————-+————+——–+————-+

| 101 | Arbaaz | 25-09-1998 | Manager     | 15-02-2014 |  35000 | Computer    |

| 103 | Iram   | 12-09-1998 | Tester      | 01-11-2015 |  25000 | Electronics |

+—–+——–+————+————-+————+——–+————-+

2 rows in set (0.04 sec) 

mysql> delete from emp where Designation=”Manager”;

Query OK, 1 row affected (0.03 sec)

mysql> select * from emp;

+—–+——-+————+————-+————+——–+————-+

| Id  | Name  | DOB        | Designation | Join_date  | Salary | Department  |

+—–+——-+————+————-+————+——–+————-+

| 102 | Navin | 1-06-1998  | Developer   | 28-07-2016 |   5000 | Electronics |

| 103 | Iram  | 12-09-1998 | Tester      | 01-11-2015 |  25000 | Electronics |

| 104 | Nikul | 20-12-1998 | Developer   | 25-03-2014 |  15000 | Computer    |

+—–+——-+————+————-+————+——–+————-+

3 rows in set (0.00 sec)      

mysql> select Name from emp where Salary=(select min(Salary) from emp);

+——-+

| Name  |

+——-+

| Navin |

+——-+

1 row in set (0.00 sec)

mysql> select Name from emp where Salary=(select max(Salary) from emp);

+——+

| Name |

+——+

| Iram |

+——+

1 row in set (0.00 sec)

mysql> select avg(Salary) from emp;

+————-+

| avg(Salary) |

+————-+

|  15000.0000 |

+————-+

1 row in set (0.01 sec)

mysql> select avg(Salary) from emp where Department=’Electronics’;

+————-+

| avg(Salary) |

+————-+

|  15000.0000 |

+————-+

1 row in set (0.30 sec)

mysql> select count(Id) from emp where Department=”Computer”;

+———–+

| count(Id) |

+———–+

|         1 |

+———–+

1 row in set (0.00 sec)

mysql> select Name from emp where Name like “N%l”;

+——-+

| Name  |

+——-+

| Nikul |

+——-+

1 row in set (0.00 sec)

mysql> select Name from emp where Salary BETWEEN 10000 AND 80000;

+——-+

| Name  |

+——-+

| Iram  |

| Nikul |

+——-+

2 rows in set (0.00 sec)

mysql> select Name from emp where Salary in(5000,20000,30000,50000);

+——-+

| Name  |

+——-+

| Navin |

+——-+

1 row in set (0.01 sec)

mysql> select Name from emp where Department=”ELectronics”;

+——-+

| Name  |

+——-+

| Navin |

| Iram  |

+——-+

2 rows in set (0.00 sec)

mysql> create table emp1(Id int(20),Name char(40),DOB char(40),Designation char(40),Join_date char(40),Salary int(20),Department char  (40));

Query OK, 0 rows affected (0.14 sec)

mysql> insert into emp1 values(101,”Roger”,”02-07-1997″,”Developer”,”29-04-2016″,”5000″,”ENTC”);

Query OK, 1 row affected (0.02 sec)

mysql> insert into emp1 values(111,”Steve”,”15-09-1999″,”Tester”,”31-10-2013″,”30000″,”Computer”);

Query OK, 1 row affected (0.05 sec)

mysql> select * from emp1;

+——+——-+————+————-+————+——–+————+

| Id   | Name  | DOB        | Designation | Join_date  | Salary | Department |

+——+——-+————+————-+————+——–+————+

|  101 | Roger | 02-07-1997 | Developer   | 29-04-2016 |   5000 | ENTC       |

|  111 | Steve | 15-09-1999 | Tester      | 31-10-2013 |  30000 | Computer   |

+——+——-+————+————-+————+——–+————+

2 rows in set (0.00 sec)

mysql> select Id from emp UNION select Id from emp1;

+——+

| Id   |

+——+

|  102 |

|  103 |

|  104 |

|  101 |

|  111 |

+——+

5 rows in set (0.01 sec)

mysql> insert into emp1 values(102,”Tony”,”12-08-2000″,”Tester”,”25-10-2016″,”35000″,”Computer”);

Query OK, 1 row affected (0.03 sec)

mysql> select Id from emp where Id IN (select Id from emp1);

+—–+

| Id  |

+—–+

| 102 |

+—–+

1 row in set (0.00 sec)

mysql> select Id from emp where Id NOT IN (select Id from emp1);

+—–+

| Id  |

+—–+

| 103 |

| 104 |

+—–+

2 rows in set (0.00 sec)

 Practical No -4    Design at least 10 SQL queries for suitable database application using SQL DML statements: all types of Join, Sub-Query and View