Structured Query Language (SQL) Programming and Database Design

Databases drive nearly every Internet and Intranet Web site. Global IT’s Introduction to SQL and Database Design class teaches you how to master their power through a thorough grounding in SQL and sound database design fundamentals.

This course is designed for people with no relational database experience. The database that we use for the class is MySQL, an open source product that is gaining in popularity in the industry and SQL Server. However, the concepts are basic enough that they transfer easily to any standard database product that abides by the ANSI standards.

SQL (Structured Query Language) is used to retrieve, edit, remove and add data in relational databases. SQL programmers define and create relational database structures including tables, views etc.Good SQL skills are always in demand and are useful in a number of careers including database developers, report writers, business analysts, computer programmers, web developers, testers, data analysts, etc. Because an increasing number of companies are storing and analyzing an ever increasing amount of corporate data, the demand for SQL or database skills will continue to increase.

SQL has a shorter learning curve when compared to other skills like business analysis or computer programming or testing. Part of the reason is that SQL is a well-defined, standardized language that has been around for a number of years.

Database Basics

• What is a database?
• How databases store information
• Relational databases
• Different types of databases
• Normalization
• Installing MySQL 5.0 and SQL Server 2005 Express Editions

Database Design Fundamentals

• Entity, attribute, and relationship
• Understanding Primary and Foreign Keys
• Designing a database to satisfy a set of requirements
• Create an entity-relationship diagram to represent the design of a database

Creating Tables

• Create and run SQL commands
• How to create a table
• Data Types
• Adding and viewing data in tables

Single Table Queries

• Retrieve data from a database using SQL commands
• Use simple and compound conditions in queries
• Use the BETWEEN, LIKE, and IN operators in queries
• Use computed columns in queries
• Sort data using the ORDER BY clause
• Sort data using multiple keys and in ascending and descending order
• Use aggregate functions in a query
• Use subqueries
• Group data using the GROUP BY clause
• Select individual groups of data using the HAVING clause
• Retrieve columns with null values

Multiple-Table Queries

• Use joins to retrieve data from more than one table
• Use the IN and EXISTS operators to query multiple tables
• Use a sub query with a sub query
• Use an alias
• Join a table to itself
• Perform set operations (union, intersection, and difference)
• Use the ALL and ANY operators in a query
• Perform special operations (inner join, outer join, and product)

Updating Data

• Create a new table from an existing table
• Change data using the UPDATE command
• Add new data using the INSERT command
• Delete data using the DELETE command
• Use nulls in UPDATE commands
• Change the structure of an existing table
• Use the COMMIT and ROLLBACK commands to make permanent data
• updates or to reverse updates
• Understand transactions and the role of COMMIT and ROLLBACK in supporting transactions
• Drop a table

Database Administration

• Understand, create, and drop views
• Recognize the benefits of using views
• Use a view to update data
• Grant and revoke users’ database privileges
• Understand the purpose, advantages, and disadvantages of using an index
• Create, use, and drop an index
• Understand and obtain information from the system catalog
• Use integrity constraints to control data entry

SQL Functions and Procedures

• Understand how to use functions in queries
• Use the UPPER and LOWER functions with character data
• Use the ROUND and FLOOR functions with numeric data
• Add a specific number of months or days to a date
• Calculate the number of days between two dates
• Use concatenation in a query

Embed SQL commands in PL/SQL and T-SQL procedures

• Retrieve single rows using embedded SQL
• Update a table using embedded INSERT, UPDATE, and DELETE commands
• Use cursors to retrieve multiple rows in embedded SQL
• Manage errors in procedures containing embedded SQL commands
• Use SQL in a language that does not support embedded SQL commands
• Use triggers