RDBMS

1.SQL Create Table

The create table statement is used to create a new table. The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns

.Syntax:

SQL> Create table tablename

( Column1data type [default exp] [Constraint],

Column2 data type [default exp] [Constraint],

Column3 data type [default exp] [Constraint],

);

Example:

SQL> Create table employee

(Name varchar2 (15),

Eid number (6),

Age number (3),

City varchar2 (30),

State varchar2 (20)

);

Table created.

2.  If you want to see the structure of the table then use the following command on SQL prompt.

·       SQL > DESC employee;

 3.ALTER Table Command

After creating the table one may have need to change the table either by adding new columns or by modifying tile existing columns.

ALTER TABLE TableName ADD ColumnName DataType;

OR

ALTER TABLE TableName DROP ColumnName;

4.Drop Table-This command that removes an entire SQL table.

 Syntax: SQL> Drop Table Tablename [cascade constraint];

Example:

SQL> DROP TABLE Persons;

Table dropped 

5.SQL DELETE

This statement removes one or more records from a table.

Delete from <table> [where <condition>];

For example

SQL > DELETE FROM student

WHERE ROLL=2214;

1 row deleted.

6.The TRUNCATE statement will delete all rows in the  table, without deleting the table itself.

SQL>    truncate table <table> ;

7.SQL --To make changes or modifications in the records of the table, the UPDATE statement is used.

 The syntax is

UPDATE <tablename>

SET <columnnamel> = <expression> [, <column narne2>= <expression> ]

........... [,<columnnameN>=<expression> ]

[WHERE condition];  

For example

Update INSTRUCTOR

SET SALARY = 10000;

5 ROW UPDATED 

8.SQL INSERT INTO--The INSERT statement is used to add new row to a table. 

The Syntax is

INSERT INTO <table_name>

[( <columnname1>[,<columnname2> ] .....[<columnnameN>])]

VALUES (<columnvalue1>[,<columnvalue2> ] ..... [,<columnvalueN>]);  

SQL> CREATE TABLE INSTRUCT

(INST_ID NUMBER(4) PRIMARY KEY ,

NAME VARCHAR2(15) NOT NULL,

DOB DATE NOT NULL,

POST VARCHAR2(20),

PHONE NUMBER (8));

Table Created.

SQL> INSERT INTO INSTRUCTOR

2 (INST_ID, NAME, DOB, POST, PHONE)

3 VALUES

4 (l00, 'DINESH', '15-AUG-78', 'LECTURER', 28378);

1 row created.

SQL> SELECT * From INSTRUCTOR;

This statement will retrieve all the rows from the INSTRUCTOR table.

SQL DATE--Date functions operate on values of the DATE datatype.

ADD_MONTHS (dt, i): Returns the date dt plus i month and i is a decimal number. 

CURRENT _DATE: Returns the current date 

LAST_DAY(dt) Returns the last day of the month for the date dt. 

MONTHS_BETWEEN (dt1, dt2): Returns the number of months that d11 is later than dt2

NEXT_DAY(dt, s): Returns the date that corresponds to the next day of week specified by the string s following the date dt. 

SYSDATE Returns the current date/time, takes no arguments.

ROUND(dt[, fint]) : Returns the date dt rounded to the granularity specified in the date-format string fmt.

TRUNC(dt[, fmt]) Returns the date dt truncated to the granularity specified by the format string fmt.

SQL LIKE-- The LIKE condition is used to perform wildcard searches of valid search string values.

% denotes zero or many characters. _ denotes one character.

Examples using % wildcard:

SELECT ename, empno, job, sal

FROM emp

WHERE ename LIKE 'S%';

SQL BETWEEN--The SQL BETWEEN & AND operator selects a range of data between two values.

For example

Select * from employee where sal between 1000 and 3000;

SQL IN--The IN operator allows you to specify multiple values in a WHERE clause.

For example

Select * from emp where deptno IN(10,20,30);

SQL DISTINCT

The SQL DISTINCT query is used with the SELECT keyword retrieves only unique data values 

For example

Select distinct ename,job from emp;

SQL GROUP BY

This is used along with the SQL aggregate functions and specifies the groups where selected rows are placed. 

Syntax: SQL> SELECT column_name, aggregate_function (column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

For example

SQL>SELECT JOB, AVG (SAL) FROM EMP GROUP BY JOB;