“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.

”
Chinese Proverb

Name: Date:

To complete the Northwinds queries, you must have the Northwinds DB in your SQL server

The Northwinds ERD

SELECTing Specific Columns

The following syntax is used to retrieve specific columns in

all rows of a table.

Syntax
SELECT table_name.column_name,
table_name.column_name
FROM table;

-- OR

SELECT column, column

FROM table;

Code Sample:
SELECT FirstName, LastName
FROM Employees;
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”
Chinese Proverb

SELECTing Specific Columns

Duration: 5 to 15 minutes.

In this exercise, you will practice selecting specific columns from tables in the Northwind
database.

1. Select CategoryName and Description from the Categories table.

2. Select ContactName, CompanyName, ContactTitle and Phone from the Customers table.
3. Select EmployeeID, Title, FirstName, LastName, and Region from the Employees
table.
4. Select RegionID and RegionDescription from the Region table.
5. Select CompanyName, Fax, Phone and HomePage from the Suppliers table.

The WHERE Clause and Operator Symbols

The WHERE clause is used to retrieve specific rows from tables. The WHERE clause can contain one
or more conditions that specify which rows should be returned.

Syntax
SELECT column, column
FROM table
WHERE conditions;

The following table shows the symbolic operators used in WHERE conditions.

SQL Symbol Operators

Operator Description
= Equals
<> Not Equal
> Greater Than
< Less Than
>= Greater Than or Equal To
<= Less Than or Equal To

Note that non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in
single quotes. Examples are shown below.
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”
Chinese Proverb

Checking for Equality - Code Sample:

SimpleSelects/Demos/Where-Equal.sql
/*
Create a report showing the title and the first and last name
of all sales representatives.
*/

SELECT Title, FirstName, LastName

FROM Employees
WHERE Title = 'Sales Representative';

The above SELECT statement will return the following results:

Working With Transact Sql

What is the difference between SQL, PL-SQL and T-SQL?

Can anyone explain what the differences between these three are, and provide scenarios where
each would be relevantly used?

 SQL is a query language to operate on sets.

It is more or less standardized, and used by almost all relational database management
systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.

 PL/SQL is a proprietary procedural language used by Oracle

 PL/pgSQL is a procedural language used by PostgreSQL
 TSQL is a proprietary procedural language used by Microsoft in SQL Server.

Procedural languages are designed to extend SQL's abilities while being able to integrate well
with SQL. Several features such as local variables and string/data processing are added. These
features make the language Turing-complete.

They are also used to write stored procedures: pieces of code residing on the server to manage
complex business rules that are hard or impossible to manage with pure set-based operations.
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”
Chinese Proverb

To create a database with T-SQL

1. In a Query Editor window, type but do not execute the following code:

 CREATE DATABASE TestData

GO

 Use the pointer to select the words CREATE DATABASE, and then press F1. The CREATE
DATABASE topic in SQL Server Books Online should open. You can use this technique to find
the complete syntax for CREATE DATABASE and for the other statements that are used in this
tutorial.

 In Query Editor, press F5 to execute the statement and create a database named TestData.

To create a database to contain the new table

Enter the following code into a Query Editor window.

USE master;
GO

--Delete the TestData database if it exists.

IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
DROP DATABASE TestData;
END

--Create a new database called TestData.

CREATE DATABASE TestData;
Press the F5 key to execute the code and create the database.

Switch the Query Editor connection to the TestData database

In a Query Editor window, type and execute the following code to change your connection to
the TestData database.

USE TestData
GO

To create a table

 In a Query Editor window, type and execute the following code to create a simple table
named Products. The columns in the table are named ProductID, ProductName, Price,
and ProductDescription. The ProductID column is the primary key of the table. int,
varchar(25), money, and text are all data types. Only the Price and
ProductionDescription columns can have no data when a row is inserted or changed.
This statement contains an optional element (dbo.) called a schema. The schema is the
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”
Chinese Proverb

database object that owns the table. If you are an administrator, dbo is the default schema.
dbo stands for database owner.

CREATE TABLE dbo.Products

(ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(25) NOT NULL,
Price money NULL,
ProductDescription text NULL)
GO

Now that you have created the Products table, you are ready to insert data into the table by using
the INSERT statement. After the data is inserted, you will change the content of a row by using
an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict
the update to a single row. The four statements will enter the following data.

ProductID ProductName Price ProductDescription

1 Clamp 12.48 Workbench clamp
50 Screwdriver 3.17 Flat head
75 Tire Bar \Tool for changing tires.
3000 3mm Bracket .52

The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to
be inserted. The two hyphens in front of a line indicate that the line is a comment and the text
will be ignored by the compiler. In this case, the comment describes a permissible variation of
the syntax.

To insert data into a table

1. Execute the following statement to insert a row into the Products table that was created
in the previous task. This is the basic syntax.

 -- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO

 The following statement shows how you can change the order in which the parameters are
provided by switching the placement of the ProductID and ProductName in both the field list (in
parentheses) and in the values list.

 -- Changing the order of the columns

INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”
Chinese Proverb

 The following statement demonstrates that the names of the columns are optional, as long as
the values are listed in the correct order. This syntax is common but is not recommended because
it might be harder for others to understand your code. NULL is specified for the Price column
because the price for this product is not yet known.

 -- Skipping the column list, but keeping the values in order

INSERT dbo.Products
VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
GO

 The schema name is optional as long as you are accessing and changing a table in your
default schema. Because the ProductDescription column allows null values and no value is
being provided, the ProductDescription column name and value can be dropped from the
statement completely.

4. -- Dropping the optional dbo and dropping the ProductDescription column

5. INSERT Products (ProductID, ProductName, Price)
6. VALUES (3000, '3mm Bracket', .52)
7. GO

To update the products table

1. Type and execute the following UPDATE statement to change the ProductName of the
second product from Screwdriver, to Flat Head Screwdriver.

UPDATE dbo.Products
SET ProductName = 'Flat Head Screwdriver'
WHERE ProductID = 50
GO

To read the data in a table

1. Type and execute the following statements to read the data in the Products table.

 -- The basic syntax for reading data from a single table

SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
GO

 You can use an asterisk to select all the columns in the table. This is often used in ad hoc
queries. You should provide the column list in you permanent code so that the statement will
return the predicted columns, even if a new column is added to the table later.

 -- Returns all columns in the table

-- Does not use the optional schema, dbo
SELECT * FROM Products
GO
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”
Chinese Proverb

 You can omit columns that you do not want to return. The columns will be returned in the
order that they are listed.

 -- Returns only two of the columns from the table

SELECT ProductName, Price
FROM dbo.Products
GO

 Use a WHERE clause to limit the rows that are returned to the user.

 -- Returns only two of the records in the table

SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
WHERE ProductID < 60
GO

 You can work with the values in the columns as they are returned. The following example
performs a mathematical operation on the Price column. Columns that have been changed in
this way will not have a name unless you provide one by using the AS keyword.

-- Returns ProductName and the Price including a 7% tax

-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays
FROM dbo.Products
GO