Sql_From_Beginner_To_Advance/10_Alter Table.sql at main · ImrenR/Sql_From_Beginner_To_Advance · 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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
/*=============================== ALTER TABLE =========================================
ALTER TABLE is used to:

- Add columns
- Modify column definitions
- Drop columns
- Rename tables or columns
- Add constraints such as CHECK
===================================================================================*/

USE JavaCan;

/* ---------------- Table Creation ---------------- */

CREATE TABLE personel
(
    id INT PRIMARY KEY,
    isim VARCHAR(50),
    sehir VARCHAR(50),
    maas INT,
    sirket VARCHAR(20)
);

/* ---------------- Data Insertions ---------------- */

INSERT INTO personel VALUES(123456789, 'Haluk Bilgin', 'Istanbul', 50000, 'IBM');
INSERT INTO personel VALUES(234567890, 'Ipek Bilir', 'Istanbul', 25000, 'Google');
INSERT INTO personel VALUES(345678901, 'Harun Bilmiş', 'Ankara', 13000, 'IBM');
INSERT INTO personel VALUES(456789012, 'Harun Bilmiş', 'Izmir', 10000, 'Microsoft');
INSERT INTO personel VALUES(567890123, 'Harun Bilmiş', 'Ankara', 17000, 'Amazon');
INSERT INTO personel VALUES(456789013, 'Ipek Bilir', 'Ankara', 15000, 'Microsoft');
INSERT INTO personel VALUES(123456710, 'Halime Bak', 'Bursa', 25000, 'IBM');

SELECT * FROM personel;

/* ---------------- TASKS ---------------- */

/* Task01:
Add column ulke_isim with default value 'Turkiye'.
*/

ALTER TABLE personel
ADD ulke_isim VARCHAR(50) DEFAULT 'Turkiye';

/* Task02:
Add gender and yas columns.
*/

ALTER TABLE personel
ADD gender VARCHAR(20),
ADD yas INT;

/* Task02.5:
Add age column after isim column.
(MySQL supports AFTER keyword.)
*/

ALTER TABLE personel
ADD age INT AFTER isim;

/* Task02.75:
Add football_team column after isim with default value.
*/

ALTER TABLE personel
ADD football_team VARCHAR(20) DEFAULT 'takim' AFTER isim;

/* Task03:
Drop sirket column.
*/

ALTER TABLE personel
DROP COLUMN sirket;

/* Task04:
Rename column ulke_isim → ulke_adi.
*/

ALTER TABLE personel
RENAME COLUMN ulke_isim TO ulke_adi;

/* Task05:
Rename table personel → sefil_ameleler.
*/

ALTER TABLE personel
RENAME TO sefil_ameleler;

/* After table rename, use new table name */

/* Task06:
Add NOT NULL constraint to ulke_adi and change datatype to VARCHAR(30).
*/

ALTER TABLE sefil_ameleler
MODIFY ulke_adi VARCHAR(30) NOT NULL;

/* Task07:
Add CHECK constraint maas >= 9000.
*/

ALTER TABLE sefil_ameleler
ADD CONSTRAINT chk_maas CHECK (maas >= 9000);

SELECT * FROM sefil_ameleler;