SQL Structure Query Language 3rd week of classes.
Go to Simple Queries, Joins
Q1. How is information retrieved from the database?
A1. All information is retrieved from a database (explicit and implicit) using SQL. This includes information on tables and columns, security, usage statistics, etc.
Q2. What about updates and other changes in and to
a database?
A2. That is also done with SQL. Retrieval and
update are referred to as DML (Data Manipulation Language)
Q3. How about creating new tables, indices on
attributes or modifying table definition?
A3. That's also is done with SQL. Object
creation and deletion are referred as DDL (Data Definition Language). But DML
and DDL are parts of SQL.
Q4. What is the main difference between a procedural
language like C and SQL?
A4. In a procedural language like C we tell
the computer each step it should take, line by line, i.e we spell out the
procedure but in SQL we declare what we want and it's up to the
interpreter to supply the answer. So SQL is a declarative language
as opposed to a procedural language. Also SQL always returns a relation
(table) and not a variable value as in a procedural languages.
Q5. How would I retrieve, using SQL, the
contents of the entire S (Suppliers) table in DataSpj database?
A5. After you have downloaded and installed
SqlLearn and downloaded spj.mdb database,
open the spj.mdb in SqlLearn and type the following SQL statement :
SELECT* FROM S;
Then press execute under sql menu
item or press ctrl & x then the entire table S should appear. The *
(a wildcard) means you want all the columns in table S.
Q6. How about getting all the supplier names that
are in
A6. select sName
from
S
where
city = '
select is a
reserved (key) word in SQL, sName is a column name in the S
table, where is a key word in SQL, city is a column name in table
S and 'London' is a column value in column city.
SELECT sName
FROM S
WHERE city =
'
Q7. What if I wanted suppliers from every city
except
A7. SELECT sName
FROM S
WHERE city
<> '
Q8. How about something more complicated like: get
all shipments that supplied part P1 to job J3 where the quantity of shipment is
in the range 300 to 725 inclusive?
A8. SELECT *
FROM SPJ
WHERE pNo =
'P1'
AND jNo =
'J3'
AND qty
>= 300
AND qty<=
725;
Q9. What about a derived value such as shipping
cost of a part based on a formula such as cost=weigt*1.25?
A9. SELECT pNo, 'shipping cost =',
weight*1.25
FROM P;
Q10. Can we order the output based on some
attribute or attributes?
A10. SELECT pNo, weight*1.25
FROM P
ORDER BY 2 ASC, pNo DESC;
Note that ORDER BY are key words, ASC (default)
means ascending order, 2 means order by column 2 values (weight*1.25 ) and DESC
means descending order. The result will be ordered by weight*1.25 values in
increasing order and within repeating weight*1.25 values i t will be ordered by
pNo in descending order.
Microsoft supports the following type of queries of selecting top n rows:
SELECT TOP 2 *
FROM J;
Also you can select top n percent;
Select top 25 percent
From j;
Q11. How would I get information from more than one table? Say, I want to know supplier names of suppliers that shipped to job J5.
A11. This query needs the S table for sName and it needs the spj table to check which suppliers shipped to J5.
SELECT S.sName
FROM S, SPJ
WHERE SPJ.jNo = 'J5'
AND S.sNo = SPJ.sNo;
You must remember this: FUNDAMENTAL:
When you join two relations(tables)
then you must have a join condition.
In the above example the join condition is S.sNo = SPJ.sNo. Note that when we have two tables we have to specify which table the attribute comes from, as in S.sNo or SPJ.sNo. A join creates a new table of all possible pairs of rows from each table, but it's the join condition that picks out meaningful pairs, i.e. pair where the supplier (sNo) is related to a shipment record by a matching sNo in SPJ.
Q12. Can you show me how to get information from
more than two tables? Say, get all triples of city names such that the first
city supplies a part stored in the second city and third city is where the part
was shipped to. In addit ion we want only the shipments that shipped in
quantity greater than 200.
A12. SELECT DISTINCT S.City, P.City, J.City
FROM S, P, J, SPJ
WHERE S.sNo = SPJ.sNO
AND P.pNo = SPJ.pNO
AND J.jNO = SPJ.jNO
AND SPJ.qty > 200;
Note that we joined four tables which requires three join conditions, one for each pairs of tables: S & SPJ, P & SPJ and J & SPJ. Also note that the verb shipped has given us a clue that the SPJ table is needed because that is the table that records all shipments. In each join condition we matched a Primary Key with its corresponding Foreign Key. The key word DISTINCT was used to eliminate any duplicate triples.
Q13. What is a left outer join?
A13. It's when you join two tables and there is a
row in the first table that has no reference to it in the second table but you
still would like to see that row from the first (left) table in the
joined table. There is a right outer join as well where you see records
in the second (right) table even if there are no matching record in the first
table.
Try this in SqlLearn:
Insert
a new row into S table,that has no reference to it in the spj table, with this
SQL statement:
INSERT
INTO S
VALUES
('S8','Vas',30,'
then
try this select:
SELECT
*
FROM S
LEFT OUTER JOIN SPJ ON S.sNo=SPJ.sNo
WHERE
S.sNo = 'S8';
You
see how row 'S8' is listed even though there is no corresponding record to 'S8'
in spj. If you try:
SELECT
*
FROM S,
SPJ
WHERE
S.sNo = SPJ.sNo
AND
S.sNo = 'S8';
you
will not see 'S8' row.
WARNING
THE SYNTAX FOR OUTER JOINS IS DIFFERENT WITH OTHER VENDORS.
The above
syntax is for Microsoft Access.
In Oracle
the left outer join would look like this:
Select
*
From
s, spj
Where
spj.sno (+)= s.sno
Ans
s.sno = ‘s8’;
Now delete row 'S8'
with:
DELETE FROM S
WHERE S.No='S8';