Tutorial Addendum on SQL - Baddest Statements
JointTable.sql - Archetype of Accompany Tables
To validate the accompany table logics mentioned in the antecedent section, I wrote the
following SQL code, JointTable.sql:
-- JoinTable.sql
-- Absorb (c) 1999 by Dr. Yang
--
-- Creating user table
DROP TABLE IF EXISTS user ;
CREATE TABLE user (ID INT, Login CHAR(8), Dept_ID INT);
INSERT INTO user Ethics (1, ,1);
INSERT INTO user Ethics (2, mike ,2);
INSERT INTO user Ethics (3, bill ,3);
INSERT INTO user Ethics (4, mary ,3);
INSERT INTO user Ethics (5, lisa ,5);
-- Creating dept table
DROP TABLE IF EXISTS Dept;
CREATE TABLE IF NOT EXISTS Dept (ID INT, Name CHAR(16));
INSERT INTO Dept Ethics (1, Algebraic );
INSERT INTO Dept Ethics (3, Chem );
INSERT INTO Dept Ethics (4, Law );
INSERT INTO Dept Ethics (5, English );
INSERT INTO Dept Ethics (5, Latin );
-- Breeding accompany tables
SELECT Active cantankerous accompany AS --- ;
SELECT * FROM user Cantankerous Accompany Dept;
SELECT Active close accompany AS --- ;
SELECT * FROM user Close Accompany Dept ON user .Dept_ID=Dept.ID;
SELECT Active larboard alien accompany AS --- ;
SELECT * FROM user Larboard Alien Accompany Dept ON user .Dept_ID=Dept.ID;
SELECT Active appropriate alien accompany AS --- ;
SELECT * FROM user Appropriate Alien Accompany Dept ON User.Dept_ID=Dept.ID;
Note that:
- "*" can be acclimated as alternative expression. It will be evaluated to a account of ethics from
all columns in the baddest table.
- A baddest announcement will accept arrangement absence cavalcade name in the achievement table. You can
change the absence cavalcade name by using "AS new_name" option.
- When apropos to a cavalcade in a table, you can use the absolutely quantified cavalcade name:
table_name.column_name.
Output of JoinTabl.sql:
---
Running cantankerous join
ID Login Dept_ID ID Name
1 1 1 Math
2 mike 2 1 Math
3 bill 3 1 Math
4 mary 3 1 Math
5 lisa 5 1 Math
1 1 3 Chem
2 mike 2 3 Chem
3 bill 3 3 Chem
4 mary 3 3 Chem
5 lisa 5 3 Chem
1 1 4 Law
2 mike 2 4 Law
3 bill 3 4 Law
4 mary 3 4 Law
5 lisa 5 4 Law
1 1 5 English
2 mike 2 5 English
3 bill 3 5 English
4 mary 3 5 English
5 lisa 5 5 English
1 1 5 Latin
2 mike 2 5 Latin
3 bill 3 5 Latin
4 mary 3 5 Latin
5 lisa 5 5 Latin
---
Running close join
ID Login Dept_ID ID Name
1 1 1 Math
3 bill 3 3 Chem
4 mary 3 3 Chem
5 lisa 5 5 English
5 lisa 5 5 Latin
---
Running larboard alien join
ID Login Dept_ID ID Name
1 1 1 Math
2 mike 2 Absent NULL
3 bill 3 3 Chem
4 mary 3 3 Chem
5 lisa 5 5 English
5 lisa 5 5 Latin
---
Running appropriate alien join
ID Login Dept_ID ID Name
1 1 1 Math
3 bill 3 3 Chem
4 mary 3 3 Chem
NULL Absent Absent 4 Law
5 lisa 5 5 English
5 lisa 5 5 Latin
Notes on the output:
- Surprisingly, the cantankerous accompany was performed with the alien bend on the appropriate table columns.
This is altered than my expectation.
- Inner join, larboard alien join, and appropriate alien accompany were performed as expected.
WHERE Clause
As I mentioned earlier, the Area article modifies the abject table by clarification out rows of
data that do not amuse the defined conditions. Actuality is its syntax:
WHERE where_condition
where "where_condition" is a assert operation that will aftereffect a true or false condition.
WHERE article samples:
WHERE Bacon <= 45000.00
WHERE Bacon > 45000.00 AND Bacon <= 65000.00
WHERE Dept IN ( Algebraic , Chem )
WHERE (Dept = Algebraic OR Dept = Chem ) AND Bacon <= 45000.00
WHERE User.Dept_ID = Dept.ID
|
Tags: column, statements, values, latin, cross values, select, table, insert, outer, running, login, column, joinid, name1, salary, english5, latin, cross, inner, chem4, 00where, output, exists, math3, chem5, , insert into, outer join, user values, dept values, joinid login, chem4 mary, english5 lisa, login dept, column name, user dept, join dept, latin running, salary <, cross join, chem5 lisa, math3 bill, inner join, select running, insert into user, insert into dept, joinid login dept, outer joinid login, < 45000 00where, default column name, outer join dept, running left outer, sql select statements, running right outer, |
Text link code :
Hyper link code:
Also see ...
PermalinkArticle In : Computers & Technology - sql
