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.

learn sql-Substring Pattern Matching in sql

Get all suppliers whose name has ‘A’ as its second character

SELECT sname
FROM s
WHERE sname LIKE '?A*‘

? – replaces a single character
* - replaces an arbitrary number of characters

Get the surname of suppliers whose name begin with vowels

SELECT sname
FROM s
WHERE sname LIKE '[a,e,i,o,u]*'

- Get names of suppliers whose name begin with letter ‘a’ to ‘p’

SELECT sname
FROM s
WHERE sname LIKE '[a-p]*'

- Get the names of suppliers who has an even number as the first character of their status

SELECT sname
FROM s
WHERE status LIKE '[2,4,6,8]*'

example Simple Retrievals in sql

- Project all supply number, name, status and location
listed in the supplier (S) table
SELECT *
From s

- change the order of the projected attributes as supply
number, name, location and status
SELECT sno, sname, city , status
From s

- Get Part Numbers for all parts
SELECT pno
FROM p;

- Get the Part number for all parts currently being
supplied
SELECT pno
FROM spj;

- To remove duplicates
SELECT DISTINCT pno
FROM spj;

Qualified Retrievals

- Get supply numbers for suppliers in Kandy
SELECT sno
FROM s
WHERE city='Kandy‘
- Find the Parts which are located in Colombo and
weight greater than 15
SELECT pno,city,weight
FROM p
WHERE city=‘Colombo' and weight > 15

-Find the suppliers who have status value between 10 and 20
SELECT sname, status
FROM s
WHERE status between 10 and 20

-Get the details of suppliers who are either
located in Colombo or Jaffna
SELECT *
FROM s
WHERE city='colombo' or city='jaffna'
Or
SELECT *
FROM s
WHERE city in ('colombo' , city='jaffna')

- Get the name and its weight in kg for each parts in Colombo
SELECT pname,weight/1000 AS 'Weight( kg)'
FROM p
WHERE city = 'colombo'

Retrieval with Ordering

-Get the supplier number and the status for suppliers in Kandy and arrange them on descending order of status
SELECT sno, status
FROM s
WHERE city ='kandy'
ORDER BY status desc
Note: The default order is the ascending order
DESC – descending order
ASC - ascending order

SQL Data Definition for Part of the Bank Database

Data Definition Language (DDL) in sql

Create Table Construct
- An SQL relation is defined using the create table command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity_constraint1),
...,
(integrity_constraintk))

• r is the name of the relation
• each Ai is an attribute name in the schema of relation r
• Di is the data type of values in the domain of attribute Ai
- Example:
create table branch
(branch-name char(15) not null,
branch-city char(30),
assets integer)

Integrity Constraints in Create Table
- not null
- primary key (A1, ..., An)
Example: Declare branch-name as the primary key for
branch and ensure that the values of assets are nonnegative.
create table branch
(branch-namechar(15),
branch-city char(30)
assets integer,
primary key (branch-name))
primary key declaration on an attribute automatically
ensures not null in SQL-92 onwards, needs to be
explicitly stated in SQL-89

Drop and Alter Table Constructs
- The drop table command deletes all information about the dropped relation from the database.
- The alter table command is used to add attributes to an existing relation.
alter table r add A D
where A is the name of the attribute to be added to relation r
and D is the domain of A.
* All tuples in the relation are assigned null as the value for the new attribute.
- The alter table command can also be used to drop attributes of a relation alter table r drop A
where A is the name of an attribute of relation r
* Dropping of attributes not supported by many databases

Modification of the data base

Deletion
- Delete all account records at the Kandy City branch
delete *
from account
where branch_name = ‘Kandy city’

- Delete all accounts at every branch located in Kandy.
delete *
from account
where branch_name in (select branch_name from branch where branch_city=‘Kandy’)

Insertion
- Add a new tuple to account
insert into account
values (‘200116’, ‘Kandy City’,1200)

or equivalently
insert into account (branch_name, balance, account_number)
values (‘Kandy City’, 1200, ‘200116’)

- Add a new tuple to account with balance set to null
insert into account
values (‘100117’,‘Kandy City’, null)

Updates
- Increase all accounts with balances over Rs.1000 by 6%, all other accounts receive 5%.
- Write two update statements:
update account
set balance = balance * 1.06
where balance > 1000
update account
set balance = balance * 1.05
where balance ≤ 1000