Scribd
Upload
2 views3 pages

1 Exp

The document outlines the process of creating an 'employee' table in MySQL, including defining its structure and inserting records. It demonstrates user creation and permission granting, as well as the use of rollback with transactions. Additionally, it shows how to add constraints to the table, highlighting an error when attempting to insert a null value into a non-null column.

Uploaded by

Sumit Mashelkar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

1 Exp

The document outlines the process of creating an 'employee' table in MySQL, including defining its structure and inserting records. It demonstrates user creation and permission granting, as well as the use of rollback with transactions. Additionally, it shows how to add constraints to the table, highlighting an error when attempting to insert a null value into a non-null column.

Uploaded by

Sumit Mashelkar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

mysql> CREATE TABLE employee (

-> empno INT,

-> ename VARCHAR(10),

-> job VARCHAR(10),

-> mgr_no INT,

-> sal DECIMAL(10,2),

-> commission DECIMAL(10,2)

-> );

Query OK, 0 rows affected (0.04 sec)

1. Create a user and grant all permissions to the user.

mysql> CREATE USER 'dbms'@'localhost' IDENTIFIED BY 'dbms403';

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dbms'@'localhost';

Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT User, Host FROM [Link];


2. Insert the any three records in the employee table and use rollback.

ROLLBACK works only when AUTO-COMMIT is OFF

mysql> SET autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (1, 'Ram', 'Manager', 101, 50000, 5000);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (2, 'Shyam', 'Clerk', 101, 20000, 2000);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3, 'Mohan', 'Analyst', 102, 30000, 3000);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employee;

3. Add primary key constraint and not null constraint to the employee table.

ALTER TABLE employee ADD PRIMARY KEY (empno);

ALTER TABLE employee MODIFY ename VARCHAR(10) NOT NULL;


mysql> insert into employee values(106,NULL,'HR',5509,456 ,80);
ERROR 1048 (23000): Column 'ename' cannot be null

You might also like