SQL-1.pptx for database system and system query language

• 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. • It 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

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

• 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. 1. Framework (introduction) 2. Foundation (core) 3. CLI (call level interface) 4. PSM (persistent stored modules) 5. 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.

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) a. 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); 1. 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

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 Ssalary decimal (7,2);

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);

3. DATE • The Date data type is used to store date and time information. • For each date value the information stored 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).

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.

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

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: I. CREATE II. ALTER III.DROP IV. RENAME V. TRUNCATE

I. CREATE: It is used to create the database or its objects(like table, index, function, views, procedure, triggers. . .). Rules for defining table name: 1. 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);

• 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));

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));

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;

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;

DML (Data manipulation language) These commands manipulate of data present in database. The DML commands are I INSERT II UPDATE III DELETE I. 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.

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');

Ex: update std set gender=’f’; -- it will update all rows in the table update std set gender=’f’ where sno=1; -- it will update specified row in the table. II UPDATE: It is used to update existing data within a table. The syntax is, update<table_name> set <col_name> = <value> [where <condition>]; ex: delete from std where sno=1; -- it will delete specified row in the table. Delete from std; -- it will delete all rows in the table III DELETE: It is used to delete records from a database table. The syntax is: delete from <table_name> [where <condition>];

A. Commit B. Rollback C. Savepoint Transaction Control Language (TCL) 1. These commands deals with the transaction within the database. 2. A transaction executable statements and end explicitly with either rollback or commit statement and implicitly. Syntax: COMMIT [WORK]; Ex: COMMIT; A. COMMIT (SAVING TABLE CHANGES) 1. It is used to permanently save any transaction into the database. 2. Any changes made to the table contents are not saved on disk until user close the database, • The COMMIT command permanently saves all changes- • Such as rows added, attributes modified, and rows delete to any table in the database.

1. ROLLBACK command work like undo command. 2. Restore the database to its previous condition with the ROLLBACK command. 3. To restore the data to their pre-change condition. Syntax: ROLLBACK; Ex: ROLLBACK B. ROLLBACK ( RESTORING TABLE CONTENTS )

Ex: savepoint s1; /*Ex: SQL> insert into emp values(12,’manju’); SQL>insert into emp values(13,’sai’); SQL>savepoint s1; SQL> insert into emp values(14,’vani’); SQL>insert into emp values(15,’anu’); SQL>savepoint s2; SQL> insert into emp values(16,’rani’); SQL>insert into emp values(17,’vasu’); SQL>rollback to s2;*/ C. SAVE POINT: 1. Save point are like markers to divide a lengthy transaction to smaller transactions. 2. Save points are used in conjunction (Combination) with rollback, 3. To rollback portions of the current transaction. Syntax: savepoint <savepointname>; 1. In the above example we have to define two savepoint markers. 2. When rollback to s2 is given, whatever transactions happened after savepoint s2 will be undone.

Here • privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT. • object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE. • user_name is the name of the user to whom an access right is being granted. • public is used to grant access rights to all users. • WITH GRANT OPTION - allows a user to grant access rights to other users. Ex: grant all on std to user1; DCL(Data control Language) 1. These commands mainly deal with the rights, permissions and other controls of the database system. 2. Two types of DCL commands a. GRANT b. REVOKE. 3. Only Database Administrator's or owner's of the database object can provide/remove privileges (rights) on a database object. a. Grant: It is used to provide access or privileges on the database objects to the users. The Sntax is; grant<privilege_name> ON <object_name> TO <user_name |PUBLIC> [WITH GRANT OPTION];

Revoke: It is used to removes user access rights or privileges to the database objects. The syntax is, revoke<privilege_name> on <object_name> from <user_name |PUBLIC >; Ex: revoke all on std from user1;

The select statement retrieves information from the data base. The syntax is, select *[ALL/DISTINCT/Col1,Col2…Coln/expression/alias] from <table_name> [where<condition>] [group by <col>] [having<condition>] [order by <col>ASC/DESC]; Here Select - retrieve one or more columns * - select all columns Distinct - suppress duplicates Column/expression - selects the name column or an expression Alias - gives selected columns different headings From table - specifies the table containing the columns. SELECT DQL(Data Query Language)

Select specific columns Syntax: Select column-name1, column-name2 ……… from table-name; Ex: Select sno, sname from std; Selecting distinct rows: To select specific rows from a table we include a ‘where’ clause in the select command. SQL> Select distinct sname from std; Select command with ‘where’ clause To select specific rows from a table we include a ‘where’ clause in the select command. SQL>select *from std where sno=12; Select with order by clause command: This clause is used to arrange rows in ascending or descending order. SQL> Select * from std order by sname; Group by Clause 1. Group by clause can be used to divide the rows in a table into smaller groups. 2. Group function can be used to return summary information for each group. Select deptno,min(sal) from emp group by deptno; Select all columns Syntax: Select * from table-name; Ex: Select * from std;

1. Domain Integrity Constraints a. Not null b. Check 2. Entity Integrity Constraints a. Unique b. Default c. Primary key 3. Referential Constraint a. Foreign key CONSTRAINTS • Constraint is a rule that can be applied on a table or a column of a table. • Constraints can column level (or) table level column level constraints can be apply a column a table level constraints can be apply a whole table. • These ensures the accuracy and reliability of data in the Table. • If these is any violation between the constraint and the data action, the action is aborted. • prevents user from entering invalid data into tables are called constraints. Constraints can be categorized into following,

1. Domain Integrity constraints a. Not Null 1. By default all columns in a table allow NULL values. 2. When a NOT NULL constraint is enforced on a column in a table, It will not allow NULL values into that column. But It will allow duplicates. Example: create table std(sno number(4) constraint en0_not not null, sname varchar2(20), fname varchar2(20) gender char(10), course varchar2(20)); Syntax: Create table <table name> (<column name> <data type> constraint <constraint name> NOT NULL);

b. Check constraint 1. These are rules govern logical expressions or Boolean expressions. specifies a condition that must be true. Syntax: Create table <table name> (<column name> <data type> constraint <constraint name> check (column name with condition)); Example: Create table stud (marks number (5) constraint c1 check (marks>35));

2. Entity Integrity Constraints. a. Unique 1. This constraint allows only unique values to be stored in the column. 2. Oracle rejects duplication of records when the unique key constraint is used. 3. It can also allow NULL values. Since two NULL values are not allowed because it is duplicate. Syntax: Create table <table name> (<column name> <data type> constraint <constraint name> unique (column name)); Ex: create table item(item_no number(4) constaint eno_uni unique, iname varchar2(20), qty number(4), price number(5,2) amount number(8,2));

b. Default constraint: 1. Its read automatically value when user not enter value c. Primary key constraint: 1. It avoids duplication of rows and does not allow NULL values. 2. Primary key is the combination of NOT NULL and UNIQUE. 3. Primary key is a single field or combination of fields that uniquely defines record. 4. Table can have only one primary key. 5. In oracle a primary key cannot contain more than 32 columns. 6. It is also used to set the relations between the tables. Syntax: Create table <table name> (<column name> <data type> constraint <constraint name> primary key); Ex: Create table student(sno number(10) constraint P primary key, sname varchar2(10)); Syntax: Create table <table name> (<Column name> <data type><Default> <value>) Ex: CREATE TABLE student(id number (3), name varchar2 (20) , college varchar2(30) default ‘Sri Ramakrishna Degree College ’);

Ex: create table product_details ( product_no number(5), product_name varchar2(25), product_price number(11,2), constraint pk1 primary key(product_no, product_name, product_price)); COMPOSITE PRIMARY KEY : If two or more attributes together form primary key then it is called composite key. the combination of columns values should be unique. Composite primary key is always a table level constraint. we cannot go for column level. Syntax: constraint <constraint_name> <constraint_type>(col1,col2...);

3. Referential Constraint a. Foreign key 1. The referential integrity constraints enforce (impose) the relationship between the tables 2. Foreign key is a primary key in the same table or another table It helps in creating a parent child relation ships between the tables. 3. A referential integrity constraint assigns a column as a foreign key. 4. Child table primary key is called foreign key 5. Parent table primary key is called referenced key is called parent table. Syntax: Create table <table name> (<column name> <data type>, constraint <constraint name> references <table name> (column name));

Example : Create table student (Stud_ID number (10) primary key, Stu_Name varchar 2(25), Cource char) 20)); Create Table Department ( Dept_name varchar (120) NOT NULL, Stud_Id number, foreign key (Stud_Id) Reference Student (Stud _Id));

Lower: This string function convert input string in to lower sting Ex: select lower(‘ORACLE’) from dual; --- oracle Upper: This string function will convert input string into upper sting Ex: select upper(‘oracle’) from dual; ---ORACLE Initcap (Initial cap): This string function is used to capitalize first character of the input string. Ex: select initcap(‘oracle’) from dual; --Oracle Functions: functions take arguments and always return value. Sql supports Single row and Multiple row functions. Single row functions: These functions operate on single rows only and return one result per row. The single row functions are character, number, date and conversion functions. Character functions: Accept character input and can return both character and number values. The character functions are lower, upper, initcap, substr, instr, lpad, rpad, ltrim, rtrim, replace, length, Translate, ascii, chr.

Length: When the length function is used in a query. It returns length of the input string. Syntax: Length(string) Ex:SQL>select length(‘srdc’) from dual; ---4 Substr: It fetches out a piece of the string beginning at start and going for count characters, if count is not specified, the string is fetched from start and goes till end of the string. If count is not specified, the string is fetched from start and goes till end of the string. Syntax: substr(column/expression,m[,n]) Ex: select substr(‘welcome’,1) from dual; ---welcome Select substr(‘welcome’,4,4) from dual; ---come Select substr(‘welcome’,-4,2) from dual; ---oc Instr: returns the numeric position of a specific character in a given string. Syntax: instr (column/expression,’string’,[‘m],[n]) Ex: select instr(‘welcome’,’e’) from dual; --2 Select instr(‘welcome’,’e’,1,2) from dual; --7

Number functions Accept numeric input and return numeric values. The numeric functions are abs, round, ceil, floor, sqrt, mod, sign, power, sin, cos, tan, trunc, least, greatest, m0d, exp. Round: round the column expression or value to n decimal places. If n is omitted, no decimal places. If n is negative, numbers to left of the decimal point are rounded) Syntax: round(col/expr. n) Ex: select round(45.923,2), round(45.923,0), round(45.923,-1) from dual; ABS() : this function always returns positive number. Syntax: abs(negative number); Ex: select abs(-100) from dual;

LEAST: Returns the least of the specified list of values. Ex: select least(10,2,45,6) from dual; Power: this function will return power of raise value of given number Syntax: power(number,raise) Ex: select power(4,2) from dual; --- 16 Sqrt: this function return the square root value. Ex: select sqrt(25) from dual; GREATEST: Returns the greatest of the specified list of values. Ex: select greatest(10,2,4) from dual;

SQL-1.pptx for database system and system query language

More Related Content

Similar to SQL-1.pptx for database system and system query language

More from ironman82715

Recently uploaded

SQL-1.pptx for database system and system query language