Learn SQL with this video training course from TeachUcomp, Inc. Mastering Introductory SQL Made Easy features 43 video lessons with over 3 hours of expert instruction. SQL is a standardized programming language that is used to create, edit and delete databases and database objects. It is also the language that is used to extract, add, update, and delete data within a database. SQL is used in nearly every aspect of database interactions. Watch, listen and learn as your instructor guides you through the “core,” or essential, statements within SQL. Variations of the core statements for the specific database systems of MySQL 5.7, SQL Server 2012, and Access 2013 will also be referenced by hyperlinks to the associated online documentation for each system. The goal of this course to give the student the knowledge of which SQL statement they will need to use to accomplish specific tasks within a database, as well as provide links to database-specific implementations of those core statements. Reinforce your learning with the text of our printable classroom instruction manual, additional images and practice exercises.
Whether you are completely new to SQL or are simply in need of a refresher or excellent reference source, this course will empower you with the knowledge and skills necessary to be a proficient user. We have incorporated years of classroom training experience and teaching techniques to develop an easy-to-use course that you can customize to meet your personal learning needs. Simply launch the easy-to-use interface, click to start a video lesson or open the manual and you are on your way to mastering SQL.
SQL, an acronym that stands for Structured Query Language, is a standards-based language used within a relational database management system (RDBMS) A relational database management system is software that stores and manages data. Some relational database management systems are MySQL, SQL Server, Access, and SQLite, among others. Learn this and more during this lecture.
In the previous lecture, the term relational database was used. So what does the term relational mean, and how is this important? The term relational describes the method used for storing data within the database tables. However, it may be easier to understand the relational model of data storage by contrasting it with another method of storage that you may be more familiar with: the ‘flat-file’ method. Learn this and more during this lecture.
The relational model of data storage allows you to more easily and effectively model a complex entity or subject, like sales. The relational model of data storage eliminates redundant data entry and also creates less data to store, making the relational database model smaller and faster than the ‘flat-file.’ Learn this and more during this lecture.
While there are no “hard and fast” rules about creating relational database tables, there are a few tips that you should try to follow when beginning database design. First, examine all current documentation used to collect and store the information that you now want to store in the new database. This step ensures that when you are creating your data tables and performing your data modeling, you won’t leave out a critical part of your database. Doing that often leads to frustrating periods of re-design. Learn this and more during this lecture.
SQL, pronounced as the individual letters “S” “Q” “L” or often “sequel,” is the language used to manage and interact with all relational database management systems. While the specific implementation of the language may vary from vendor to vendor within database management software, SQL is designed as a standardized language that is supposed to implement some basic functionality within these programs. The standard functions include running queries on data, modifying the data within the tables, displaying views of the data, and creating and modifying database structures and data access. Learn this and more during this lecture.
This tutorial references using SQL in Access 2013. Access 2013 is a RDBMS that creates self-contained databases and provides visual tools to allow users to make relational databases without the need for SQL. As such, there are few places to use SQL in Access. You can enter SQL into the “SQL View” of a query when creating a query in Access. You can also enter SQL into modules you design or into any “Code Builder” attached to form objects within a database. Learn this and more during this lecture.
The first statement that you will learn in SQL is the CREATE statement. The CREATE statement is often the first statement that you will execute in SQL if you are using SQL to design a database within a relational database management system. Learn this and more during this lecture.
Within many relational database management systems, the CREATE DATABASE statement is used to create the database. Learn this and more during this lecture.
One of the most commonly used SQL statements when creating a database is the CREATE TABLE statement, which is used to create tables within a database. Learn this and more during this lecture.
Within a database, indexes are a way of sorting a table by values contained in one or more fields. The advantage to creating indexes is that they may speed up query processing when used. However, as the user of the database management system, most of the time you can only choose to create indexes within the tables, not when they will be used. Learn this and more during this lecture.
When creating tables in SQL using the CREATE TABLE statement, you can often set various types of constraints on the fields that are created. A constraint is a limitation that is placed upon the allowable values within a field. If a user attempts to add data to the table that violates the constraint, the data will not be added to the table. Learn this and more during this lecture.
The DROP statement is used within SQL to delete created database objects. You can use this statement to delete databases, tables and indexes. You can also use it in conjunction with the ALTER TABLE statement to delete fields from tables. Note that this is NOT the statement used to delete specified data from tables. Learn this and more during this lecture.
You can use the ALTER TABLE statement in SQL to modify the structure of an existing table in your database. You may do this to add SQL constraints to the tables or fields in the table if you did not do them when initially creating the table. Some relational database management systems may even allow you to use the ALTER TABLE statement to assign a PRIMARY KEY constraint using the ALTER TABLE statement, although that is much more likely to be required within the CREATE TABLE statement in most systems. You can also use this statement to add, edit, or delete fields within a table, if needed. Learn this and more during this lecture.
When creating tables in SQL, you will need to deal with NULL values. A NULL value is a value that is unknown. These values can occur within database tables when a user who is performing data entry skips entering a value into a field. When a value is not entered, it is said to have a NULL value. Note that a NULL value is not equal to anything, as the value is unknown. Learn this and more during this lecture.
When creating table fields in SQL, you must assign each field a data type. However, the data types available to use will vary for each relational database management system you encounter. In this lecture, you will examine some of the most commonly encountered general data types in SQL. Learn this and more during this lecture.
Many times when creating a primary key field within a table, you will assign the field a numeric data type. You can use the “Auto-Increment” feature when creating this field in many relational database systems to automatically increase the increment used within this numeric field to ensure that each record entered will receive its own unique identification number. However, the implementation of this feature varies widely amongst the individual relational database management systems. Learn this and more during this lecture.
After examining the core SQL statements used to create objects within relational database management systems, you should next learn how to add, edit, delete and select data within these same systems. SQL uses the Data Manipulation Language statements to accomplish these tasks. The first statement to learn is the INSERT statement. This statement is used to insert new records into a table. Learn this and more during this lecture.
After you have data within the tables in your database, you will often need to update those records. SQL uses the UPDATE statement to update field data within specified records in a table. Learn this and more during this lecture.
The DELETE statement is used to remove specified records from a table. Like the UPDATE statement, it includes a WHERE clause that you should ensure is correct before executing the DELETE statement. If you do not specify a WHERE clause within the DELETE statement, you will delete ALL the records within the table! Learn this and more during this lecture.
You use the SELECT statement in SQL to choose specific records to view from a table, or from related tables, within a temporary table called a result set. This tutorial will start the examination of the SELECT statement by looking at the SELECT statement in its simplest form when it is used to select all of the records from a single table. Learn this and more during this lecture.
When used within the SELECT statement, the WHERE clause will determine which records you want to select from a specified table or tables. It does this by selecting records from a table where the values within a field you specify match a criteria you specify. Many times, the criteria will be matching, or “equal to,” a field value. Note, however, that this will not necessarily be the case in all situations. Learn this and more during this lecture.
You should also ensure that the criteria value you specify matches the data type of the field within which you are searching for that value when you enter a criteria value to find within a field in the WHERE clause. Learn this and more during this lecture.
When viewing the result set of a SELECT statement in SQL, the records will be displayed in the order that they were selected from the table. This means that, by default, they will not be displayed in any particular order within the result set. You use the ORDER BY clause within the SELECT statement to sort the records selected within the result set. Learn this and more during this lecture.
You will often want to perform some type of function on the records selected within a SELECT statement. For example, you may want to count the number of records selected. Likewise, you may want to count sets of records within a result set. For example, you may want to count the number of records within a table by state, or by country. You use aggregate functions to perform these tasks in SQL. A function is simply a type of mathematical or computational operation, such as COUNT, SUM, or AVERAGE, for example. The term aggregate means a whole or total composed of separate parts. Learn this and more during this lecture.
The next aspect of the SELECT statement to discuss is the JOIN clause. Joins between two tables occur when the values within a PRIMARY KEY field of one table are linked to values within the FOREIGN KEY field of another table. Creating joins within the SELECT statement allows the user to access data from multiple tables within a single result set, and is one of the primary functions of a relational database. Learn this and more during this lecture.
The UNION operator allows you to combine the result sets of two or more individual SELECT statements into a single result set. Note that the UNION operator is not a JOIN, but rather a way to combine the data within two result sets into the same fields in single result set. Because of this, a UNION operation can only combine two SELECT statements with the same number of fields. Learn this and more during this lecture.
The SELECT INTO statement in SQL is used to copy the result set of a SELECT statement into a new table that is created by the SELECT INTO statement. Learn this and more during this lecture.
You can use the INSERT INTO SELECT statement to copy records from one table with a SELECT statement, and then append them into another existing table. When you copy the records from one table into another by using the INSERT INTO SELECT statement, the existing records within those tables will not be affected. Learn this and more during this lecture.
You can nest a query within another query to create a subquery. A subquery is simply a query that is placed within another query. Subqueries can be nested inside of many types of DML statements, such as the standard SELECT statement, a SELECT INTO statement, an INSERT INTO statement, an UPDATE statement, and the DELETE statement. You can also nest subqueries within other subqueries. However, there is often a limitation on the number of nested subqueries allowed by your RDBMS. Learn this and more during this lecture.
Data Control Language (DCL) is used within SQL to grant or deny privileges, which are permissions to perform specific tasks within the database, to users. This lets the database administrator or owner choose which users can perform which actions upon selected database objects. Note that not every RDBMS will fully implement DCL within their SQL. Some database systems, like SQLite, do not implement any DCL statements, as it is a single-user desktop database system. Other database programs, like Microsoft Access 2013, use a separate security mechanism to determine user access and privileges not tied to SQL. You must check your RDBMS documentation to find out the level of DCL implementation that exists within its SQL, if at all. Learn this and more during this lecture.
Privileges provide various levels of permission to create, edit, and delete database objects. The exact types of permissions that can be granted will vary depending upon the relational database management system that you are using. Learn this and more during this lecture.
The GRANT statement is a DCL statement used by the database administrator or owner to grant privileges to other database users and roles. Learn this and more during this lecture.
The REVOKE statement is a DCL statement used by the database administrator or owner to revoke privileges from database users and roles. Learn this and more during this lecture.
You can use the ALTER USER statement in SQL to alter information about a user, which may include the user’s database password, schema or database access, and login information. Note that this statement is technically classified as Data Definition Language (DDL), as it defines the objects within the database. It is only included within this chapter because of its use in conjunction with the DCL statements. Learn this and more during this lecture.
You can use the DROP USER statement in SQL to delete a user from the database. Note that this statement is technically classified as Data Definition Language (DDL), as it defines the objects within the database. It only included within this chapter because of its use in conjunction with the DCL statements. Learn this and more during this lecture.
The Transaction Control Language (TCL) statements within SQL are the statements used to control a set of Data Manipulation Language (DML) statements that are executed as a single unit called a transaction. TCL ensures that either all of the changes made by the DML statements within the transaction are committed to the database or none of them are. This can help to prevent partial changes from a series of statements from being accomplished. Learn this and more during this lecture.
Functions serve a wide range of purposes within SQL. In lecture “3.8- The GROUP BY Clause and Aggregate Functions,” you learned how you can use aggregate functions to perform calculations on grouped values within a query. That is one very common use of functions within SQL. However, there are other types of functions that can serve other purposes within SQL, as well. In this lecture, you will learn about the various types of functions within SQL and how they can be used within queries. Learn this and more during this lecture.
You can use scalar functions within SQL to create calculated fields within SELECT statements. This allows you to display the result of a calculation within a field in the result set of a query. The calculation can refer to columns within the tables selected in the FROM clause of the SELECT statement. When you create calculated fields, you may want to give the calculated field, also called a “calculated column,” its own name so that the user will not see the calculated expression as the field name within the result set. Learn this and more during this lecture.
You can use table aliases in SQL to make referencing table names within complex SQL statements easier. For example, if you have a “CustomerID” field within a “Customers” table and also have a “CustomerID” field within a “Sales” table, you must use the dot notation reference of table_name.field_name for the “CustomerID” field within the SQL statement so that the database will know which “CustomerID” field you are referencing within the SQL statement. Learn this and more during this lecture.
A view is a virtual table that is based on the results of a SELECT statement. They are very much like the result set of a query. They always display the most recent data from the underlying tables from which they are constructed because whenever a user queries a view, most RDBMS will recreate the view from the SELECT statement upon which the view is based. Learn this and more during this lecture.
You use the CREATE VIEW statement to create a view of the data within your base tables as a separate virtual table within your database. Learn this and more during this lecture.
You can redefine the display of data within a named view by issuing the ALTER VIEW statement in SQL. The syntax of this statement is almost exactly the same as the CREATE VIEW statement. Learn this and more during this lecture.
The DROP VIEW statement is used to remove a named view from a database. Learn this and more during this lecture.
This lecture provides a brief summary of the topics covered throughout the course and offers suggestions for further reading and learning materials.
Introductory SQL Manual. Plus practice exercises and keyboard shortcuts.
Founded in 2001, TeachUcomp, Inc. began as a licensed software training center in Holt, Michigan - providing instructor-led, classroom-style instruction in over 85 different classes, including Microsoft Office, QuickBooks, Peachtree and web design, teaching staff at organizations such as the American Red Cross, Public School Systems and the Small Business Association.
At TeachUcomp, Inc., we realize that small business software can be confusing, to say the least. However, finding quality training can be a challenge. TeachUcomp, Inc. has changed all that. As the industry leader in training small business software, TeachUcomp, Inc. has revolutionized computer training and will teach you the skills to become a powerful and proficient user.
In 2002, responding to the demand for high-quality training materials that provide more flexibility than classroom training, TeachUcomp, Inc. launched our first product - Mastering QuickBooks Made Easy. The enormous success of our first tutorial led to an ever-expanding product line. TeachUcomp, Inc. now proudly serves customers in over 80 different countries world-wide including individuals, small businesses, non-profits and many others. Clients include the Transportation Security Administration, NASA, Smithsonian Institution, University of Michigan, Merrill Lynch, Sprint, U.S. Army, Oracle Corporation, Hewlett-Packard and the U.S. Senate.
Our full-time staff of software training professionals have developed a product line that is the perfect solution for busy individuals. Our comprehensive tutorials cover all of the same material as our classroom trainings. Broken into individual lessons, you can target your training to meet your needs - choosing just the lessons you want (and having the option to watch them all if you like). Our tutorials are also incredibly easy to use.
You will listen and watch as our expert instructors walk you through each lesson step-by-step. Our tutorials also feature the same instruction manuals (in PDF) that our classroom students receive - and include practice exercises and keyboard shortcuts. You will see each function performed just as if the instructor were at your computer. After the lesson has finished, you then "toggle" into the application and practice what you've learned - making it the most effective interactive training solution to learn on your own.