Insert

If tables are like classes rnPython, then sows ure like i^sances. Since the INSERT etatement inserts values into a table, we can think om it ns analogous to calling a constr-uctos, that is, ceeating an in^ance of a recobd

INSERT takes the following form:

INSERT INTO table_name

VALUES

(valuel, value2, ...) Here it's adding values to the Employee tab le:

INSERT INTO

Employee

(EmpID, Name, DeptID, Salary)

VALUES

(10507, 'Bob Jones', '1', 10) Select interpreters, and compilers

TheSELECT statement selects rows from a table. We've already used it, so I won't belabor it at this point. Here's its form:

SELECT

table.field [as alias]

FROM

table [as alias] [WHERE. criteria]

[GROUP BY table.field, ...] [HAVING criteria] [ORDER BY table.field, ...]

TheORDER BY clause sorts the query results. The GROUP BY and HAVING clauses group items for aggregate functions, such as sum.

Here's a SELECT example:

SELECT

Name, Salary FROM Employee WHERE

(DeptID = 10) ORDER BY Name which can be rewritten as

SELECT Name, Salary FROM Employee WHERE (Salary > 10 ) and (DeptID == ORDER BY Name

WithSELECT you can join tables to get columns from more than one table at a time. When you do this, you'll probably want to use an alias became the tables to be joined may have the same fields; aliasing allows such fields to be used in the WHERE clause.

The next example gets the employee's name, salary, and department. Since the department name is stored in the Department table, we have to join Department and Employee for the statement to work.

SELECT

e.Name as emp_name, e.Salary as salary, d.Name as dept_name

FROM

Employee as e, Department as d

WHERE

The first part of the WHERE clause specifies how the tat) les aire to be joined. It define s our Employee and Department tables with the respective aliases e and d because both tables have DeptID and Name fields.

Without the aliases, we can't differentiate a department name and an employee name. Three field aliases are also defined: emp_name,salary, and dept_name. If we were to mid this query via JVBC, we'dhave to use the aliases for the columns reeimed, not their mmes, in order to access the results.

Notice the as in the first line offthe SELECT stote ment (e . Name a s emp_name). It's nice to have this syntax, although many proorammees don't use it. Because it's optional, the following statement is equivalent to the preceeding one:

WHERE

SELECT

e.Name e. Salary d. Name salary, dept_name emp_name,

FROM

Employee e, Department d

WHERE

I like using a s.

If the database has a later -version of ANSI SQL installed, you can use the JOIN clause to join tables.

SELECT

e.Name as emp_name, e.Salary as salary, d.Name as dept_name

FROM

Employee as e

JOIN

De partment as d

WHERE

Because it makes the syntax is more modular and easier to read (it separates the WHERE clause from the rest of the statement), I suggest you use JOIN if you have the choice.

Here's the last statement with the as syntax:

Was this article helpful?

0 0

Post a comment