I work on a MacBook Pro M1 with a 16" display that has been a great workhorse for me for years. Using a containerized image of Microsoft SQL Server makes it possible to run an entire SQL Server on my aging MacBook.
As in 2020, it's very handy to have a SQL instance on your local machine. I do this kind of stuff with a local sql database instance:
data modeling
demonstrations
development
quick proof of concept
sandbox development
disconnected work
The main difference in 2024 versus 2020, is that I'll be using Azure SQL Edge, rather than a full version of SQL Server. For most of my uses, this is sufficient. Besides, it requires less memory and disk space.
The particular image we'll use is published by Microsoft, and can be found here on DockerHub. This capability to run SQL Server on Macbooks comes from Microsoft's SQL Server on Linux efforts. Thank you.
Be aware that you can get a free Azure SQL database in Microsoft's Azure cloud, which might server your needs better. For me, this has a slightly different set of capabilities, and would be inconvenient with a spotty wifi connection, or while completely disconnected. (me at a coffee shop)
I did this several years ago, and used my local SQL instance extensively. I was able to get a full version of SQL Server 2019 running in about 5 minutes. I followed Bob Ward's Take the SQL Server Mac Challenge while I was listening to him speak at SQL Intersection in Las Vegas.
If you follow these instructions, it should be very easy. It wasn't as easy for me - I found out all the ways that don't work :-) I couldn't get a full SQL Server running, and I'm fairly certain it was because of Docker not fully supporting the M1 Apple Chip, even with latest version of Docker and Rosetta 2 emulation.
Hopefully my experience will save you some time. At the end, I'll talk about the choice of Azure SQL Edge, and its limitations.
Instructions
Here's the overview:
Get Docker
Open Terminal
Execute a long-ish docker command
Verify via Docker Desktop
Connect to the instance from a client in the host machine
1. Docker Installation
Head over to Docker and download the Docker Desktop for Mac. It offers both Intel and Mac chip versions of the product. I always have to think about this and look - my M1 has an Apple chip. I confirm this by clicking the Apple image in the upper left corner of my desktop and choosing About This Mac.
<brief gif demo here>
Get the stable version for whichever platform you are on. I used version 4.30.0.
Follow the straightforward instructions.
2. Open a terminal window
Open up a Mac terminal window.
<brief gif demo here>
3. Run Docker Command
All the pieces of this command are necessary. This command fetches the container image and uses it to create a container, all in one command.
To show what's going on, I'll break it down. Here's the basic command:
docker run mcr.microsoft.com/azure-sql-edge
This basic command will execute without errors, but will not do what we want without the other specified options. This basic command says "create a container from the image at this location". If you haven't already downloaded it, it will go fetch the image.
Here's the full command again, broken into multiple lines. Note the forward slash continuation character at the end of each line:
docker run /
-e "ACCEPT_EULA=Y" /
-e "MSSQL_SA_PASSWORD=ABCabc123" /
-p 1433:1433 /
--name sqledge /
--hostname sqledge /
-d /
mcr.microsoft.com/azure-sql-edge
You can see that the first and last line above contain the basic command. All the other lines are options. The "-e" options are options that are passed to the "environment", i.e., they are passed into the container. The other options are for Docker at the host level to say how we want the container set up.
-e "ACCEPT_EULA=Y"
This option indicates your acceptance of the End User License Agreement for Azure SQL Edge. You gotta accept to use it :-) Note that Docker ignores this option - it just passes it into the container.
-e "MSSQL_SA_PASSWORD=ABCabc123"
This option sets the password for the database's system administrator account. This will be how we log in to the database in the container. Choose whatever password you would like.
If you're not familiar with SQL Server, traditionally, a user with username "sa" was set up to be the "root" or "highly privileged" user of the database. In today's world with actual production servers, this is a sufficiently secure way to operate. But for a local non-production network-disconnected instance, this is sufficient. And we won't have to set up the tighter security mechanisms. Again, Docker ignores this option - it just passes it into the container.
-p 1433:1433
This option maps port 1433 inside the container to port 1433 in the host.
Remember that there are two machines involved. The host (my Macbook running Docker), and the virtual machine (the container with Azure SQL Edge running in it.) Inside the container, Azure SQL Edge will be listening on port 1433 (that's the default port). Docker will connect the host's port 1433 to the client's port 1433. So if I connect a database client like Azure Data Studio to my Macbook's port 1433, I will be able to communicate between Azure Data Studio (in the host) and Azure SQL Edge (inside the container).
In a more complex setup, it is possible to set up multiple containers with SQL instances (or other servers) and map them at the host level with Docker to create a system of servers.
-name sqledge
This gives the container a Docker name. You can now refer to this container by name with Docker commands.
--hostname sqledge
This sets the hostname inside the container. Note that this does not change anything at the host level (in this case, my Macbook).
You might ask, well isn't this an environment thing? Why doesn't this start with "-e"?
Well, sort of. All virtual machines will need a hostname, so Docker provides a standard way to set it. However, not all virtual machines will need an sa password. So items that are specific to a particular container image are passed as -e options.
-d
This tells Docker to run this as a "disconnected" virtual machine, which does not have a user interface or window. We won't see a window for this container on the host. It will be running in the background.
4. Verify via Docker Desktop
If you've run the command in the previous section, you should have a running container aka virtual machine based on the mcr.microsoft.com/azure-sql-edge image.
You should be able to see this listed in Docker Desktop.
And you should also be able to see it by running this command in the terminal.
docker ps
Which gives as output:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
79e76f16d903 mcr.microsoft.com/azure-sql-edge "/opt/mssql/bin/perm…" 4 hours ago Up 4 hours 1401/tcp, 0.0.0.0:1433->1433/tcp sqledge
Which gives this info, reorganized:
CONTAINER ID 79e76f16d903
IMAGE mcr.microsoft.com/azure-sql-edge
COMMAND "/opt/mssql/bin/perm…"
CREATED 4 hours ago
STATUS Up 4 hours
PORTS 1401/tcp, 0.0.0.0:1433->1433/tcp
NAMES sqledge
5. Connect using a Database Client
I'll use Microsoft's Azure Data Studio (ADS), which runs on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS:
A couple of things to point out in this connection dialog:
the Server has a comma between the IP and the port, i.e., localhost, 1433
the Authentication type is SQL login, which would not be very secure for a production server
the User name is sa
the Password will need to be the password from the docker command. in our case "ABCabc123"
the Server Group is a convenience for ADS, which allows you to put connections in groups
the other entries have been left at the defaults
And here is a query that has been executed with ADS:
Just to be clear about what is going on:
Azure Data Studio (ADS) is running on the host, my Macbook
ADS is connected to port 1433 on the localhost IP address, localhost is my Macbook (localhost IP is typically 127.0.0.1, which works also)
Two-way communication is mapped from localhost:1433 to/from sqledge:1433
The SQL Server in the container is listening to/sending on port 1433
6. Shut it down
You can, of course, leave this container running in the background and use it whenever you need it. But it does continue to use memory and a bit of cpu, even when idle.
You can stop and start the container using the Docker Desktop application. Or, you can issue these commands in a terminal window:
docker stop sqledge
docker stop sqledge
To completely remove the container from the host machine (it gets deleted from the hard drive) you can issue this command:
docker rm sqledge
Azure SQL Edge local vs SQL Server local vs Azure SQL Server
I thought quite a bit about what I needed to do, and which setup(s) would do everything I need. Here's my thoughts on capabilities and effort with each of these setups.
My main concern with the free Azure SQL Server (managed) database is the security overhead and need for constant connectivity. It's just easier to use SQL Authentication than the authentication required by Azure/AWS.
Here's what I like about a local Azure SQL Edge instance:
simple authentication
can create, start, stop, remove, clone containers easily
can do these things:
all data definition language commands (create, alter, drop)
all data manipulation language commands (SELECT, INSERT, UPDATE, etc.)
special data types such as HierarchyID, Spatial, Full Text, FileStreams, In Memory OLTP, JSON (limitations), etc.
special features such as Replication Polybase, Snapshots, Linked Servers, CLR, High Availability, etc.
auxiliary server product capabilities: Analysis Services, Reporting Services, etc.
Summary
I think this will serve my needs for doing basic demos, T-SQL programming, data modeling and proof-of-concept experimentation. I hope this is helpful to you!
This post shows how to run a SQL Server Instance on about any computer using Docker Containers. Your next step might be to get a sample database into that SQL Server Instance.
Thanks to Microsoft, you can get their sample databases as T-SQL scripts. You can use these to install these databases on whatever server you are connected to, including your "containerized" SQL Server instance.
Let's do the Northwind database. We'll use Azure Data Studio, since it works on Win/Linux/Mac.
Here are the steps:
Click on this link, which will open up the text of the instnwnd.sql file.
Copy all that code - I used ctrl-a to select it all, then ctrl-c to copy all of that into the clipboard.
Open up Azure Data Studio (ADS)
Right-click on your database server, and choose New Query
Paste all the code into the query window - I used ctrl-v to do the paste from the clipboard
Now click Run
The image above shows my docker container instance running on localhost port 1401, which is mapped to my docker container's localhost port 1433 (as explained in the prior post.)
Depending on your machine, this may take several minutes to run, and will give many output messages. Depending on the version of SQL Server that you are running, you are likely to get a few harmless error messages and warnings like "Tokenization is skipped for long lines ...".
Now right-click on your Databases folder and choose Refresh to see the Northwind database, with all its data.
You can do this same procedure with any of the sample databases available here.
I've found it very convenient to have a local instance of SQL Server. I use it
for:
quick tests
performance testing
demonstrations
development
testing
work around limitations of a cloud instance
This has become really easy with containers. Even if you have the ability to install SQL Server natively on the host (Windows or Linux), it is much quicker and easier to use containers.
This means that you can run an instance of SQL Server on Windows, Linux, and Mac.
The first time I did this, I followed Bob Ward's Take the SQL Server Mac challenge, and had a SQL instance running on my Macbook, while in the audience listening to Bob's presentation. Yep, under 5 minutes.
So here is my pared-down How-To. I've tried to do a minimal setup that should work on Win, Linux, Mac, with very little adjustment.
Here's the overview:
Download, install, and run Docker
Get to an (elevated?) command prompt
Run a long-ish docker command
Verify via Docker
Connect from Host with client
Shut it down
1. Docker Installation
Head over to Docker and download Docker Desktop. I recommend the Stable version for whichever platform you are on:
Follow the installation instructions for your platform. This varies slightly for the platform you are on. If you can install a program on your machine, you can do this.
2. Command Prompt
For Mac, use the Terminal Application to get a command line. Here's how.
For Windows, type cmd in the search area, and choose "Run As Administrator". Here's how. Administrator privileges are not required for everything, but use it this first time to make sure you can get it working.
3. Run Docker Command
This is a long-ish command, but all the pieces are necessary. There are various ways to do this, but i'm using a "quick" method found in Microsoft's documentation. This method both pulls the SQL Server 2019 container image, and runs it with the correct parameters. Here's the command that I used:
Either of the above should work via cut-and-paste, but I find it easier to read and visually verify the one-parameter-per-line version.
The basic command (without options, which won't work) is:
docker run mcr.microsoft.com/mssql/server:2019-latest
This creates a process that runs the image mcr.microsoft.com/mssql/server:2019-latest. If that image happens to be on the local machine, great. If not, it will download the image from mcr.microsoft.com.
Note the "tag" at the end of the image url: 2019-latest. Microsoft releases many container images from which you can choose. Find them all on DockerHub. With this tag, are basically saying, get the latest stable release of SQL Server 2019.
That basic command will not create a usable sql server container. You need some additional pieces:
-e "ACCEPT_EULA=Y"
passes "Y" to SQL Server, indicating acceptance of the End User License Agreement (EULA)
-e "SA_PASSWORD=123ABCabc"
passes the "sa" user password to SQL Server
you will log in to this server using SQL Authentication, with "sa" as the username, and "123ABCabc" as the password
I'm using "123ABCabc" because it satisfies the password policy requirements (9 chars, upper and lower, digits"
"sa" is short for system administrator, and is basically the root user on the server instance that you are running
-p 1401:1433
maps the container's port 1433 to your machine's port 1401
SQL Server "listens" for connections on this port by default
So if you connect to your host machine's port 1401, you will be connected to the container's port 1433
in my situation, I am running a SQL instance on the host that is already using the default port 1433.
--name sqlsrvr1
this gives the container your desired name, rather than a docker-generated name
when issuing commands on the host, you can refer to the container by this name
-h sqlsrvr1
this sets the HOSTNAME environment variable inside the container, rather than the docker-generated name
when issuing commands inside the container, you can refer the system inside the container by this name
-d
this runs the container "detached"
this means that the container will run in the background rather than the foreground
4. Verify via Docker
If you've run the command in the previous section, you should have a running container based on the mcr.microsoft.com/mssql/server:2019-latest image. Run this at the command line to see it:
docker ps
This command gives a list of the running containers. You should see results something like this:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d325650811b1 mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 43 minutes ago Up 43 minutes 0.0.0.0:1401->1433/tcp sqlsrvr1
The results indicate that the container is running, with the proper name, and listening on the proper port
Verify via Connection
Now fire up a SQL client to connect to the container, and use it. I'm using Azure Data Studio (ADS), which will run on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS
Just to be clear:
ADS is running on the host
it is connecting to the host IP address (localhost)
it is connecting to the host Port 1401
Docker has mapped host port 1401 to the container's port 1433, which is the default port for SQL Server
So ADS will be connected to the container's port 1433
Note the Server text box has "url, port" - there's a comma between the url and the port
You should see that your container is shown in the Server Explorer on the left-hand side of the ADS window.
6. Shut it down
You can, of course, leave this container running in the background and use it whenever you need it. But what if you want to shut it down? Or entirely get rid of the container? Here's how to do it:
docker stop sqlsrvr1
This stops the container. It will no longer be running in the background. The container is still there, and can be restarted like this:
docker start sqlsrvr1
To entirely remove the container from the host machine (it cannot be started, and will no longer take up space on your host's drive), you would issue this command on the stopped container:
docker rm sqlsrvr1
Summary
Docker can seem overwhelming at first, especially if you are not comfortable working at the command line. But if you are looking to run a local instance of SQL Server, running a Docker container is the fastest path.
It's a deep dive and much of the hands-on content is going over my head. But I am really appreciating the context, motivation, and use cases described. If you get a chance to hear Michele Bustamente speak, take it.
I am no expert on these topics, and much of it is a real departure from common design patterns back when I was a System Architect.
Domain Driven Design (DDD) and Microservices seem to fit well when there is a need to scale out and provide capacity elasticity. So it ties with containers and orchestration. Here are some succinct statements that I wish someone had written down for me.
Orchestration - management of containers, resources, and their relationships through elastic operations, i.e. adding or removing containers or resources
relates to domains sharing resources across domains
one domain/microservice (A) might have high levels of writes, while another (B) might have high levels of reads. This could be architected as separate data stores, with A writes going to a write-optimized store, and changes being replicated (eventually) on B's read optimized store
Summary
The current use case is for high volume systems that must be elastic. But the idea of smaller, focused, agile, domain driven teams is compelling. With well-scoped smaller domains, it should easier to achieve smooth CI/CD, embrace re-factoring when necessary, stay focused on domain issues, and deliver high-quality software. As the tooling gets better, the overhead involved in containers, orchestration, and distributed microservice coordination should make this even more compelling.
-- Database by Doug
-- Douglas Kline
-- 1/30/2019
-- the SwitchOffset function
-- how to use switchoffset
-- function available beginning SQL 2008
-- see "Time Zones and DATETIMEOFFSET" video
-- an update to the previous video
-- thanks to a viewer who pointed this function out to me
-- you know who you are!
SELECT GETDATE() AS [now, somewhere]
-- my time zone is EST -05:00
-- the server is in the Azure US east data center (EST)
-- note that the GETDATE() returns the time GMT, i.e. -00:00
-- the returned time is 5 hours in the future (based on EST)
-- also note that GETDATE() does not contain the time zone,
-- it returns a datetime, which does not contain time zone information
SELECT SQL_VARIANT_PROPERTY(GETDATE(), 'BaseType')
-- you can the server datetime with time zone like this:
SELECT SYSDATETIMEOFFSET(),
SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'BaseType')
-- this result proves that Azure SQL returns UTC 00:00
-- my current database server happens to be
-- in the eastern time zone of the US, which is -05:00 UTC
-- so what is the actual time, in EST?
-- observe the difference between the following values
SELECT GETDATE() AS [Azure datetime GMT],
CAST (GETDATE() AS DATETIMEOFFSET) AS [converted Azure datetime GMT],
TODATETIMEOFFSET(GETDATE(), '-05:00') AS [todatetimeoffset result EST], -- but note no hour change
SYSDATETIMEOFFSET() AS [sysdatetimeoffset EST],
SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00') AS [switchoffset] -- this is the right one
-- note that GETDATE() is not as accurate
-- for a couple of reasons
-- fewer decimal points
-- but also
-- datetimes' one-thousandths place is always 0, 3, or 7
-- from doc'n "Rounded to increments of .000, .003, or .007 seconds"
-- so, before SWITCHOFFSET existed, ...
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00') AS [EST the easy way],
TODATETIMEOFFSET(DATEADD(HOUR, -5, SYSDATETIMEOFFSET()), '-05:00') AS [EST the hard way]
-- so, thinking of a DATETIMEOFFSET data type as a complex object
-- with many different parts: year, month, day, hour, time zone, etc.
-- it looks like SWITCHOFFSET changes two things: time zone and hour
-- but let's say that my source datetimeoffset
-- is near a time part boundary,
-- for example, the end of the year
DECLARE @NewYearsEveEST AS DATETIMEOFFSET
DECLARE @NewYearsEveGMT AS DATETIMEOFFSET
SET @NewYearsEveEST = DATETIMEOFFSETFROMPARTS(2019,12,31,23,50,0,0,-5,0,7)
SET @NewYearsEveGMT = SWITCHOFFSET(@NewYearsEveEST,'+00:00')
SELECT @NewYearsEveEST AS [NYEveEST],
@NewYearsEveGMT AS [NYEveGMT]
-- note that the year, month, day, hour, and time zone changed
-- in summary
-- SWITCHOFFSET is really helpful to have
-- simpler code, likely more reliable
-- use SYSDATETIMEOFFSET to get max precision w/Offset
-- Database by Doug
-- Douglas Kline
-- 1/30/2019
-- the SwitchOffset function
-- Database by Doug
-- Douglas Kline
-- 11/10/2018
-- Understanding Relational Division - a (relatively) simple example
USE JobSkills
-- show the diagram of the data model
-- explain
-- here are our applicants (note - 4 of them)
SELECT *
FROM Applicant
-- here are our skills (note - 4 of them)
SELECT *
FROM Skill
-- now what if every applicant had every skill
-- that would mean that there would be 16 (4 x 4)
-- records in the ApplicantSkill table
-- it would look something like this:
SELECT *
FROM Applicant
CROSS JOIN Skill
ORDER BY Applicant.applicantID,
Skill.skillID
-- if you haven't seen this before,
-- this is a cross join
-- a cross join of two tables creates all possible combinations
-- of the records in the two tables
-- with no condition - no requirement on lining up FKs and PKs
-- this is known as the *cartesian product* of all records in both tables
-- and you might recall that *product* is another way of saying
-- 'the result of multiplication'
-- in other words, CROSS JOIN is kind of *relational multiplication*
-- sort of (Applicant records) x (Skill records)
-- think of this in math terms a * b = c
-- where a is Applicants
-- b is Skills
-- and c is the result
-- now if you remember your algebra,
-- if a * b = c
-- that means that c / b = a
-- in our context 4 records times 4 records equals 16
-- so 16 divided by 4 should equal 4
-- let's try to do that!
-- I'm going to use a common table expression (CTE),
-- so you can tell there's nothing up my sleeve!
-- note that this is exactly the CROSS JOIN from above
; WITH [c] (applicantID, lastName, skillID, skillName)
AS
(
SELECT *
FROM Applicant
CROSS JOIN Skill
)
SELECT applicantID,
lastName
FROM [c]
GROUP BY applicantID,
lastName
HAVING COUNT(skillID) = (SELECT COUNT(*) FROM Skill)
-- this could also be rephrased as a subquery like this:
SELECT applicantID,
lastName
FROM (
SELECT *
FROM Applicant
CROSS JOIN Skill
) [c]
GROUP BY applicantID,
lastName
HAVING COUNT(skillID) = (SELECT COUNT(*) FROM Skill)
-- the logic is that I'm showing all applicants
-- that have the same number of skills
-- as are in the skill table
-- so if CROSS JOIN is the "relational multiplication" operator
-- what is the "relational division" operator?
-- unfortunately, there is no relational division operator
-- that's why we are using HAVING and COUNT to get the logical equivalent
-- summary so far
-- 3 applicants CROSS JOIN 4 skills = 12 results, e.g., everyone has every skill
-- then 12 results divided by 4 skills = 3 applicants, the original applicants
-- but this is maybe an unrealistically simple example
-- not all applicants will have all skills
-- what we'll have in reality is a job that requires a certain set of skills
-- then we'll have applicants that each have a certain set of skills
-- and what we want is a set of applicants that whose skills *match* the requirements
-- here is the data model
-- let's start simply, with a single job that requires two skills
-- and a set of applicants that each have a set of skills
-- I've cooked some data for that...
-- examining jobskill...
SELECT Job.jobID,
Job.jobName,
Skill.SkillID,
Skill.SkillName
FROM Job
JOIN JobSkill ON Job.jobID = JobSkill.jobID
JOIN Skill ON JobSkill.skillID = Skill.skillID
WHERE Job.JobID = 10
ORDER BY Job.jobID,
Skill.SkillID
-- this says that the software developer job
-- requires the sql and javascript skills
-- now let's cook up some applicants with some skills
SELECT Applicant.applicantID,
Applicant.lastName,
Skill.skillID,
Skill.skillName
FROM Applicant
JOIN ApplicantSkill ON Applicant.applicantID = ApplicantSkill.applicantID
JOIN Skill ON ApplicantSkill.skillID = Skill.skillid
ORDER BY Applicant.applicantID,
Skill.skillID
-- so it looks like viable candidates are Jones and Brown
-- Jones has exactly the right skills and no more
-- Brown has the right skills, plus one
-- so to find a candidate that satisfies our job...
-- a * b = c
-- candidateSkills = jobskills
-- candidates * skills = jobskills
-- candidates = jobskills / skills
-- this analogy is not perfect, in that * is not really a cross join
-- and candidateskills and jobskills are actual records,
-- for which we are trying to find alignment via joins
--
-- we'll solve this with the COUNT method we used before
SELECT Applicant.applicantID,
Applicant.lastName,
COUNT(jobSkill.SkillID) AS [MatchingSkillCount]
FROM ApplicantSkill
JOIN JobSkill ON ApplicantSkill.skillID = JobSkill.SkillID
JOIN Applicant ON Applicant.ApplicantID = ApplicantSkill.ApplicantID
WHERE JobSkill.JobID = 10
GROUP BY Applicant.applicantID,
Applicant.lastName
HAVING COUNT(jobSkill.SkillID) = (SELECT COUNT(*) FROM JobSkill WHERE JobSkill.jobID = 10)
ORDER BY Applicant.applicantID
-- note that the outer query limits the applicant skill count to only
-- skills that match exactly the skills for this job
-- and the subquery does the same
-- let's look at this in a more general sense:
SELECT Job.jobID,
Job.jobName,
Skill.SkillID,
Skill.SkillName
FROM Job
JOIN JobSkill ON Job.jobID = JobSkill.jobID
JOIN Skill ON JobSkill.skillID = Skill.skillID
ORDER BY Job.jobID,
Skill.SkillID
-- two jobs, each of which require two skills
-- disjoint skill set
-- same set of applicants
SELECT Applicant.applicantID,
Applicant.lastName,
Skill.skillID,
Skill.skillName
FROM Applicant
JOIN ApplicantSkill ON Applicant.applicantID = ApplicantSkill.applicantID
JOIN Skill ON ApplicantSkill.skillID = Skill.skillid
ORDER BY Applicant.applicantID,
Skill.skillID
-- and the number of matching applicant skills for each job
SELECT Applicant.applicantID,
Applicant.lastName,
Job.JobID,
Job.jobName,
COUNT(ApplicantSkill.SkillID) AS [MatchingJobSkillCount]
FROM ApplicantSkill
JOIN JobSkill ON ApplicantSkill.skillID = JobSkill.SkillID -- this is *key*
JOIN Job ON Job.jobID = JobSkill.JobID
JOIN Applicant ON Applicant.ApplicantID = ApplicantSkill.ApplicantID
GROUP BY Applicant.applicantID,
Applicant.lastName,
Job.JobID,
Job.jobName
ORDER BY Applicant.applicantID,
Job.JobID
-- now we'd like to limit this to the applicants with jobskill count
-- equal to the jobskill count of each job
-- the trick here is that the the HAVING subquery must be correlated
SELECT Applicant.applicantID,
Applicant.lastName,
Job.JobID,
Job.jobName,
COUNT(ApplicantSkill.SkillID) AS [MatchingJobSkillCount]
FROM ApplicantSkill
JOIN JobSkill ON ApplicantSkill.skillID = JobSkill.SkillID -- this is *key*
JOIN Job ON Job.jobID = JobSkill.JobID
JOIN Applicant ON Applicant.ApplicantID = ApplicantSkill.ApplicantID
GROUP BY Applicant.applicantID,
Applicant.lastName,
Job.JobID,
Job.jobName
HAVING COUNT(ApplicantSkill.SkillID) = (SELECT COUNT(js.skillID)
FROM JobSkill js
WHERE js.jobID = job.jobID)
ORDER BY Applicant.applicantID,
Job.JobID
-- perhaps this is clearer as a CTE:
; WITH [JobWithSkillCount] (jobID, skillCount)
AS
(
SELECT jobID,
COUNT(skillID)
FROM JobSkill
GROUP BY jobID
)
SELECT Applicant.applicantID,
Applicant.lastName,
Job.JobID,
Job.jobName,
COUNT(ApplicantSkill.SkillID) AS [MatchingJobSkillCount]
FROM ApplicantSkill
JOIN JobSkill ON ApplicantSkill.skillID = JobSkill.SkillID -- this is *key*
JOIN Job ON Job.jobID = JobSkill.JobID
JOIN Applicant ON Applicant.ApplicantID = ApplicantSkill.ApplicantID
JOIN [JobWithSkillCount] ON [JobWithSkillCount].jobID = job.jobID
GROUP BY Applicant.applicantID,
Applicant.lastName,
Job.JobID,
Job.jobName,
JobWithSkillCount.skillCount
HAVING COUNT(ApplicantSkill.SkillID) = JobWithSkillCount.skillCount
ORDER BY Applicant.applicantID,
Job.JobID
-- hopefully, you find this a helpful example for understanding relational division
-- A couple last comments
-- I've presented this from the employer point of view:
-- applicants who match job requirements
-- but it is exactly equivalent form an applicant point of view:
-- jobs that match skillsets
-- I'm not showing the "remainder" of the division
-- for example, Brown actually has *extra* skills!
-- this example shows applicants with the exact matching skillsets
-- and some may have extra skills
-- this is not necessarily the *most efficient* way to
-- do relational division
-- my goal was to create an example for understanding relational division
-- an performant solution would of course depend on many things:
-- the data volume, its statistical distribution, available indexes,
-- the schema, etc.
-- I hope that you found this helpful!
-- Database by Doug
-- Douglas Kline
-- 11/10/2018
-- Understanding Relational Division - a (relatively) simple example
-- Database by Doug
-- Douglas Kline
-- 10/19/2018
--
-- Finding unmatched records
-- LEFT JOIN... WHERE IS NULL
-- suppose you want to find records in one table
-- that do not match records in another table
-- some practical examples:
-- products that are not in a category
-- (in Products table, but not in Categories)
-- Categories that have no products
-- (in Categories table, but not in Products table)
-- two main cases,
-- first case: look for the NULL
-- products that are not in a category
SELECT ProductID
FROM Products
WHERE CategoryID IS NULL
-- this is fairly simple, because
-- everything we need is in a single table
-- we don't need to inspect another table
-- this is when the Foreign Key is
-- in the table of interest
-- Categories that have no products
-- this is tougher
-- since which Categories to display
-- depends on what's in the Products table
-- here's the pattern, then we'll build up
-- to why it works
SELECT Categories.CategoryName
FROM Categories
LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.ProductID IS NULL
-- the pattern is
-- <table of interest> LEFT JOIN <unmatched table>
-- WHERE <unmatched table>.<primary key> IS NULL
-- consider this, normal join
SELECT Categories.CategoryName,
Categories.CategoryID,
Products.CategoryID,
Products.ProductID,
Products.ProductName
FROM Categories
JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Categories.CategoryName
-- note 77 records
-- now the LEFT JOIN
SELECT Categories.CategoryName,
Categories.CategoryID,
Products.CategoryID,
Products.ProductID,
Products.ProductName
FROM Categories
LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Categories.CategoryName
-- now 78 records
-- and note the Office Supplies category with no Product
-- the record has been replaced with a *NULL record*
-- the LEFT JOIN includes Categories without matching Products
-- Categories LEFT JOIN Products -- Categories is on the LEFT
-- how do I know that it's really a *NULL record*
-- and not just NULLs in the fields? which might be ok
-- because Products.ProductID is NULL, which can't happen for a PK
-- so, let's limit to just that record
SELECT Categories.CategoryName,
Categories.CategoryID,
Products.CategoryID,
Products.ProductID,
Products.ProductName
FROM Categories
LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.ProductID IS NULL
ORDER BY Categories.CategoryName
-- and paring it back to the simple form
SELECT Categories.CategoryName
FROM Categories
LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.ProductID IS NULL
ORDER BY Categories.CategoryName
-- Database by Doug
-- Douglas Kline
-- 10/19/2018
--
-- Finding unmatched records
-- LEFT JOIN... WHERE IS NULL
-- Database by Doug
-- Douglas Kline
-- 10/18/2018
-- Time Zones and DateTimeOffset
-- how to use time zones and convert from one to another
-- first, how to think about date/time/datetime in SQL
-- suppose I do this:
SELECT GETDATE() AS [now, somewhere]
-- the data type that is returned is a datetime
-- I can show this:
SELECT SQL_VARIANT_PROPERTY(GETDATE(), 'BaseType')
-- think of the datetime data type as a
-- multi-part object that holds each
-- part as a separate value
-- for example:
SELECT GETDATE(),
DATEPART(YEAR, GETDATE()) AS [year],
DATEPART(MONTH, GETDATE()) AS [month],
DATEPART(DAY, GETDATE()) AS [day],
DATEPART(HOUR, GETDATE()) AS [hour],
DATEPART(SECOND, GETDATE()) AS [second],
DATEPART(MILLISECOND, GETDATE()) AS [millisecond]
-- note that all the parts are whole numbers
-- DATEPART() always returns whole numbers - no decimals
-- and when we manipulate datetimes, we use whole numbers:
SELECT GETDATE() AS [now],
DATEADD(DAY, 2, GETDATE()) AS [2 days from now],
DATEADD(DAY, 2.5, GETDATE()) AS [2.5 days from now?],
DATEADD(HOUR, 12,
DATEADD(DAY, 2, GETDATE()))AS [2.5 days from now]
-- look at that last entry again
-- notice that 2.5 was truncated to an integer
-- there is no such thing as a fractional part of a datetime
-- regardless of how SQL Server stores the date/time/datetime
-- under the covers
-- it treats each part as a separate integer value
-- so what about time zones?
-- since datetime (and datetime2) doesn't have a time zone part
-- we need a new data type: datetimeoffset
-- you can the server datetime with time zone like this:
SELECT SYSDATETIMEOFFSET(),
SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'BaseType')
-- my current database server happens to be
-- in the eastern time zone of the US, which is -04:00 UTC
-- note that the datetimeoffset data type
-- has everything that datetime does
-- but additionally, it has another part, the time zone
-- so how would I show the actual east coast time?
-- you might think that the function TODATETIMEOFFSET
-- would do that for you, but all it really does is
-- add the time zone to a datetime
SELECT TODATETIMEOFFSET(GETDATE(),'-04:00') AS [time on east coast?],
SYSDATETIMEOFFSET() AS [time on east coast],
CAST(GETDATE() AS DATETIMEOFFSET) AS [time on east coast?]
-- note that the hours are the same
-- it didn't really move time zones for you
-- it just added the time zone information you gave it
-- to actually adjust the hours, you need to
-- actually add/subtract the hours
SELECT GETDATE() AS [implied time zone],
TODATETIMEOFFSET
(
GETDATE(),
'-00:00'
) AS [explicit UTC +00:00 time zone],
DATEADD(hour, -4, GETDATE()) AS [east coast time, implied time zone],
TODATETIMEOFFSET
(
DATEADD(hour, -4, GETDATE()),
'-04:00'
) AS [east coast with time zone]
SELECT GETDATE(), SYSDATETIMEOFFSET()
-- in summary
-- DATETIMEOFFSET is a DATETIME with additional information: the time zone
-- TODATETIMEOFFSET converts from DATETIME to DATETIMEOFFSET
-- but doesn't add or subtract time
-- to take a datetime from one time zone, and show it in another
-- timezone, you have to add/subtract the hours yourself
-- and set the correct time zone in a DATETIMEOFFSET
-- Database by Doug
-- Douglas Kline
-- 10/18/2018
-- Time Zones and DateTimeOffset
-- Database by Doug
-- Douglas Kline
-- 10/19/2018
-- ANY and ALL
-- comparing a value to a column of values
-- (aka acomparing a scalar to a vector of scalars)
-- consider this setup for testing
-- if the WHERE condition is true or false
SELECT 1 AS [isTrue?]
WHERE 10 = 10 -- testing if this is true or false
SELECT 1 AS [isTrue?]
WHERE 10 <> 10 -- testing if this is true or false
-- we get a record if it is true, otherwise no record
-- also, see how I can create a literal table
SELECT tempField
FROM (VALUES(11),(12),(7)) tempTable(tempField)
-- note that this creates a single column of values
-- which could be used in something like IN
-- for example
SELECT 1
WHERE 12 IN ( SELECT tempField
FROM (VALUES(11),(12),(7)) tempTable(tempField))
-- I could rephrase this as:
SELECT 1
WHERE 12 = ANY ( SELECT tempField
FROM (VALUES(11),(12),(7)) tempTable(tempField))
-- back to the first example:
SELECT 1 AS [isTrue?]
WHERE 10 > 11
-- I'm comparing a single scalar value, 10, with another
-- single scalar value, 11
-- now consider if I want to compare the value 10
-- to multiple other values
SELECT 1
WHERE 10 > 11
OR 10 > 12
OR 10 > 7
-- this is three logical expressions
-- combined with OR into the whole logical expression
-- so if ANY of them are true
-- the whole logical expression is true
-- now let's say that the values 11, 12, 7 are in a column
-- since we're in an RDBMS, they are *likely* to be in a column
-- I can rephrase this with ANY like this:
SELECT 1
WHERE 10 > ANY (SELECT tempfield
FROM (VALUES
(11),
(12),
(7)
) AS tempTable(tempfield))
-- this is exactly equivalent to:
SELECT 1
WHERE 10 > 11
OR 10 > 12
OR 10 > 7
-- back to the first example again...
-- back to the first example:
SELECT 1 AS [isTrue?]
WHERE 10 > 11
-- let's say I want to do multiple comparisons again
-- but AND them together like this:
SELECT 1
WHERE 10 > 11 -- logical expression 1
AND 10 > 12 -- logical expression 2
AND 10 > 7 -- logical expression 3
-- this is three logical expressions
-- combined with ANDs into the whole WHERE
-- logical expression
-- so all three expressions must be true
-- for the WHERE clause to be true
-- I can make it true with different values:
SELECT 1
WHERE 10 > 9 -- logical expression 1
AND 10 > 8 -- logical expression 2
AND 10 > 7 -- logical expression 3
-- I can rephrase this with ALL
-- with the values in a column
SELECT 1
WHERE 10 > ALL (SELECT tempfield
FROM (VALUES
(11),
(12),
(7)
) AS tempTable(tempfield))
-- and get it to be true with a change to the values
SELECT 1
WHERE 10 > ALL (SELECT tempfield
FROM (VALUES
(9),
(8),
(7)
) AS tempTable(tempfield))
-- so you can think of ANY and ALL
-- as comparison operator modifiers
-- comparison operators usually take a scalar value on each side
-- scalar scalar
-- 3 < 7
-- 3 > 7
-- 3 = 7
-- 3 != 7
-- 3 <> 7
-- 3 <= 7
-- 3 >= 7
-- ANY and ALL take a scalar on the left, and a column on the right
-- scalar ANY column
-- scalar ALL column
-- 3 < ANY ((1),(2),(3))
-- 3 >= ALL ((1),(2),(3))
-- and the column is generally created with a SELECT statement
-- scalar ANY (SELECT FROM...)
-- scalar ALL (SELECT FROM...)
-- so here's a more concrete example using the Northwind database Orders table
-- suppose we want to know a list of customers who paid more than
-- $200 on freight on an order in 1996?
-- in other words $200 < ANY(orders in 1996)
SELECT CompanyName
FROM Customers
WHERE $200 < ANY (SELECT freight
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND YEAR(Orderdate) = 1996)
ORDER BY CompanyName
-- notice that this is a correlated subquery
-- it refers to the outer query (Customers.customerID)
-- also notice that this could be rephrased
-- with a JOIN DISTINCT
SELECT DISTINCT Customers.CompanyName
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.Freight > $200
AND YEAR(Orders.OrderDate) = 1996
ORDER BY CompanyName
-- is there a difference? why one and not the other
-- it depends
-- depending on your situation, one SQL phrasing might
-- be clearer than another
-- depending on your data model, volume, statistics, indexes, etc.
-- one might be faster than the other
-- in other words, you might get different query plans
-- in general, I recommend writing your SQL in the clearest
-- manner possible
-- then carefully rephrasing to a better performing, but
-- perhaps less understandable, form if there is a performance issue
-- in summary,
-- ANY and ALL modify comparison operators
-- they succinctly compare a single scalar to
-- a column of scalars
-- thanks for watching!
-- Database by Doug
-- Douglas Kline
-- 10/19/2018
-- ANY and ALL
-- Database by Doug
-- Douglas Kline
-- 10/10/2018
-- CAST - converting to a new data type
-- sometimes you need to change one data type to another data type
-- consider this:
SELECT '4.0'
-- I might want to deal with this as a number
-- for example
SELECT '4.0' + 2.0
-- this works
-- even though they '4.0' is a varchar
-- and 2.0 is a floating point number
-- the db does an *implicit* conversion
-- of the '4.0' to a floating point
-- then does the addition
-- and returns a floating point
-- we hope the db "knows what I mean"
-- and are *assuming* it ends up doing the right thing
-- for simple things, this mostly works
-- the db is pretty smart
-- however, high quality code doesn't normally
-- make assumptions, so let's be *explicit*
SELECT CAST('4.0' AS float) + 2.0
-- here, I'm using the CAST function to
-- *explicitly* change a varchar to a float
-- I'm not relying on the db "knowing what I mean"
-- you can generally CAST between data types
-- fairly freely
-- see the full matrix of allowable conversions
-- for SQL Server 17
-- here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017
-- note that some to/from conversions are implicit / automatic
-- some are not allowed at all
-- and some require explicit CASTs
-- here are a few common conversions you might want to do
-- converting numeric and dates to varchars
-- especially when needing to concatenat
SELECT 'Doug' + 1 AS [trying for Doug1]
SELECT 1 + 'Doug' AS [trying for 1Doug]
-- and the fixes
SELECT 'Doug' + CAST(1 AS VARCHAR)
SELECT CAST(1 AS VARCHAR) + 'Doug'
SELECT GETDATE() + 'Doug' -- error
SELECT CAST(GETDATE() AS VARCHAR) + 'Doug'
-- forcing specific types of operations
-- consider this
SELECT 3 / 2
-- notice that I get *integer* division
-- because both operands are integers
-- but what if I want to see 1.5 as the result?
-- I can fix literals easy enough
SELECT 3.0 / 2.0
-- but what about this:
SELECT UnitsInStock / unitsonorder
FROM Products
WHERE unitsOnorder <> 0
-- I'm getting integer division
-- here's how to get floating point division:
SELECT CAST(UnitsInStock AS FLOAT) / CAST(unitsonorder AS FLOAT)
FROM Products
WHERE unitsOnorder <> 0
-- another common conversion is from varchar to date/time
-- see the differences here:
SELECT '4/8/2018'
SELECT CAST('4/8/2018' AS DATE)
SELECT CAST('4/8/2018' AS DATETIME2)
-- and here:
SELECT '20180408 11:00'
SELECT CAST('20180408 11:00' AS DATE)
SELECT CAST('20180408 11:00' AS DATETIME2)
-- summary
-- for 'throw-away' code, the db will implicitly convert
-- for high quality code in a system
-- you should not rely on implicit conversions
-- instead, use CAST
-- Database by Doug
-- Douglas Kline
-- 10/10/2018
-- CAST - converting to a new data type
-- Database by Doug
-- Douglas Kline
-- 10/9/2018
-- DISTINCT - removing duplicates, but in a "dumb" way
-- consider this
SELECT ProductID,
ProductName,
SupplierID
FROM Products
-- this is a list of all products
-- note the repeats in the SupplierID
-- suppose I want top know the list of suppliers
-- in the Products table
SELECT SupplierID
FROM Products
-- again, notice the repeats
-- to remove the repeats, I can do this:
SELECT DISTINCT SupplierID
FROM Products
-- you might be saying, why not just do this:
SELECT SupplierID
FROM Suppliers
-- my response is: that's a different list
-- it's the list of all suppliers in the supplier table
-- What I'm looking for is the list of all suppliers
-- in the products table
-- in other words, all suppliers which we actually *use*
-- Let's look at another example, with a table I've created
SELECT firstName
FROM Person
ORDER BY firstName
-- this is a list of all people's first names
-- as we scroll down, we'll start to see repeats
-- in other words, multiple people have the same first name
-- now let's say we want a list of all first distinct firstnames
SELECT DISTINCT firstName
FROM Person
ORDER BY firstName
-- so now there isn't a record returned for every Person record
-- there's a record returned for every unique firstname
-- also note that NULL is considered to be a unique firstname
-- you might say, why not use Group By to do this?
-- like this:
SELECT firstName
FROM Person
GROUP BY firstName
ORDER BY firstName
-- logically, it returns the exact same records, and always will
-- however, GROUP BY does a lot more work
-- it actually sets up groups of records in preparation to
-- calculate aggregates like SUM, COUNT, AVG, etc.
-- DISTINCT is much faster
-- if it sees a value it has seen before, it just throws it out
-- in other words, it doesn't group the records
-- it just makes a list of unique values
-- so, don't use GROUP BY when what you really need is DISTINCT
-- alright, what if you want to count stuff?
SELECT COUNT(ID) AS [# of people],
COUNT(DISTINCT ID) AS [# of distinct primary key values],
COUNT(firstName) AS [# of people with non-NULL firstnames],
COUNT(DISTINCT firstName) AS [# of distinct firstNames]
FROM Person
-- note that the first two values are always the same,
-- since primary key values are distinct aka unique
-- and also notice that there are 599 distinct first names, but recall
SELECT DISTINCT firstName
FROM Person
ORDER BY firstName
-- so why is the COUNT(DISTINCT firstname) = 599
-- but DISTINCT firstname gives 600 records?
-- remember that COUNT counts non-NULL values
-- finally, DISTINCT is sort of "dumb", in that it doesn't
-- know anything about primary keys
-- or anything about the underlying table(s)
-- it only considers values from the fields you provide
-- consider this:
SELECT DISTINCT firstname
FROM Person
-- it doesn't give distinct Person records, just distinct firstnames
-- now this:
SELECT DISTINCT lastname
FROM Person
-- and the distinct applies to the *combination*
-- of all the fields in the SELECT clause
-- in this example,
-- all distinct *combinations* of gender and firstname are shown
SELECT DISTINCT gender,
firstname
FROM Person
ORDER BY gender,
firstname
-- in summary,
-- distinct removes duplicates
-- it removes duplicates based on all fields in the SELECT list
-- when used with COUNT, it will not count duplicate values
-- thanks for watching!
-- Database by Doug
-- Douglas Kline
-- 10/9/2018
-- DISTINCT - removing duplicates, but in a "dumb" way
-- Database by Doug
-- Douglas Kline
-- 8/28/2018
-- Numeric Expressions - expressions that return a numeric data type
-- beginner level
-- simple numeric expression examples
-- and things to keep in mind
-- I might say 'db'
-- which is shorthand for 'database engine'
-- this is the server software that interprets
-- your SQL and performs actions
-- you can ask your database engine (db) to be your calculator:
SELECT 1 + 2
-- if you are familar with spreadsheets
-- and how to type formulas
-- it is very similar
SELECT 1 + 2
-- an expression is something that the db
-- will read, translate, and perform
-- so in the expression above
-- it reads a 1, then a plus sign, then a 2
-- interprets it as addition
-- performs the addition
-- and returns the results
--------------------------------------------------
SELECT 1 + 2
-- there are certain items in the expression
-- the plus sign (+) is an *operator*
-- more specifically, it is a *binary* operator
-- it defines an operation on two items (thus binary)
-- more specifically, the items on its left and right
-- the plus sign says to the db:
-- take the items on my left and right and add them
-- it is an instruction to the db
SELECT 1 + 2
-- the 1 and 2 are the *operands* of the operator
-- they are the items that the operator applies to
-- the 1 and 2 are also examples of *literals*
-- in other words 1 literally means the number 1
-- in coding, it means: not code, a value
-- here is an example of a unary operator (one operand)
SELECT -5
-- here, the literal value 5
-- is modified to become negative
-- by the minus sign operator
------------------------------------------------------------
-- how does the db know what *is* an expression, and what *isn't*
-- in this example
SELECT 1 + 2
-- the db is *expecting* an expression to be in this position
-- (after the word SELECT)
-- so expressions are mainly known by their
-- location/position in your SELECT statement
---------------------------------------------------------
-- notice that usual order of operations is followed
-- * and / come before + and -
-- otherwise it is left-to-right,
-- unless you add parentheses
-- then sub-expressions in parentheses are evaluated first
SELECT 1 + 2 * 6
SELECT (1 + 2) * 6
SELECT (3 - 1) / 2
------------------------------------------------------
-- notice that operators sometimes change meaning
-- depending on their context
SELECT 1 / 3 -- does integer division
SELECT 1.0 / 3.0 -- floating point division
SELECT 1 / 3.0 -- floating point division
SELECT 1.0 / 3 -- floating point division
-- if both operands (items on either side of the operator)
-- are whole numbers, do integer division
-- otherwise do floating point division
-----------------------------------------------------------------------------
-- here's an operator that might be new to you: %
SELECT 5 % 3 -- modulus operator - gives the remainder after integer division
SELECT 8 % 3 -- same answer
SELECT (8 / 3) * 3 + 8 % 3 -- equals 8
-----------------------------------------------------------------------------
-- what if your data is not a literal?
-- in other words, it comes out of the db
SELECT unitsInstock -- this is an expression, just very simple
FROM Products
SELECT unitsInStock + unitsOnOrder -- effective inventory
FROM Products
-- plus is still the operator
-- the operands are UnitsInStock and unitsOnOrder
-- the expression is evaluated on each record, separately
-----------------------------------------------------
-- but what if the data type is not right for
-- the operations I want to do?
-- specifically, the data is whole numbers
-- but I want to do floating point division
SELECT unitsInStock,
reOrderLevel,
unitsinstock / reOrderLevel
FROM Products
WHERE reOrderLevel <> 0 -- to avoid division by zero
-- but I'd really like to see the results as a floating point/decimal
-- you have to convert *prior* to dividing
SELECT unitsInStock,
reOrderLevel,
CONVERT(real,unitsinstock) / reOrderLevel
FROM Products
WHERE reOrderLevel <> 0 -- to avoid division by zero
-- the unitsInStock is converted to a real (aka floating point number)
-- before it gets divided
-- since one of the items operands
-- is a floating point number, it does floating point division
-------------------------------------------------------------
-- perhaps a more realistic and useful expression
SELECT ProductID,
ProductName,
unitsInStock * unitprice AS [Dollar Value in Inventory]
FROM Products
ORDER BY ProductID
-- note that unitprice is a money data type,
-- and the unitsInstock is a whole number
-- so the result is shown as a money data type
-- (note the 2 digits to the right of the decimal)
-- we can show this explicitly:
SELECT SQL_VARIANT_PROPERTY(unitPrice, 'BaseType'),
SQL_VARIANT_PROPERTY(unitsInStock, 'BaseType'),
SQL_VARIANT_PROPERTY(unitsInStock * unitPrice, 'BaseType')
FROM Products
-----------------------------------------------------------------------
-- in summary
-- expressions are instructions for the db to create new values
-- expressions are known by their location in the statement
-- expressions have operators and operands
-- Database by Doug
-- Douglas Kline
-- 8/28/2018
-- Numeric Expressions
-- Database by Doug
-- Douglas Kline
-- 8/28/2018
-- Character Expressions - expressions that return character data
-- beginner level
-- might want to check out numeric expressions
------------------------------------------------------------------
-- consider this statement
SELECT 'Fred',
'Flintstone'
-- note that the single quotes are not shown in
-- the table results
-- this is because that are not *part of* the data
-- they are used to mark the beginning and end of the data
-- and are called *delimiters*
-- so the statement has two pieces of data
-- delimited by single quotes
SELECT 'Fred',
'Flintstone'
-- the *type* of data is *character* data
-- more specifically, a *sequence of characters*
-- in programming, they would be called *strings*
-- another term might be *string literals*
-- now look at this:
SELECT 123 AS [numeric data type],
'123' AS [character data type]
-- the first column is a number
-- the second column is a sequence of characters
-- first the character '1', then the character '2', ...
-- now consider this
SELECT 1 + 5 AS [numeric],
'1' + '5' AS [character]
-- see how the plus sign means something different
-- depending on what's on either side?
-- with character data, the plus sign means *concatenate*
-- or 'put together'
-- so the basic operator for character expressions
-- is the plus sign
-- we can prove this:
SELECT SQL_VARIANT_PROPERTY(1 + 5, 'BaseType'),
SQL_VARIANT_PROPERTY('1' + '5', 'BaseType')
-- here'a another example:
SELECT 'Fred' + ' ' + 'Flintstone' AS [Full Name]
-------------------------------------------------------------
-- there's lots more we want to do with
-- character data
-- but we need functions....
-- here are a few simple ones:
SELECT LEN (' Fred'), -- find how many chars - note the spaces
UPPER(' Fred'), -- convert all chars to upper case
LOWER(' Fred'), -- convert all chars to lower case
LEFT (' Fred', 2), -- return just the left-most 2 chars
RIGHT(' Fred', 2), -- return just the right-most 2 chars
LTRIM(' Fred') -- get rid of any space chars on left
-- you might also want to find the location of a certain character
SELECT CHARINDEX(' ', 'Fred Flintstone') AS [location of space character]
-- and also, you can combine these functions into a more complex expression
SELECT 'Fred Flintstone' AS [original full name],
RIGHT('Fred Flintstone', -- right-most 10 characters
LEN('Fred Flintstone') -- the number 15
- CHARINDEX(' ', 'Fred Flintstone') -- the number 5
) AS [last name only]
---------------------------------------------------------------------------------
-- what about data from a table - not literals
SELECT ProductName,
LEN(ProductName) AS [length of name],
UPPER(ProductName) AS [uppercase],
CHARINDEX(' ', ProductName),
RIGHT(ProductName, LEN(ProductName) - CHARINDEX(' ', ProductName))
FROM Products
---------------------------------------------------------------------------------
-- in summary
-- string literals are delimited by single quotes
-- plus sign means concatenate
-- functions help with other tasks
-- Database by Doug
-- Douglas Kline
-- 8/28/2018
-- Character Expressions
