STRUCTURED QUERY LANGUAGE

1. STRUCTURED QUERY LANGUAGE

2. SQL is the standard command set used to communicate with the database management systems. • Pronounced as sequel. • SQL consists of • RESERVED WORDS • USERDEFINED WORDS

3. Reserved words are a fixed part of the SQL language. • User defined words are made up by the user and represents the names of various DB objects, such as tables, columns, views, index • SQL is not CASE SENSITIVE. • SQL is a non-procedural language

4. What is SQL used for • Using SQL one can create and maintain data manipulation objects like tables, views, sequences etc.. • The objects are be created and stored in the db server’s HARD DISK.

5. DML,DDL • Data Definition Language : The SQL statements used to create objects are called DDLs. Eg: Create, alter, drop, truncate, • Data Manipulation Language : The SQL statements used to manipulate data within these objects is called as DML. Eg: Insert, Update, delete, Explain plan, Lock Table

6. Data Types used in Oracle • CHAR(size) • VARCHAR(size) /VARCHAR2(size) • NUMBER(P,S) • DATE

7. CHAR(size) : • Used to store character string with number of characters equal to the size specified in brackets. • A char string can hold a maximum255 characters • NULL value is padded if the length of the string is less than that of the size specified.

8. NUMBER(P,S) • used to store numbers(fixed or floating point) • Precision determines the maximum length of the data. • Scale determines the number of places to the right of the decimal. • Default of scale is omitted then the value is 0. • Precision length is 38 .

9. DATE : • This data type is used to represent date and time. • DD-MM-YY is the standard format • Also entered as DD|MM|YYYY

10. CREATE STATEMENT • Used to create a table in the data base CREATE TABLE <table name > ( <column name1> datatype(size), <column name2> datatype(size), <column nameN> datatype(size) );

11. INSERT STATEMENT • Insert statement allows to insert a new row in the db. INSERT INTO <table name> VALUES (<column value1, column value2,…column valueN>); • Enclose date and character values in single quotes.

12. To insert values only in specified columns: INSERT INTO <table name>(<column name1, column name2>) VALUES (<column value1,column value2>);

13. To insert date in a Date type column either specify the date or use the SYSDATE built-in function. • In the following command SQL prompts for a value for each column INSERT INTO <table name> VALUES (&column name1, ‘&column name2’,… ‘&column nameN’);

14. Statement to view the table contents: Select * from <table name>; Select <column name> from <table name>; • Statement to view the table structure: Describe <table name>; OR Desc<tablename>

15. UPDATE STATEMENT • The update statement is used to modify existing data values in a table. • Updates all the rows in a table UPDATE <table name> SET <column name1> = value/exp ,… <column nameN> =value/exp;

16. Updates the set of rows in a table that satisfy the where clause UPDATE <table name> SET <column name1> =value/exp, …< column nameN> =value/exp WHERE <column nameN> = value/exp;

17. DELETE STATEMENT • The delete statement is used to remove rows from table. • To remove all rows from a table DELETE FROM <table name>; • To remove selected set of rows from a table DELETE FROM <table name> WHERE [search condition];

18. ALTER • To change the definition of an existing table in the db: adding, deleting and changing the definitions of columns: • To Add new columns: ALTER TABLE <table name> ADD(<new column name1> datatype(size), … <new column nameN> datatype(size));

19. To alter a column definition: ALTER TABLE <table name> MODIFY(<column name1> new-datatype(new-size)); • To delete a column from a table: ALTER TABLE <table name> DROP COLUMN (<column name1>, <column name2>,…<column nameN>);

20. RENAME & DROP • To rename a table: RENAME <old table name> TO <new table name>; • To delete a table: DROP TABLE <table name>;

21. Drop Table deletes the row as well as the table definition • Truncate Table <table name>deletes only the rows not the table definition. Truncate cannot be rolled backed.

22. VIEWING DATA • Once data has been inserted into a table you can view the data in the table using the select statement: • SELECTcolumnname 1 … columnname n FROMtablename; Shows data from the given columns • SELECT * FROM tablename; Shows the data from all the columns

23. When a WHERE clause is added to the SQL statement the oracle server compares each record from the table with the condition specified in the where clause. • SELECT * FROM <table name > WHERE <search condition>; • SELECTcolumnname,columnname FROM <tablename > WHERE <search condition>;

24. Create table student (rollnumber NUMBER(4), name VARCHAR(20)); INSERT into student VALUES (111,’ANASHWARA’); INSERT into student VALUES (222,’DEEPTHI’);

25. select * from student whererollnumber =111; • select name from student whererollnumber =111;