SQL is a term quite often heard in IT sector. If you browse the internet, you will find a good number of SQL tutorials also. This article is meant for spreading light on SQL and SQL tutorial.
Introduction to SQL
SQL or structured Query Language is used to communicate with database and is used to manipulate database. As per the ANSI (American National Standards Institute) norms, it is the standard language for relational database management system or RDBMS. There are different versions of SQL and they all support the major commands such as SELECT, UPDATE, DELETE, INSERT AND WHERE in the same manner. SQL can perform the following actions.
- Executing queries against a database
- Retrieval of data
- Inserting records in a database
- Updating records in database
- Deleting records in database
- Creating new records in database
- Creating new database
- Creating stored procedures in database
- Creating views in database
- Setting permission on tables, procedures and views
A database contains one or more tables and each table will have a name of it’s own and each table will have rows containing data.
SQL statements are used to perform most of the actions done on database. SQL statements are not case sensitive. For example writing “SELECT” and “select” is one and the same. Some database systems need a semicolon after the SQL statements. In standard conditions, semicolon is used to separate SQL statements when a database needs more than one statement.
As the significance of SQL increased the demand for experts in SQL has also increased and this resulted in the mushrooming of SQL tutorials. Although the syllabi may change a bit depending on the priorities of the trainers, the core of the course will be one and the same. Most of the professional trainers follow the below given pattern of course contents.
Chapter 1 : SQL Overview
- 1.1 Outlining SQL as the cornerstone of database activity.
- 1.2 Applying the ANSI/ISO standards.
- 1.3 Describing the fundamental building blocks: tables, columns, primary keys and foreign keys.
Chapter 2 : Building the Database Schema
- 2.1 Creating tables and columns.
- 2.2 Building tables with CREATE TABLE.
- 2.3 Modifying table structure with ALTER TABLE.
- 2.4 Adding columns to an existing table.
- 2.5 Removing tables with DROP TABLE.
Chapter 3 : Protecting data integrity with constraints
- 3.1 Guaranteeing uniqueness with primary key constraints.
- 3.2 Enforcing integrity with foreign key constraints.
- 3.3 Imposing business rules with check constraints.
Chapter 4 : Improving performance with indexes
- 4.1 Expediting data retrieval with indexes.
- 4.2 Recommending guidelines for index creation.
Chapter 5 : Manipulating Data
- 5.1 Modifying table contents.
- 5.2 Adding table rows with INSERT.
- 5.3 Changing row content with UPDATE.
- 5.4 Removing rows with DELETE.
Chapter 6 : Applying transactions
- 6.1 Controlling transactions with COMMIT and ROLLBACK.
- 6.2 Deploying BEGIN TRANSACTION in SQL Server.
Chapter 7 : Working with the SELECT Statement
- 7.1 Writing Single Table queries.
- 7.2 Retrieving data with SELECT.
- 7.3 Specifying column expressions.
- 7.4 Sorting the result with ORDER BY.
- 7.5 Handling NULL values in expressions.
Chapter 8 : Restricting rows with the WHERE filter
- 8.1 Testing for equality or inequality.
- 8.2 Applying wildcard characters.
- 8.3 Avoiding NULL value pitfalls.
Chapter 9 : Querying Multiple Tables
- 9.1 Applying the ANSI/ISO standard join syntax.
- 9.2 Matching related rows with INNER JOIN.
- 9.3 Including nonattached rows with OUTER JOIN.
- 9.4 Creating a Cartesian product with CROSS JOIN.
Chapter 10 : Combining results with set operators
- 10.1 Stacking results with UNION.
- 10.2 Identifying matching rows with INTERSECT.
- 10.3 Utilizing EXCEPT to find non matching rows.
Chapter 11 : Employing Functions in Data Retrieval
Chapter 12 : Processing data with row functions
- 12.1 Conditional formatting with the CASE expression.
- 12.2 Utilizing the CASE expression to simulate IF tests.
- 12.3 Dealing with NULL values.
Chapter 13 : Performing analysis with aggregate functions
- 13.1 Summarizing data using SUM, AVG and COUNT.
- 13.2 Finding the highest/lowest values with MAX and MIN.
- 13.3 Defining the summary level with GROUP BY.
- 13.4 Applying filter conditions with HAVING.
Chapter 14 : Constructing Nested Queries
- 14.1 Applying subqueries in filter conditions.
- 14.2 Correlated vs. uncorrelated subqueries.
- 14.3 Testing the existence of rows.
- 14.4 Including subqueries in expressions.
- 14.5 Placing subqueries in the column list.
- 14.6 Creating complex expressions containing subqueries.
- 14.7 Handling subqueries that return no rows.
Chapter 15 : Developing In-Line and Stored Views
- 15.1 Breaking down complex problems.
- 15.2 Selecting data from a query result set.
- 15.3 Subqueries in the FROM clause.
- 15.4 Creating views in a database.
- 15.5 Materialized Views.