MSSQL – Page 3 – Alejandro G. Carlstein Ramos Mejia Blog

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Right (Outer) Join with Exclusion

Tables

Table 1: Employees

EmployeeID	LastName	FirstName	BirthDate	Photo
8	Callahan	Laura	1/9/1958	EmpID8.pic
9	Dodsworth	Anne	7/2/1969	EmpID9.pic
10	West	Adam	9/19/1928	EmpID10.pic

Table 2: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10255	68	9	7/12/1996	3
10262	65	8	7/22/1996	3
10263	20	9	7/23/1996	3
10268	33	8	7/30/1996	3

Note: There is no record with EmployeeID = 10 (West Adam).

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
  RIGHT JOIN Employees
    ON Orders.EmployeeID = Employees.EmployeeID
WHERE Employees.EmployeeID IS NULL
ORDER BY Orders.OrderID;

Result

OrderID	LastName	FirstName
NULL	West	Adam

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Right (Outer) Join

Tables

Table 1: Customers

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
37	Hungry Owl All-Night Grocers	Patricia McKenna	8 Johnstown Road	Cork		Ireland
77	The Big Cheese	Liz Nixon	89 Jefferson Way Suite 2	Portland	97201	USA

Table 2: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2
10365	3	3	1996-11-27	2

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste)

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  RIGHT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerName 
  IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
       , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10310	The Big Cheese	9/20/1996

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Left (Outer) Join with Exclusion

Tables

Table 1: Customers

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
37	Hungry Owl All-Night Grocers	Patricia McKenna	8 Johnstown Road	Cork		Ireland
77	The Big Cheese	Liz Nixon	89 Jefferson Way Suite 2	Portland	97201	USA

Table 2: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2
10365	3	3	1996-11-27	2

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste)

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Orders.CustomerID IS NULL
  AND Customers.CustomerName 
    IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
         , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result

OrderID	CustomerName	OrderDate	OrderID
NULL	Alfreds Futterkiste	NULL	NULL

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Left (Outer) Join

Tables

Table 1: Customers

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
37	Hungry Owl All-Night Grocers	Patricia McKenna	8 Johnstown Road	Cork		Ireland
77	The Big Cheese	Liz Nixon	89 Jefferson Way Suite 2	Portland	97201	USA

Table 2: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2
10365	3	3	11/27/1996	2

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste).

Query:

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerName 
  IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
       , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result:

OrderID	CustomerName	OrderDate
NULL	Alfreds Futterkiste	NULL
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10310	The Big Cheese	9/20/1996

Leave a comment

Alejandro G. Carlstein Ramos Mejia March 7, 2014 March 7, 2014 MSSQL / SQL

MSSQL Basic Cheat Sheet

Description	Example
Version	SELECT @@version

List of Users	SELECT name FROM master..syslogins
Current User	SELECT user; — Returns user such as “dbo”SELECT user_name(); — Returns user such as “dbo”SELECT system_user; — Returns [DOMAIN]\[USERNAME]SELECT loginame FROM master.sysprocesses WHERE spid = @@SPID;
Privileges	SELECT is_srvrolemember(‘sysadmin’); SELECT is_srvrolemember(‘securityadmin’); SELECT is_srvrolemember(‘serveradmin’); SELECT is_srvrolemember(‘setupadmin’); SELECT is_srvrolemember(‘diskadmin’); SELECT is_srvrolemember(‘bulkadmin’); SELECT is_srvrolemember(‘dbcreator’);———————————————————-SELECT name FROM master..syslogins WHERE sysadmin = 1; SELECT name FROM master..syslogins WHERE securityadmin = 1; SELECT name FROM master..syslogins WHERE serveradmin = 1; SELECT name FROM master..syslogins WHERE serveradmin = 1; — May return empty SELECT name FROM master..syslogins WHERE setupadmin = 1; — May return empty SELECT name FROM master..syslogins WHERE diskadmin = 1; — May return empty SELECT name FROM master..syslogins WHERE bulkadmin = 1; SELECT name FROM master..syslogins WHERE dbcreator = 1; SELECT name FROM master..syslogins WHERE hasaccess = 1; SELECT name FROM master..syslogins WHERE denylogin = 0; SELECT name FROM master..syslogins WHERE isntname = 0; SELECT name FROM master..syslogins WHERE isntgroup = 0;
List All Databases	SELECT name FROM master..sysdatabases;
Database by Id	SELECT DB_NAME(ID); — Where ID is 0, 1, 2, …, N
Current Database	SELECT DB_NAME();
List Columns	SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name=’TABLE_NAME’); — Where TABLE_NAME is the table name on the current database
List Tables	SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘U’; — Where DATABASE_NAME is the database which you wish to list the tables
List Views	SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘V’; — Where DATABASE_NAME is the database which you wish to list the views
Hostname	SELECT HOST_NAME();
Information and Location of DB file	EXEC sp_helpdb DATABASE_NAME; — Where DATABASE_NAME is the name of the database

Leave a comment

Tables

Table 1: Employees

Table 2: Orders

Query

Result

Tables

Table 1: Customers

Table 2: Orders

Query

Result

Tables

Table 1: Customers

Table 2: Orders

Query

Result

Tables

Table 1: Customers

Table 2: Orders

Query:

Result:

Description

Example