Sql Server

LearnSQL.com Team

SQL Server Cheat Sheet

Welcome to the SQL Server Cheat Sheet! Whether you're just starting out with SQL Server or you're a seasoned developer looking for a quick reference, this guide is tailored for you. This cheat sheet is a comprehensive guide to SQL Server, offering a quick reference to its essential commands. It covers the basics of creating and displaying databases and tables, the commands to modify tables, and the fundamental syntax for T-SQL commands such as SELECT, INSERT, UPDATE, and DELETE.

Jill Thornhill

How to Learn T-SQL Querying

T-SQL is the dialect of SQL used by Microsoft SQL Server databases. Find out the best way to learn T-SQL querying with interactive online courses. In today's data-driven world, increasing your database knowledge is always a good choice. Microsoft SQL Server is one of the most popular databases nowadays. Knowing T-SQL – the query language of SQL Server databases –is often recommended as a worthwhile skill for careers in IT, business, or research fields.

Nicole Darnley

How to Write a Recursive CTE in SQL Server

A guide to understanding and using recursive CTEs in SQL Server SQL Server offers a lot of powerful tools for working with data, including Common Table Expressions (CTEs). A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can break down long queries into smaller, more manageable pieces of logic and make them more readable. SQL Server offers recursive Common Table Expressions.

Martyna Sławińska

How to Export Data From Microsoft SQL Server to a CSV File

When working with data and databases, it is common to export data for further processing or transport to another database. Follow this article to learn how to export data from Microsoft SQL Server to a CSV file. In this article, we first recall what a CSV file is and why you would want to export data in this format from an MS SQL Server database. Then, we cut to the chase and export data to a CSV file using both SQL Server Management Studio (SSMS) and SQL Command Line (SQLCMD).

Tihomir Babic

An Overview of Microsoft SQL Server Text Functions

Data analysts use text values when creating SQL reports. You can create your own labels with functions, classify rows based on text value, or do computations based on it. This article covers how to do all that. The text values in databases are not just for reading. As with numeric values, we perform operations on them and manipulate the text. SQL Server has a fair number of text functions.

Jakub Romanowski

Top 5 MS SQL Server Courses for Beginners

Every journey, including a career change, begins with the first step. If you're thinking about learning data analysis or strengthening your MS SQL Server skills arsenal, you've come to the right place. Here are the top 5 MS SQL Server online courses. Enjoy! You've probably heard that today’s organizations rely heavily on data analysis. You heard right. They are looking for employees who can navigate the world of databases and draw conclusions from data.

Jakub Romanowski

Free SQL Course of the Month – Creating Basic SQL Reports in SQL Server

This is another in our series of free SQL Courses of the Month. This time, you can learn SQL Reporting in MS SQL Server for free. Do you want to be able to prepare even better SQL reports? Or maybe you have some basic knowledge of SQL Server (one of the major SQL dialects) and would like to go a step further? This is the course for you.

Jakub Romanowski

Where to Practice JOINs in MS SQL Server

Are you tired of looking for exercises that will help you master SQL JOINs in MS SQL Server? Find out about an online course that will take your JOINs skills to the next level! There are tons of MS SQL Server courses out there. Some are good, although they often lack the most important thing ... the possibility of practicing what you learn with interactive exercises. This is one of the main reasons I advise people (especially beginners) not to learn SQL exclusively from YouTube clips.

Agnieszka Kozubek-Krycuń

How to Group Data by Week in SQL Server

This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server. In this course, we showed you how you can analyze the customer lifecycle (customer acquisition, conversion, activity, retention, and churn) with SQL. We discussed customer registration cohorts, or groups of customers who registered during the same period (e.g. same week, same month). Analyzing customer registration cohorts lets you see registration trends and relate registration cohorts with marketing campaigns.

Belma Mesihovic

Grouping Data in SQL Server

People who work with data know that data grouping can sometimes be a chore. If you struggle and waste hours with Excel, OpenOffice, or some other tool trying to group various sets of data, take a look at this article and learn how to do the work much quicker and easier by using SQL. You often find yourself in a situation where you need to analyze data and present the results in a form other than the one in which the data is stored.

Jeffrey J. Keller

Grouping, Rolling, and Cubing Data

The first two articles in this series highlighted SQL Server's ability to organize data into user-defined windows and its aggregate functions. Part 3 will focus on other methods of aggregating and organizing data using built-in SQL Server features – specifically, its grouping functions. For consistency, the same base data will be used as in the first two parts of this series. CREATE TABLE WindowTable (WindowID INT IDENTITY, House Varchar(32), FullName Varchar(64), PhysicalSkill Decimal(4, 2), MentalSkill Decimal (4, 2)) GO

Jeffrey J. Keller

Window Functions: Part Two: Aggregating Data

In part one of this series on SQL Server window functions, the functionality of the OVER() clause was discussed. This article will focus on aggregate functions. The majority of these can be used in conjunction with OVER. Some, like SUM() and AVG(), are widely used. Others — including VARP() and CHECKSUM_AGG() — are not as well-known, but are potentially quite useful. OVER defines the user-specified range on which a function is applied.

Jeffrey J. Keller

Window Functions in SQL Server: Part One: The OVER() Clause

Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row. Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as LAG, LEAD, RANK, CUME_DIST, and many others.

Patrycja Dybka

Basic Date and Time Functions in MS SQL Server

As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server. Let’s start with functions that extract a year, month and day from a given date. declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day SQL Server uses GETDATE() and SYSDATETIME() to get a current date and time.

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.

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.

How to make a column unique in SQL?

Problem: You would like to make a given 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 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 definition column (name VARCHAR(100) UNIQUE).

How to Use LIKE in SQL

Problem: You’d like to find a substring matching a pattern in a string. Example: Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1AlanWatsonMadrid 2LisaBlackSingapore 3LauraWilliamsSeattle 4MilanBrickSeoul Let’s see some examples of pattern matching in SQL. Example 1: Let’s display the first name, last name, and city of each customer whose city name starts with the letter 'S.

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.

How to Round Up a Number to the Nearest Integer in SQL

Problem: You want to round up a number to the nearest integer. 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.

How to Round Numbers in SQL

Problem: You want to round a number to a specific number of decimal places. 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.68 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.

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.

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:

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.

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.

How to Get the Day from a Date in T-SQL

Problem: You’d like to get the day from a date field in a SQL Server database. 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. We’ll get it from the VisitDate field.

How to Get the Current Date and Time (Without Time Zone) in T-SQL

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).

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.

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.

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.

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 Delete a Foreign Key Constraint in SQL

Problem: You want to drop 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 1 (new table): ALTER TABLE student DROP CONSTRAINT fk_student_city_id; Discussion: To drop 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.

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 (new table): 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.

How to Convert a String to Uppercase in SQL

Problem: You would like to convert a string to uppercase. 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.

How to Convert a String to Lowercase in SQL

Problem: You would like to convert a string to lowercase. Example: Our database has a table named product with data in the id and name columns. idname 1Cobb Salad 2Pot roast 3Jerky 4BANANA SPLIT 5CORN bread 6chicken fried Steak Notice that the naming styles are inconsistent for these products.

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.

How to Calculate a Square Root in SQL

Problem: You want to find the square root of a number. 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 1: SELECT number, SQRT(number) AS square_root FROM data; The result is:

How to Add Ranking Positions to Rows 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.

How to Add Ranking Positions of Rows in SQL with RANK()

Problem: You want to rank records by skipping ranking positions after rows that have the same rank. Example: Our database has a table named championship with data in the following columns: id (primary key), user_name, and score. iduser_namescore 111John12 112Mary23 115Lisa45 120Alan23 221Chris23 Let’s display all users’ names and their scores sorted in descending order and ranked by score.

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 LT20302019-02-20 GH11002019-03-01 SR54672019-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.