SQL - New Wings

About the course 

You will learn how to both read and write complex SQL queries in a relational database (SQL Server). The skills you will learn are also largely applicable to any other major database system, such as MySQL, PostgreSQL, Oracle Database, and much more. 

Although we use SQL Server to interrogate data throughout this course, SQL is a common language easily adapted to other RDBMS (relational database management systems) such as MySQL, IBM DB2, PostgreSQL, Oracle, Ingres, Sybase and Microsoft Access. 

Why Learn SQL? 

SQL queries, which are essentially requests or instructions that you send to a database, allow you to retrieve information and update, insert, or delete data. SQL is mainly associated with the IT sector and is an everyday instrument for database administrators. Developers use SQL to write an application that requires database connector, and systems architects use it to design database models. All of this mean value that encyclopedism SQL is a great choice for anyone who would like to pursue a career in IT. 


1: SQL Overview 

Outlining SQL as the cornerstone of database activity 

Applying the ANSI/ISO standards 

Describing the fundamental building blocks: tables, columns, primary keys, and foreign keys ∙ 

2: Building the Database Schema 

Creating tables and columns 

Building tables with CREATE TABLE 

Modifying table structure with ALTER TABLE 

Adding columns to an existing table 

Removing tables with DROP TABLE

Protecting data integrity with constraints 

Guaranteeing uniqueness with primary key constraints Enforcing integrity with foreign key constraints Imposing business rules with check constraints Improving performance with indexes 

Expediting data retrieval with indexes 

Recommending guidelines for index creation 

3: Manipulating Data 

Modifying table contents 

Adding table rows with INSERT 

Changing row content with UPDATE 

Removing rows with DELETE 

Applying transactions 

Controlling transactions with COMMIT and ROLLBACK Deploying BEGIN TRANSACTION in SQL Server 

4: Working with the SELECT Statement

Writing Single Table queries 

Retrieving data with SELECT 

Specifying column expressions 

Sorting the result with ORDER BY 

Handling NULL values in expressions 

Restricting rows with the WHERE filter 

Testing for equality or inequality 

Applying wildcard characters 

Avoiding NULL value pitfalls 

5: Querying Multiple Tables

Applying the ANSI/ISO standard join syntax 

Matching related rows with INNER JOIN 

Including no matched rows with OUTER JOIN Creating a Cartesian product with CROSS JOIN Combining results with set operators 

Stacking results with UNION 

Identifying matching rows with INTERSECT 

Utilizing EXCEPT to find nonmatching rows

6: Employing Functions in Data Retrieval

Processing data with row functions 

Conditional formatting with the CASE expression Utilizing the CASE expression to simulate IF tests Dealing with NULL values 

Performing analysis with aggregate functions 

Summarizing data using SUM, AVG and COUNT Finding the highest/lowest values with MAX and MIN Defining the summary level with GROUP BY 

Applying filter conditions with HAVING 

7: Constructing Nested Queries

Applying sub queries in filter conditions 

Creating correlated vs. no correlated sub queries Testing the existence of rows 

Including sub queries in expressions 

Placing sub queries in the column list 

Creating complex expressions containing sub queries Handling sub queries that return no rows 

8: Developing In-Line and Stored Views

Breaking down complex problems 

Selecting data from a query result set 

Using sub queries in the FROM clause