Background
Today has been all about databases. Some posts written earlier today -
- Difference between DML and DDL statements in SQL
- Difference between a stored procedure and user defined function in SQL
Note : This SQL queries are in fact PL/SQL and will be executed on oracle database 11g R2. I am using sqlfiddle site to test the queries. You can also do so.
Table Creation
Lets first create a database to work on. I am going to create a database named EXPENDITURE with columns as NAME representing the name of person whose expense is and the column AMOUNT which is the amount due for a person.
create table EXPENDITURE (NAME varchar(255), AMOUNT int);
Add Primary key constraint and Insert operation
Now lets go ahead and set constraints of primary key on our table . Primary key is a column of set of columns that uniquely define a row. In our case it will be column NAME. So there cannot be two rows in the table with same entry for NAME. Also lets add some dummy values to our table -
alter table EXPENDITURE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (NAME);
insert into EXPENDITURE values ('JOHN',25);
insert into EXPENDITURE values ('SAM',127);
insert into EXPENDITURE values ('EDWARD',12);
insert into EXPENDITURE values ('PENNY',1024);
Now lets print the values in the database -
select * from EXPENDITURE; | NAME | AMOUNT | |--------|--------| | JOHN | 25 | | SAM | 127 | | EDWARD | 12 | | PENNY | 1024 |
Add Secondary key constrain
Eg.
create table schema8.EMPLOYEE(ID int, name varchar2(255)); alter table schema8.EMPLOYEE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (ID); create table schema8.EMPLOYEE_SALARY(EMPLOYEE_ID int, department varchar2(255), salary int); alter table schema8.EMPLOYEE_SALARY ADD CONSTRAINT FK_EMP_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES schema8.EMPLOYEE(ID);
Delete operation
Now lets perform a delete for entry with name ='SAM' and and check the table again.
delete from EXPENDITURE where NAME='SAM'; | NAME | AMOUNT | |--------|--------| | JOHN | 25 | | EDWARD | 12 | | PENNY | 1024 |
Yup entry is deleted. You can also do a truncate which will remove all entries from the table or you can do drop which will drop the entire table including all the entries in it.
Renaming and modifying column
Now lets rename the column AMOUNT to EXPENSE and modify the column NAME from varchar(255) to varchar(128) and say it can never be null. Finally lets describe the table to see the changes -
ALTER TABLE EXPENDITURE RENAME COLUMN AMOUNT to EXPENSE; ALTER TABLE EXPENDITURE MODIFY NAME varchar(128) not null; DESCRIBE EXPENDITURE;
Note : Unfortunately DESCRIBE does not seem to work on JSFiddle. It's because "describe" is not a part of SQL. It is something that Oracle's sqlplus shell implements, not the server. So as an alternative we can do -
ALTER TABLE EXPENDITURE RENAME COLUMN AMOUNT to EXPENSE; ALTER TABLE EXPENDITURE MODIFY NAME varchar(128) not null; select column_name, data_type, data_length, nullable from all_tab_columns where table_name = 'EXPENDITURE'; | COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE | |-------------|-----------|-------------|----------| | NAME | VARCHAR2 | 128 | N | | EXPENSE | NUMBER | 22 | Y |
GROUP BY AND ORDER BY statements
select * from EXPENDITURE order by EXPENSE; | NAME | EXPENSE | |--------|---------| | EDWARD | 12 | | JOHN | 25 | | PENNY | 1024 | select * from EXPENDITURE order by EXPENSE DESC; | NAME | EXPENSE | |--------|---------| | PENNY | 1024 | | JOHN | 25 | | EDWARD | 12 | select NAME, max(EXPENSE) from EXPENDITURE group by NAME; | NAME | MAX(EXPENSE) | |--------|--------------| | EDWARD | 12 | | JOHN | 25 | | PENNY | 1024 |
Note : Since we have unique names (NAME being the primary key) each group is infact a row. So you would see the same result selecting all rows from a database.
NOTE : You cannot use aggregate functions in where clause.
Counting number of rows in a Table
You can do something like -
SELECT COUNT(NAME) FROM EXPENDITURE; | COUNT(NAME) | |-------------| | 3 |
Note : Use the primary key in the argument of COUNT() function. As primary key guarantees unique row output of above query will give you total number of rows in the table.
Lets move on to something more advance...
Packages, Procedures and Functions....
Consider following code -
-- create table
create table EXPENDITURE (NAME varchar(255), AMOUNT int)
//
--add primary key
alter table EXPENDITURE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (NAME)
//
-- insert data
insert into EXPENDITURE values ('JOHN',25)
//
insert into EXPENDITURE values ('EDWARD',12)
//
insert into EXPENDITURE values ('PENNY',1024)
//
-- rename column
ALTER TABLE EXPENDITURE RENAME COLUMN AMOUNT to EXPENSE
//
-- change data type
ALTER TABLE EXPENDITURE MODIFY NAME varchar(128) not null
//
-- create package spec
create or replace package PKG_EXPENDITURE
is
-- define one public procedure
procedure ADD_EXPENSE(p_name in varchar, p_expense in int);
end;
//
-- create package body
create or replace package body PKG_EXPENDITURE
is
-- private DOUBLE EXPENSE procedure
procedure DOUBLE_EXPENSE(p_name in varchar, p_expense in int)
is
begin
insert into EXPENDITURE values (p_name, p_expense);
end;
-- private function to return twice the input
function double(p_number in int)
return int
is
begin
return 2 * p_number;
end;
procedure ADD_EXPENSE(p_name in varchar, p_expense in int)
is
begin
DOUBLE_EXPENSE(p_name, double(p_expense));
end;
end;
//
and now execute it as -
-- execute the public procedure of the package
begin
PKG_EXPENDITURE.ADD_EXPENSE('Aniket',111);
end;
//
select * from EXPENDITURE
//
and you should see the output -
| NAME | EXPENSE | |--------|---------| | JOHN | 25 | | EDWARD | 12 | | PENNY | 1024 | | Aniket | 222 |
Explanation - In above code we have first defined a public procedure ADD_EXPENSE. From this procedure we are calling a private procedure DOUBLE_EXPENSE which doubling the expense amount using a private function called double. Finally in DOUBLE_EXPENSE procedure we are simply inserting the value into the EXPENDITURE table. To check the output we are calling this public proc ADD_EXPENSE and then printing the table.
Note : I have used "//" as a query terminator. You can use ';'. You have a list of options in SQLFiddle.
