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