Convert Rows into Columns or Transpose Rows to Columns In Oracle SQL

SQL Database LanguageHello Friends, 
Have you ever been in a situation where you as a developer knows that your data is stored in your table rows, but you would like to present the data as a column ??

There are many way to handle this situation. Here I am sharing few of them which I know.​

Below is the sample table and data :
————————————————————
[sql]CREATE TABLE yr_table (
        Roll_No            NUMBER,
        Subject            VARCHAR2(20),
        Marks              NUMBER
);
[/sql]
[sql]insert into yr_table VALUES (1212324,’MTH’,90);
insert into yr_table VALUES (1212324,’PHY’,72);
insert into yr_table VALUES (1212324,’CHE’,85);
insert into yr_table VALUES (1212324,’BIO’,78);
insert into yr_table VALUES (1212334,’MTH’,85);
insert into yr_table VALUES (1212334,’PHY’,65);
insert into yr_table VALUES (1212334,’CHE’,74);
insert into yr_table VALUES (1212672,’MTH’,88);
insert into yr_table VALUES (1212672,’PHY’,42);
insert into yr_table VALUES (1212672,’BIO’,12);
COMMIT;
[/sql]
——————————————————————-

Now I would like to represent above data into below format :

We can achieve above output  in different way.

# Method 1 ->  Using DECODE  function

[sql]SELECT  ROLL_NO,
                 MAX( DECODE( SUBJECT , ‘MTH’ , MARKS ) ) AS MTH  ,
                 MAX( DECODE( SUBJECT , ‘PHY’ , MARKS ) ) AS PHY ,
                 MAX( DECODE( SUBJECT , ‘CHE’ , MARKS ) ) AS CHE ,
                 MAX( DECODE( SUBJECT , ‘BIO’ ,  MARKS ) ) AS BIO
FROM yr_table
GROUP BY ROLL_NO ORDER BY 1  ;​
[/sql]
To understand the above query, first see  the below output of above query without using MAX function then you can understand how above query works.

Method 2 -> Using CASE​ statement 
[sql]SELECT    ROLL_NO,
                MAX( CASE WHEN SUBJECT = ‘MTH’ THEN MARKS END) MTH ,
                MAX( CASE WHEN SUBJECT = ‘PHY’ THEN MARKS END) PHY ,
                MAX( CASE WHEN SUBJECT = ‘CHE’ THEN MARKS END) CHE ,
                MAX( CASE WHEN SUBJECT = ‘BIO’ THEN MARKS END) BIO
FROM yr_table
GROUP BY ROLL_NO ORDER BY 1 ;​
[/sql]
Here CASE statement works same as DECODE function.

Method 3 -> Using PIVOT Operator​

The PIVOT and the UNPIVOT operators were introduced in Oracle version 11g. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The following query will give the same result as the query above, just by using the PIVOT operator.
[sql]SELECT * FROM yr_table
PIVOT (
MAX ( MARKS )  FOR (SUBJECT) IN (‘MTH’ AS MTH, ‘PHY’ AS PHY, ‘CHE’ AS CHE, ‘BIO’ AS BIO)

ORDER BY 1​​
[/sql]
 You can check below link for more clarification on PIVOT Operator.  http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html 

Method 4 -> Using WITH clause and PIVOT Operator​   
[sql]WITH TMP AS (
    SELECT ROLL_NO,SUBJECT , MARKS FROM yr_table
)
SELECT * FROM TMP 
PIVOT (
MAX(MARKS)  FOR (SUBJECT) IN (‘MTH’ AS MTH, ‘PHY’ AS PHY, ‘CHE’ AS CHE, ‘BIO’ AS BIO)
)
ORDER BY 1
[/sql]

The WITH clause, was added into the Oracle SQL syntax in Oracle 9.2 . The WITH clause can be used to reduce repetition and simplify complex SQL statements. Here don’t get confuse with WITH Clause; just think that it create a temporary table which we can use it in select statement.

Method 5 -> Using WITH clause and Sub-query
[sql]WITH TMP AS (
        SELECT ROLL_NO,SUBJECT , MARKS FROM yr_table
)
SELECT Y.ROLL_NO ,
             (  SELECT TMP.MARKS FROM  TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘MTH’)  AS MTH ,
             (  SELECT TMP.MARKS FROM  TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘PHY’)  AS PHY ,
             (  SELECT TMP.MARKS FROM  TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘CHE’)  AS CHE ,
             (  SELECT TMP.MARKS FROM TMP WHERE  TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘BIO’)   AS BIO
FROM (SELECT DISTINCT ROLL_NO FROM yr_table ) Y
ORDER BY 1  ;
[/sql]
Method 6 -> Using Multiple Joins

[sql]SELECT  DISTINCT
              A.ROLL_NO ,    
              B.MARKS AS MTH ,
              C.MARKS AS PHY ,
              D.MARKS AS CHE ,
              E.MARKS AS BIO
FROM yr_table A 
LEFT JOIN yr_table B
    ON  A.ROLL_NO = B.ROLL_NO AND B.SUBJECT = ‘MTH’
LEFT JOIN yr_table C 
    ON A.ROLL_NO = C.ROLL_NO AND C.SUBJECT = ‘PHY’
LEFT JOIN yr_table D
    ON A.ROLL_NO = D.ROLL_NO AND D.SUBJECT = ‘CHE’
LEFT JOIN yr_table E
    ON A.ROLL_NO = E.ROLL_NO AND E.SUBJECT = ‘BIO’
ORDER BY 1;
[/sql]

Friends, please share your knowledge as well, if you know any other method.

Follow me at :
Facebook
Read more

Basics of Normalization with examples

Normalization Resolved
Normalization is one of the favorite topics of interviewee. It does not matter whether you have mentioned DBMS in your resume or not .This question is going to come and the funny part is that all of us know
  • what is normalization?
  • What are the different types of normalization?

So when this question on being asked the interviewer who have already prepared for it start with the history of normalization and end with the geography of normalization but when the next question for which they have not prepared i.e.  apply normalization in real case scenario.

Now here comes the real part of normalization and just because of not proper concepts, people end up confusing themselves. So the idea is to not only to get familiar with normalization but also how to apply it in real time scenario.

What is Normalization?
Database designed based on ER model may have some amount of inconsistency, ambiguity and redundancy. To resolve these issues some amount of refinement is required. This refinement process is called as Normalization. I know all of you are clear with the definition, let’s go with :

  • what is the need of normalization?
  • What are the problems we can face if we proceed without normalization?
  • What are the advantages of normalization?

Asking question to oneself is the best way to get familiar with all the concepts.

The need of Normalization
I am going to show you one simple E-R model database.

Student Details Course Details Result details
1001   Ram               11/09/1986 M4       Basic Maths                       7 11/11/2004       89           A
1002   Shyam           12/08/1987 M4       Basic Maths                       7 11/11/2004       78           B
1001   Ram               23/06/1987 H6                                                    4 11/11/2004       87           A
1003   Sita                16/07/1985 C3        Basic Chemistry                 11 11/11/2004       90           A
1004   Gita               24/09/1988 B3                                                     8 11/11/2004       78           B
1002   Shyam           23/06/1988 P3        Basic Physics                     13      11/11/2004       67           C
1005   Sunita           14/09/1987 P3        Basic Physics                      13 11/11/2004       78           B
1003   Sita                23/10/1987 B4                                                      5 11/11/2004       67           C
1005   Sunita           13/03/1990 H6                                                     4 11/11/2004       56           D
1004   Gita               21/08/1987 M4      Basic Maths                         7 11/11/2004       78           B
 
In first look the above table is looking so arranged and well in format but if we try to find out what exactly this table is saying to us , we can easily figure out the various anomalies in this table . Ok let me help you guys in finding out the same.
  1. Insert Anomaly: We cannot insert prospective course which does not have any registered student or we cannot insert student details that is yet to register for any course.
  2. Update Anomaly: if we want to update the course M4’s name we need to do this operation three times. Similarly we may have to update student 1003’s name twice if it changes.
  3. Delete Anomaly: if we want to delete a course M4 , in addition to M4 occurs details , other critical details of student also will be deleted. This kind of deletion is harmful to business. Moreover, M4 appears thrice in above table and needs to be deleted thrice.
  4. Duplicate Data: Course M4’s data is stored thrice and student 1002’s data stored twice .This redundancy will increase as the number of course offerings increases.
Process of normalization:
Before getting to know the normalization techniques in detail, let us define a few building blocks which are used to define normal form.
  1. Determinant : Attribute X can be defined as determinant if it uniquely defines the value Y in a given relationship or entity .To qualify as determinant attribute need NOT be a key attribute .Usually dependency of attribute is represented as X->Y ,which means attribute X decides attribute Y.
Example: In RESULT relation, Marks attribute may decide the grade attribute .This is represented as Marks->grade and read as Marks decides Grade.
Marks -> Grade
In the result relation, Marks attribute is not a key attribute .Hence it can be concluded that key attributes are determinants but not all the determinants are key attributes.
 
  1. Functional Dependency: Yes functional dependency has definition but let’s not care about that. Let’s try to understand the concept by example. Consider the following relation :
REPORT(Student#,Course#,CourseName,IName,Room#,Marks,Grade)
Where:
  • Student#-Student Number
  • Course#-Course Number
  • CourseName -CourseName
  • IName- Name of the instructor who delivered the course
  • Room#-Room number which is assigned to respective instructor
  • Marks- Scored in Course Course# by student Student #
  • Grade –Obtained by student Student# in course Course #
  • Student#,Course#  together (called composite attribute) defines EXACTLY ONE value of marks .This can be symbolically represented as
                                Student#Course# Marks
This type of dependency is called functional dependency. In above example Marks is functionally dependent on Student#Course#.
Other Functional dependencies in above examples are:
  • Course# -> CourseName
  • Course#-> IName(Assuming one course is taught by one and only one instructor )
  • IName -> Room# (Assuming each instructor has his /her own and non shared room)
  • Marks ->Grade
Formally we can define functional dependency as: In a given relation R, X and Y are attributes. Attribute Y is functional dependent on attribute X if each value of X determines exactly one value of Y. This is represented as :

                X->Y
However X may be composite in nature.
 
  1. Full functional dependency: In above example Marks is fully functional dependent on student#Course#  and not on the sub set of Student#Course# .This means marks cannot be determined either by student # or Course# alone .It can be determined by using Student# and Course# together. Hence Marks is fully functional dependent on student#course#.
CourseName is not fully functionally dependent on student#course# because one of the subset course# determines the course name and Student# does not having role in deciding Course name .Hence CourseName is not fully functional dependent on student #Course#.
               
Student#
                  Marks
Course#
Formal Definition of full functional dependency : In a given relation R ,X and Y are attributes. Y is fully functionally dependent on attribute X only if it is not functionally dependent on sub-set of X.However X may be composite in nature.
 
  1. Partial Dependency: In the above relationship CourseName,IName,Room# are partially dependent on composite attribute Student#Course# because Course# alone can defines the coursename, IName,Room#.
Room#
IName
CourseName
Course#
Student#
Formal Definition of Partial dependency: In a given relation R, X and Y are attributes .Attribute Y is partially dependent on the attribute X only if it is dependent on subset attribute X .However X may be composite in nature.
 
  1. Transitive Dependency:  In above example , Room# depends on IName and in turn depends on Course# .Here Room# transitively depends on Course#.
IName
Room#
Course#              
Similarly Grade depends on Marks,in turn Marks depends on Student#Course#  hence Grade
Fully transitively depends on Student#Course#.
 
  1. Key attributes : In a given relationship R ,if the attribute X uniquely defines all other attributes ,then the attribute X is a key attribute which is nothing but the candidate key.
Ex: Student#Course# together is a composite key attribute which determines all attributes in relationship REPORT(student#,Course#,CourseName,IName,Room#,Marks,Grade)uniquely.Hence Student# and Course# are key attributes.
 
Types of Normal Forms
  1. First Normal Form(1NF)
A relation R is said to be in first normal form (1NF) if and only if all the attributes of the relation R are atomic in nature 
Student Details Course Details Result details
1001   Ram               11/09/1986 M4       Basic Maths                       7 11/11/2004       89           A
1002   Shyam           12/08/1987 M4       Basic Maths                       7 11/11/2004       78           B
1001   Ram               23/06/1987 H6                                                    4 11/11/2004       87           A
1003   Sita                16/07/1985 C3        Basic Chemistry                 11 11/11/2004       90           A
1004   Gita               24/09/1988 B3                                                     8 11/11/2004       78           B
1002   Shyam           23/06/1988 P3        Basic Physics                     13      11/11/2004       67           C
1005   Sunita           14/09/1987 P3        Basic Physics                      13 11/11/2004       78           B
1003   Sita                23/10/1987 B4                                                      5 11/11/2004       67           C
1005   Sunita           13/03/1990 H6                                                     4 11/11/2004       56           D
1004   Gita               21/08/1987 M4      Basic Maths                         7 11/11/2004       78           B
 
Table shown above Student Details ,Course Details and Result Details can be further divided. Student Details attribute is divided into Student#(Student Number) , Student Name and date of birth. Course Details is divided into Course# ,Course Name,Prerequisites and duration. Similarly  Results attribute is divided into DateOfexam,Marks and Grade.
 
  1. Second Normal Form (2NF)
A relation is said to be in Second Normal Form if and only If :
  • It is in the first normal form ,and
  • No partial dependency exists between non-key attributes and key attributes.
           
Let us re-visit 1NF table structure.
  • Student# is key attribute for Student ,
  • Course# is key attribute for Course
  • Student#Course#  together form the composite key attributes for result relationship.
  • Other attributes are non-key attributes.
To make this table 2NF compliant, we have to remove all the partial dependencies.
  • StudentName and DateOfBirth depends only on student#.
  • CourseName,PreRequisite and DurationInDays depends only on Course#
  • DateOfExam depends only on Course#.
To remove this partial dependency we need to split Student_Course_Result table into four separate tables ,STUDENT ,COURSE,RESULT and EXAM_DATE tables as shown in figure.

STUDENT TABLE

Student # Student Name DateofBirth
1001 Ram Some value
1002 Shyam Some value
1003 Sita Some value
1004 Geeta Some value
1005 Sunita Some value
 
COURSE TABLE
Course# CourseName Duration of days
C3 Bio Chemistry 3
B3 Botany 8
P3 Nuclear Physics 1
M4 Applied Mathematics 4
H6 American History 5
B4 Zoology 9

RESULT TABLE
Student# Course# Marks Grade
1001 M4 89 A
1002 M4 78 B
1001 H6 87 A
1003 C3 90 A
1004 B3 78 B
1002 P3 67 C
1005 P3 78 B
1003 B4 67 C
1005 H6 56 D
1004 M4 78 B
 
EXAM DATE Table
Course# DateOfExam
M4 Some value
H6 Some value
C3 Some value
B3 Some value
P3 Some value
B4 Some value
 
  • In the first table (STUDENT) ,the key attribute is Student# and all other non-key attributes, StudentName and DateOfBirth are fully functionally dependant on the key attribute.
  • In the Second Table (COURSE) , Course# is the key attribute and all the non-key attributes, CourseName, DurationInDays are fully functional dependant on the key attribute.
  • In third table (RESULT) Student#Course# together are key attributes and all other non key attributes, Marks  and Grade are fully functional dependant on the key attributes.
  • In the fourth Table (EXAM DATE) Course# is the key attribute and the non key attribute ,DateOfExam is fully functionally dependant on the key attribute.
At first look it appears like all our anomalies are taken away ! Now we are storing Student 1003 and M4 record only once. We can insert prospective students and courses at our will. We will update only once if we need to change any data in STUDENT,COURSE tables. We can get rid of any course or student details by deleting just one row.

Let us analyze the RESULT Table

Student# Course# Marks Grade
1001 M4 89 A
1002 M4 78 B
1001 H6 87 A
1003 C3 90 A
1004 B3 78 B
1002 P3 67 C
1005 P3 78 B
1003 B4 67 C
1005 H6 56 D
1004 M4 78 B
 
We already concluded that :
  • All attributes are atomic in nature
  • No partial dependency exists between the key attributes and non-key attributes
  • RESULT table is in 2NF
 
Assume, at present, as per the university evaluation policy,
  • Students who score more than or equal to 80 marks are awarded with “A” grade
  • Students who score more than or equal to 70 marks up till 79 are awarded with “B” grade
  • Students who score more than or equal to 60 marks up till 69 are awarded with “C” grade
  • Students who score more than or equal to 50 marks up till 59 are awarded with “D” grade
 
The University management which is committed to improve the quality of education ,wants to change the existing grading system to a new grading system .In the present RESULT table structure ,
  • We don’t have an option to introduce new grades like A+ ,B- and E
  • We need to do multiple updates on the existing record to bring them to new grading definition
  • We will not be able to take away “D” grade if we want to.
  • 2NF does not take care of all the anomalies and inconsistencies.
 
  1. Third Normal Form (3NF)
A relation R is said to be in 3NF if and only if
  • It is in 2NF
  • No transitive dependency exists between non-key attributes and key attributes.
In the above RESULT table Student# and Course# are the key attributes. All other attributes, except grade are non-partially , non – transitively dependant on key attributes. The grade attribute is dependant on “Marks “ and in turn “Marks” is dependent on Student#Course#. To bring the table in 3NF we need to take off this transitive dependency.
 
Student# Course# Marks
1001 M4 89
1002 M4 78
1001 H6 87
1003 C3 90
1004 B3 78
1002 P3 67
1005 P3 78
1003 B4 67
1005 H6 56
1004 M4 78
 
UpperBound LowerBound Grade
100 95 A+
94 90 A
89 85 B+
84 80 B
79 75 B-
74 70 C
69 65 C-
 
After Normalizing tables to 3NF , we got rid of all the anomalies and inconsistencies. Now we can add new grade systems, update the existing one and delete the unwanted ones.
Hence the Third Normal form is the most optimal normal form and 99% of the databases which require efficiency in
  • INSERT
  • UPDATE
  • DELETE
Operations are designed in this normal form.
 
Hope this article will be useful to engineering students and to interviewer too!

You can download the pdf format of this article from here

You can view this article at our slideshare page too:

 

Read more

IBM – The Great Mind Challenge 2011

TGMC The Great Mind Challenge (TGMC) is a programming contest by IBM, India to help engineering students in testing their skills. Student Engineers interested to  join software industry can get lot of exposure from this. 

How this can help Engineering/programming students ?
This contest is made for students interested in programming only. Biggest opportunity for final year students as they can use this for final year project and i believe this can change your future. You can grab an opportunity to work with IM (Information Management software) team of IBM.

wolverinetyagi

 

Launched in 2004, TGMC is an innovative program that addresses the need to better educate millions of students for a more competitive information technology (IT) workforce by partnering with colleges and universities.

Every year, the growth and reach of TGMC has increased, seeing a marked increase in the number of students who are keen to participate in such an endeavor. In fact, TGMC is now listed in the Limca Book of Records as the largest technological contest of its kind – testament to the fact that there is a need in today’s competitive world for such an initiative that supports, guides, and challenges students to higher goals.

Universities provide excellent learning environments, but find it difficult to teach students about an IT transformation in progress unless those driving the transformation – IT companies at the helm of transformation – provide real-time input and support. TGMC is an out reach of the IBM Academic Initiative, a global program that facilitates the collaboration between IBM and educators to teach students the IT skills they need to be competitive and keep pace with changes in the workplace.

The IBM Academic Initiative includes an online portal that provides access to software, training, and course materials, most at no charge. Available within the Academic Initiative web site, IBM offers hundreds of resources for integration into college curricula to help teach students how to master the fast-growing market of open technologies. Through this initiative, IBM is working with students to build technology and science skills.
 

Have questions? Call us toll-free at 1800-425-9366.

We are available from 8 a.m. to 6 p.m. Monday through Friday.
You can also visit our FAQs, e-mail us at tgmc@in.ibm.com or post a question on our group message board.

Source: www.ibm.com

Hurry UP!!

Last date for joining is 31st october.

Click Here to know more

 

Read more

Triggers and Procedures in MySQL

Introduction
MySQL Triggers
are one of the newer features in MySQL that are helping to make it a viable alternative for large enterprise applications. So, what are MySQL triggers, and why does MySQL's ability to use them make it more attractive to serious database users?

Simply put, triggers are small programs that are stored in the database itself, and are activated by database events which often originate at the application layer. These precipitating database events are UPDATE, DELETE or INSERT queries.

The trigger itself may execute before or after the query that initiates it. Triggers are often used to maintain the integrity of data across tables of an application.
When a user on a website makes a purchase, for example, the first action that occurs in the database may be that a credit is inserted into an accounting table. By way of a trigger this action could initiate a chain reaction of events in other tables throughout the application. The product count of an item could be decremented in an inventory table, a debit deducted from a customer's account balance in another table, a store credit applied to yet another table.

Why to use procedures

You may say that you have been doing this all along in your applications using PHP or Perl or Python or ASP code. What's the big deal about using MySQL triggers? Well, there are some advantages to using triggers over application code for maintaining integrity of data across tables.

A trigger generally performs the types of tasks described faster than application code, and and can be activated easily and quickly behind the scenes and does not need to be a part of your application code. This saves time and spares you from redundant coding. If you ever port your application to another language, chances are your triggers can stay in place without modification, along with your tables and other database objects.

Explanation

To demonstrate how MySQL triggers work, let's set up two simple tables on a database we'll call “sales_records” that have data that is interdependent. Imagine a database that tracks the sales records of three salespeople at a department store.
They work in the electronics department selling things like TVs , stereos, and MP3 players. We have the main table that keeps a record of each sale made. It records the amount of the sale (sale_amt), the date (date), the name of the salesman (name), his id number (employee_id), and the product id (prod_id). We'll call this table (cleverly enough) “sales”.

In the second table, we want to keep some data that will allow us to easily keep track of how each salesperson is doing. It will include the salesperson's id (employee_id), name (name), total number of sales (total_sales), and a column that keeps each salesperson's average amount per sale (ave_sale). We want to see who's moving the high-end items. We'll call this table “performance”.

How to use Triggers and Procedures

Now comes the hard part. As I mentioned, triggers are database objects just as tables are. Triggers, however, are able to execute procedural code that modifies data in your tables. In this case, we want our trigger to fire before any INSERT statement that executes in the sales table. When a sale record is inserted in the sales table, the salesperson's totals must be updated in the performance table. The following code can be typed in your favorite text editor and pasted into your console at the MySQL prompt.

Before you do that though, you want to execute this line: mysql: Delimiter $$ Our procedural code uses semicolons at the end of statements, so we need to set a different delimiter to let MySQL know when our code block is over, and so that it doesn't stop processing our block when it hits a semicolon. Keep in mind that after you finish your block you will have to set the delimiter back to the semicolon, or end any subsequent commands with the new delimiter.

For example if you made errors in your CREATE TRIGGER block and want to delete it, DROP TRIGGER; won't work unless you set the delimiter back to the semicolon. Here is the code for the trigger: OK, let's talk about the code.

  • Using the CREATE TRIGGER statement, we've initiated the trigger, naming it 'sales_bi_trg'. MySQL triggers can fire before or after an INSERT, UPDATE or DELETE event.
  • This one fires before any data is inserted in the 'sales' table. The FOR EACH ROW clause signifies that the block will act on each row that meets the criteria of our SQL statements.
  • The keywords BEGIN and END enclose the trigger statements that will execute when the trigger fires.
  • There are two variables declared. The first is 'num_row' which checks to see if the employee has who has made the sale that is to be entered, has had a sale entered in the performance table previously. If there are no employee_id's that match, then this is the employee's first sale, and this meets the 'ELSE' condition of our “IF' statement. This data will be entered as an insert in the performance table rather than an update. If the 'num_row' is greater than 0, then the performance table will be updated. The second variable, 'tot_rows', is a count of how many sales the employee has in the 'sales' table. This value is used to calculate the employee's average sale.
  • The count is being done before the sale is inserted in the sale table, so we have to add one to it. When the 'performance' table is updated the average sale = total_sales/(tot_rows+1).
  • If our MySQL trigger is working correctly, the 'performance' table will keep a running total of each salespersons total sales, and also the average amount of their total sales. It will do this independently of your application code and be portable to any application platform. To give it a whirl, insert some data into the 'sales' table and monitor the content of the 'performance' table. Here is the statement: Change the numbers and names and try it a few times. (Remember, an employee keeps the same employee_id number for each of his sales.)

If you're feeling adventurous, start thinking about how the MySQL trigger would have to be extended to account for UPDATE and DELETE statements on the 'sales' table. Another eg :

create trigger tb1_bi_trg before insert on tb1 for each row begin declare rnk int;
select new.rank into rnk;
if rnk<=4 then insert into tb2 set salary=5000;
else insert into tb2 set salary=2000;
end if;
end
create view v as select rank,name,marks*rank as income from tb1;

Another eg :
CREATE TABLE Employee1( id int, first_name VARCHAR(30), last_name VARCHAR(15), start_date  DATE, end_date      DATE, city  VARCHAR(10), description   VARCHAR(15) );

insert into Employee1 values (01,'Girish','Tewari','20081225',  '20100625','Nainital','Programmer');

insert into Employee1 values (02,'Komal','Choudhry','20071122', '20100421','Meerut','Programmer');

insert into Employee1 values (03,'Mahendra','Singh','20061012',  '20070512','Lucknow','Programmer');

select * from employee1;

CREATE TABLE Employee_log( user_id       VARCHAR(15), description   VARCHAR(100) );

CREATE TRIGGER Employee_Trigger AFTER UPDATE ON employee1
                 FOR EACH ROW BEGIN INSERT into Employee_log (user_id, description)
                VALUES (user(), CONCAT('Id with ',NEW.id,' is modified ', ' from ',OLD.start_date, ' to ', NEW.start_date));
END$$

update employee1 set start_date='20061231';
PROCEDURES : delimiter // create procedure procedure_name begin select * from table_name; end delimiter ;

Now call the procedure call procedure_name(); it will shows all the contents of the table table_name. declaring variable in a procedure : After "begin" just write the followings declare variable_name datatype(size) default default_value; eg: declare x int default 0 we can also declare more than 1 variables at a time declare x,y int default 0

Assigning variables
declare total_count int default 0 set total_count = 10;
declare total_products int default 0 select count(*) into total_products from products;

In mysql console you can see what you have written in the procedure i.e you can see the code of the procedure by using :

select body, definer, (and/or any other column available) from mysql.proc where name='your_procedure'

Read more

Drupal installation problem in your system

Drupal Installation SolutionIf drupal is not working in your Windows(like if you have tried installing drupal but it’s giving some errors). Try these (worked for me) –

1. Increase PHP’s memory settings by doing
a)      Click on the WAMP icon in your system tray (or wherever it is just click on it). It will show you all the options like Apache, Mysql, PHPadmin, Localhost etc.
b)      Select Config files (in newer versions ) or PHP (in old versions) —-à then click on PHP.ini
c)       Go to line “upload_max_filesize=2M”, change it to 32 M or more
d)      Save and restart WAMP

2. Got an ERROR : C:\WAMP\www\includes\file.inc on line 911 –
Do this : Go to the line where it is written “elseif ($depth>=$min_depth && ereg($mask, $file))” change ereg to mb_ereg

3. Setup Database problem

a) First create a database named drupal or whatever you want.

b) Enter the database username as root and password “”[null]
Keep in mind : When you are renaming the default_settings.php to settings.php then make sure you are copying the content of default_settings.php & renaming it to settings.php.
Keep this file in  sites\default. After doing all this I got my drupal site , hoping the same for you. Cheers!

Read more