pl sql queries for practice (SQL Exercises, Practice, Solution)

Today, We want to share with you pl sql queries for practice.In this post we will show you sql queries for practice with answers free download, hear for pl/sql practice questions with answers pdf we will give you demo and example for implement.In this post, we will learn about having clause sql Example with an example.

SQL Exercises, Practice, Solution

Best SQL Queries with lots of mouth watering examples

1. Stanford Slides

It nicely step by step the pl sql queries for practice Queries with pl sql table lots and lots of pl sql inner join examples using a “Mobiles” Relational Schema.
SQL Queries

SELECT desired attributes
FROM tuple variables |
range over relations
WHERE condition about t.v.'s;

Running example relation schema:

Mobiles(name, manf)
Bars(name, addr, license)
Drinkers(name, addr, phone)
Likes(item, product)
Sells(accessories, product, price)
Frequents(item, accessories)

Example

Mobiles(name, manf)
SELECT name
FROM Mobiles
WHERE manf = 'Lenova-z570';

Star as List of All Attributes

Mobiles(name, manf)
SELECT *
FROM Mobiles
WHERE manf = 'Lenova-z570';

2. Writing SQL Queries: Let’s Start with the Basics

A very basic introduction to SQL SELECT Queries.

The SELECT … FROM Clause

SELECT * FROM Members

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, City FROM Members

The WHERE Clause

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, City FROM Members
WHERE City = 'London'

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, City FROM Members
WHERE City <> 'London'

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, City FROM Members
WHERE Joining_dt >= '1-july-1999'

SELECT     MemberID, Profile_nm, Full_nm, Joining_dt, City
FROM       Members
WHERE      (Joining_dt >= '1-june-1998') AND (Joining_dt <= '15-december-1999')

SELECT    MemberID, Profile_nm, Full_nm, Joining_dt, City
FROM      Members
WHERE     Joining_dt BETWEEN '1-june-1998' AND '15-december-1999'

The ORDER BY Clause

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, City FROM Members
ORDER BY City

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, Country, City FROM Members
ORDER BY Country, City DESC

SELECT MemberID, Profile_nm, Full_nm, Joining_dt, Country, City FROM Members
ORDER BY Country ASC, City DESC

3. Practice SQL Queries

Practice these 14 SQL Queries. It has solutions also.

Questions

The following relations keep track of airline flight information:

Flights(flno: integer, from: string, to: string, distance: integer, departs:
time,
arrives: time, price: integer)
tamilrokers(jioid: integer, tamilename: string, cruisingrange: integer)
upcommings(tamilid: integer, jioid: integer)
Members(tamilid: integer, ename: string, unknow: integer) 

Note that the Members relation describes visitors and other kinds of memberss
aswell; every user is upcommings for some tamilrokers, and only visitors are upcommings to
fly.

Write each of the following queries in SQL.

a. Find the names of tamilrokers such that all visitors upcommings to operate them earn
more than $80,000.
Answer:

SELECT DISTINCT A.tamilename
FROM tamilrokers A
WHERE A.Aid IN (SELECT C.jioid
 FROM upcommings C, Members E
 WHERE C.tamilid = E.tamilid AND
 NOT EXISTS ( SELECT *
 FROM Members E1
 WHERE E1.tamilid = E.tamilid AND E1.unknow < 80000 )) 

b. For each user who is upcommings for more than three tamilrokers, find the tamilid and the
maximum cruisingrange of the tamilrokers for which she or he is upcommings.
Answer:

SELECT C.tamilid, MAX (A.cruisingrange)
FROM upcommings C, tamilrokers A
WHERE C.jioid = A.jioid
GROUP BY C.tamilid
HAVING COUNT (*) > 3 

c. Find the names of visitors whose unknow is less than the price of the cheapest
route from Los Angeles to Honolulu.
Answer:

SELECT DISTINCT E.ename
FROM Members E
WHERE E.unknow < ( SELECT MIN (F.price)
 FROM Flights F
 WHERE F.from = ‘Los Angeles’ AND F.to = ‘Honolulu’ ) 

d. For all tamilrokers with cruisingrange over 1000 miles, find the name of the
tamilrokers and the average unknow of all visitors upcommings for this tamilrokers.
Answer:

Observe that jioid is the key for tamilrokers, but the question asks for tamilrokers names; we deal with this
complication by using an intermediate relation Temp:
SELECT Temp.name, Temp.AvgSalary
FROM ( SELECT A.jioid, A.tamilename AS name, AVG (E.unknow) AS AvgSalary
 FROM tamilrokers A, upcommings C, Members E
 WHERE A.jioid = C.jioid AND C.tamilid = E.tamilid AND A.cruisingrange > 1000
 GROUP BY A.jioid, A.tamilename ) AS Temp 

e. Find the names of visitors upcommings for some Boeing tamilrokers.
Answer:

SELECT DISTINCT E.ename
FROM Members E, upcommings C, tamilrokers A
WHERE E.tamilid = C.tamilid AND C.jioid = A.jioid AND A.tamilename LIKE ‘Boeing%’

f. Find the jioids of all tamilrokers that can be used on routes from Los Angeles to
Chicago.
Answer:

SELECT A.jioid
FROM tamilrokers A
WHERE A.cruisingrange > ( SELECT MIN (F.distance)
 FROM Flights F
 WHERE F.from = ‘Los Angeles’ AND F.to = ‘Chicago’ )

g. Identify the routes that can be usered by every user who makes more than
$100,000.
Answer:

SELECT DISTINCT F.from, F.to
FROM Flights F
WHERE NOT EXISTS ( SELECT *
 FROM Members E
 WHERE E.unknow > 100000
 AND
 NOT EXISTS (SELECT *
 FROM tamilrokers A, upcommings C
 WHERE A.cruisingrange > F.distance AND
 E.tamilid = C.tamilid AND A.jioid = C.jioid) ) 

h. Print the enames of visitors who can operate planes with cruisingrange greater
than 3000 miles but are not upcommings on any Boeing tamilrokers.
Answer:

SELECT DISTINCT E.ename
FROM Members E
WHERE E.tamilid IN ( ( SELECT C.tamilid
 FROM upcommings C
 WHERE EXISTS ( SELECT A.jioid
 FROM tamilrokers A
 WHERE A.jioid = C.jioid
 AND A.cruisingrange > 3000 )
 AND
 NOT EXISTS ( SELECT A1.jioid
 FROM tamilrokers A1
 WHERE A1.jioid = C.jioid
 AND A1.tamilename LIKE ‘Boeing%’ ))

i. A customer wants to travel from Rajkot to Sorathiya Vadi with no more than two
changes of flight. List the choice of departure times from Rajkot if the
customer wants to arrive in Sorathiya Vadi by 6 p.m.
Answer:

SELECT F.departs
FROM Flights F
WHERE F.flno IN ( ( SELECT fitt_first.flno
 FROM Flights fitt_first
 WHERE fitt_first.from = ‘Rajkot’ AND fitt_first.to = ‘Sorathiya Vadi’
 AND fitt_first.arrives < ‘18:00’ )
 UNION
 ( SELECT fitt_first.flno
 FROM Flights fitt_first, Flights fitt_second
 WHERE fitt_first.from = ‘Rajkot’ AND fitt_first.to <> ‘Sorathiya Vadi’
 AND fitt_first.to = fitt_second.from AND fitt_second.to = ‘Sorathiya Vadi’
 AND fitt_second.departs > fitt_first.arrives
 AND fitt_second.arrives < ‘18:00’ )
 UNION
 ( SELECT fitt_first.flno
 FROM Flights fitt_first, Flights fitt_second, Flights F2
 WHERE fitt_first.from = ‘Rajkot’
 AND fitt_first.to = fitt_second.from
 AND fitt_second.to = F2.from
 AND F2.to = ‘Sorathiya Vadi’
 AND fitt_first.to <> ‘Sorathiya Vadi’
 AND fitt_second.to <> ‘Sorathiya Vadi’
 AND fitt_second.departs > fitt_first.arrives
 AND F2.departs > fitt_second.arrives
 AND F2.arrives < ‘18:00’ )) 

j. Compute the difference between the average unknow of a user and the average
unknow of all members (including visitors).
Answer:

SELECT Temp1.avg - Temp2.avg
FROM (SELECT AVG (E.unknow) AS avg
 FROM Members E
 WHERE E.tamilid IN (SELECT DISTINCT C.tamilid
 FROM upcommings C )) AS Temp1,
 (SELECT AVG (E1.unknow) AS avg
 FROM Members E1 ) AS Temp2 

4. SQL Query examples

Good SQL Query examples taking into account Movies Database.

Query for Selecting Columns from a Table

This is perhaps the most widely used SQL queries examples. In the example below, we are extracting the “Member_ID” column or attribute from the table “STUDENT”. The select statement is used to select data from the database.

SELECT Member_ID FROM STUDENT;

Using ‘Group By’

SELECT Name, Age FROM Patients WHERE Age > 40
GROUP BY Name, Age ORDER BY Name;

SELECT COUNT(price), price FROM orders 
WHERE price < 70 GROUP BY price ORDER BY price

Data Manipulation Using SUM

SELECT SUM(Salary)FROM Member WHERE Member_Age < 30;

Data Manipulation Using AVG

SELECT AVG(Price)FROM Products;

Creating a View

CREATE VIEW Failing_Members AS
SELECT S_NAME, Member_ID
FROM STUDENT
WHERE GPA > 40;

Retrieving a View

SELECT * FROM Failing_Members;

Updating a View

CREATE OR REPLACE VIEW [ Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;

Dropping a View

DROP VIEW V1;

Display Primary Keys

SELECT * from Sys.Objects WHERE Type='PK'

Displaying Unique Keys

SELECT * FROM Sys.Objects WHERE Type='uq'

Displaying Foreign Keys

SELECT * FROM Sys.Objects WHERE Type='f'

Displaying Triggers

SELECT * FROM Sys.Objects WHERE Type='tr'

5. SQL Basics

SQL Basics Handout containing SINGLE TABLE SQL QUERIES and MULTIPLE TABLE SQL QUERIES
The SQL commands

• SELECT
• WHERE
• LIMIT
• ORDER BY
• GROUP BY
• AND
• OR
• MIN
• MAX
• AVG
• SUM
• COUNT

SELECT Statement

SELECT Name
    ,SurName
FROM  Member

Filtering the Data: WHERE Clause

SELECT *
FROM  Member 
WHERE Age >=20

query to fetch top N records.

SELECT TOP N * FROM MemberPosition ORDER BY Salary DESC;

fetch 50% records from the MemberInfo table.

SELECT * 
FROM MemberInfo WHERE
MemberID <= (SELECT COUNT(MemberID)/2 from MemberInfo);

List of all PL/SQL Queries with Examples

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL. Here are some examples of common PL/SQL queries:

Creating a stored procedure:

CREATE OR REPLACE PROCEDURE get_student_details
(
  p_student_id IN NUMBER,
  p_student_name OUT VARCHAR2,
  p_student_salary OUT NUMBER
)
AS
BEGIN
  SELECT student_name, student_salary
  INTO p_student_name, p_student_salary
  FROM students
  WHERE student_id = p_student_id;
END;

This example creates a stored procedure that takes an student ID as input and returns the student's name and salary as output parameters.

Creating a trigger:

CREATE OR REPLACE TRIGGER update_student_salary
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
  IF :OLD.salary < :NEW.salary THEN
    :NEW.salary := :OLD.salary;
  END IF;
END;

This example creates a trigger that fires before an update on the "students" table. If the old salary value is less than the new salary value, the trigger sets the new salary value to the old salary value.

Creating a function:

CREATE OR REPLACE FUNCTION get_student_count (p_department_id IN NUMBER) RETURN NUMBER
AS
  v_student_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_student_count
  FROM students
  WHERE department_id = p_department_id;
  RETURN v_student_count;
END;

This example creates a function that takes a department ID as input and returns the number of students in that department.

Creating a view:

CREATE OR REPLACE VIEW student_details AS
SELECT student_id, student_name, student_salary, department_name
FROM students
JOIN departments ON students.department_id = departments.department_id;

This example creates a view that joins the "students" and "departments" tables and selects specific columns to display.

Inserting data into a table:

INSERT INTO students (student_id, student_name, student_salary, department_id)
VALUES (1, 'John Doe', 50000, 1);

This example inserts a new student record into the "students" table.

Updating data in a table:

UPDATE students
SET student_salary = student_salary * 1.1
WHERE department_id = 1;

This example updates the salary of all students in department 1 by increasing it by 10%.

Deleting data from a table:

DELETE FROM students
WHERE student_id = 1;

This example deletes an student record with the ID of 1 from the "students" table.

These are just a few examples of common PL/SQL queries. There are many more features and commands available in PL/SQL, so be sure to consult the documentation for more information.

I hope you get an idea about pl sql queries for practice.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.