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.
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)
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.
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
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.
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.
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.
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.
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.
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
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
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.
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.
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).
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'
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 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);
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.