3 Nov 2022 Jakub Romanowski SQL Track of the Season: SQL from A to Z in MS SQL Server Maybe your company uses MS SQL Server and you’d like to join the data analysis team. Or maybe you want to add a new skill to your CV. Either way, our SQL Track of the Season: SQL from A to Z in MS SQL Server, will help. In this article, I’ll answer common questions about this awesome set of online MS SQL Server courses. If you’re reading this blog post, you probably already know that you should start learning SQL and working with databases. Read more 12 Aug 2021 Martyna Sławińska An Overview of MS SQL Server Data Types SQL Server data types define what can be stored in a column, local variable, expression, or parameter. It is essential to pick the right data type. Ultimately. your choice of data types affects the whole database. Read on to learn about all of the data types available in MS SQL Server. In this article, we’ll cover numerical, text, and date and time data type categories in detail. We’ll go through their syntax, storage size, and typical use cases. Read more 26 Mar 2021 Dorota Wdzięczna How to Install Microsoft SQL Server 2019 and SQL Server Management Studio Do you want to install SQL Server 2019 on your Windows PC? How about the latest version of SQL Server Management Studio? I’ll walk you through the process. Soon you’ll be writing T-SQL queries and operating on SQL Server databases! As I write this article, Microsoft SQL Server 2019 is the latest version of SQL Server. It’s the 15th version of one of the most popular database servers in the world. Read more 25 Mar 2021 Jakub Romanowski Is MS SQL Server Still Worth Learning in 2024? MS SQL Server is an in-demand skill in today’s job market. Should you learn MS SQL Server in 2024? In this article, I’ll answer this question. Thinking about whether you should dive into Microsoft SQL Server in 2024 – especially with so many free database alternatives available? MS SQL Server has been a go-to for handling data for ages, thanks to its solid features and reliability. But now, with a bunch of new options on the block, some people wonder if it's still the champ. Read more 10 Mar 2021 Jakub Romanowski Where Can I Find Good Courses to Practice MS SQL Server? You’ve got the basics of MS SQL Server. Where can you find practice exercises in this SQL dialect? You've started learning MS SQL Server. Maybe you've done a course, watched some YouTube tutorials, and feel pretty confident in your knowledge. What now? Let me tell you, you need to practice. Only by writing queries can you truly master SQL. In this article, I will show you where to find practice MS SQL Server exercises. Read more 16 Feb 2021 How to Find the Difference Between Two Datetimes in T-SQL Problem: You have two columns of the type datetime and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1 (difference in seconds): SELECT id, departure, arrival, DATEDIFF(second, departure, arrival) AS difference FROM travel; The result is: Read more 24 Jan 2021 How to Group by Two Columns in SQL Problem: You want to group your data by two columns so you can count some statistics. Example: In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day. The order table looks like this: order_dateproduct_idcustomer_idnumber 2023-11-25711 2023-11-251213 2023-11-265312 2023-11-26124 2023-11-26321 2023-11-261627 2023-11-26332 2023-11-27631 Solution: SELECT order_date, customer_id, SUM(number) AS products_number FROM order GROUP BY order_date, customer_id; The result is: Read more 24 Jan 2021 How to Delete a Foreign Key Constraint in SQL Problem: You want to delete a foreign key from a table in a database. Example: We want to remove the foreign key named fk_student_city_id from the table student. Solution: ALTER TABLE student DROP CONSTRAINT fk_student_city_id; Discussion: To delete a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. Read more 24 Jan 2021 How to Create a Table with a Foreign Key in SQL Problem: You want to create a foreign key for a table in a database. Example: We would like to create a table named student that contains a foreign key that refers to the id column in the table city. Solution 1: Creating new table with single-column foreign key CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, city_id INT FOREIGN KEY REFERENCES city(id) ); Discussion: To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Read more 24 Jan 2021 How to Calculate the Difference Between Two Dates in T-SQL Problem: You have two columns of the date type and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure, or the number of days from arrival to departure inclusively. The travel table looks like this: iddeparturearrival 12018-03-252018-04-05 22019-09-122019-09-23 32018-07-142018-07-14 42018-01-052018-01-08 Solution: SELECT id, departure, arrival, DATEDIFF(day, departure, arrival) AS date_difference, DATEDIFF(day, departure, arrival) + 1 AS days_inclusive FROM travel; The result is: Read more 30 Dec 2020 How to remove a unique constraint in SQL? Problem: You would like to remove a unique constraint from the column/columns in a table in a database. Example: We would like to drop the unique constraint in the table product from the column name. The statement below shows how to do it. Solution ALTER TABLE product DROP CONSTRAINT UQ_product_name In this example the table product is altered by using the ALTER TABLE clause. After this clause you enter the name of the table (in our example: product) and the clause DROP CONSTRAINT with the name of the unique constraint you want to remove. Read more 30 Dec 2020 How to Make a Column Unique in SQL? Problem: You would like to make a column unique in a given table in a database. Example: We would like to make the column name unique in the table product. The query below presents one way to do it. Solution 1: Creating new table with a UNIQUE constraint CREATE TABLE product ( id INT NOT NULL PRIMARY KEY, name VARCHAR(100) UNIQUE, producer VARCHAR(100), category VARCHAR(100) ); Discussion: In this example a given column (the column name) was made unique by adding the clause UNIQUE at the end of the column definition (name VARCHAR(100) UNIQUE). Read more 30 Dec 2020 How to Group by Year in T-SQL Problem: You want to group your data by year in SQL Server database. Example: One of the columns in your data is transaction_date. It contains a date. You would like to group all your data by year and calculate the total money earned each year. The data table looks like this: transaction_datemoney 2022-03-251700 2023-09-12100 2022-07-141200 2022-01-05400 2023-06-082000 2021-03-061500 Solution 1: Displaying the year and the money earned SELECT YEAR(transaction_date) AS year, SUM(money) AS money_earned FROM data GROUP BY YEAR(transaction_date); The result is: Read more 30 Dec 2020 How to Calculate a Square Root in SQL Problem: You want to find the square root of a number in SQ:. Example: You want to compute the square root of all numbers in the column number from the table data. number 9 2 1 0.25 0 -4 Solution: SELECT number, SQRT(number) AS square_root FROM data; The result is: Read more 9 Oct 2020 How to Get Yesterday’s Date in T-SQL Problem: You would like to display yesterday's date (without time) in an SQL Server database. Solution: SELECT DATEADD(day, -1, CAST(GETDATE() AS date)) AS YesterdayDate; Assuming today is 2020-09-24, the result is: yesterday_date 2020-09-23 Discussion: To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime) and cast it to date. Read more 22 Feb 2020 How to Subtract 30 Days from a Date in T-SQL Problem: You’d like to get the date 30 days before a given date in T-SQL. Example: Our database has a table named Computer with data in the columns Id, Name, and PurchaseDate. IdNamePurchaseDate 1Sony GX10002019-01-20 2Samsung LX20002019-04-15 3Dell K802019-08-30 Let’s get the name of each computer and the date 30 days before its purchase date. Read more 22 Feb 2020 How to Get the Year from a Date in T-SQL Problem: You’d like to get the year from a date field in a SQL Server database. Example: Our database has a table named Children with data in the columns Id, FirstName, LastName, and BirthDate. IdFirstNameLastNameBirthDate 1JaneSmith2018-06-20 2GaryBrown2010-02-02 3LoraAdams2014-11-05 Let’s get the year from each child’s birthdate. Solution: We’ll use the YEAR() function. Here’s the query you would write: Read more 22 Feb 2020 How to Get the Month from a Date in T-SQL Problem: You’d like to get the month from a date field in a SQL Server database. Example: Our database has a table named Furniture with data in the columns Id, Name, and ProducedDate. IdNameProducedDate 1sofa2018-01-10 2chair2018-01-05 3desk2018-06-20 4bookcase2018-11-15 Let’s get the month from each product’s ProducedDate and find out which furniture was produced in a given month. Read more 22 Feb 2020 How to Get the Last Day of the Month in T-SQL Problem: You’d like to find the last day of the month for a specific date in T-SQL. Example: Our database has a table named Furniture with data in the columns Id, Name, and PurchaseDate. IdNamePurchaseDate 1sofa2019-02-10 2desk2019-04-01 3bookcase2019-05-20 Let’s get products’ names and purchase dates and the last day of the month when these items were purchased. Read more 22 Feb 2020 How to Get the Day from a Date in T-SQL Problem: You’d like to get the number of day from a date field in a SQL Server database. For example, from the date of February 27, 2023, you'd like to get the number 27. Example: Our database has a table named MedicalVisit with data in the columns Id, FirstName, LastName, and VisitDate. IdFirstNameLastNameVisitDate 1JaneMiller2019-11-17 2AlexSmith2019-11-20 3WilliamBrown2019-11-20 4AliceThomas2019-11-05 For each patient, let’s find the day of the medical visit. Read more 22 Feb 2020 How to Get the Current Date (Without Time) in T-SQL Problem: You’d like to get the current date in T-SQL, but you don’t need the time. Solution: We’ll use the GETDATE() function to get the current date and time. Then we’ll use the CAST() function to convert the returned datetime data type into a date data type. SELECT CAST( GETDATE() AS Date ) ; Here’s the result of the query: 2019-08-17 Discussion: To get the current date and time in SQL Server, use the GETDATE() function. Read more 22 Feb 2020 How to Get Current Date & Time in T-SQL (No Time Zone) Problem: You’d like to get the current date and time in T-SQL, but you don’t want the time zone offset. Solution: We’ll use GETDATE(), CURRENT_TIMESTAMP, and SYSDATETIME() to get the current date and time without the time zone offset. The first two functions allow us to get the current time with a lower precision. (GETDATE() is a T-SQL function, while CURRENT_TIMESTAMP is a SQL standard function; both functions return the same data type). Read more 22 Feb 2020 How to Change Date and Time Formats in T-SQL Problem: You’d like to change the format of a date field or value in a SQL Server database. Example: Our database has a table named Patient with data in the columns Id, FirstName, LastName, and RegistrationDate. IdFirstNameLastNameRegistrationDate 1JaneWilliams2019-06-20 2GabrielBrown2019-02-02 3LoraFolk2016-11-05 Let’s change the format of each patient’s registration date. We’ll put the name of the weekday first, followed by the month day and name and a 4-digit year (e. Read more 22 Feb 2020 How to Add Days to a Date in T-SQL Problem: You’d like to add a given number of days to a date in T-SQL. Example: Our database has a table named Flight with data in the columns Code and DepartureDate. CodeDepartureDate LT20302023-02-20 GH11002023-03-01 SR54672023-12-30 Let’s change the departure date for all flights, adding two days to the current departure date. Solution: We will use the DATEADD() function to specify the unit of time to add, define how much to add, and select the date to change. Read more 15 Feb 2020 How to Find Rows with Minimum Value Problem: You want to find rows which 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. You want to find cities with the lowest temperature. idcitytemperature 1Houston23 2Atlanta20 3Boston15 4Cleveland15 5Dallas34 6Austin28 Solution: SELECT id, city, temperature FROM weather WHERE temperature = (SELECT MIN(temperature) FROM weather); Here’s the result: Read more 15 Feb 2020 How to Find Rows with Maximum Value Problem: You want to find rows which 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. You want to find the students who have the highest grades. idfirst_namelast_namegrade 1LisaJackson3 2GaryLarry5 3TomMichelin2 4MartinBarker2 5EllieBlack5 6MarySimpson4 Solution: SELECT id, first_name, last_name, grade FROM student WHERE grade = (SELECT MAX(grade) FROM student); Here’s the result: Read more 15 Feb 2020 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. Solution: SELECT id, first_name, last_name FROM children WHERE middle_name IS NULL; Here’s the result: Read more 15 Feb 2020 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. Read more 15 Feb 2020 How to Filter Records with Aggregate Function SUM Problem: You need to filter groups of rows by the sum of a given column. 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. Read more 15 Feb 2020 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. Read more 15 Feb 2020 How to Filter Records with Aggregate Function AVG Problem: You want to filter groups of rows in by the average value of a given column. Example: Our database has a table named product with data in the following columns: id, name, store and price. idnamestoreprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.45 7milkClark’s Grocery2.10 8breadViolet Grocery2. Read more 9 Feb 2020 What’s the Difference Between RANK and DENSE_RANK in SQL? Problem: You want to compare the rankings produced by RANK and DENSE_RANK and add them as new columns to a table. Example: Our database has a table named sales_assistant with data in the following columns: id (primary key), first_name, last_name, month, and sold products. idfirst_namelast_namemonthsold products 1LisaBlack 52300 2MaryJacobs52400 3LisaBlack 62700 4MaryJacobs62700 5AlexSmith 62900 6MaryJacobs71200 7LisaBlack 71200 8AlexSmith 71000 Let’s display each sales assistant’s first and last name and number of sold products. Read more 9 Feb 2020 How to Remove Leading and Trailing Spaces in T-SQL Problem: You’d like to remove a spaces or a specific characters from the beginning and end of a string in T-SQL. 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. Read more 9 Feb 2020 How to Number Rows in SQL Problem: You would like to give each row in the result table a separate number. Example: Our database has a table named furniture with data in the following columns: code (primary key) and name. codename 101bed 202sofa 333chair 123bookcase 235table 766desk The furniture table stores the name of pieces of furniture that we want to number. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 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. Read more 9 Feb 2020 How to Convert a String to Uppercase in SQL Problem: You would like to convert a string to uppercase in SQL. Example: Our database has a table named questionnaire with data in the following columns: id, first_name, last_name, and favorite_car. idfirst_namelast_namefavorite_car 1AlanJacksonHonda Civic 2ElisaThomsonTOYOTA Camry 3MaryMartinesNissan rogue 4ChrisBrownford focus 5AdamSpringRam PICKUP Our table stores the make and model of the favorite car for each person who filled out our questionnaire. Read more 27 Nov 2019 How to Use LIKE in SQL Problem: You need to search for specific patterns (certain combinations of letters or numbers) within text data in your database. We'll show you how to use the LIKE operator in SQL to: Find city names starting with S Find city names with exactly five characters Find city names starting with S and with o as the second-to-last character You'll also learn how to use LIKE and wildcard operators in SQL to find your own patterns in text data in your database. Read more 27 Nov 2019 How to Round Up a Number to the Nearest Integer in SQL Problem: You want to round up a number to the nearest integer in SQL. Example: Our database has a table named rent with data in the following columns: id, city, area, and bikes_for_rent. idcityareabikes_for_rent 1Los Angeles1302.151000 2Phoenix1340.69500 3Fargo126.44101 Let’s show each city’s name along with the ratio of its area to the number of bikes for rent. Read more 27 Nov 2019 How to Round Numbers in SQL Problem: You want to round a number to a specific number of decimal places in SQL. Example: Our database has a table named product with data in the following columns: id, name, and price_net. idnameprice_net 1bread2.34 2croissant1.22 3roll0.38 Suppose there’s a tax of 24% on each product, and you’d like to compute the gross price of each item (i.e., after taxes) and round the value to two decimal places. Read more 27 Nov 2019 How to Floor Numbers in SQL Problem: You want to round a number down to the nearest integer. Example: Our database has a table named employee with data in the following columns: id, first_name, last_name, and hours_worked (for the current month). idfirst_namelast_namehours_worked 1AlanWatson95 2LisaBlack225 3LauraWilliams104 Let’s show the first and last name of each employee along with the number of days they worked. Read more 27 Nov 2019 How to Add Rankings with DENSE_RANK() in SQL Problem: You want to add a ranking position to rows consecutively, even if the rows have the same values. Example: Our database has a table named competition with data in the following columns: id (primary key), first_name, last_name, and score. idfirst_namelast_namescore 11JohnThomas345 14MaryJohnson222 16LisaBrown154 23AlanBlake222 32ChrisTaylor154 Let’s display all details about students: their last name, first name, and scores sorted by score in descending order. Read more 26 Nov 2019 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 2Tom 146 3Lucy20 4Tom 118 5Tom 102 6Lucy90 7Lucy34 8John122 Let’s find the total score obtained by all players. Solution: SELECT SUM(score) as sum_score FROM game; Here’s the result: Read more 26 Nov 2019 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. Read more 26 Nov 2019 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: Read more 25 Nov 2019 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. Read more 25 Nov 2019 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. Read more 25 Nov 2019 How to Multiply Two Columns in SQL Problem: You 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. Read more 25 Nov 2019 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. Read more 25 Nov 2019 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. Read more 25 Nov 2019 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 3steakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products. Read more 25 Nov 2019 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. Read more 25 Nov 2019 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. Read more 20 Nov 2019 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. Read more 19 Mar 2019 Dorota Wdzięczna 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. Read more 19 Feb 2019 Dorota Wdzięczna 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. Read more 17 Jan 2019 Roman Pijacek 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. Read more 8 Oct 2018 Dorota Wdzięczna 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. If you want to learn T-SQL, the dialect of SQL used in SQL Server, check out our SQL from A to Z in MS SQL Server track. Read more