Version used here SQL Server Management Studio 15.0.18206.0
4 The following illustrates the BikeStores database diagram:
5 Load Sample Database
After that data is loaded, you can make your own diagram, navigate like this:
Go to Sql Server Management Studio >
Object Explorer >
Databases >
Choose and expand your Database.
Under your database right click on “Database Diagrams” and select “New Database Diagram”.
It will a open a new window. Choose tables to include in ER-Diagram (to select multiple tables press “ctrl” or “shift” button and select tables).
Click add.
Wait for it to complete. Done!!
BikeStores Sample Database – create objects.sql – this file is for creating database objects including schemas and tables.
BikeStores Sample Database – load data.sql – this file is for inserting data into the tables
BikeStores Sample Database – drop all objects.sql – this file is for removing the tables and their schemas from the sample database. It is useful when you want to refresh the sample database.
Add a new Database called BikeStores and load the create objects.sql
Open the file for loading data into the tables.
Choose the BikeStores Sample Database – load data.sql file and click the Open button.
Here is our own diagram from the steps above:
Data Manipulation
SQL Server OFFSET FETCH clauses to limit the number of rows returned by a query:
ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
The OFFSET clause specifies the number of rows to skip before starting to return rows from the query. The offset_row_count can be a constant, variable, or parameter that is greater or equal to zero.
The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed. The offset_row_count can a constant, variable or scalar that is greater or equal to one.
The OFFSET clause is mandatory while the FETCH clause is optional. Also, the FIRST and NEXT are synonyms respectively so you can use them interchangeably. Similarly, you can use the FIRST and NEXT interchangeably.
/****** Script for SelectTopNRows command from SSMS ******/
--SELECT [product_id]
,[product_name]
,[brand_id]
,[category_id]
,[model_year]
,[list_price]
FROM [BikeStores].[production].[products]
ORDER BY product_id
OFFSET 10 ROWS;
To get the top 10 most expensive products you use both OFFSET and FETCH clauses:
/****** Script for SelectTopNRows command from SSMS ******/ --SELECT [product_id] ,[product_name] ,[brand_id] ,[category_id] ,[model_year] ,[list_price] FROM [BikeStores].[production].[products] ORDER BY list_price desc,product_name OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
Using TOP WITH TIES to include rows that match the values in the last row
/****** Script for SelectTopNRows command from SSMS ******/
--SELECT TOP 3 WITH TIES
[product_id]
,[product_name]
,[list_price]
FROM [BikeStores].[production].[products]
ORDER BY list_price desc
DISTINCT multiple columns example
/****** Script for SelectTopNRows command from SSMS ******/
--SELECT DISTINCT
[city]
,[state]
FROM [BikeStores].[sales].[customers]
The following statement finds the distinct city and state of all customers.
Between, list prices that are not in the range 149.99 and 199.99
/****** Script for SelectTopNRows command from SSMS ******/
--SELECT [product_id]
,[product_name]
,[list_price]
FROM [BikeStores].[production].[products]
WHERE list_price NOT BETWEEN 150 AND 200
order by list_price
Joins
Joins – give you a brief overview of joins types in SQL Server including inner join, left join, right join and full outer join.
INNER JOIN – select rows from a table that have matching rows in another table.
LEFT JOIN – return all rows from the left table and matching rows from the right table. In case the right table does not have the matching rows, use null values for the column values from the right table.
RIGHT JOIN – learn a reversed version of the left join.
FULL OUTER JOIN – return matching rows from both left and right tables, and rows from each side if no matching rows exist.
CROSS JOIN – join multiple unrelated tables and create Cartesian products of rows in the joined tables.
Self join – show you how to use the self-join to query hierarchical data and compare rows within the same table.
Setting up sample tables:
--create schema hr;
--go
CREATE TABLE hr.candidates(
id INT PRIMARY KEY IDENTITY,
fullname VARCHAR(100) NOT NULL
);
CREATE TABLE hr.employees(
id INT PRIMARY KEY IDENTITY,
fullname VARCHAR(100) NOT NULL
);
INSERT INTO
hr.candidates(fullname)
VALUES
('John Doe'),
('Lily Bush'),
('Peter Drucker'),
('Jane Doe');
INSERT INTO
hr.employees(fullname)
VALUES
('John Doe'),
('Jane Doe'),
('Michael Scott'),
('Jack Sparrow');