slides rdbms 3

8/11/2019 Slides Rdbms 3

1/58


2/58


3/58


4/58


5/58


6/58


7/58

You cant write programs like the ones you would have done using C language

You can only write questions in English like language called queries which will fetch some data rowsfrom the database.


8/58


9/58


10/58

SQL has three flavors of statements. The DDL, DML and DCL.DDL is Data Definition Language statements. Some examples:CREATE to create objects in the databaseALTER to alter the structure of the databaseDROP to delete objects from the databaseTRUNCATE to remove all records from a table. The space allocated for the records is alsoremovedDML is Data Manipulation Language statements. Some examples:SELECT to retrieve data from the databaseINSERT to insert data into a tableUPDATE to update existing data within a tableDELETE to delete all records from a table. The space allocated for the records remains intactDCL is Data Control Language statements. Some examples:GRANT to give user access privileges to database objectsREVOKE to withdraw access privileges given with the GRANT commandCOMMIT to save the work doneROLLBACK to restore database to original since the last COMMIT


11/58

SQL supports various data types

IntegersDecimal numbers--- NUMBER, INTEGER .

Number is an oracle data type. Integer is an ANSI data type. Integer is equivalent of NUMBER(38)The syntax for NUMBER is NUMBER(P,S) p is the precision and s is the scale. P can range from 1to 38Floating point numbers---- FLOATFixed length character strings---- CHAR []

The CHAR data type is fixed length, with the maximum size of the column specified in parentheses.Specifying the size is optional, and the default size is 1 byte.The maximum allowed size in a CHAR data type column is 2000 bytes.

Variable length character strings --- VARCHAR2()A maximum size for the column should be defined. Unlike CHAR columns, VARCHAR2 columns arenot blank-padded with trailing spaces if the column value is shorter than its maximum specifiedlength. The range is values allowed for size is from 1 to 4000 bytes.

Dates-----DATEThe DATE data type stores date and time information. You can store the dates from January 1, 4712BC to December 31, 9999 AD.

LONGLONG columns can store up to 2GB of data. There can be only one LONG column in the tabledefinition.


12/58


13/58

The Arithmetic operators are used to calculate something like given in the example below:

Select * from employee where sal * 1.1 > 1000 ;

The logical operators are used to combine conditions like:

Select * from employee where (sal > 1000 AND age > 25);

The above two examples also illustrate the use of relational operators.


14/58


15/58


16/58


17/58


18/58

Constraints:

NOT NULL: Prevents NULL values from being entered into the column. These types of constraintsare defined on a single column. By default, Oracle allows NULL values in any column. A NOT NULLconstraint is defined at the column level; it cannot be defined at the table level.

PRIMARY KEY: Uniquely identifies each row of the table and prevents NULL values. A table canhave only one primary key constraint.

UNIQUE: Any unique column will not allow duplicate values to be present in it. However there can betwo or more than two NULL in the unique column (because Null is not equal to Null).


19/58


20/58

Constraints:


21/58


22/58

Constraints:

SelfRefrencing Foregn Key: When a foreign key refers the columns of the same table then it iscalled self referencing foreign key

Default Values:The default value specified will be used when you do not specify any value for the column whileinserting data. The default value specified in the definition should satisfy the data type and length ofthe column. If a default value is not explicitly set, the default for the column is implicitly set to NULL.


23/58


24/58

Constraints:

Column level ( highlighted in orange rectangle)

Table level (highlighted in blue rectangle signifies)


25/58

In the above example the Foreign Key has been specified as Table level constraint


26/58

In the above example the Foreign Key has been specified as Table level constraint


27/58

Used to modify the structure of a table by adding and removing columns

The ALTER TABLE statement cannot be used to change the name of a column or table

Column to be modified should be empty to decrease column length

Column to be modified should be empty to change the data type

If the table has only one column, the ALTER TABLE statement cannot be used to drop that columnbecause that would render the table definition invalid.


28/58


29/58

ALTER TABLE statement can be used to Add or Drop primary key constraint to / from a table

ALTER TABLE statement can be used to Add or Drop foreign key constraint to / from a tableALTER TABLE statement can be used to Add or Drop Unique constraint to / from a table

ALTER TABLE statement can be used to Add or Drop check constraint to / from a table

If a table already has a primary key, then adding a primary key using the ALTER TABLE statementresults in an error.The RDBMS will not allow a PRIMARY KEY constraint (using the ALTER TABLE statement) oncolumn(s) if the column(s) has NULL or duplicate values .


30/58

A table can have one or more Foreign key

Adding a foreign key constraint using ALTER TABLE command will result in error if the existing datain master or child table does not support the foreign key restriction


31/58


32/58

Most RDBMS restrict the droping of a table if it has attribute(s) being refered to by attribute(s) ofanother table.


33/58

TRUNCATE:

The TRUNCATE statement is similar to a DELETE statement without a WHERE clause, except forthe following:

TRUNCATE is very fast whether the table is large or small . DELETE generate s undo

information, in case a rollback is issued. TRUNCATE will not generate undo.TRUNCATE is DDL and so it performs an implicit commit .(you cannot rolled back).


34/58


35/58

It is possible that we do not provide the column names while inserting the record

but in this case we would pass values for all the columns in exactly the same order in which theyappear in the tableEg

Insert into customer values('C1','John','01-Mar-09','John1001','John@123');


36/58

When we wish to insert values only for few selected columns.

For e.g in a Customer table, we may know only the CustomerId, CustomerName,UserId,Passwordand not the DateOfRegistration

So, we may insert only values for CustomerId, CustomerName,UserId,Password . The value of the

remaining column will be represented as NULL by default.


37/58

DELETE command cannot delete column(s) from a table. It deletes only row(s)


38/58


39/58


40/58


41/58


42/58

Other ways of writing the same query with customised Columns Names:

SELECT FixedDeposit_No AS Customer Fixed Diposit No., Amount_In_dollar AS FixedDeposit Amount

FROM Customer_FixedDeposit;

Or

SELECT FixedDeposit_No AS CustomerFixedDipositNo. , Amount_In_dollar ASFixedDepositAmount

FROM Customer_FixedDeposit;


43/58

Distinct will filter repetitive occurrence (s) of a particular row in the output.

The output of the query using distinct will be always be in sorted orderThe sorting will be based on entire row of the output. (Refer the Demo)


44/58


45/58


46/58


47/58


48/58


49/58


50/58

BETWEEN:

The BETWEEN operator is used to test a range. BETWEEN A AND B evaluates to TRUE if the valueis greater than or equal to A and less than or equal to B.If NOT is used, the result is the reverse.


51/58

IN and NOT IN:

The IN and NOT IN operators are used to test a membership condition. When using the NOT INoperator, if any value in the list or the result returned from the sub query is NULL, the query returnsno rows.


52/58

LIKE:

The LIKE operator is used to perform pattern matching. The pattern-search character % is used tomatch any character and any number of characters. The pattern-search character _ is used to matchany single character.

ESCAPE key word is used when we want to give a different meaning to pattern-search characterEg if we are looking for values ending with % then we can use the statement

select * from MYTAB

where IR like '%\%' ESCAPE '\ ;

Where MYTAB is created as

create table MYTAB( IR varchar2(5));

And has the following data

IR

-----

12

13%

14


53/58

IS NULL:

To find the NULL values or the NOT NULL values, you need to use the IS NULL operator. The = or !=operator will not work with NULL values.IS NULL evaluates to TRUE if the value is NULL.

IS NOT NULL evaluates to TRUE is the value is not NULL.


54/58

Column alias names can be used in the ORDER BY clause.

select empno, sal, sal*.1 as "HRA", sal*.5 as "DA",

sal+ sal *.1+sal *.5 as "GROSS" from emp order by GROSS;


55/58

Not only can you use the column name or the column alias to sort the result set of a query,you can also sort the results by specifying the position of the column in the SELECT clause.


56/58


57/58


58/58

slides rdbms 3

Documents