SQL Induction Program

This Induction Program is both for Developers and Testers. 
This post will go through basics concepts which should be known to everyone interested in SQL Server.


Table of Content


 Section 1

• 1.1 Basics of the SELECT Statement
• 1.2 Conditional Selection
• 1.3 Relational Operators
• 1.4 Compound Conditions
• 1.5 IN & BETWEEN
• 1.6 Using LIKE

Section 2

• 2.1 Joins
• 2.2 Keys
• 2.3 Performing a Join
• 2.4 Eliminating Duplicates
• 2.5 Aliases & In/Subqueries

Section 3

• 3.1 Aggregate Functions
• 3.2 Views
• 3.3 Creating New Tables
• 3.4 Altering Tables
• 3.5 Adding Data
• 3.6 Deleting Data
• 3.7 Updating Data

Section 4

• 4.1 Indexes
• 4.2 GROUP BY & HAVING
• 4.3 More Sub-queries
• 4.4 EXISTS & ALL
• 4.5 UNION & Outer Joins
• 4.6 Embedded SQL
• 4.7 Common SQL Questions
• 4.8 Nonstandard SQL
• 4.9 Syntax Summary

Section 1.1   Basics of the SELECT Statement
In a relational database, data is stored in tables. An example table would relate Social Security
Number, Name, and Address:

Now, let's say you want to see the address of each employee. Use the SELECT statement, like so:

SELECT FirstName, LastName, Address, City, State

FROM EmployeeAddressTable;

The following is the results of your query of the database:

To explain what you just did, you asked for the all of data in the EmployeeAddressTable, and

specifically, you asked for the columns called FirstName, LastName, Address, City, and State. Note

that column names and table names do not have spaces...they must be typed as one word; and that the

statement ends with a semicolon (;). Semicolon is not necessary all the time but count good programming practice. 

 The general form for a SELECT statement, retrieving all of the rows in the table is:

SELECT ColumnName, ColumnName, ...

FROM TableName;

To get all columns of a table without typing all column names, use:

SELECT * FROM TableName;

Each database management system (DBMS) and database software has different methods for logging

in to the database and entering SQL commands.

Section 1.2 Conditional Selection

The WHERE clause is used to specify that only certain rows of the table are displayed, based on the

criteria described in that WHERE clause. It is most easily understood by looking at a couple of

examples.

If you wanted to see the EMPLOYEEIDNO's of those making at or over $50,000, use the following:

SELECT EMPLOYEEIDNO

FROM EMPLOYEESTATISTICSTABLE

WHERE SALARY >= 50000;

EMPLOYEEIDNO

------------

010

105

152

215

244

The WHERE description, SALARY >= 50000, is known as a condition. The same can be done for text

columns:

SELECT EMPLOYEEIDNO

FROM EMPLOYEESTATISTICSTABLE

WHERE POSITION = 'Manager';

This displays the ID Numbers of all Managers. Generally, with text columns, stick to equal to or not

equal to, and make sure that any text that appears in the statement is surrounded by single quotes (').

Section 1.3 Relational Operators

There are six Relational Operators in SQL. Usage of two of them is already demonstrated in Section 1.2.

keep checking .....will Continue.........