Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
Introduction to SQL
Have you found yourself interested in learning all of the basics of writing SQL queries? We have the course for you. Designed by Webucator, a global training partner, this course covers all of the best activities to get you started. And it's packed with readings, trainer presentations, and exercises!
This SQL training course is designed for students new to writing SQL queries. A typical student will need to learn SQL to build applications or to generate business reports.
For the most part, the SQL learned in the course is applicable to all major databases. In the few cases in which there are differences between the databases, these differences will be pointed out.
This engaging course also comes with template exercise files ready to start using right away. To download, visit: http://www.webucator.com/portal/wiki/index.cfm/SQL101.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Relational Database Basics|
|Brief History of SQL In 1970, Dr. E.F. Codd published "A Relational Model of Data for Large Shared Data Banks," an article that outlined a model for storing and manipulating data using tables. Shortly after Codd's article was published, IBM began working on creating a relational database. Between 19…|
|Relational Databases A relational database at its simplest is a set of tables used for storing data. Each table has a unique name and may relate to one or more other tables in the database through common values. Tables A table in a database is a collection of rows and columns. Tables are also known…|
|Popular Databases Commercial Databases Oracle Oracle is the most popular relational database. It runs on both Unix and Windows. It used to be many times more expensive than SQL Server and DB2, but it has come down a lot in price. SQL Server SQL Server is Microsoft's database and, not surprisingly, o…|
|SQL Statements Database Manipulation Language (DML) DML statements are used to work with data in an existing database. The most common DML statements are: SELECT INSERT UPDATE DELETE Database Definition Language (DDL) DDL statements are used to structure objects in a database. The most common DDL st…|
Relational Database Concepts
|Section 2: Simple SELECTs|
|Introduction to the Northwind Database The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server and Microsoft Access. The database contains the sales data for Northwind Traders, a fictitious specialty foods export-import…|
|Exploring the Tables Duration: 10 to 20 minutes. In this exercise, you will explore all the data in the Northwind database by selecting all the rows of each of the tables. Select all columns of all rows from the tables below. The number of records that should be returned is indicated in parentheses…|
|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: SimpleSelects/Demos/SelectCols.sql /* Select the FirstName and La…|
Using SELECT Demo
|SELECTing Specific Columns Duration: 5 to 15 minutes. In this exercise, you will practice selecting specific columns from tables in the Northwind database. Select CategoryName and Description from the Categories table. Select ContactName , CompanyName , ContactTitle and Phone from the C…|
|Sorting Records The ORDER BY clause of the SELECT statement is used to sort records. Sorting By a Single Column To sort by a single column, simply name that column in the ORDER BY clause. Syntax SELECT column, column FROM table ORDER BY column; Note that columns in the ORDER BY clause do not…|
|Sorting Results Duration: 5 to 15 minutes. In this exercise, you will practice sorting results in SELECT statements. Select CategoryName and Description from the Categories table sorted by CategoryName . Select ContactName , CompanyName , ContactTitle , and Phone from the Customers t…|
|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 o…|
WHERE Clause Demo
|Using the WHERE clause to check for equality or inequality Duration: 5 to 15 minutes. In this exercise, you will practice using the WHERE clause to check for equality and inequality. Create a report showing all the company names and contact names of Northwind's customers in Buenos Aires. Create a…|
|Using the WHERE clause to check for greater or less than Duration: 5 to 15 minutes. In this exercise, you will practice using the WHERE clause to check for values greater than or less than a specified value. Create a report that shows the employee id, order id, customer id, required date, and ship…|
|Checking for NULL Duration: 5 to 15 minutes. In this exercise, you will practice selecting records with fields that have NULL values. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Create a report that shows the first and last name…|
|Using WHERE and ORDER BY Together Duration: 5 to 15 minutes. In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY . Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name. C…|
|The WHERE Clause and Operator Words The following table shows the word operators used in WHERE conditions. SQL Word Operators Operator Description BETWEEN Returns values in an inclusive range IN Returns values in a specified subset LIKE Returns values that match a simple pattern NOT Negates an opera…|
WHERE Clause Operators Demo
|More SELECTs with WHERE Duration: 5 to 15 minutes. In this exercise, you will practice writing SELECT statements that use WHERE with word operators. Create a report that shows the first and last names and birth date of all employees born in the 1950s. Create a report that shows the product name…|
|Checking Multiple Conditions AND AND can be used in a WHERE clause to find records that match more than one condition. Code Sample: SimpleSelects/Demos/Where-And.sql /* Create a report showing the first and last name of all sales representatives whose title of courtesy is "Mr.". */ SELECT FirstNa…|
Multiple Conditions Demo
|Writing SELECTs with Multiple Conditions Duration: 5 to 15 minutes. In this exercise, you will practice writing SELECT statements that filter records based on multiple conditions. Create a report that shows the first and last names and cities of employees from cities other than Seattle in the stat…|
|Section 3: Advanced SELECTs|
|Calculated Fields Calculated fields are fields that do not exist in a table, but are created in the SELECT statement. For example, you might want to create FullName from FirstName and LastName . Concatenation Concatenation is a fancy word for stringing together different words or characters.…|
Calculated Fields Demo
|Calculating Fields Duration: 10 to 20 minutes. In this exercise, you will practice writing SELECT statements with calculated fields. Create a report that shows the unit price, quantity, discount, and the calculated total price using these three fields. Note for SQL Server users only: You will be u…|
|Aggregate Functions and Grouping Aggregate Functions Aggregate functions are used to calculate results using field values from multiple records. There are five common aggregate functions. Common Aggregate Functions Aggregate Function Description COUNT() Returns the number of rows containing non-NULL…|
Aggregate Functions Demo
|Working with Aggregate Functions Duration: 10 to 20 minutes. In this exercise, you will practice working with aggregate functions. Create a report that returns the following from the Order_Details table. The report should only return rows for which TotalUnits is less than 200. 2. Create a report t…|
|Built-in Data Manipulation Functions In this section, we will discuss some of the more common built-in data manipulation functions. Unfortunately, the functions differ greatly between databases, so you should be sure to check your database documentation when using these functions. The tables below s…|
Data Manipulation Demo
|Data Manipulation Functions Duration: 10 to 20 minutes. In this exercise, you will practice using data manipulation functions. Create a report that shows the units in stock, unit price, the total price value of all units in stock, the total price value of all units in stock rounded down, and the tot…|
|Section 4: Subqueries, Joins and Unions|
|Subqueries Subqueries are queries embedded in queries. They are used to retrieve data from one table based on data in another table. They generally are used when tables have some kind of relationship. For example, in the Northwind database, the Orders table has a CustomerID field, which referenc…|
|Subqueries Duration: 20 to 30 minutes. In this exercise, you will practice writing subqueries. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders. You will need to escape the apostrophe in "Grandma Kel…|
|Joins How can we find out Which products are provided by which suppliers? Which customers placed which orders? Which customers are buying which products? Such reports require data from multiple tables. Enter joins. Syntax SELECT table1.column, table2.column FROM table1 JOIN table2 ON (table1.column=…|
Using Joins Demo
|Using Joins Duration: 25 to 40 minutes. In this exercise, you will practice using joins. 1. Create a report that shows the order ids and the associated employee names for orders that shipped after the required date. It should return the following. There should be 37 rows returned. 2. Create a report…|
|Outer Joins So far, all the joins we have worked with are inner joins, meaning that rows are only returned that have matches in both tables. For example, when doing an inner join between the Employees table and the Orders table, only employees that have matching orders and orders that have match…|
|Unions Unions are used to retrieve records from multiple tables or to get multiple record sets from a single table. Code Sample: SubqueriesJoinsUnions/Demos/Unions.sql /* Get the phone numbers of all shippers, customers, and suppliers */ SELECT CompanyName, Phone FROM Shippers UNION SELECT CompanyNa…|
Outer Joins and Unions
|Working with Unions Duration: 10 to 20 minutes. In this exercise, you will practice using UNION . Create a report showing the contact name and phone numbers for all employees, customers, and suppliers. Solution: SubqueriesJoinsUnions/Solutions/Unions.sql /****************************** SQL Server S…|
|Section 5: Conditional Processing with CASE|
|Using CASE CASE functions contain one or more WHEN clauses as shown below. Syntax --OPTION 1 SELECT CASE column WHEN VALUE THEN RETURN_VALUE WHEN VALUE THEN RETURN_VALUE WHEN VALUE THEN RETURN_VALUE WHEN VALUE THEN RETURN_VALUE ELSE RETURN_VALUE END AS ColumnName FROM table --OPTION 2 SELECT CASE…|
Using Case Demo
|Working with CASE Duration: 10 to 15 minutes. In this exercise you will practice using CASE . Create a report that shows the company names and faxes for all customers. If the customer doesn't have a fax, the report should show "No Fax" in that field as shown below. Solution: Case/Solutions/Case.sql…|
|Section 6: Inserting, Updating and Deleting Records|
|INSERT To insert a record into a table, you must specify values for all fields that do not have default values and cannot be NULL . Syntax INSERT INTO table (columns) VALUES (values); The second line of the above statement can be excluded if all required columns are inserted and the values are list…|
|Inserting Records Duration: 5 to 15 minutes. In this exercise, you will practice inserting records. Insert yourself into the Employees table. Include the following fields: LastName , FirstName , Title , TitleOfCourtesy , BirthDate , HireDate , City , Region , PostalCode , Country , Home…|
|UPDATE The UPDATE statement allows you to update one or more fields for any number of records in a table. You must be very careful not to update more records than you intend to! Syntax UPDATE table SET field = value, field = value, field = value WHERE conditions; Code Sample: InsertsUpdatesDelete…|
UPDATE and DELETE Demo
|Updating and Deleting Records Duration: 5 to 15 minutes. In this exercise, you will practice updating and deleting records. Update your record in the Employees table to include some Notes . Raise the unit price of all products in the Products table by 10% for all products that are out of stock.…|