HMctfT0w

1drop database if exists studentDB
2create database studentDB
3use studentDB
4drop table if exists tblStudent
5create table tblStudent (
6	s_id int primary key,
7	s_name varchar(20),
8	s_address varchar(20),
9	s_phone nvarchar(14),
10	s_age int,
11	s_pktmoney nvarchar(10)
12)
13
14insert into tblStudent(s_id, s_name, s_address, s_phone, s_age, s_pktmoney)
15	values(1, 'Abiral', 'B', 11, 12, 200),
16			(2, 'Bijen', 'C', 22, 13, 300),
17			(3, 'Dijen', 'E', 33, 14, 200),
18			(4, 'Fijen', 'G', 44, 15, 100),
19			(5, 'Hijen', 'I', 55, 16, 200),
20			(6, 'Jijen', 'K', 66, 17, 300),
21			(7, 'Lijen', 'M', 77, 18, 400),
22			(8, 'Nijen', 'O', 88, 19, 100),
23			(9, 'Pijen', 'Q', 99, 20, 400),
24			(10, 'Rijen', 'S', 100, 21, 100)
25
26select * from tblStudent
27
28alter table tblStudent
29alter column s_age int not null
30
31alter table tblStudent
32alter column s_name varchar(20) not null
33
34alter table tblStudent
35alter column s_address varchar(20) not null
36
37alter table tblStudent
38alter column s_phone nvarchar(14) not null
39
40alter table tblStudent
41alter column s_pktmoney nvarchar(10) not null
42
43
44select * from tblStudent
45where s_pktmoney > 100 and s_age >= 20
46
47alter table tblStudent
48add constraint abc default 20 for s_age
49
50alter table tblStudent
51add constraint def default 100 for s_pktmoney
52
53insert into tblStudent(s_id, s_name, s_address, s_phone)
54	values(809, 'babb', 'V', 222)
55
56select * from tblStudent
57
58alter table tblStudent
59	drop constraint abc
60
61select * from tblStudent
62where s_name not like 'A%'
63
64select * from tblStudent
65where s_name like '[^P]%'
66
67select * from tblStudent
68where s_name like '_a%'
69
70select * from tblStudent
71where s_name like '%a%'
72
73select * from tblStudent
74where s_name like '[A-N]%'
75
76select * from tblStudent
77where s_age between 20 and 27
78
79select * from tblStudent
80where s_age between 20 and 27
81
82select * from tblStudent
83where s_name like '____%'
84
85select * from tblStudent
86where s_name like '____'
87
88select * from tblStudent
89where s_name like '__a__'
90
91select * from tblStudent
92where s_name like '%b%b%b'
93
94select * from tblStudent
95where s_name like '%ab%'
96
97select * from tblStudent
98where s_age > (select AVG(s_age) from tblStudent)
99
100select s_name, s_pktmoney from tblStudent
101group by s_pktmoney 
102
103select count(s_pktmoney) as count, s_pktmoney from tblStudent
104group by s_pktmoney
105
106select distinct(s_pktmoney) from tblStudent
107
108select min(s_name) as name, s_pktmoney from tblStudent
109group by s_pktmoney
110
111select count(*), s_pktmoney from tblStudent
112group by s_pktmoney
113
114select min(s_id), min(s_name), min(s_address), min(s_phone), min(s_age), s_pktmoney from tblStudent
115group by s_pktmoney