CREATE TABLE SQL

 

Ex 1:

Create a table called Employee & execute the following. Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION) 1. Create a user and grant all permissions to theuser. 2. Insert the any three records in the employee table contains attributes EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check the result. 3. Add primary key constraint and not null constraint to the employee table. 4. Insert null values to the employee table and verify the result

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

CREATE USER 'emp_jones'@'localhost' IDENTIFIED BY 'emp123';

GRANT ALL PRIVILEGES ON *.* TO 'emp_user'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

What this does:

 1 Create a user

CREATE USER 'emp_user'@'localhost' IDENTIFIED BY 'emp123';

EXPLANATION

emp_user → username

@'localhost' → user can log in only from the local machine

IDENTIFIED BY 'emp123' → sets the password

2 Grant all privileges

GRANT ALL PRIVILEGES ON *.* TO 'emp_user'@'localhost' WITH GRANT OPTION;

EXPLANATION

• ALL PRIVILEGES → gives all permissions (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.)

• *.* → applies to all databases and all tables

• WITH GRANT OPTION → allows emp_user to grant permissions to other users

Result:
emp_user has full control over the entire MySQL server.

⚠️ Note: This is fine for lab/practice, but unsafe for production.

3 Apply changes immediately

FLUSH PRIVILEGES;

EXPLANATION

• Reloads the privilege tables

• Ensures the new permissions take effect immediately

Result:
No need to restart MySQL — changes are active right away.

Result:

A new MySQL user named emp_user is created with password emp123.

II.  Insert the any three records in the employee table contains attributes EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check the result. 

step 1:

CREATE DATABASE joda_db;

step2: 

USE joda_db;

step3 : 

Insert the any three records in the employee table contains attributes

 EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION

CREATE TABLE EMPLOYEE (

    EMPNO        INT PRIMARY KEY,

    ENAME        VARCHAR(50),

    JOB          VARCHAR(50),

    MANAGER_NO   INT,

    SAL          DECIMAL(10,2),

    COMMISSION   DECIMAL(10,2)

);

use rollback (Enter Values). Check the result

INSERT INTO Employee VALUES (101, 'John', 'Manager', NULL, 50000, 5000);

INSERT INTO Employee VALUES (102, 'vikki', 'Clerk', 101, 25000, 2000);

INSERT INTO Employee VALUES (103, 'Raj', 'Salesman', 101, 30000, 3000);

show table... etc

SELECT * FROM Employee;

   

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

Step 1: Make EMPNO the Primary Key

This means no duplicate EMPNO and no empty EMPNO.

ALTER TABLE EMPLOYEE
ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO);

note: 

This line tells the database:
“EMPNO is the main ID for each employee.”

ALTER TABLE EMPLOYEE

ADD PRIMARY KEY (EMPNO);

Step 2: Make columns NOT NULL

This means these columns must have values.

ALTER TABLE EMPLOYEE
MODIFY ENAME VARCHAR(50) NOT NULL;

ALTER TABLE EMPLOYEE

MODIFY JOB VARCHAR(50) NOT NULL;

ALTER TABLE EMPLOYEE
MODIFY SAL DECIMAL(10,2) NOT NULL;

👉 These lines tell the database:

“ENAME, JOB, and SAL cannot be empty/Null.”

Before Make columns NOT NULL

After Make columns NOT NULL

IV, 4. Insert null values to the employee table and verify the result. 

Note:

cant use this commamd for Empno, Ename,Job,Sal because we changed these as NOT NULL.

So we do remaining Managerno and Commission.

SOLUTION:
 Insert NULL into nullable columns (works)

INSERT INTO EMPLOYEE (EMPNO, ENAME, JOB, SAL, MANAGER_NO, COMMISSION)

VALUES (202, 'Ravi', 'Sales', 18000, NULL, NULL);

Verify the result:

 SELECT * FROM EMPLOYEE WHERE EMPNO = 202;