PL/SQL XAMPP EXAMPLE SET 2

 

In this , we are going to learn how to use SQL in XAMPP. Here mainly cover CREATE, FOREIGN KEY ADD, ALTER, INSERT, SELECT, AGE RETRIEVE, TIMESTAMPDIFF, DISTINCT, LIKE, CURDATE, COUNT, SUM, MAX, AND, OR, IN, NOT IN, TRUNC, RENAME OPERATION ,UNION, BETWEEN, NESTED QUERIES. All types are discussed in the given below links, you might like,

Preparation Related 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 2:

Create the following table

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

create table studies ( P_name varchar(20) primary key, Study_place varchar(20), Course varchar(20), Cost_no int(20));

OUTPUT:
Table Created

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

create table programmer (P_name varchar2(20), DOB date, DOJ date, Gender varchar(15), Prof1 char(10), prof2 char(10), salary int(20));

OUTPUT:
Table Created

In XAMPP Software, ADD Foreign Key procedure is :

Syntax:

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

Alter table BIMS.Programmer add Foreign key ( P_name ) references BIMS.studies ( P_name ) 

OUTPUT:
Table Altered

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

create table BIMS.Software(P_name varchar(20), Tittle char(10), Dev_in char(10), S_cost int(20), D_cost int(20), Sold int(20)) 

OUTPUT:
Table Created

#Foreign Key

Alter table BIMS.Software add Foreign key ( P_name ) references BIMS.studies ( P_name ) 

OUTPUT:
Table Altered

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

2. Insert 5 rows in Studies table.

insert into studies values ('Surushi', 'IIT', 'PHP', 15000)
insert into studies values ('Rakesh', 'IIT', 'JAVA', 20000)
insert into studies values ('Ram', 'IIT', 'NET', 10000)
insert into studies values ('Komla', 'IIT', 'PHP', 15000)
insert into studies values ('Sam', 'IIT', 'JAVA', 2000)

OUTPUT:
5 rows inserted

3. Insert 5 rows in Programmer table.

insert into Programmer values ('Surushi', '1997/04/03','2016/11/04', 'FEMALE', 'C', 'JAVA',5000);
insert into Programmer values ('Rakesh', '1997/04/03','2016/12/04', 'MALE',  'JAVA','C++', 8000);
insert into Programmer values ('Ram', '2018/04/26','2016/02/23', 'MALE',  'C++', 'C',8000);
insert into Programmer values ('Komla', '1995/04/22','2017/04/18', 'FEMALE',  'C++', 'C',8000);

OUTPUT:
5 rows inserted

4. Insert 5 rows in Software table.

insert into Software values ('Surushi','Creator','Oracle',2000,1000,5000);
insert into Software values ('Ram','Developer','C',3000,1000,7000);
insert into Software values ('Rakesh','Developer','Oracle',2000,1000,8000);
insert into Software values ('Sam','Creator','VB',2000,1000,9000);
insert into Software values ('Komla','Maintainer','VB',3000,1000,9000);

OUTPUT:
5 rows inserted

(i) Display software table.

Select * from software;

OUTPUT:
P_name	Tittle	Dev_in	S_cost	D_cost	Sold 	
Surushi 	Creator 	Oracle 	2000 	1000 	5000
Ram 	Developer 	C 	3000 	1000 	7000
Rakesh 	Developer 	Oracle 	2000 	1000 	8000
Sam 	Creator 	VB 	2000 	1000 	9000
Komla 	Maintainer 	VB 	3000 	1000 	9000

5. (ii) Display Programmer table.

select * from programmer;

OUTPUT:
P_name	DOB	DOJ	Gender	Prof1	prof2	salary 	
Surushi 	1997-04-03 	2016-11-04 	FEMALE 	C 	JAVA 	5000
Rakesh 	1997-04-03 	2016-12-04 	MALE 	JAVA 	C++ 	8000
Ram 	2018-04-26 	2016-02-23 	MALE 	C++ 	C 	8000
Komla 	1995-04-22 	2017-04-18 	FEMALE 	C++ 	C 	8000

5. (iii) Display studies table.

select * from studies;

OUTPUT:

P_name	Study_place	Course	Cost_no 	
Komla	 	IIT 	PHP 	15000
Rakesh 		IIT 	JAVA 	20000
Ram 		IIT 	NET 	10000
Sam 		IIT 	JAVA 	2000
Surushi 	IIT 	PHP 	15000

6. Write the following queries:

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

select avg(S_cost) as avg_cost from software where Dev_in='ORACLE';

OUTPUT:
avg_cost 	
2000.0000

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

 select P_name from studies where course='PHP';

OUTPUT:
P_name
Komla
Surushi

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

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

Syntax:
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

https://www.youtube.com/watch?v=eHIHbdBmPeg 
# SQL
 select P_name, trunc(months_between(sysdate,DOB)/12) as age, trunc(months_between(sysdate,DOJ)/12) as experience from programmer;

# XAMPP
select P_name,  timestampdiff(year,DOB,CURDATE()) as age,   timestampdiff(year,DOJ,CURDATE()) as experience from programmer; 

OUTPUT: 
P_name	age	experience 	
Surushi 23 	4
Rakesh 	23 	3
Ram 	24 	2
Komla 	25 	3

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

select distinct(dev_in) from software where sold= (select max(sold) from software)

OUTPUT:
dev_in 	
VB

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

select P_name, DOB from programmer where DOB like '%04%'

OUTPUT:
P_name	 DOB 	
Surushi 1997-04-03
Rakesh 	1997-04-03
Ram 	1996-04-26
Komla 	1995-04-22

(vi) How many programmer have done the Java Course

select count(*) As Java Programmer from studies where course='Java';

OUTPUT:
Java Programmer
2

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

select sum(s_cost * sold - d_cost) as revenue from software where dev_in = 'C';

OUTPUT:
revenue 	
20999000

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

select * from software where P_name ='Rakesh';

OUTPUT:
P_name 	Tittle 	Dev_in 	S_cost 	D_cost 	Sold 	
Rakesh 	Developer 	Oracle 	2000 	1000 	8000

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

select * from software where s_cost * sold > D_cost;

OUTPUT:
P_name	Tittle		Dev_in	S_cost	D_cost	Sold 	
Surushi Creator 	Oracle 	2000 	1000 	5000
Ram 	Developer 	C 	3000 	1000 	7000
Rakesh 	Developer 	Oracle 	2000 	1000 	8000
Sam 	Creator 	VB 	2000 	1000 	9000
Komla 	Maintainer 	VB 	3000 	1000 	9000

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

select max(s_cost) from software group by dev_in having dev_in ='VB';

OUTPUT:
max(s_cost) 	
3000

(xi) How many package developed in ORACLE.

select count(*) from software where dev_in ='ORACLE';

OUTPUT:
count(*) 	
2

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

select count(*) from studies where cost_no between 10000 and 20000

OUTPUT:
P_name	Study_place	Course	Cost_no 	
Komla 	IIT 	PHP 	15000
Rakesh 	IIT 	JAVA 	20000
Ram 	IIT 	NET 	10000
Surushi IIT 	PHP 	15000

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

select * from programmer where prof1= 'C' or prof2='C'

OUTPUT:
P_name 	 	DOB	DOJ	        Gender	Prof1	prof2	salary 	
Surushi 1997-04-03 	2016-11-04 	FEMALE 	C 	JAVA 	5000
Ram 	1996-04-26 	2016-02-23 	MALE 	C++ 	C 	8000
Komla 	1995-04-22 	2017-04-18 	FEMALE 	C++ 	C 	8000

(xiv) How many programmer know either C or Java

select count(*) from programmer where prof1 in ('C','Java') or prof2 in ('C','Java')

OUTPUT: 
count(*) 	
4

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

select count(*) from programmer where prof1 not in ('C','C++') and prof2 not in ('C','C++')

OUTPUT:
count(*) 	
0

(xvi) How old is the oldest male programmer.

# SQL
select trunc(max(months_between(sysdate,DOB)/12)) as counter from programmer where Gender ="Male"


# XAMPP
select 
max(timestampdiff(year,DOB,CURDATE()))
as counter from programmer where Gender ="Male"


OUTPUT:
counter 	
24

(xvii) How many female programmers are there.

select count(*) from programmer where Gender = 'Female';

OUTPUT:
count(*) 	
2

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

select distinct prof1 as language from programmer where Gender = 'Male' union select distinct prof2 from programmer where Gender='Male';

OUTPUT:
language 	
JAVA
C++
C

(xix) How many programmer drop 5000 to 10000.

select count(*) from programmer where salary between 5000 and 10000

OUTPUT:
count(*) 	
4

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

select * from software where sold=(select max(sold) from software)

OUTPUT:
P_name	Tittle	Dev_in	S_cost	D_cost	Sold 	
Sam 	Creator 	VB 	2000 	1000 	9000
Komla 	Maintainer 	VB 	3000 	1000 	9000

Simply Practice it and with the help of preparation notes, You can get sufficient knowledge in DBMS or PL/SQL language. You cover CREATE, FOREIGN KEY ADD, ALTER, INSERT, SELECT, AGE RETRIEVE, TIMESTAMPDIFF, DISTINCT, LIKE, CURDATE, COUNT, SUM, MAX, AND, OR, IN, NOT IN, TRUNC, RENAME OPERATION ,UNION, BETWEEN, NESTED QUERIES topics.

Comments

Post a Comment

Popular posts from this blog

Oracle Live SQL

Image
  In this article, you are going to learn how to use Oracle Live SQL and how to work with Oracle Database without installing the database. Get instant access to the Oracle Database and learn from a collection of community scripts and tutorials. Write your own SQL scripts and easily share them with others. All you need to get started is your Oracle.com account. Following steps helps you to build your knowledge in SQL. Step 1: Start with a basic structure as given in the following below diagram. Fig: Table Step 2: Learn some basic terminologies i.e. Field, Row, And Column (s). So, it helps you to better understanding. Step 3: Break it into some simple steps, i.e. Create a Table, Make some conditions on attributes (integrity constraints), Inserting values, and Make sure all values are inputted in their proper place. Step 4: There are some queries and you should answer those. Step 5: Commit; After Creating and inserting values because sometimes data is not save
Read more

PL/SQL XAMPP EXAMPLE SET 1

Image
  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/ Oracle Database 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 var
Read more