Deborshi and Surangan
8th July (1st day)
select * from <insert Table name here>;
select <insert column name>,...., from <insert Table name here>;
//these will allow you to get data from an existing table
create table STUDENT26(Name varchar2(50), Roll integer, Department varchar2
(20), Marks Number(4));
//this will allow you to create a table, where
STUDENT26 = name of table
Name, Roll, Department, Marks = name of columns
varchar2 (<insert size of String>) = for inserting Strings
integer = for integer value
Number(<insert size of Number>) = for integer value
Insert into STUDENT26(Name, Roll, Department, Marks)
values('A',1,'CSE',20); //this will insert 1 row at a time
OR
Insert into STUDENT26 values('A',2,'CSE',20) //this will also insert 1 row at
a time but we need to be aware of the data types to which we are adding the values.
//if we want to add multiple queries at once,
Insert into STUDENT26 values('&Name', &Roll, '&Department', &Marks);
//use / to continue inputting values
//use commit; to save to the database.
DESC STUDENT26;
//gives description of Table
--------------------------
15th July (2nd day)
//to select based on an attribute from the table:
select * from <Table name> WHERE <Column name> = '<Value>'
for eg: select Name,dept from S1 where Roll = '2'
//to order the column and display it in ascending or descending (for descending we
use desc keyword)
select * from <Table Name> ORDER BY <Column name> <use desc for descending sort>
for eg: select * from S1 ORDER BY Marks desc
//to rename a table:
rename <old table name> to <new table name>
for eg: rename Student to S1
// to find dept
select disctint dept from S1;
// to delete
delete from S1;
sql>drop table S1;
sql>trunquate table S1;
sql>delete from S1 where roll = "4";
//to update a value
update <Table Name> SET dept = 'CSE' //will convert every entry into CSE
OR
update <Table Name> SET dept = 'CSE' where Roll = '5'; //for converting only
specified field
//to change the schema of a table (i.e the column name)
we use ALTER
we can add, drop, modify using ALTER.
//to add a column:
ALTER table <table name> ADD (Gender Integer); //this will create the schema
but wont have the values inside it so we have to use "update"
//to drop a column:
ALTER table <table name> DROP Column Gender; //we have to specify that we are
dropping a column i,e, Colmn here.
//to modify a column
ALTER table <table name> MODIFY (Roll Number (10)); //we cannot change column
data type from 1 to another + we cannot change size from bigger to smaller value.
//to change name of column:
ALTER table <table name> Rename COLUMN <old colmn name> TO <new column name>;
-----------------------------------
----
22 July (3rd day)
//Pattern matching
we use LIKE keyword for pattern matching
//to see if a name starts with A
LIKE 'A%'
//to see if a name ends with A
LIKE '%A'
//to see if the middle contains AB
LIKE '%AB%'
//to see if start = A end = B
LIKE 'A%B'
//to see if 2nd character is A
LIKE _ (<--this is a blank space) A%
example: SELECT * from STUDENT26 where Name LIKE '%A'; //this will find name ending
with A.
//if there is a special symbol say @, how will we match the pattern?
----not in syll----
select * from STUDENT26 where REGEXP_LIKE (Name <i.e. the column name>,<insert
special char for which we need the pattern, in '[]'>, <insert 'i' or 'I' if you
want lowercase sensi or uppercase sensi of the other characters>)
//Logical Operators
//we have AND OR NOT BETWEEN
//for and & or
we use similar syntax
eg: SELECT * from STUDENT26 where Name LIKE '%A' <you can use AND OR here i'll use
and> AND Marks > 100;
//for not
SELECT * from STUDENT26 where NOT (Department = 'CSE');
//for between
SELECT * from STUDENT26 where marks BETWEEN 17 AND 200;
//Aggregate function
//we have MAX MIN AVG SUM COUNT
select MAX(marks) from S1;
select MIN(marks) from S1;
select AVG(marks) from S1;
select SUM(marks) from S1;
//count means we count the number of 'NON NULL ROWS' unless we specify *
select COUNT(Name) from S1; //for counting all non-null names
select COUNT(*) from S1; //for counting all names (even considering NULL)
-----------------------------------------------------------
29th July (4th day)
//we will do "group by" and "having" (having has same function as where)
group by --> will divide according to the group
eg:
select dept, COUNT(name) from S1 group by (dept) having COUNT(name) > 1;
//IN and NOT IN
select * from S1 where name IN('RAM','D'); //will only show names having these
names.
select * from S1 where name NOT IN('RAM','D'); //will show everything except the
names.
//UNION and INTERSECTION
-->UNION --> will combine all values and show distinct only//UNION ALL will show
all values
-->Intersect--> will show common values
-->MINUS-->
to union/intersect/minus:
select NAME from T1 UNION/UNION ALL/INTERSECT/MINUS select NAME from T2;
//SUB QUERY
//nested query has inner brackets and outer brackets. inner executes first and the
outer.
for eg:
select name from S1 where marks = <can write = or IN> (select MAX (Marks) from S1);
//To find details of a particular rank
select * from (select name, rollno ,marks, dense_rank() over (order by marks desc)
as Rank_NUM FROM S1) where Rank_NUM=5; //for 5th rank
//How to primary key table
to make a field mandatory we should add NOT NULL to it.
if table has a primary key, then no duplicates are allowed.
eg: roll number is primary key.
create table T1 (Name varchar2(20), Roll Integer primary key, Dept varchar(20) NOT
NULL);
//Foriegn Key links tables and normalizes the values and removes redundancies.
create table T2 (Roll Integer REFERENCES T1, Name varchar2(20),Dept varchar(20) NOT
NULL);
-----------------------------------------
---------------
5th August (5th day)
JOINING --> when normalisation is done to remove redundanies we use joining. We
need atleast 1 common field (i.e. primary & foreign key relation is required)
We will do:
Inner join
Outer join --> Left Right and FullOuterJoin
Inner join will show common portion
Left Join will show left table + common part
Right '' '' '' right table + common part
FullOuterJoin = join both left and right and gives common part too.
syntax:
Select S1.Name, S1.Roll, S2.d_id from S1 <Insert join name i.e. INNER JOIN, LEFT
OUTER JOIN, RIGHT OUTER JOIN etc> S2 <show primary key relation with ON> ON S1.Roll
= S2.Roll <add where clause if needed>
PlSql:
Combination of Procedural lang + Structural Query Lang (i.e SQL)
4 sections:
DECLARE section = variables used are declared
BEGIN "" = write PLSQL code
Exception "" = same as OOPL
END "" = end the thing.
//Problem : Give account number + Check if balance is less than 5k then -100 as
fine.
\\Create table Acct-mstr with attri. Acct_no varchar(20) and cur_bal number (11,2)
Declare // first section
mcur_bal number (11,2); //,2 = . er por 2 toh ghor | since we are keeping cur_bal
value here, we need same data type + size like cur_bal.
mAcct_no varchar2(20);
mFine number (4):= 100;
mMin_bal constant number (7,2):= 5000.00;
Begin
mAcct_no:= &mAcct_no; //& is to take input through keyboard
select cur_bal INTO mcur_bal from Acct_mstr where Acct_no = mAcct_no; //into used
to select value from table to mcur.
IF mcur_bal <mMin_bal THEN update Acct_mstr set cur_bal = cur_bal - mFine where
Acct_no = mAcct_no;
END IF;
END;
/ \\slash is to run the command.
------------------------
2nd September (6th day)
#Create user.
create user <username> identified by <password>;
#Grant (to give access to enter to database)
grant create session to <username>;
grant all on <table name> to <username>;
#Revoke
revoke all on <table name> from <username>;
#
select * from <mother user>.<tablename> (this is to access the table OF mother
user. basically jekhane databse ache oita acess korte chae).
#to see system date:
select sysdate from dual;
