Lab/Sem4/SQL/first.txt at main · ifsvivek/Lab · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
mysql> CREATE TABLE Employee(
    ->     EMPNO INT,
    ->     ENAME VARCHAR(50),
    ->     JOB VARCHAR(50),
    ->     MANAGER_NO INT,
    ->     SAL INT,
    ->     COMMISSION INT
    -> );
Query OK, 0 rows affected (0.03 sec)


mysql> CREATE USER 'FirstUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)


mysql> GRANT ALL PRIVILEGES ON FirstDB.Employee TO 'FirstUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)


mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO Employee(EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES(1, 'John', 'Manager', 0, 10000, 1000);
Query OK, 1 row affected (0.00 sec)


mysql> INSERT INTO Employee(EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES(2, 'Jane', 'Manager', 0, 10000, 1000);
Query OK, 1 row affected (0.00 sec)


mysql> INSERT INTO Employee(EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES(3, 'Jack', 'Manager', 0, 10000, 1000);
Query OK, 1 row affected (0.00 sec)


mysql> SELECT * FROM Employee;
+-------+-------+---------+------------+-------+------------+
| EMPNO | ENAME | JOB     | MANAGER_NO | SAL   | COMMISSION |
+-------+-------+---------+------------+-------+------------+
|     1 | John  | Manager |          0 | 10000 |       1000 |
|     2 | Jane  | Manager |          0 | 10000 |       1000 |
|     3 | Jack  | Manager |          0 | 10000 |       1000 |
+-------+-------+---------+------------+-------+------------+
3 rows in set (0.00 sec)


mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT * FROM Employee;
Empty set (0.00 sec)


mysql> ALTER TABLE Employee ADD PRIMARY KEY(EMPNO);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE Employee MODIFY EMPNO INT NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> INSERT INTO Employee(EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES(NULL, 'John', 'Manager', 0, 10000, 1000);
ERROR 1048 (23000): Column 'EMPNO' cannot be null

mysql> INSERT INTO Employee(EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES(4, 'Jane', 'Manager', 0, 10000, 1000);
Query OK, 1 row affected (0.00 sec)


mysql> INSERT INTO Employee(EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION) VALUES(5, 'Jack', 'Manager', 0, 10000, 1000);
Query OK, 1 row affected (0.00 sec)


mysql> SELECT * FROM Employee;
+-------+-------+---------+------------+-------+------------+
| EMPNO | ENAME | JOB     | MANAGER_NO | SAL   | COMMISSION |
+-------+-------+---------+------------+-------+------------+
|     4 | Jane  | Manager |          0 | 10000 |       1000 |
|     5 | Jack  | Manager |          0 | 10000 |       1000 |
+-------+-------+---------+------------+-------+------------+
2 rows in set (0.00 sec)


mysql> DROP TABLE Employee;
Query OK, 0 rows affected (0.01 sec)


mysql> DROP USER 'FirstUser'@'localhost';
Query OK, 0 rows affected (0.01 sec)