MSBI Course

Using of Where Clause in SQL Server

Using of Where Clause in SQL Server

This SQL Server tutorial explains how to use the WHERE clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

WHERE conditions;

Parameters or Arguments

conditions : The conditions that must be met for records to be selected.

For example:

SELECT * FROM employees WHERE first_name = ‘Jane’;

In this SQL Server WHERE clause example, we’ve used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is ‘Jane’. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

SSIS School of Training

Types of keys is SQL Server

What is Key? Keys are fields in a table which participate in below activities in RDBMS systems:

  • To create relationships between two tables.
  • To maintain uniqueness in a table.
  • To keep consistent and valid data in database.
  • Might help in fast data retrieval by facilitating indexes on column(s).

SQL Server supports various types of keys, which are listed below:

  1. Candidate Key
  2. Primary Key
  3. Unique Key
  4. Alternate Key
  5. Composite Key
  6. Super Key
  7. Foreign Key

Before discussing each type in brief, have a look on the below image used as an an example to define types of keys.

Lets discuss each type in detail:

  • Candidate Key: Candidate key is a key of a table which can be selected as a primary key of the table. A table can have multiple candidate keys, out of which one can be selected as a primary key.

Example: Employee_Id, License_Number and Passport_Number are candidate keys

  • Primary Key: Primary key is a candidate key of the table selected to identify each record uniquely in table. Primary key does not allow null value in the column and keeps unique values throughout the column. In above example, Employee_Id is a primary key of Employee table. In SQL Server, by default primary key creates a clustered index on a heap tables (a table which does not have a clustered index is known as a heap table). We can also define a nonclustered primary key on a table by defining the type of index explicitly.

A table can have only one primary key and primary key can be defined in SQL Server using below SQL statements: CRETE TABLE statement (at the time of table creation) – In this case, system defines the name of primary key ALTER TABLE statement (using a primary key constraint) – User defines the name of the primary key Example: Employee_Id is a primary key of Employee table.

  • Unique Key: Unique key is similar to primary key and does not allow duplicate values in the column. It has below differences in comparison of primary key:

It allows one null value in the column. By default, it creates a nonclustered index on heap tables. Alternate Key Alternate key is a candidate key, currently not selected as primary key of the table. Example: License_Number and Passport_Number are alternate keys.

  • Composite Key: Composite key (also known as compound key or concatenated key) is a group of two or more columns that identifies each row of a table uniquely. Individual column of composite key might not able to uniquely identify the record. It can be a primary key or candidate key also.

Example: In salary table, Employee_Id and Salary_Month_Year are combined together to identify each row uniquely in Salary table. Independently Employee_Id or Salary_Month_Year column cannot identify each row uniquely. We can create a composite primary key on Salary table using Employee_Id and Salary_Month_Year columns.

  • Super Key: Super key is a set of columns on which all columns of the table are functionally dependent. It is a set of columns that uniquely identifies each row in a table. Super key may hold some additional columns which are not strictly required to uniquely identify each row. Primary key and candidate keys are minimal super keys or you can say subset of super keys.

In above example, In Employee table, column Employee_Id is sufficient to uniquely identify any row of the table, so that any set of column from Employee table which contains Employee_Id is a super key for Employee Table. For example: {Employee_Id}, {Employee_Id, Employee_Name}, {Employee_Id, Employee_Name, Address} etc. License_Number and Passport_Number columns can also identify any row of the table uniquely. Any set of column which contains License_Number or Passport_Number or Employee_Id is a super key of the table. For example: {License_Number, Employee_Name, Address}, {License_Number, Employee_Name, Passport_Number}, {Passport_Number, Employee_Name, Address, License_Number}, {Passport_Number, Employee_Name}, {Passport_Number, Employee_Id} etc.

  • Foreign Key: In a relationship between two tables, a primary key of one table is referred as a foreign key in another table. Foreign key can have duplicate values in it and can also keep null values if column is defined to accept nulls.

Example: Employee_Id (primary key of Employee table ) is a foreign key in Salary table.

Use SQL Server Management Studio (SSMS) on Windows to manage SQL Server on Linux

This topic shows how to use SQL Server Management Studio (SSMS) to connect to SQL Server 2017 on Linux. SSMS is a Windows application, so use SSMS when you have a Windows machine that can connect to a remote SQL Server instance on Linux.

After successfully connecting, you run a simple Transact-SQL (T-SQL) query to verify communication with the database.

Install the newest version of SQL Server Management Studio

When working with SQL Server, you should always use the most recent version of SQL Server Management Studio (SSMS). The latest version of SSMS is continually updated and optimized and currently works with SQL Server 2017 on Linux. To download and install the latest version, see Download SQL Server Management Studio. To stay up-to-date, the latest version of SSMS prompts you when there is a new version available to download.

Connect to SQL Server on Linux

The following steps show how to connect to SQL Server 2017 on Linux with SSMS.

  1. Start SSMS by typing Microsoft SQL Server Management Studioin the Windows search box, and then click the desktop app.
    1. In the Connect to Serverwindow, enter the following information (if SSMS is already running, click Connect > Database Engine to open the Connect to Server window):

     

    Setting Description
    Server type The default is database engine; do not change this value.
    Server name Enter the name of the target Linux SQL Server machine or its IP address.
    Authentication For SQL Server 2017 on Linux, use SQL Server Authentication.
    Login Enter the name of a user with access to a database on the server (for example, the default SA account created during setup).
    Password Enter the password for the specified user (for the SA account, you created this during setup).

  2. Click Connect.
  3. After successfully connecting to your SQL Sever, Object Explorer opens and you can now access your database to perform administrative tasks or query data.

    Run sample queries

    After you connect to your server, you can connect to a database and run a sample query. If you are new to writing queries, see Writing Transact-SQL Statements.

    1. Identify a database to use to run a query against. This could be a new database you created in the Transact-SQL tutorial. Or it could be the AdventureWorks sample database that you downloaded and restored.
    2. In Object Explorer, navigate to the target database on the server.
    3. Right-click the database and then select New Query:

    4. n the query window, write a Transact-SQL query to select data from one of the tables. The following example selects data from the Production.Product table of the AdventureWorks database.

       SELECT TOP 10 Name, ProductNumber
       FROM Production.Product
       ORDER BY Name ASC
    5. Click the Execute button:

What is Normalization? 

What is Normalization? 

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

It divides larger tables to smaller tables and link them using relationships.

In this tutorial, you will learn-

The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of First Normal Form and he continued to extend theory with Second and Third Normal Form. Later he joined with Raymond F. Boyce  to develop the theory of Boyce-Codd Normal Form.

Theory of Data Normalization in Sql is still being developed further. For example there are discussions even on 6th Normal Form. But in most practical applications normalization achieves its best in 3rd Normal Form. The evolution of Normalization theories is illustrated below

1st Normal Form >> 2nd Normal Form >> 3rd Normal Form >>4th Normal Form >>  5th Normal Form >>6th Normal Form

Database Normalization Examples –

Assume a video library maintains a database of movies rented out. Without any normalization all information is stored in one table as shown below.

Further We shall discuss on different normal forms in detailed:

 

 

 

 

 

 

How to use INNER JOIN in SQL Server?

The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax: The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2…
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Image Representation:

The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

Sample Two Table Data:

FOODS:

 ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_ID
1 Bread PCS 25
2 Sliced Bread PCS 16
3 Roasted Bread PCS 32
4 Garlic Bread PCS 48
5 Cheese Bread PCS 18
6 Fruit Bread PCS 22
7 Milk Bread PCS 28

Company:

COMPANY_ID COMPANY_NAME COMPANY_CITY
25 SUBWAY IRLAND
16 BURGER KING UNITED STATES
32 KFC UK
18 ROLLZONE AUSTRALIA
28 DOMINOS INDIA

Example

Here is an example of inner join in SQL between two tables.

To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition –

1. company_id of foods and company_id company table must be same, the following SQL statement can be used :

SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
INNER JOIN company
ON foods.company_id =company.company_id;

Example of SQL INNER JOIN using JOIN keyword

To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition –

1. company id of foods and company id of company table must be same,

the following SQL statement can be used :
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
JOIN company
ON foods.company_id =company.company_id;

SQL Server next version CTP 1.4

Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server v.Next). Community Technology Preview (CTP) 1.4 is available on both Windows and Linux. In this preview, they added the ability to schedule jobs using SQL Server Agent on Linux. We can try the preview in your choice of development and test environments now: www.sqlserveronlinux.com.

Key CTP 1.4 enhancements
The primary enhancement to SQL Server v.Next on Linux in this release is the ability to schedule jobs using SQL Server Agent. This functionality helps administrators automate maintenance jobs and other tasks, or run them in response to an event. Some SQL Server Agent functionality is not yet enabled for SQL Server on Linux. To learn more and see sample SQL Server Agent jobs, you can read detailed blog titled “SQL Server on Linux: Running scheduled jobs with SQL Server Agent” or attend an Engineering Town Hall about “SQL Server Agent and Full Text Search in SQL Server on Linux.”

The mssql-server-linux container image on Docker Hub now includes the sqlcmd and bcp command line utilities to make it easier to create and attach databases and automate other actions when working with containers. For additional detail on CTP 1.4, please visit What’s New in SQL Server v.Next, Release Notes and Linux
documentation.

In addition, SQL Server Analysis Services and SQL Server Reporting Services developer tools now support Visual Studio 2017. They are available for installation from the Visual Studio Marketplace providing the option for automatic updates going forward.

 

Get SQL Server v.Next CTP 1.4 today!
Try the preview of the next release of SQL Server today! Get started with the preview of SQL Server with our developer tutorials that show you how to install and use SQL Server v.Next on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice.

SQLServer, SQL DBA, MSBI Training Certification

For details, please visit www.sqlschool.com/Certifications

Microsoft Certifications Training

 

Microsoft Certifications Training For SQL Server, SQL DBA and MSBI [MCSE]

70-461 Training, 70-462 Training, 70-463 Training, 70-466 Training, 70-4657 Training

For details, please visit www.sqlschool.com/Certifications 

Choose SQL School for Real-time and Practical Trainings with Projects and Placements.

24×7 LIVE Server access and salient highlights of our Project Oriented On-Job Trainings, our courses are carefully designed with keeping in view, a starter in SQL Server technology. Prices are very much competitive and option to pay in installments for DBA and MSBI courses is an added choice of relief.

Real-time project absolutely FREE for every course you sign up!

SQL Server, SQL DBA and MSBI Trainer @ SQL School

What our customer say? Google | Facebook | Sulekha | Just Dial | Grotal | Yet 5 |ThinkVidya| View More

 

 

 

Register Today for Free Demo : SQL Server, SQL DBA, MSBI Trainings

Reach Us today for Free Demo

Email: contact@sqlschool.com

USA: +1 (510) 400 – 4845

India: +91 (0) 9666440801    /    040 64577244

SQL School is rated the BEST Training Center for SQL Server and MSBI

https://www.google.co.in/?gfe_rd=cr&ei=OnhhVZfHG6_v8wequYHAAQ&gws_rd=ssl#q=sql+school

https://www.google.co.in/?gfe_rd=cr&ei=OnhhVZfHG6_v8wequYHAAQ&gws_rd=ssl#q=sql+school

 

SQL School Training Institute (www.sqlschool) offers you complete practical real-time trainings.

Courses Offered:
1. SQL Server Developer Course (http://sqlschool.com/TSQL-Online-Training.html)
2. SQL DBA Course (http://sqlschool.com/SQLDBA-Online-Training.html)
3. MSBI Course (http://sqlschool.com/MSBI-Online-Training.html)
4. SSIS Course (http://sqlschool.com/SSIS-Online-Training.html)
5. SSAS Course (http://sqlschool.com/SSAS-Online-Training.html)
6. SSRS Course (http://sqlschool.com/SSRS-Online-Training.html)

To register for free demo:
http://sqlschool.com/contact.html

Call Us:
Mail: contact@sqlschool.com
Skype: SQL School
USA (001): (510) 400 – 4845
India (0091): 9666440801

MSBI / SQL BI LIVE Online Training with Realtime Project

www.sqlschool.com

www.sqlschool.com

 

Please register for MSBI (SSIS, SSAS, SSRS) LIVE Online Training – Free Demo on Apr 3, 2015 8:00 AM IST at:

https://attendee.gotowebinar.com/register/1523739862997573633

Completely Real-time and Practical.
Theory Material in Advance.
Trainer Profile @ https://www.linkedin.com/in/saiphanindra

After registering, you will receive a confirmation email containing information about joining the webinar.

For course details, please visit : http://sqlschool.com/MSBI-Online-Training.html