6 Likes 1 Comment

In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that takes care of fetching and updating of data in the database whereas PL/SQL is the component that processes these data. Further, in this article, we will also discuss how to combine the SQL within the PL/SQL block.



Create the following table

(i) Studies ( P_name, S_place, Course, Cost_no )

(ii) Programmer ( P_name, DOB, DOJ, Gender, Prof1, Prof2, Sal ). Make P_name as foreign key from Studies relation.

In XAMPP Software, ADD Foreign Key procedure is :


Alter table Child_table_name add Foreign key ( Column_name ) references
parent_table (Column_name )

For Foreign Key: https://www.youtube.com/watch?v=A42FG4LzdbY

(iii) Software ( P_name, title, dev_in, S_cost, De_cost, Sold ). Make P_name as foreign key from Studies relation.

If you know any direct approach to add foreign key, please place a comment .

2. Insert 5 rows in Studies table.

  1. Insert 5 rows in Programmer table.

4. Insert 5 rows in Software table.

  1. (i) Display software table.

5. (ii) Display Programmer table.

5. (iii) Display studies table.

6. Write the following queries:

(i) Find out the selling cost average for packages developed in oracle.

(ii) Display the name of those who have done the PHP course.

(iii) Display the name and CV of all the programmer.

How to get an age from a birth field in MySQL XAMPP?

select name, birth(same), CURDATE(), TIMESTAMPDIFF(YEAR, birth(same), CURDATE()) AS AGE from users;

Using TIMESTAMPDIFF , we will get age.

DATE REALETED : https://www.w3schools.com/sql/sql_ref_mysql.asp

(iv) What is the highest number of copies sold by package.

(v) Display the name and date of birth of all the programmers born in April.

(vi) How many programmer have done the Java Course

(vii) How many revenue has been earned through the sale of package develop in C.

(viii) Display the details of software develope by Rakesh.

(ix) Display the details of packages for which the development cost has been recover.

(x) What is the price of the cost lies software develop in VB.

(xi) How many package developed in ORACLE.

(xii) How many programmers paid 10000 to 20000 for the course.

(xiii) Display the details of the programmer knowing C.

(xiv) How many programmer know either C or Java

(xv) How many programmer don’t know C and C++.

(xvi) How old is the oldest male programmer.

(xvii) How many female programmers are there.

(xviii) What are the language known by the Male programmer.

(xix) How many programmer drop 5000 to 10000.

(xx) Show the programmer and development details of the package which has been sold highest number of package.


You might like


About the Author: Saif

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner