-
Notifications
You must be signed in to change notification settings - Fork 60
/
Day-8_SQL_COMMANDS.txt
162 lines (107 loc) · 5.9 KB
/
Day-8_SQL_COMMANDS.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
161
162
"Welcome To Ashok IT"
"Oracle Database"
Topic : SQL Commands
Date : 22/11/2022
(Session - 08)
_____________________________________________________________________________________________________________________________
Yesterday Session:
==================
SQL Commands
============
* SQL Commands is nothing but SQL Statements (or) SQL Queries.
* Basically Statement represents preforming the particular task (or) Statement is group of predefined Words
Example : select * from emp;
* Every Statement must be terminated by Semicolon.
* The Statement which we are Passed to Database Software System such statements is called as "SQL Statements".
* Basically SQL Commands/SQL Statements are Categorized as below
1) Data Definition Language(DDL) Commands (or) DDL Statements
>> Deals with Creating/Modifying/Dropping Structure of Database Objects.
>> DDL Commands are "create, alter, drop,truncate"
2) Data Manipulation Lanaguage(DML) Commands (or) DML Statements
>> Deals with Data of an Database objects
>> DML Commands are "insert, update,delete"
3) Data Control Language(DCL) Commands (or) DCL Statements
>> Deals with giving the Permission (or) Revoking the permisssion of an users
>> DCL Commands are used By "DBA Persons"
>> DCL Commands are "grant,revoke"
4) Date Retrevial Language(DRL) Commands (or) DRL Statements
>> Deals with Retreving the data from Database objects
>> DRL Commands are "select"
5) Transaction Control Language(TCL) Commands (or) TCL Statements
>> Deals with Transaction Management
>> TCL Commands are "commit,rollback,savepoint"
NOTE
====
Database Objects in Oracle : Tables,Views,Sequences,indexes,Stored Procedures,Stored Functions etc.,
_____________________________________________________________________________________________________________________________
Today Session : (Database Object >> Table)
==========================================
* To Create a Database Object of an Table we need to use DDL Command of "create".
* After Creating the Database object of an Table in Database, as programmer we need to insert data into table then we need to
use DML command "insert".
* After Inserting the data into Database as programmer we need to validate the information by using DRL Command "select"
Example : Creating sample Database table
========================================
Write a Query for Creating Database Table of "customers" information with following fields > CustomerId >> only numbers
> CustomerName >> string
> Gender >> string
> Age >> only numbers
> location >> string
Step-1
======
>> In order to create the Database table we will use DDL Command "create" and observe the syntax as below
create table <table_name>(
<column_name_1> <Datatype>,
<column_name_2> <Datatype>,
...........................
...........................
...........................
<column_name_n> <Datatype>,
);
NOTE: Naming Conventions need to be follow for any name in Oracle Database
==========================================================================
1) Every name in Database must begins with "Alphabet".
2) We need to follow the mentioned charset i.e.,a-z,A-Z,0-9,@,$,#,_
3) Names are not Case Sensitive.
4) Predefined keywords are not allowed.
5) Blank Spaces are also not allowed in the names.
Query For Creating Database table
=================================
SQL> create table ashokit_customers(
customer_id number,
customer_name varchar(50),
gender varchar(15),
age number,
location varchar(50)
);
OUTPUT
======
Table Created
Step-2:
=======
* Inserting the data into Database table by using DML Command "insert"
Syntax For Insert Command
==========================
1) insert into <table_name>(column_name_1,column_name_2.................column_name_n) values(value1,value2,.....valuen);
Example
=======
insert into ashokit_customers(customer_id,customer_name,gender,age,location) values(01,'Mahesh','Male',35,"Hyderabad");
insert into ashokit_customers(customer_id,customer_name,gender,age,location) values(02,'Ashok','Male',35,"Hyderabad");
2) insert into <table_name> values(value1,value2,value3,value4..................valuen);
Example
=======
insert into ashokit_customers values(03,'Rajesh','Male',40,'Mumbai');
3) insert into <table_name> values(&customerId,&customername,&gender,&age,&location);
Step-3
======
* Just Validating the Information by selecting data from Database Table using DRL Command "select"
Syntax for Select Command
=========================
select * from <table_name>; >> Retrieves all the information from given table
Example
=======
select * from ashokit_customers;
NOTE
====
sql >> set linesize 300;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++