SQL.pptx structure query language in database management system

This is using in structure query language

1. SQL stands for Structured Query Language. Oracle is a Relational Database Management System (RDBMS). It is used for storing and managing data in relational database management system (RDMS). Oracle being RDBMS, stored data in tables called relations . These relations are data can be representation in two-dimensional The rows are called tuples it represents records The columns called attributes it represents pieces of information contained in the record . I t is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables. Structure query language is not case sensitive . Generally, keywords of SQL are written in uppercase. Statements of SQL are dependent on text lines . We can use a single SQL statement on one or multiple text line. Introduction to ORACLE

2. History of SQL The SQL language was originally developed at the IBM research laboratory in San José, in connection with a project developing a prototype for a relational database management system called System R in the early 70s . The first database management systems based on SQL became available commercially by the end of the 70s . At the same time, relational database management systems based on other languages were published.   In 1986 , the first SQL standard was approved by ISO and ANSI . In 1989 , an integrity enhancement was appproved by ISO , containing, among other features, the specification possibility for keys ,  foreign keys and some other constraints In 1992 , the new version SQL-92 (also called SQL2 ) was approved. It contained large enhancements to the language. the basic level, mainly containing the core of the old SQL/89 the intermediate level , containing new data types, operations and structures the full SQL with even more data types and structures

3. In 1995 , the SQL/CLI call level interface was approved, i.e. the interface specification for use through programs. The standard specifies the ODBC interface . In 1996 , the SQL/PSM (persistent stored modules) was approved as a database procedure specification language. In 1999 , the new version SQL-99 (also called SQL3 ) was approved. The standard is divided into five parts. Framework (introduction) Foundation (core) CLI (call level interface) PSM (persistent stored modules) Bindings (to programming languages) The embedding of  SQL  into a Java program ( SQLJ ) was approved on 2000 . At least the standards for multimedia enhancements and data warehouse features are being developed.

4. DATA TYPES in SQL It specified which type data and size can be stored in a specific field or it specified the field size and type in a file. a. Number(L,D) b. Integer c. Smallint d. Decimal( l,d ) Number(L,D): 1 . L means length, D means Decimal numbers 2 . The declaration Number (7,2) indicates numbers 3 . That will be stored with two decimal places and may be up to seven digits long, 4 . It includes the sign and the decimal place. Examples: 12.32, -134.23 Syntax: Column-name data-type (L); or Column-name data-type (L, D) Ex : Sno number (3); or sno number(7,2); Numeric: The number data type is used to store zero, positive and negative values. User can specify a fixed point number using the following form There are different types of data types

5. b. Integer : It may be abbreviated as INT Integers are (whole) counting numbers, So they cannot be used to store numbers that require decimal places c. Smallint : Like Integer, but limited to integer values up to six digits. If your integer values are relatively small, use smallint instead of Int. Syntax : Column-name data-type (L); Ex : Sno integer (3); Syntax : Column-name data-type (L); Ex : Sno smallint (3); age smallint (3); d. Decimal( l,d ) 1. Like the number specification, but the storage length is a minimum specification. 2. That is, greater lengths are acceptable, but smller ones are not. 3. Decmal (9,2), decimal(9), and decimal are all acceptable. Syntax : Column-name data-type (L); or Column-name data-type (L, D) Ex : Sno decimal (3); or S salary decimal (7,2);

6. 2. Character The character data type is used to store character ( alphanumeric) data. This can be fixed length or variable length a. Char(L) b. Varchar (L) or Varchar2(L) a. Char(L) Fixed-length character data fro up to 255 characters. If you store strings that are not as long as the char parameter value The remaining spaces are left unused. Therefore, if you specify char (25), each stored as 25 characters Syntax : Column-name data-type (L); Ex: city char(18); b. Varchar (L) or Varchar2(L) Variable-length character data The designation varchar2 (25) will let you store characters long. However, varchar will not leave unused spaces. Oracle automatically converts varchar to varchar2 Syntax : Column-name data-type (L); Ex: Sname varchar (25); Syntax : Column-name data-type (L); Ex: Sname varchar2(25);

7. 3. DATE The Date data type is used to store date and time information. For each date value the information s tored is, Century, Year, Month, Day, Hour, Minute, Second The default format of the date data type is ‘ DD-MON-YY ’. The format can be changed with NLS_DATE_FORMAT command. Syntax : Column-name DATE Ex : date_of_birth date 4 . Raw (Size): Stores binary data of length size. Maximum size is 2000 bytes. One must have to specify size with RAW type data, because by default it does not specify any size. 6 . LOB : is use to store unstructured information such as sound and video clips, pictures up to 4 GB size. 5 . Long Raw: Store binary data of variable length up to 2GB (Gigabytes).

8. 7. CLOB: A Character Large Object containing fixed-width multi-byte characters . Varying width character sets are not supported. Maximum size is 4GB . 9. BFILE: Contains a locator to a large Binary File stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4GB .   8. BLOB: To store a Binary Large Object such a graphics, video clips and sound files . Maximum size is 4GB .

9. SQL Commands SQL commands are instructions . It is used to communicate with the database. It is also used to perform specific tasks, functions , and queries of data. SQL can perform various tasks like create a table , add data to tables , drop the table, modify the table, set permission for users. Commands can be classified into five types

10. 1. Data Definition Language (DDL) DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. All the command of DDL are auto-committed that means it permanently save all the changes in the database. Here are Five commands that come under DDL: CREATE ALTER DROP RENAME TRUNCATE

11. I. CREATE: It is used to create the database or its objects (like table, index, function, views, procedure, triggers . . .). Rules for defining table name: Table name always start with an alphabet. 2. The length of table name and column name cannot exceed more than 30 characters. 3. Table name cannot allow blank spaces, hyphens but it allows underscore as special character. 4. Table name cannot contain oracle reserve words. 5. Every column in the table must be separated by comma(,). The syntax is,   create table <table_name>( col1 data_type(size), col2 data_type(size), . . . Coln data_type(size)); Ex: create table student ( sno number(4), name varchar2(30), fname varchar2(30), gender char);  

12. To change (alter) table structure by changing attribute character and by adding columns. All changes in the table structure are made by using the ALTER TABLE command. ADD, MODIFY and DROP keywords That produces the specific change user want to create Use ADD to add a column, MODIFY to change column characteristics and DROP to delete a column from a table Most RDBMSs do not allow you to delete a column (unless the column does not contain any value). The alter table command can also be used to add table constraints. II. ALTER Ex:- SQL> alter table student add( Age number(3)); Table altered. ADDING A COLUMN: Alter an existing table by adding one or more columns Syntax : Alter table table-name add (column name <data type> (size));

13. EX:- SQL> alter table student modify( sno decimal(3,1)); Table altered. CHANGING (MODIFY) A COLUMNS DATA TYPE : Some RDBMSs such as oracle, do not let you change data type unless the column to be changed is empty. Syntax: Alter table table-name modify (column name <data type> (size)); EX: SQL> alter table student modify ( sname varchar2( 40 )); CHANGING (MODIFY) A COLUMN’S DATA CHARACTERISTICS The column to be changed already contains data, make changes in the column’s characteristics if those changes do not alter the data type. Increase the width of the column size. Syntax: Alter table table-name modify (column-name data-type (new size));

14. RESTRICTION ON ALTER COMMAND : The alter table command cannot perform the following 1. Change the name of the table 2. Change the name of the column 3. Decrease the size of a column it table data exists. DROPPING A COLUMN: 1. User wants to delete a table column by using drop 2. Some RDBMSs impose restrictions on attribute deletion. 3. The attribute that are involved in foreign key relationships not delete an attribute of table that contains only that one attribute. Syntax : Alter table-table-name drop column column-name; Ex: SQL> alter table student drop column age ;

15. 3. Rename: It is used to rename an object existing in the database. The syntax is, rename< old_name > to < new_name >; ex: rename student to std ;   4. Drop : It is used to delete the database objects from the database permanently. The syntax is , drop table < table_name >; ex: drop table std ; 5. Truncate : It is used to remove all records from a table, including all spaces allocated for the records are removed. The syntax is, truncate table < table_name >; ex: truncate table std ;

16. DML (Data manipulation language) These commands manipulate of data present in database. The DML commands are I INSERT II UPDATE III DELETE   INSERT : It is used to insert the data into the database objects. The preceding data entry lines: 1. The row contents are entered between parentheses. Note that the first character after value is a parenthesis and that the last character in the command sequence is also a parenthesis. 2. Character (String) and date value must be entered between apostrophes (‘). 3. Numerical entries are not enclosed in apostrophes. 4. Attribute entries are separated by commas. 5. A value is required for each column in the table.

17. Syntax : INSERT INTO tablename VALUES( value1, value2, value3……); SQL>insert into student values( 1 ,'ram‘,’ venkatesh ’, ’Male’ ); 1 row Inserted SQL>insert into student values(2,’ Hari’ ,’ Ramudu , ’Male’ ); 1 row Inserted SQL>insert into student values( 3 ,’ krishna ‘,’ Narayana’ , ’Male’ ); 1 row Inserted SQL>insert into student values( 4 ,’ Rahul ‘,’ varun ’, ’Male’ ); 1 row Inserted SQL>insert into student values( 5 , ’Swathi ‘,’ venkatesh ’, ’FeMale’ ); INSERTING ROWS WITH OPTIONAL ATTRIBUTES The attributes that have required values, by listing the attribute names inside parentheses after the table name. Syntax : INSERT INTO tablename ( cumnname , columname …..)values(value1,value2….); SQL> insert into student ( sno , sname )values(9,'saran');