TCL Commands in MySQL, Back and Restore database

 Enter password: ****

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

Your MySQL connection id is 11

Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> use kvs;

Database changed

mysql> show tables;

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

| Tables_in_kvs |

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

| dept          |

| emp           |

| emp1          |

| emp2          |

| emp3          |

| emp4          |

| emp5          |

| emp6          |

| emp7          |

| r1            |

| r2            |

| r3            |

| r4            |

| r5            |

| r6            |

| student       |

| t1            |

| t2            |

| t3            |

| t4            |

| t5            |

| t6            |

| t7            |

| voters        |

| voters1       |

| voters2       |

| voters3       |

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

27 rows in set (1.00 sec)

mysql> select * from emp;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 65000.00 | Maths   |

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

12 rows in set (0.26 sec)

mysql> update emp set salary = 70000 where id=112;

Query OK, 1 row affected (0.58 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 70000.00 | Maths   |

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

12 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from emp;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 70000.00 | Maths   |

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

12 rows in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> create table account(ac_no int,balance decimal);

Query OK, 0 rows affected (2.80 sec)

mysql> desc account;

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

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

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

| ac_no   | int           | YES  |     | NULL    |       |

| balance | decimal(10,0) | YES  |     | NULL    |       |

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

2 rows in set (0.11 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> desc account;

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

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

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

| ac_no   | int           | YES  |     | NULL    |       |

| balance | decimal(10,0) | YES  |     | NULL    |       |

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

2 rows in set (0.01 sec)

mysql> show tables;

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

| Tables_in_kvs |

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

| account       |

| dept          |

| emp           |

| emp1          |

| emp2          |

| emp3          |

| emp4          |

| emp5          |

| emp6          |

| emp7          |

| r1            |

| r2            |

| r3            |

| r4            |

| r5            |

| r6            |

| student       |

| t1            |

| t2            |

| t3            |

| t4            |

| t5            |

| t6            |

| t7            |

| voters        |

| voters1       |

| voters2       |

| voters3       |

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

28 rows in set (0.01 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.15 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

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

2 rows in set (0.04 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

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

2 rows in set (0.00 sec)

mysql> update account set balance=balance-100 where ac_no=100;

Query OK, 1 row affected (0.18 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+100 where ac_no=200;

Query OK, 1 row affected (0.14 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> truncate account;

Query OK, 0 rows affected (1.56 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

Empty set (0.20 sec)

mysql> set transaction;

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 '' at line 1

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> update account set balance=balance-100 where ac_no=100;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+100 where ac_no=200;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;

Query OK, 0 rows affected (0.09 sec)

mysql> select * from account;

Empty set (0.00 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.16 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

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

2 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

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

2 rows in set (0.00 sec)

mysql> update account set balance=balance-100 where ac_no=100;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+100 where ac_no=200;

Query OK, 1 row affected (0.15 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> trancate account;

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 'trancate account' at line 1

mysql> truncate account;

Query OK, 0 rows affected (1.27 sec)

mysql> select * from account;

Empty set (0.24 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.03 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

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

2 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.06 sec)

mysql> select * from account;

Empty set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

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

2 rows in set (0.00 sec)

mysql> update account set balance=balance-100 where ac_no=100;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+100 where ac_no=200;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;

Empty set (0.01 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> update account set balance=balance-100 where ac_no=100;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+100 where ac_no=200;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.13 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   19900 |

|   200 |   10100 |

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

2 rows in set (0.00 sec)

mysql> truncate account;

Query OK, 0 rows affected (1.14 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(100,20000),(200,10000);

Query OK, 2 rows affected (0.14 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into account values(300,25000);

Query OK, 1 row affected (0.00 sec)

mysql> savepoint A;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(400,30000);

Query OK, 1 row affected (0.00 sec)

mysql> savepoint B;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(500,40000);

Query OK, 1 row affected (0.00 sec)

mysql> savepoint C;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(600,50000);

Query OK, 1 row affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

|   300 |   25000 |

|   400 |   30000 |

|   500 |   40000 |

|   600 |   50000 |

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

6 rows in set (0.00 sec)

mysql> rollback to C;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

|   300 |   25000 |

|   400 |   30000 |

|   500 |   40000 |

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

5 rows in set (0.03 sec)

mysql> rollback to B;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

|   300 |   25000 |

|   400 |   30000 |

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

4 rows in set (0.00 sec)

mysql> rollback to A;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

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

| ac_no | balance |

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

|   100 |   20000 |

|   200 |   10000 |

|   300 |   25000 |

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

3 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.06 sec)

mysql> select * from account;

Empty set (0.00 sec)

mysql> show tables;

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

| Tables_in_kvs |

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

| account       |

| dept          |

| emp           |

| emp1          |

| emp2          |

| emp3          |

| emp4          |

| emp5          |

| emp6          |

| emp7          |

| r1            |

| r2            |

| r3            |

| r4            |

| r5            |

| r6            |

| student       |

| t1            |

| t2            |

| t3            |

| t4            |

| t5            |

| t6            |

| t7            |

| voters        |

| voters1       |

| voters2       |

| voters3       |

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

28 rows in set (0.00 sec)

mysql> drop kvs;

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 'kvs' at line 1

mysql> drop database kvs;

Query OK, 28 rows affected (7.90 sec)

mysql> show tables;

ERROR 1046 (3D000): No database selected

mysql> show databases;

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

| Database           |

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

| 12cs               |

| abc                |

| delhi              |

| information_schema |

| leet               |

| mysql              |

| performance_schema |

| pysales_inventory  |

| sakila             |

| spsc               |

| spsharmaclasses    |

| spsharmag          |

| sys                |

| world              |

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

14 rows in set (0.06 sec)

mysql> create databases kvs;

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 'databases kvs' at line 1

mysql> create database kvs;

Query OK, 1 row affected (0.09 sec)

mysql> show databases;

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

| Database           |

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

| 12cs               |

| abc                |

| delhi              |

| information_schema |

| kvs                |

| leet               |

| mysql              |

| performance_schema |

| pysales_inventory  |

| sakila             |

| spsc               |

| spsharmaclasses    |

| spsharmag          |

| sys                |

| world              |

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

15 rows in set (0.00 sec)

mysql> use kvs;

Database changed

mysql> show tables;

Empty set (0.02 sec)

mysql> show tables;

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

| Tables_in_kvs |

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

| account       |

| dept          |

| emp           |

| emp1          |

| emp2          |

| emp3          |

| emp4          |

| emp5          |

| emp6          |

| emp7          |

| r1            |

| r2            |

| r3            |

| r4            |

| r5            |

| r6            |

| student       |

| t1            |

| t2            |

| t3            |

| t4            |

| t5            |

| t6            |

| t7            |

| voters        |

| voters1       |

| voters2       |

| voters3       |

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

28 rows in set (0.09 sec)

mysql> select * from emp;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 70000.00 | Maths   |

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

12 rows in set (0.09 sec)

mysql> create table rohit as select * from emp;

Query OK, 12 rows affected, 1 warning (0.69 sec)

Records: 12  Duplicates: 0  Warnings: 1

mysql> select * from rohit;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 70000.00 | Maths   |

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

12 rows in set (0.00 sec)

mysql> create table rohit1 as select * from emp where id<110;

Query OK, 9 rows affected, 1 warning (0.81 sec)

Records: 9  Duplicates: 0  Warnings: 1

mysql> select * from rohit1;

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

| id   | name  | DOJ        | age  | salary   | subject |

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

|  102 | Sita  | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam   | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj   | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit  | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya  | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj   | 2018-01-23 |   33 | 80000.00 | CS      |

|  109 | Rohan | NULL       |   32 |     NULL | Hindi   |

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

9 rows in set (0.00 sec)

mysql> create table rohit2 as select id,name,salary from emp where id<110;

Query OK, 9 rows affected, 1 warning (0.50 sec)

Records: 9  Duplicates: 0  Warnings: 1

mysql> select * from rohit2;

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

| id   | name  | salary   |

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

|  102 | Sita  | 75000.00 |

|  103 | Sam   | 95000.00 |

|  104 | Priya | 72000.00 |

|  105 | Raj   | 80000.00 |

|  106 | Amit  | 72000.00 |

|  107 | Pooja | 65000.00 |

|  108 | Riya  | 73000.00 |

|  105 | Raj   | 80000.00 |

|  109 | Rohan |     NULL |

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

9 rows in set (0.00 sec)

mysql> create table rohit2(id int,age int);

ERROR 1050 (42S01): Table 'rohit2' already exists

mysql> create table if not exists rohit2(id int,age int);

Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> select * from rohit2;

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

| id   | name  | salary   |

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

|  102 | Sita  | 75000.00 |

|  103 | Sam   | 95000.00 |

|  104 | Priya | 72000.00 |

|  105 | Raj   | 80000.00 |

|  106 | Amit  | 72000.00 |

|  107 | Pooja | 65000.00 |

|  108 | Riya  | 73000.00 |

|  105 | Raj   | 80000.00 |

|  109 | Rohan |     NULL |

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

9 rows in set (0.00 sec)

mysql> select * from emp;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 70000.00 | Maths   |

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

12 rows in set (0.03 sec)

mysql> select * from emp where exists (select salary where id=109);

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

| id   | name  | DOJ  | age  | salary | subject |

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

|  109 | Rohan | NULL |   32 |   NULL | Hindi   |

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

1 row in set (0.04 sec)

mysql> select * from emp where exists (select salary where id=115);

Empty set (0.00 sec)

mysql> select * from emp where True;

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

| id   | name    | DOJ        | age  | salary   | subject |

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

|  102 | Sita    | 2018-12-05 |   32 | 75000.00 | Hindi   |

|  103 | Sam     | 2010-10-10 |   38 | 95000.00 | Hindi   |

|  104 | Priya   | 2019-12-05 |   28 | 72000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  106 | Amit    | 2020-12-05 |   28 | 72000.00 | Hindi   |

|  107 | Pooja   | 2021-12-10 |   28 | 65000.00 | Hindi   |

|  108 | Riya    | 2019-03-05 |   31 | 73000.00 | CS      |

|  105 | Raj     | 2018-01-23 |   33 | 80000.00 | CS      |

|  110 | Aman    | 2019-02-05 |   31 | 72000.00 | CS      |

|  109 | Rohan   | NULL       |   32 |     NULL | Hindi   |

|  111 | Shubham | 2021-10-10 |   25 | 65000.00 | Maths   |

|  112 | Ajay    | 2021-10-10 |   25 | 70000.00 | Maths   |

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

12 rows in set (0.00 sec)

mysql> select * from emp where False;

Empty set (0.00 sec)

mysql> select * from emp where id=120;

Empty set (0.00 sec)

mysql> select * from emp where id=102;

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

| id   | name | DOJ        | age  | salary   | subject |

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

|  102 | Sita | 2018-12-05 |   32 | 75000.00 | Hindi   |

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

1 row in set (0.00 sec)

mysql>

Microsoft Windows [Version 10.0.19042.1237]

(c) Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>cd C:\Program Files\MySQL\MySQL Server 8.0\bin

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -uroot -proot kvs>kvsback.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -proot kvs<kvsback.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

C:\Program Files\MySQL\MySQL Server 8.0\bin>

Concurrency Control Protocols

If a transaction has obtained a __________ lock, it can read but cannot write on the item
a) Shared mode
b) Exclusive mode
c) Read only mode
d) Write only mode




If a transaction has obtained a ________ lock, it can both read and write on the item
a) Shared mode
b) Exclusive mode
c) Read only mode
d) Write only mode




A transaction can proceed only after the concurrency control manager ________ the lock to the transaction
a) Grants
b) Requests
c) Allocates
d) None of the mentioned




If a transaction can be granted a lock on an item immediately in spite of the presence of another mode, then the two modes are said to be ________
a) Concurrent
b) Equivalent
c) Compatible
d) Executable






A transaction is made to wait until all ________ locks held on the item are released
a) Compatible
b) Incompatible
c) Concurrent
d) Equivalent






The situation where no transaction can proceed with normal execution is known as ________
a) Road block
b) Deadlock
c) Execution halt
d) Abortion



The protocol that indicates when a transaction may lock and unlock each of the data items is called as __________
a) Locking protocol
b) Unlocking protocol
c) Granting protocol
d) Conflict protocol






The two phase locking protocol consists which of the following phases?
a) Growing phase
b) Shrinking phase
c) Both a and b
d) None of the mentioned






If a transaction may obtain locks but may not release any locks then it is in _______ phase
a) Growing phase
b) Shrinking phase
c) Deadlock phase
d) Starved phase



If a transaction may release locks but may not obtain any locks, it is said to be in ______ phase
a) Growing phase
b) Shrinking phase
c) Deadlock phase
d) Starved phase



Which of the following cannot be used to implement a timestamp
a) System clock
b) Logical counter
c) External time counter
d) None of the mentioned

A logical counter is _________ after a new timestamp has been assigned
a) Incremented
b) Decremented
c) Doubled
d) Remains the same

The _________ requires each transaction executes in two or three different phases in its lifetime
a) Validation protocol
b) Timestamp protocol
c) Deadlock protocol
d) View protocol

During __________ phase, the system reads data and stores them in variables local to the transaction.
a) Read phase
b) Validation phase
c) Write phase
d) None of the mentioned

During the _________ phase the validation test is applied to the transaction
a) Read phase
b) Validation phase
c) Write phase
d) None of the mentioned




During the _______ phase, the local variables that hold the write operations are copied to the database
a) Read phase
b) Validation phase
c) Write phase
d) None of the mentioned




Read only operations omit the _______ phase
a) Read phase
b) Validation phase
c) Write phase
d) None of the mentioned

Conflict Serializability Assignment - S P SHARMA CLASSES

 

Question 1 : Consider the following schedules involving two transactions. Which one of the following statement is true? 

S1: R1(X) R1(Y) R2(X) R2(Y) W2(Y) W1(X) 
S2: R1(X) R2(X) R2(Y) W2(Y) R1(Y) W1(X) 

• Both S1 and S2 are conflict serializable
• Only S1 is conflict serializable
• Only S2 is conflict serializable
• None

Question 2 : Consider the following schedules involving two transactions. Which one of the following statement is true? 

S1: r1(x) r1(y) w2(x) w1(x) r2(y)
S2: r1(x) r3(y) w1(x) w2(y) r3(x) w2(x)

• Both S1 and S2 are conflict serializable
• Only S1 is conflict serializable
• Only S2 is conflict serializable
• None

Question 3 : Consider the following schedules involving three transactions. Which one of the following statement is true? 

S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z);
    w3(Y); w2(Z); r1(Z); w1(X); w1(Z)
S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z);
    r2(Z); w3(Y); w1(X); w2(Z); w1(Z) 

Which one of the following statements about the schedules is TRUE?
(A) Only S1 is conflict-serializable.
(B) Only S2 is conflict-serializable.
(C) Both S1 and S2 are conflict-serializable.
(D) Neither S1 nor S2 is conflict-serializable.

Question 4 : Let $S$ be the following schedule of operations of three transactions T1, T2 and T3 in a relational database system:

R2(Y),R1(X),R3(Z),R1(Y)W1(X),R2(Z),W2(Y),R3(X),W3(Z)$$$$

Consider the statements P$P$ and Q$Q$ below:

• P: S is conflict-serializable.
• Q: If T3 commits before T1 finishes, then S is recoverable.

Which one of the following choices is correct?

1. Both P and Qare true
2. P is true and Q is false
3. P is false and Q is true
4. Both P and Q are false

1. C

2. C

3. A

4. B