Learn Joint Queries in sql

- A Joint query joints some of the rows in one table with some of the rows in another table.

- For each part being supplied get the part number and the names of all cities which are supplied that part

SELECT p.pno,city
FROM p,spj
WHERE p.pno=spj.pno

-Get all pairs of part name and project name such that the part and the project are located
in the same city

SELECT pname,jname
FROM p,j
WHERE p.city=j.city

Get all information about suppliers and parts where the supplier and the part concern are
located in the same city but omitting suppliers with status value less than 20

SELECT s.*,p.*
FROM s,p
WHERE s.city=p.city AND status>=20

Get all suppliers and their supplying information of those suppliers with status value less than 20 and the quantity supplied grater than 200

SELECT s.*,spj.*
FROM s,spj
WHERE s.sno=spj.sno AND status<20>200


For each shipment get the supplier number, part number, project number and the value of the
shipment

SELECT spj.sno,p.pno,spj.jno,spj.qty*p.price
FROM p,spj
WHERE spj.pno=p.pno

Get all pairs of city names such that a supply located in the first city is currently supplying a part stored in the second city

SELECT DISTINCT s.city,p.city
FROM s,spj,p
WHERE s.sno=spj.sno AND spj.pno=p.pno

Get all pairs of supplier numbers such that two suppliers concern are located in the same city

SELECT f.sno,t.sno
FROM s f, s t
WHERE f.city=t.city AND f.sno

learn simple sql command using example data base-select clause

A Part of a Bank Database


The select Clause
- The select clause list the attributes desired in the result of a query (corresponds to the projection operation of the relational algebra)
- E.g. find the names of all branches in the loan relation
select branch_name
from loan
- In the “pure” relational algebra syntax, the query would be:
∏branch-name(loan)
- SQL does not permit the ‘-’ character in names,
• Use, e.g., branch_name instead of branch-name in a real implementation
- SQL names are case insensitive, i.e. you can use capital or small letters.

- The general form of the SELECT clause
Select [distinct] attribute (s)
from table (s)
[where condition]
[group by attributes (s)]
[having condition]
[order by attributes (s)]
- SQL allows duplicates in relations as well as in query results
- To force the elimination of duplicates, insert the keyword distinct after select

- e.g Find the names of all branches in the loan relations, and remove duplicates
select distinct branch_name
from loan
- The keyword all specifies that duplicates not be removed.
select all branch_name
from loan

- An asterisk in the select clause denotes “all attributes”
select *
from loan
- The select clause can contain arithmetic expressions involving
the operation, +, –, *, and /, and operating on constants or
attributes of tuples
- The query:
select loan_number, branch_name, amount * 100
from loan
would return a relation which is the same as the loan relations,
except that the attribute amount is multiplied by 100

learn Basic Structure of SQL

- SQL is based on set and relational operations with certain modifications and enhancements
- A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
• Ais represent attributes
• ris represent relations
• P is a predicate.
-This query is equivalent to the relational algebra (to be
discussed later) expression ∏A1, A2, ..., An(σP (r1 x r2 x ... x
rm))
- The result of an SQL query is a relation.