Section : Introduction

 

. Introduction

 

 

. Installation of SQL Server  & SSMS

 

 

. Connect To Sql Server

 

 

. Create Sample Database (MediaStoreDb)

 

 

. Generate a Database Relationship Diagram

 

Section : SELECT

 

. Get all Customer Details

 

 

. Get all Album Details

 

 

. Get Customer Fields

 

 

. Get Track Fields

 

 

. Get Employee Fields

 

Section : Column Alias

 

. Display Customer Phone Number using Custom Field Name

 

 

. Display Track Unit Price using Custom Field Alias

 

Section : DISTINCT

 

. Get Customers Unique Countries

 

 

. Get Employee Titles

 

Section : WHERE(=,<>,>,<,>=,LIKE,BETWEEN,IN

 

. Get Tracks having Track Length

Condition

 

 

. Customers having Last Name Condition

 

 

. Tracks Starting With The Letters A – F

 

 

. Tracks Not Starting With The Letters A- F

 

 

. Get Invoices Between Two Dates

 

 

. Get Customers from California and Florida

 

 

. Get Management Level Employees

 

 

. Get IT Staff Level Employees

 

 

. Customers in USA, Canada with Custom Last Name

 

 

. Customers not from USA with Custom Last Name

 

 

. Customers Missing a State Code

 

Section : ORDER BY (ASC/DESC)

 

. Customers Living in Berlin Sorted

 

 

. Most Recent Orders between Two Dates

 

 

. Employees Sorted by Title and Last Name

 

 

. Tracks Having a Composer Sorted by Name

 

Section : TOP

 

. Last Five Invoices Created

 

 

. Top % of Tracks by Length

 

 

. First Ever Invoice Created

 

Section : Aggregates/GROUP BY (SUM,AVG,COUNT,MAX,)

 

. Total Price of Each Invoice

 

 

. Total Price of Each Invoice with Condition

 

 

. Average Track Length

 

 

. Customer Count by Country

 

 

. Countries Having Less than Customers

 

 

. Invoice Count per Customer

 

 

. Longest Running Track and Total Tracks

 

 

. Average Runtime, Total Price, Highest Bytes,Total Tracks (All Albums)

 

 

. Average Runtime, Total Price, Highest Bytes,Total Tracks (Per Album)

 

Section : Concatenation

 

. Customer Display Name (Last, First)

 

 

. Apply % Discount of Track’s Unit Price

 

Section : Inner Queries/SubQueries

 

. Tracks Having Runtime Smaller than Average

 

 

. Customers with Invoices More Than Some Amount

 

 

. Tracks Having No Sales

 

 

. Genres Having No Sales

 

Section : Joins (INNER, LEFT,RIGHT)

 

. Customer, Invoice Info for Orders Placed Before March

 

 

. Invoice Details for a Specific Customer

 

 

. Total Daily Sales by Sales Agent

 

 

. Customers Having Zero Orders Placed

 

 

. Albums that Generated Zero Sales

 

Section : Stored Procedures

 

. Overview

 

 

. Create and Alter Your First Stored

Procedure

 

 

. Excel GetCustomers Report