1> What command is used to grant specific privileges to a user?with practical example?
Grant commad is used to giving permissions to the users.
First we have create an user.
Syntax : Create user username identified by ‘password’.
For giving permissions to user using grant command.
Syntax: Grant select ,insert on table_name to username.
Example:
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 4 | bbb | A |A |
+------+-------+---------+-------+
3 rows in set (0.00 sec)
mysql> create user lucky1 identified by '123';
Query OK, 0 rows affected (0.07 sec)
mysql> grant select ,insert on dcl to lucky1;
Query OK, 0 rows affected (0.07 sec)
Now go to mysql workbench and select that particular user and perform operations which are granted to
the user.
2> How do you revoke a privilege from a user?with practical example?
By using “revoke” command, we can revoke a privilege from a user.
Example:
[Link] give some commands to the user, lets take select and insert.
[Link] the above command insert and add one record
[Link] revoke the command insert from the user.
Syntax: revoke insert on dcl from lucky1;
[Link] if the user try to add data will shows an error.
3> What is the purpose of the COMMIT command in a transaction? defination and practical example?
Commit command is used to store the data permently. After commit no one can change the data.
Example:
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 4 | bbb | A |A |
| 5 | eee | A |B |
+------+-------+---------+-------+
4 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.05 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from dcl where s_no = 5;
Query OK, 1 row affected (0.23 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 4 | bbb | A |A |
+------+-------+---------+-------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 4 | bbb | A |A |
+------+-------+---------+-------+
3 rows in set (0.00 sec)
4> How do you undo changes made during a transaction?definationa and practical example?
We can undo the changes during the transaction by using “rollback” command. Rollback is used to
retrive the changed or removed data previously .
Example:
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 5 | eee | A |B |
| 6 | fff | A |c |
+------+-------+---------+-------+
4 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from dcl where s_no = 5;
Query OK, 1 row affected (0.00 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 6 | fff | A |c |
+------+-------+---------+-------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.06 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 5 | eee | A |B |
| 6 | fff | A |c |
+------+-------+---------+-------+
4 rows in set (0.00 sec)
5> What is a SAVEPOINT and how is it used?
Savepoint is command used to retrive the deleted or changed data anytime during the transaction.
Example:
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 6 | fff | A |c |
| 5 | eee | A |B |
+------+-------+---------+-------+
4 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dcl values(7,'zzz','A','B');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint y;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 6 | fff | A |c |
| 5 | eee | A |B |
| 7 | zzz | A |B |
+------+-------+---------+-------+
5 rows in set (0.00 sec)
mysql> delete from dcl where s_no = 7;
Query OK, 1 row affected (0.00 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 6 | fff | A |c |
| 5 | eee | A |B |
+------+-------+---------+-------+
4 rows in set (0.00 sec)
mysql> rollback to a;
ERROR 1305 (42000): SAVEPOINT a does not exist
mysql> rollback to y;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from dcl;
+------+-------+---------+-------+
| s_no | name | section | grade |
+------+-------+---------+-------+
| 2 | satya | A | A+ |
| 3 | aaa | A |A |
| 6 | fff | A |c |
| 5 | eee | A |B |
| 7 | zzz | A |B |
+------+-------+---------+-------+
5 rows in set (0.00 sec)
6> write a query:
creates a savepoint,SAVEPOINT before_update;
The UPDATE statement changes the age of the employee with employee_id = 3 to 30.
The ROLLBACK TO SAVEPOINT before_update; undoes the changes made after the savepoint was
created,
effectively rolling back the UPDATE operation, so the age of the employee remains unchanged.
mysql> select*from employees;
+-------------+---------------+------+
| employee_id | employee_name | age |
+-------------+---------------+------+
| 1|a | 35 |
| 2|b | 36 |
| 3|c | 30 |
| 4|d | 1|
| 5|e | 3|
+-------------+---------------+------+
5 rows in set (3.67 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> savepoint before_update;
Query OK, 0 rows affected (0.00 sec)
mysql> update employees set age=30 where age=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from employees;
+-------------+---------------+------+
| employee_id | employee_name | age |
+-------------+---------------+------+
| 1|a | 35 |
| 2|b | 36 |
| 3|c | 30 |
| 4|d | 1|
| 5|e | 30 |
+-------------+---------------+------+
5 rows in set (0.00 sec)
mysql> rollback to before_update;
Query OK, 0 rows affected (0.28 sec)
mysql> select*from employees;
+-------------+---------------+------+
| employee_id | employee_name | age |
+-------------+---------------+------+
| 1|a | 35 |
| 2|b | 36 |
| 3|c | 30 |
| 4|d | 1|
| 5|e | 3|
+-------------+---------------+------+
5 rows in set (0.00 sec)
7> How do you grant all privileges on a table to a user?practical example?
By using ‘all privileges’ commend we can grant all privileges on a table to a user.
Syntax:
grant all privileges on tablename to username;
flush privileges;
Examples: 1)insert command
8> What is the effect of REVOKE on a user who already has access to a table?practical example?
By using “revoke” command, we can revoke a privilege from a user.
Example:
[Link] give some commands to the user, lets take select and insert.
[Link] the above command insert and add one record
[Link] revoke the command insert from the user.
Syntax: revoke insert on dcl from lucky1;
[Link] if the user try to add data will shows an error.
9> What are some examples of transaction control commands in SQL?
• COMMIT. This command helps a user to save a given transaction into the database permanently
• ROLLBACK. This command functions to restore the database
• SAVEPOINT. This command helps in saving a transaction ,so that a user can easily rollback to that
point when its required.
10> Is it possible to use ROLLBACK after COMMIT
No, its not possible to use rollback after commit because commit saves the transaction data
permanently.
11> What happens if you don't use COMMIT at the end of a transaction?
If you don't use COMMIT at the end of a transaction, there is a chance of changing the transaction data.