Create table: – Create table statement used in SQL for creating a new table. This statement is a part of DDL. The column name must be specified among the data types. Each table must have at least one column. Tables are basic structure where data stored in the database. Tables are divided into rows and columns. Each row represents one piece of data and each column can be thought of representing a component of that piece of data e.g., if we have a table for recording customer information, then the columns may include information such as name, address, city, country, birth date and so on. As a result, when we specify a table, we include the column headers and data type for that particular column. Syntax of create table command is –
CREATE TABLE <Table Name>(ColumnName1 DataType1 Size1[,ColumnName2 DataTupe2 Size2, _ _ _ ]);
For example –
CREATE TABLE EMP(ECode Number(6),EName Varchar2(50),Dept varchar2(50));
Insert: – Once a table is created the most thing to do load this table with data to be manipulated later. This data can be inserted command. When inserting a single row of data into the table, the insert operation:
- Create a new empty row in the database file.
- Loads the values passed into the column field.
The syntax of the INSERT command is –
INSERT INTO <Table Name> VALUES(<List of values>);
The word values must precede the list data to be inserted. An example –
INSERT INTO EMP(1001,’Ramesh Kumar’,’Production’);
Select: – The SELECT command retrieve information from the database. Using a SELECT commands, we can do the following –
- Selection: – We can use the selection capacity in SQL to choose the rows in a table that we want returned by a query. We can use various criteria to selectively restrict the rows that we see.
- Projection: – we can use the projection capability in SQL to choose the columns in a table that we want returned by our query. We can choose as few or as many columns of the table as we require.
- Join: – We can use the join capability in SQL to bring together data stored in different table by creating link between them.
Syntax of SELECT command is –
SELECT [DISTINCT] */[<Column Names> [alias], _ _ _ ] FROM <Table> [WHERE <Condition>];
For example –
SELECT * FROM EMP;
Delete: – The DELETE command used in SQL to delete one or more rows from existing table. This command is useful for deleting all rows of existing table. This command can be used with or without condition. If we use this command without any condition entire rows of existing table will be deleted and if we use this command with condition, one or more rows may be deleted if it return true syntax of DELETE command is –
DELETE <Table Name> [WHERE<Condition>];
For example –
For deleting all rows
DELETE EMP FOR ECODE>1000;
Only those rows are deleted from table EMP which ECODE column is greater than one thousand.
Grant: – Objects that are created by user are owned controlled by the user. If a user wishes to access of the object will have to give permission for such access. This is called granting of privileges.
In a multiuser environment, we want to maintain security of the database and use. With oracle server database security, we can do the following –
- Control database access.
- Give access to specific objects in the database.
- Confirm given and received privileges with oracle data directory.
- Create synonyms for database object.
Syntax of GRANT statement is –
GRANT <Object Privilege> ON <Object Name> To <User Name> [WITH GRANT OPTION] GRANT <Database privileges[,Database privilege, _ _ _ ]> TO <User Name>[,User Name, _ _ _]> IDENTIFIES BY <PASSWORD>[,PASSWORD, _ _ _ ]>;