1 / 25

STRUCTURED QUERY LANGUAGE

STRUCTURED QUERY LANGUAGE. SQL is the standard command set used to communicate with the database management systems. Pronounced as sequel. SQL consists of RESERVED WORDS USERDEFINED WORDS. Reserved words are a fixed part of the SQL language.

vila
Télécharger la présentation

STRUCTURED QUERY LANGUAGE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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;

More Related