This repository has been archived by the owner on Oct 10, 2021. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
BasicSQLQueries.txt
160 lines (89 loc) · 3.16 KB
/
BasicSQLQueries.txt
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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
1) Create table DEPT with following columns and data types.
CloumnName null Data type
Deptno notnull number(2)
Dname char(14)
Location char(13)
SQL> create table dept(deptno number(2) NOT NULL,dnmae char(14), location char(13));
Table created.
2) Insert in the DEPT table the following rows
Deptno dname location
10 Accounting Newyork
20 Research Chicago
30 Sales Dallas
40 Operations Boston
SQL> insert into dept values(10,'Accounting','Newyork');
1 row created.
SQL> insert into dept values(20,'Research','Chicago');
1 row created.
SQL> insert into dept values(30,'Sales','Dallas');
1 row created.
SQL> insert into dept values(40,'operations','Boston');
1 row created.
3) Insert yourself as a new employee into EMP table.
SQL> create table emp(empno number(4) NOT NULL,empname varchar2(15),salary number(5));
Table created.
SQL> insert into emp values(1000,'Badri',70000);
1 row created.
4) Add a new department into the DEPT table with a department number of 99
location of Miami, and a department name of education.
SQL> insert into dept values(99,'Marketing','Miami');
1 row created.
5) Update your own employee data by giving yourself a raise of $1000 per month.
SQL> update emp set salary = salary + 1000 where empno = 1000 ;
1 row updated.
6) View the changes you have just done.
SQL> select * from emp where empno = 1000;
EMPNO EMPNAME SALARY
----- ------- ------
1000 Badri 71000
7) Delete yourself from the EMP table.
SQL> delete from emp where empno = 1000;
1 row deleted.
8) Select from EMP table to check whether the row is existing.
SQL> select * from emp;
no rows selected.
9) Create a duplicate EMP table name is EMPTEST.
SQL> create table emptest as select * from emp;
Table created.
10) Add a new column named SEX to the EMPTEST table with data type of character
and length 1.
SQL> alter table emptest add (sex char(1));
Table altered.
11) Display and see the structure of table EMPTEST.
SQL> desc emptest;
Name Null Type
---- ---- ----
EMPNO NOT NULL NUMBER(4)
EMPNAME VARCHAR2(15)
SALARY NUMBER(5)
SEX CHAR(1)
12) The user have changed their mind. Instead of storing SEX as F or M, they want to
store SEX as MALE or FEMALE. So increase the size of the SEX column.
SQL> alter table emptest modify(sex char(6));
Table altered.
13) Display each employee’s name and hire date.
SQL> alter table emptest add(hiredate date);
Table altered.
SQL> insert into emptest values(1000,'Badri',71000,'Male',DATE'2019-07-05);
1 row created.
SQL> insert into emptest values(1001,'Badri R',60000,'Male',DATE'2019-07-05);
1 row created.
SQL> insert into emptest values(999,'ArunLaxman',50000,'Male',DATE'2019-07-05);
1 row created.
SQL> select empname,hiredate from emptest;
EMPNAME HIREDATE
------- --------
Badri 05-JUL-19
Badri R 05-JUL-19
ArunLaxman 05-JUL-19
14) Display the information in the above query with hire date appearing first.
SQL> select hiredate,empname from emptest;
HIREDATE EMPNAME
------- --------
05-JUL-19 Badri
05-JUL-19 Badri R
05-JUL-19 ArunLaxman
SQL> drop table dept;
Table dropped.
SQL> drop table emptest;
Table dropped.