1.

SQL QUERIES USING DDL COMMANDS WITH CONSTRAINTS

AIM:
1. Create a table for Department with the following attributes.

Dept_No number(2)PK
Dept_Name varchar2(20) not null
2. Create a table for Student with the followingattributes.
Regno varchar2(10)PK
Name varchar2(15) not null
DOB Date
Class varchar2(10)
Dept_No number(2) FK
College_Name varchar2(20) Default ‘Government Arts College’
City varchar2(15)
3. Create a table for Attendance with the following attributes.
Regno varchar2(7) FK
Attendance_Percentage number(3)
4. Display the physical table structure of Department, Student,Attendance.
5. Write a query to add an column Age number(2) to the table Student with
a constraints of accepting Age>=17 &<=22.
6. Write a query to rename the column name Regno to Rollno from the table
of Student.
7. Write a query to remove the column name Class from Student.
8. Write a query to truncate the table Attendance.

ALGORITHM:
1) Start---All Programs-----Select Oracle Database 10g Express
Edition-------Go to Database Homepage.
2) Enter Username and Password as system.
3) Select SQL to type the SQL statements required.
4) Create tables Department, Student and Attendance as stated.
5) View all the tables using describe commands.
6) Apply DDL commands ( Alter,Rename,Drop,Truncate) to the existing
tables and view to the results
2. SQL QUERIES USING DML COMMANDS
AIM:

1. Insert values into the tables of Department and Student.

2. Write a query to change the value of City=Chennai to
City=Coimbatore in the Student table
3. Write a query to remove the last record from the Student table.
4. Write query to retrieve data from the existing tables.
5. Write a query to fetch unique values of City from the Student table.
6. Write a query to fetch the details of Rollno & Name with the title head
“Registerno” & “Student_Name” from the Student table.
7. Write a query to display the birth details of a student as name was
born DOB from Student table.
8. Write a query to fetch the student details of Age between 17 & 20
from the Student table.
9. Write a query to fetch the student details of Dept_No3 & 5 from the
Student table.
10.Write a quert to fetch the details of Rollno & Name where name start
with ‘A’ from the Student table.
11.Write a query to fetch the details of Student Name with Rollno who
were born in the year 2003 from the Student table.
12.Write a query to fetch the details of student according to Dept_no &
DOB

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express

Edition------Go to DatabaseHomepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Insert values into the Department and Student table according to the
Constraints given.
5. Use the following commands in the existing
table(Update,Delete,Select,Distinct,Alias,Concatinate,Between,In,Wildca
rds abd Order by) and view to the results
3. SQL QUERIES USING BUILT INFUNCTIONS
AIM:

1. Write a query to round a number to the specific number of decimal

places from the oracle table.
2. Write a query to truncate a number to the specific number of decimal
places.
3. Write a query to return the value of a number raised to the power of
another number.
4. Write a query to return the absolute value of a number.
5. Write a query to return the remainder of a number divided by another
number.
6. Write a query to return the sign of a number.
7. Write a query to return the largest integer value which is smaller than
(or) equal to the given number
8. Write a query to change the case of Name to upper, Class to lower and
College_Name to initcap from the Student table.
9. Write a query to combine the fields Class and Dept_Name from the
Student table and Department table using string functions
10.Write a query to extract text from string from the Student table using
string functions.
11.Write a query to return a numeric values for string
12.Write a query to remove leading spaces from a string
13.Write a query to remove trailing spaces from a string.
14.Write a query to remove leading and trailing spaces from a string.
15.Write a query to return the length of a string.
16.Write a query to add a string to the left side of the original string.
17.Write a query to add a string to the right side of the original string.
18.Write a query to replace all occurrences of a substring within a string
with a new substring.
19.Write a query to get the current date of the month.
20.Write a query to return the current database system timestamp
21.Write a query to return the current time zone.
22.Write a query to extract a part from a given date.
23.Write a query to extract the last day of the month for a given date.
24.Write a query to return the current date and time.
25.Write a query to return the date of the first weekday specified by day
name that is later than a date.
26.Write a query to return the current date and time of the system.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express

Edition------Go to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Apply the following Numeric functions into the oracle tables
(Round,Trunc,Power,ABC,Mod,Sign,Floor)
5. Implement string functions on the Student table
(Upper,Lower,Initcap,Substr,Instr,Ltrim,Rtrim,Trim,Length,Lpad,
Rpad,Replace)
6. Apply the following Date & Time functions on the oracle table
(Current_Date,Current_Timestamp,dbtimezone,extract,last_day,loc
altimestamp,nextday,sysdate)

4. SQL QUERIES USING GROUP FUNCTIONS

AIM :
1. Create a table for Stu_Mark with the following attributes.
Regno varchar2(10)
Stu_Name varchar2(20)
Tamil number(4)
English number(4)
Maths number(4)
2. Insert values into the Stu_Mark table.
3. Write a query to calculate the total value of Tamil marks.
4. Write a query to return lowest value of Maths marks.
5. Write a query to return highest value of English Marks.
6. Write a query to return the average value of Tamil marks.
7. Write a query to find the number of records available inStu_Mark table.
8. Write a query to display the records by grouping Regno and Stu_Name from
the Stu_Mark table.
9.Write a query to filtering data from the table according to the conditions.

ALGORITHM :

1. Start---All Programs-----Select Oracle Database 10g Express Edition------Go

to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Create Student_Mark table as stated.
5 Insert values into the Stu_Mark table.
6. Implement the following group functions on the Stu_Mark table and view the
results. (Sum,Min,Max,Avg,Count,Group By, )

5. SQL QUERIES USING HAVING FUNCTION

AIM:
Write a query to filtering data from the table according to the conditions.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express Edition------Go

to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Implement the following HAVING function on the Stu_Mark table and view
the results.
6.SQL QUERIES USING ADVANCED SQL OPERATORS

AIM:

1. Create a table for Employee with the following attributes.

Id int Primary Key,
Name varchar2(15),
Department varchar2(10),
Salary Number(8,2),
Gender varchar2(10),
Age int,
City varchar2(10)
2. Create a table for Emloyee1 with the following attributes.
Employee_Id int,
FirstName varchar2(20),
LastName varchar2(20),
Gender varchar2(10)
Department varchar2(20)
3. Create a table for Employee2 with the following attributes.
Employee_Id int,
FirstName varchar2(20),
LastName varchar2(20),
Gender varchar2(10)
Department varchar2(20)
4. Insert values into the Employee, Emloyee1 and Employee2 tables.
5. Write a query to perform the mathematical operations in Employee table.
6. Write a query to check whether the given fields are same, greater, and lesser
in Employee table.
7. Write a query to apply Boolean Operations on Employee table.
8. Write a query to access the record of those employees whose name starts with
given character from the Employee table.
9. Write a query to combine a similar and different type of data in the
Employee1 and Employee2 tables.
10. Write query to combine similar record in Employee and Employee1 tables.
11. Write a query to return all records from the left table, and the matching
records from the right table.
12. . Write a query to return all records from the right table, and the matching
records from the left table.
13. Write a query to return all record when there are matching in records in left
or right table.

ALGORITHM:
1. Start---All Programs-----Select Oracle Database 10g Express Edition------Go
to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Create tables Employee, Employee1, Employee2, as stated.
5. Insert into the values of Employee, Employee1, Employee2 according to the
Field has given.
6. Implement the Arithmetic, Logical and Comparison Operators on Employee
table and view the results.
7. Apply the Set and Like operators on Employee1 and Employee2 tables and
view the results.
8. Implement Inner, Left Outer, Right Outer and Full Outer Join Operators on
Employee, Employee1 and Employee2 tables and view the results.

7.SQL QUERIES USING MULTIPLE SUB QUERIES

AIM:

1. Write a query to return the records that matches your SELECT query on
Employee table.
2. Write a query to use a comparison operator in a Group by and HAVING
clause in Employee table.
3. Write a query to return more than one row to the outer statement using IN
clause on Employee1 and Employee2 table.
4. Write a query to compare inner query with multiple columns values of the
outer query on Employee1 and Employee2 table.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express Edition------Go

to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Implement Single Row Sub Query using Group by and having commands on
Employee table and view the results.
5. Implement Multiple Row Sub Query using IN Clause on Employee1 and
Employee2 tables and view the results.
6.Implement Multiple Column Sub Query using WHERE, IN, BETWEEN
Commands on Employee1 and Employee2 tables and view the results.
8. PL/SQL BLOCK USING % TYPE ATTRIBUTE

AIM:

Write a PL/SQL program to declare variables and display the appropriate

value of variable using %TYPE attribute
.
ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express

Edition------Go to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Declare the variable name from the Employee table using %TYPE
attribute.
5. Assign the values to the respective variables to the Employee table.
6. Display the respective records from Employee table using PL/SQL
print statement.
9. PL/SQL BLOCK USING % ROWTYPE ATTRIBUTE

AIM:
Write a PL/SQL program to declare variables and display the appropriate
value of variable using %ROWTYPE attribute

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express

Edition------Go to Database Homepage.
2. Enter Username and Password as system.
3. Select SQL to type the SQL statements required.
4. Declare the variable name from the Employee table using
%ROWTYPE attribute.
5. Assign the values to the respective variables to the Employee table.
6. Display the respective records from Employee table using PL/SQL
print statement.
10.PL/SQL BLOCK USING CONDITIONAL STATEMENTS

AIM:

Write a PL/SQL program using Conditional Statements.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g

Express Edition------Go to Database Homepage.
2. Enter Username and Password as system.
3. Declare and assign the values to the respective variables
and check the conditions using IF, IF ELSE, NESTED IF
ELSE and CASE statements.
11.PL/SQL BLOCK USING LOOPS

AIM:

Write a PL/SQL program using Loop Statements.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g Express Edition------

Go to Database Homepage.
2. Enter Username and Password as system.
3. Declare and assign the values of respective variables and check the given
number is factorial or not using WHILE Loop.
4. Declare and assign the values of respective variables and perform
multiplication table using FOR Loop.
12.PL/SQL BLOCK USING IMPLICIT CURSORS
AIM:

Write a PL/SQL program using Implicit Cursors.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g

Express Edition------Go to Database Homepage.

2. Enter Username and Password as system.

3. Declare and SET the values of respective variables
and add the given amount to the Salary and update the
Salary in the Employee table using Implicit Cursors.
13.PL/SQL BLOCK USING EXPLICIT CURSORS

AIM:

Write a PL/SQL program using Explicit Cursors.

ALGORITHM:

1. Start---All Programs-----Select Oracle Database 10g

Express Edition------Go to Database Homepage.
2. Enter Username and Password as system.
3. Declare the variables using anchored Data type with
reference to Employee table.
4. Fetch and display the required data from the
Employee table using Explicit Cursors.
14. PL/SQL Block using Procedure.

AIM:

Write a PL/SQL program using Procedure.

ALGORITHM:

1.start--All programs--select oracle database10g Express Edition----Go to

database homepage.

2.Enter username and password as system.

3. Create table Procedure to and Create a new table and it's fields using

the fields in Employee table

4.Select Data and store to new table using where Condition from the

Employee table.

5. Display the structure of new table.

6. Display the records in the new table.
15.PL/SQL Block using Functions

AIM:
Write a PL/SQL program using functions.
ALGORITHM:

1.start--All programs--select oracle database10g Express Edition----Go to

database homepage.
2.Enter username and password as system.
3. Create a function to return the number of records(count)in the
Employee table.
4.using select command to find the no of records in employee table
5. In the main program call the function created
6. Then display the total number of records in employee table