-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsql_queries
103 lines (65 loc) · 2.93 KB
/
sql_queries
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
###
CREATE TABLE retired_Soldier
(
rs_id number NOT NULL PRIMARY KEY,
rs_firstn VARCHAR(25) NOT NULL,
rs_lastn VARCHAR(25) NOT NULL,
rs_gen CHAR(1) NOT NULL CHECK(rs_gen in('M','F','m','f')),
rs_rank VARCHAR(25) NOT NULL,
rs_phone number(10) NOT NULL,
rs_email VARCHAR(25) NOT NULL,
rs_posting VARCHAR(25) NOT NULL
);
insert into retired_soldier select s_id,s_firstn,s_lastn,s_gen,s_rank,s_phone,s_email,s_posting
from soldier where s_id = 1234;
delete from soldier where s_id=(select s_id from soldier where s_email = 'abcd@gmail.com');
delete from father where f_id=(select f_id from father where f_email = 'rahuld@gmail.com');
delete from mother where m_id=(select m_id from mother where m_email =
'lmang@gmail.com');
delete from spouse where sp_id=(select sp_id from mother where sp_email =
'rani@gmail.com');
delete from vision where vis_id =1;
delete from vaccine where v_id =1;
delete from medical where m_id =(select s_id from soldier where s_email = 'abcd@gmail.com');
delete from skillset where sk_id =(select s_id from soldier where s_email = 'abcd@gmail.com');
delete from arms_used where a_id =(select s_id from soldier where s_email =
'abcd@gmail.com');
delete from SB where s_id =(select s_id from soldier where s_email = 'abcd@gmail.com');
###
CREATE TABLE martyred_Soldier
(
ms_id number NOT NULL PRIMARY KEY,
ms_firstn VARCHAR(25) NOT NULL,
ms_lastn VARCHAR(25) NOT NULL,
ms_gen CHAR(1) NOT NULL CHECK(ms_gen in('M','F','m','f')),
ms_rank VARCHAR(25) NOT NULL,
ms_posting VARCHAR(25) NOT NULL
);
insert into martyred_soldier select s_id,s_firstn,s_lastn,s_gen,s_rank,s_posting from soldier
where s_id = 1234;
delete from soldier where s_id=(select s_id from soldier where s_email = 'abcd@gmail.com');
delete from father where f_id=(select f_id from father where f_email = 'rahuld@gmail.com');
delete from mother where m_id=(select m_id from mother where m_email =
'lmang@gmail.com');
delete from spouse where sp_id=(select sp_id from mother where sp_email =
'rani@gmail.com');
delete from vision where vis_id =1;
delete from vaccine where v_id =1;
delete from medical where m_id =(select s_id from soldier where s_email = 'abcd@gmail.com');
delete from skillset where sk_id =(select s_id from soldier where s_email = 'abcd@gmail.com');
delete from arms_used where a_id =(select s_id from soldier where s_email =
'abcd@gmail.com');
delete from SB where s_id =(select s_id from soldier where s_email = 'abcd@gmail.com');
###
update soldier set s_posting = 'Delhi' where s_id=1234;
update soldier set s_state = 'Delhi NCR' where s_id=1234;
###
update arms_used set AK103=1 where s_id=(select s_id from soldier where S_rank='General');
###
select count(s_id),s_posting from soldier group by s_posting;
###
select count(s_id) "Count of soldiers using AK103" from arms_used group by ak103;
###
select * from medical natural join vaccine natural join vision where m_id=&m_id;
###
select * from soldier natural join sb where s_id=&s_id order by ifsc desc;