Articles Cookbook

Tag: Ms Sql Server

14 Differences Between Standard SQL and Transact-SQL

In my last article, I roughly described how standard SQL differs from T-SQL and who should learn which. Now I'd like to focus on the syntax differences and illustrate these differences with examples. If you think T-SQL is an extension implementing all the features from standard SQL, you aren't right. However, in SQL Server you will find almost all the features of the SQL standard. In this article you will find examples of some of the differences in syntax between standard SQL and Transact-SQL.

What's the Difference Between SQL and T-SQL?

If you are beginning to learn SQL and are confused by the differences between standard SQL and other similar languages like T-SQL, this article will help make things clear. You’ll not only learn about the difference between SQL and T-SQL but also find explanations concerning which topics would be better to start learning first: standard SQL or something more specific like MS SQL Server. What is Standard SQL? SQL (Structured Query Language) is a basic ANSI/ISO standard programming language designed to operate on data stored in relational databases.

Microsoft SQL Server Pros and Cons

Thinking about using Microsoft SQL Server? If so, you’re in the right place. In this article, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from both a company’s and a data specialist’s perspective. What Is Microsoft SQL Server? Before diving into the pros and cons of Microsoft SQL Server, I’ll explain what it is. In technical terms, it is a relational database management system (RDBMS) developed by Microsoft.

Microsoft SQL Server 2017 Installation Step by Step

-- -- Microsoft SQL Server is one of the most popular professional database servers on the market. In this guide, I’ll show you all SQL Server 2017 installation steps to help you install it on the Windows operating system together with SQL Management Studio. How to Install Microsoft SQL Server 2017 In this guide, I’ll show you the SQL Server 2017 installation step by step.

How to remove leading and/or trailing spaces of a string in T-SQL

Problem: You’d like to remove a spaces or a specific characters from the beginning and end of a string. Example: Our database has a table named company with data in two columns: id and name. idname 1' Super Market ' 2'Green shop ' 3' Modern Bookshop' Let’s trim the name of each company to remove the unnecessary space at the beginning and end.

How to Sum Values of a Column in SQL?

Problem: You’d like to sum the values of a column. Example: Our database has a table named game with data in the following columns: id, player, and score. idplayerscore 1John134 2Tom146 3Lucy20 4Tom118 5Tom102 6Lucy90 7Lucy34 8John122 Let’s find the total score obtained by all players.

How to Replace Part of a String in SQL

Problem: You’d like to replace part of a string with another string. Example: Our database has a table named investor with data in the following columns: id, company, and phone. idcompanyphone 1Big Market123–300-400 3The Sunny Restaurant123–222-456 4My Bank123-345-400 We’d like to change the phone number format for each company by replacing the hyphen character with a space.

How to Order by Count in SQL?

Problem: You aggregated data into groups, but you want to sort the records in descending order by the number of elements in the groups. Example: Our database has a table named user with data in the following columns: id, first_name, last_name, and country. idfirst_namelast_namecountry 1LisaWilliamsEngland 2GaryAndersPoland 3TomWilliamsPoland 4MichaelBrownFrance 5SusanSmithUSA 6AnneJonesUSA 7EllieMillerPoland Let’s create a report on our users.

How to Order By Two Columns in SQL?

Problem: You need to display records from a given table sorted by two columns. Example: Our database has a table named employee with the following columns: id, first_name, last_name, and salary. idfirst_namelast_namesalary 1LisaUlman3000 2AdaMuller2400 3ThomasGreen2400 4MichaelMuller3000 5MaryGreen2400 Let’s display all information for each employee but sort the records according to salary in descending order first and then by last name in ascending order.

How to Order Alphabetically in SQL

Problem: You want to display records from a table in alphabetical or reverse-alphabetical order according to given column. Example: Our database has a table named customer. The customer table contains data in the id, first_name, and last_name columns. id first_name last_name 1 Susan Thomas 2 John Michael 3 Tom Muller Let’s display each customer’s information, sorted in ascending order by their last name.

How to Multiply Two Columns in SQL

Problem: want to multiply values from two columns of a table. Example: Our database has a table named purchase with data in the following columns: id, name, price, quantity, and discount_id. idnamepricequantitydiscount_id 1pen731 2notebook582 3rubber1131 4pencil case2423 Let’s multiply the price by the quantity of the products to find out how much you paid for each item in your order.

How to Join on Multiple Columns

Problem: You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another. Example: Our database has three tables named student, enrollment, and payment. The student table has data in the following columns: id (primary key), first_name, and last_name. idfirst_namelast_name 1EllieWillson 2TomBrown 3SandraMiller The enrollment table has data in the following columns: primary key (student_id and course_code), is_active, and start_date.

How to Join Multiple (3+) Tables in One Statement

Problem: You’d like to combine data from more than two tables using only one SELECT statement. Example: There are four tables in our database: student, teacher, subject, and learning. The student table contains data in the following columns: id, first_name, and last_name. idfirst_namelast_name 1TomMiller 2JohnSpring 3LisaWilliams 4EllieBarker 5JamesMoore The teacher table contains data in the following columns: id, first_name, last_name, and subject.

How to Handle Divide by Zero In SQL

Problem: You want to perform division in your SQL query, but the denominator is an expression that can be zero. The database will give you an error when the denominator is in fact zero. Example: Our database has a table named investor_data with data in the following columns: id, investor_year, price_per_share, income, and expenses. idinvestor_yearprice_per_shareincomeexpenses 120162032002300 2201713020002000 3201840200100 420191559004900 Let’s divide the price per share by the difference between income and expenses to determine the P/E ratio (price-earnings ratio) for each year.

How to Find the Minimum Value of a Column in SQL

Problem: You’d like to find the smallest numeric value in a column. Example: Our database has a table named employment with data in the following columns: id, first_name, last_name, department, and salary. idfirst_namelast_namedepartmentsalary 1EllieMartinesmarketing1200 2MartinJohnsonfinance2300 3MichaelJacobsproduction1100 4StephenKowalskimarketing4300 5StanleyMillermarketing3500 6JenyBrownfinance5000 7MargaretGreenmarketing1500 8LisaThomasproduction2800 Let’s find the lowest salary among all employees.

How to Find the Maximum Value of a Numeric Column in SQL

Problem: You’d like to find the maximum value of a numeric column. Example: Our database has a table named product with data in the following columns: id, name, year, and items. idnameyearitems 1bread roll2018345 2chocolate2017123 3butter201934 4bread roll2019456 5butter201856 6butter201778 7chocolate201987 8chocolate201876 Let’s find the maximum number of items sold over all years.

How to Find the Average of a Numeric Column in SQL

Problem: You’d like to calculate the average of numbers stored in a column. Example: Our database has a table named sale with data in the following columns: id, city, year, and amount. idcityyearamount 1Los Angeles20172345.50 2Chicago20181345.46 3Annandale2016900.56 4Annandale201723230.22 5Los Angeles201812456.20 6Chicago201789000.40 7Annandale201821005.77 8Chicago20162300.89 Let’s calculate the average sales, regardless of city or year.

How to Find Records with NULL in a Column

Problem: You want to find records with NULL in a column. Example: Our database has a table named children with data in four columns: id, first_name, middle_name, and last_name. idfirst_namemiddle_namelast_name 1JohnCarlJackson 2TomNULLThomson 3LisaAliceNULL 4AnneNULLSmith Let’s find the id, first name, and last name of children without a middle name (NULL in column middle_name). We use the IS NULL operator for this.

How to Find Minimum Values in Columns

Problem: You want to find rows that store the smallest numeric value in a column. Example: Our database has a table named weather with data in the following columns: id, city, and temperature. idcitytemperature 1Houston23 2Atlanta20 3Boston15 4Cleveland15 5Dallas34 6Austin28 Here’s how to find cities with the lowest temperature.

How to Find Duplicate Rows in SQL?

Problem: You have duplicate rows in your table, with only the IDs being unique. How do you find those duplicate entries? Example: Our database has a table named product with data in the following columns: id, name, and category. idnamecategory 1steakmeat 2cakesweets 3dsteakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products.

How to Filter Rows without NULL in a column

Problem: You want to find records without a NULL in a column. Example: Our database has a table named product with data in three columns: id, name, and price. idnameprice 1butterNULL 2milk2.35 3bread3.25 4cheeseNULL Let’s find the names and prices of products that have a price (without a NULL). Do this by using the IS NOT NULL operator.

How to Filter Records with Aggregate Function SUM

Problem: You need to find rows in which groups have a sum of values in one column less than a given value. Example: Our database has a table named company with data in the following columns: id, department, first_name, last_name, and salary. iddepartmentfirst_namelast_namesalary 1marketingLoraBrown2300 2financeJohnJackson3200 3marketingMichaelThomson1270 4productionTonyMiller6500 5productionSallyGreen2500 6financeOlivierBlack3450 7productionJenifferMichelin2800 8marketingJeremyLorson3600 9marketingLouisSmith4200 Let’s find the names of departments that have sums of salaries of its employees less than 7000.

How to Filter Records with Aggregate Function COUNT

Problem: You want to find groups of rows with a specific number of entries in a group. Example: Our database has a table named product with data in the following columns: id, name and category. idnamecategory 1sofafurniture 2glovesclothing 3T-Shirtclothing 4chairfurniture 5deskfurniture 6watchelectronics 7armchairfurniture 8skirtclothing 9radio receiverelectronics Let’s find the category of products with more than two entries.

How to Filter Records with Aggregate Function AVG

Problem: You want to find groups of rows in which the average of values in a column is higher or lower than a given value. Example: Our database has a table named product with data in the following columns: id, name, grocery and price. idnamegroceryprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.

How to Eliminate Duplicate Rows in SQL

Problem: You’d like to eliminate any duplicate rows from the result set of a query so that each row appears only once. Example: Our database has a table named clothes with data in the following columns: id, name, color, and year_produced. idnamecoloryear_produced 1T-shirtyellow2015 2jacketblue2016 3jeansblack2015 4jacketblue2015 5jacketgreen2016 6jacketyellow2017 7hatyellow2017 Let’s get the non-repeated names and colors of clothes produced before 2017.

How to Count the Number of Rows in a Table in SQL

Problem: You’d like to determine how many rows a table has. Example: Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name. ideIDname 123456sparky 223457mily 3NULLlessy 4NULLcarl 534545maggy Let’s count all rows in the table. Solution: COUNT(*) counts the total number of rows in the table:

How to Count Distinct Values in SQL

Problem: You’d like to count how many different non-NULL values there are in a given column. Example: Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1JohnWilliamsChicago 2TomBrownAustin 3LucyMillerChicago 4EllieSmithDallas 5BrianJonesAustin 6AllanDavisNULL Let’s find the number of different (and non-NULL) cities.

Finding Maximum Values in Rows

Problem: You want to find which rows store the largest numeric value in a given column. Example: Our database has a table named student with data in the following columns: id, first_name, last_name, and grade. idfirst_namelast_namegrade 1LisaJackson3 2GaryLarry5 3TomMichelin2 4MartinBarker2 5EllieBlack5 6MarySimpson4 Let’s find the students who have the highest grades.