PL/SQL XAMPP EXAMPLE SET 1
In this PL/SQL XAMPP EXAMPLE SET 1, You can to learn how to use SQL commands in XAMPP. Here cover DML, Data Insertion, Data Updation, Data Deletion, Data Selection, CREATE, DESCRIBE SCHEMA, INSERT, ADD COLUMN, REMOVE COLUMN, UPDATE, ALTER, PRIMARY KEY, MODIFY, DROP COLUMN, DATATYPE CHANGE, RENAME FIELD, DISTINCT, DELETE RECORD, CLEAR ALL DATA, REMOVE TABLE. There are some preparation notes, links are given below, you might like,
Preparation Notes: https://www.shoutcoders.com/sql-preparation-time-note-4/, https://www.shoutcoders.com/sql-preparation-time-note-3/, https://www.shoutcoders.com/sql-preparation-time-note-2/, https://www.shoutcoders.com/sql-preparation-time-note-1/
PL/SQL XAMPP EXAMPLE SET 1 :
1. Create the following table
Student {roll, name, address, ph_no, DOB, MA, HS, DOA, Stream, C_num}
create table student (Roll int(4), Name varchar(10), Address varchar(20), Ph_num int(10), DOB date, MA int(4), HS int(4), DOA date, Stream varchar(4), C_Num char(6));
Output:
Table Created
Write the following queries
i) Describe Student table
desc student
OUTPUT:
Field Type NULL Key
Roll int(4) YES NULL
Name varchar(10) YES NULL
Address varchar(20) YES NULL
Ph_num int(10) YES NULL
DOB date YES NULL
MA int(4) YES NULL
HS int(4) YES NULL
DOA date YES NULL
Stream varchar(4) YES NULL
C_Num char(6) YES NULL
ii) Insert 6 values into these table. (Two values using method1, method2, method3)
METHOD 1
insert into student values(55,'SIZUKA','Hungame',9556992,'2000-01-18',75,45,'2011-07-19','BBA','E119');
OUTPUT:
1 row inserted
insert into student values(57,'ANIES','Argnam',5860299,'2000-01-18',75,45,'2011-07-19','BBA','E118');
OUTPUT:
1 row inserted
METHOD 2
insert into student (Roll, Name, Address, Ph_Num, DOB, MA, HS, DOA, Stream, C_num) values (77,'SK.Danny','PukurPani',985089805,'2000-08-08',75,65,'2011-08-10','BCA','E009');
OUTPUT:
1 row inserted
insert into student (Roll, Name, Address, Ph_Num, DOB, MA, HS, DOA, Stream, C_num) values (70,'Sk.Singa','Kestopur',0213568,'1999-01-01',54,55,'2001-08-05','BBA','E008');
OUTPUT:
1 row inserted
METHOD 3 // works in Oracle. In Xampp, Apply Method 1
insert all
into student values (01,'Sk.Saif','Burdwan',9662348,'1998-01-03',49,58,'2001-08-05','BCA','E001')
into student values (02,'Sk.Akash','Burdwan',62587,'1998-01-06',48,68,'2011-08-05','BBA','E007')
select * from dual;
[IN XAMPP]
#insert into student values (01,'Sk.Saif','Burdwan',9662348,'1998-01-03',49,58,'2001-08-05','BCA','E001')
#insert into student values (02,'Sk.Akash','Burdwan',62587,'1998-01-06',48,68,'2011-08-05','BBA','E007')
OUTPUT:
2 row inserted
iii) Add a column name FEES
ALTER TABLE student ADD Fees int(10);
OUTPUT:
Table inserted.iv) Insert 20,000 for BCA students and 18000 for BBA students.
update student set Fees =20000 WHERE Stream='BCA';
OUTPUT:
2 rows updated.
update student set Fees =18000 WHERE Stream='BBA';
OUTPUT:
4 rows updated.v) Remove C_num attribute from student Database.
alter table student drop COLUMN C_Num;
OUTPUT:
Table altered.vi) Make Roll As Primary Key
alter table student add primary key(Roll);
OUTPUT:
Table altered.
vii) Change the database range varchar(10) of name attribute to varchar(20).
ALTER TABLE student MODIFY Name varchar(20);
OUTPUT:
Table altered.viii) Change the FIELD name "MA" to "CLASS 10".
Syntax in xampp:
ALTER TABLE tableName CHANGE "oldcolname" "newcolname" datatype(length);
ALTER TABLE `student` CHANGE `MA` `Class 10` INT(4) NULL DEFAULT NULL;
OUTPUT:
Table altered.
ix) Find the details of the student who are from Burdwan.
select * from student where Address='Burdwan';
OUTPUT:
Roll Name Address Ph_num DOB Class 10 HS DOA Stream Fees
1 Sk.Saif Burdwan 9662348 1998-01-03 49 58 2001-08-05 BCA 20000
2 Sk.Akash Burdwan 62587 1998-01-06 48 68 2011-08-05 BBA 18000
x) Show the students with marks who admitted in BCA course.
select Roll, Name, Stream, HS, 'Class 10' from student where stream='BCA'
OUTPUT:
Roll Name Stream HS Class_10
1 Sk.Saif BCA 58 49
77 SK.Danny BCA 65 75
xi) Show the address field, not show duplicate address.
select distinct address from student;
OUTPUT:
ADDRESS
Burdwan
Hungame
Argnam
Kestopur
PukurPani
xii) Delete the student roll_num 70
delete from student where roll=70;
OUTPUT:
1 row affected.
xiii) Delete all values from Student table
delete from student;
OUTPUT:
5 rows affected.
xiv) Remove student table
Drop table student
OUTPUT:
Table Dropped.
Here, Example set is completed. DML note is given below.
DML TRANSACTIONS IN PL/SQL
DML stands for Data Manipulation Language. These statements are mainly used to perform the manipulation activity. It deals with the below operations.
- Data Insertion
- Data Updation
- Data Deletion
- Data Selection
In PL/SQL, we can do the data manipulation only by using the SQL commands.
DATA INSERTION
In PL/SQL, we can insert the data into any table using the SQL command INSERT INTO. This command will take the table name, table column and column values as the input and insert the value in the base table.
The INSERT command can also take the values directly from another table using 'SELECT' statement rather than giving the values for each column. Through 'SELECT' statement, we can insert as many rows as the base table contains.
Syntax:
BEGIN;
INSERT INTO <table_name>(<column1 >,<column2>,...<column_n>) VALUES(<valuel><value2>,...:<value_n>);
END;
The above syntax shows the INSERT INTO command. The table name and values are a mandatory fields, whereas column names are not mandatory if the insert statements have values for all the column of the table.
The keyword 'VALUES' is mandatory if the values are given separately as shown above.
Syntax:
BEGIN;
INSERT INTO <table_name>(<columnl>,<column2>,...,<column_n>)
SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;
The above syntax shows the INSERT INTO command that takes the values directly from the <table_name2> using the SELECT command.
The keyword 'VALUES' should not be present in this case as the values are not given separately.
DATA UPDATION
Data update simply means an update of the value of any column in the table. This can be done using 'UPDATE' statement. This statement takes the table name, column name and value as the input and updates the data.
Syntax:
BEGIN;
UPDATE <table_name>
SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n> WHERE <condition that uniquely identifies the record that needs to be update>;
END;
The above syntax shows the UPDATE. The keyword 'SET' instruct that PL/SQL engine to update the value of the column with the value given.
'WHERE' clause is optional. If this clause is not given, then the value of the mentioned column in the entire table will be updated.
DATA DELETION
Data deletion means to delete one full record from the database table. The 'DELETE' command is used for this purpose.
Syntax:
BEGIN;
DELETE FROM <table_name>
WHERE <condition that uniquely identifies the record that needs to be update>;
END;
- The above syntax shows the DELETE command. The keyword 'FROM' is optional and with or without 'FROM' clause the command behaves in the same way.
- 'WHERE' clause is optional. If this clause is not given, then the entire table will be deleted.
DATA SELECTION
Data projection/fetching means to retrieve the required data from the database table. This can be achieved by using the command 'SELECT' with 'INTO' clause. The 'SELECT' command will fetch the values from the database, and 'INTO' clause will assign these values to the local variable of the PL/SQL block.
Below are the points that need to be considered in 'SELECT' statement.
'SELECT' statement should return only one record while using 'INTO' clause as one variable can hold only one value. If the 'SELECT' statement returns more than one value than 'TOO_MANY_ROWS' exception will be raised.
'SELECT' statement will assign the value to the variable in the 'INTO' clause, so it needs to get at least one record from the table to populate the value. If it didn't get any record, then the exception 'NO_DATA_FOUND' is raised.
The number of columns and their datatype in 'SELECT' clause should match with the number of variables and their datatypes in the 'INTO' clause.
The values are fetched and populated in the same order as mentioned in the statement.
'WHERE' clause is optional that allows to having more restriction on the records that are going to be fetched.
'SELECT' statement can be used in the 'WHERE' condition of other DML statements to define the values of the conditions.
The 'SELECT' statement when using 'INSERT', 'UPDATE', 'DELETE' statements should not have 'INTO' clause as it will not populate any variable in these cases.
Comments
Post a Comment